在Struts中引发关于SQL语法的MySQLSyntaxErrorException [英] Throwing MySQLSyntaxErrorException regarding syntax of SQL in Struts

问题描述

我在Struts框架上做一个项目.

I am making a project on Struts framework.

在使用SQL将数据插入表中时，服务器出现异常，我找不到解决方法.

I am getting a exception from server while inserting data in a table using SQL and I am unable to find the solution.

错误如下:

javax.servlet.ServletException: 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 ',,,,,,,'yes','yes',,,,'admin')' at line 1

我正在生成错误的代码如下:

    sql="Insert into field_visit_details (visit_date,block,village,flw_category,
   flw_name,flw_contact_no,aware_zinc,
aware_ors,past_zinc,past_ors,qty_avail_zinc_ten,qty_avail_zinc_twe,qty_avail_zinc_syr,
qty_avail_ors,qty_disp_zinc_ten,qty_disp_zinc_twe,qty_disp_zinc_syr,qty_disp_ors,
stockout_zinc,stockout_ors,diar_cases_seen,diar_cases_reff,diar_deaths_less_than_five,
added_by)
 values 
('"+field_visit_date+"','"+block_row_one+"','"+village_row_one+"','"+flw_category_row_one
+"','"+flw_name_row_one+"','"+flw_contact_no_row_one+"','"+aware_zinc_row_one+"','"+
aware_ors_row_one+"','"+past_zinc_row_one+"','"+past_ors_row_one+"','"+
qty_avail_zinc_ten_row_one+"','"+qty_avail_zinc_twe_row_one+"','"+
qty_avail_zinc_syr_row_one+"','"+qty_avail_ors_row_one+"','"+qty_disp_zinc_ten_row_one
+"','"+qty_disp_zinc_twe_row_one+"','"+qty_disp_zinc_syr_row_one+"','"+
qty_disp_ors_row_one+"','"+stockout_zinc_row_one+"','"+stockout_ors_row_one+
"','"+diar_cases_seen_row_one+"','"+diar_cases_reff_row_one+"','"+diar_deaths_row_one
+"','"+loginid+"')";
            System.out.println(sql);
            int x=stmt.executeUpdate(sql);
            if(x>0)
                SUCCESS="admin";

表结构如下:

 CREATE TABLE IF NOT EXISTS `field_visit_details` ( 
`field_visit_id` int(11) NOT NULL auto_increment,   
`visit_date` date NOT NULL,   
`block` varchar(100) NOT NULL,   
`village` varchar(100) NOT NULL,   
`flw_category` varchar(45) NOT NULL,   
`flw_name` varchar(100) NOT NULL,   
`flw_contact_no` varchar(13) NOT NULL,   
`aware_zinc` varchar(10) NOT NULL,   
`aware_ors` varchar(10) NOT NULL,   
`past_zinc` varchar(10) NOT NULL,   
`past_ors` varchar(10) NOT NULL,   
`qty_avail_zinc_ten` int(11) NOT NULL,   
`qty_avail_zinc_twe` int(11) NOT NULL,   
`qty_avail_zinc_syr` int(11) NOT NULL,   
`qty_avail_ors` int(11) NOT NULL,  
`qty_disp_zinc_ten` int(11) NOT NULL,   
`qty_disp_zinc_twe` int(11) NOT NULL,   
`qty_disp_zinc_syr` int(11) NOT NULL,   
`qty_disp_ors` int(11) NOT NULL,   
`stockout_zinc` varchar(11) NOT NULL,   
`stockout_ors` varchar(11) NOT NULL,   
`diar_cases_seen` int(11) NOT NULL,   
`diar_cases_reff` int(11) NOT NULL,   
`diar_deaths_less_than_five` int(11) NOT NULL,   
`added_by` varchar(100) NOT NULL,   
PRIMARY KEY  (`field_visit_id`)   ) 
ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

我在控制台中打印时的查询是:

     Insert into field_visit_details (visit_date,block,village,flw_category,flw_name,
flw_contact_no,aware_zinc,aware_ors,past_zinc,past_ors,qty_avail_zinc_ten,
qty_avail_zinc_twe,qty_avail_zinc_syr,qty_avail_ors,
    qty_disp_zinc_ten,qty_disp_zinc_twe,qty_disp_zinc_syr,qty_disp_ors,stockout_zinc,
    stockout_ors,diar_cases_seen,diar_cases_reff,diar_deaths_less_than_five,added_by)
     values
     ('2014-07-02','ASDASD','','asha','asd+','99','yes','yes','yes','yes',
    '12','12','12','12','12','12','12','12','yes','yes','12','12','12','admin')

但事实是查询正在将数据成功插入表中，但服务器仍显示上述错误.

But the thing is that the query is inserting the data successfully in the table but still server is showing the above error.

我什至直接在phpmyadmin中使用了上面打印的查询，并且运行良好，没有任何问题.

I even used the above printed query directly in phpmyadmin and worked well without any problem.

推荐答案

SQL语句中有语法异常.您发布的错误消息指向它.

You have a syntax exception in the SQL statement. The error message you have posted point to it.

因此，您需要更改SQL语句并修复语法错误.为此，您应该将其重写为使用 PraparedStatement ，例如示例

So, you need to change SQL statement and fix syntax errors. For this purpose you should rewrite it to use PraparedStatement like in the example JDBC PreparedStatement example – Insert a record.

在为预准备语句设置参数时，对数据库列类型使用 set 方法.

And use appropriate to the database column types set methods when setting parameters to the prepared statement.

这篇关于在Struts中引发关于SQL语法的MySQLSyntaxErrorException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！