mycat+mysql 报错com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: partition table, insert mus

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: partition table, insert must provide ColumnList

1 代码详情

package com.chainhu.test;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import javax.sql.DataSource;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.test.context.ContextConfiguration;import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations = { "classpath:applicationContext.xml" })public class MyCatTest {@Autowiredprivate DataSource dataSource;@Testpublic void add() {Connection connection = null;PreparedStatement pstat = null;try {connection = dataSource.getConnection();String sql = "insert into deal_product values(?,?,?,?)";// 错误在此处pstat = (PreparedStatement) connection.prepareStatement(sql);for (int i = 0; i < 12; i++) {pstat.setString(1, i+"");pstat.setString(2, "product" + i);pstat.setDouble(3, 23.1 + i);pstat.setInt(4, 2 + i);pstat.execute();}} catch (SQLException e) {e.printStackTrace();} finally {if (pstat != null) {try {pstat.close();} catch (SQLException e) {e.printStackTrace();}}if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}}}

2 异常

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: partition table, insert must provide ColumnListat 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:406)at com.mysql.jdbc.Util.getInstance(Util.java:381)at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3536)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3468)at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1957)at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2107)at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2086)at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1365)at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)at com.chainhu.test.MyCatTest.add(MyCatTest.java:35)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:73)at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82)at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:73)at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:217)at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:83)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:68)at org.junit.runners.ParentRunner.run(ParentRunner.java:363)at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:163)at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

3 解决办法

　　我把所有的列明都添加上，发现添加成功：

       String sql = "insert into deal_product(id, pname, price, pnum) values(?,?,?,?)";

    这是因为mycat分区表,插入必须提供ColumnList（列名）