@Test @FailureExpected(jiraKey = "HHH-2225") public void testNativeQueryWithFormulaAttributeWithoutAlias() { String sql = "select TABLE_NAME , sysdate() from all_tables where TABLE_NAME = 'AUDIT_ACTIONS' "; Session s = openSession(); s.beginTransaction(); s.createSQLQuery( sql ).addEntity( "t", AllTables.class ).list(); s.getTransaction().commit(); s.close(); }
@After public void cleanup() { doInHibernate( this::sessionFactory, session -> { session.createSQLQuery( "delete from MATERIAL_RATINGS" ).executeUpdate(); session.createSQLQuery( "delete from BUILDING_RATINGS" ).executeUpdate(); session.createSQLQuery( "delete from ASSOCIATION_TABLE" ).executeUpdate(); session.createSQLQuery( "delete from MAIN_TABLE" ).executeUpdate(); } ); }
@Test public void test_sql_hibernate_query_parameters_example() { doInJPA( this::entityManagerFactory, entityManager -> { Session session = entityManager.unwrap( Session.class ); //tag::sql-hibernate-query-parameters-example[] List<Person> persons = session.createNativeQuery( "SELECT * " + "FROM Person " + "WHERE name like :name" ) .addEntity( Person.class ) .setParameter("name", "J%") .list(); //end::sql-hibernate-query-parameters-example[] assertEquals(1, persons.size()); }); }
@Test @TestForIssue(jiraKey = "HHH-7368") public void testPaginationWithTrailingSemicolon() throws Exception { doInHibernate( this::sessionFactory, session -> { session.createNativeQuery( "select id from Product2 where description like 'Kit%' order by id;" ) .setFirstResult( 2 ).setMaxResults( 2 ).list(); } ); }
@Test public void setParameterWithWrongTypeShouldNotThrowIllegalArgumentException() { doInJPA(this::entityManagerFactory, entityManager -> { entityManager.createNativeQuery( "select id " + "from Event " + "where readings = :readings" ) .unwrap( NativeQuery.class ) .setParameter( "readings", new String[]{null, "a"}, StringArrayType.INSTANCE ) .getResultList(); }); }
@Test public void testNativeSQL() { doInJPA( this::entityManagerFactory, entityManager -> { List<UUID> books = entityManager.createNativeQuery( "select b.id as id " + "from Book b " + "where b.id = :id") .setParameter( "id", book.id ) .unwrap( NativeQuery.class ) .addScalar( "id", PostgresUUIDType.INSTANCE ) .getResultList(); assertEquals(1, books.size()); } ); }
.addEntity("org", Organization.class) .addJoin("emp", "org.employments") .addScalar("regionCode", StringType.INSTANCE) .list(); assertEquals( 2, l.size() ); .addEntity("org", Organization.class) .addJoin("emp", "org.employments") .addJoin("pers", "emp.employee") .list(); assertEquals( l.size(), 1 ); "from ORGANIZATION org " + " left outer join EMPLOYMENT emp on org.ORGID = emp.EMPLOYER, ORGANIZATION org2" ) .addEntity("org", Organization.class) .addJoin("emp", "org.employments") .setResultTransformer( DistinctRootEntityResultTransformer.INSTANCE ) .list(); assertEquals( l.size(), 2 );
public final static <T> List<T> findObjectsBySql(BaseDaoImpl<?, ?> baseDao, String ssql, Object[] values, PageDesc pageDesc, Class<T> objectType) { int startPos = 0; int maxSize = 0; if(pageDesc!=null){ startPos = pageDesc.getRowStart(); maxSize = pageDesc.getPageSize(); } NativeQuery q = baseDao.getCurrentSession().createNativeQuery(ssql); setQueryParameter(q,values); if (maxSize > 0) q.setMaxResults(maxSize); if (startPos >= 0) q.setFirstResult(startPos); if(objectType!=null) q.addEntity(objectType); List<T> l = q.list(); if(l!=null && pageDesc!=null){ if(maxSize>0){ q = baseDao.getCurrentSession().createNativeQuery(QueryUtils.buildGetCountSQL(ssql)); setQueryParameter(q,values); pageDesc.setTotalRows(Integer.valueOf(q.list().get(0).toString())); }else pageDesc.setTotalRows(l.size()); } return l; }
@Test public void test_sql_hibernate_query_scalar_explicit_result_set_example() { doInJPA( this::entityManagerFactory, entityManager -> { Session session = entityManager.unwrap( Session.class ); //tag::sql-hibernate-scalar-query-explicit-result-set-example[] List<Object[]> persons = session.createNativeQuery( "SELECT * FROM Person" ) .addScalar( "id", LongType.INSTANCE ) .addScalar( "name", StringType.INSTANCE ) .list(); for(Object[] person : persons) { Long id = (Long) person[0]; String name = (String) person[1]; } //end::sql-hibernate-scalar-query-explicit-result-set-example[] assertEquals(3, persons.size()); }); }
@Test public void test_sql_hibernate_entity_associations_query_many_to_one_join_example() { doInJPA( this::entityManagerFactory, entityManager -> { Session session = entityManager.unwrap( Session.class ); //tag::sql-hibernate-entity-associations-query-many-to-one-join-example[] List<Object[]> tuples = session.createNativeQuery( "SELECT * " + "FROM Phone ph " + "JOIN Person pr ON ph.person_id = pr.id" ) .addEntity("phone", Phone.class ) .addJoin( "pr", "phone.person") .list(); for(Object[] tuple : tuples) { Phone phone = (Phone) tuple[0]; Person person = (Person) tuple[1]; assertNotNull( person.getName() ); } //end::sql-hibernate-entity-associations-query-many-to-one-join-example[] assertEquals(3, tuples.size()); }); }
); _session.createNativeQuery( updateStatement() ) .setParameter( "value", "changed" ) .setParameter( "id", it.getId() ) .executeUpdate(); fail( "Pessimistic lock not obtained/held" );
@Test public void test_sql_hibernate_entity_associations_query_one_to_many_join_example_1() { try { doInJPA( this::entityManagerFactory, entityManager -> { Session session = entityManager.unwrap( Session.class ); List<Phone> phones = session.createNativeQuery( "SELECT * " + "FROM Phone ph " + "JOIN phone_call c ON c.phone_id = ph.id" ) .addEntity("phone", Phone.class ) .addJoin( "c", "phone.calls") .setResultTransformer( Criteria.DISTINCT_ROOT_ENTITY ) .list(); for(Phone phone : phones) { List<Call> calls = phone.getCalls(); } assertEquals(2, phones.size()); }); } catch (Exception e) { log.error( "HHH-10504", e ); //See issue https://hibernate.atlassian.net/browse/HHH-10504 } }
.setParameter( "chaos_size", null, StandardBasicTypes.LONG ) .list(); assertEquals( 1, chaoses.size() ); .setParameter( "chaos_size", null, StandardBasicTypes.LONG ) .list();
@Test public void testFlushAutoSQLNativeSession() { doInHibernate( this::sessionFactory, session -> { session.createNativeQuery( "delete from Person" ).executeUpdate();; } ); doInHibernate( this::sessionFactory, session -> { log.info( "testFlushAutoSQLNativeSession" ); //tag::flushing-auto-flush-sql-native-example[] assertTrue(((Number) session .createNativeQuery( "select count(*) from Person") .getSingleResult()).intValue() == 0 ); Person person = new Person( "John Doe" ); session.persist( person ); assertTrue(((Number) session .createNativeQuery( "select count(*) from Person") .uniqueResult()).intValue() == 0 ); //end::flushing-auto-flush-sql-native-example[] } ); }
@Override protected Result check() throws Exception { return timeBoundHealthCheck.check(() -> { try (Session session = sessionFactory.openSession()) { final Transaction txn = session.beginTransaction(); try { session.createNativeQuery(validationQuery).list(); txn.commit(); } catch (Exception e) { if (txn.getStatus().canRollback()) { txn.rollback(); } throw e; } } return Result.healthy(); }); } }
@Test public void testTS() throws Exception { Session session = openSession(); Transaction txn = session.beginTransaction(); Simple sim = new Simple( Long.valueOf(1) ); sim.setDate( new Date() ); session.save( sim ); Query q = session.createSQLQuery( "select {sim.*} from SimpleEntity {sim} where {sim}.date_ = ?" ).addEntity( "sim", Simple.class ); q.setTimestamp( 0, sim.getDate() ); assertTrue ( q.list().size()==1 ); session.delete(sim); txn.commit(); session.close(); }
@Test public void test() { List<PostComment> comments = doInJPA(entityManager -> { return (List<PostComment>) entityManager.createNativeQuery( "SELECT * " + "FROM PostComment c " + "WHERE c.status = :status " + "CONNECT BY PRIOR c.id = c.parent_id " + "START WITH c.parent_id IS NULL AND lower(c.description) like :token ") .setParameter("status", Status.APPROVED.name()) .setParameter("token", "high-performance%") .unwrap(NativeQuery.class) .addEntity(PostComment.class) .setResultTransformer(PostCommentTreeTransformer.INSTANCE) .list(); }); assertEquals(1, comments.size()); } }
@Test public void test2ndLevelCacheWithNativeUpdateStatementSynchronization() { doInJPA(entityManager -> { assertEquals(1, getLatestPostComments(entityManager).size()); printQueryCacheRegionStatistics(); LOGGER.info("Execute native query with synchronization"); entityManager.createNativeQuery( "UPDATE post SET title = '\"'||title||'\"' ") .unwrap(NativeQuery.class) .addSynchronizedEntityClass(Post.class) .executeUpdate(); assertEquals(1, getLatestPostComments(entityManager).size()); printQueryCacheRegionStatistics(); }); }
public int getMaxIdLength() { String queryString = "select max(length(id)) as maxidlen from sysconfig"; if (prefix != null) queryString += " where id like :query"; NativeQuery query = db.session().createNativeQuery(queryString); if (prefix != null) query.setParameter ("query", prefix + "%"); return (int) query.addScalar("maxidlen", IntegerType.INSTANCE).getSingleResult(); } }
private int countInsertedRows(Session s) { return ((Number) s.createSQLQuery( "SELECT COUNT(*) FROM sequenceIdentifier" ) .uniqueResult()).intValue(); }