Native SQL from Spring / Hibernate without entity mapping?

Question

I need to write some temporary code in my existing Spring Boot 1.2.5 application that will do some complex SQL queries. By complex, I mean a single queries about 4 different tables and I have a number of these. We all decided to use existing SQL to reduce potential risk of getting the new queries wrong, which in this case is a good way to go.

My application uses JPA / Hibernate and maps some entities to tables. From my research it seems like I would have to do a lot of entity mapping.

I tried writing a class that would just get the Hibernate session object and execute a native query but when it tried to configure the session factory it threw an exception complaining it could not find the config file.

Could I perhaps do this from one of my existing entities, or at least find a way to get the Hibernate session that already exists?

UPDATE:

Here is the exception, which makes perfect sense since there is no config file to find. Its app configured in the properties file.

org.hibernate.HibernateException: /hibernate.cfg.xml not found
at org.hibernate.internal.util.ConfigHelper.getResourceAsStream(ConfigHelper.java:173)

For what it's worth, the code:

@NamedNativeQuery(name = "verifyEa", query = "select account_nm from per_person where account_nm = :accountName") public class VerifyEaResult { private SessionFactory sessionFact = null;

String accountName;

private void initSessionFactory()
{
    Configuration config = new Configuration().configure();
    ServiceRegistry serviceRegistry = new ServiceRegistryBuilder().applySettings(config.getProperties()).getBootstrapServiceRegistry();

    sessionFact = config.buildSessionFactory(serviceRegistry);  
}


public String getAccountName()
{
    // Quick simple test query 
    String sql = "SELECT * FROM PER_ACCOUNT WHERE ACCOUNT_NM = 'lynnedelete'";

    initSessionFactory();

    Session session = sessionFact.getCurrentSession();


    SQLQuery q = session.createSQLQuery(sql);

    List<Object> result = q.list();

    return accountName;
}
}

It just seems to me I should be able to get either the Hibernate Session or, better, the JPA EntityManager. Thats the path I'm on, although with no success yet. If there is a better way please let me know. — Jim Archer
– Jim Archer, Commented Oct 20, 2015 at 15:45
already have, JDBC. Far more appropriate for what you want to do. — Neil Stockton
– Neil Stockton, Commented Oct 20, 2015 at 17:29

Максим Шатов · Accepted Answer · 2015-11-09 20:37:17Z

2

You can use Data access with JDBC, for example:

public class Client {
    private final JdbcTemplate jdbcTemplate;

    // Quick simple test query 
    final static String SQL = "SELECT * FROM PER_ACCOUNT WHERE ACCOUNT_NM = ?";

    @Autowired
    public Client(DataSource dataSource) {
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public List<Map<String, Object>> getData(String name) {
        return jdbcTemplate.queryForList(SQL, name);
    }


}

answered Nov 9, 2015 at 20:37

Максим Шатов

3,3911 gold badge31 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jim Archer Over a year ago

Thanks, I ended up doing something very similar to this. Spring provides JdbcTemplate Autowired, so there is no need to Autowire the datasource.

Zon · Accepted Answer · 2020-03-19 13:19:17Z

0

The short way is:

jdbcTemplate.queryForList("SELECT 1", Collections.emptyMap());

answered Mar 19, 2020 at 13:19

Zon

20k8 gold badges99 silver badges104 bronze badges

Collectives™ on Stack Overflow

Native SQL from Spring / Hibernate without entity mapping?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related