在第1行“itid=1”附近使用正确语法的MySQL server版本

Question

SQL语法中有错误；查看与MySQL服务器版本相对应的手册，以了解第1行ITID=1附近使用的正确语法

当我试图为我的帐单应用程序生成帐单时，上面的错误将会出现。下面是我的应用程序的代码。请给我一些解决这个错误的方法..................

public void actionPerformed(ActionEvent ae)
{   
    String str1=(String)ae.getActionCommand();
    Object source = ae.getSource();
    if(source==home)
    {
        this.dispose();
        home t=new home();
        t.nn("","","","");
    }
    if(source==add)
    {
        name.setEditable(false);
        pho.setEditable(false);
        da.setEditable(false);

        try
        {
        Class.forName("com.mysql.jdbc.Driver");
        Connection c = DriverManager.getConnection("jdbc:mysql://localhost/billing", "root", "");
        Statement stm = c.createStatement();
        t=0;
        if(pho.getText().length()!=10&&pho.getText().length()!=0)
        jp.showMessageDialog(this,"Phone Number Must Have 10 Digits","INFORMATION",jp.ERROR_MESSAGE);
        else
        {   
                        ResultSet rs1 = stm.executeQuery("select * from bill where billid="+bid.getText()+"and itid="+id.getText());
            while(rs1.next())
            {   
                t=1;
            }
            if(t==0)
            {
                t=0;
            PreparedStatement ps  = c.prepareStatement("insert into bill values(?,?,?,?,?,?)");
            if((bid.getText()).length()!=0)
            ps.setString(1,bid.getText());
            else
            ps.setString(1,"");
            if((name.getText()).length()!=0)
            ps.setString(2,name.getText()); 
            else
            ps.setString(2,""); 
            if((pho.getText()).length()!=0)
            ps.setString(3,pho.getText());
            else
            ps.setString(3,"");
            if((da.getText()).length()!=0)
            ps.setString(4,da.getText());
            else
            ps.setString(4,"");
            if((id.getText()).length()!=0)
            ps.setString(5,id.getText());
            else
            ps.setString(5,"");
            if((co.getText()).length()!=0)
            ps.setString(6,co.getText());
            else
            ps.setString(6,"");

            ps.executeUpdate();
            //jp.showMessageDialog(this,"Record Inserted Successfully","SUCCESS",jp.INFORMATION_MESSAGE);

            it[cu]=Integer.parseInt(id.getText());
            coun[cu++]=Integer.parseInt(co.getText());

            }
            else
            {
                jp.showMessageDialog(this,"Sorry, Item ID is Already Entered","INFORMATION",jp.ERROR_MESSAGE);
                t=0;
            }

        }

        c.close();
        stm.close();
        }
        catch(ClassNotFoundException cnf)
        {
            System.out.println("Cnf Exception");
        }
        catch(SQLException sql)
        {
            jp.showMessageDialog(this,sql,"EXCEPTION",jp.ERROR_MESSAGE);
        }
    }
    if(source==reset)
    {
        id.setEditable(true);
        id.setText(null);
    }
    if(source==ge)
    {
        //this.dispose();
        res u1=new res();
        u1.n1(it,coun,cu);
    }
}   

CREATE TABLE IF NOT EXISTS `bill` (
  `billid` int(10) NOT NULL,
  `cus` varchar(20) NOT NULL,
  `pho` bigint(10) NOT NULL,
 `day` date NOT NULL,
 `itid` int(10) NOT NULL,
 `count` int(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Answer 1

永远不要忘记空格：

"select * from bill where billid="+bid.getText()+" and itid="+id.getText()

请使用PreparedStatement:

PreparedStatement lPreparedStatement = con.prepareStatement("select * from bill where billid=? and itid=?");
lPreparedStatement.setInt(1, Integer.valueOf(bid.getText()));
lPreparedStatement.setInt(2, Integer.valueOf(id.getText()));

此外：

1. 电话号码也应该是varchar。如果您的客户的电话号码是+49176/128383，您该怎么办？您还必须保存“+”和“/”，这对于数字数据类型是不可能的。
2. 对于日期，请使用setDate而不是setString（请参阅列day)
3. 对于int和Integer，请使用setInt而不是setstring