CODEWITHSUNDEEP
javadatabasesqlite
Davide Cremona
Davide Cremona

Reputation: 121

PreparedStatement.executeUpdate() doesn't insert in sqlite database

i'm trying to make a DAO class for my Java project. I've a SQLite Database with only one table "USER". The schema is:

    CREATE TABLE USER(
username VARCHAR(20) NOT NULL PRIMARY KEY,
password VARCHAR NOT NULL,
total_matches INTEGER DEFAULT 0,
victories INTEGER DEFAULT 0,
times_alien INTEGER DEFAULT 0,
times_human INTEGER DEFAULT 0,
total_steps INTEGER DEFAULT 0,
humans_killed INTEGER DEFAULT 0,
aliens_killed INTEGER DEFAULT 0,
kills_as_human INTEGER DEFAULT 0,
kills_as_alien INTEGER DEFAULT 0,
total_escapes INTEGER DEFAULT 0,
total_broken_hatches INTEGER DEFAULT 0,
total_noises INTEGER DEFAULT 0,
online_status VARCHAR(5) NOT NULL DEFAULT "false");

My UserDAOImpl class has findAll(), findByNickname(String nickname), insert, update, delete methods.

I use PreparedStatementto prevent SQL Injection.

My Issue is that if I call my insert(User toInsert) method and then cycle through the findAll() result, I can see the right insertion.

But if I go to the Terminal and open the DB with the SQLite command line, when I execute "SELECT * FROM USER", the previous insertion doesn't shows.

The DB Class:

/**
 * The Class DB.
 * Gives a connection to the game Database.
 */
public class DB {

/**  the singleton instance of the Database. */
    private static DB instance = new DB();

    /**  the path to the database. */
    private static final String DBPath = "db/eftaios_DB.db";



    /**
     * Istantiates a new Database.
     */
    private DB(){
        /*
         * 
         */
    }

    /**
     * Create a connection between this class and the database files.
     *
     * @return the database connection.
     * @throws SQLException the SQL exception
     */
    public Connection connect() throws SQLException{
        Connection dbConnection = null;
        try {

            Class.forName("org.sqlite.JDBC");
            String dbPath = DB.class.getClassLoader().getResource(DBPath).getPath();
            dbConnection = DriverManager.getConnection("jdbc:sqlite:"+dbPath);

        } catch (ClassNotFoundException e) {
            /*
             * do nothing, the class is imported in the maven dependencies
             */
        } catch (SQLException e) {
            throw new SQLException();
        }
        return dbConnection;
    }

The DAO Class is:

/**
 * The class UserDAOImpl implements the UserDAOInterface
 * It implements a DAO (Data Access Object) for the User table.
 * It gives access to the User table on the Database.
 * With this class you can perform queries like find, insert, delete and update on the USER table.
 */
public class UserDAOImpl implements UserDAOInterface {

    /**  the database connection used to query it. */
    private Connection dbConnection;

    /**  the result of a query to the database. */
    private ResultSet queryResult;

    /**  the statement to execute to perform db queries. */
    private Statement queryStatement;

    /**  the prepared statement to execute mysql injection secure queryes. */
    private PreparedStatement queryPreparedStatement;

    /**  the name of the database user's table. */
    private static final String USER_TABLE = "USER";

    /**
     * To user list.
     *
     * @param qryResult the qry result
     * @return the list
     * @throws SQLException the SQL exception
     */
    private List<User> toUserList(ResultSet qryResult) throws SQLException{
        List<User> result = new ArrayList<User>();
        /* forall user in result, populate the new user and add it to the users list */
        while(qryResult.next()){
            User record = new User();
            record.setNickname(qryResult.getString(User.NICKNAME_COL_NAME));
            record.setPassword(qryResult.getString(User.PASSWORD_COL_NAME));
            record.setAliensKilled(qryResult.getInt(User.ALIENS_KILLED_COL_NAME));
            record.setHumansKilled(qryResult.getInt(User.HUMANS_KILLED_COL_NAME));
            record.setKillsAsAlien(qryResult.getInt(User.KILLS_AS_ALIEN_COL_NAME));
            record.setKillsAsHuman(qryResult.getInt(User.KILLS_AS_HUMAN_COL_NAME));
            record.setOnlineStatus(qryResult.getBoolean(User.ONLINE_STATUS_COL_NAME));
            record.setTimesAlien(qryResult.getInt(User.TIMES_ALIEN_COL_NAME));
            record.setTimesHuman(qryResult.getInt(User.TIMES_HUMAN_COL_NAME));
            record.setTotalBrokenHatches(qryResult.getInt(User.TOTAL_BROKEN_HATCHES_COL_NAME));
            record.setTotalEscapes(qryResult.getInt(User.TOTAL_ESCAPES_COL_NAME));
            record.setTotalMatches(qryResult.getInt(User.TOTAL_MATCHES_COL_NAME));
            record.setTotalNoises(qryResult.getInt(User.TOTAL_NOISES_COL_NAME));
            record.setTotalSteps(qryResult.getInt(User.TOTAL_STEPS_COL_NAME));
            record.setVictories(qryResult.getInt(User.VICTORIES_COL_NAME));
            result.add(record);
        }
        return result;
    }

