CODEWITHSUNDEEP
springhibernatejpa
Jim Archer
Jim Archer

Reputation: 1417

Native SQL from Spring / Hibernate without entity mapping?

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;
}
}

Upvotes: 2

Views: 12621

Answers (2)

Zon
Zon

Reputation: 19880

The short way is:

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

Upvotes: 0

Максим Шатов
Максим Шатов

Reputation: 3311

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);
    }


}

Upvotes: 2

Related Questions