ActiveJDBC有SQLSyntaxErrorException

Question

使用ActiveJDBC，我一直在手动打开和关闭连接，插入和读取任何内容，所有这些都没有出现以下错误：

public class DatabaseLoader {

    private static final Logger logger = LogManager.getLogger(DatabaseLoader.class);

    public static void openConnection() {
        Base.open("com.mysql.cj.jdbc.Driver", ConfigManager.getAddress(), ConfigManager.getUsername(), ConfigManager.getPassword());
    }

    public static void openConnectionIfClosed() {

        if (!Base.hasConnection()) {
            logger.info("Connecting to database!");
            openConnection();
        }
    }

    public static void closeConnectionIfOpen() {
        if (Base.hasConnection()) {
            logger.info("Disconnecting from database!");
            Base.close();
        }
    }
}

但是，我最近尝试添加C3P0和连接池，尽可能地紧跟https://github.com/javalite/javalite/blob/master/activejdbc/src/test/java/org/javalite/activejdbc/C3P0PoolTest.java。新代码如下所示：

public class DatabaseLoader {

    private static final Logger logger = LogManager.getLogger(DatabaseLoader.class);

    static DataSource dataSourceUnpooled;
    static DataSource dataSourcePooled;

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            dataSourceUnpooled = DataSources.unpooledDataSource(ConfigManager.getAddress(), ConfigManager.getUsername(), ConfigManager.getPassword());
            dataSourcePooled = DataSources.pooledDataSource(dataSourceUnpooled); //init the connection pool
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static void openConnection() {
        Base.open(dataSourcePooled);
    }

    public static void openConnectionIfClosed() {
        if (!Base.hasConnection()) {
            openConnection();
        }
    }

    public static void closeConnectionIfOpen() {
        if (Base.hasConnection()) {
            logger.info("Disconnecting from database!");
            Base.close();
        }
    }
}

使用此代码，与数据库的初始连接似乎成功，并且一切正常，直到我插入了什么。例如，如下所示：

// ServerMessage is an activejdbc model that has worked to insert things 31k times before adding C3P0

ServerMessage message = ServerMessage.findOrCreateIt("message_id_snowflake", messageIdSnowflake, "server_id", serverId, "user_id", userId);

每当我插入某项内容时，就会得到以下错误：

org.javalite.activejdbc.DBException: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '::BIGINT,19::INT,1772::INT)' at line 1, query: INSERT INTO server_messages (message_id_snowflake, server_id, user_id) VALUES (?::BIGINT,?::INT,?::INT), params: 928224895626793001, 19, 1772
        at org.javalite.activejdbc.DB.execInsert(DB.java:734)
        at org.javalite.activejdbc.Model.insert(Model.java:2741)
        at org.javalite.activejdbc.Model.save(Model.java:2673)
        at org.javalite.activejdbc.Model.saveIt(Model.java:2597)
        at org.javalite.activejdbc.ModelDelegate.createIt(ModelDelegate.java:146)
        at org.javalite.activejdbc.ModelDelegate.findOrCreateIt(ModelDelegate.java:438)
        at org.javalite.activejdbc.ModelDelegate.findOrCreateIt(ModelDelegate.java:412)
        at dev.laarryy.eris.models.guilds.ServerMessage.findOrCreateIt(ServerMessage.java:219)
        at dev.laarryy.eris.listeners.MessageCreateListener.on(MessageCreateListener.java:49)
        ... 21 more
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '::BIGINT,19::INT,1772::INT)' at line 1
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1348)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:1502)
        at org.javalite.activejdbc.DB.execInsert(DB.java:713)
        ... 29 more

这对我来说很奇怪，因为应该插入的bigint、int和int实际上是一个bigint、int和int，我不知道还能做什么来找出错误的原因。在堆栈跟踪的第二行到最后一行的C3P0行使我认为，ActiveJDBC编写的语句在某种程度上并不是将其发送到数据库的语句。

我也尝试过启用C3P0语句缓存，以防有效果，但它没有。

如有任何建议或指导，将不胜感激！

更新

我现在还尝试遵循快速启动过程，在这里https://www.mchange.com/projects/c3p0/#quickstart，希望手动设置驱动程序类可能解决问题，但它没有。我还是有同样的痕迹。我试过的密码：

public class DatabaseLoader {

    private static final Logger logger = LogManager.getLogger(DatabaseLoader.class);

    static ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();

    static {
        try {
           comboPooledDataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
            comboPooledDataSource.setJdbcUrl(ConfigManager.getAddress());
            comboPooledDataSource.setUser(ConfigManager.getUsername());
            comboPooledDataSource.setPassword(ConfigManager.getPassword());
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
    }

    public static void openConnection() {
        Base.open(comboPooledDataSource);
    }

    public static void openConnectionIfClosed() {
        if (!Base.hasConnection()) {
            openConnection();
        }
    }

    public static void closeConnectionIfOpen() {
        if (Base.hasConnection()) {
            logger.info("Disconnecting from database!");
            Base.close();
        }
    }
}

我还尝试切换到Hikari 5，除了第二行到最后一行是来自Hikari而不是c3p0之外，我还得到了完全相同的错误。这让我相信，ActiveJDBC端有问题，而不是连接池端。

要求提供的详细资料：

(Corretto)

• ActiveJDBC 3.0-SNAPSHOT

• MySQL版本8.0.26

• mysql-connector-java 8.0.26 (Driver.getMajorVersion()返回8，#getMinorVersion()按预期返回0)

• C3P0版本0.9.5.5

Answer 1

不幸的是，有一个缺陷的快照发布了几个小时。快照v3.0-快照有一个错误，其中PostgreSQL语法被意外地默认为MySQL。似乎给你带来了麻烦。JDBC连接池与此无关。我们对快照很小心，但是这样的事情会发生(每隔几年一次)。你在更新的过程中搞错了。

如何解决这个问题是为了消除所有Maven缓存：

rm -rf ~/.m2/repository/org/javalite

在下一个构建中，您将获得一个新的(大大改进的：)快照，它没有这个缺陷。

很抱歉出现了麻烦，但是快照“正在进行中”。

相关缺陷：https://github.com/javalite/javalite/issues/1187