    /*
     * (non-Javadoc)
     * @see it.polimi.ingsw.deolacremona.server.model.database.UserDAOInterface#findAll()
     */
    @Override
    public List<User> findAll() throws SQLException {

        String findAllQuery = "SELECT * FROM "+USER_TABLE;

        List<User> users = new ArrayList<User>();
        this.dbConnection   = DB.getDatabase().connect();
        this.dbConnection.setAutoCommit(false);
        this.queryStatement = this.dbConnection.createStatement();
        this.queryResult    = this.queryStatement.executeQuery(findAllQuery);

        users = this.toUserList(queryResult);

        this.dbConnection.commit();
        this.queryResult.close();
        this.queryStatement.close();
        this.dbConnection.close();
        return users;
    }

    /*
     * (non-Javadoc)
     * @see it.polimi.ingsw.deolacremona.server.model.database.UserDAOInterface#findByNickname(java.lang.String)
     */
    @Override
    public List<User> findByNickname(String userNickname) throws SQLException {

        String findByNicknameQuery = "SELECT * FROM "+USER_TABLE+" WHERE "+User.NICKNAME_COL_NAME+"=?";

        List<User> users = new ArrayList<User>();
        this.dbConnection = DB.getDatabase().connect();
        this.dbConnection.setAutoCommit(false);

        /* preparing the statement to prevent sql injection */
        this.queryPreparedStatement = this.dbConnection.prepareStatement(findByNicknameQuery);
        this.queryPreparedStatement.setString(1, userNickname);

        /* now get the result */
        this.queryResult = this.queryPreparedStatement.executeQuery();

        users = this.toUserList(queryResult);

        this.dbConnection.commit();
        this.queryPreparedStatement.close();
        this.queryResult.close();
        this.dbConnection.close();

        return users;
    }

    /*
     * (non-Javadoc)
     * @see it.polimi.ingsw.deolacremona.server.model.database.UserDAOInterface#insert(it.polimi.ingsw.deolacremona.server.model.database.User)
     */
    @Override
    public boolean insert(User toInsert) throws SQLException {

            boolean result = false;
            MD5Hasher hasher = new MD5Hasher();
            String md5Password = hasher.md5(toInsert.getPassword());

            String insertQuery = 

                    "INSERT INTO "+USER_TABLE+" ("+User.NICKNAME_COL_NAME+","+User.PASSWORD_COL_NAME+") VALUES (?,?)";

            this.dbConnection = DB.getDatabase().connect();
            this.dbConnection.setAutoCommit(false);

            /* preparing the statement to prevent sql injection */
            this.queryPreparedStatement = this.dbConnection.prepareStatement(insertQuery);
            this.queryPreparedStatement.setString(1, toInsert.getNickname());
            this.queryPreparedStatement.setString(2, md5Password);

            if(this.queryPreparedStatement.executeUpdate()==1)
                result = true;



            this.queryPreparedStatement.close();
            this.dbConnection.commit();
            this.dbConnection.close();

            return result;
    }

