preface

In the second Hibernate article, I simply described several Hibernate query methods…. So far, we have used some simple primary key queries. Query all data using HQL…. Hibernate query operation, connection pool, reverse engineering…

Get /load primary key query

Because primary key queries are used a lot, Hibernate encapsulates them for us…

  • Get () Queries immediately
  • Load () lazy loading

Object navigation query

If an object has a one-to-many or many-to-one relationship with an object before

  • In the old SQL query: if we want to get the current object and another object’s association relationship, we must use a multi-table query to get the data
  • Hibernate provides object navigation queries: we can use the primary key after the query, get the object, directly use the object to get the collection… You can get the corresponding data.
		// Object navigation query
		Dept dept =  (Dept) session.get(Dept.class, 12);
		System.out.println(dept.getDeptName());

		// Here you can get all the employees of the department
		System.out.println(dept.getEmps());
Copy the code

HQL query

The basic concepts have been covered in the previous Hibernate chapters. So we’re going to look directly at how to use it.

It is worth noting:

  • In the hbm.xml file auto-import=”true” is set to true. Of course, the default value is true
  • If false, specify the full name of the class when writing the HQL

Query all columns

	// Write the object directly
	Query q = session.createQuery("from Dept");

	// Method 2: You can alias the object
	Query q = session.createQuery("select d from Dept d");
 
Copy the code

Note that HQL does not support *, the following code is incorrect.

	// Can't use *
	Query q = session.createQuery("select * from Dept d"); 

Copy the code

Query the specified column

The HQL query returns an array of objects [].

		// Query only the name and eatBanana columns
        Query query = session.createQuery("select m.name,m.eatBanana from Monkey m");
        System.out.println(query.list());
Copy the code

Encapsulated object

A query for a specified column returns an array of objects… But object array we are not easy to manipulate ah… Hibernate also provides the ability to encapsulate arrays of objects into objects

  • Javabeans have corresponding constructors
        Query query = session.createQuery("select new Monkey(m.name,m.eatBanana )from Monkey m");
Copy the code

Conditions of the query

In SQL, we also use a lot of conditional query, let’s take a look at HQL conditional query features.

A placeholder

A placeholder is what? In SQL, we also use…


        Query query = session.createQuery("from Monkey m where m.name=?");
        //HQL starts from 0
        query.setParameter(0."Big Monkey");

        System.out.println(query.list());

Copy the code

Named parameters

HQL also supports named parameter query! Here’s how to use it:

Syntax: : naming


        Query query = session.createQuery("from Monkey m where m.name=:monkeyName");
        //HQL starts from 0
        query.setParameter("monkeyName"."Big Monkey");
        System.out.println(query.list());

Copy the code

Range queries

Range queries are queries for a specific range of data using the between and keyword. Same as SQL…

		Query q = session.createQuery("from Dept d where deptId between ? and ?");
		q.setParameter(0.1);
		q.setParameter(1.20);
		System.out.println(q.list());
Copy the code

Fuzzy query

Fuzzy queries are queries that use the Like keyword, which is the same as SQL.

  • The % sign must be written on the parameter, cannot write with SQL!

		/ / fuzzy
		Query q = session.createQuery("from Dept d where deptName like ?");
		q.setString(0."%部%");
		System.out.println(q.list());
Copy the code

Aggregate function statistics

We also often query requirements such as how many records there are in the database. How to use it in HQL?

  • HQL provides a method called uniqueResult() that returns data with only one record
        Query query = session.createQuery("select COUNT(*) from Monkey");
        Object o = query.uniqueResult();
        System.out.println(o);

Copy the code

Grouping query

Grouped queries are the same as SQL…


		//-- Count the number of employees in each department
		SELECT dept_id,COUNT(*) FROM t_employee GROUP BY dept_id;
		/ / HQL writing
		Query q = session.createQuery("select e.dept, count(*) from Employee e group by e.dept");
		System.out.println(q.list());
Copy the code

Join queries

Join query is a multi-table query… There are three types of multi-table queries

  • Internal connection equivalent connection
  • The left outer join
  • There are foreign link

Note that the join query also returns an array of objects!


		//1) inner join [mapping has been configured, when the association, directly write object attributes can be]
		Query q = session.createQuery("from Dept d inner join d.emps");
		
		//2) left outer join
		Query q = session.createQuery("from Dept d left join d.emps");

		//3) Right outer connection
		Query q = session.createQuery("from Employee e right join e.dept");
		q.list();
		
Copy the code

Urgent connection

Since the join query returns an array of objects, it would be inconvenient to use an array of objects… Since it is a join query, then the object and the object is definitely associated relationship… So, we want to populate the left table with the right table, or the right table with the left table… Make it return an object instead of an array of objects! HQL provides the fetch keyword for us to do the urgent connection ~


		//1) select * from 'fetch'; // select * from 'fetch';
		Query q = session.createQuery("from Dept d inner join fetch d.emps");
		q.list();
		
		//2) Urgent left outer connection
		Query q = session.createQuery("from Dept d left join fetch d.emps");
		q.list();
