Hibernate---Hql查询2---
2024-08-31 09:55:02
hibernate.cfg.xml配置： <?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <!-- Generated by MyEclipse Hibernate Tools. -->
<hibernate-configuration>
<session-factory>
<property name="dialect">
org.hibernate.dialect.MySQLDialect
</property>
<property name="connection.url">
jdbc:mysql://127.0.0.1/java?characterEncoding=utf-8
</property>
<property name="connection.username">root</property>
<property name="connection.password">root</property>
<property name="connection.driver_class">
com.mysql.jdbc.Driver
</property>
<property name="myeclipse.connection.profile">
com.mysql.jdbc.Driver
</property>
<property name="show_sql">true</property>
<property name="format_sql">true</property>
<mapping resource="com/etc/entity/Person.hbm.xml" />
<mapping resource="com/etc/entity/Sex.hbm.xml" />
</session-factory>
</hibernate-configuration>
=============================================
2个实体： package com.etc.entity; /**
* Person entity. @author MyEclipse Persistence Tools
*/ public class Person implements java.io.Serializable { // Fields private Integer pid;
private Sex sex; //n端持有1端的1个关联对象
private String pname; // Constructors /** default constructor */
public Person() {
} /** full constructor */
public Person(Sex sex, String pname) {
this.sex = sex;
this.pname = pname;
} // Property accessors public Integer getPid() {
return this.pid;
} public void setPid(Integer pid) {
this.pid = pid;
} public Sex getSex() {
return this.sex;
} public void setSex(Sex sex) {
this.sex = sex;
} public String getPname() {
return this.pname;
} public void setPname(String pname) {
this.pname = pname;
} @Override
public String toString() {
return "Person [pid=" + pid + ", pname=" + pname + ", sex=" + sex.getSexname() + "]";
} } ===============
package com.etc.entity; import java.util.HashSet;
import java.util.Set; /**
* Sex entity. @author MyEclipse Persistence Tools
*/ public class Sex implements java.io.Serializable { // Fields private Integer sexid;
private String sexname;
private Set persons = new HashSet(0); //1 端持有N端的1个集合对象 // Constructors /** default constructor */
public Sex() {
} @Override
public String toString() {
return "Sex [persons的数量:" + persons.size() + ", sexid=" + sexid + ", sexname="
+ sexname + "]";
} /** minimal constructor */
public Sex(String sexname) {
this.sexname = sexname;
} /** full constructor */
public Sex(String sexname, Set persons) {
this.sexname = sexname;
this.persons = persons;
} // Property accessors public Integer getSexid() {
return this.sexid;
} public void setSexid(Integer sexid) {
this.sexid = sexid;
} public String getSexname() {
return this.sexname;
} public void setSexname(String sexname) {
this.sexname = sexname;
} public Set getPersons() {
return this.persons;
} public void setPersons(Set persons) {
this.persons = persons;
} }
=============
Person.hbm.xml配置： <?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="com.etc.entity.Person" table="person" catalog="java" lazy="true">
<id name="pid" type="java.lang.Integer">
<column name="pid" />
<generator class="identity" />
</id>
<many-to-one name="sex" class="com.etc.entity.Sex" lazy="false" cascade="save-update">
<column name="sexid" not-null="true"/>
</many-to-one>
<property name="pname" type="java.lang.String">
<column name="pname" length="20" not-null="true" />
</property>
</class>
<query name="find_unusual_sex">
from Person where sex.sexname not in('男性','女性 ')
</query>
<sql-query name="find_max_count_sex">
select t1.*,sex.sexname from
(select count(1) 人口,sexid from person group by sexid) t1,sex
where t1.人口
=
(select max(人口) from
(select count(1) 人口,sexid from person group by sexid) t2
)
and t1.sexid = sex.sexid;
</sql-query>
</hibernate-mapping>
============================
Sex.hbm.xml配置： <?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="com.etc.entity.Sex" table="sex" catalog="java">
<id name="sexid" type="java.lang.Integer">
<column name="sexid" />
<generator class="identity" />
</id>
<property name="sexname" type="java.lang.String">
<column name="sexname" length="20" not-null="true" unique="true" />
</property>
<set name="persons" inverse="true" lazy="false" outer-join="false" cascade="">
<key>
<column name="sexid" not-null="true" />
</key>
<one-to-many class="com.etc.entity.Person" />
</set>
</class>
</hibernate-mapping>
==========================
测试类：hql查询.java package com.etc.test;
import java.util.List;
import java.util.Properties;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import com.etc.dao.HibernateSessionFactory;
import com.etc.entity.Person;
import com.etc.entity.Sex;
public class hql查询 {
static void 完整对象的查询()
{
//1 获取连接
Session s = HibernateSessionFactory.getSession();
//2 构造hql语句
String hql = "from Person where pname like '老%'";
//3 执行查询
List<Person> list = s.createQuery(hql).list();
//4 遍历结果
for(Person p:list)
{
System.out.println(p);
}
//5 关闭连接
HibernateSessionFactory.closeSession();
}
static void 部分字段的查询()
{
//1 获取连接
Session s = HibernateSessionFactory.getSession();
//2 构造hql语句。只返回部分字段
String hql = "select pid,pname from Person where pname like '老%'";
//3 执行查询
List<Object[]> list = s.createQuery(hql).list();
//4 遍历
for(Object[] fields:list)
{
//把每条记录的字段显示查询
for(Object field:fields)
{
System.out.print(field+"\t");
}
System.out.println();
}
//5 关闭连接
HibernateSessionFactory.closeSession(); }
static void 关联条件查询()
{
//1 获取连接
Session s = HibernateSessionFactory.getSession();
//a 显式关联(inner join关键字)
//2 构造hql语句
String hql = "from Person as p inner join p.sex as s " +
"where s.sexname like '男%'";
//3 执行查询.获得主对象和关联对象
List<Object[]> list = s.createQuery(hql).list();
//4 遍历结果
for(int i=0;i<list.size();i++)
{
Person p = (Person) list.get(i)[0];
System.out.println(p);
}
//b 隐式关联.推荐使用
/*2 构造hql语句。使用关联对象的属性作为约束条件。
String hql = "from Person where sex.sexname like '女%'";
//3 执行查询
List<Person> list = s.createQuery(hql).list();
//4 遍历结果
for(Person p:list)
{
System.out.println(p);
}
*/
//5 关闭连接
HibernateSessionFactory.closeSession();
}
static void 聚合查询()
{
//1 获取连接
Session s = HibernateSessionFactory.getSession();
//2 构造hql语句
/*
String hql = "select '记录的总数:'||cast(count(pid),string) from Person";
List<Object> list = s.createQuery(hql).list();
System.out.println(list.get(0));
*/
//分组查询，统计各个性别的人数
String hql = "select '人数:'||cast(count(pid),string),'性别名:'||sex.sexname from Person " +
"group by sex";
List<Object[]> list = s.createQuery(hql).list();
for(Object[] fields:list)
{
//把每条记录的字段显示查询
for(Object field:fields)
{
System.out.print(field+"\t");
}
System.out.println();
}
//5 关闭连接
HibernateSessionFactory.closeSession();
}
static void 分页查询()
{
//1 获取连接
Session s = HibernateSessionFactory.getSession();
//2 构造hql语句
String hql = "from Person where pname like '老%'";
//3 执行查询.第3到6条记录
Query q = s.createQuery(hql);
q.setFirstResult(2);//设置起点
q.setMaxResults(4);//设置条数
List<Person> list = q.list();
//4 遍历结果
for(Person p:list)
{
System.out.println(p);
}
//5 关闭连接
HibernateSessionFactory.closeSession();
}
static void 命名查询()
{
//1 获取连接
Session s = HibernateSessionFactory.getSession();
//3 执行查询
List<Person> list = s.getNamedQuery("find_unusual_sex").list();
//4 遍历结果
for(Person p:list)
{
System.out.println(p);
}
//5 关闭连接
HibernateSessionFactory.closeSession();
}
static void 动态参数查询() //支持？的hql语句
{
//1 获取连接
Session s = HibernateSessionFactory.getSession();
//a ?作为动态参数
//String hql = "from Person where pname like ?";
//b 取参数别名。
//c 设置查询属性
//d 关联查询
String hql = "from Person where sex =:sex";
//String hql = "from Person where pname like :name " +
// "and sex.sexname =:sex";
Query q = s.createQuery(hql); //a 按照位置指定
//q.setString(0,"%白%"); //与jdbc不同，从0开始
/*b 按照别名进行指定
q.setParameter("name", "%老%");
q.setParameter("sex", "女性");
*/
/*c 采用构造"查询属性"的方法
Properties pro = new Properties();
pro.setProperty("name","%老%");
pro.setProperty("sex", "女性");
q.setProperties(pro);
*/
//d 设置关联对象作为查询条件
Sex sex = new Sex();
sex.setSexid(2); //只有sex.主键作为查询条件。
q.setEntity("sex", sex);//将对象作为sex参数的查询条件
//3 执行查询
List<Person> list = q.list(); //4 遍历结果
for(Person p:list)
{
System.out.println(p);
}
//5 关闭连接
HibernateSessionFactory.closeSession();
}
static void sql的查询()
{
//1 获取连接
Session s = HibernateSessionFactory.getSession();
//2 构造sql语句。找出人数最多的性别名，及它的人数
SQLQuery q = (SQLQuery) s.getNamedQuery("find_max_count_sex");
//3 执行查询
List<Object[]> list = q.list();
//4 遍历
for(Object[] fields:list)
{
//把每条记录的字段显示查询
for(Object field:fields)
{
System.out.print(field+"\t");
}
System.out.println();
}
//5 关闭连接
HibernateSessionFactory.closeSession();
}
public static void main(String[] args)
{
//完整对象的查询();
//部分字段的查询();
//关联条件查询();
//聚合查询();
//分页查询();
//命名查询();
//动态参数查询();
sql的查询();
}
}
最新文章
- 对B+树与索引在MySQL中的认识
- codePrinter
- The model backing the <Database> context has changed since the database was created.
- 部分和问题(dfs)
- C# 弗洛伊德(Floyd)算法
- Centos7系统配置上的变化（三）为网络接口添加多IP
- Angular React 和 Vue的比较
- .NET反编译工具：de4dot
- RequestMapper
- flink连接hbase方法及遇到的问题
- 微信小程序代码构成
- IE10打印预览无反应
- iOS UI基础-9.1 UITableView 团购
- HDFS 入门介绍
- andorid 网格视图GridView
- Redis和RabbitMQ在项目中的使用
- PageObject&PageFactory
- 迷你MVVM框架 avalonjs 学习教程12、数据联动
- Adobe Dynamic Http Streaming的简单配置与实现 (FMS, HLS, HDS)
- 《GB/T 20988-2007：信息系统灾难恢复规范》[中]（国家质检总局 & 国标委）阅读笔记