濒临崩溃－恳请大虾出手相助！（之MySQL问题）

Question

想做一个迷你的管理软件，没想到出了N个错，到了这个怎么也搞不定了，还望大虾指点！这个错是在信息录入页，按完提交出的，可是我怎么也不明白，Sql语句错在哪了？？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 'describe,money,realMoney,moreMoney,addContent,mobile1,mobile2,phone,weight,high,' at line 1
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2822)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1536)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1626)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3031)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:943)
        at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:768)
        at com.flower.www.persistence.CustomerInfoDao.inserCustomerInfo(CustomerInfoDao.java:94)
        at com.flower.www.service.CustomerInfoFacadeImpl.insertCustomerInfo(CustomerInfoFacadeImpl.java:18)
        at com.flower.www.webtier.CustomerInfoController.insertCustomerInfo(CustomerInfoController.java:97)
        at com.flower.www.webtier.CustomerInfoController.doGet(CustomerInfoController.java:183)
        at com.flower.www.webtier.CustomerInfoController.doPost(CustomerInfoController.java:202)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
        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:233)
        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:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
        at java.lang.Thread.run(Unknown Source)
Error Trace in inserCustomerInfo() : 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 'describe,money,realMoney,moreMoney,addContent,mobile1,mobile2,phone,weight,high,' at line 1

Answer 1

部分代码如下：
package com.flower.www.domian;import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Locale;
public class CustomerInfo {
        private Integer id;
        private String customerName;
        private Integer age;
        private String sex;
        private String date;
        private String describe;
        private Integer money;
        private Integer realMoney;
        private Integer moreMoney;
        private String addContent;
        private String mobile1;
        private String mobile2;
        private String phone;
        private Integer weight;
        private Integer high;
        private String birthday;
        private String qq;
        private String email;
        private Integer range;
        private String caiKuo;
        private String caiPen;
        private String shuiBan;
        private String xiangKuang;
        private String xiaoJian;
        private Integer ckFlag;
        private Integer cpFlag;
        private Integer sbFlag;
        private Integer xkFlag;
        private Integer xjFlag;
        private String good1;
        private String good2;
        private String good3;
        private String good4;
        private String good5;
        private Integer g1;
        private Integer g2;
        private Integer g3;
        private Integer g4;
        private Integer g5;
        
        get和set方法省略，太长了不让发！        public CustomerInfo() {
                
                this.customerName="";
                this.age=Integer.valueOf(0);
                this.sex="";
                this.date="2008-01-01";
                this.describe="";
                this.money=Integer.valueOf(0);
                this.realMoney=Integer.valueOf(0);
                this.moreMoney=Integer.valueOf(0);
                this.addContent="";
                this.mobile1="";
                this.mobile2="";
                this.phone="";
                this.weight=Integer.valueOf(0);
                this.high=Integer.valueOf(0);
                this.birthday="2008-01-01";
                this.qq="";
                this.email="";
                this.range=Integer.valueOf(0);
                this.caiKuo="";
                this.caiPen="";
                this.shuiBan="";
                this.xiangKuang="";
                this.xiaoJian="";
                this.ckFlag=Integer.valueOf(0);
                this.cpFlag=Integer.valueOf(0);
                this.sbFlag=Integer.valueOf(0);
                this.xkFlag=Integer.valueOf(0);
                this.xjFlag=Integer.valueOf(0);
                this.good1="";
                this.good2="";
                this.good3="";
                this.good4="";
                this.good5="";
                this.g1=Integer.valueOf(0);
                this.g2=Integer.valueOf(0);
                this.g3=Integer.valueOf(0);
                this.g4=Integer.valueOf(0);
                this.g5=Integer.valueOf(0);
                
        }
        
        /**
          *method 将字符串类型的日期转换为一个Date
          *@param dateString 需要转换为Date的字符串
          *@return dataTime Date
          */
         public final java.sql.Date string2Date(String dateString)
          throws java.lang.Exception {
          DateFormat dateFormat;
          dateFormat = new SimpleDateFormat("yyyy-MM-dd");
          dateFormat.setLenient(false);          
          java.util.Date timeDate = dateFormat.parse(dateString); //u t i l类型
          java.sql.Date dateTime = new java.sql.Date(timeDate.getTime()); //s q l类型
          System.out.println("CustomerInfo sring2Date()"+dateTime);
          return dateTime;
         }
        
        
        
        }

Answer 2

