@Test // @Ignore( "Support for locking on native-sql queries not yet implemented" ) public void testNativeSql() { Session session = openSession(); session.beginTransaction(); SQLQuery qry = session.createSQLQuery( "select * from door" ); qry.addRoot( "door", Door.class ); qry.getLockOptions().setLockMode( LockMode.PESSIMISTIC_WRITE ); qry.setFirstResult( 2 ); qry.setMaxResults( 2 ); @SuppressWarnings("unchecked") List results = qry.list(); assertEquals( 2, results.size() ); for ( Object door : results ) { assertEquals( LockMode.PESSIMISTIC_WRITE, session.getCurrentLockMode( door ) ); } session.getTransaction().commit(); session.close(); }
@Test public void testNativeQueryWithFormulaAttribute() { SQLFunction dateFunction = getDialect().getFunctions().get( "current_date" ); String dateFunctionRendered = dateFunction.render( null, java.util.Collections.EMPTY_LIST, sessionFactory() ); String sql = String.format( "select t.TABLE_NAME as {t.tableName}, %s as {t.daysOld} from ALL_TABLES t where t.TABLE_NAME = 'AUDIT_ACTIONS' ", dateFunctionRendered ); String sql2 = String.format( "select TABLE_NAME as t_name, %s as t_time from ALL_TABLES where TABLE_NAME = 'AUDIT_ACTIONS' ", dateFunctionRendered ); Session s = openSession(); s.beginTransaction(); s.createSQLQuery( sql ).addEntity( "t", AllTables.class ).list(); s.createSQLQuery( sql2 ).setResultSetMapping( "all" ).list(); SQLQuery q = s.createSQLQuery( sql2 ); q.addRoot( "t", AllTables.class ).addProperty( "tableName", "t_name" ).addProperty( "daysOld", "t_time" ); q.list(); s.getTransaction().commit(); s.close(); }
@Override @Transactional public List<VocabularyWord> getWords(int vocId, int firstResult, int pageSize) { Session s = this.template.getSessionFactory().getCurrentSession(); SQLQuery query = s.createSQLQuery("SELECT {vW.*}, {m.*} FROM (SELECT * FROM vocabulary_words AS vw WHERE vw.vocabulary_id=:id LIMIT :from,:size) AS vW LEFT JOIN meaning AS m ON (m.vocabulary_words_id = vW.id);"); query.addEntity("vW", VocabularyWord.class) .addJoin("m", "vW.meaning") .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .setInteger("id", vocId) .setInteger("from", firstResult) .setInteger("size", pageSize); query.addRoot("vW", VocabularyWord.class); List l = query.list(); return l; }