1
2
3
4 package sk.stuba.fiit.foo07.genex.common;
5
6 import java.io.IOException;
7 import java.io.InputStream;
8 import java.io.UnsupportedEncodingException;
9 import java.sql.Connection;
10 import java.sql.PreparedStatement;
11 import java.sql.ResultSet;
12 import java.sql.SQLException;
13 import java.sql.Timestamp;
14 import java.util.ArrayList;
15 import java.util.Calendar;
16
17 import javax.xml.parsers.DocumentBuilder;
18 import javax.xml.parsers.DocumentBuilderFactory;
19 import javax.xml.parsers.FactoryConfigurationError;
20 import javax.xml.parsers.ParserConfigurationException;
21
22 import org.apache.derby.tools.ij;
23 import org.w3c.dom.NamedNodeMap;
24 import org.w3c.dom.Node;
25 import org.w3c.dom.NodeList;
26 import org.xml.sax.SAXException;
27
28 import sk.stuba.fiit.foo07.genex.beans.Answer;
29 import sk.stuba.fiit.foo07.genex.beans.Question;
30 import sk.stuba.fiit.foo07.genex.dao.AnswerDao;
31 import sk.stuba.fiit.foo07.genex.dao.AnswerDaoDerby;
32 import sk.stuba.fiit.foo07.genex.dao.QuestionDao;
33 import sk.stuba.fiit.foo07.genex.dao.QuestionDaoDerby;
34
35
36
37
38
39 public class DerbyDBTools {
40
41 public static int runScript(Connection con, String resourceFilePath)
42 throws UnsupportedEncodingException, NullPointerException {
43
44 InputStream in = new DerbyDBTools().getClass().getResourceAsStream(
45 resourceFilePath);
46
47 return ij.runScript(con, in, "UTF8", System.out, "UTF8");
48 }
49
50
51
52
53 public static void cleanDatabase(Connection con) {
54
55 }
56
57 public static boolean databaseInitialized(Connection con) {
58 try {
59 boolean ret = true;
60 if (DerbyDBTools.runScript(con, "/ddl/dbCheck.sql") != 0)
61 return false;
62
63
64
65 return ret;
66 } catch (Exception e) {
67 return false;
68 }
69 }
70
71
72
73
74
75
76
77
78
79
80
81 public static void loadData(Connection con, String xmlPath)
82 throws SQLException, UnsupportedEncodingException {
83 if (con == null)
84 throw new NullPointerException(
85 "Database connection not initialized");
86
87 if (DerbyDBTools.databaseInitialized(con) != true) {
88 throw new SQLException(
89 "Database not initialized with default tables");
90 }
91
92 Question toImport = new Question();
93 PreparedStatement pst = null;
94 ResultSet rs = null;
95 int importCategoryID = 0;
96 try {
97 pst = con
98 .prepareStatement("SELECT userID FROM TUSER WHERE login = ?");
99 pst.setString(1, "importer");
100
101 rs = pst.executeQuery();
102 rs.next();
103 toImport.setUserID(rs.getInt(1));
104 } catch (SQLException e) {
105 throw new SQLException("User for importing does not exist");
106 }
107 try {
108 pst = con
109 .prepareStatement("SELECT questionTypeID FROM QuestionType WHERE name = ?");
110 pst.setString(1, "Single choice");
111 rs = pst.executeQuery();
112 rs.next();
113 toImport.setQuestionTypeID(rs.getInt(1));
114 } catch (SQLException e) {
115 throw new SQLException("QuestionType for importing does not exist");
116 }
117 try {
118 pst = con
119 .prepareStatement("SELECT categoryID FROM Category WHERE name = ?");
120 pst.setString(1, "Import");
121
122 rs = pst.executeQuery();
123
124 rs.next();
125 importCategoryID = rs.getInt(1);
126 } catch (SQLException e) {
127 throw new SQLException("Category for importing does not exist");
128 }
129 try {
130 DocumentBuilderFactory factory = DocumentBuilderFactory
131 .newInstance();
132 DocumentBuilder parser = factory.newDocumentBuilder();
133 Node doc = parser.parse(new DerbyDBTools().getClass()
134 .getResourceAsStream(xmlPath));
135 followNode(con, doc, toImport, importCategoryID);
136
137 } catch (SAXException e) {
138 System.out.println(xmlPath + " is not well-formed.");
139 } catch (IOException e) {
140 System.out
141 .println("Due to an IOException, the parser could not check "
142 + xmlPath);
143 } catch (FactoryConfigurationError e) {
144 System.out.println("Could not locate a factory class");
145 } catch (ParserConfigurationException e) {
146 System.out.println("Could not locate a JAXP parser");
147 }
148 }
149
150 private static void followNode(Connection con, Node node,
151 Question toImport, int importCategoryID) {
152
153 if (node.getNodeName().equals("question")) {
154 NodeList children = node.getChildNodes();
155 ArrayList<Answer> answers = new ArrayList<Answer>();
156 for (int i = 0; i < children.getLength(); i++) {
157 Node item = children.item(i);
158 if (item.getNodeName().equals("text")) {
159 toImport.setText(item.getFirstChild().getNodeValue());
160 }
161
162 if (item.getNodeName().equals("answers")) {
163 Node ansNode = item.getFirstChild();
164
165 while (ansNode != null) {
166 Answer tmp = new Answer();
167 if (ansNode.getNodeName().equals("answer")) {
168 tmp.setText(ansNode.getFirstChild().getNodeValue());
169 NamedNodeMap nnm = ansNode.getAttributes();
170 tmp.setIsCorrect(Short.valueOf(
171 nnm.getNamedItem("correct").getNodeValue())
172 .equals(new Short((short) 1)));
173 answers.add(tmp);
174 }
175
176 ansNode = ansNode.getNextSibling();
177 }
178 }
179 }
180 NamedNodeMap nnm = node.getAttributes();
181 toImport.setDifficulty(Integer.valueOf(nnm.getNamedItem(
182 "difficulty").getNodeValue()));
183
184 toImport.setCreated(new Timestamp(Calendar.getInstance()
185 .getTimeInMillis()));
186 toImport.setLastUpdate(new Timestamp(Calendar.getInstance()
187 .getTimeInMillis()));
188
189 QuestionDao questionDao = new QuestionDaoDerby(con);
190
191
192 try {
193 questionDao.addQuestion(importCategoryID, toImport);
194
195 AnswerDao ansDao = new AnswerDaoDerby(con);
196
197 ansDao.addAnswersToQuestion(toImport.getQuestionID(), answers);
198 } catch (Exception e) {
199 }
200 } else if (node.hasChildNodes()) {
201 Node firstChild = node.getFirstChild();
202 followNode(con, firstChild, toImport, importCategoryID);
203 }
204
205 Node nextNode = node.getNextSibling();
206 if (nextNode != null)
207 followNode(con, nextNode, toImport, importCategoryID);
208
209 }
210
211 }