Execute native sql with hibernate

Another issue that might hit you (like it hit me) is this:

You want to run a native query, but can't get it to work in your production code? Pay attention if you are using a different database user for the application than the schema owner. In that case you may have to add the schema prefix to the referenced tables in order to make it work.

In my example I am using an entity manager instead of the session:

String sql = "select id from some_table";
Query query = em.createNativeQuery(sql);
List<Long> results = query.getResultList();

if some_table is owned by e.g. dba while the application is running as user, you will need to modify the query to:

String sql = "select id from dba.some_table";

Having Hibernate set to prefix all tables with

<prop key="hibernate.default_schema">dba</prop>

does apparently NOT affect native queries.

This should help you.

Session session = Hibernate.util.HibernateUtil.getSessionFactory().openSession();
session.beginTransaction();
String sql = String.format("INSERT INTO products (name,cost) VALUES('%s',%s);",product.getName(), product.getCost());
session.createSQLQuery(sql).executeUpdate();
session.getTransaction().commit();
session.close();

Its always better to use PreparedStatement (You dont want to give way to SQL Injections).

String sql = "INSERT INTO products (name,cost) VALUES (?,?)";

Session sess = Hibernate.util.HibernateUtil.getSessionFactory().openSession();
Connection con = sess.connection();
PreparedStatement pstmt = con.prepareStatement(sql);

pstmt.setString(1, product.getName());
pstmt.setInt(2, product.getCost());

pstmt.executeUpdate();

con.commit();
pstmt.close();

The solution that work for me is the following:

public List<T> queryNativeExecute(String query) throws CustomException {
        List<T> result =null;
        Session session =null;
        Transaction transaction=null; 
        try{
            session = HibernateUtil.getSessionJavaConfigFactory().openSession();
            transaction = session.beginTransaction();
            session.createNativeQuery(query).executeUpdate();
            transaction.commit();
        }catch(Exception exception){
            result=null;
            if (transaction !=null && transaction.isActive()){
                transaction.rollback();
            }
            throw new CustomException(exception.getMessage(),exception,error.ErrorCode.DATABASE_TABLE,this.getClass().getSimpleName());
        }finally{
            if (session !=null){
                session.close();    
            }
        }

        return result;
    }