List of usage examples for javax.persistence EntityManager createNativeQuery
public Query createNativeQuery(String sqlString, String resultSetMapping);
Query for executing a native SQL query. From source file:com.xidu.framework.common.dao.impl.BaseDaoImpl.java
/** * /*www . j ava2 s . c om*/ * {@inheritDoc} overridden: * * @Date : 2011-3-28 * @see com.xidu.framework.common.dao.IBaseDao#getListByNativeQueryWithMapAndRSMapping(java.lang.String, * java.lang.String, java.util.Map) * */ @SuppressWarnings("unchecked") @Override public List<T> getListByNativeQueryWithMapAndRSMapping(final String nativeQuerySql, final String sqlResultSetMappingName, final Map<String, ?> paraMap) { return getJpaTemplate().executeFind(new JpaCallback<List<T>>() { @Override public List<T> doInJpa(EntityManager em) throws PersistenceException { Query nativeQuery = em.createNativeQuery(nativeQuerySql, sqlResultSetMappingName); prepareQueryParam(nativeQuery, paraMap); return nativeQuery.getResultList(); } }); }
From source file:com.xidu.framework.common.dao.impl.BaseDaoImpl.java
/** * /*from www. j a v a 2 s . c om*/ * {@inheritDoc} overridden: * * @Date : 2011-3-28 * @see com.xidu.framework.common.dao.IBaseDao#getListByNativeQueryWithMap(java.lang.String, * java.util.Map) * */ @Override public List<T> getListByNativeQueryWithMap(final String nativeQuerySql, final Map<String, ?> paraMap) { return getJpaTemplate().execute(new JpaCallback<List<T>>() { @SuppressWarnings("unchecked") @Override public List<T> doInJpa(EntityManager em) throws PersistenceException { Query nativeQuery = em.createNativeQuery(nativeQuerySql, clazz); prepareQueryParam(nativeQuery, paraMap); return nativeQuery.getResultList(); } }); }
From source file:com.xidu.framework.common.dao.impl.BaseDaoImpl.java
/** * //from w w w . j a v a2 s. c o m * {@inheritDoc} overridden: * * @Date : 2011-3-28 * @see com.xidu.framework.common.dao.IBaseDao#getListByNativeQueryWithVaParam(java.lang.String, * java.lang.Object[]) * */ @Override public List<T> getListByNativeQueryWithVaParam(final String nativeQuerySql, final Object... params) { return getJpaTemplate().execute(new JpaCallback<List<T>>() { @SuppressWarnings("unchecked") @Override public List<T> doInJpa(EntityManager em) throws PersistenceException { Query nativeQuery = em.createNativeQuery(nativeQuerySql, clazz); prepareQueryParam(nativeQuery, params); return nativeQuery.getResultList(); } }); }
From source file:com.xidu.framework.common.dao.impl.BaseDaoImpl.java
/** * /*from w w w. j av a 2s.c o m*/ * {@inheritDoc} overridden: * * @Date : 2011-3-28 * @see com.xidu.framework.common.dao.IBaseDao#getListByNativeQueryWithVaParamAndRSMapping(java.lang.String, * java.lang.String, java.lang.Object[]) * */ @SuppressWarnings("unchecked") @Override public List<T> getListByNativeQueryWithVaParamAndRSMapping(final String nativeQuerySql, final String sqlResultSetMappingName, final Object... params) { return getJpaTemplate().executeFind(new JpaCallback<List<T>>() { @Override public List<T> doInJpa(EntityManager em) throws PersistenceException { Query nativeQuery = em.createNativeQuery(nativeQuerySql, sqlResultSetMappingName); prepareQueryParam(nativeQuery, params); return nativeQuery.getResultList(); } }); }
From source file:com.adeptj.modules.data.jpa.core.AbstractJpaRepository.java
/** * {@inheritDoc}// w w w .ja v a 2 s . c o m */ @Override public <T> T getScalarResultOfType(Class<T> resultClass, QueryType type, String query, List<Object> posParams) { EntityManager em = JpaUtil.createEntityManager(this.getEntityManagerFactory()); try { switch (type) { case JPA: TypedQuery<T> typedQuery = em.createQuery(query, resultClass); JpaUtil.bindQueryParams(typedQuery, posParams); return typedQuery.getSingleResult(); case NATIVE: Query nativeQuery = em.createNativeQuery(query, resultClass); JpaUtil.bindQueryParams(nativeQuery, posParams); return resultClass.cast(nativeQuery.getSingleResult()); default: throw new IllegalStateException("Invalid QueryType!!"); } } catch (Exception ex) { // NOSONAR throw new JpaException(ex); } finally { JpaUtil.closeEntityManager(em); } }
From source file:us.co.douglas.assessor.dao.AccountDAOImpl.java
private Sale getSale(String accountNo) { log.info("getSale()..."); EntityManager entityManager = getEntityManager(); try {//ww w. ja v a 2s. c om String sqlQuery = "SELECT TBLSALE.*, TBLSALEACCT.*, TBNSALEINVENTORY.* " + "FROM ENCOMPASS.TBLSALE TBLSALE " + "JOIN ENCOMPASS.TBLSALEACCT TBLSALEACCT ON TBLSALEACCT.RECEPTIONNO = TBLSALE.RECEPTIONNO " + "JOIN ENCOMPASS.TBNSALEINVENTORY TBNSALEINVENTORY ON TBNSALEINVENTORY.RECEPTIONNO = TBLSALE.RECEPTIONNO " + "INNER JOIN " + " (SELECT TBLSALEACCT.ACCOUNTNO, MAX(TBLSALEACCT.INVENTORYEFFECTIVEDATE) AS MAXDATE FROM ENCOMPASS.TBLSALEACCT GROUP BY TBLSALEACCT.ACCOUNTNO) TM " + " ON TBLSALEACCT.ACCOUNTNO = TM.ACCOUNTNO AND TBLSALEACCT.INVENTORYEFFECTIVEDATE = TM.MAXDATE " + "WHERE TBLSALEACCT.VEREND = 99999999999 " + "AND TBLSALE.VEREND = 99999999999 " + "AND TBLSALEACCT.ACCOUNTNO = :accountNo ORDER BY TBLSALE.SALEDATE DESC"; log.info("sqlQuery: " + sqlQuery); Query query = entityManager.createNativeQuery(sqlQuery, Sale.class); query.setMaxResults(maxResults); query.setParameter("accountNo", accountNo); List<Sale> saleList = query.getResultList(); log.info("saleList: " + saleList.size()); if (saleList.size() > 0) { return saleList.get(0); } else { return null; } } catch (Exception ex) { throw new RuntimeException(ex); } finally { entityManager.close(); } }
From source file:commonSession.PersistAttivitaFacadeBean.java
public List resultListNativeQuery(String sql, Class clazz, Map<String, Object> parametri, String unit, String orderBy) throws Exception { EntityManager em = GestionaleEm; String query = null;//from w ww .j a va 2s. co m // if(unit.equals("GestionaleEm")) em = GestionaleEm; // else em = TerrEm; final StringBuilder str = new StringBuilder(sql + " WHERE "); for (Map.Entry<String, Object> elemento : parametri.entrySet()) { String s = null; // elemento.getKey(); // elemento.getValue(); // equals("id_ente") if (elemento.getKey().contains("id_ente")) s = elemento.getKey() + " = " + elemento.getValue() + " AND "; else s = "UPPER(" + elemento.getKey() + ")" + " LIKE " + "UPPER('%" + elemento.getValue() + "%')" + " AND "; str.append(s); } Integer lunghezza = str.length() - 5; query = str.toString(); query = query.substring(0, lunghezza) + " ORDER BY " + orderBy; Query q = em.createNativeQuery(query, clazz); return q.getResultList(); }
From source file:us.co.douglas.assessor.dao.AccountDAOImpl.java
public PropertyAddress getPropertyAddress(String accountNo) { log.info("getPropertyAddress()..."); EntityManager entityManager = getEntityManager(); try {/*from ww w. j a v a2 s. c o m*/ String sqlQuery = "SELECT " + "TBLACCT.ACCOUNTNO, " + "ISNULL(TBLACCTPROPERTYADDRESS.PREDIRECTION, '') AS PREDIRECTION, " + "ISNULL(TBLACCTPROPERTYADDRESS.POSTDIRECTION, '') AS POSTDIRECTION, " + "ISNULL(TBLACCTPROPERTYADDRESS.STREETNO, '') AS STREETNUMBER, " + "ISNULL(TBLACCTPROPERTYADDRESS.UNITNAME, '') AS UNITNAME, " + "ISNULL(TBLACCTPROPERTYADDRESS.STREETTYPE, '') AS STREETTYPE, " + "ISNULL(TBLACCTPROPERTYADDRESS.STREETNAME, '') AS STREETNAME, " + "ISNULL(SUBSTRING(TBLACCTPROPERTYADDRESS.PROPERTYZIPCODE, 1, 5), '') AS ZIPCODE, " + "ISNULL(TBLACCTPROPERTYADDRESS.PROPERTYCITY, '') AS CITYNAME, " + "'CO' AS STATENAME " + "FROM " + "ENCOMPASS.TBLACCT TBLACCT " + "JOIN ENCOMPASS.TBLACCTPROPERTYADDRESS TBLACCTPROPERTYADDRESS ON TBLACCTPROPERTYADDRESS.ACCOUNTNO = TBLACCT.ACCOUNTNO " + "WHERE TBLACCT.VEREND = 99999999999 AND TBLACCTPROPERTYADDRESS.VEREND = 99999999999 " + "AND TBLACCT.ACCOUNTNO = :accountNo " + "ORDER BY TBLACCT.ACCOUNTNO DESC"; log.info("sqlQuery: " + sqlQuery); Query query = entityManager.createNativeQuery(sqlQuery, PropertyAddress.class); query.setMaxResults(maxResults); query.setParameter("accountNo", accountNo); List<PropertyAddress> addressList = query.getResultList(); if (addressList.size() > 0) { return addressList.get(0); } else { return null; } } catch (Exception ex) { throw new RuntimeException(ex); } finally { entityManager.close(); } }
From source file:com.remediatetheflag.global.persistence.HibernatePersistenceFacade.java
public User getUser(String username, String password) { EntityManager em = getHibernateEntityManager(); User user;/*from ww w . j a va 2 s . c o m*/ try { user = (User) em.createNativeQuery( "SELECT * FROM users WHERE username = :usr AND password = SHA2(CONCAT(:pwd,(SELECT salt FROM users WHERE username = :usr)),512)", User.class).setParameter("usr", username).setParameter("pwd", password).getSingleResult(); em.close(); return user; } catch (Exception e) { em.close(); logger.error("Login Failed for " + username + ": " + e.getMessage()); return null; } }
From source file:us.co.douglas.assessor.dao.AccountDAOImpl.java
public List<BasicAccountInfo> getAllSearchableParcels() { log.info("getAllSearchableParcels..."); List<BasicAccountInfo> allSearchableParcels = new ArrayList<BasicAccountInfo>(); EntityManager entityManager = getEntityManager(); try {//from w w w . j a v a 2 s . c om String sqlQuery = "select " + "ISNULL(TBLACCT.ACCOUNTNO, '') as accountNo, " + "ISNULL(TBLACCT.PARCELNO, '') as parcelNo, " + "ISNULL(TBLPERSONSECURE.NAME1, '') + ' ' + ISNULL(TBLPERSONSECURE.NAME2, '') as ownerName, " + "ISNULL(TBLACCT.BUSINESSNAME, '') as businessName, " + "ISNULL(TBLACCT.BUSINESSLICENSE, '') as businessLicense, " + "ISNULL(TBLACCTNBHD.NBHDCODE, '') as neighborhoodCode, " + "ISNULL(TBLACCTNBHD.NBHDEXTENSION, '') as neighborhoodExt, " + "ISNULL(TBLACCTPROPERTYADDRESS.STREETNO, '') + ' ' + ISNULL(TBLACCTPROPERTYADDRESS.STREETNAME, '') + ' ' + ISNULL(TBLACCTPROPERTYADDRESS.STREETTYPE, '') + ' ' + ISNULL(TBLACCTPROPERTYADDRESS.UNITNAME, '') as propertyStreet, " + "ISNULL(TBLACCTPROPERTYADDRESS.PROPERTYCITY, '') as propertyCity, " + "'CO' as propertyState, + " + "ISNULL(SUBSTRING(TBLACCTPROPERTYADDRESS.PROPERTYZIPCODE, 1, 5), '') as propertyZipCode, " + "ISNULL(TBNSUBDIVISION.SUBNAME, '') as subdivisionName " + "from encompass.TBLACCT TBLACCT " + "right outer join encompass.TBLACCTPROPERTYADDRESS TBLACCTPROPERTYADDRESS on TBLACCTPROPERTYADDRESS.ACCOUNTNO = TBLACCT.ACCOUNTNO " + "right outer join encompass.TBLACCTOWNERADDRESS TBLACCTOWNERADDRESS on TBLACCTOWNERADDRESS.ACCOUNTNO = TBLACCT.ACCOUNTNO " + "right outer join encompass.TBLPERSONSECURE TBLPERSONSECURE on TBLPERSONSECURE.PERSONCODE = TBLACCTOWNERADDRESS.PERSONCODE " + "right outer join encompass.TBLADDRESSSECURE TBLADDRESSSECURE on TBLADDRESSSECURE.ADDRESSCODE = TBLACCTOWNERADDRESS.ADDRESSCODE " + "right outer join encompass.TBLACCTNBHD TBLACCTNBHD on TBLACCTNBHD.ACCOUNTNO = TBLACCT.ACCOUNTNO " + "right outer join encompass.TBLACCTLEGAL TBLACCTLEGAL on TBLACCTLEGAL.ACCOUNTNO = TBLACCT.ACCOUNTNO " + "right outer join encompass.TBLSUBACCOUNT TBLSUBACCOUNT on TBLSUBACCOUNT.ACCOUNTNO = TBLACCT.ACCOUNTNO " + "right outer join encompass.TBNSUBDIVISION TBNSUBDIVISION on TBNSUBDIVISION.SUBNO = TBLSUBACCOUNT.SUBNO " + "where TBLACCT.verend = 99999999999 " + "and TBLADDRESSSECURE.verend = 99999999999 " + "and TBLPERSONSECURE.verend = 99999999999 " + "and TBLACCTOWNERADDRESS.verend = 99999999999 " + "and TBLACCTPROPERTYADDRESS.verend = 99999999999 " + "and TBLACCTNBHD.verend = 99999999999 " + "and TBLACCTLEGAL.verend = 99999999999 " + "and TBLSUBACCOUNT.verend = 99999999999 " + "order by TBLACCT.ACCOUNTNO asc"; log.info("sqlQuery: " + sqlQuery); Query query = entityManager.createNativeQuery(sqlQuery, BasicAccountInfo.class); allSearchableParcels = query.getResultList(); log.info("allSearchableParcels.size(): " + allSearchableParcels.size()); } catch (Exception ex) { throw new RuntimeException(ex); } finally { entityManager.close(); } return allSearchableParcels; }