java - 打开和关闭数据库

Question

我正在开发一个应用程序来记录我服务器上的 MySql 数据库。每次要用到数据库，就获取已有的连接，如果没有，我第一时间想到。当我进行插入或选择时，效果很好，但在咨询之后，当它结束时，我永远无法重新获得连接，也不会返回咨询。

我的数据库类

public class Database {
/**
 * Gets just one instance of the class
 * Connects on construct
 * @returns connection
 */
private Connection _conn = null;
private long timer;

//singleton code
private static Database DatabaseObject;
private Database() {}
public static Database connect() {
    if (DatabaseObject == null)
        DatabaseObject = new Database();
    return DatabaseObject._connect();
}
public Object clone() throws CloneNotSupportedException {
    throw new CloneNotSupportedException();
}
//end singleton code


/**
 * Connects with the defined parameters on Config
 * Prevents re-connection if object was already connected
 * @throws SQLException
 */
private Database _connect() {
    try {
    if (this._conn == null || !this._conn.isValid(0)) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Properties connProps = new Properties();
            connProps.put("user", Config.Config.DB_USER);
            connProps.put("password", Config.Config.DB_PASS);
            this._conn = DriverManager.
                        getConnection("jdbc:" + Config.Config.DB_DBMS + "://" + Config.Config.DB_HOST + ":"
                                + Config.Config.DB_PORT + "/" + Config.Config.DB_NAME, Config.Config.DB_USER, Config.Config.DB_PASS);
            timer = System.currentTimeMillis();
        } catch (ClassNotFoundException e) {
            System.out.println("Where is your MySQL JDBC Driver?");
            e.printStackTrace();
        } catch (Exception e) {
            System.out.println("Could not connect to DB");
            e.printStackTrace();
        }
    } else {
        try {
            long tmp = System.currentTimeMillis() - timer;
            if (tmp > 1200000) { //3600000 one hour ; 1200000 twenty minutes
                System.out.println("Forcing reconnection ("+tmp+" milliseconds passed since last connection)");
                this.close();
                this._connect();
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Forcing reconnection");
            this._conn = null;
            this._connect();
        }
    }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return this;
}

/**
 * Closes connections
 * This has to be invoked when database connection is no longer needed
 * @throws SQLException
 */
public void close() throws SQLException {
    if (this._conn != null) {
        this._conn.close();
        this._conn = null;
    }
}

   /**
    * Getter for connection
    * @return
    */
   public Connection get() {
      return this._conn;
   }
}

我查询的函数如下:

private Statement sment = null;
private PreparedStatement psment = null;
private ResultSet rset = null;
public boolean existsByNameAndUserId(String md5, int userId, int eventId) {
    Connection conn = Database.connect().get();
    try {
        psment = conn.prepareStatement("SELECT * FROM files "
                                        + "WHERE user_id = ? AND md5 = ? AND evento_id = ?");
        psment.setInt(1, userId);
        psment.setString(2, md5);
        psment.setInt(3, eventId);
        rset = psment.executeQuery();

        if (rset.next()) {
            return true;
        }
    } catch (Exception e) {

        e.printStackTrace();
    }

    return false;
}

private void close() {
    try { if (rset != null) rset.close(); } catch (Exception e) {System.out.println(e.getMessage());};
    try { if (psment != null) psment.close(); } catch (Exception e) {System.out.println(e.getMessage());};
    try { if (sment != null) sment.close(); } catch (Exception e) {System.out.println(e.getMessage());};
}

接下来，我调用上面的函数来判断一条记录是否具有这些特征，如果没有，我就插入。

String SQL_INSERT = "INSERT INTO files (evento_id, user_id, path, thumb, preview, width, height, md5, numero_corredor, created, modified) "
        + "VALUES (?,?,?,?,?,?,?,?,?,NOW(),NOW())";
public void save(List<components.File.Schema> files) throws SQLException {
    try (
            Connection conn = Database.connect().get();
            PreparedStatement statement = conn.prepareStatement(SQL_INSERT);
        ) {
            int i = 0;

            for (components.File.Schema file : files) {
                if(!existsByNameAndUserId(file.getMd5(), file.getUserId(), file.getEventId())){
                    statement.setInt(1, file.getEventId());
                    statement.setInt(2, file.getUserId());
                    statement.setString(3, file.getPath());
                    statement.setString(4, file.getPreview());
                    statement.setString(5, file.getThumb());

                    statement.setInt(6, file.getWidth());
                    statement.setInt(7, file.getHeight());
                    statement.setString(8, file.getMd5());
                    statement.setString(9, null);
                    statement.addBatch();
                    i++;
                    if (i % 1000 == 0 || i == files.size()) {
                        statement.executeBatch(); // Execute every 1000 items.
                    }
                }
            }
       }
}

Answer 1

您的问题是由于您将 Connection conn = Database.connect().get() 放在了 try-with-resources 语句中你应该这样做，但它会关闭你的连接，当你再次调用它时，因为方法 _connect() 没有有效的测试，它不会创建新的连接。有效测试是 this._conn == null || !this._conn.isValid(0)，实际上在您的原始测试中您调用了 this._conn.isValid(0)，它将在我们的上下文中返回 false由于连接已关闭，因此它不会创建新连接，这不是我们想要的。

响应更新:问题的第二部分是在保存方法中调用existsByNameAndUserId 关闭当前连接，您应该只关闭语句并让方法保存关闭连接。