public ArrayList groupRecurUserNoDelFromDB(long groupID,
int startIndex,
int numResults,String UserName) {
ArrayList list=new ArrayList();
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
String sql = "SELECT * FROM " +
"( SELECT A.*, rownum r FROM " +
"( SELECT u.userid FROM tuser u," +
"( select groupid,rownum od from" +
"( select * from tgroup t WHERE isuse = 1 ) a " +
"connect by prior a.groupid = a.parentid start with a.groupid = ? " +
"order siblings by a.orderid ) g " +
"WHERE isuse = 1 and u.groupid = g.groupid " +
"and u.name like ? " +
"order by od,u.orderid ) A " +
"where rownum <= ? ) B " +
"WHERE r >?";
/*
* String sql1 = select * from tgroup t ,tgroup g
where t.parentid=g.groupid start with g.groupid=? order by t.orderid
*/ //CachedRowSet crs=null;
try{
con=ConnectionManager.getConnection();
pstmt=con.prepareStatement(sql);
pstmt.setLong(1,groupID);
pstmt.setString(2,"%"+UserName==null?"":UserName+"%");
pstmt.setInt(3,numResults);
pstmt.setInt(4,startIndex);
rs=pstmt.executeQuery();
while(rs.next()){
User user = new User();
user.setUserId(rs.getLong("userid"));
user.setGroupID(rs.getLong("groupid"));
user.setUserName(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setCanSendSMS(rs.getBoolean("cansendsms"));
user.setEmail(rs.getString("email"));
user.setGender(rs.getBoolean("gender"));
user.setHPhone(rs.getString("hphone"));
user.setIconID(rs.getString("iconid"));
user.setLoginIP(rs.getString("loginip"));
user.setMobile(rs.getString("mobile"));
user.setName(rs.getString("name"));
user.setPersonalSign(rs.getString("personalsign"));
user.setPhs(rs.getString("phs"));
user.setUse(rs.getBoolean("isuse"));
user.setOrderId(rs.getDouble("orderid"));
list.add(user);
System.out.println(list);
}
}catch(SQLException e){
logger.warn(e);
System.out.println(e);
}finally{
ConnectionManager.closeObject(con, pstmt, rs);
}
return list;
}
SELECT * FROM (SELECT A.*, rownum r FROM (SELECT u.userid FROM tuser u,(select groupid,rownum od from ( select * from tgroup t WHERE ISUSE = 1 ) a connect by prior a.groupid = a.parentid start with a.groupid = ? order siblings by a.orderid) g WHERE ISUSE = 1 and u.groupid = g.groupid and u.name like ? order by od,u.orderid)A where rownum <= ?) B WHERE r >?";总是提示列名无效groupid..102
start..0
s+r..15
query..
java.sql.SQLException: 列名无效
帮看看like那里是否有错pstmt.setString(2,"%"+UserName==null?"":UserName+"%");