异常情况是
com.microsoft.sqlserver.jdbc.SQLServerException: '@P1' 附近有语法错误。Bean和DBUtil部分应该是没有问题的。下面的代码是查询语句以及Frame框架里查询还有上一个,下一个三个按钮的代码。
数据库就是简单的一个表 应该没有问题。
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;
}
} 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()); }
com.microsoft.sqlserver.jdbc.SQLServerException: '@P1' 附近有语法错误。Bean和DBUtil部分应该是没有问题的。下面的代码是查询语句以及Frame框架里查询还有上一个,下一个三个按钮的代码。
数据库就是简单的一个表 应该没有问题。
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;
}
} 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()); }
改了后异常变成了
com.microsoft.sqlserver.jdbc.SQLServerException: 由 ORDER BY 编号 1 标识的 SELECT 项包含一个变量,作为标识列位置的表达式的一部分。按照引用列名的表达式排序时,只允许使用变量。
语句是 String sql="select *from war order by ?, ?";
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;
}
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());
跳到指定行,你的statement创建的时候要带参数,
你参考下statement的其他构造方法
不懂这个
s2 = "desc";
} else {
s1 = null; 【这地方逻辑合理么?如果s2不为降序 s1 就为null ?是s2=null;吧?要么就s2=asc; 】
}
下面写法是合理的(mysql)
select * from xxx ORDER BY xxx,yyy DESC
select * from xxx ORDER BY xxx DESC
select * from xxx ORDER BY xxx,NULL
看看你sql最终拼的正确吗
你可以用断点看看那个sql到底是什么样的,应该是select *from war order by @P1 @P2
所以这种情况下就动态拼接sql。
String sql="select *from war order by " + s1 + " " + s2;这样就可以了
然后返回一个实体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;
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;