StringBuffer sql=new StringBuffer();
sql.append("select customer.id,customer.COMPANY_FULLNAME,customer.COMPANY_SHORTNAME,customer.COMPANY_PROFESSION,a.display from cus_customer_info customer," +
"(select * from sys_datadic where parentid=" +
"(select id from sys_datadic t where t.value='companyProfession')) a" +
" where customer.company_profession = a.value");
if(StringUtils.isNotBlank(value)){
sql.append(" and customer.company_fullname like '%"+value+"%'");
}
sql.append(" order by customer.click_num desc, customer.create_time desc");
Connection conn=this.getSession().connection();
List<CustomerInfo> list=new ArrayList<CustomerInfo>();
try {
Statement state=conn.createStatement();
ResultSet rs=state.executeQuery(sql.toString());
while(rs.next()){
CustomerInfo info=new CustomerInfo();
info.setId(rs.getString("id"));
info.setCompanyFullname(rs.getString("COMPANY_FULLNAME"));
info.setCompanyShortname(rs.getString("COMPANY_SHORTNAME"));
info.setDisplay(rs.getString("display"));
info.setCompanyProfession(Short.valueOf(rs.getString("COMPANY_PROFESSION")));
list.add(info);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
这是jdbc方式来操作数据库。
后来查询资料,得到了其他的方式:
this.getSession().createSQLQuery("").addEntity(CustomerInfo.class)
这种方式指定接收的实体bean为CustomerInfo.class
但是上边的sql中的a.display这个列是无法插入到CustomerInfo.class中的display属性中的
所以还可以采用这个方法:
List list = this.getSession().createSQLQuery("").setResultTransformer(
Transformers.ALIAS_TO_ENTITY_MAP).list();
此时,每个Object可以转换成一个Map
Map map = (Map) list.get(i);
map.get("id");