    /*
     * (non-Javadoc)
     * @see it.polimi.ingsw.deolacremona.server.model.database.UserDAOInterface#update(it.polimi.ingsw.deolacremona.server.model.database.User)
     */
    @Override
    public boolean update(User toUpdate) throws SQLException {
        boolean result = false;

        String updateQuery = "UPDATE "+USER_TABLE+" SET "
                + User.ALIENS_KILLED_COL_NAME        +"=?,"
                + User.HUMANS_KILLED_COL_NAME        +"=?,"
                + User.KILLS_AS_ALIEN_COL_NAME       +"=?,"
                + User.KILLS_AS_HUMAN_COL_NAME       +"=?,"
                + User.ONLINE_STATUS_COL_NAME        +"=?,"
                + User.TIMES_ALIEN_COL_NAME          +"=?,"
                + User.TIMES_HUMAN_COL_NAME          +"=?,"
                + User.TOTAL_BROKEN_HATCHES_COL_NAME +"=?,"
                + User.TOTAL_ESCAPES_COL_NAME        +"=?,"
                + User.TOTAL_MATCHES_COL_NAME        +"=?,"
                + User.TOTAL_NOISES_COL_NAME         +"=?,"
                + User.TOTAL_STEPS_COL_NAME          +"=?,"
                + User.VICTORIES_COL_NAME            +"=?"
                + " WHERE "+User.NICKNAME_COL_NAME+"=?";


        /* preparing the sql statement to prevent sql injection */
        this.dbConnection = DB.getDatabase().connect();
        this.dbConnection.setAutoCommit(false);
        this.queryPreparedStatement = this.dbConnection.prepareStatement(updateQuery);
        this.queryPreparedStatement.setInt    (1, toUpdate.getAliensKilled());
        this.queryPreparedStatement.setInt    (2, toUpdate.getHumansKilled());
        this.queryPreparedStatement.setInt    (3, toUpdate.getKillsAsAlien());
        this.queryPreparedStatement.setInt    (4, toUpdate.getKillsAsHuman());
        this.queryPreparedStatement.setBoolean(5, toUpdate.isOnlineStatus());
        this.queryPreparedStatement.setInt    (6, toUpdate.getTimesAlien());
        this.queryPreparedStatement.setInt    (7, toUpdate.getTimesHuman());
        this.queryPreparedStatement.setInt    (8, toUpdate.getTotalBrokenHatches());
        this.queryPreparedStatement.setInt    (9, toUpdate.getTotalEscapes());
        this.queryPreparedStatement.setInt    (10, toUpdate.getTotalMatches());
        this.queryPreparedStatement.setInt    (11, toUpdate.getTotalNoises());
        this.queryPreparedStatement.setInt    (12, toUpdate.getTotalSteps());
        this.queryPreparedStatement.setInt    (13, toUpdate.getVictories());
        this.queryPreparedStatement.setString (14, toUpdate.getNickname());

        if(this.queryPreparedStatement.executeUpdate()==1){
            result = true;
        }


        this.queryPreparedStatement.close();
        this.dbConnection.commit();
        this.dbConnection.close();
        return result;
    }


    /*
     * (non-Javadoc)
     * @see it.polimi.ingsw.deolacremona.server.model.database.UserDAOInterface#updateAdder(it.polimi.ingsw.deolacremona.server.model.database.User)
     */
    @Override
    public boolean updateAdder(User toUpdate) {
        // TODO Auto-generated method stub
        return false;
    }

    /*
     * (non-Javadoc)
     * @see it.polimi.ingsw.deolacremona.server.model.database.UserDAOInterface#delete(it.polimi.ingsw.deolacremona.server.model.database.User)
     */
    @Override
    public boolean delete(User toDelete) throws SQLException {

        boolean result = false;

        String deleteQuery = "DELETE FROM "+USER_TABLE+" WHERE username=?";

        this.dbConnection = DB.getDatabase().connect();
        this.dbConnection.setAutoCommit(false);

        this.queryPreparedStatement = this.dbConnection.prepareStatement(deleteQuery);
        this.queryPreparedStatement.setString(1, toDelete.getNickname());

        if(this.queryPreparedStatement.executeUpdate()==1){
            result = true;
        }

        this.queryPreparedStatement.close();
        this.dbConnection.commit();
        this.dbConnection.close();

        return result;
    }
}

My test main method is:

public static void main(String[] args) throws SQLException, UnknownHostException{
        DB database = DB.getDatabase();
        database.connect();

        MD5Hasher h = new MD5Hasher();

        UserDAOImpl d = new UserDAOImpl();

        User s = new User();
        s.setNickname("davide");
        s.setPassword("ciao");

        if(d.insert(s))
            System.out.println("insert");       
//      d.delete(s);

        for(User x : d.findAll()){
            System.out.println("Nickname: "+x.getNickname()+" password: "+x.getPassword()+" matches: "+x.getTotalMatches());
        }
    }

Thank you for your time.

EDIT: when I cut the database and put it into another directory, exit eclipse, move back the database in his previous directory and reopen eclipse, then all the changes that Java done previously are lost. –

Upvotes: 2

Views: 518

Answers (1)

Davide Cremona
Davide Cremona

Reputation: 121

SOLVED: In a Maven Project, all the resources are copied into another directory after the "build" command. I was reading the wrong db.

Upvotes: 1

Related Questions