package com.flower.www.persistence;import java.sql.*;
import java.util.ArrayList;
import java.util.List;import org.omg.CORBA.Request;import com.flower.www.domian.CustomerInfo;public class CustomerInfoDao {
        private Connection dataConnection;
        final static String selectStr = "select * from customerinfo";
        final static String insertStr = "insert into customerinfo(customerName,age,sex,date,describe,money,realMoney,moreMoney,addContent,mobile1,mobile2,phone,weight,high,birthday,qq,email,range,caiKuo,caiPen,shuiBan,xiangKuang,xiaoJian,ckFlag,cpFlag,sbFlag,xkFlag,xjFlag,good1,good2,good3,good4,good5,g1,g2,g3,g4,g5)" +
                                                                    "value(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        final static String findStr2Test = "select * from customerinfo where age = ?";
        
        public CustomerInfoDao() {
                DataBaseConnect dataBaseConnect = null;
                try {
                        dataBaseConnect = new DataBaseConnect();
                } catch (ClassNotFoundException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                        System.out.println("Error Trace in CustomerInfo() constructor1 : " + e.getMessage());
                } catch (IllegalAccessException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                        System.out.println("Error Trace in CustomerInfo() constructor : " + e.getMessage());
                } catch (InstantiationException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                        System.out.println("Error Trace in CustomerInfo() constructor: " + e.getMessage());
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                        System.out.println("Error Trace in CustomerInfo() constructor: " + e.getMessage());
                }
                dataConnection = dataBaseConnect.dataConnection;
        }
        
        public int inserCustomerInfo(CustomerInfo customerInfo) throws Exception {
                try {
                        PreparedStatement statement = dataConnection.prepareStatement(insertStr);
                        String date = customerInfo.getDate();
                        String birthday = customerInfo.getBirthday();
                        System.out.println(date);
                        System.out.println(birthday);
                        
                        statement.setString(1, customerInfo.getCustomerName());
                        statement.setInt(2, customerInfo.getAge());
                        statement.setString(3, customerInfo.getSex());
                        statement.setDate(4, customerInfo.string2Date(date));
                        
                        statement.setString(5, customerInfo.getDescribe());
                        statement.setInt(6, customerInfo.getMoney());
                        statement.setInt(7, customerInfo.getRealMoney());
                        statement.setInt(8, customerInfo.getMoreMoney());
                        statement.setString(9, customerInfo.getAddContent());
                        statement.setString(10, customerInfo.getMobile1());
                        statement.setString(11, customerInfo.getMobile2());
                        statement.setString(12, customerInfo.getPhone());
                        statement.setInt(13, customerInfo.getWeight());
                        statement.setInt(14, customerInfo.getHigh());
                        statement.setDate(15, customerInfo.string2Date(birthday));
                        
                        statement.setString(16, customerInfo.getQq());
                        statement.setString(17, customerInfo.getEmail());
                        statement.setInt(18, customerInfo.getRange());
                        statement.setString(19, customerInfo.getCaiKuo());
                        statement.setString(20, customerInfo.getCaiPen());
                        statement.setString(21, customerInfo.getShuiBan());
                        statement.setString(22, customerInfo.getXiangKuang());
                        statement.setString(23, customerInfo.getXiaoJian());
                        statement.setInt(24, customerInfo.getCkFlag());
                        statement.setInt(25, customerInfo.getCpFlag());
                        statement.setInt(26, customerInfo.getSbFlag());
                        statement.setInt(27, customerInfo.getXkFlag());
                        statement.setInt(28, customerInfo.getXjFlag());
                        statement.setString(29, customerInfo.getGood1());
                        statement.setString(30, customerInfo.getGood2());
                        statement.setString(31, customerInfo.getGood3());
                        statement.setString(32, customerInfo.getGood4());
                        statement.setString(33, customerInfo.getGood5());
                        statement.setInt(34, customerInfo.getG1());
                        statement.setInt(35, customerInfo.getG2());
                        statement.setInt(36, customerInfo.getG3());
                        statement.setInt(37, customerInfo.getG4());
                        statement.setInt(38, customerInfo.getG5());
                        
                        statement.execute();
                        
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                        System.out.println("Error Trace in inserCustomerInfo() : " + e.getMessage());                }
                return 1;
                
        }
                
                
}

Answer 3

package com.flower.www.persistence;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;public class DataBaseConnect {        /**
         * @param args
         */
        public Connection dataConnection;
        private String url = "";
        private String driver;
        
        public DataBaseConnect() throws ClassNotFoundException,IllegalAccessException,InstantiationException,SQLException{
                driver="com.mysql.jdbc.Driver";
                url="jdbc:mysql://localhost:3306/lilyfox";
                Class.forName(driver).newInstance();
                dataConnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/lilyfox?user=root&password=root&useunicode=true&characterencoding=gb2312" );
        }
        
        }

