[Hibernate] Hibernate multi-Table query and hibernate Table query

[Hibernate] Hibernate multi-Table query and hibernate Table query

In [Hibernate] Hibernate's difference in clustering query, grouping query, sorting, and time (click to open the link), we have already talked about how to replace SQL statements with HQL statements, perform clustering query, grouping query, sorting and time difference query, and point out that hql can do anything in place of SQL statements. I originally thought that for multi-table queries of hql statements, Java and xml modifications should be made to the entities in the table first. In fact, this is not necessary. It can be done with an HQL statement. SQL multi-Table query has been discussed in [Mysql] Using Inner join and nested query to implement multi-Table query, basic concepts of primary key and foreign key (click to open the link.

For example, the following SQL statement:

select t1.Title,t1.Contentfrom blog as t1 ,usertable as t2where t1.userid=t2.id and t2.username='a'

The HQL statement is as follows:

String hql="select t1.title,t1.content from Blog as t1,Usertable as t2 where t1.userId=t2.id and t2.username='a'"  List<Object> resultList = session.createQuery(hql).list();  for (int i = 0; i < resultList.size(); i++) {      Object[] obj = (Object[])resultList.get(i);      System.out.println(obj[0]+","+obj[1]);  }

The core idea is to write tables in SQL statements as Hibernate entities, fields and columns in SQL statements as member variables of Hibernate entities, and use the table as t1, t2. XX field to simplify. As shown above, two tables, Blog and Usertable, are converted into t1 and t2 tags, which convert SQL fields and columns into Hibernate object member variables, object.

The result obtained by Hibernate is a List containing the Object array. That is to say, each item in the List is an Object array. The Nth item of the Object array corresponds to the nth item of the query result.

You can proceed to the next step.

The following example describes the multi-Table query of an HQL statement.

The Blog records the Blog published by the user, and the usertable records the basic information of the user. The userid in the Blog table and the primary key id of the usertable form a complete reference.

The two tables correspond to the following entities in the Hibernate Java project:

Blog. java

import javax.persistence.*;@Entity@Table(name = "blog")public class Blog {private int id;private String title;private String content;private int userId;@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}@Column(name = "Title")public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}@Column(name = "Content")public String getContent() {return content;}public void setContent(String content) {this.content = content;}@Column(name = "userid")public int getUserId() {return userId;}public void setUserId(int userId) {this.userId = userId;}@Overridepublic String toString() {return id + "," + title + "," + content + "," + userId;}}

Usertable. java

import javax.persistence.*;@Entity@Table(name = "usertable")public class Usertable {private int id;private String username;private String password;@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}@Column(name = "username")public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}@Column(name = "password")public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}@Overridepublic String toString() {return id + "," + username + "," + password;}}

At the same time, hibernate. cfg. xml is configured as follows:

<? 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"> It can be found that this does not specify any integrity of the reference.
Next, you need to query the Blog posted by the user whose username is a in usertable.
We know that we need to first query the id of the user whose username is a, and then use this id to query the Blog table.
If you use an SQL statement to complete the multi-Table query, write as follows:

select t1.Title,t1.Contentfrom blog as t1 ,usertable as t2where t1.userid=t2.id and t2.username='a'
The query result is as follows:



This is written using Hibernate. The code in HibernateMultiTableTest. java is as follows:

Import java. util. list; import org. hibernate. *; import org. hibernate. cfg. *; class dbDAO {private Session session; // constructor, initialization Session, equivalent to connecting to the database public dbDAO () {// new Configuration (). configure (), right, hibernate. cfg. read all configurations in xml //. buildSessionFactory (). openSession () is used to create a Session factory and instantiate sessionthis. session = new Configuration (). configure (). buildSessionFactory (). openSession ();} // execute the public Query query (String hql) {r Eturn session. createQuery (hql);} // insert and modify public void save (Object object) {Transaction transaction = session. beginTransaction (); session. save (object); transaction. commit () ;}// execute the delete public void delete (Object object) {Transaction transaction = session. beginTransaction (); session. delete (object); transaction. commit () ;}// destructor to interrupt the Session, which is equivalent to interrupting the database connection protected void finalize () throws Exception {if (session. isCo Nnected () | session! = Null) {session. close () ;}}@ SuppressWarnings ("unchecked") public class HibernateMultiTableTest {public static void main (String args []) {dbDAO db = new dbDAO (); list <Object> resultList = db. query ("select t1.title, t1.content from Blog as t1, Usertable as t2 where t1.userId = t2.id and t2.username = 'A '"). list (); // HQL multi-Table query System. out. println ("user whose username is a in usertable, the published content is as follows:"); System. out. println (); for (int I = 0; I <resultList. size (); I ++) {Object [] obj = (Object []) resultList. get (I); System. out. println ("title:" + obj [0]); System. out. println ("content:" + obj [1]); System. out. println ();}}}
The running result is as follows: