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