[Microsoft][ODBC Microsoft Access Driver] 语法错误 (操作符丢失) 在查询表达式 't1.userName limit 0' 10
java.sql.SQLException:[Microsoft][ODBCMicrosoftAccessDriver]语法错误(操作符丢失)在查询表达式't1.user...
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] 语法错误 (操作符丢失) 在查询表达式 't1.userName limit 0' 中。
public List<DormManager> dormManagerList(Connection con, PageBean pageBean, DormManager s_dormManager)throws Exception {
List<DormManager> dormManagerList = new ArrayList<DormManager>();
StringBuffer sb = new StringBuffer("SELECT * FROM t_dormManager t1 ORDER BY t1.userName");
if(StringUtil.isNotEmpty(s_dormManager.getName())) {
sb.append(" where t1.name like '%"+s_dormManager.getName()+"%'");
} else if(StringUtil.isNotEmpty(s_dormManager.getUserName())) {
sb.append(" where t1.userName like '%"+s_dormManager.getUserName()+"%'");
}
if(pageBean != null) {
sb.append(" limit "+pageBean.getStart()+","+pageBean.getPageSize());
}
PreparedStatement pstmt = con.prepareStatement(sb.toString());
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
DormManager dormManager=new DormManager();
dormManager.setDormManagerId(rs.getInt("dormManId"));
int dormBuildId = rs.getInt("dormBuildId");
dormManager.setDormBuildId(dormBuildId);
dormManager.setDormBuildName(DormBuildDao.dormBuildName(con, dormBuildId));
dormManager.setName(rs.getString("name"));
dormManager.setSex(rs.getString("sex"));
dormManager.setUserName(rs.getString("userName"));
dormManager.setTel(rs.getString("tel"));
dormManager.setPassword(rs.getString("password"));
dormManagerList.add(dormManager);
}
return dormManagerList;
} 展开
public List<DormManager> dormManagerList(Connection con, PageBean pageBean, DormManager s_dormManager)throws Exception {
List<DormManager> dormManagerList = new ArrayList<DormManager>();
StringBuffer sb = new StringBuffer("SELECT * FROM t_dormManager t1 ORDER BY t1.userName");
if(StringUtil.isNotEmpty(s_dormManager.getName())) {
sb.append(" where t1.name like '%"+s_dormManager.getName()+"%'");
} else if(StringUtil.isNotEmpty(s_dormManager.getUserName())) {
sb.append(" where t1.userName like '%"+s_dormManager.getUserName()+"%'");
}
if(pageBean != null) {
sb.append(" limit "+pageBean.getStart()+","+pageBean.getPageSize());
}
PreparedStatement pstmt = con.prepareStatement(sb.toString());
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
DormManager dormManager=new DormManager();
dormManager.setDormManagerId(rs.getInt("dormManId"));
int dormBuildId = rs.getInt("dormBuildId");
dormManager.setDormBuildId(dormBuildId);
dormManager.setDormBuildName(DormBuildDao.dormBuildName(con, dormBuildId));
dormManager.setName(rs.getString("name"));
dormManager.setSex(rs.getString("sex"));
dormManager.setUserName(rs.getString("userName"));
dormManager.setTel(rs.getString("tel"));
dormManager.setPassword(rs.getString("password"));
dormManagerList.add(dormManager);
}
return dormManagerList;
} 展开
1个回答
展开全部
ACCESS数据库Jet SQL引擎不支持"Limit"(Limit是MySQL独有的限定返回特定行数谓词),限定返回指定记录行数必须使用"Top"谓词。
语法:TOP n [PERCENT]
Top谓词可返回特定数目或百分比的记录,且这些记录将落在由 ORDER BY 子句指定的前面或后面的范围中。TOP必须放在SQL语句前面、紧跟SELECT关键字后面,而不是像Limit放在整个SQL语句的最后面。
因此题主的SQL语句拼接代码必须按照上述语法规则重新编写,最终返回的SQL语句字串应像下列例句才能在ACCESS数据库中正确运行:
SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear
= 1994
ORDER BY GradePointAverage DESC;
或
SELECT TOP 10 PERCENT
FirstName, LastName
FROM Students
WHERE
GraduationYear = 1994
ORDER BY GradePointAverage ASC;
注意:Top只能返回最前面若干记录行、而Limit支持从某行开始取若干行,Top支持按百分比取前若干行、而Limit不支持按百分比取前若干行。
语法:TOP n [PERCENT]
Top谓词可返回特定数目或百分比的记录,且这些记录将落在由 ORDER BY 子句指定的前面或后面的范围中。TOP必须放在SQL语句前面、紧跟SELECT关键字后面,而不是像Limit放在整个SQL语句的最后面。
因此题主的SQL语句拼接代码必须按照上述语法规则重新编写,最终返回的SQL语句字串应像下列例句才能在ACCESS数据库中正确运行:
SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear
= 1994
ORDER BY GradePointAverage DESC;
或
SELECT TOP 10 PERCENT
FirstName, LastName
FROM Students
WHERE
GraduationYear = 1994
ORDER BY GradePointAverage ASC;
注意:Top只能返回最前面若干记录行、而Limit支持从某行开始取若干行,Top支持按百分比取前若干行、而Limit不支持按百分比取前若干行。
追问
那我要连接的access数据库,应该怎么改才可以实现?
追答
将limit换成top并将有关拼接代码移到 “select ”关键字的后面,具体参阅相关编程语言规范
本回答被网友采纳
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询