mybatis is not working while put AUTO_INCREMENT in INSERT statement

Question

just like the code below, it's work fine in Navicate, but I put this in mybatis, it's not working, seems cannot get the value.

    INSERT INTO `table_user`(
        `account`,
        `createtime`,
        `path`
    )
    VALUES
    (
    'test',
    now(),
    CONCAT(
        'test-',
        (
            SELECT
                AUTO_INCREMENT
            FROM
                information_schema. TABLES
            WHERE
                TABLE_SCHEMA = DATABASE ()
            AND TABLE_NAME = 'user_info'
        )
    )
)

mybatis: INSERT INTO ( account, createtime, path ) VALUES ( #{account}, now(), CONCAT('test-',(SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='user_info')) )

I got exceptions:

### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'path' cannot be null
### The error may involve bestyyt.dao.UserInfoDao.save-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO                table_user               (         account, createtime, path         ) VALUES (         ?, now(), CONCAT('test-',(SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='table_user'))         )
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'path' cannot be null
; SQL []; Column 'path' cannot be null; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'path' cannot be null

    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:85)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447)
    at com.sun.proxy.$Proxy26.insert(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:279)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:56)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
    at com.sun.proxy.$Proxy27.save(Unknown Source)
    at bestyyt.dao.UserInfoDaoTest.save(UserInfoDaoTest.java:28)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'path' cannot be null
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.Util.getInstance(Util.java:387)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
    at com.sun.proxy.$Proxy29.execute(Unknown Source)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434)
    ... 34 more

Does you table 'table_user' has a auto_increment primary key? — John
work fine is table 'user_info',in mybatis,you write table ' table_user' — John
yes, I changed the table name, but I think it's not caused by this. — Lyn
The problem is not with insert itself. SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='user_info' returns null when query fails. Are you running these two queries against the same database? — Roman Konoval

Paul Campbell Paul Campbell · Accepted Answer · 2018-03-16T14:22:39

The short answer here is to hardcode your database name in the query rather than use DATABASE(). The longer version ...

As has been identified in the comments the immediate cause of your problem is the NULL returned by the query. The CONCAT() function will return NULL if any part of it is NULL which you could solve by using

CONCAT_WS('','test-',(SELECT the rest of your query))

That would always guarantee that path contained the string 'test-' so you'd not fail the NOT NULL constraint on the column (but you'd have nonsense data).

Since you have confirmed the existence of the user_info table, the failure of the query is likely down to

TABLE_SCHEMA=DATABASE()

which attempts to select the default (current) database. If there isn't a default then it returns NULL (hence CONCAT fails). To get DATABASE() to return a value you need to set it explicitly with a USE statement (or your client does this). I tried this in workbench but it would probably work in navicat e.g.

-- make sure you have no database selected
CREATE DATABASE foo;
USE foo;
DROP DATABASE foo;

-- create the test table
CREATE DATABASE test IF NOT EXISTS;
DROP TABLE IF EXISTS test.auto_inc;
CREATE TABLE test.auto_inc (
  `id` INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  `path` varchar(20) NOT NULL );

-- without a default using database()
SELECT 'With DB()',`AUTO_INCREMENT`
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'auto_inc'; 
Empty set (0.00 sec)

USE test;

-- with explicit USE statement
SELECT 'DB()',`AUTO_INCREMENT` 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'auto_inc'; 
+------+----------------+
| DB() | AUTO_INCREMENT |
+------+----------------+
| DB() |              1 |
+------+----------------+
1 row in set (0.02 sec)

I can't see anything in your sample where you explicitly set the database. Presumably, you specify a database in your connection string but that doesn't appear to be sufficient. You could test that from your application with a simple

SELECT DATABASE();

If you hardcode the database name, as you do the table name, it will work without the need for a USE statement .

Finally, since this table has an AUTO_INCREMENT column and the record you create would have that value, why are you trying to get it separately to write into the record?

mybatis is not working while put AUTO_INCREMENT in INSERT statement

1 Answers