java - PreparedStatement.executeUpdate() 不插入 sqlite 数据库

Question

我正在尝试为我的 Java 项目创建一个 DAO 类。我有一个只有一个表“USER”的 SQLite 数据库。架构是:

    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");

我的 UserDAOImpl 类有 findAll()、findByNickname(String nickname)、insert、update、delete 方法。

我使用 PreparedStatement 来防止 SQL 注入(inject)。

我的问题是，如果我调用我的 insert(User toInsert) 方法然后循环遍历 findAll() 结果，我可以看到正确的插入。

但是如果我转到终端并使用 SQLite 命令行打开数据库，当我执行“SELECT * FROM USER”时，之前的插入不会显示。

数据库类:

/**
 * 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;
    }

DAO 类是:

/**
 * 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;
    }
}

我的测试主要方法是:

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());
        }
    }

感谢您的宝贵时间。

编辑:当我切割数据库并将其放入另一个目录时，退出eclipse，将数据库移回他以前的目录并重新打开eclipse，然后Java 之前所做的所有更改都丢失了。 –

Answer 1

已解决:在 Maven 项目中，所有资源都在“构建”命令后复制到另一个目录中。我读错了数据库。