-
Notifications
You must be signed in to change notification settings - Fork 0
/
DBUserDao.java
177 lines (155 loc) · 4.85 KB
/
DBUserDao.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
package teoskanta.user.dao;
import java.io.File;
import teoskanta.user.User;
import java.util.*;
import java.sql.*;
public class DBUserDao implements UserDao<User, Integer> {
private Connection connection;
private Statement stat;
private PreparedStatement stmt;
/**
* Sets up connection to db.
*
* @throws SQLException
*/
private void startConn() throws SQLException {
connection = DriverManager.getConnection("jdbc:sqlite:database.db");
stat = connection.createStatement();
}
/**
* Closes connection to db.
*
* @throws SQLException
*/
private void closeConn() throws SQLException {
stat.close();
stmt.close();
connection.close();
}
/**
* Checks that database file and table exists.
*/
public void checkDBFile() {
// check if database file exists
String userTable = "CREATE TABLE IF NOT EXISTS Users ("
+ "`id` INTEGER PRIMARY KEY AUTOINCREMENT,"
+ "`username` TEXT NOT NULL,"
+ "`password` TEXT NOT NULL);";
//String db = "jdbc:sqlite:database.db";
try {
startConn();
stat = connection.createStatement();
stat.executeUpdate(userTable);
System.out.println("A new database has been created or checked.");
closeConn();
} catch (Exception e) {
System.out.println("Database check produced an error: " + e);
}
}
/**
* Checks database for user id.
*
* @param username String-type input username
* @param password String-type input password
* @return returns user id
* @throws SQLException
*/
public int getUserIdFromDB(String username, String password) throws SQLException {
int id;
startConn();
stmt = connection.prepareStatement("SELECT * FROM Users WHERE username = ? AND password = ?");
stmt.setString(1, username);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();
if (!rs.next()) {
return 0;
}
id = rs.getInt("id");
rs.close();
closeConn();
return id;
}
/**
* Creates user to database.
*
* @param user User-type input
* @throws SQLException
*/
@Override
public void create(User user) throws SQLException {
startConn();
stmt = connection.prepareStatement("INSERT INTO Users"
+ "(username, password)"
+ "VALUES (?,?)");
stmt.setString(1, user.getUsername());
stmt.setString(2, user.getPassword());
stmt.executeUpdate();
closeConn();
}
/**
* Method for reading the database for Users. DEPRECATED
* @param key int-type variable for userid input
* @return returns User-object
* @throws SQLException
*/
@Override
public User read(Integer key) throws SQLException {
startConn();
stmt = connection.prepareStatement("SELECT * FROM Users WHERE id = ?");
stmt.setInt(1, key);
ResultSet rs = stmt.executeQuery();
if (!rs.next()) {
return null;
}
User u = new User(rs.getString("username"), rs.getString("password"));
rs.close();
closeConn();
return u;
}
/**
* Method to find user from database.
*
* @param username String-type variable username
* @param password String-type variable password
* @return returns true if given input Strings are same as received strings from database false if not.
* @throws SQLException
*/
public Boolean findUser(String username, String password) throws SQLException {
startConn();
stmt = connection.prepareStatement("SELECT username, password FROM Users WHERE username = ? AND password = ?");
stmt.setString(1, username);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();
String rsUserName, rsPassword;
if (!rs.next()) {
return false;
} else {
rsUserName = rs.getString("username");
rsPassword = rs.getString("password");
}
System.out.println(rsUserName + " SQL " + rsPassword);
if (rsUserName.equals(username) && rsPassword.equals(password)) {
rs.close();
closeConn();
return true;
} else {
rs.close();
closeConn();
return false;
}
}
@Override
public User update(User object) throws SQLException {
// ei toteutettu
return null;
}
@Override
public void delete(User object, Integer key) throws SQLException {
// ei toteutettu
}
@Override
public List<User> list(Integer key) throws SQLException {
// ei toteutettu
return null;
}
}