Copy the code

Query statement in configuration file

We can store common statements in a specific mapping configuration file. In the case of the Dept

<! CDATA = <> = <> = <> = <> = <> = <> = <> --> <query name="getAllDept"> <! [CDATA[ from Dept d where deptId < ? ]]> </query>Copy the code

In the program, we can get the statements configured by the configuration file

		Query q = session.getNamedQuery("getAllDept");
		q.setParameter(0.10);
		System.out.println(q.list());
Copy the code

The Criteria query

Criteria is a fully object-oriented query…

Criteria uses add() to add conditions. Conditions are encapsulated with a Restrictions class


        Criteria criteria = session.createCriteria(Monkey.class);
        criteria.add(Restrictions.eq())

Copy the code

Let’s take a quick look at the methods for Restrictions:

They are greater than, less than, equal to and so on…. Criteria queries do not use grouped, join queries.

SQLQuery Local SQL query

Sometimes, we might have a very complex table structure, and if we use associative mapping, the configuration file is very bloated… Therefore, not all tables are mapped to create tables…

At this point, we need to use SQLQuery to maintain our data..

SQLQuery is not cross-database because Hibernate specifies the “dialect” of the database at configuration time…


        SQLQuery sqlQuery = session.createSQLQuery(SELECT * FROM monkey_ limit 0,3);

        System.out.println(sqlQuery.list());
Copy the code

Returns an array of objects:

Hibernate also supports object encapsulation of data in SQLQuery.. Just add the type


        SQLQuery sqlQuery = session.createSQLQuery(SELECT * FROM monkey_ limit 0,3).addEntity(Monkey.class);

        System.out.println(sqlQuery.list());


Copy the code

Paging query

In traditional SQL, we use two steps in the DAO layer to implement paging queries

  • Gets the total number of records in a database table
  • Query data from start position to end digit

Hibernate also has good support for paging queries, so let’s take a look:


        Query query = session.createQuery("from Monkey");

        // get the scrolling result set
        ScrollableResults scroll = query.scroll();
        // Scroll to the last line
        scroll.last();
        int i = scroll.getRowNumber() + 1;
        System.out.println("Total routes:" + i);

        // Set the paging location
        query.setFirstResult(0);
        query.setMaxResults(3);

        System.out.println(query.list());
Copy the code
  • Provides methods that let us set the start and end positions
  • The ScrollableResults are provided to get the rolling result set and the total number of records

Note that the scrolling result set starts at 0, so you need +1 to get the total number of records!

If we were to useSELECT COUNT(*) FROM entityWe can use the uniqueResult() method to get a unique record of the data, which is converted to type Long.

	Long totalRecord = (Long) queryCount.uniqueResult();
Copy the code

Hibernate connection pool

Hibernate comes with connection pooling, but it’s relatively simple.. Hibernate supports C3P0 connection pools… So let’s change the Hibernate connection pool to C3P0

Take a look at Hibernate’s own connection pool

You can view Hibernate’s default configured connection pool in the hibernate.properties file

The hibernate. Properties configuration file can be found in \project\etc

Hibernate’s own connection pool has nothing but a single connection number of 1…

Check out Hibernate support for C3P0

  • # hibernate.c3P0.max_size 2 Maximum number of connections
  • # hibernate.c3P0.min_size 2 Minimum number of connections
  • # hibernate.c3P0.timeout 5000 Specifies the timeout period
  • # hibernate.c3P0.max_statements 100 Maximum number of commands to execute
  • # hibernate.c3P0.idLE_TEST_period 3000 Idle test time
  • C3p0.acquire_increment 2 Specifies the number of increments each time when there are not enough connections
  • #hibernate.c3p0.validate false

Modify the Hibernate connection pool

We configure C3p0 in hibernate.cfg.xml to serve as Hibernate’s database connection pool

Hibernate supports connection pool components:

Now that we have found it, we can configure the corresponding class and related configuration in hibernate.cfg.xml


		<! -- [Connection Pool configuration] -->
		<! Config Connection Driver Management class -->
		<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
		<! -- Set connection pool parameters -->
		<property name="hibernate.c3p0.min_size">2</property>
		<property name="hibernate.c3p0.max_size">4</property>
		<property name="hibernate.c3p0.timeout">5000</property>
		<property name="hibernate.c3p0.max_statements">10</property>
		<property name="hibernate.c3p0.idle_test_period">30000</property>
		<property name="hibernate.c3p0.acquire_increment">2</property>
Copy the code

Thread Session usage

When we create a Session, there are two methods

  • OpenSession () [creates a new Session every time]
  • GetCurrentSession () [get the current thread’s Session, if not created]

In general, we use threaded sessions a lot

If you want to use getCurrentSession(), you need to configure it in the configuration file:

        <! -- Configure thread Session-->
        <property name="hibernate.current_session_context_class">thread</property>
Copy the code

The test data


