初学者提问 关于Java与数据库连接造成的@P1附近语法有错误
异常情况是
com.microsoft.sqlserver.jdbc.SQLServerException: '@P1' 附近有语法错误。
Bean和DBUtil部分应该是没有问题的。下面的代码是查询语句以及Frame框架里查询还有上一个，下一个三个按钮的代码。
数据库就是简单的一个表 应该没有问题。
- Java code
package sel;import Bean.WaresBean;import db.DBUtil;import java.sql.Connection;import java.util.ArrayList;public class WaresSel { public ArrayList select(String s1,String s2,int row){ Connection con= DBUtil.getConnection(); java.sql.PreparedStatement ps=null; java.sql.ResultSet rs=null; ArrayList array=new ArrayList(); String sql="select *from war order by ? ?"; try{ ps=con.prepareStatement(sql); ps.setString(1, s1); ps.setString(2, s2); rs=ps.executeQuery(); rs.absolute(row); array.add(rs.getString(1)); array.add(rs.getString(2)); array.add(rs.getString(3)); array.add(rs.getString(4)); array.add(rs.getString(5)); }catch (java.sql.SQLException e){ e.printStackTrace(); }finally{ DBUtil.closeAll(con, ps, rs); } return array; } public int getRow(String s1,String s2){ Connection con= DBUtil.getConnection(); java.sql.PreparedStatement ps=null; java.sql.ResultSet rs=null; ArrayList array=new ArrayList(); String sql="select *from war order by ? ?"; try{ ps=con.prepareStatement(sql); ps.setString(1, s1); ps.setString(2, s2); rs=ps.executeQuery(); int row=rs.getRow(); return row; }catch (java.sql.SQLException e){ e.printStackTrace(); }finally{ DBUtil.closeAll(con, ps, rs); } return 0; }}- Java code
private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) { // TODO add your handling code here: String s1 = jComboBox1.getSelectedItem().toString(); String s2 = jComboBox2.getSelectedItem().toString(); if(s1.equals("商品名称")){ s1="waresName"; }else if(s1.equals("商品价格")){ s1="waresPrice"; }else if(s1.equals("折扣额")){ s1="rebate"; }else{ s1="surplusCount"; } if (s2.equals("降序")) { s2 = "desc"; } else { s1 = null; } ArrayList a2 = new ArrayList(); WaresSel ws = new WaresSel(); int row = ws.getRow(s1, s2) + 1; a2 = ws.select(s1, s2, row); if(a2.toString().length()<s1.length()){ javax.swing.JOptionPane.showMessageDialog(this, "没有下一个商品","错误",0); } jTextArea1.setText(a2.get(1).toString()); jTextField1.setText(a2.get(2).toString()); jTextField2.setText(a2.get(3).toString()); jTextField3.setText(a2.get(4).toString()); jTextArea2.setText(a2.get(5).toString()); } private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) { // TODO add your handling code here: String s1 = jComboBox1.getSelectedItem().toString(); String s2 = jComboBox2.getSelectedItem().toString(); if(s1.equals("商品名称")){ s1="waresName"; }else if(s1.equals("商品价格")){ s1="waresPrice"; }else if(s1.equals("折扣额")){ s1="rebate"; }else{ s1="surplusCount"; } if (s2.equals("降序")) { s2 = "desc"; } else { s1 = null; } ArrayList a1 = new ArrayList(); WaresSel ws = new WaresSel(); int row = ws.getRow(s1, s2) - 1; if (row <= 0) { javax.swing.JOptionPane .showMessageDialog(this, "没有上一个商品。", "错误", 0); return; } a1 = ws.select(s1, s2, row); jTextArea1.setText(a1.get(1).toString()); jTextField1.setText(a1.get(2).toString()); jTextField2.setText(a1.get(3).toString()); jTextField3.setText(a1.get(4).toString()); jTextArea2.setText(a1.get(5).toString()); } private void jMenuItem1ActionPerformed(java.awt.event.ActionEvent evt) { this.dispose(); } private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) { // TODO add your handling code here: String s1 = jComboBox1.getSelectedItem().toString(); String s2 = jComboBox2.getSelectedItem().toString(); ArrayList a = new ArrayList(); if(s1.equals("商品名称")){ s1="waresName"; }else if(s1.equals("商品价格")){ s1="waresPrice"; }else if(s1.equals("折扣额")){ s1="rebate"; }else{ s1="surplusCount"; } if (s2.equals("降序")) { s2 = "desc"; } else { s1 = null; } WaresSel ws = new WaresSel(); a =ws.select(s1, s2, 1); jTextArea1.setText(a.get(1).toString()); jTextField1.setText(a.get(2).toString()); jTextField2.setText(a.get(3).toString()); jTextField3.setText(a.get(4).toString()); jTextArea2.setText(a.get(5).toString()); }
[解决办法]
if (s2.equals("降序")) {
s2 = "desc";
} else {
s1 = null; 【这地方逻辑合理么？如果s2不为降序 s1 就为null ？是s2=null;吧？要么就s2=asc; 】
}
[解决办法]
是不能这样用的，兄弟。你对于prepareStatement的用法就是错误的。这种事对值的替换。
你可以用断点看看那个sql到底是什么样的，应该是select *from war order by @P1 @P2
所以这种情况下就动态拼接sql。
String sql="select *from war order by " + s1 + " " + s2;这样就可以了
[解决办法]
lz的意思是不是根据一个变量来查询升序还是降序吧？
然后返回一个实体bean的集合？ 我把WaresBean 这个当做你的实体类bean了
当做s1 是变量字段、 s2 是升序降序的条件
ArrayList<WaresBean> array = new ArrayList<WaresBean>();
StringBuffer sql = new StringBuffer();
sql.append( " select * from war where 1=1 " );
if(s1!=null && s1.equels("降序");){
sql.append(" and order by ? desc");
ps=con.prepareStatement(sql.toString);
ps.setString(1,s1);
}
else if(s1!=null && s1.equels("");){
sql.append(" and order by ?");
ps=con.prepareStatement(sql.toString);
ps.setString(1,s1);
}
rs=ps.executeQuery();
WaresBean wb = new WaresBean();
while(rs.next()){
wb.setXXXX(rs.getString("字段"));【wb的属性的set方法传对应的类型，rs.getXXXX有类型对应，需要几个就set几个属性进去】
//wb.setXXXX(rs.getString("字段"));
cag.add(wb);
}
//try catch 自己捕获、
return cag;