mysql - netbeans mysql syntax Error at line 1 and no error in mysql 查询浏览器

Question

您好，我有一个 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

这里

我已经尝试了很多方法来让它工作，但我不知道哪里出错了。我希望其他人可以提供帮助，我们将不胜感激。

Answer 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';";

您的代码生成“...from astpay a, astpaytype pwhere(a.type ='all' or ...”