Refine search
sqlQuery.setResultSetMapping( "org-description" ); sqlQuery.list(); sqlQuery.addRoot( "org", Organization.class ); sqlQuery.addFetch( "emp", "org", "employments" ); sqlQuery.list(); sqlQuery.addRoot( "org", Organization.class ) .addProperty( "id", "orgid" ) .addProperty( "name" ).addColumnAlias( "name" ); sqlQuery.addFetch( "emp", "org", "employments" ) .addProperty( "key", "employer" ) .addProperty( "element", "empid" ) .addProperty( "element.employmentId", "empId" ) .addProperty( "element.salary" ).addColumnAlias( "AMOUNT" ).addColumnAlias( "CURRENCY" ); sqlQuery.list(); sqlQuery.addScalar( "orgid", LongType.INSTANCE ) .addScalar( "name", StringType.INSTANCE ) .addScalar( "empid", LongType.INSTANCE ) .addScalar( "employee", LongType.INSTANCE ) .addScalar( "startDate", TimestampType.INSTANCE ) .addScalar( "endDate", TimestampType.INSTANCE ) .addScalar( "regionCode", StringType.INSTANCE ) .addScalar( "empId", LongType.INSTANCE ) .addScalar( "AMOUNT", FloatType.INSTANCE ) .addScalar( "CURRENCY", StringType.INSTANCE );
SQLQuery query = session.createSQLQuery("SELECT mods.*, pmr.pipelineId as pmrPipelineId, p.name as pmrPipelineName, m.type as materialType, m.fingerprint as fingerprint" + " FROM modifications mods " + " INNER JOIN pipelineMaterialRevisions pmr ON (mods.id >= pmr.fromRevisionId AND mods.id <= pmr.toRevisionId) AND mods.materialId = pmr.materialId " addEntity("mods", Modification.class). addScalar("pmrPipelineId", new LongType()). addScalar("pmrPipelineName", new StringType()). addScalar("materialType", new StringType()). addScalar("fingerprint", new StringType()). setParameterList("ids", CollectionUtil.map(relevantToLookedUpMap.keySet(), PipelineId.MAP_ID)). list();
public void store(Cargo cargo) { getSession().saveOrUpdate(cargo); // Delete-orphan does not seem to work correctly when the parent is a component getSession().createSQLQuery("delete from Leg where cargo_id = null").executeUpdate(); }
public static int updateNaturalOrderForPipeline(Session session, Long pipelineId, double naturalOrder) { String sql = "UPDATE pipelines SET naturalOrder = :naturalOrder WHERE id = :pipelineId"; SQLQuery query = session.createSQLQuery(sql); query.setLong("pipelineId", pipelineId); query.setDouble("naturalOrder", naturalOrder); return query.executeUpdate(); }
private List<Long> fromInclusiveModificationsForPipelineRange(Session session, String pipelineName, Integer fromCounter, Integer toCounter) { String pipelineIdsSql = queryExtensions.queryFromInclusiveModificationsForPipelineRange(pipelineName, fromCounter, toCounter); SQLQuery pipelineIdsQuery = session.createSQLQuery(pipelineIdsSql); final List ids = pipelineIdsQuery.list(); if (ids.isEmpty()) { return new ArrayList<>(); } String minMaxQuery = " SELECT mods1.materialId as materialId, min(mods1.id) as min, max(mods1.id) as max" + " FROM modifications mods1 " + " INNER JOIN pipelineMaterialRevisions pmr ON (mods1.id >= pmr.actualFromRevisionId AND mods1.id <= pmr.toRevisionId) AND mods1.materialId = pmr.materialId " + " WHERE pmr.pipelineId IN (:ids) " + " GROUP BY mods1.materialId"; SQLQuery query = session.createSQLQuery("SELECT mods.id " + " FROM modifications mods" + " INNER JOIN (" + minMaxQuery + ") as edges on edges.materialId = mods.materialId and mods.id >= min and mods.id <= max" + " ORDER BY mods.materialId ASC, mods.id DESC"); query.addScalar("id", new LongType()); query.setParameterList("ids", ids); return query.list(); }
private long modificationAfter(final long id, final MaterialInstance materialInstance) { BigInteger result = (BigInteger) getHibernateTemplate().execute((HibernateCallback) session -> { String sql = "SELECT id " + " FROM modifications " + " WHERE materialId = ? " + " AND id > ?" + " ORDER BY id" + " LIMIT 1"; SQLQuery query = session.createSQLQuery(sql); query.setLong(0, materialInstance.getId()); query.setLong(1, id); return query.uniqueResult(); }); return result == null ? id : result.longValue(); }
@SuppressWarnings("unchecked") private static void deleteMutations() { Session session = HibernateUtil.getSessionFactory().openSession(); Transaction tx = session.beginTransaction(); String queryString = "SELECT * from Mutation WHERE mutationType = 0"; Query query = session.createSQLQuery(queryString).addEntity( Mutation.class); List<Mutation> testCaseMutations = query.list(); for (Mutation m : testCaseMutations) { if (m.getMutationResult() != null) { session.delete(m.getMutationResult()); } session.delete(m); } logger.info("Deleted " + testCaseMutations.size() + " Mutations"); tx.commit(); session.close(); } }
session = getSessionFactory().openSession(); session.beginTransaction(); String query = ""; if (!"".equals(updateOwnerIdInPoolTableQueryString)) { query = "UPDATE SAM_QUESTIONPOOLACCESS_T SET agentid = :id WHERE questionpoolid IN (" + updateOwnerIdInPoolTableQueryString + ") AND accesstypeid = 34"; session.createSQLQuery(query).setString("id", ownerId).executeUpdate(); session.createSQLQuery(query).setString("id", ownerId).executeUpdate(); session.flush(); session.createSQLQuery(query).executeUpdate(); session.flush();
public void testCompositeIdId() throws HibernateException, SQLException { Session s = openSession(); s.beginTransaction(); CompositeIdId id = new CompositeIdId(); id.setName("Max"); id.setUser( "c64" ); id.setId("games"); s.save(id); s.getTransaction().commit(); s.close(); SQLQuery query = s.createSQLQuery( sql ).addEntity( "c", CompositeIdId.class ); query.setString(0, "c64"); query.setString(1, "games"); CompositeIdId id2 = (CompositeIdId) query.uniqueResult(); check(id, id2);
@Test public void testEmbeddedCompositeProperties() throws HibernateException, SQLException { Session session = openSession(); session.beginTransaction(); Single s = new Single(); s.setId("my id"); s.setString("string 1"); session.save(s); session.getTransaction().commit(); SQLQuery query = session.createSQLQuery( "select {sing.*} from Single {sing}" ).addEntity( "sing", Single.class ); List list = query.list(); assertTrue(list.size()==1); query = session.createSQLQuery( "select {sing.*} from Single {sing} where sing.id = ?" ).addEntity( "sing", Single.class ); query.setString(0, "my id"); list = query.list(); assertTrue(list.size()==1); query = session.createSQLQuery( "select s.id as {sing.id}, s.string_ as {sing.string}, s.prop as {sing.prop} from Single s where s.id = ?" ) .addEntity( "sing", Single.class ); query.setString(0, "my id"); list = query.list(); assertTrue(list.size()==1); query = session.createSQLQuery( "select s.id as {sing.id}, s.string_ as {sing.string}, s.prop as {sing.prop} from Single s where s.id = ?" ) .addEntity( "sing", Single.class ); query.setString(0, "my id"); list = query.list();
@Override @SuppressWarnings("unchecked") public List<String> retrieveByHint(String hint, Integer appId) { Session currentSession = sessionFactory.getCurrentSession(); Integer channelTypeId = (Integer) currentSession.createQuery( "select id from ChannelType where name = 'Manual'") .uniqueResult(); if (channelTypeId == null) { assert false : "ThreadFix was unable to find the manual channel. This indicates an incomplete database connection."; .createQuery( "select id from ApplicationChannel where applicationId = :appId and channelTypeId = :channelTypeId") .setInteger("appId", appId) .setInteger("channelTypeId", channelTypeId).uniqueResult()); if (applicationChannelId == null) return null; Integer scanId = (Integer) currentSession .createQuery( "select id from Scan where applicationId = :appId and applicationChannelId = :applicationChannelId") .setInteger("appId", appId) + "(select surfaceLocationId from Finding where scanId = :scanId) and path like " + ":hint order by path") .setString("hint", "%" + hint + "%") .setInteger("scanId", scanId).list();
/** * @param attributes attributes on a Person or Patient object. similar to: [gender, givenName, * middleName, familyName] * @return list of patients that match other patients * @see org.openmrs.api.db.PatientDAO#getDuplicatePatientsByAttributes(java.util.List) */ @SuppressWarnings("unchecked") @Override public List<Patient> getDuplicatePatientsByAttributes(List<String> attributes) { List<Patient> patients = new ArrayList<>(); List<Integer> patientIds = new ArrayList<>(); if (!attributes.isEmpty()) { String sqlString = getDuplicatePatientsSQLString(attributes); if(sqlString != null) { SQLQuery sqlquery = sessionFactory.getCurrentSession().createSQLQuery(sqlString); patientIds = sqlquery.list(); if (!patientIds.isEmpty()) { Query query = sessionFactory.getCurrentSession().createQuery( "from Patient p1 where p1.patientId in (:ids)"); query.setParameterList("ids", patientIds); patients = query.list(); } } } sortDuplicatePatients(patients, patientIds); return patients; }
private void componentTest(String sql) throws SQLException { Componentizable c = setupComponentData(); Session session = openSession(); session.beginTransaction(); SQLQuery q = session.createSQLQuery( sql ).addEntity( "comp", Componentizable.class ); List list = q.list(); assertEquals( list.size(), 1 ); Componentizable co = (Componentizable) list.get(0); assertEquals( c.getNickName(), co.getNickName() ); assertEquals( c.getComponent().getName(), co.getComponent().getName() ); assertEquals( c.getComponent().getSubComponent().getSubName(), co.getComponent().getSubComponent().getSubName() ); session.delete( co ); session.getTransaction().commit(); session.close(); }
Session session = sessionFactory.getCurrentSession(); candidateUser = (User) session.createQuery( "from User u where (u.username = ? or u.systemId = ? or u.systemId = ?) and u.retired = '0'").setString( 0, login).setString(1, login).setString(2, loginWithDash).uniqueResult(); String passwordOnRecord = (String) session.createSQLQuery("select password from users where user_id = ?") .addScalar("password", StandardBasicTypes.STRING).setInteger(0, candidateUser.getUserId()) .uniqueResult(); String saltOnRecord = (String) session.createSQLQuery("select salt from users where user_id = ?").addScalar( "salt", StandardBasicTypes.STRING).setInteger(0, candidateUser.getUserId()).uniqueResult();
/** * @see org.openmrs.api.db.EncounterDAO#getSavedEncounterDatetime(org.openmrs.Encounter) */ @Override public Date getSavedEncounterDatetime(Encounter encounter) { //Usages of this method currently are internal and don't require a flush //Otherwise we end up with premature flushes of Immutable types like Obs //that are associated to the encounter before we void and replace them Session session = sessionFactory.getCurrentSession(); FlushMode flushMode = session.getFlushMode(); session.setFlushMode(FlushMode.MANUAL); try { SQLQuery sql = session .createSQLQuery("select encounter_datetime from encounter where encounter_id = :encounterId"); sql.setInteger("encounterId", encounter.getEncounterId()); return (Date) sql.uniqueResult(); } finally { session.setFlushMode(flushMode); } }
@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(); }
public Integer CountCorpusByAttributes(Long auId, String corpusstr) { Session session = sessionFactory.getCurrentSession(); String sqlString = "SELECT b.* FROM auth_user_data_objects AS a " + "INNER JOIN corpus as b ON a.audo_uid_resource = b.crp_id " + "WHERE audo_user_id = ? AND audo_type_resource = ?"; SQLQuery qry = session.createSQLQuery(sqlString); qry.setParameter(0, auId); qry.setParameter(1, corpusstr); qry.addEntity("corpus", Corpus.class); @SuppressWarnings("unchecked") List<Corpus> result = qry.list(); return result.size(); }
@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 public Author getAuthorByUsername(String username) { Author authorDTo = null; try (Session session = sessionFactory.openSession()) { SQLQuery sqlQuery = session .createSQLQuery("SELECT a.username as \"username\", a.name as \"name\", a.email as \"email\"" + "FROM Author a where a.username=:username"); sqlQuery.setParameter("username", username); authorDTo = (Author) sqlQuery.setResultTransformer(Transformers.aliasToBean(Author.class)).uniqueResult(); } return authorDTo; }
@Test public void testRegisteredNamedSQLQueryWithScalar() { final NamedSQLQueryDefinitionBuilder builder = new NamedSQLQueryDefinitionBuilder(); builder.setName("namedQuery"); builder.setQuery("select count(*) AS c from ORGANIZATION"); builder.setQueryReturns(new NativeSQLQueryReturn[1]); sessionFactory().registerNamedSQLQueryDefinition("namedQuery", builder.createNamedQueryDefinition()); final Session s = openSession(); s.beginTransaction(); final SQLQuery query = (SQLQuery) s.getNamedQuery("namedQuery"); query.addScalar("c"); final Number result = (Number) query.uniqueResult(); s.getTransaction().commit(); s.close(); assertNotNull(result); assertTrue(0 == result.intValue()); }