Execute native sql with hibernate

Question

I'm using hibernate 4.2.6 and PostgreSQL 9.1 I've been trying to execute sql query with hibernate. I've written:

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());
createSQLQuery(sql);//has no effect. Query doesn't execute.
session.getTransaction().commit();
session.close();

This query does not executed in DB. But if I write

String sql = String.format("INSERT INTO products (name,cost) VALUES('%s',%s);", product.getName(), product.getCost());
Properties connectionProps = new Properties();
connectionProps.put("user", "postgres");
connectionProps.put("password", "123");
Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/solid",connectionProps);
conn.createStatement().execute(sql);

corresponding row will add to table. Why hibernate doesn't work, but native query wth JDBC works?

Answer 1

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

Answer 2

worked with me in spring-boot and hibernate 5.4.2 using the entity manager

 @Autowired
EntityManager em;

public List<String> getDistinctColumnValues(String tableName, String columnName) {

    List<String> result = em.createNativeQuery("select distinct (" + columnName + ") from " + tableName).getResultList();
    return result;
}

Answer 3

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