@Test
	public void testSession(a) throws Exception {
		//openSession: Creates a Session. A new Session is created each time
		Session session1 = sf.openSession();
		Session session2 = sf.openSession();
		System.out.println(session1 == session2);
		session1.close();
		session2.close();
		
		//getCurrentSession Creates or obtains a session
		// Create session in thread mode
		
      
       thread
      
		Session session3 = sf.getCurrentSession();// Create session, bind to thread
		Session session4 = sf.getCurrentSession();// Get the session from the currently accessing thread
		System.out.println(session3 == session4);
		
		// Close the session created by a thread. The session is automatically closed when the thread ends.
		//session3.close();
		//session4.close(); Error: The same session was closed!
	}
Copy the code

Why reverse engineering

Because every time we write Hibernate, we need to write entities, write mapping files. Hibernate’s mapping files are also error-prone. Reverse engineering can help us automatically generate entities and mapping files, which is very convenient.

The use of PowerDesigner

When designing database tables, we use PowerDesigner to generate conceptual models, physical models…

Design an organizational structure of people: organization, department, staff, leader, role, authority.

  • An organization has multiple departments
  • There are multiple employees in a department
  • A leader can manage multiple departments and be an employee himself
  • An employee can have multiple roles
  • A role can be assigned to more than one person
  • After personnel role allocation, you can set whether it is effective and allocate time
  • A role has multiple permissions

Conceptual model:

In PowerDesigner, the direction of the arrow is always one side

Generate physical model:

The resulting physical model looks like this:

Generate SQL statements

We can build them individually, copy them one by one

You can also generate SQL statements for the entire physical model:


	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	/* MySQL 5.0 */
	/* Created on: 2017/6/5 20:22:52 */
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	
	
	drop table if exists person_role;
	
	drop table if exists t_company;
	
	drop table if exists t_dept;
	
	drop table if exists t_employee;
	
	drop table if exists t_person;
	
	drop table if exists t_privilege;
	
	drop table if exists t_role;
	
	drop table if exists t_role_privilege;
	
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	/* Table: person_role */
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	create table person_role
	(
	   person_id            varchar(32) not null,
	   role_id              varchar(32) not null,
	   state                varchar(32),
	   primary key (person_id, role_id)
	);
	
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	/* Table: t_company */
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	create table t_company
	(
	   company_id           varchar(32) not null.name                 varchar(32),
	   primary key (company_id)
	);
	
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	/* Table: t_dept */
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	create table t_dept
	(
	   dept_id              varchar(32) not null,
	   company_id           varchar(32) not null.name                 varchar(32),
	   primary key (dept_id)
	);
	
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	/* Table: t_employee */
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	create table t_employee
	(
	   person_id            varchar(32) not null,
	   dept_id              varchar(32),
	   name                 varchar(32),
	   employee_id          varchar(32),
	   primary key (person_id)
	);
	
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	/* Table: t_person */
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	create table t_person
	(
	   person_id            varchar(32) not null,
	   dept_id              varchar(32) not null.name                 varchar(32),
	   primary key (person_id)
	);
	
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	/* Table: t_privilege */
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	create table t_privilege
	(
	   privilege_id         varchar(32) not null.name                 varchar(32),
	   primary key (privilege_id)
	);
	
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	/* Table: t_role */
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	create table t_role
	(
	   role_id              varchar(32) not null.name                 varchar(32),
	   primary key (role_id)
	);
	
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	/* Table: t_role_privilege */
	/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
	create table t_role_privilege
	(
	   role_id              varchar(32) not null,
	   privilege_id         varchar(32) not null,
	   primary key (role_id, privilege_id)
	);
	
	alter table person_role add constraint FK_person_role foreign key (person_id)
	      references t_person (person_id) on delete restrict on update restrict;
	
	alter table person_role add constraint FK_person_role2 foreign key (role_id)
	      references t_role (role_id) on delete restrict on update restrict;
	
	alter table t_dept add constraint FK_companty_dept foreign key (company_id)
	      references t_company (company_id) on delete restrict on update restrict;
	
	alter table t_employee add constraint FK_inherit foreign key (person_id)
	      references t_person (person_id) on delete restrict on update restrict;
	
	alter table t_person add constraint FK_dept_person foreign key (dept_id)
	      references t_dept (dept_id) on delete restrict on update restrict;
	
	alter table t_role_privilege add constraint FK_belong foreign key (role_id)
	      references t_role (role_id) on delete restrict on update restrict;
	
	alter table t_role_privilege add constraint FK_own foreign key (privilege_id)
	      references t_privilege (privilege_id) on delete restrict on update restrict;


Copy the code

Generate eight tables in the database:

Use Hibernate reverse engineering under Idea

It is worth noting that the mapping files generated under Intellij IDEA have no corresponding association relationship. In other words: one-to-many or many-to-many relationships are not automatically generated for you… Therefore, we need to add Set ourselves.

Update, if you want to reflect the corresponding relation, please refer to this blog!

  • https://my.oschina.net/wangyuefive/blog/683771

If the article has the wrong place welcome to correct, everybody exchanges with each other. Students who are used to reading technical articles on wechat and want to get more Java resources can follow the wechat public account :Java3y