嗨,我有一个netbeans问题,我正在通过几个查询更新数据库,当一个查询在mysql查询浏览器中完美运行时,只有一个运行第二个查询就给出了语法问题。这是netbeans中的代码:
try{
String Query = "select nr,linkid,transdate,amount,type from astpay"
+ "where type = 'all' or 'cash';";
Statement ps = test.createStatement();
ResultSet rs = ps.executeQuery(Query);
//if there are payments that fit the criteria
if(rs.next()){
//while loop to generatepremuim for the cash payments
while(rs.next()){
System.out.println("Now to generate the premuim for the cash payments...");
Query = "insert into astpay" +
"(linkid,branchno,transdate,amount,refno,month,year,type)" +
"select a.linkid,a.branchno, a.transdate, a.amount, a.refno, a.month,a.year, p.paytype" +
"from astpay a, astpaytype p" +
"where(a.type ='all' or a.type ='cash' or a.type ='debit')" +
"and p.paytype = 'prem';";
ps = test.createStatement();
ps.execute(Query);
}
}else{
System.out.println("There was a an error generating the Premuims for Cash payments....");
}
}catch(SQLException exp){
System.err.println("Failed to execute the statement!");
System.err.println(exp.getMessage());
}
}
这是在mysql查询浏览器中运行的相同字符串的mysql代码:
`insert into astpay (linkid,branchno,transdate,amount,refno,month,year,type) select a.linkid,a.branchno, a.transdate, a.amount, a.refno, a.month,a.year, p.paytype from astpay a, astpaytype p where (a.type = 'all' or a.type ='cash' or a.type = 'debit') and p.paytype = 'prem';`
这是netbeans中返回的错误:
Failed to execute the statement!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 '= 'all' or 'cash'' at line 1
这里
我已经尝试了很多方法来使其正常工作,但我不知道哪里出了问题。我希望其他人能够提供帮助,我们将不胜感激。
使用ps.executeUpdate
代替ps.execute
。并在表之间添加联接astpay
和astpaytype
(或cross join
如果需要,则使用显式)
您的错误:
更改
where type = 'all' or 'cash'
至
where type = 'all' or type = 'cash'
要么
where type in ('all' ,'cash')
您的第二个错误:在where
以下位置添加空间:
Query = "insert into astpay" +
"(linkid,branchno,transdate,amount,refno,month,year,type)" +
"select a.linkid,a.branchno, a.transdate, a.amount, a.refno, a.month,a.year, p.paytype" +
"from astpay a, astpaytype p" +
" where(a.type ='all' or a.type ='cash' or a.type ='debit')" +
" and p.paytype = 'prem';";
您的代码产生“ ...从astpay a,astpaytype pwhere(a.type ='all'或...”
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句