You have an error in your SQL syntax; check the manual that corresponds to your

J2EE 码拜 9年前 (2015-08-12) 1303次浏览
 

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO TAB_CUSTOMER_INFO_SYNC_VIP (id, code, name, company_site, industry, fax, mobile, address, branch_code, branch_name, belong_branch, belong_center, create_time,vip_create_time ) values (?,?,?,?,?,?,?,?,?,?,?,?,new Date(),?)]; nested exception is java.sql.BatchUpdateException: 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 “”Date(),null)”” at line 1
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615)
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:884)
at com.yundaex.common.base.BaseSpringJdbc.batchUpdateNotAutoCommit(BaseSpringJdbc.java:126)
at com.yundaex.ydmcp.domain.dao.impl.CustomerVipDaoImpl.batchToUpOrInsertCV(CustomerVipDaoImpl.java:54)
at com.yundaex.ydmcp.task.handle.ResolveCustSyncLocalHandle.syncCustForVIPSys(ResolveCustSyncLocalHandle.java:105)
at com.yundaex.ydmcp.task.handle.ResolveCustSyncLocalHandle$$FastClassByCGLIB$$1d22cc0e.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:689)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:622)
at com.yundaex.ydmcp.task.handle.ResolveCustSyncLocalHandle$$EnhancerByCGLIB$$e7ee2c23.syncCustForVIPSys(<generated>)
at com.yundaex.ydmcp.task.ResolveCustSyncLocalTask.execute(ResolveCustSyncLocalTask.java:28)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:64)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:53)
at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.BatchUpdateException: 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 “”Date(),null)”” at line 1
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2054)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1467)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:899)
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:1)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
… 29 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 “”Date(),null)”” at line 1
at sun.reflect.GeneratedConstructorAccessor22.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2450)
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2006)
… 35 more
[2015-08-05 19:36:00] INFO  com.yundaex.ydmcp.task.FetchWayBillsTask – 从客户临时表抓取vip客户定时任务结束…

#1
插入语句如下:

/ 插入vip客户表
			String insertCustomerVipSql = "INSERT INTO TAB_CUSTOMER_INFO_SYNC_VIP (id, code, name, company_site, " 
         	        + "industry, fax, mobile, address, branch_code, branch_name, belong_branch, "
					+ "belong_center, create_time,vip_create_time ) values (?,?,?,?,?,?,?,?,?,?,?,?,new Date(),?)";
			List<Map<String, Object>> listToBatch = null;
			for (int x = 0, len = custs.size(); x < len; x++) {
				if (listToBatch == null) {
					listToBatch = new ArrayList<Map<String, Object>>();
				}
				listToBatch.add(custs.get(x));
				if (((x + 1) % Constant.FETCH_WB_BATCH_EXECUTE_COUNT == 0) || (x == len - 1)) { // 最多5000 提交一次
					cusVipDao.batchToUpOrInsertCV(listToBatch, insertCustomerVipSql);
					listToBatch = null;
				}
			}

Dao层的代码

@Repository("customerVipDao")
public class CustomerVipDaoImpl extends YdmcpBaseSpringJdbc<CustomerVip> implements CustomerVipDao {
	public void batchToUpOrInsertCV(List<Map<String, Object>> listToBatch, String insertSql) throws Exception {

			int size = listToBatch.size();//13个 :size=13
			Object[] insertObjects = new Object[size];
		   //经测试:cusVipList.size()=1
	  for (int i = 0; i < size; i++) { // 循环 objList <Object>
			    CustomerVip cusVip=new CustomerVip();
			    String code=cusVip.getCode();
			    String name=cusVip.getName();
			    String company_site=cusVip.getCompany_site();
			    String industry=cusVip.getIndustry();
			    String fax=cusVip.getFax();
			    String mobile=cusVip.getMobile();
			    String address=cusVip.getAddress();
			    String branchCode=cusVip.getBranchCode();
			    String branchName=cusVip.getBranchName();
			    String belongBranch=cusVip.getBelongBranch();
			    String belongCenter=cusVip.getBelongCenter();
			    Date vipCreateTime=cusVip.getVipCreateTime();
//				String primaryKey = StringUtils.getUUID();;//用UUID生成主键
			    String id=cusVip.getId();//即id
				// TODO obj.getXXX();
			     insertObjects[i] = new Object[] {id, code, name, company_site, industry, fax, mobile, address, branchCode, branchName, belongBranch, belongCenter, vipCreateTime};
				}
		super.batchUpdateNotAutoCommit(insertSql, size, insertObjects); // 批量插入或更新
	}
}

#2

5分

语法错误了。 right syntax to use near “”Date(),null)”” at line 1    这个  Date()数据多少, 再看看  null这个 对不
#3

10分

sql没有new date这种写法,这个是java的

+ “belong_center, create_time,vip_create_time ) values (?,?,?,?,?,?,?,?,?,?,?,?,new Date(),?)”;  改为:

+ “belong_center, create_time,vip_create_time ) values (?,?,?,?,?,?,?,?,?,?,?,?,”+new Date()+”,?)”;

#4

5分

数据库是oracle的话直接sysdate替换new Date()
或者你在插入之前本地new个date然后用TODATE(你new的date,””yyyy-mm-dd””)替换

CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明You have an error in your SQL syntax; check the manual that corresponds to your
喜欢 (0)
[1034331897@qq.com]
分享 (0)