Answer 4

sql 语法错误，你把你的inserCustomerInfo
里面执行的SQL打印出来，到命令行手工执行看看吧！

Answer 5

package com.flower.www.service;import java.util.List;import com.flower.www.domian.CustomerInfo;public interface CustomerInfoFacade {
        
        int insertCustomerInfo(CustomerInfo customerInfo) throws Exception;
        int deleteCustomerInfo(CustomerInfo customerInfo);
        int updateCustomerInfo(CustomerInfo customerInfo);
        List selectCustomerInfo(CustomerInfo customerInfo);
        CustomerInfo findCustomerInfo(CustomerInfo customerInfo);}
package com.flower.www.service;import java.util.List;import com.flower.www.domian.CustomerInfo;
import com.flower.www.persistence.CustomerInfoDao;public class CustomerInfoFacadeImpl implements CustomerInfoFacade {
        private CustomerInfoDao customerInfoDao;
        
        public CustomerInfoFacadeImpl() {
                
                customerInfoDao = new CustomerInfoDao();
                
        }
        
        public int insertCustomerInfo(CustomerInfo customerInfo) throws Exception {
                return customerInfoDao.inserCustomerInfo(customerInfo);
        }
        
        public int deleteCustomerInfo(CustomerInfo customerInfo) {
                return customerInfoDao.deleteCustomerInfo(customerInfo);
        }
        
        public int updateCustomerInfo(CustomerInfo customerInfo) {
                return customerInfoDao.updateCustomerInfo(customerInfo);
        }
        
        public List selectCustomerInfo(CustomerInfo customerInfo) {
                return customerInfoDao.selectCustomerInfo(customerInfo);
        }
        
        public CustomerInfo findCustomerInfo(CustomerInfo customerInfo) {
                return customerInfoDao.findCustomerInfo(customerInfo);
        }
        }

Answer 6

package com.flower.www.webtier;import java.io.IOException;
import java.util.List;import javax.servlet.http.HttpServlet;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import com.flower.www.domian.CustomerInfo;
import com.flower.www.service.CustomerInfoFacade;
import com.flower.www.service.CustomerInfoFacadeImpl;
public class CustomerInfoController extends HttpServlet {
        
        private CustomerInfoFacade customerInfoFacade;
        private int controlMethod;
        
        public CustomerInfoController() {
                
                customerInfoFacade = new CustomerInfoFacadeImpl();
                
        }
                
