怎样用hibernate的hql查询查询成map或list

这里制作一个引子，具体内容比较多，而且hibernate文档里的hql篇写的很详细
可以这么用：
List list = getHibernateTemplate().find("select new map(t1.c1,t2.c3) from table1 t1 ,table2 t2 where t1.c1=t2.c2");
这样的话list里的每个元素都是一个map，每个map里包含两个元素
注意：这里的table1和table2都是class名并不是真的表名，毕竟这是hql。除了可以用map还还支持list和自定义的bean。

//HQL-Associations
String hql = "select s.name, p.name, p.price from Product p inner join p.supplier as s";
Query query = session.createQuery(hql);
List results = query.list();

//HQL-Delete
String hql = "delete from Product where name = :name";
Query query = session.createQuery(hql);
query.setString("name","Product 1");
int rowCount = query.executeUpdate();

//HQL-Function
String hql = "select min(product.price), max(product.price) from Product product";
Query query = session.createQuery(hql);
List results = query.list();

//HQL-Fetch Associations HQL Inner Join
String hql = "from Supplier s inner join fetch s.products as p";
Query query = session.createQuery(hql);
List results = query.list();

//HQL-Named Parameters
String hql = "from Product where price > :price";
Query query = session.createQuery(hql);
query.setDouble("price",2.0);
List results = query.list();
String hql = "from Product as product where product.supplier=:supplier";
Query query = session.createQuery(hql);
query.setEntity("supplier",supplier);
List results = query.list();

//HQL-Update
String hql = "update Supplier set name = :newName where name = :name";
Query query = session.createQuery(hql);
query.setString("name","Supplier Name 1");
query.setString("newName","s1");
int rowCount = query.executeUpdate();

//HQL-where
String hql = "from Product where price > 2.0 and name like 'P%'";
Query query = session.createQuery(hql);
List results = query.list();

//HQL-Map
String hql = " select new map(usr.name as userName, usr.password as password) from User usr";
Query query = session.createQuery(hql);
List list = query.list();
Map goods =(Map)list.get(0);

【注】
    String hql = " select new map(usr.name as userName, usr.password as password)　from com.jason.User usr";
    String hql = " select new map(usr.name as userName, usr.password as password) from com.jason.User usr";
由于from之前的空格，引起unexpected token: 　from



//select new

给一个构建函数：
public class Department(Department d, Integer employeeSize)

然后写成这样：
SELECT new Department(department, count(employee.id)) FROM .....

 

//查询其中几个字段,添加new map(),注意map里的m是小写的。也不需要导入包，这样通过query.list()出来的list里存放的不再是默认的Object数组了，而是map集合了   
120. String hql = " select new map(name,passwd) from Users";   
121. Query query = session.createQuery(hql);   
122. //默认查询出来的list里存放的是一个Object数组，但是在这里list里存放的不再是默认的Object数组了，而是Map集合了   
123. List<Map> list = query.list();   
124. for(Map user : list){   
125. //一条记录里所有的字段值都是map里的一个元素,key是字符串0,1,2,3....，value是字段值   
126. //如果将hql改为：String hql = " select new map(name as username,passwd as password) from Users";,那么key将不是字符串0,1,2...了，而是"username","password"了   
127. String name = (String)user.get("0");//get("0");是get(key),注意:0,1,2...是字符串，而不是整形   
128. String passwd = (String)user.get("1");   
129.   
130. System.out.println(name + " : " + passwd);   
131. }   
132. /**   
133. 输出结果为：   
134. name1 : password1   
135. name2 : password2   
136. name3 : password3   
137. */   
138.//查询其中几个字段,添加new map(),注意map里的m是小写的。也不需要导入包，这样通过query.list()出来的list里存放的不再是默认的Object数组了，而是map集合了   
139. String hql = " select new map(name,passwd) from Users";   
140. Query query = session.createQuery(hql);   
141. //默认查询出来的list里存放的是一个Object数组，但是在这里list里存放的不再是默认的Object数组了，而是Map集合了   
142. List<Map> list = query.list();   
143. for(Map user : list){   
144. //一条记录里所有的字段值都是map里的一个元素,key是字符串0,1,2,3....，value是字段值   
145. //如果将hql改为：String hql = " select new map(name as username,passwd as password) from Users";,那么key将不是字符串0,1,2...了，而是"username","password"了   
146. String name = (String)user.get("0");//get("0");是get(key),注意:0,1,2...是字符串，而不是整形   
147. String passwd = (String)user.get("1");   
148.   
149. System.out.println(name + " : " + passwd);   
150. }   
151. /**   
152. 输出结果为：   
153. name1 : password1   
154. name2 : password2   
155. name3 : password3   
156. */

 

评论

1 楼 txy821 2010-11-04  

//查询其中几个字段,添加new map(),注意map里的m是小写的。也不需要导入包，这样通过query.list()出来的list里存放的不再是默认的Object数组了，而是map集合了  
120. String hql = " select new map(name,passwd) from Users";  
121. Query query = session.createQuery(hql);  
122. //默认查询出来的list里存放的是一个Object数组，但是在这里list里存放的不再是默认的Object数组了，而是Map集合了  
123. List<Map> list = query.list();  
124. for(Map user : list){  
125. //一条记录里所有的字段值都是map里的一个元素,key是字符串0,1,2,3....，value是字段值  
126. //如果将hql改为：String hql = " select new map(name as username,passwd as password) from Users";,那么key将不是字符串0,1,2...了，而是"username","password"了  
127. String name = (String)user.get("0");//get("0");是get(key),注意:0,1,2...是字符串，而不是整形  
128. String passwd = (String)user.get("1");  
129.  
130. System.out.println(name + " : " + passwd);  
131. }  
132. /**  
133. 输出结果为：  
134. name1 : password1  
135. name2 : password2  
136. name3 : password3  
137. */  
138.//查询其中几个字段,添加new map(),注意map里的m是小写的。也不需要导入包，这样通过query.list()出来的list里存放的不再是默认的Object数组了，而是map集合了  
139. String hql = " select new map(name,passwd) from Users";  
140. Query query = session.createQuery(hql);  
141. //默认查询出来的list里存放的是一个Object数组，但是在这里list里存放的不再是默认的Object数组了，而是Map集合了  
142. List<Map> list = query.list();  
143. for(Map user : list){  
144. //一条记录里所有的字段值都是map里的一个元素,key是字符串0,1,2,3....，value是字段值  
145. //如果将hql改为：String hql = " select new map(name as username,passwd as password) from Users";,那么key将不是字符串0,1,2...了，而是"username","password"了  
146. String name = (String)user.get("0");//get("0");是get(key),注意:0,1,2...是字符串，而不是整形  
147. String passwd = (String)user.get("1");  
148.  
149. System.out.println(name + " : " + passwd);  
150. }  
151. /**  
152. 输出结果为：  
153. name1 : password1  
154. name2 : password2  
155. name3 : password3  
156. */