PrepareStatement/Commit序列在mysql/HikariCP Webapp中不起作用

问题描述

我有一个使用MySQL和HikariCP进行连接池的Web应用程序.我使用如下定义的单例连接池对象连接到我的池:

I have a web application that uses MySQL and HikariCP for the connection pooling. I connect to my pool using a singleton connection pool object defined like so:

package com.webapp.db;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.log4j.Logger;

public class HikariConnectionPool
{
    private static final Logger log = Logger.getLogger(HikariConnectionPool.class.getName());
    private static volatile HikariConnectionPool hikariCP = null;
    private static HikariDataSource hikariDataSource = null;

    private HikariConnectionPool() // We don't want any other object creating this pool
    {
        HikariConfig config = new HikariConfig();
        config.addDataSourceProperty("url","jdbc:mysql://remoteMYSQLServer:3306/webapp");
        config.addDataSourceProperty("user", "webapp");
        config.addDataSourceProperty("password", "password");
        config.setMinimumIdle(1);
        config.setMaximumPoolSize(2);
        config.setInitializationFailFast(true);
        config.setConnectionTestQuery("VALUES 1");
        config.setJdbcUrl("jdbc:mysql://remoteMYSQLServer:3306/webapp");
        config.setDriverClassName ("com.mysql.jdbc.Driver");

         hikariDataSource = new HikariDataSource (config);
   }

   public static HikariConnectionPool getInstance()
   {
    if (hikariCP == null)
        {
        synchronized (HikariConnectionPool.class)
            {
            if (hikariCP == null)
                {
                hikariCP = new HikariConnectionPool ();
                }
            }
        }
    return hikariCP;
    }

    public static HikariDataSource getDataSource ()
    {
    hikariCP = getInstance ();
    return hikariDataSource;
    }
}

在我的应用程序中，我使用以下代码来获取数据源:

In my application, I use the following code to get a data source:

HikariDataSource ds = HikariConnectionPool.getDataSource ();

，然后尝试使用插入到数据库中

and then try to insert into the database using

try {
Connection connection = ds.getConnection ();
String sString = "insert into webapp.sometable (?, ?, ?, ?, ?);";
PreparedStatement statement = connection.prepareStatement (sString);
statement.setString (1, fname);
statement.setString (2, sname);
statement.setString (3, email);
statement.setString (4, password);
statement.setString (5, phone);

statement.executeUpdate();
connection.commit ();
connection.close ();
} catch (SQLException e) { e.printStackTrace(); }

但是我看不到提交给数据库的值.我究竟做错了什么?任何帮助深表感谢.

but I do not see the values committed to the database. What am I doing wrong? Any help is deeply appreciated.

推荐答案

具有双重检查锁定的getInstance()不正确，可能会导致争用情况.有关正确的模式，请参见Wikipedia上的双重检查锁定" .

Your getInstance() with the double-checked locking is incorrect and could cause a race condition. See "Double-checked locking" on Wikipedia for a correct pattern.

您的SQL语句不需要以分号结尾.而且它可能不需要使用模式"webapp"进行限定.因为您已明确连接到webapp数据库.

Your SQL statement does not need to end with a semicolon. And it probably does not need to be qualified with the schema "webapp." because you are explicitly connecting to the webapp database.

此外，我将跳过构建数据源的驱动程序管理器版本，并采用以下模式:

Also, I'd skip the Driver Manager version of constructing a datasource and go with the following pattern:

HikariConfig config = new HikariConfig();
config.setMinimumIdle(1);
config.setMaximumPoolSize(2);
config.setInitializationFailFast(true);
config.setConnectionTestQuery("VALUES 1");
config.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
config.addDataSourceProperty("serverName", "localhost");
config.addDataSourceProperty("port", "3306");
config.addDataSourceProperty("databaseName", "webapp");
config.addDataSourceProperty("user", "webapp");
config.addDataSourceProperty("password", "password");

这篇关于PrepareStatement/Commit序列在mysql/HikariCP Webapp中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！