· 9 years ago · Jan 04, 2017, 01:44 PM
1public class BergDAO {
2 private String databasePath;
3 private Connection connection;
4 private Statement statement;
5
6
7 public BergDAO(String databasePath) {
8 this.databasePath = databasePath;
9 laadDriver();
10 maakConnectie();
11 maakStatement();
12 maakTabel();
13 sluitConnectie();
14 }
15
16 private void laadDriver(){
17 try {
18 Class.forName("org.hsqldb.jdbcDriver");
19 }
20 catch(ClassNotFoundException ex) {
21 System.out.println("Error: unable to load driver!");
22 System.exit(1);
23 }
24 }
25
26 private Statement maakStatement(){
27 try {
28 statement = connection.createStatement();
29 return statement;
30 } catch (SQLException e) {
31 e.printStackTrace();
32 }
33 return null;
34 }
35
36 private void sluitConnectie(){
37 try {
38 statement.close();
39 connection.close();
40 } catch (SQLException e) {
41 e.printStackTrace();
42 } finally {
43 if (connection != null){
44 try {
45 connection.close();
46 } catch (SQLException e) {
47 e.printStackTrace();
48 }
49 }
50 }
51 }
52
53 private void maakConnectie(){
54 try {
55 connection = DriverManager.getConnection("jdbc:hsqldb:file:" + databasePath, "sa", "");
56 } catch (SQLException e) {
57 e.printStackTrace();
58 }
59 }
60
61 public void vullTabel(){
62 for (Berg berg : Data.getList()) {
63 create(berg);
64 System.out.println(berg);
65 }
66 }
67
68 private void maakTabel() {
69 try {
70 Statement statement = connection.createStatement();
71 statement.executeUpdate("DROP TABLE IF EXISTS berg");
72 String create = "CREATE TABLE berg " +
73 "(id INTEGER NOT NULL IDENTITY ," +
74 "name VARCHAR(20) NOT NULL," +
75 "height INTEGER NOT NULL," +
76 "area VARCHAR(20) NOT NULL," +
77 "x DOUBLE NOT NULL," +
78 "y DOUBLE NOT NULL," +
79 "massif VARCHAR(20) NOT NULL," +
80 "date DATE)";
81 statement.executeUpdate(create);
82 statement.close();
83 } catch (SQLException e) {
84 e.printStackTrace();
85 }
86 }
87
88 public boolean create(Berg berg){
89 maakConnectie();
90 Statement statement = maakStatement();
91 boolean b = false;
92 try {
93 if (berg != null){
94 /*statement.execute(String.format("INSERT INTO berg (id, name, height, area, x, y, massif, date) VALUES" +
95 "(NULL, '%s', %d, '%s', %f, %f, '%s', '%s'", berg.getName(), berg.getHeight(), berg.getArea(),
96 berg.getX_co_top(), berg.getY_co_top(), berg.getMassif().name(), Date.valueOf(berg.getFirstClimb())
97 ));*/
98 /*statement.execute(String.format("INSERT INTO berg (id, name, height, area, x, y, massif, date) VALUES (NULL, '%s', %d, '%s', %.2f, %.2f, '%s', '%s')",
99 berg.getName(), berg.getHeight(), berg.getArea(), berg.getX_co_top(), berg.getY_co_top(),
100 berg.getMassif().name(), Date.valueOf(berg.getFirstClimb())));*/
101 statement.execute("INSERT INTO berg (id, name, height, area, x, y, massif, date)" +
102 "VALUES (NULL , '" + berg.getName() + "', " + berg.getHeight() + ", '" + berg.getArea() + "'," +
103 berg.getX_co_top() + ", " + berg.getY_co_top() + ", '" + berg.getMassif().name() + "', '" +
104 Date.valueOf(berg.getFirstClimb()) + "')");
105
106 b = true;
107 }
108 //sluitConnectie();
109 } catch (SQLException e) {
110 e.printStackTrace();
111 } finally {
112 sluitConnectie();
113 }
114 return b;
115 }
116
117 public Berg retrieve(int id){
118 maakConnectie();
119 Statement statement = maakStatement();
120 try {
121 ResultSet resultSet = statement.executeQuery(//String.format("SELECT * FROM berg WHERE id = %d", id));
122 "SELECT * FROM berg WHERE id = " + id);
123 if (resultSet.wasNull()){
124 return null;
125 }
126 if (/*!resultSet.wasNull()*/resultSet != null){
127 //System.out.println("lol");
128 resultSet.next();
129 Berg berg = new Berg(
130 resultSet.getInt("id"),
131 resultSet.getString("name"),
132 resultSet.getInt("height"),
133 resultSet.getString("area"),
134 resultSet.getDouble("x"),
135 resultSet.getDouble("y"),
136 Massif.valueOf(resultSet.getString("massif")),
137 resultSet.getDate("date").toLocalDate()
138 );
139 return berg;
140 }
141 return null;
142 } catch (SQLException e) {
143 //System.out.println("catch");
144 e.printStackTrace();
145 } finally {
146 //System.out.println("finally");
147 sluitConnectie();
148 }
149 return null;
150 }
151
152 public boolean update(Berg berg){
153 maakConnectie();
154 Statement statement = maakStatement();
155 try {
156 /*return statement.execute(String.format("UPDATE berg SET name='%s', height=%d, area=%s, x=%f, y=%f, massif=%s, date=%s" +
157 " WHERE id = %d",berg.getName(), berg.getHeight(), berg.getArea(), berg.getY_co_top(), berg.getY_co_top(),
158 berg.getMassif().name(), Date.valueOf(berg.getFirstClimb()), berg.getId()));
159 */
160 statement.execute("UPDATE berg SET name='" + berg.getName() + "', height=" + berg.getHeight() + ", area='" +
161 berg.getArea() + "', x=" + berg.getX_co_top() + ", y=" + berg.getY_co_top() + ", massif='" + berg.getMassif().name() +
162 "', date='" + Date.valueOf(berg.getFirstClimb()) + "' WHERE id=" + berg.getId());
163 return true;
164 } catch (SQLException e) {
165 e.printStackTrace();
166 }
167 return false;
168 }
169
170 public boolean delete(int pk){
171 maakConnectie();
172 Statement statement = maakStatement();
173 try {
174 return statement.execute(String.format("DELETE FROM berg WHERE id=%d", pk));
175 } catch (SQLException e) {
176 e.printStackTrace();
177 }
178 return false;
179 }
180
181 public Bergen filter(int height){
182 maakConnectie();
183 Statement statement = maakStatement();
184 try {
185 Bergen b = new Bergen();
186 ResultSet resultSet = statement.executeQuery(/*String.format("SELECT * FROM berg WHERE height > %d", height)*/
187 "SELECT * FROM berg WHERE height > " + height);
188 while (resultSet.next()) {
189 Berg berg = new Berg(resultSet.getInt("id"),
190 resultSet.getString("name"),
191 resultSet.getInt("height"),
192 resultSet.getString("area"),
193 resultSet.getDouble("x"),
194 resultSet.getDouble("y"),
195 Massif.valueOf(resultSet.getString("massif")),
196 resultSet.getDate("date").toLocalDate()
197 );
198 b.voegToe(berg);
199
200 }
201 return b;
202 } catch (SQLException e) {
203 e.printStackTrace();
204 }
205 return null;
206 }
207}