        private void insertCustomerInfo(HttpServletRequest request, HttpServletResponse response) throws IOException ,ServletException {
                CustomerInfo customerInfo = new CustomerInfo();
                customerInfo.setCustomerName(request.getParameter("customerName"));
                if(!(request.getParameter("age").equals(""))) { //request.getParameter("age") == null && 
                        //customerInfo.setAge(Integer.valueOf(0));
                        customerInfo.setAge(Integer.valueOf(request.getParameter("age")));
                }  
                customerInfo.setSex(request.getParameter("sex"));
                if(!request.getParameter("date").equals("")) {
                        customerInfo.setDate(request.getParameter("date"));
                }        
                customerInfo.setDescribe(request.getParameter("describe"));
                if(!request.getParameter("money").equals("")) { 
                        customerInfo.setMoney(Integer.valueOf(request.getParameter("money")));
                }  
                if(!request.getParameter("realMoney").equals("")) { 
                        customerInfo.setRealMoney(Integer.valueOf(request.getParameter("realMoney")));
                }  
                if(!request.getParameter("moreMoney").equals("")) { 
                        customerInfo.setMoreMoney(Integer.valueOf(request.getParameter("moreMoney")));
                }
                customerInfo.setAddContent(request.getParameter("addContent"));
                customerInfo.setMobile1(request.getParameter("mobile1"));
                customerInfo.setMobile2(request.getParameter("mobile2"));
                customerInfo.setPhone(request.getParameter("phone"));
                if(!request.getParameter("weight").equals("")) { 
                        customerInfo.setWeight(Integer.valueOf(request.getParameter("weight")));
                }
                if(!request.getParameter("high").equals("")) { 
                        customerInfo.setHigh(Integer.valueOf(request.getParameter("high")));
                }
                if(!request.getParameter("birthday").equals("")) {
                        customerInfo.setBirthday(request.getParameter("birthday"));
                }        
                customerInfo.setQq(request.getParameter("qq"));
                customerInfo.setEmail(request.getParameter("email"));
                if(!request.getParameter("range").equals("")) { 
                        customerInfo.setRange(Integer.valueOf(request.getParameter("range")));
                }
                customerInfo.setCaiKuo(request.getParameter("caiKuo"));
                customerInfo.setCaiPen(request.getParameter("caiPen"));
                customerInfo.setShuiBan(request.getParameter("shuiBan"));
                customerInfo.setXiangKuang(request.getParameter("xiangKuang"));
                customerInfo.setXiaoJian(request.getParameter("xiaoJian"));
//                customerInfo.setCkFlag(Integer.valueOf(request.getParameter("ckFlag")));
//                customerInfo.setCpFlag(Integer.valueOf(request.getParameter("cpFlag")));
//                customerInfo.setSbFlag(Integer.valueOf(request.getParameter("sbFlag")));
//                customerInfo.setXkFlag(Integer.valueOf(request.getParameter("xkFlag")));
//                customerInfo.setXjFlag(Integer.valueOf(request.getParameter("xjFlag")));
                customerInfo.setGood1(request.getParameter("good1"));
//                customerInfo.setGood2(request.getParameter("good2"));
//                customerInfo.setGood3(request.getParameter("good3"));
//                customerInfo.setGood4(request.getParameter("good4"));
//                customerInfo.setGood5(request.getParameter("good5"));
//                customerInfo.setG1(Integer.valueOf(request.getParameter("g1")));
//                customerInfo.setG2(Integer.valueOf(request.getParameter("g2")));
//                customerInfo.setG3(Integer.valueOf(request.getParameter("g3")));
//                customerInfo.setG4(Integer.valueOf(request.getParameter("g4")));
//                customerInfo.setG5(Integer.valueOf(request.getParameter("g5")));
                
                try {
                        customerInfoFacade.insertCustomerInfo(customerInfo);
                } catch (Exception e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                        System.out.println("Error Trace in CustomerInfoController insertCustomerInfo(): " + e.getMessage());
                }
                //selectCustomerInfo(request, response);
        }
        
                private void findCustomerInfo(HttpServletRequest request, HttpServletResponse response) throws IOException ,ServletException {
                CustomerInfo customerInfo = new CustomerInfo();
//                customerInfo.setCustomerName(request.getParameter("customerName"));
                customerInfo.setAge(Integer.valueOf(request.getParameter("customerName")));
                System.out.println("customerInfoCustomerName"+customerInfo.getCustomerName());
                System.out.println("customerInfoAge"+customerInfo.getAge());
                CustomerInfo returncustomerInfo = customerInfoFacade.findCustomerInfo(customerInfo);
                request.setAttribute("returncustomerInfo", returncustomerInfo);
                RequestDispatcher requestDispatcher = request.getRequestDispatcher("customerModify.jsp");
                requestDispatcher.forward(request, response);
        }
        
        public void doGet (HttpServletRequest request, HttpServletResponse response) throws IOException ,ServletException {
                request.setCharacterEncoding("gb2312");
                controlMethod = Integer.parseInt(request.getParameter("controlMethod"));
                switch(controlMethod) {
                        case 0:{
                                this.selectCustomerInfo(request, response);
                                break;
                        }
                        case 1:{
                                this.insertCustomerInfo(request, response);
                                break;
                        }
                        case 2:{
                                this.updateCustomerInfo(request, response);
                                break;
                        }
                        case 3:{
                                this.deleteCustomerInfo(request, response);
                                break;
                        }
                        case 4:{
                                this.findCustomerInfo(request, response);
                                break;
                        }
                }
        }
        
        public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException ,ServletException {
                doGet(request, response);
        }
        
}

Answer 7

final static String insertStr = "insert into customerinfo(customerName,age,sex,date,describe,money,realMoney,moreMoney,addContent,mobile1,mobile2,phone,weight,high,birthday,qq,email,range,caiKuo,caiPen,shuiBan,xiangKuang,xiaoJian,ckFlag,cpFlag,sbFlag,xkFlag,xjFlag,good1,good2,good3,good4,good5,g1,g2,g3,g4,g5)" + 
                                                                    "value(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; 这里参数赋值        final static String findStr2Test = "select * from customerinfo where age = ?"; 这里参数赋值这两个sql不要放在一起，分开试试看

Answer 8

做个断点调试一下，看看问题是处在什么地方。应该是sql语句的错误！

Answer 9

describe是关键字，换个写法。

Answer 10

先测试你的sql是不是对的，再拿到类里面去用