/**
* 分页查询物料信息
* @param pageNo 第几页
* @param pageSize 每页多少条数据
* @param queryStr 条件
* @return pageModel对象
*/
public PageModel findAllItem(int pageNo, int pageSize, String queryStr) {
StringBuffer sbSql = new StringBuffer();
// //第一中方法
// sbSql.append("select a.item_no, a.item_name, a.spec, a.pattern, a.category as category_id, ")
// .append("b.name as category_name, a.unit as unit_id, c.name as unit_name ")
// .append("from t_items a, t_data_dict b, t_data_dict c ")
// .append("where a.category=b.id and a.unit=c.id "); //第二中方法
sbSql.append("select a.item_no, a.item_name, a.spec, a.pattern, a.category as category_id, ")
.append("(select b.name from t_data_dict b where a.category=b.id) as category_name, ")
.append("a.unit as unit_id, ")
.append("(select c.name from t_data_dict c where a.unit=c.id) as unit_name ")
.append("from t_items a ");
if (queryStr != null && queryStr.trim().length() != 0) {
sbSql.append("where a.item_no like '" + queryStr + "%' or a.item_name like '" + queryStr + "%'");
}
sbSql.append("order by a.item_no ")
.append("limit ")
.append((pageNo -1 ) * pageSize)
.append(", ")
.append(pageSize);
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
PageModel pageModel = null;
try {
conn = DB.getConn();
stmt = conn.createStatement();
rs = stmt.executeQuery(sbSql.toString());
List itemList = new ArrayList();
while (rs.next()) {
Item item = new Item();
item.setItemNo(rs.getString("item_no"));
item.setItemName(rs.getString("item_name"));
item.setSpec(rs.getString("spec"));
item.setPattern(rs.getString("pattern"));
//构造ItemCategory
ItemCategory ic = new ItemCategory();
ic.setId(rs.getString("category_id"));
ic.setName(rs.getString("category_name"));
item.setCategory(ic);
//构造ItemUnit
ItemUnit iu = new ItemUnit();
iu.setId(rs.getString("unit_id"));
iu.setName(rs.getString("unit_name"));
item.setUnit(iu);
itemList.add(item);
}
pageModel = new PageModel();
pageModel.setPageNo(pageNo);
pageModel.setPageSize(pageSize);
pageModel.setList(itemList);
pageModel.setTotalRecords(getTotalRecords(conn, queryStr));
}catch(SQLException e) {
//记录日志,log4j等......
e.printStackTrace();
throw new AppException("drp.database.item.error.findallitem");
}finally {
DB.closeRs(rs);
DB.closeStmt(stmt);
DB.closeConn(conn);
}
return pageModel;
}
请帮我解释下前面的sql语句,sbSql.append("select a.item_no, a.item_name, a.spec, a.pattern, a.category as category_id, ")
// .append("b.name as category_name, a.unit as unit_id, c.name as unit_name ")
// .append("from t_items a, t_data_dict b, t_data_dict c ")
// .append("where a.category=b.id and a.unit=c.id ");怎么会无缘无故跑出个a和b和c,这三个是从哪里来的,还有as
* 分页查询物料信息
* @param pageNo 第几页
* @param pageSize 每页多少条数据
* @param queryStr 条件
* @return pageModel对象
*/
public PageModel findAllItem(int pageNo, int pageSize, String queryStr) {
StringBuffer sbSql = new StringBuffer();
// //第一中方法
// sbSql.append("select a.item_no, a.item_name, a.spec, a.pattern, a.category as category_id, ")
// .append("b.name as category_name, a.unit as unit_id, c.name as unit_name ")
// .append("from t_items a, t_data_dict b, t_data_dict c ")
// .append("where a.category=b.id and a.unit=c.id "); //第二中方法
sbSql.append("select a.item_no, a.item_name, a.spec, a.pattern, a.category as category_id, ")
.append("(select b.name from t_data_dict b where a.category=b.id) as category_name, ")
.append("a.unit as unit_id, ")
.append("(select c.name from t_data_dict c where a.unit=c.id) as unit_name ")
.append("from t_items a ");
if (queryStr != null && queryStr.trim().length() != 0) {
sbSql.append("where a.item_no like '" + queryStr + "%' or a.item_name like '" + queryStr + "%'");
}
sbSql.append("order by a.item_no ")
.append("limit ")
.append((pageNo -1 ) * pageSize)
.append(", ")
.append(pageSize);
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
PageModel pageModel = null;
try {
conn = DB.getConn();
stmt = conn.createStatement();
rs = stmt.executeQuery(sbSql.toString());
List itemList = new ArrayList();
while (rs.next()) {
Item item = new Item();
item.setItemNo(rs.getString("item_no"));
item.setItemName(rs.getString("item_name"));
item.setSpec(rs.getString("spec"));
item.setPattern(rs.getString("pattern"));
//构造ItemCategory
ItemCategory ic = new ItemCategory();
ic.setId(rs.getString("category_id"));
ic.setName(rs.getString("category_name"));
item.setCategory(ic);
//构造ItemUnit
ItemUnit iu = new ItemUnit();
iu.setId(rs.getString("unit_id"));
iu.setName(rs.getString("unit_name"));
item.setUnit(iu);
itemList.add(item);
}
pageModel = new PageModel();
pageModel.setPageNo(pageNo);
pageModel.setPageSize(pageSize);
pageModel.setList(itemList);
pageModel.setTotalRecords(getTotalRecords(conn, queryStr));
}catch(SQLException e) {
//记录日志,log4j等......
e.printStackTrace();
throw new AppException("drp.database.item.error.findallitem");
}finally {
DB.closeRs(rs);
DB.closeStmt(stmt);
DB.closeConn(conn);
}
return pageModel;
}
请帮我解释下前面的sql语句,sbSql.append("select a.item_no, a.item_name, a.spec, a.pattern, a.category as category_id, ")
// .append("b.name as category_name, a.unit as unit_id, c.name as unit_name ")
// .append("from t_items a, t_data_dict b, t_data_dict c ")
// .append("where a.category=b.id and a.unit=c.id ");怎么会无缘无故跑出个a和b和c,这三个是从哪里来的,还有as
解决方案 »
- Exception in thread ""http-bio-80"-exec-1" java.lang.OutOfMemoryError: PermGen s
- tomcat jdbc 连接oracle 非常慢
- 我的小站,大家看有没有必要改成PHP???
- 请问关于servlet转发问题
- 没分了啊以后有了一定补上啊!一个上传的问题啊!希望得到大家的帮助啊!谢谢了
- 一个中文乱码问题,急!!请帮帮忙啊!!
- 复选框删除操作只执行最后一次,怎么解决
- DSOFrame自带保存按钮如何添加JAVASCRIPT方法
- 初学jsp ,老师布置下面的图片管理的作业,请大侠给点指点啊!
- 求一个新浪微博API调用开发大神!!!
- jsp如何生成新闻列表
- 如何在jsp网页上显示时间?
t_items a, t_data_dict b, t_data_dict c
给表取的别名