用jdbc连接操作mysql 语法问题

0 0

用jdbc连接操作mysql 语法问题0

如题
错误代码如下：
com.mysql.jdbc.exceptions.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 '?,english_name=?,sex=?,age=?,birthday=?,description=? WHERE id=?' at line 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1049)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1749)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1666)
at org.apache.jsp.operatePerson_jsp._jspService(operatePerson_jsp.java:223)
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 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(Unknown Source)

程序代码如下：
String id = request.getParameter("id");
        String sql = "UPDATE tb_person SET name=?,english_name=?,sex=?,age=?,birthday=?,description=? WHERE id=?";
        Connection conn = null;
        PreparedStatement ps = null;
        try {
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/databaseWeb?characterEncoding=UTF-8","root","root");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        java.sql.Date d = new java.sql.Date(sdf.parse(birthday).getTime());
        ps = conn.prepareStatement(sql);
        ps.setString(1, toSQlString(name));
        ps.setString(2, toSQlString(englishName));
        ps.setString(3, toSQlString(sex));
        ps.setInt(4, Integer.parseInt(age));
        ps.setDate(5, d);
        ps.setString(6, toSQlString(description));
        System.out.println(id);
        ps.setInt(7, Integer.parseInt(id));
        int result = ps.executeUpdate(sql);

2012年7月25日 22:46

v韧竹v
-1
0 0 9

5个答案按时间排序按投票排序

0 0

看看这个或许有帮助，应该是你的字段使用了mysql的关键字http://hi.baidu.com/wuguohu/blog/item/873b7c31bdb2931deac4af80.html/cmtid/f9f8bfa1830c269b46106441

2012年7月26日 11:12

fangfumin
14
0 1 12

1条评论

0 0

很明显是sql语句有问题，把sql拿出来运行下就知道问题在那里了。

祝好运了。

2012年7月26日 10:57

那夜你哭了
17
0 0 4

添加评论

0 0

试试这个：


package com.xawx.db;

import java.sql.*;

public class DB {
	private final String DRIVER="com.mysql.jdbc.Driver";
	private final String URL="jdbc:mysql://192.168.1.54:3306/weibo";
	private final String USERNAME="root";
	private final String USERPASS="123456";
	private Connection conn=null;
	
	public DB(){
		try {
			Class.forName(DRIVER);
			System.out.println("数据库驱动加载成功！");
		} catch (ClassNotFoundException e) {
			System.out.println("数据库驱动加载失败！");
		}
	}
	public Connection getConnection(){
		try {
			conn=DriverManager.getConnection(URL, USERNAME, USERPASS);
			System.out.println("数据库连接成功！");
		} catch (SQLException e) {
			System.out.println("数据库连接失败！");
		}
		return conn;
	}
	public void closeConnection(){
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				System.out.println("数据库关闭失败！");
			}finally{
				conn=null;
			}
		}
	}
}

public int Update(){
		DB db=new DB();
		int temp=0;
		  String sql = "UPDATE tb_person SET name=?,english_name=?,sex=?,age=?,birthday=?,description=? WHERE id=?";
		  try {
				PreparedStatement pps=db.getConnection().prepareStatement(sql);
				pps.setString(1, toSQlString(name)); 
		        pps.setString(2, toSQlString(englishName)); 
		        pps.setString(3, toSQlString(sex)); 
		        pps.setInt(4, Integer.parseInt(age)); 
		        pps.setDate(5, d); 
		        pps.setString(6, toSQlString(description)); 
		        System.out.println(id); 
		        ps.setInt(7, Integer.parseInt(id)); 
		        int result = ps.executeUpdate(sql);
				temp=pps.executeUpdate();
				System.out.println("更新成功！");
				db.closeConnection();
			} catch (SQLException e) {
				System.out.println("更新失败！");
			}
		  return temp;
	}

2012年7月26日 09:34

pandro
30
0 0 1

1条评论

0 0

从日志中拷贝执行失败的sql，通过mysql客户端再次执行，这样更容易发现sql的问题在哪里

2012年7月26日 08:56

charles751
1
0 1 7

2条评论

0 0

你这样貌似sql中name是关键字
你先修改部分字段
UPDATE tb_person SET english_name=?,sex=?,age=?,birthday=?,description=? WHERE id=?
在mysql查询分析器中开能正常修改不，如果能，那么就是name关键字的问题了
如果是
UPDATE tb_person SET [name]=?,english_name=?,sex=?,age=?,birthday=?,description=? WHERE id=?

2012年7月25日 23:12