writing custom queries in Spring boot

Question

I have recently started using Spring boot, and have run into a bit of a problem. before, when i was just using Spring data with hibernate and JPA, I could create a hibernate.cfg.xml file that would give a bunch of configuration that could be passed to a config object and then ultimately create a SessionFactory object that would create a Session object that could be used to pass the query to hibernate:

package util;

import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder; 
import org.hibernate.cfg.Configuration;

public class HibernateUtil {
    private static final SessionFactory sessionFactory = buildSessionFactory();
    private static SessionFactory buildSessionFactory() { 
        try {
        // Create the SessionFactory from hibernate.cfg.xml
        Configuration configuration = new Configuration().configure("hibernate.cfg.xml"); return configuration.buildSessionFactory( new
        StandardServiceRegistryBuilder().applySettings( configuration.getProperties() ).build() ); 
        }
        catch (Throwable ex) {
        // Make sure you log the exception, as it might be swallowed System.err.println("Initial SessionFactory creation failed." + ex); throw new ExceptionInInitializerError(ex);
        } 
    }
    public static SessionFactory getSessionFactory() { return sessionFactory; }
}

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">

<hibernate-configuration> 
    <session-factory>

        <!-- Database connection settings -->
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property> 
        <property name="connection.url">jdbc:mysql://localhost:3306/hello-world</property> 
        <property name="connection.username">root</property>
        <property name="connection.password">password</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property> 

        <!-- Create/update tables automatically using mapping metadata -->
        <property name="hbm2ddl.auto">update</property> 

        <!-- Use Annotation-based mapping metadata -->
        <mapping class="entity.Author"/> 
        <mapping class="entity.Article"/> 
    </session-factory>
</hibernate-configuration>

Main.java

    public class HelloWorldClient {

    public static void main(String[] args) {
        Session session = HibernateUtil.getSessionFactory().openSession(); 
        Transaction txn = session.getTransaction();

        EntityManagerFactory emf = Persiscance.createEntityManagerFactory("hibernate.cfg.xml");
        EntityManager em = emf.createEntityManager();
        EntityTransaction txn = em.getTransaction();

        try {
            txn.begin();
            Author author = new Author("name", listOfArticlesWritten);
            Article article = new Article("Article Title", author);
            session.save(author);
            session.save(article);

            Query query = session.createQuery("select distinct a.authorName from Article s
                                                where s.author like "Joe%" and title = 'Spring boot');

            List<Article> articles = query.list();

            txn.commit();
        } catch(Exception e) {
            if(txn != null) { txn.rollback(); }
            e.printStackTrace();
        } finally {
            if(session != null) { session.close(); } }
    } 
}

This is where the issue appears. I don't know how to avoid creating a hibernate.cfg.xml file or session factory for custom queries. in the Spring guides page, and some tutorials i have worked through, they take their DAO and extend the CrudRepository interface which gives a bunch of methods already, as well as a way to name the method so that Hibernate can build the sql on its own.

what i am trying to accomplish, at least in this post is to be able to execute the above query in spring boot. I can create a properties file

application.properties

# ===============================
# = DATA SOURCE
# ===============================

# Set here configurations for the database connection
spring.datasource.url = jdbc:mysql://localhost:3306/spring-boot-demo
spring.datasource.username = test
spring.datasource.password = test

# Mysql connector
spring.datasource.driverClassName = com.mysql.jdbc.Driver



# ===============================
# = JPA / HIBERNATE
# ===============================

# Specify the DBMS
spring.jpa.database = MYSQL

# Show or not log for each sql query
spring.jpa.show-sql = true

# Ddl auto must be set to "create" to ensure that Hibernate will run the
# import.sql file at application startup

#create-drop| update | validate | none
spring.jpa.hibernate.ddl-auto = update

# SQL dialect for generating optimized queries
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

# ===============================
# = THYMELEAF
# ===============================

spring.thymeleaf.cache = false
#debug=true

I can move all but the mapping to a properties file, but then I am unclear how to write the query because there is no longer a session object.

