/Apputv/Ludo/server/DatabaseTableModel.java
Java | 623 lines | 432 code | 40 blank | 151 comment | 50 complexity | cec3f4ce243d034f4416f0644ad50482 MD5 | raw file
- package no.ntnu.imt3281.ludo.server;
- import java.sql.Connection;
- import java.sql.DatabaseMetaData;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.ResultSetMetaData;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.logging.Logger;
- import javax.swing.table.AbstractTableModel;
- import no.ntnu.imt3281.I18N.I18N;
- import no.ntnu.imt3281.ludo.client.Globals;
- /**
- * This class contains all that has to do with the database. Creating database, creating tables, queries
- * rows, columns and so on.
- * Borrowed some code from Oivind Kolloen (Skoleeksempler --> Databases)
- * @author
- *
- */
- public class DatabaseTableModel extends AbstractTableModel {
- private static Logger logger = Logger.getLogger(Globals.LOG_NAME);
-
- private final static String url = "jdbc:derby:FiskDb;create=true";
- private Connection con;
- private Statement stmt;
- private ResultSet res;
- private ResultSetMetaData resMeta;
- private DatabaseMetaData meta;
- private int numRows;
- private boolean connectedToDatabase = false;
- private static final long serialVersionUID = 1L;
- /**
- * Constructor of class DatabaseTableModel. This constructor connects to the database.
- * @throws SQLException
- */
- public DatabaseTableModel() throws SQLException {
- try{
- con = DriverManager.getConnection(url);
- stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
- connectedToDatabase = true;
- meta = con.getMetaData();
- res = meta.getTables(null, "APP", "PLAYER", null);
- }catch (SQLException e) {
- logger.throwing(this.getClass().getName(), "DatabaseTableModel", e);
- }
- /**
- * Checks if table exists, if it doesn't create table
- */
- if(!res.next()) {
- createPlayerTable();
- createChatTable();
- }
- }
-
- /**
- * Method that creates the player table. Has username as a primary key
- * @see java.sql.Connection#createStatement()
- * @see java.sql.Statement#execute(String)
- * As https://bitbucket.org/okolloen/imt3281-project2-2017/wiki/Home notes that a user should be
- * able to change its username, we can not use it as a PrimaryKey - So we will use ID.
- */
- private void createPlayerTable() {
- //stmt represents the SQL statement. if connection succesful run the table query
- try (Statement stmtt = con.createStatement()){
- stmtt.execute("CREATE TABLE player ("
- + "id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
- + "username varchar(128) NOT NULL, "
- + "hashpassword varchar(328) NOT NULL, "
- + "token varchar(400), "
- + "bio varchar(328), "
- + "profilePicture blob,"
- + "gamesPlayed int DEFAULT 0,"
- + "gamesWon int DEFAULT 0,"
- + "PRIMARY KEY (id))");
- } catch (SQLException e) {
- logger.throwing(this.getClass().getName(), "createPlayerTable", e);
- }
- }
- /**
- * Method that creates the chat table. Chat table has id as its primary key
- * @see java.sql.Connection#createStatement()
- * @see java.sql.Statement#execute(String)
- */
- private void createChatTable() {
- try (Statement stmtt = con.createStatement()){
- stmtt.execute("CREATE TABLE chat ("
- + "id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
- + "username varchar(128) NOT NULL, "
- + "message TEXT NOT NULL, "
- + "groupId INT NOT NULL,"
- + "PRIMARY KEY (id))");
- } catch (SQLException e) {
- logger.throwing(this.getClass().getName(), "createChatTable", e);
- }
- }
- /**
- * Method to close the connection to the database
- */
- public void disconnectFromDatabase () {
- if (connectedToDatabase) {
- try {
- res.close();
- stmt.close();
- con.close();
- } catch (SQLException sqle) {
- logger.throwing(this.getClass().getName(), "disconnectFromDatabase", sqle);
- } finally {
- connectedToDatabase = false;
- }
- }
- }
- /**
- * Method gotten from AbstractTableModel which DatabaseTableModel extends from
- */
- @Override
- public int getColumnCount() throws IllegalStateException {
- if (!connectedToDatabase)
- throw new IllegalStateException("Unable to connect to database");
- try {
- return resMeta.getColumnCount();
- } catch (SQLException sqle) {
- logger.throwing(this.getClass().getName(), "getColumnCount", sqle);
- }
- return 0;
- }
- /**
- * Method gotten from AbstractTableModel which DatabaseTableModel extends from
- */
- @Override
- public String getColumnName(int column) throws IllegalStateException {
- if (!connectedToDatabase)
- throw new IllegalStateException("Unable to connect to database");
- try {
- return resMeta.getColumnName(column+1);
- } catch (SQLException sqle) {
- logger.throwing(this.getClass().getName(), "getColumnName", sqle);
- }
- return "";
- }
- /**
- * Method gotten from AbstractTableModel which DatabaseTableModel extends from
- */
- @Override
- public int getRowCount() {
- if (!connectedToDatabase)
- throw new IllegalStateException("Unable to connect to database");
- return numRows;
- }
- /**
- * Method gotten from AbstractTableModel which DatabaseTableModel extends from
- */
- @Override
- public Object getValueAt(int rowIndex, int columnIndex) {
- if (!connectedToDatabase)
- throw new IllegalStateException("Unable to connect to database");
- try {
- res.absolute(rowIndex + 1);
- return res.getObject(columnIndex + 1);
- } catch (SQLException sqle) {
- logger.throwing(this.getClass().getName(), "getValueAt", sqle);
- }
- return "";
- }
- /**
- * Method gotten from AbstractTableModel which DatabaseTableModel extends from
- */
- @Override
- public boolean isCellEditable(int rowIndex, int columnIndex) {
- return (columnIndex>0);
- }
- /**
- * Method gotten from AbstractTableModel which DatabaseTableModel extends from
- */
- @Override
- public void setValueAt(Object val, int row, int col) {
- if (!connectedToDatabase)
- throw new IllegalStateException("Unable to connect to database");
- try {
- // Move to the right row
- res.absolute(row+1);
- // Update the value in the given column
- res.updateObject(col+1, val);
- // Fire the update back at the database
- // NOTE, can be buffered, important to close the result set/connection
- // correctly to ensure that the database gets updated.
- res.updateRow();
- // Let the table know the model has changed
- fireTableCellUpdated(row, col);
- } catch (SQLException sqle) {
- logger.throwing(this.getClass().getName(), "setValueAt", sqle);
- }
- }
- /**
- * Method to add user to the database. It takes the username and password written by the user in the application
- * and sends it to a method to set the strings together and send it with a prepareStatement for security.
- * @param username written by user
- * @param password written by user
- */
- public void addUser(String username, String password, String token) {
- String sql = "INSERT INTO player (username, hashpassword, token) VALUES(?, ?, ?)";
- execute(sql, username, HashAndSalt.hashPassword(password), token);
- }
- /**
- * Builds the string to be sent to the databse with a PrepareStatement for max security against SQL injection.
- * @param sql the SQL string to be executed
- * @param arg1 depending on what is sent, but is the username if the user is trying to log in or register
- * @param arg2 depending on what is sent, but is the password if the user is trying to log in or register
- * @return
- */
- private void execute(String sql, String arg1, String arg2, String arg3) {
- try (PreparedStatement pstmt = con.prepareStatement(sql)) {
- pstmt.setString(1, arg1);
- if (arg2 != null)
- pstmt.setString(2, arg2);
- if (arg3 != null)
- pstmt.setString(3, arg3);
- pstmt.execute();
- } catch(SQLException sqle) {
- logger.throwing(this.getClass().getName(), "execute", sqle);
- }
- }
- /**
- * Method to get the token from the database based on the username sent. This method
- * is used with the autologin method which the client reads token of a registered user from file
- * and sends to database for validation.
- * @param username
- * @return token based on the username given
- * @throws SQLException
- */
- public String getToken(String username) throws SQLException {
- PreparedStatement prepStatement = null;
- ResultSet reSet = null;
- String query = "select token from player where username = ?";
- try {
- prepStatement = con.prepareStatement(query);
- prepStatement.setString(1, username);
-
- reSet = prepStatement.executeQuery();
- if(reSet.next()) {
- String tok = reSet.getString("TOKEN");
- return tok;
- } else {
- String tok = "TOKENFAILURE";
- return tok;
- }
- } catch (Exception e) {
- logger.throwing(this.getClass().getName(), "getToken", e);
- String tok = "TOKENFAILURE";
- return tok;
- } finally {
- if(prepStatement != null){
- prepStatement.close();
- reSet.close();
- }
- }
- }
- /**
- * Method to autologin a user that have chosen the option "Remember me". It collects the username and hashed
- * password from the database from the token given.
- * @param token
- * @return
- * @throws SQLException
- */
- public String[] autoLogin(String token) throws SQLException {
- PreparedStatement prepStatement = null;
- ResultSet reSet = null;
- String query = "select username, hashpassword, id from player where token = ?";
- try {
- prepStatement = con.prepareStatement(query);
- prepStatement.setString(1, token);
-
- reSet = prepStatement.executeQuery();
- if(reSet.next()) {
- String user = reSet.getString("USERNAME");
- String pw = reSet.getString("HASHPASSWORD");
- int id = reSet.getInt("ID");
- String strId = Integer.toString(id);
- String[] arr = new String[3];
- arr[0] = user;
- arr[1] = pw;
- arr[2] = strId;
- return arr;
- }
- } catch (Exception e){
- logger.throwing(this.getClass().getName(), "autoLogin", e);
- } finally {
- if(prepStatement != null){
- prepStatement.close();
- reSet.close();
- }
- }
- return null;
- }
- /**
- * Method to check if the user can log in with the username and password he/she has written.
- * This method sends the password given in parameter and the hashed password from the database
- * to the validatePassword method to decrypt the password for validation.
- * @see no.ntnu.imt3281.ludo.server.HashAndSalt#validatePassword(String, String)
- * @param user
- * @param password
- * @return
- * @throws SQLException
- */
- public boolean isLoggedIn(String user, String password) throws SQLException {
- PreparedStatement preparedStatement = null;
- ResultSet resultSet = null;
- String query = "select * from player where username = ?";
- try {
- preparedStatement = con.prepareStatement(query);
- preparedStatement.setString(1, user);
- resultSet = preparedStatement.executeQuery();
- if (resultSet.next()) {
- String pass = resultSet.getString("HASHPASSWORD");
- if(HashAndSalt.validatePassword(password,pass))
- return true;
- return false;
- } else {
- return false;
- }
- } catch (Exception e) {
- logger.throwing(this.getClass().getName(), "isLoggedIn", e);
- return false;
- } finally {
- if(preparedStatement != null) {
- preparedStatement.close();
- resultSet.close();
- }
- }
- }
- /**
- * Method to check if the username is taken in the database when adding a new user.
- * @param username the username to be checked
- * @return true if taken, false if not
- * @throws SQLException
- */
- public boolean isUserTaken(String username) {
- PreparedStatement preparedStatement = null;
- ResultSet resultSet = null;
- String query = "select username from player where username = ?";
- try {
- preparedStatement = con.prepareStatement(query);
- preparedStatement.setString(1, username);
-
- resultSet = preparedStatement.executeQuery();
- while (resultSet.next()) {
- if (resultSet.getString(1).equals(username))
- return true;
- }
- } catch (SQLException e) {
- logger.throwing(this.getClass().getName(), "isUserTaken", e);
- } finally {
- try {
- if(preparedStatement != null) {
- preparedStatement.close();
- resultSet.close();
- }
- } catch (SQLException e) {
- logger.throwing(this.getClass().getName(), "isUserTaken", e);
- }
- }
- return false;
- }
- /**
- * Gets Info about a user, given a username. Can be used in edit profile, and also in view profile if we add that.
- * @param name
- * @return a hashmap containing the things we want
- */
- public HashMap<String,Object> getInfo(String name) {
- HashMap<String,Object> map = new HashMap<>();
- PreparedStatement preparedStatement = null;
- ResultSet resultSet = null;
- String query = "select id,username,bio,profilePicture from player where username = ?";
- try {
- preparedStatement = con.prepareStatement(query);
- preparedStatement.setString(1, name);
- resultSet = preparedStatement.executeQuery();
- while (resultSet.next()) {
- // Get the info we want.
- map.put("USERNAME",resultSet.getString("USERNAME") );
- map.put("BIO",resultSet.getString("BIO") );
- map.put("IMAGE",resultSet.getBytes("profilePicture"));
- map.put("ID",resultSet.getInt("id"));
- return map;
- }
- } catch (SQLException e) {
- logger.throwing(this.getClass().getName(), "getInfo", e);
- } finally {
- try {
- if(preparedStatement != null) {
- preparedStatement.close();
- resultSet.close();
- }
- } catch (SQLException e) {
- logger.throwing(this.getClass().getName(), "getInfo", e);
- }
- }
- return null;
- }
- /**
- * Method to update the users profile picture. Method uses hashmap to collect the sent data
- * from client, so that it knows what user to update.
- * @param map hashmap from clientside filled by user
- */
- public void updateUserPicture(HashMap<String, Object> map){
- String query = "update player set profilePicture = ? where id = ?";
- PreparedStatement preparedStatement = null;
- try {
- preparedStatement = con.prepareStatement(query);
- preparedStatement.setBytes(1, (byte[]) map.get("IMAGE"));
- preparedStatement.setInt(2, (int) map.get("ID"));
- if(preparedStatement.execute())
- System.out.println("Succeded. We updated the picture");
- }
- catch (Exception e){
- logger.throwing(this.getClass().getName(), "updateUserPicture", e);
- } finally {
- try {
- if(preparedStatement != null) {
- preparedStatement.close();
- }
- } catch (SQLException e) {
- logger.throwing(this.getClass().getName(), "updateUserPicture", e);
- }
- }
- }
- /**
- * Method to edit the user profile of the user. It collects the data from the hashmap sent by the
- * client.
- * @param map hashmap from client containing username, bio, password
- */
- public void editUserInfo(HashMap<String, Object> map) {
- //update survey set name='newName' where id = 111
- String query = "update player set username = ?,bio = ? where username = ?";
- if(map.get("PASSWORD") != null){
- // Before comming this far, we should have checked if password and Password2 is the same
- query = "update player set username = ?,bio = ?, HASHPASSWORD = ? where username = ?";
- }
- PreparedStatement preparedStatement = null;
- try {
- preparedStatement = con.prepareStatement(query);
- preparedStatement.setString(1, (String) map.get("USERNAME"));
- preparedStatement.setString(2, (String) map.get("BIO"));
- if(map.get("PASSWORD") != null) {
- String pass = HashAndSalt.hashPassword((String) map.get("PASSWORD"));
- preparedStatement.setString(3, pass);
- preparedStatement.setString(4, (String) map.get("USERNAME"));
- }else {
- preparedStatement.setString(3, (String) map.get("USERNAME"));
- }
- if(preparedStatement.execute())
- System.out.println("Succeded. We updated the userInfo");
- } catch (SQLException e) {
- logger.throwing(this.getClass().getName(), "editUserInfo", e);
- } finally {
- try {
- if(preparedStatement != null) {
- preparedStatement.close();
- }
- } catch (SQLException e) {
- logger.throwing(this.getClass().getName(), "editUserInfo", e);
- }
- }
- }
-
- /**
- * Adding a username and chat message to chat table
- * @param username of the player sending the chat message
- * @param message that was sent to the chat
- */
- public void logChatMessages(String username, String message, Integer groupId) {
- String sql = "INSERT INTO chat (username, message, groupId) VALUE(?, ?, ?)";
- excecuteLog(sql, username, message, groupId);
-
- }
- /**
- * Execute log query
- * @param sql query
- * @param username argument
- * @param message argument
- * @param groupId argument
- * @return resultset or null
- */
- private void excecuteLog(String sql, String username, String message, Integer groupId) {
- try (PreparedStatement stmtt = con.prepareStatement(sql)) {
- stmtt.setString(1, username);
- if (message != null)
- stmtt.setString(2, message);
- if (groupId != null)
- stmtt.setInt(3, groupId);
- stmtt.execute();
- } catch(SQLException e) {
- logger.throwing(this.getClass().getName(), "excecuteLog", e);
- }
- }
- /**
- * Update a players games won score
- * @param username of player who won a game
- */
- public void incrementGamesWon(String username) {
- String query = "update player set gamesWon = gamesWon + 1 where username = ?";
- executeUpdateGames(query, username);
- }
-
- /**
- * Update a players games played score
- * @param username of player who finished a game
- */
- public void incrementGamesPlayed(String username) {
- String query = "update player set gamesPlayed = gamesPlayed + 1 where username = ?";
- executeUpdateGames(query, username);
- }
- /**
- * Executes an sql query of updating game score
- * @param query which to be sent
- * @param username of player to add score
- * @return result of query
- */
- private void executeUpdateGames(String query, String username) {
- try(PreparedStatement stmtt = con.prepareStatement(query)) {
- stmtt.setString(1, username);
- stmtt.execute();
- } catch (SQLException e) {
- logger.throwing(this.getClass().getName(), "executeUpdateGames", e);
- }
- }
- /**
- * Gets a ranking list of most games won
- * @return top ten list of most games won
- */
- public ArrayList<String> getWonRanking() {
- ArrayList<String> list = new ArrayList<String>();
- PreparedStatement preparedStatement = null;
- ResultSet resultSet = null;
- String tmp;
-
- String query = "select username,gamesWon from player order by gamesWon DESC fetch first 10 rows only";
- try {
- preparedStatement = con.prepareStatement(query);
- resultSet = preparedStatement.executeQuery();
-
- while (resultSet.next()) {
- tmp = String.format("%-20s %10s",resultSet.getString("USERNAME"), I18N.getBundle().getString("ranking.winnersWon") + resultSet.getString("gamesWon"));
- list.add(tmp);
- }
- } catch (SQLException e) {
- logger.throwing(this.getClass().getName(), "getWonRanking", e);
- } finally {
- try {
- if(preparedStatement != null) {
- preparedStatement.close();
- resultSet.close();
- }
- } catch (SQLException e) {
- logger.throwing(this.getClass().getName(), "getWonRanking", e);
- }
- }
- return list;
- }
- /**
- * Gets a ranking list of most played games
- * @return top ten list of games played
- */
- public ArrayList<String> getPlayedRanking() {
- ArrayList<String> list = new ArrayList<String>();
- PreparedStatement preparedStatement = null;
- ResultSet resultSet = null;
- String tmp;
-
- String query = "select username,gamesPlayed from player order by gamesPlayed DESC FETCH FIRST 10 ROWS ONLY";
- try {
- preparedStatement = con.prepareStatement(query);
- resultSet = preparedStatement.executeQuery();
- while (resultSet.next()) {
- tmp = String.format("%-20s %10s",resultSet.getString("USERNAME"),I18N.getBundle().getString("ranking.winnersPlayed") + resultSet.getString("gamesPlayed"));
- list.add(tmp);
- }
- } catch (SQLException e) {
- logger.throwing(this.getClass().getName(), "getPlayedRanking", e);
- } finally {
- try {
- if(preparedStatement != null) {
- preparedStatement.close();
- resultSet.close();
- }
- } catch (SQLException e) {
- logger.throwing(this.getClass().getName(), "getPlayedRanking", e);
- }
- }
- return list;
- }
- }