Continuing my Java Tips from experience that I have learned from code reviews to different programming tasks.

Tip 11: Use Hibernate Statistics

Now, this is only for your development profiles/environments, not for production. Hibernate has built-in statistics on your queries. This will break down how much time is spent along each step in the query. A great tool for performance tuning.

spring.jpa.properties.hibernate.generate_statistics=true
logging.level.org.hibernate.stat=DEBUG

Tip 12: Hibernate Slow Query Log

Following up using Hibernate Statistics, The difference here is this one is more suited for use on test, and production with a lower impact on performance. In fact, this is highly recommended to use during performance testing.

Using this feature you are able to specify what queries to log, if they take over x milliseconds then log the query for you to follow. In the example below we set it to 10ms. You will need to determine the best value for your application. Also, remember not all queries are built the same. A query to look up all users in the database won’t be as fast as a query to look up a single field from a single row in an indexed table.

spring.jpa.properties.hibernate.session.events.log.LOG_QUERIES_SLOWER_THAN_MS=10

Tip 13: Use ResultSetExtractor on Queries

Native queries in Hibernate/JPA result in List<Object[]> as our results. This is not very clean to work with. There is a very simple way to handle this, let’s say we have the following query:

String SQL = "select first, last from Person";
      List <Object[]> results = jdbcTemplateObject.query(SQL);

This is going to give you all the records In the Person table, with 2 columns of data “first”, and “last”. Now you have to know that those 2 columns are Strings, that 0 is “first” and 1 is “last”. It would be far easier to push this code directly to a Person object. So we create a ResultSetExtractor like this:

	private static final class PersonEtractor implements ResultSetExtractor<List<Person>> {

		@Override
		public List<Person> extractData(ResultSet resultSet) throws SQLException, DataAccessException {

			List<Person> personList= new ArrayList<>();

			while (resultSet.next()) {
				Person person= new Person();
				person.setName(resultSet.getString("first"));
				person.setType(resultSet.getString("last"));
				personList.add(equipment);
			}
			return personList;
		}
	}

This is an extractor that is created as a separate class. We could just as easily create this as an inline function. However, then we couldn’t reuse the code if we needed to. Now to use this in our query:

String SQL = "select first, last from Person";
      List <Object[]> results = jdbcTemplateObject.query(SQL, new PersonExtractor());

Why do this v’s working with the List<Object[]>?

• Code Reuse – Queries can often get used in mulitple places in the code. Using this prevents duplicated code.
• Readability – Having the results in a POJO will make the code that use this easier to read.
• Mis-use – Having a POJO with defined types gives you a POJO with defined types that a user isn’t having to trying an identify the types.
• Identity – You do not have to know the query to know what the columns returned, as they are now defined in a POJO.

Tip 14: Use @SqlResultSetMapping instead of List<Object[]>

While not going into a full-length article here on how to use @SqlResultSetMapping, I am going to explain why you want to do this. In many cases, you do not want to work with a List<Object[]> results from a query. You’re going to want to have that into a POJO, of some sort. There are 2 ways to do this, @SqlResultSetMapping, and a ResultSetExtractor (Tip 13). This method uses annotations to set up the transformation of your data. For a more detailed description of exactly how to use this, look here Baeldung – A Guide to SqlResultSetMapping.

Why do this v’s working with the List<Object[]>?

• Code Reuse – Queries can often get used in mulitple places in the code. Using this prevents duplicated code.
• Readability – Having the results in a POJO will make the code that use this easier to read.
• Mis-use – Having a POJO with defined types gives you a POJO with defined types that a user isn’t having to trying an identify the types.
• Identity – You do not have to know the query to know what the columns returned, as they are now defined in a POJO.

Now you ask why do this over the ResultSetExtractor (Tip 13)?

They both can accomplish the same thing, so then it becomes a preference of your coding style. However, ResultSetExtractor has an advantage over this. You may have cases where you have the same query, but you want it handled differently. Now in this case you simply use a different ResultSetExtractor.

Tip 15: Pad Hibernate Parameters in “IN” clause

This one is a little confusing, it took me a while to fully understand this change. Let’s say we have two queries:

List<Person> people = personRepository.findAllById(Arrays.asList(10L, 12L, 17L 33L));

This will of course find the Person records with IDs 10, 12, 17 & 33. Hibernate will create a cached plan that it will use to perform the query. Now if our second query is like this:

List<Person> people = personRepository.findAllById(Arrays.asList(4L, 71L 101L));

Hibernate will create an entirely new plan and cache it. Because it does now have the same number of parameters for the “IN” clause. This slows down the query as it has to generate a new plan. Hibernate has a way to improve it:

spring.jpa.properties.hibernate.query.in_clause_parameter_padding=true

This tells Hibernate to use padding, which allows it to pad up to the next 2^n parameters. This means these two queries would now use the same plan, not requiring a new plan to be generated and a higher number of hits in your cache. That increases your performance and makes your queries more efficient.