public Object doInHibernate(Session session) throws HibernateException, SQLException {
// Returns all the lifecycle values and the amount of entries for a specific
// seal level for all MassnahmenUmsetzung instances which have not been
// fullfilled (see {@link MassnahmenUmsetzung#isCompleted).
Query query =
session
.createSQLQuery(
"select p1.propertyvalue as pv, count(p1.propertyvalue) as amount "
+ "from properties p1 inner join propertylist pl1 on p1.properties_id=pl1.dbid, "
+ "properties p2 inner join propertylist pl2 on p2.properties_id=pl2.dbid "
+ "where pl1.typedlist_id = pl2.typedlist_id "
+ "and p1.propertytype = :p1type "
+ "and p2.propertytype = :p2type "
+ "and p2.propertyvalue not in (:p2values) "
+ "group by p1.propertyvalue ")
.addScalar("pv", Hibernate.STRING)
.addScalar("amount", Hibernate.INTEGER)
.setString("p1type", MassnahmenUmsetzung.P_LEBENSZYKLUS)
.setString("p2type", MassnahmenUmsetzung.P_UMSETZUNG)
.setParameterList(
"p2values",
new Object[] {
MassnahmenUmsetzung.P_UMSETZUNG_JA, MassnahmenUmsetzung.P_UMSETZUNG_ENTBEHRLICH
},
Hibernate.STRING);
if (LOG.isDebugEnabled()) {
LOG.debug("generated query:" + query.getQueryString());
}
return query.list();
}
private <T> List<T> executeQuery(
Query query, int numColumns, LastModificationDateFactory<T> lastModificationDateFactory) {
query.setReadOnly(true);
query.setCacheable(false);
ScrollableResults scrollableResults = query.scroll(ScrollMode.FORWARD_ONLY);
final List<T> dates = new ArrayList<>();
int resultsCount = 0;
int datesCount = 0;
while (scrollableResults.next()) {
resultsCount++;
Object[] row = scrollableResults.get();
T date = extractLastModificationDate(row, numColumns, lastModificationDateFactory);
if (date != null) {
dates.add(date);
datesCount++;
}
}
if (resultsCount > datesCount) {
LOGGER.warn(
"Got {} in result set and {} dates. query= {}",
resultsCount,
datesCount,
query.getQueryString());
} else {
LOGGER.debug("Got {} in result set and {} dates.", resultsCount, datesCount);
}
return dates;
}
@SuppressWarnings("unchecked")
public List<T> select(final String hql, final List<Object> values) {
Session session = getSession();
if (values.isEmpty()) {
return session.createQuery(hql).list();
} else {
Query query = session.createQuery(hql);
int pos = 0;
for (Object value : values) {
query.setParameter(pos++, value);
}
logger.debug(query.getQueryString());
return query.list();
}
}
private void fillInNamedQueryBuilder(
NamedQueryDefinitionBuilder builder, org.hibernate.Query query) {
builder
.setQuery(query.getQueryString())
.setComment(query.getComment())
.setCacheable(query.isCacheable())
.setCacheRegion(query.getCacheRegion())
.setCacheMode(query.getCacheMode())
.setTimeout(query.getTimeout())
.setFetchSize(query.getFetchSize())
.setFirstResult(query.getFirstResult())
.setMaxResults(query.getMaxResults())
.setReadOnly(query.isReadOnly())
.setFlushMode(query.getFlushMode());
}
public static void main(final String[] args) throws Exception {
final Session session = getSession();
try {
System.out.println("querying all the managed entities...");
final Map metadataMap = session.getSessionFactory().getAllClassMetadata();
for (Object key : metadataMap.keySet()) {
final ClassMetadata classMetadata = (ClassMetadata) metadataMap.get(key);
final String entityName = classMetadata.getEntityName();
final Query query = session.createQuery("from " + entityName);
System.out.println("executing: " + query.getQueryString());
for (Object o : query.list()) {
System.out.println(" " + o);
}
}
} finally {
session.close();
}
}
@Test
public void entityMappingTest() {
try {
log.info("querying all the managed entities...");
final Map<String, ClassMetadata> metadataMap =
session.getSessionFactory().getAllClassMetadata();
for (Object key : metadataMap.keySet()) {
final ClassMetadata classMetadata = metadataMap.get(key);
final String entityName = classMetadata.getEntityName();
final Query query = session.createQuery("from " + entityName);
query.setCacheable(true);
log.info("executing hql= " + query.getQueryString());
for (Object o : query.list()) {
log.info("IEntity= " + o);
}
}
} finally {
session.flush();
}
log.info("성공했습니다.");
}
/* (non-Javadoc)
* @see it.eng.qbe.export.IQueryRewriter#rewrite(java.lang.String)
*/
public String rewrite(String query) {
String sqlQuery = null;
logger.debug("rewrite: HQL query to convert: " + query);
Query hibQuery = session.createQuery(query);
SessionFactory sessionFactory = session.getSessionFactory();
SessionFactoryImplementor sessionFactoryImplementor =
(SessionFactoryImplementor) sessionFactory;
ASTQueryTranslatorFactory astQueryTranslatorFactory = new ASTQueryTranslatorFactory();
QueryTranslator queryTranslator = null;
Class[] parsTypes = null;
Method createQueryTranslatorMethod = null;
try {
// Hibernate 3.0
parsTypes = new Class[3];
parsTypes[0] = String.class;
parsTypes[1] = Map.class;
parsTypes[2] = SessionFactoryImplementor.class;
createQueryTranslatorMethod =
astQueryTranslatorFactory.getClass().getMethod("createQueryTranslator", parsTypes);
try {
queryTranslator =
(QueryTranslator)
createQueryTranslatorMethod.invoke(
astQueryTranslatorFactory,
new Object[] {
hibQuery.getQueryString(), Collections.EMPTY_MAP, sessionFactoryImplementor
});
} catch (Throwable e) {
e.printStackTrace();
}
} catch (NoSuchMethodException e) {
parsTypes = new Class[4];
parsTypes[0] = String.class;
parsTypes[1] = String.class;
parsTypes[2] = Map.class;
parsTypes[3] = SessionFactoryImplementor.class;
try {
createQueryTranslatorMethod =
astQueryTranslatorFactory.getClass().getMethod("createQueryTranslator", parsTypes);
if (createQueryTranslatorMethod != null) {
try {
queryTranslator =
(QueryTranslator)
createQueryTranslatorMethod.invoke(
astQueryTranslatorFactory,
new Object[] {
String.valueOf(System.currentTimeMillis()),
hibQuery.getQueryString(),
Collections.EMPTY_MAP,
sessionFactoryImplementor
});
} catch (Throwable t) {
t.printStackTrace();
}
}
} catch (NoSuchMethodException ex) {
e.printStackTrace();
}
}
queryTranslator.compile(new HashMap(), false);
sqlQuery = queryTranslator.getSQLString();
logger.debug("rewrite: generated SQL query: " + sqlQuery);
return sqlQuery;
}
/**
* Fetch all the matching patients from all the community and all assigning authorities on a known
* id.
*
* @param Patient
* @return Patient
*/
public List<Patient> findPatients(Patient patient) {
log.debug("PatientDAO.findAllPatients() - Begin");
Session session = null;
List<Patient> patientsList = new ArrayList<Patient>();
try {
SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
session = sessionFactory.openSession();
log.info("Reading Records...");
// NHIN required query parameters
String gender = patient.getGender();
Timestamp dateOfBirth = patient.getDateOfBirth();
String firstName = patient.getPersonnames().get(0).getFirstName();
String lastName = patient.getPersonnames().get(0).getLastName();
// NHIN optional query parameters
String ssn = patient.getSsn();
String prefix = patient.getPersonnames().get(0).getPrefix();
String middleName = patient.getPersonnames().get(0).getMiddleName();
String suffix = patient.getPersonnames().get(0).getSuffix();
Address address = new Address();
if (patient.getAddresses() != null && patient.getAddresses().size() > 0) {
address = patient.getAddresses().get(0);
}
Phonenumber phonenumber = new Phonenumber();
if (patient.getPhonenumbers() != null && patient.getPhonenumbers().size() > 0) {
phonenumber = patient.getPhonenumbers().get(0);
}
// Build the select with query criteria
StringBuffer sqlSelect =
new StringBuffer(
"SELECT DISTINCT p.patientId, p.dateOfBirth, p.gender, p.ssn, i.id, i.organizationid");
sqlSelect.append(" FROM patientdb.patient p");
sqlSelect.append(" INNER JOIN patientdb.identifier i ON p.patientId = i.patientId");
sqlSelect.append(" INNER JOIN patientdb.personname n ON p.patientId = n.patientId");
if (address.getAddressId() != null) {
sqlSelect.append(" INNER JOIN patientdb.address a ON p.patientId = a.patientId");
}
if (phonenumber.getPhonenumberId() != null) {
sqlSelect.append(" INNER JOIN patientdb.phonenumber h ON p.patientId = h.patientId");
}
StringBuffer criteriaString = new StringBuffer("");
if (NullChecker.isNotNullish(gender)) {
if (criteriaString.length() > 0) {
criteriaString.append(" AND");
} else {
criteriaString.append(" WHERE");
}
criteriaString.append(" p.gender = ?");
}
if (dateOfBirth != null) {
if (criteriaString.length() > 0) {
criteriaString.append(" AND");
} else {
criteriaString.append(" WHERE");
}
criteriaString.append(" p.dateOfBirth = ?");
}
if (NullChecker.isNotNullish(firstName)) {
if (criteriaString.length() > 0) {
criteriaString.append(" AND");
} else {
criteriaString.append(" WHERE");
}
criteriaString.append(" n.firstname = ?");
}
if (NullChecker.isNotNullish(lastName)) {
if (criteriaString.length() > 0) {
criteriaString.append(" AND");
} else {
criteriaString.append(" WHERE");
}
criteriaString.append(" n.lastname = ?");
}
if (NullChecker.isNotNullish(ssn)) {
if (criteriaString.length() > 0) {
criteriaString.append(" AND");
} else {
criteriaString.append(" WHERE");
}
criteriaString.append(" p.ssn = ?");
}
if (NullChecker.isNotNullish(prefix)) {
if (criteriaString.length() > 0) {
criteriaString.append(" AND");
} else {
criteriaString.append(" WHERE");
}
criteriaString.append(" n.prefix = ?");
}
if (NullChecker.isNotNullish(middleName)) {
if (criteriaString.length() > 0) {
criteriaString.append(" AND");
} else {
criteriaString.append(" WHERE");
}
criteriaString.append(" n.middleName = ?");
}
if (NullChecker.isNotNullish(suffix)) {
if (criteriaString.length() > 0) {
criteriaString.append(" AND");
} else {
criteriaString.append(" WHERE");
}
criteriaString.append(" n.suffix = ?");
}
if (address.getAddressId() != null && NullChecker.isNotNullish(address.getStreet1())) {
if (criteriaString.length() > 0) {
criteriaString.append(" AND");
} else {
criteriaString.append(" WHERE");
}
criteriaString.append(" a.street1 = ?");
}
if (address.getAddressId() != null && NullChecker.isNotNullish(address.getStreet2())) {
if (criteriaString.length() > 0) {
criteriaString.append(" AND");
} else {
criteriaString.append(" WHERE");
}
criteriaString.append(" a.street2 = ?");
}
if (address.getAddressId() != null && NullChecker.isNotNullish(address.getCity())) {
if (criteriaString.length() > 0) {
criteriaString.append(" AND");
} else {
criteriaString.append(" WHERE");
}
criteriaString.append(" a.city = ?");
}
if (address.getAddressId() != null && NullChecker.isNotNullish(address.getState())) {
if (criteriaString.length() > 0) {
criteriaString.append(" AND");
} else {
criteriaString.append(" WHERE");
}
criteriaString.append(" a.state = ?");
}
if (address.getAddressId() != null && NullChecker.isNotNullish(address.getPostal())) {
if (criteriaString.length() > 0) {
criteriaString.append(" AND");
} else {
criteriaString.append(" WHERE");
}
criteriaString.append(" a.postal = ?");
}
if (phonenumber.getPhonenumberId() != null
&& NullChecker.isNotNullish(phonenumber.getValue())) {
if (criteriaString.length() > 0) {
criteriaString.append(" AND");
} else {
criteriaString.append(" WHERE");
}
criteriaString.append(" h.value = ?");
}
sqlSelect.append(criteriaString);
sqlSelect.append(" ORDER BY i.id, i.organizationid");
Query sqlQuery =
session
.createSQLQuery(sqlSelect.toString())
.addScalar("patientId", Hibernate.LONG)
.addScalar("dateOfBirth", Hibernate.TIMESTAMP)
.addScalar("gender", Hibernate.STRING)
.addScalar("ssn", Hibernate.STRING)
.addScalar("id", Hibernate.STRING)
.addScalar("organizationid", Hibernate.STRING);
int iParam = 0;
if (NullChecker.isNotNullish(gender)) {
sqlQuery.setString(iParam, gender);
iParam++;
}
if (dateOfBirth != null) {
sqlQuery.setTimestamp(iParam, dateOfBirth);
iParam++;
}
if (NullChecker.isNotNullish(firstName)) {
sqlQuery.setString(iParam, firstName);
iParam++;
}
if (NullChecker.isNotNullish(lastName)) {
sqlQuery.setString(iParam, lastName);
iParam++;
}
if (NullChecker.isNotNullish(ssn)) {
sqlQuery.setString(iParam, ssn);
iParam++;
}
if (NullChecker.isNotNullish(prefix)) {
sqlQuery.setString(iParam, prefix);
iParam++;
}
if (NullChecker.isNotNullish(middleName)) {
sqlQuery.setString(iParam, middleName);
iParam++;
}
if (NullChecker.isNotNullish(suffix)) {
sqlQuery.setString(iParam, suffix);
iParam++;
}
if (address.getAddressId() != null && NullChecker.isNotNullish(address.getStreet1())) {
sqlQuery.setString(iParam, address.getStreet1());
iParam++;
}
if (address.getAddressId() != null && NullChecker.isNotNullish(address.getStreet2())) {
sqlQuery.setString(iParam, address.getStreet2());
iParam++;
}
if (address.getAddressId() != null && NullChecker.isNotNullish(address.getCity())) {
sqlQuery.setString(iParam, address.getCity());
iParam++;
}
if (address.getAddressId() != null && NullChecker.isNotNullish(address.getState())) {
sqlQuery.setString(iParam, address.getState());
iParam++;
}
if (address.getAddressId() != null && NullChecker.isNotNullish(address.getPostal())) {
sqlQuery.setString(iParam, address.getPostal());
iParam++;
}
if (phonenumber.getPhonenumberId() != null
&& NullChecker.isNotNullish(phonenumber.getValue())) {
sqlQuery.setString(iParam, phonenumber.getValue());
iParam++;
}
log.debug("Final SQL Query is " + sqlQuery.getQueryString());
List<Object[]> result = sqlQuery.list();
if (result != null && result.size() > 0) {
Long[] patientIdArray = new Long[result.size()];
Timestamp[] dateOfBirthArray = new Timestamp[result.size()];
String[] genderArray = new String[result.size()];
String[] ssnArray = new String[result.size()];
String[] idArray = new String[result.size()];
String[] organizationIdArray = new String[result.size()];
int counter = 0;
for (Object[] row : result) {
patientIdArray[counter] = (Long) row[0];
dateOfBirthArray[counter] = (Timestamp) row[1];
genderArray[counter] = row[2].toString();
ssnArray[counter] = row[3].toString();
idArray[counter] = row[4].toString();
organizationIdArray[counter] = row[5].toString();
counter++;
}
for (int i = 0; i < patientIdArray.length; i++) {
Patient patientData = new Patient();
patientData.setPatientId(patientIdArray[i]);
patientData.setDateOfBirth(dateOfBirthArray[i]);
patientData.setGender(genderArray[i]);
patientData.setSsn(ssnArray[i]);
Identifier identifierData = new Identifier();
identifierData.getPatient().setPatientId(patientIdArray[i]);
identifierData.setId(idArray[i]);
identifierData.setOrganizationId(organizationIdArray[i]);
patientData.getIdentifiers().add(identifierData);
// Populate demographic data
patientData.setAddresses(
AddressDAO.getAddressDAOInstance().findPatientAddresses(patientIdArray[i]));
patientData.setPersonnames(
PersonnameDAO.getPersonnameDAOInstance().findPatientPersonnames(patientIdArray[i]));
patientData.setPhonenumbers(
PhonenumberDAO.getPhonenumberDAOInstance()
.findPatientPhonenumbers(patientIdArray[i]));
patientsList.add(patientData);
}
}
} catch (Exception e) {
log.error("Exception during read occured due to : " + e.getMessage(), e);
} finally {
// Flush and close session
if (session != null) {
session.flush();
session.close();
}
}
log.debug("PatientDAO.findPatients() - End");
return patientsList;
}
