异常描述:
一个删除语句的功能按钮用Servlet+JDBC实现,点击按钮后报错:
com.microsoft.sqlserver.jdbc.SQLServerException: 必须声明标量变量 "@P0where"
原因:sql语句换行后,SQL语句换行时,把单词间的空格丢了。
解决办法:补上,丢失的空格,保持SQL的正确性。注:SQL语句换行后仍需保留单行时候的单词之间空格。
纠正之前代码:
String sql="update umdata set idumfolder=?"
+"where idumfolder=? and not id in"
+"(select max(id) from umdata where idumfolder=? group by sto )";
String sql="delete from umdata"
+"where idumfolder=? and not id in"
+"(select max(id) from umdata where idumfolder=? group by sto )";
以下是纠正后的代码:
public class EmployeeDAOImpl implements EmployeeDAO{
//20140129重复记录移至垃圾箱
public void moveAllRepeatToTrash(Employee e) throws Exception {
Connection conn = DbUtil.getConnection();
String sql="update umdata set idumfolder=? "
+"where idumfolder=? and not id in "
+"(select max(id) from umdata where idumfolder=? group by sto )";
PreparedStatement prep = conn.prepareStatement(sql);
prep.setString(1, "trash");
prep.setString(2, "out");
prep.setString(3, "out");
prep.executeUpdate();
DbUtil.close();
}
//20140129彻底删除重复记录
public void deleteAllRepeat(Employee e) throws Exception {
Connection conn = DbUtil.getConnection();
String sql="delete from umdata "
+"where idumfolder=? and not id in "
+"(select max(id) from umdata where idumfolder=? group by sto )";
PreparedStatement prep = conn.prepareStatement(sql);
prep.setString(1, "out");
prep.setString(2, "out");
prep.executeUpdate();
DbUtil.close();
}