在项目中使用了RowBounds进行分页,但是生成临时表的时候报表的别名错误,可是这不是我生成的sql啊:
Servlet.service() for servlet [dispatcherServlet] in context with path [/bos] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias
### The error may exist in file [D:\workspace\spring\.metadata\.plugins\org.eclipse.wst.server.core\tmp1\wtpwebapps\bos\WEB-INF\classes\mapper\StaffMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select * from ( select tmp_page.*, rownum row_id from ( select id, name, telephone, haspda, deltag, station, standard from bc_staff ) tmp_page where rownum <= ? ) where row_id > ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias] with root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at cn.jgsdx.gyx.dao.BaseDAO.selectPublicListPage(BaseDAO.java:54)
at cn.jgsdx.gyx.service.impl.StaffServiceImpl.selectPublicListPage(StaffServiceImpl.java:25)
at cn.jgsdx.gyx.controller.StaffController.selectPublicListPage(StaffController.java:31)在我的dao里面分页的代码 public <T> PageList<T> selectPublicListPage(String countSqlID, String sqlID, PageList<T> page, Object obj) {
// 查询总数
Integer total = this.getSqlSession().selectOne(countSqlID, obj);
if (total == null) {
total = 0;
}
//设置查询的条数范围
RowBounds rowBounds = new RowBounds(page.getFirstResult(), page.getPageSize());
// 查询列表信息
List<T> list = this.getSqlSession().selectList(sqlID, obj, rowBounds);
page.setTotal(total != null ? total : 0);
page.setRows(list);
page.setTotalPage((total + page.getPageSize() - 1) / page.getPageSize());
return page;
}service的实现:public PageList<Staff> selectPublicListPage(Integer page, Integer rows) {
PageList<Staff> pageList = new PageList<>();
pageList.setCurrentPage(page);
pageList.setPageSize(rows);
Map<String, Object> map = new HashMap<>();
//参数1:查询取派员总数的sqlid,参数二:查询的sqlid
return baseDAO.selectPublicListPage(BosSqlIdDefine.StaffSqlId.QUERY_STAFF_BY_COUNT, BosSqlIdDefine.StaffSqlId.QUERY_STAFF_BY_All, pageList, map);
}mapper文件:
<select id="queryStaffByCount" resultType="int">
select count(*) as total from
bc_staff
</select>
<select id="queryStaffByAll" resultMap="BaseResultMap">
select <include refid="Base_Column_List" /> from bc_staff
</select>
看了一下报错信息是表的别名的问题,可是这是mybatis自动生成的临时表,我怎么去控制这个过程,或者是哪里有问题,麻烦有人能帮忙吗,拜谢
Servlet.service() for servlet [dispatcherServlet] in context with path [/bos] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias
### The error may exist in file [D:\workspace\spring\.metadata\.plugins\org.eclipse.wst.server.core\tmp1\wtpwebapps\bos\WEB-INF\classes\mapper\StaffMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select * from ( select tmp_page.*, rownum row_id from ( select id, name, telephone, haspda, deltag, station, standard from bc_staff ) tmp_page where rownum <= ? ) where row_id > ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias] with root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at cn.jgsdx.gyx.dao.BaseDAO.selectPublicListPage(BaseDAO.java:54)
at cn.jgsdx.gyx.service.impl.StaffServiceImpl.selectPublicListPage(StaffServiceImpl.java:25)
at cn.jgsdx.gyx.controller.StaffController.selectPublicListPage(StaffController.java:31)在我的dao里面分页的代码 public <T> PageList<T> selectPublicListPage(String countSqlID, String sqlID, PageList<T> page, Object obj) {
// 查询总数
Integer total = this.getSqlSession().selectOne(countSqlID, obj);
if (total == null) {
total = 0;
}
//设置查询的条数范围
RowBounds rowBounds = new RowBounds(page.getFirstResult(), page.getPageSize());
// 查询列表信息
List<T> list = this.getSqlSession().selectList(sqlID, obj, rowBounds);
page.setTotal(total != null ? total : 0);
page.setRows(list);
page.setTotalPage((total + page.getPageSize() - 1) / page.getPageSize());
return page;
}service的实现:public PageList<Staff> selectPublicListPage(Integer page, Integer rows) {
PageList<Staff> pageList = new PageList<>();
pageList.setCurrentPage(page);
pageList.setPageSize(rows);
Map<String, Object> map = new HashMap<>();
//参数1:查询取派员总数的sqlid,参数二:查询的sqlid
return baseDAO.selectPublicListPage(BosSqlIdDefine.StaffSqlId.QUERY_STAFF_BY_COUNT, BosSqlIdDefine.StaffSqlId.QUERY_STAFF_BY_All, pageList, map);
}mapper文件:
<select id="queryStaffByCount" resultType="int">
select count(*) as total from
bc_staff
</select>
<select id="queryStaffByAll" resultMap="BaseResultMap">
select <include refid="Base_Column_List" /> from bc_staff
</select>
看了一下报错信息是表的别名的问题,可是这是mybatis自动生成的临时表,我怎么去控制这个过程,或者是哪里有问题,麻烦有人能帮忙吗,拜谢
解决方案 »
- jsp对于文件读写的小问题
- struts1+spring+hibernate,单元测试错误
- 一个关于web服务搭建的复杂问题 悬赏
- hibernate错误this instance does not yet exist as a row in the database
- 开发jsp,servlet在线写代码并运行其代码,遇到的一个棘手的问题
- (给100分!)Weblogic配置JDBC联接缓冲池总是出问题
- 部署Duke Bank老出错,大家帮帮看看
- 用Eclipse+lomboz开发EJB的数据库连接问题,请帮忙
- 哪里有eclipse(myEclipse)+tomcat+struts+Mysql 教程啊??
- eclipse 经典快捷键---加者有分
- web.xml加载bean.xml获取不到bean属性的值?也没有报错日志
- 同一段代码,在main正常运行,在servlet运行报错
<id column="id" jdbcType="VARCHAR" property="id" />
<result column="name" jdbcType="VARCHAR" property="name" />
<result column="telephone" jdbcType="VARCHAR" property="telephone" />
<result column="haspda" jdbcType="CHAR" property="haspda" />
<result column="deltag" jdbcType="CHAR" property="deltag" />
<result column="station" jdbcType="VARCHAR" property="station" />
<result column="standard" jdbcType="VARCHAR" property="standard" />
</resultMap>这是映射的结果集