今天,在小程序中做到排序功能时,莫名抛出了一个奇怪的错误。'@P3' 附近有语法错误。代码如下:
@Override
public List<Users> getUsers(int index,StringBuffer sb,int displayNum,String sortName,String sortDir) throws ClassNotFoundException, SQLException
{
int start=index;
int end=index+displayNum;
List<Users> list=new ArrayList<Users>();
String sql="select rownum=identity(int,1,1),UserID,UserName,rolename,Email,Name,Age,Gender,Telephone,[state] into #temp from users" +
" join [Role] " +
" on Users.RoleID=[Role].roleid " +
" select top 10 * ,total=(select COUNT(*) from #temp) from #temp " +
" where rownum>? and rownum<=? ";
if(!(sortName.equals("") || sortDir.equals("")))
{
sql+=" order by ? ?";
}
sql+=" drop table #temp";
Connection conn=prepare();
PreparedStatement ps=conn.prepareStatement(sql);
ps.setInt(1, start);
ps.setInt(2, end);
if(!(sortName.equals("") || sortDir.equals("")))
{
ps.setString(3, sortName);
ps.setString(4, sortDir);
}
ResultSet rs= ps.executeQuery();
String s="";
while(rs.next())
{
Users user=new Users();
user.setAge(rs.getString("Age"));
user.setEmail(rs.getString("Email"));
user.setGender(rs.getString("Gender"));
user.setName(rs.getString("Name"));
user.setState(rs.getString("state").charAt(0));
user.setTelephone(rs.getString("Telephone"));
user.setUserID(rs.getString("UserID"));
user.setUserName(rs.getString("UserName"));
user.setRoleName(rs.getString("rolename"));
list.add(user);
s=rs.getString("total");
}
sb.append(s);
conn.close();
ps.close();
rs.close();
return list;
}
经过一些简单的排除,我琢磨着应该是ps.setString(3,sortName)这一句出现了问题。
但是,令人不解的是,不管怎么分析,语句没有错,传入的参数也没有错。就是一执行就会报错。
于是,我把代码改成下面这样。
@Override
public List<Users> getUsers(int index,StringBuffer sb,int displayNum,String sortName,String sortDir) throws ClassNotFoundException, SQLException
{
int start=index;
int end=index+displayNum;
List<Users> list=new ArrayList<Users>();
String sql="select rownum=identity(int,1,1),UserID,UserName,rolename,Email,Name,Age,Gender,Telephone,[state] into #temp from users" +
" join [Role] " +
" on Users.RoleID=[Role].roleid " +
" select top 10 * ,total=(select COUNT(*) from #temp) from #temp " +
" where rownum>"+start+" and rownum<="+end+" ";
if(!(sortName.equals("") || sortDir.equals("")))
{
sql+=" order by "+sortName+" "+sortDir+" ";
}
sql+=" drop table #temp";
Connection conn=prepare();
Statement ps= conn.createStatement();
ResultSet rs= ps.executeQuery(sql);
String s="";
while(rs.next())
{
Users user=new Users();
user.setAge(rs.getString("Age"));
user.setEmail(rs.getString("Email"));
user.setGender(rs.getString("Gender"));
user.setName(rs.getString("Name"));
user.setState(rs.getString("state").charAt(0));
user.setTelephone(rs.getString("Telephone"));
user.setUserID(rs.getString("UserID"));
user.setUserName(rs.getString("UserName"));
user.setRoleName(rs.getString("rolename"));
list.add(user);
s=rs.getString("total");
}
sb.append(s);
conn.close();
ps.close();
rs.close();
return list;
}
有意思的是,这次执行却没有报任何错,完美运行。可是,这现象却让我不解了,为什么同样的语句,两种不同的写法会有如此差异呢?
后来,通过自己不断的缩小测试范围,以及网上查得的一些资料。总算是知道了,order by 后面不能用参数的形式传入值。只能用字符串拼接或其他方式。