PageRenderTime 57ms CodeModel.GetById 25ms RepoModel.GetById 1ms app.codeStats 0ms

/Apputv/Ludo/server/DatabaseTableModel.java

https://bitbucket.org/Sfenstad91/java
Java | 623 lines | 432 code | 40 blank | 151 comment | 50 complexity | cec3f4ce243d034f4416f0644ad50482 MD5 | raw file
  1. package no.ntnu.imt3281.ludo.server;
  2. import java.sql.Connection;
  3. import java.sql.DatabaseMetaData;
  4. import java.sql.DriverManager;
  5. import java.sql.PreparedStatement;
  6. import java.sql.ResultSet;
  7. import java.sql.ResultSetMetaData;
  8. import java.sql.SQLException;
  9. import java.sql.Statement;
  10. import java.util.ArrayList;
  11. import java.util.HashMap;
  12. import java.util.logging.Logger;
  13. import javax.swing.table.AbstractTableModel;
  14. import no.ntnu.imt3281.I18N.I18N;
  15. import no.ntnu.imt3281.ludo.client.Globals;
  16. /**
  17. * This class contains all that has to do with the database. Creating database, creating tables, queries
  18. * rows, columns and so on.
  19. * Borrowed some code from Oivind Kolloen (Skoleeksempler --> Databases)
  20. * @author
  21. *
  22. */
  23. public class DatabaseTableModel extends AbstractTableModel {
  24. private static Logger logger = Logger.getLogger(Globals.LOG_NAME);
  25. private final static String url = "jdbc:derby:FiskDb;create=true";
  26. private Connection con;
  27. private Statement stmt;
  28. private ResultSet res;
  29. private ResultSetMetaData resMeta;
  30. private DatabaseMetaData meta;
  31. private int numRows;
  32. private boolean connectedToDatabase = false;
  33. private static final long serialVersionUID = 1L;
  34. /**
  35. * Constructor of class DatabaseTableModel. This constructor connects to the database.
  36. * @throws SQLException
  37. */
  38. public DatabaseTableModel() throws SQLException {
  39. try{
  40. con = DriverManager.getConnection(url);
  41. stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
  42. connectedToDatabase = true;
  43. meta = con.getMetaData();
  44. res = meta.getTables(null, "APP", "PLAYER", null);
  45. }catch (SQLException e) {
  46. logger.throwing(this.getClass().getName(), "DatabaseTableModel", e);
  47. }
  48. /**
  49. * Checks if table exists, if it doesn't create table
  50. */
  51. if(!res.next()) {
  52. createPlayerTable();
  53. createChatTable();
  54. }
  55. }
  56. /**
  57. * Method that creates the player table. Has username as a primary key
  58. * @see java.sql.Connection#createStatement()
  59. * @see java.sql.Statement#execute(String)
  60. * As https://bitbucket.org/okolloen/imt3281-project2-2017/wiki/Home notes that a user should be
  61. * able to change its username, we can not use it as a PrimaryKey - So we will use ID.
  62. */
  63. private void createPlayerTable() {
  64. //stmt represents the SQL statement. if connection succesful run the table query
  65. try (Statement stmtt = con.createStatement()){
  66. stmtt.execute("CREATE TABLE player ("
  67. + "id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
  68. + "username varchar(128) NOT NULL, "
  69. + "hashpassword varchar(328) NOT NULL, "
  70. + "token varchar(400), "
  71. + "bio varchar(328), "
  72. + "profilePicture blob,"
  73. + "gamesPlayed int DEFAULT 0,"
  74. + "gamesWon int DEFAULT 0,"
  75. + "PRIMARY KEY (id))");
  76. } catch (SQLException e) {
  77. logger.throwing(this.getClass().getName(), "createPlayerTable", e);
  78. }
  79. }
  80. /**
  81. * Method that creates the chat table. Chat table has id as its primary key
  82. * @see java.sql.Connection#createStatement()
  83. * @see java.sql.Statement#execute(String)
  84. */
  85. private void createChatTable() {
  86. try (Statement stmtt = con.createStatement()){
  87. stmtt.execute("CREATE TABLE chat ("
  88. + "id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
  89. + "username varchar(128) NOT NULL, "
  90. + "message TEXT NOT NULL, "
  91. + "groupId INT NOT NULL,"
  92. + "PRIMARY KEY (id))");
  93. } catch (SQLException e) {
  94. logger.throwing(this.getClass().getName(), "createChatTable", e);
  95. }
  96. }
  97. /**
  98. * Method to close the connection to the database
  99. */
  100. public void disconnectFromDatabase () {
  101. if (connectedToDatabase) {
  102. try {
  103. res.close();
  104. stmt.close();
  105. con.close();
  106. } catch (SQLException sqle) {
  107. logger.throwing(this.getClass().getName(), "disconnectFromDatabase", sqle);
  108. } finally {
  109. connectedToDatabase = false;
  110. }
  111. }
  112. }
  113. /**
  114. * Method gotten from AbstractTableModel which DatabaseTableModel extends from
  115. */
  116. @Override
  117. public int getColumnCount() throws IllegalStateException {
  118. if (!connectedToDatabase)
  119. throw new IllegalStateException("Unable to connect to database");
  120. try {
  121. return resMeta.getColumnCount();
  122. } catch (SQLException sqle) {
  123. logger.throwing(this.getClass().getName(), "getColumnCount", sqle);
  124. }
  125. return 0;
  126. }
  127. /**
  128. * Method gotten from AbstractTableModel which DatabaseTableModel extends from
  129. */
  130. @Override
  131. public String getColumnName(int column) throws IllegalStateException {
  132. if (!connectedToDatabase)
  133. throw new IllegalStateException("Unable to connect to database");
  134. try {
  135. return resMeta.getColumnName(column+1);
  136. } catch (SQLException sqle) {
  137. logger.throwing(this.getClass().getName(), "getColumnName", sqle);
  138. }
  139. return "";
  140. }
  141. /**
  142. * Method gotten from AbstractTableModel which DatabaseTableModel extends from
  143. */
  144. @Override
  145. public int getRowCount() {
  146. if (!connectedToDatabase)
  147. throw new IllegalStateException("Unable to connect to database");
  148. return numRows;
  149. }
  150. /**
  151. * Method gotten from AbstractTableModel which DatabaseTableModel extends from
  152. */
  153. @Override
  154. public Object getValueAt(int rowIndex, int columnIndex) {
  155. if (!connectedToDatabase)
  156. throw new IllegalStateException("Unable to connect to database");
  157. try {
  158. res.absolute(rowIndex + 1);
  159. return res.getObject(columnIndex + 1);
  160. } catch (SQLException sqle) {
  161. logger.throwing(this.getClass().getName(), "getValueAt", sqle);
  162. }
  163. return "";
  164. }
  165. /**
  166. * Method gotten from AbstractTableModel which DatabaseTableModel extends from
  167. */
  168. @Override
  169. public boolean isCellEditable(int rowIndex, int columnIndex) {
  170. return (columnIndex>0);
  171. }
  172. /**
  173. * Method gotten from AbstractTableModel which DatabaseTableModel extends from
  174. */
  175. @Override
  176. public void setValueAt(Object val, int row, int col) {
  177. if (!connectedToDatabase)
  178. throw new IllegalStateException("Unable to connect to database");
  179. try {
  180. // Move to the right row
  181. res.absolute(row+1);
  182. // Update the value in the given column
  183. res.updateObject(col+1, val);
  184. // Fire the update back at the database
  185. // NOTE, can be buffered, important to close the result set/connection
  186. // correctly to ensure that the database gets updated.
  187. res.updateRow();
  188. // Let the table know the model has changed
  189. fireTableCellUpdated(row, col);
  190. } catch (SQLException sqle) {
  191. logger.throwing(this.getClass().getName(), "setValueAt", sqle);
  192. }
  193. }
  194. /**
  195. * Method to add user to the database. It takes the username and password written by the user in the application
  196. * and sends it to a method to set the strings together and send it with a prepareStatement for security.
  197. * @param username written by user
  198. * @param password written by user
  199. */
  200. public void addUser(String username, String password, String token) {
  201. String sql = "INSERT INTO player (username, hashpassword, token) VALUES(?, ?, ?)";
  202. execute(sql, username, HashAndSalt.hashPassword(password), token);
  203. }
  204. /**
  205. * Builds the string to be sent to the databse with a PrepareStatement for max security against SQL injection.
  206. * @param sql the SQL string to be executed
  207. * @param arg1 depending on what is sent, but is the username if the user is trying to log in or register
  208. * @param arg2 depending on what is sent, but is the password if the user is trying to log in or register
  209. * @return
  210. */
  211. private void execute(String sql, String arg1, String arg2, String arg3) {
  212. try (PreparedStatement pstmt = con.prepareStatement(sql)) {
  213. pstmt.setString(1, arg1);
  214. if (arg2 != null)
  215. pstmt.setString(2, arg2);
  216. if (arg3 != null)
  217. pstmt.setString(3, arg3);
  218. pstmt.execute();
  219. } catch(SQLException sqle) {
  220. logger.throwing(this.getClass().getName(), "execute", sqle);
  221. }
  222. }
  223. /**
  224. * Method to get the token from the database based on the username sent. This method
  225. * is used with the autologin method which the client reads token of a registered user from file
  226. * and sends to database for validation.
  227. * @param username
  228. * @return token based on the username given
  229. * @throws SQLException
  230. */
  231. public String getToken(String username) throws SQLException {
  232. PreparedStatement prepStatement = null;
  233. ResultSet reSet = null;
  234. String query = "select token from player where username = ?";
  235. try {
  236. prepStatement = con.prepareStatement(query);
  237. prepStatement.setString(1, username);
  238. reSet = prepStatement.executeQuery();
  239. if(reSet.next()) {
  240. String tok = reSet.getString("TOKEN");
  241. return tok;
  242. } else {
  243. String tok = "TOKENFAILURE";
  244. return tok;
  245. }
  246. } catch (Exception e) {
  247. logger.throwing(this.getClass().getName(), "getToken", e);
  248. String tok = "TOKENFAILURE";
  249. return tok;
  250. } finally {
  251. if(prepStatement != null){
  252. prepStatement.close();
  253. reSet.close();
  254. }
  255. }
  256. }
  257. /**
  258. * Method to autologin a user that have chosen the option "Remember me". It collects the username and hashed
  259. * password from the database from the token given.
  260. * @param token
  261. * @return
  262. * @throws SQLException
  263. */
  264. public String[] autoLogin(String token) throws SQLException {
  265. PreparedStatement prepStatement = null;
  266. ResultSet reSet = null;
  267. String query = "select username, hashpassword, id from player where token = ?";
  268. try {
  269. prepStatement = con.prepareStatement(query);
  270. prepStatement.setString(1, token);
  271. reSet = prepStatement.executeQuery();
  272. if(reSet.next()) {
  273. String user = reSet.getString("USERNAME");
  274. String pw = reSet.getString("HASHPASSWORD");
  275. int id = reSet.getInt("ID");
  276. String strId = Integer.toString(id);
  277. String[] arr = new String[3];
  278. arr[0] = user;
  279. arr[1] = pw;
  280. arr[2] = strId;
  281. return arr;
  282. }
  283. } catch (Exception e){
  284. logger.throwing(this.getClass().getName(), "autoLogin", e);
  285. } finally {
  286. if(prepStatement != null){
  287. prepStatement.close();
  288. reSet.close();
  289. }
  290. }
  291. return null;
  292. }
  293. /**
  294. * Method to check if the user can log in with the username and password he/she has written.
  295. * This method sends the password given in parameter and the hashed password from the database
  296. * to the validatePassword method to decrypt the password for validation.
  297. * @see no.ntnu.imt3281.ludo.server.HashAndSalt#validatePassword(String, String)
  298. * @param user
  299. * @param password
  300. * @return
  301. * @throws SQLException
  302. */
  303. public boolean isLoggedIn(String user, String password) throws SQLException {
  304. PreparedStatement preparedStatement = null;
  305. ResultSet resultSet = null;
  306. String query = "select * from player where username = ?";
  307. try {
  308. preparedStatement = con.prepareStatement(query);
  309. preparedStatement.setString(1, user);
  310. resultSet = preparedStatement.executeQuery();
  311. if (resultSet.next()) {
  312. String pass = resultSet.getString("HASHPASSWORD");
  313. if(HashAndSalt.validatePassword(password,pass))
  314. return true;
  315. return false;
  316. } else {
  317. return false;
  318. }
  319. } catch (Exception e) {
  320. logger.throwing(this.getClass().getName(), "isLoggedIn", e);
  321. return false;
  322. } finally {
  323. if(preparedStatement != null) {
  324. preparedStatement.close();
  325. resultSet.close();
  326. }
  327. }
  328. }
  329. /**
  330. * Method to check if the username is taken in the database when adding a new user.
  331. * @param username the username to be checked
  332. * @return true if taken, false if not
  333. * @throws SQLException
  334. */
  335. public boolean isUserTaken(String username) {
  336. PreparedStatement preparedStatement = null;
  337. ResultSet resultSet = null;
  338. String query = "select username from player where username = ?";
  339. try {
  340. preparedStatement = con.prepareStatement(query);
  341. preparedStatement.setString(1, username);
  342. resultSet = preparedStatement.executeQuery();
  343. while (resultSet.next()) {
  344. if (resultSet.getString(1).equals(username))
  345. return true;
  346. }
  347. } catch (SQLException e) {
  348. logger.throwing(this.getClass().getName(), "isUserTaken", e);
  349. } finally {
  350. try {
  351. if(preparedStatement != null) {
  352. preparedStatement.close();
  353. resultSet.close();
  354. }
  355. } catch (SQLException e) {
  356. logger.throwing(this.getClass().getName(), "isUserTaken", e);
  357. }
  358. }
  359. return false;
  360. }
  361. /**
  362. * Gets Info about a user, given a username. Can be used in edit profile, and also in view profile if we add that.
  363. * @param name
  364. * @return a hashmap containing the things we want
  365. */
  366. public HashMap<String,Object> getInfo(String name) {
  367. HashMap<String,Object> map = new HashMap<>();
  368. PreparedStatement preparedStatement = null;
  369. ResultSet resultSet = null;
  370. String query = "select id,username,bio,profilePicture from player where username = ?";
  371. try {
  372. preparedStatement = con.prepareStatement(query);
  373. preparedStatement.setString(1, name);
  374. resultSet = preparedStatement.executeQuery();
  375. while (resultSet.next()) {
  376. // Get the info we want.
  377. map.put("USERNAME",resultSet.getString("USERNAME") );
  378. map.put("BIO",resultSet.getString("BIO") );
  379. map.put("IMAGE",resultSet.getBytes("profilePicture"));
  380. map.put("ID",resultSet.getInt("id"));
  381. return map;
  382. }
  383. } catch (SQLException e) {
  384. logger.throwing(this.getClass().getName(), "getInfo", e);
  385. } finally {
  386. try {
  387. if(preparedStatement != null) {
  388. preparedStatement.close();
  389. resultSet.close();
  390. }
  391. } catch (SQLException e) {
  392. logger.throwing(this.getClass().getName(), "getInfo", e);
  393. }
  394. }
  395. return null;
  396. }
  397. /**
  398. * Method to update the users profile picture. Method uses hashmap to collect the sent data
  399. * from client, so that it knows what user to update.
  400. * @param map hashmap from clientside filled by user
  401. */
  402. public void updateUserPicture(HashMap<String, Object> map){
  403. String query = "update player set profilePicture = ? where id = ?";
  404. PreparedStatement preparedStatement = null;
  405. try {
  406. preparedStatement = con.prepareStatement(query);
  407. preparedStatement.setBytes(1, (byte[]) map.get("IMAGE"));
  408. preparedStatement.setInt(2, (int) map.get("ID"));
  409. if(preparedStatement.execute())
  410. System.out.println("Succeded. We updated the picture");
  411. }
  412. catch (Exception e){
  413. logger.throwing(this.getClass().getName(), "updateUserPicture", e);
  414. } finally {
  415. try {
  416. if(preparedStatement != null) {
  417. preparedStatement.close();
  418. }
  419. } catch (SQLException e) {
  420. logger.throwing(this.getClass().getName(), "updateUserPicture", e);
  421. }
  422. }
  423. }
  424. /**
  425. * Method to edit the user profile of the user. It collects the data from the hashmap sent by the
  426. * client.
  427. * @param map hashmap from client containing username, bio, password
  428. */
  429. public void editUserInfo(HashMap<String, Object> map) {
  430. //update survey set name='newName' where id = 111
  431. String query = "update player set username = ?,bio = ? where username = ?";
  432. if(map.get("PASSWORD") != null){
  433. // Before comming this far, we should have checked if password and Password2 is the same
  434. query = "update player set username = ?,bio = ?, HASHPASSWORD = ? where username = ?";
  435. }
  436. PreparedStatement preparedStatement = null;
  437. try {
  438. preparedStatement = con.prepareStatement(query);
  439. preparedStatement.setString(1, (String) map.get("USERNAME"));
  440. preparedStatement.setString(2, (String) map.get("BIO"));
  441. if(map.get("PASSWORD") != null) {
  442. String pass = HashAndSalt.hashPassword((String) map.get("PASSWORD"));
  443. preparedStatement.setString(3, pass);
  444. preparedStatement.setString(4, (String) map.get("USERNAME"));
  445. }else {
  446. preparedStatement.setString(3, (String) map.get("USERNAME"));
  447. }
  448. if(preparedStatement.execute())
  449. System.out.println("Succeded. We updated the userInfo");
  450. } catch (SQLException e) {
  451. logger.throwing(this.getClass().getName(), "editUserInfo", e);
  452. } finally {
  453. try {
  454. if(preparedStatement != null) {
  455. preparedStatement.close();
  456. }
  457. } catch (SQLException e) {
  458. logger.throwing(this.getClass().getName(), "editUserInfo", e);
  459. }
  460. }
  461. }
  462. /**
  463. * Adding a username and chat message to chat table
  464. * @param username of the player sending the chat message
  465. * @param message that was sent to the chat
  466. */
  467. public void logChatMessages(String username, String message, Integer groupId) {
  468. String sql = "INSERT INTO chat (username, message, groupId) VALUE(?, ?, ?)";
  469. excecuteLog(sql, username, message, groupId);
  470. }
  471. /**
  472. * Execute log query
  473. * @param sql query
  474. * @param username argument
  475. * @param message argument
  476. * @param groupId argument
  477. * @return resultset or null
  478. */
  479. private void excecuteLog(String sql, String username, String message, Integer groupId) {
  480. try (PreparedStatement stmtt = con.prepareStatement(sql)) {
  481. stmtt.setString(1, username);
  482. if (message != null)
  483. stmtt.setString(2, message);
  484. if (groupId != null)
  485. stmtt.setInt(3, groupId);
  486. stmtt.execute();
  487. } catch(SQLException e) {
  488. logger.throwing(this.getClass().getName(), "excecuteLog", e);
  489. }
  490. }
  491. /**
  492. * Update a players games won score
  493. * @param username of player who won a game
  494. */
  495. public void incrementGamesWon(String username) {
  496. String query = "update player set gamesWon = gamesWon + 1 where username = ?";
  497. executeUpdateGames(query, username);
  498. }
  499. /**
  500. * Update a players games played score
  501. * @param username of player who finished a game
  502. */
  503. public void incrementGamesPlayed(String username) {
  504. String query = "update player set gamesPlayed = gamesPlayed + 1 where username = ?";
  505. executeUpdateGames(query, username);
  506. }
  507. /**
  508. * Executes an sql query of updating game score
  509. * @param query which to be sent
  510. * @param username of player to add score
  511. * @return result of query
  512. */
  513. private void executeUpdateGames(String query, String username) {
  514. try(PreparedStatement stmtt = con.prepareStatement(query)) {
  515. stmtt.setString(1, username);
  516. stmtt.execute();
  517. } catch (SQLException e) {
  518. logger.throwing(this.getClass().getName(), "executeUpdateGames", e);
  519. }
  520. }
  521. /**
  522. * Gets a ranking list of most games won
  523. * @return top ten list of most games won
  524. */
  525. public ArrayList<String> getWonRanking() {
  526. ArrayList<String> list = new ArrayList<String>();
  527. PreparedStatement preparedStatement = null;
  528. ResultSet resultSet = null;
  529. String tmp;
  530. String query = "select username,gamesWon from player order by gamesWon DESC fetch first 10 rows only";
  531. try {
  532. preparedStatement = con.prepareStatement(query);
  533. resultSet = preparedStatement.executeQuery();
  534. while (resultSet.next()) {
  535. tmp = String.format("%-20s %10s",resultSet.getString("USERNAME"), I18N.getBundle().getString("ranking.winnersWon") + resultSet.getString("gamesWon"));
  536. list.add(tmp);
  537. }
  538. } catch (SQLException e) {
  539. logger.throwing(this.getClass().getName(), "getWonRanking", e);
  540. } finally {
  541. try {
  542. if(preparedStatement != null) {
  543. preparedStatement.close();
  544. resultSet.close();
  545. }
  546. } catch (SQLException e) {
  547. logger.throwing(this.getClass().getName(), "getWonRanking", e);
  548. }
  549. }
  550. return list;
  551. }
  552. /**
  553. * Gets a ranking list of most played games
  554. * @return top ten list of games played
  555. */
  556. public ArrayList<String> getPlayedRanking() {
  557. ArrayList<String> list = new ArrayList<String>();
  558. PreparedStatement preparedStatement = null;
  559. ResultSet resultSet = null;
  560. String tmp;
  561. String query = "select username,gamesPlayed from player order by gamesPlayed DESC FETCH FIRST 10 ROWS ONLY";
  562. try {
  563. preparedStatement = con.prepareStatement(query);
  564. resultSet = preparedStatement.executeQuery();
  565. while (resultSet.next()) {
  566. tmp = String.format("%-20s %10s",resultSet.getString("USERNAME"),I18N.getBundle().getString("ranking.winnersPlayed") + resultSet.getString("gamesPlayed"));
  567. list.add(tmp);
  568. }
  569. } catch (SQLException e) {
  570. logger.throwing(this.getClass().getName(), "getPlayedRanking", e);
  571. } finally {
  572. try {
  573. if(preparedStatement != null) {
  574. preparedStatement.close();
  575. resultSet.close();
  576. }
  577. } catch (SQLException e) {
  578. logger.throwing(this.getClass().getName(), "getPlayedRanking", e);
  579. }
  580. }
  581. return list;
  582. }
  583. }