maybe you need custom method implement. [this is an stackoverflow answer](http://stackoverflow.com/questions/11880924/how-to-add-custom-method-to-spring-data-jpa) — Thang Hoang, Dec 16 '15 at 04:01
I explained solution here, may be it will helpful for you https://stackoverflow.com/a/48854352/4720910 — Musa, Feb 18 '18 at 17:13

score 15 · Accepted Answer · answered Dec 16 '15 at 08:23

If you use Spring Boot + Spring Data JPA, then you configure your datasource (which you now put in hibernate.cfg.xml) into the application.properties, by using the spring.datasource.* properties.

This should automatically create an entity manager for you. If you need to use queries, you can use Spring Data JPA's repositories, for example:

public interface ArticleRepository extends JpaRepository<Article, Long> {
    @Query("select s from Article s where s.author like ?1 and s.title = ?2")
    List<Article> findByAuthorAndTitle(String author, String title);
}

Now you can autowire the repository and use the given query, like this:

List<Article> articles = repository.findByAuthorAndTitle("Joe%", "Spring boot");

If you really need custom queries, you can use the Predicate/Criteria API from JPA. Spring offers a wrapped version of these predicates, called Specifications.

To do that, you extend your ArticleRepository with another interface called JpaSpecificationExecutor<Article>. This adds some extra methods to your repository:

Specification<Article> spec = Specifications.<Article>where((root, query, cb) -> {
    return cb.and(
         cb.like(root.get("author"), "Joe%"),
         cb.equal(root.get("title"), "Spring boot"));
});
List<Article> articles = repository.findAll(spec);

This allows you to dynamically create queries, though from your question it doesn't look like you really need it.

score 2 · Answer 2 · answered Dec 16 '15 at 04:34

In spring boot application you no need to create xml configuration, you must congigure java file itself. Check with this example,

import java.util.Properties;

import javax.sql.DataSource;

import org.hibernate.annotations.common.util.impl.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.hibernate4.HibernateTransactionManager;
import org.springframework.orm.hibernate4.LocalSessionFactoryBean;

@Configuration
public class DatabaseConfig {

 private org.jboss.logging.Logger log = LoggerFactory.logger(DatabaseConfig.class);

 @Value("${db.driver}")
 private String DB_DRIVER;

 @Value("${db.username}")
 private String DB_USERNAME;

 @Value("${db.password}")
 private String DB_PASSWORD;

 @Value("${db.url}")
 private String DB_URL;

 @Value("${hibernate.dialect}")
 private String HIBERNATE_DIALECT;

 @Value("${hibernate.show_sql}")
 private String HIBERNATE_SHOW_SQL;

 @Value("${hibernate.hbm2ddl.auto}")
 private String HIBERNATE_HBM2DDL_AUTO;

 @Value("${entitymanager.packagesToScan}")
 private String ENTITYMANAGER_PACKAGES_TO_SCAN;

 @Bean
 public DataSource dataSource() {
  DriverManagerDataSource dataSource = null;
  try {
   dataSource = new DriverManagerDataSource();
   dataSource.setDriverClassName(DB_DRIVER);
   dataSource.setUrl(DB_URL);
   dataSource.setUsername(DB_USERNAME);
   dataSource.setPassword(DB_PASSWORD);
  } catch (Exception e) {
   e.getMessage();
  }
  return dataSource;
 }

 @Bean
 public LocalSessionFactoryBean sessionFactory() {
  LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean();
  sessionFactoryBean.setDataSource(dataSource());
  sessionFactoryBean.setPackagesToScan(ENTITYMANAGER_PACKAGES_TO_SCAN);
  Properties hibernateProps = new Properties();
  hibernateProps.put("hibernate.dialect", HIBERNATE_DIALECT);
  hibernateProps.put("hibernate.show_sql", HIBERNATE_SHOW_SQL);
  hibernateProps.put("hibernate.hbm2ddl.auto", HIBERNATE_HBM2DDL_AUTO);
  sessionFactoryBean.setHibernateProperties(hibernateProps);
  return sessionFactoryBean;
 }

 @Bean
 public HibernateTransactionManager transactionManager() {
  HibernateTransactionManager transactionManager = new HibernateTransactionManager();
  transactionManager.setSessionFactory(sessionFactory().getObject());
  return transactionManager;
 }
}

writing custom queries in Spring boot

2 Answers2

Linked