java：Hibernate框架3（使用Myeclipse逆向工程生成实体和配置信息，hql语句各种查询（使用hibernate执行原生SQL语句，占位符和命名参数，封装Vo查询多个属性，聚合函数，链接查询，命名查询），Criteria）
1.使用Myeclipse逆向工程生成实体和配置信息：
步骤1:配置MyEclipse Database Explorer：
步骤2:为项目添加hibernate的依赖：
此处打开后，点击next进入下个页面：
此处选择，主键自增，然后点击Finish：
2.hql语句各种查询：
Hibernate.cfg.xml:
<?xml version='1.0' encoding='UTF-8'?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd"> <!-- Generated by MyEclipse Hibernate Tools. --> <hibernate-configuration> <session-factory> <property name="myeclipse.connection.profile">udisk</property> <property name="connection.url"> jdbc:mysql://localhost:3306/udisk </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="dialect"> org.hibernate.dialect.MySQLDialect </property> <mapping resource="cn/zzsxt/entity/Fileinfo.hbm.xml" /> <mapping resource="cn/zzsxt/entity/Userinfo.hbm.xml" /> </session-factory> </hibernate-configuration>
Fileinfo:
package cn.zzsxt.entity; import java.sql.Timestamp; /** * Fileinfo entity. @author MyEclipse Persistence Tools */ public class Fileinfo implements java.io.Serializable { // Fields private Integer fileId; private Userinfo userinfo; private String fileName; private Timestamp uploadTime; private Long fileSize; private String fileCode; private String filePath; // Constructors /** default constructor */ public Fileinfo() { } /** full constructor */ public Fileinfo(Userinfo userinfo, String fileName, Timestamp uploadTime, Long fileSize, String fileCode, String filePath) { this.userinfo = userinfo; this.fileName = fileName; this.uploadTime = uploadTime; this.fileSize = fileSize; this.fileCode = fileCode; this.filePath = filePath; } // Property accessors public Integer getFileId() { return this.fileId; } public void setFileId(Integer fileId) { this.fileId = fileId; } public Userinfo getUserinfo() { return this.userinfo; } public void setUserinfo(Userinfo userinfo) { this.userinfo = userinfo; } public String getFileName() { return this.fileName; } public void setFileName(String fileName) { this.fileName = fileName; } public Timestamp getUploadTime() { return this.uploadTime; } public void setUploadTime(Timestamp uploadTime) { this.uploadTime = uploadTime; } public Long getFileSize() { return this.fileSize; } public void setFileSize(Long fileSize) { this.fileSize = fileSize; } public String getFileCode() { return this.fileCode; } public void setFileCode(String fileCode) { this.fileCode = fileCode; } public String getFilePath() { return this.filePath; } public void setFilePath(String filePath) { this.filePath = filePath; } @Override public String toString() { return "Fileinfo [fileId=" + fileId + ", fileName=" + fileName + ", uploadTime=" + uploadTime + ", fileSize=" + fileSize + ", fileCode=" + fileCode + ", filePath=" + filePath + "]"; } }
Fileinfo.hbm.xml:
<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"> <!-- Mapping file autogenerated by MyEclipse Persistence Tools --> <hibernate-mapping> <class name="cn.zzsxt.entity.Fileinfo" table="fileinfo" catalog="udisk"> <id name="fileId" type="java.lang.Integer"> <column name="fileId" /> <generator class="native" /> </id> <many-to-one name="userinfo" class="cn.zzsxt.entity.Userinfo" fetch="select" lazy="false"> <column name="userId" /> </many-to-one> <property name="fileName" type="java.lang.String"> <column name="fileName" length="100" /> </property> <property name="uploadTime" type="java.sql.Timestamp"> <column name="uploadTime" length="19" /> </property> <property name="fileSize" type="java.lang.Long"> <column name="fileSize" /> </property> <property name="fileCode" type="java.lang.String"> <column name="fileCode" /> </property> <property name="filePath" type="java.lang.String"> <column name="filePath" /> </property> </class> </hibernate-mapping>
Userinfo:
package cn.zzsxt.entity; import java.util.HashSet; import java.util.Set; /** * Userinfo entity. @author MyEclipse Persistence Tools */ public class Userinfo implements java.io.Serializable { // Fields private Integer userId; private String userName; private String userPass; private Integer userType; private Set fileinfos = new HashSet(0); // Constructors /** default constructor */ public Userinfo() { } /** full constructor */ public Userinfo(String userName, String userPass, Integer userType, Set fileinfos) { this.userName = userName; this.userPass = userPass; this.userType = userType; this.fileinfos = fileinfos; } // Property accessors public Integer getUserId() { return this.userId; } public void setUserId(Integer userId) { this.userId = userId; } public String getUserName() { return this.userName; } public void setUserName(String userName) { this.userName = userName; } public String getUserPass() { return this.userPass; } public void setUserPass(String userPass) { this.userPass = userPass; } public Integer getUserType() { return this.userType; } public void setUserType(Integer userType) { this.userType = userType; } public Set getFileinfos() { return this.fileinfos; } public void setFileinfos(Set fileinfos) { this.fileinfos = fileinfos; } }
Userinfo.hbm.xml:
<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"> <!-- Mapping file autogenerated by MyEclipse Persistence Tools --> <hibernate-mapping> <class name="cn.zzsxt.entity.Userinfo" table="userinfo" catalog="udisk"> <id name="userId" type="java.lang.Integer"> <column name="userId" /> <generator class="native" /> </id> <property name="userName" type="java.lang.String"> <column name="userName" length="50" /> </property> <property name="userPass" type="java.lang.String"> <column name="userPass" length="50" /> </property> <property name="userType" type="java.lang.Integer"> <column name="userType" /> </property> <set name="fileinfos" inverse="true"> <key> <column name="userId" /> </key> <one-to-many class="cn.zzsxt.entity.Fileinfo" /> </set> </class> <query name="getAllUsers"> <!-- hql语句 --> <![CDATA[ from Userinfo ]]> </query> </hibernate-mapping>
FileinfoVo:
package cn.zzsxt.vo; public class FileinfoVo { private int fileId; private String fileName; private long fileSize; public FileinfoVo(int fileId, String fileName, long fileSize) { super(); this.fileId = fileId; this.fileName = fileName; this.fileSize = fileSize; } public FileinfoVo() { super(); // TODO Auto-generated constructor stub } public int getFileId() { return fileId; } public void setFileId(int fileId) { this.fileId = fileId; } public String getFileName() { return fileName; } public void setFileName(String fileName) { this.fileName = fileName; } public long getFileSize() { return fileSize; } public void setFileSize(long fileSize) { this.fileSize = fileSize; } }
A.使用hibernate执行原生SQL语句(不推荐):
package cn.zzsxt.demo; import java.util.List; import org.hibernate.SQLQuery; import org.hibernate.Session; import cn.zzsxt.entity.Userinfo; import cn.zzsxt.util.HibernateSessionFactory; /** * 使用hibernate执行原生SQL语句(不推荐) * 使用SQLQuery执行sql语句 * 使用Query执行HQL语句 * * @author Think * */ public class TestNativeSQL { public static void main(String[] args) { String sql="select * from userinfo"; Session session = HibernateSessionFactory.getSession(); SQLQuery sqlQuery = session.createSQLQuery(sql); sqlQuery.addEntity(Userinfo.class);//将查询出的记录封装成Userinfo类型的对象 List<Userinfo> list = sqlQuery.list(); for (Userinfo userinfo : list) { System.out.println(userinfo.getUserId()+"---"+userinfo.getUserName()); } HibernateSessionFactory.closeSession(); } }
B.hql基本语句：
package cn.zzsxt.demo; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; import cn.zzsxt.entity.Userinfo; import cn.zzsxt.util.HibernateSessionFactory; public class TestQuery1 { public static void main(String[] args) { //String sql="select * from userinfo "; //String hql="select u from Userinfo u"; // String hql="from Userinfo"; // String sql="select * from userinfo where userType=1"; // String hql="from Userinfo where userType=1"; // String sql="select * from userinfo where userId between 1 and 5"; // String hql="from Userinfo where userId between 1 and 5"; // String sql="select * from userinfo where userId in(3,4)"; // String hql="from Userinfo where userId in(3,4)"; // String sql="select * from userinfo where userName like '%zhang%'"; String hql="from Userinfo where userName like '%zhang%'"; Session session = HibernateSessionFactory.getSession(); //创建Query对象，类似与PreparedStatement对象 Query query = session.createQuery(hql); List<Userinfo> list = query.list(); for (Userinfo userinfo : list) { System.out.println(userinfo.getUserId()+"--"+userinfo.getUserName()+"---"+userinfo.getUserType()); } HibernateSessionFactory.closeSession(); } }
C.占位符和命名参数：
package cn.zzsxt.demo; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; import cn.zzsxt.entity.Userinfo; import cn.zzsxt.util.HibernateSessionFactory; /** * 占位符(?):使用setXxx方法为占位符绑定值，占位符的下标从0开始。 * 命名参数:使用自定义名称来代替占位符(?)，优点:可以解决占位符过多而导致的绑定时出现的错乱现象。 * 定义命名参数:使用冒号(:)开头+自定义的参数的名称 * from Userinfo where userName=:userName and userPass=:userPass * * @author Think * */ public class TestQuery2 { public static void main(String[] args) { Session session = HibernateSessionFactory.getSession(); // String sql="select * from userinfo where userName=? and userPass=?"; // String hql="from Userinfo where userName=? and userPass=?"; // //创建Query对象，类似与PreparedStatement对象 // Query query = session.createQuery(hql); // //为占位符?绑定参数的值 query.setXxx(占位符的下标,绑定的值); 与PreparedStatement占位符的下标不同(PreparedStatement占位符的下标从1开始，Query占位符的下标从0开始) // query.setString(0, "zhangsan"); // query.setString(1, "81dc9bdb52d04dc20036dbd8313ed055"); String hql="from Userinfo where userName=:userName and userPass=:userPass"; Query query = session.createQuery(hql); //为命名参数绑定值,去掉冒号 query.setString("userName", "zhangsan"); query.setString("userPass", "81dc9bdb52d04dc20036dbd8313ed055"); //命名参数 List<Userinfo> list = query.list(); for (Userinfo userinfo : list) { System.out.println(userinfo.getUserId()+"--"+userinfo.getUserName()+"---"+userinfo.getUserType()); } HibernateSessionFactory.closeSession(); } }
D：查询实体类中多个属性，需要封装Vo：
package cn.zzsxt.demo; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; import cn.zzsxt.util.HibernateSessionFactory; import cn.zzsxt.vo.FileinfoVo; /** * Object[] objs = new Object[3]; objs[0]=2; objs[1]="ajax原理.png"; objs[2]=85356; list.add(objs); Object[] objs2 = new Object[3]; objs2[0]=3; objs2[1]="MD5加密.txt"; objs2[2]=85356; list.add(objs2); * 查询部分列数据:默认将每一行数据封装成Object[]进行返回 * * @author Think * */ public class TestQuery3 { /** * 查询部分列，返回将每行记录封装成Object[] */ public static void test1(){ Session session = HibernateSessionFactory.getSession(); // String sql="select fileId,fileName,fileSize from fileinfo"; String hql="select fileId,fileName,fileSize from Fileinfo"; Query query = session.createQuery(hql); //错误:java.lang.ClassCastException // List<Fileinfo> list = query.list(); // for (Fileinfo fileinfo : list) { // System.out.println(fileinfo.getFileId()+"---"+fileinfo.getFileName()+"---"+fileinfo.getFileSize()); // } //查询部分列其返回值为list，list中包含是一个Object[] List<Object[]> list = query.list(); for (Object[] objects : list) { System.out.println(objects[0]+"---"+objects[1]+"---"+objects[2]); } HibernateSessionFactory.closeSession(); } /** * 将查询出的结果封装成VO(推荐) */ public static void test2(){ Session session = HibernateSessionFactory.getSession(); String hql="select new cn.zzsxt.vo.FileinfoVo(fileId,fileName,fileSize) from Fileinfo"; Query query = session.createQuery(hql); List<FileinfoVo> list = query.list(); for (FileinfoVo fileinfoVo : list) { System.out.println(fileinfoVo.getFileId()+"--"+fileinfoVo.getFileName()+"--"+fileinfoVo.getFileSize()); } } public static void main(String[] args) { test2(); } }
E.聚合函数：
package cn.zzsxt.demo; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; import cn.zzsxt.entity.Userinfo; import cn.zzsxt.util.HibernateSessionFactory; /* * 聚合函数(统计函数): * max():最大值 * min():最小值 * avg():平均值 * sum():求和 * count():计数 * 排序：order by 属性 asc|desc * asc:升序(默认) * desc：降序 * 分组:group by * 分页: * setFirstResult() * setMaxResult() */ public class TestQuery4 { /** * 聚合函数 * uniqueResult():执行查询结果唯一的方法 */ public static void test1(){ Session session = HibernateSessionFactory.getSession(); // String sql="select count(*) from userinfo"; // String hql="select count(u) from Userinfo u"; String hql="select max(u.userId) from Userinfo u"; Query query = session.createQuery(hql); // List list = query.list(); // System.out.println(list.get(0)); //当结果唯一时可以使用uniqueResult()方法 Object obj = query.uniqueResult(); System.out.println(obj); HibernateSessionFactory.closeSession(); } /** * 排序：order by 属性 asc|desc * asc:升序(默认) * desc：降序 */ public static void test2(){ Session session = HibernateSessionFactory.getSession(); String hql="from Userinfo order by userId desc"; Query query = session.createQuery(hql); List<Userinfo> list = query.list(); for (Userinfo userinfo : list) { System.out.println(userinfo.getUserId()+"--"+userinfo.getUserName()); } HibernateSessionFactory.closeSession(); } /** * 分组: group by 属性 * 需求:查询用户类型的用户数量和用户类型 */ public static void test3(){ Session session = HibernateSessionFactory.getSession(); String hql="select count(u),u.userType from Userinfo u group by u.userType"; Query query = session.createQuery(hql); List<Object[]> list= query.list(); for (Object[] objects : list) { System.out.println(objects[0]+"---"+objects[1]); } HibernateSessionFactory.closeSession(); } /** * 分页: * setFirstResult(int beginRow):设置起始位置=(当前页-1)*分页单位 * setMaxResult(int pageSize):设置分页单位 */ public static void test4(){ Session session = HibernateSessionFactory.getSession(); String hql="from Userinfo "; Query query = session.createQuery(hql); //设置起始位置=(当前页-1)*分页单位; query.setFirstResult(2); //设置分页单位 query.setMaxResults(2); List<Userinfo> list = query.list(); for (Userinfo userinfo : list) { System.out.println(userinfo.getUserId()+"---"+userinfo.getUserName()); } HibernateSessionFactory.closeSession(); } public static void main(String[] args) { test4(); } }
F.链接查询，左右连接查询：
package cn.zzsxt.demo; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; import cn.zzsxt.entity.Fileinfo; import cn.zzsxt.util.HibernateSessionFactory; /* *链接查询: *1.等值链接查询:where子语句 *2.链接查询:inner join(内连接)/left join(左连接) /right join(右连接) *子查询:将一个查询语句的结果作为另外一个查询语句的条件。所有用链接查询能实现的功能都可以使用子查询实现，但反之则不一定成立。 * */ public class TestQuery5 { /** * 等值链接查询:where子语句 */ public static void test1(){ Session session = HibernateSessionFactory.getSession(); // String sql="SELECT f.* FROM userinfo u,fileinfo f WHERE u.userId=f.userId AND u.userName='zhangsan'"; String hql="select f from Userinfo u,Fileinfo f where u.userId=f.userinfo.userId and u.userName='zhangsan'"; Query query = session.createQuery(hql); List<Fileinfo> list = query.list(); for (Fileinfo fileinfo : list) { System.out.println(fileinfo.getFileId()+"---"+fileinfo.getFileName()); } HibernateSessionFactory.closeSession(); } /** * 使用内连接查询:inner join */ public static void test2(){ Session session = HibernateSessionFactory.getSession(); // String sql="SELECT f.* FROM userinfo u INNER JOIN fileinfo f ON(u.userId=f.userId) WHERE u.userName='zhangsan'"; String hql="select f from Userinfo u inner join Fileinfo f on(u.userId=f.userinfo.userId) where u.userName='zhangsan'"; Query query = session.createQuery(hql); List<Fileinfo> list = query.list(); for (Fileinfo fileinfo : list) { System.out.println(fileinfo.getFileId()+"---"+fileinfo.getFileName()); } HibernateSessionFactory.closeSession(); } /** * 左连接: left join * 右连接: right join */ public static void test3(){ Session session = HibernateSessionFactory.getSession(); // String sql="SELECT u.userName,f.* FROM userinfo u LEFT JOIN fileinfo f ON(u.userId=f.userId) "; // String hql="SELECT u.userName,f FROM Userinfo u LEFT JOIN Fileinfo f ON(u.userId=f.userinfo.userId) "; // String sql="SELECT u.userName,f.* FROM userinfo u RIGHT JOIN fileinfo f ON(u.userId=f.userId) "; String hql="SELECT u.userName,f FROM Userinfo u RIGHT JOIN Fileinfo f ON(u.userId=f.userinfo.userId) "; Query query = session.createQuery(hql); List<Object[]> list = query.list(); for (Object[] objects : list) { System.out.println(objects[0]+"---"+objects[1]); } HibernateSessionFactory.closeSession(); } /** * 子查询: * 查询用户名为zhangsan的所上传的文件信息 */ public static void test4(){ Session session = HibernateSessionFactory.getSession(); // String sql="SELECT * FROM fileinfo WHERE userId=(SELECT userId FROM userinfo WHERE username='zhangsan')"; String hql="SELECT f FROM Fileinfo f WHERE f.userinfo.userId=(SELECT userId FROM Userinfo WHERE userName='zhangsan')"; Query query = session.createQuery(hql); List<Fileinfo> list = query.list(); for (Fileinfo fileinfo : list) { System.out.println(fileinfo.getFileId()+"---"+fileinfo.getFileName()); } HibernateSessionFactory.closeSession(); } public static void main(String[] args) { test4(); } }
G：命名查询：
package cn.zzsxt.demo; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; import cn.zzsxt.entity.Userinfo; import cn.zzsxt.util.HibernateSessionFactory; /* *命名查询:将hql查询语句配置到映射文件中，通过名称获取配置信息 */ public class TestQuery6 { public static void test1(){ Session session = HibernateSessionFactory.getSession(); Query query = session.getNamedQuery("getAllUsers"); List<Userinfo> list = query.list(); for (Userinfo userinfo : list) { System.out.println(userinfo.getUserId()+"---"+userinfo.getUserName()); } HibernateSessionFactory.closeSession(); } public static void main(String[] args) { test1(); } }
H.Criteria:
package cn.zzsxt.demo2; import java.util.ArrayList; import java.util.List; import org.hibernate.Criteria; import org.hibernate.Session; import org.hibernate.criterion.MatchMode; import org.hibernate.criterion.Order; import org.hibernate.criterion.Projection; import org.hibernate.criterion.Projections; import org.hibernate.criterion.Restrictions; import cn.zzsxt.entity.Userinfo; import cn.zzsxt.util.HibernateSessionFactory; /** * Criteria是Hibernate提供的一组纯面向对象的查询API. * 其功能类似Query, 但完全以面向对象的形式管理查询逻辑. 更符合ORM的设计思想. * @author Think * */ public class TestCriteria { /** * 查询所有 */ public static void test(){ Session session = HibernateSessionFactory.getSession(); Criteria c = session.createCriteria(Userinfo.class); List<Userinfo> list = c.list(); for (Userinfo userinfo : list) { System.out.println(userinfo.getUserId()+"---"+userinfo.getUserName()); } HibernateSessionFactory.closeSession(); } public static void test2(){ Session session = HibernateSessionFactory.getSession(); Criteria c = session.createCriteria(Userinfo.class); //添加约束条件 // c.add(Restrictions.between("userId", 1, 5));//userId>=1 and userId<=5 // c.add(Restrictions.eq("userType", 1));//userType=1 // List<Integer> ids = new ArrayList<Integer>(); // ids.add(3); // ids.add(4); // c.add(Restrictions.in("userId",ids )); // c.add(Restrictions.like("userName", "%zhang%")); /* * MatchMode.START==>zhang% * MatchMode.END==>%zhang * MatchMode.ANYWHERE==>%zhang% * */ // c.add(Restrictions.like("userName", "zhang", MatchMode.ANYWHERE)); // c.add(Restrictions.le("userId", 5));//le==>less equals小于等于 // c.add(Restrictions.ge("userId", 2));//ge==>great equals大于等于 // c.add(Restrictions.gt("userId", 2));//gt==>great than大于 // c.add(Restrictions.lt("userId", 5));//lt==>less than小于 //排序 // c.addOrder(Order.asc("userId"));//按照userId进行升序排列 c.addOrder(Order.desc("userId"));//按照userId进行升序排列 List<Userinfo> list = c.list(); for (Userinfo userinfo : list) { System.out.println(userinfo.getUserId()+"---"+userinfo.getUserName()); } HibernateSessionFactory.closeSession(); } /** * 分页: * setFirstResult() * setMaxResult() */ public static void test3(){ Session session = HibernateSessionFactory.getSession(); Criteria c = session.createCriteria(Userinfo.class); c.setFirstResult(2); c.setMaxResults(2); List<Userinfo> list = c.list(); for (Userinfo userinfo : list) { System.out.println(userinfo.getUserId()+"---"+userinfo.getUserName()); } HibernateSessionFactory.closeSession(); } /** * 聚合函数:使用投影 */ public static void test4(){ Session session = HibernateSessionFactory.getSession(); Criteria c = session.createCriteria(Userinfo.class); c.setProjection(Projections.count("userId"));//设置聚合函数的投影 // List list = c.list(); // System.out.println(list.get(0)); Object obj = c.uniqueResult(); System.out.println(obj); HibernateSessionFactory.closeSession(); } /** * 分组 */ public static void test5(){ Session session = HibernateSessionFactory.getSession(); Criteria c = session.createCriteria(Userinfo.class); c.setProjection(Projections.groupProperty("userType"));//设置分组的投影 List <Integer> list = c.list(); for (Integer i : list) { System.out.println(i); } HibernateSessionFactory.closeSession(); } public static void main(String[] args) { test5(); } }
