com.microsoft.sqlserver.jdbc.SQLServerException: '@P0' 附近有语法错误。

Question

com.microsoft.sqlserver.jdbc.SQLServerException: '@P0' 附近有语法错误。

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)

at com.jdbc.JdbcUtils.findMoreResult(JdbcUtils.java:175)

at com.product.ProductDao.listProduct(ProductDao.java:59)

at org.apache.jsp.main_jsp._jspService(main_jsp.java:77)

at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:393)

at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:320)

at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at com.filter.MyFilter.doFilter(MyFilter.java:42)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:261)

at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)

at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:581)

at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)

at java.lang.Thread.run(Thread.java:744)

注册驱动成功！！

代码

package com.product;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import java.util.Map;

import com.jdbc.JdbcUtils;

public class ProductDao implements ProductService {

private JdbcUtils jdbcUtils;

public ProductDao() {

// TODO Auto-generated constructor stub

jdbcUtils = new JdbcUtils();

}

@Override

public boolean addProduct(List<Object> params) {

boolean flag = false;

try {

jdbcUtils.getConnection();

String sql = "insert into product(proid,proname,proprice,proaddress,proimage) values(?,?,?,?,?)";

flag = jdbcUtils.updateByPreparedStatement(sql, params);

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}finally{

// 关闭数据库连接

jdbcUtils.releaseConn();

}

return flag;

}

@Override

public List<Map<String, Object>> listProduct(String proname ,int start ,int end) {

// TODO Auto-generated method stub

List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();

List<Object> params  = new ArrayList<Object>();

try {

jdbcUtils.getConnection();

String sql = "select * from product where 1=1 and proname like ? limit ?,?";

if(proname.equals("")){

sql = "select * from product limit ? ,?";

params.add(start);

params.add(end);

}else{

params.add("%"+proname+"%");

params.add(start);

params.add(end);

}

list = jdbcUtils.findMoreResult(sql, params);

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

} finally{

jdbcUtils.releaseConn();

}

return list;

}

//查询总记录数

@Override

public int getItemCount(String proname) {

// TODO Auto-generated method stub

int count = 0;

Map<String, Object> map = null;

List<Object> params = null;

try {

jdbcUtils.getConnection();

String sql = "select count(*) totalCount from product where 1=1 and proname like ?";

if(proname.equals("")){

sql = "select count(*) totalCount from product";

}else{

params = new ArrayList<Object>();

params.add("%"+proname+"%");

}

map = jdbcUtils.findSimpleResult(sql, params);

count = Integer.parseInt(map.get("totalCount").toString());

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

} finally{

// 关闭数据库连接

jdbcUtils.releaseConn();

}

return count;

}

@Override

public boolean delProduct(String[] ids) {

boolean flag = false;

try {

jdbcUtils.getConnection();

if (ids!=null) {

String[] sql = new String[ids.length];

for(int i = 0 ; i< ids.length; i++){

sql[i] = "delete from product where proid = '"+ids[i]+"'";

System.out.println(sql[i]);

}

flag = jdbcUtils.deleteByBatch(sql);

}

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

} finally{

// 关闭数据库连接

jdbcUtils.releaseConn();

}

return flag;

}

@Override

public Map<String, Object> viewProduct(String proid) {

// TODO Auto-generated method stub

Map<String, Object> map = null;

try {

jdbcUtils.getConnection();

List<Object> params = new ArrayList<Object>();

params.add(proid);

String sql = "select * from product where proid = ?";

map = jdbcUtils.findSimpleResult(sql, params);

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

} finally{

// 关闭数据库连接

jdbcUtils.releaseConn();

}

return map;

}

}

怎么解决

Answer 1

打印出执行的SQL语句，然后检查是否有错误，比如SQL语句中少了逗号或缺了空格。仔细检查