1.HQL查询:
基本规则:就像SQL一样,也是有select from where,但是select,from后面跟着并不是表中的字段名或者表名,而是他们对应的类名和属性名。注意:若是select * 一般是可以省略的;注意类名包名属性名的大小写,这里对这些东西大小写敏感。
下面做一个测试,事先连接好数据库,写好实体类。
记录大坑:今天写hibernate配置文件的时候,遇到了两个大坑,害的我花了三小时才搞通了,心累。。。。。。。。。。就是因为自己菜
大坑一:在主配置文件中,写资源mapping的时候,运行之后,老是找不到资源位置,也就是*.hbm.xml文件的位置。最后实在气不过,直接把*.hbm.xml扔到resource文件中,不和entity文件在一起,和主配置文件hibernate.cfg.xml在一起就好了。
大坑二:在主配置文件中,在配置url的时候,老是报错。最后发现,这个和properties.yml中的配置不一样,后面的"&"连接都要改成";"才可以。配置文件如下:
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"> <hibernate-configuration> <session-factory> <!--驱动路径注意数据库类型 --> <property name="hibernate.connection.driver_class">com.mysql.cj.jdbc.Driver</property> <!--数据库路径 --> <property name="hibernate.connection.url">jdbc:mysql:///student?serverTimezone=UTC</property> <!--数据库用户名 --> <property name="hibernate.connection.username">root</property> <!--数据库密码 --> <property name="hibernate.connection.password">123456</property> <!--方言,便于hibernate对象操作转化为合适的数据库语句 --> <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property> <!--是否显示sql语句,sql一般会很难看 默认为false--> <property name="hibernate.show_sql">true</property> <!--下一个层级的映射文件,用来配置bean对象与数据表之间的关系 --> <mapping resource="xml/Student.hbm.xml"/> </session-factory> </hibernate-configuration>
Student.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"> <!-- 注意包名。不写的话下面要写全限定名 --> <hibernate-mapping package="com.yyj.demo.pojo"> <!-- 类名对应表名 --> <class name="Student" table="student"> <!-- 主键使用id标签。然后对应属性名与列名 --> <id name="id" type="int" column="id"> <!-- 自增长主键不同数据库有不同类型的自增长类型,有需要可以百度到答案的 --> <generator class="identity"></generator> </id> <!-- 非主键映射关系,注意类型并不是单纯的java类型也不是数据库类型,而是一种中间类型,注意大小写特别是String在这里开头要小写 --> <property name="name" type="string" column="name"></property> <property name="age" type="int" column="age"></property> <property name="xh" type="string" column="xh"></property> </class> </hibernate-mapping>
测试:
import com.yyj.demo.pojo.Student;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class TestHibernate {
private static Configuration con = null;
private static SessionFactory factory = null;
private static Session session = null;
public static void canShuChaungDi(){
//String hql = "from Student where name=?";
String hql = "from Student where name = :name"; //使用参数名称动态绑定!(推荐使用!)
Query q = session.createQuery(hql);
//q.setString(0, "SMITH"); //问号的个数,参数索引从0开始计数,而不像jdbc一样从1开始。
q.setString("name", "yyj7");
List list = q.list();
for(int i=0;i<list.size();i++){
Student c = (Student) list.get(i);
System.out.println(c.getXh());
}
}
public static void getAllStudent(){
//select *直接省略
String hql = "from Student";
//创建HQL查询
Query query = session.createQuery(hql);
query.setFirstResult(0); //从第几条开始取数据
query.setMaxResults(10); //设置每页最多显示记录的个数
List list = query.list();
for(int i = 0;i < list.size();i++){
//可以通过对象数组或者Map获取
//Object[] o = list.get(i); List<Object[]> list = query.list();
//Map map = list.get(i); List<Map> list = query.list();
Student student = (Student) list.get(i);
System.out.println(student.getName() + " " + student.getXh());
}
}
public static void dongTaiChaXun(){
String hql = "from Student where 1=1";
//创建StringBuffer对象,方便字符串的拼接
StringBuffer buffer = new StringBuffer(hql);
Student student = new Student();
student.setAge(23);
if(student.getAge() != 0){
buffer.append(" and age=:age");
}
Query query = session.createQuery(buffer.toString());
Student student1 = (Student) query.uniqueResult();//单个结果
System.out.println(student1);
}
public static void getStudent2(){
String hql = "from Student where id=:id and age=:age";
Query q = session.createQuery(hql);
Map<String, Object> ma = new HashMap<>();
ma.put("id",4);//或者使用setParameter(1,1)
ma.put("age",23);
q.setProperties(ma);
List list = q.list();//多个结果
for(int i = 0;i < list.size();i ++){
Student s = (Student) list.get(i);
System.out.println(s.getName());
}
}
public static void getStudent(){
String hql = "from Student where id=:id and age=:age and name=:name and xh=:xh";
Query query = session.createQuery(hql);
Student student = new Student(4,23,"tzj","002");
query.setProperties(student);
List<Map> list = query.list();
for(int i = 0;i < list.size();i ++){
Map map = list.get(i);
System.out.println(map.get("name"));
}
}
public static void getNum(){
//count(*),count(1)都可以
String hql = "select count(*) from Student";
Query query = session.createQuery(hql);
Number number = (Number) query.uniqueResult();
System.out.println(number.intValue());
}
public static void main(String[] args){
//读取配置文件中的信息
con =new Configuration().configure();
//获取sessionFactory对象
factory=con.buildSessionFactory();
//获取Session对象
session=factory.openSession();
canShuChaungDi();
//getAllStudent();
//getNum();
session.close();
}
}
2.SQL查询和命名方式查询:
填坑:配置hibernate文件需要添加mapping,直接上代码吧,加粗的地方要注意。
1.Address.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"> <hibernate-mapping package="com.yyj.demo.pojo"> <class name="Address" table="address"> <!-- 主键使用id标签。然后对应属性名与列名 --> <id name="id" type="int" column="id"> <!-- 自增长主键不同数据库有不同类型的自增长类型,有需要可以百度到答案的 --> <generator class="identity"></generator> </id> <!-- 非主键映射关系,注意类型并不是单纯的java类型也不是数据库类型,而是一种中间类型,注意大小写特别是String在这里开头要小写 --> <property name="province" type="string" column="province"></property> <property name="city" type="string" column="city"></property> <property name="town" type="string" column="town"></property> <property name="village" type="string" column="village"></property> </class> <sql-query name="address.list"> <return class="Address"></return> <![CDATA[select * from address where city = :city]]> </sql-query> </hibernate-mapping>
2.TestSQL.java
import java.util.List; public class TestSQL { private static Configuration con = null; private static SessionFactory factory = null; private static Session session = null; public static void getAllAddress(){ Query query = session.getNamedQuery("address.list").setParameter("city", "合肥"); List<Address> list = query.list(); for (int i = 0;i < list.size();i ++){ System.out.println(list.get(i).getProvince()); } } public static void main(String[] args){ //读取配置文件中的信息 con =new Configuration().configure(); //获取sessionFactory对象 factory=con.buildSessionFactory(); //获取Session对象 session=factory.openSession(); getAllAddress(); session.close(); } }