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:us.co.douglas.assessor.dao.AccountDAOImpl.java
public List<NeighborhoodSale> getAllNeighborhoodSales() { log.info("getAllNeighborhoodSales..."); List<NeighborhoodSale> allNeighborhoodSales = new ArrayList<NeighborhoodSale>(); EntityManager entityManager = getEntityManager(); try {/* w w w .ja va 2 s . c o m*/ String sqlQuery = "SELECT DISTINCT TBLSALE.RECEPTIONNO AS RECEPTIONNO, " + "TBLSALE.SALEDATE AS SALEDATE, " + "TBLSALE.GRANTOR AS GRANTOR, " + "TBLSALE.GRANTEE AS GRANTEE, " + "TBLSALE.SALEPRICE AS SALEPRICE, " + "TBLSALE.PPADJAMOUNT AS PPADJAMOUNT, " + "ISNULL(TBLACCTPROPERTYADDRESS.STREETNO, '') + ' ' + ISNULL(TBLACCTPROPERTYADDRESS.UNITNAME, '') + ' ' + ISNULL(TBLACCTPROPERTYADDRESS.STREETNAME, '') + ' ' + ISNULL(TBLACCTPROPERTYADDRESS.STREETTYPE, '') as propertyStreet, " + "ISNULL(TBLACCTPROPERTYADDRESS.PROPERTYCITY, '') as propertyCity, + " + "'CO' as propertyState, + " + "ISNULL(SUBSTRING(TBLACCTPROPERTYADDRESS.PROPERTYZIPCODE, 1, 5), '') AS propertyZipCode, " + "TBLSALE.GOODWILLADJAMOUNT AS GOODWILLADJAMOUNT, " + "TBLSALE.DOCUMENTDATE AS DOCUMENTDATE, " + "TBLSALE.OTHERADJAMOUNT AS OTHERADJAMOUNT, " + "TBLSALE.TIMEADJ AS TIMEADJ, " + "TBLSALE.JURISDICTIONID AS JURISDICTIONID, " + "TBLSALEACCT.ACCOUNTNO AS ACCOUNTNO, " + "TBLSALEACCT.INVENTORYEFFECTIVEDATE AS INVENTORYEFFECTIVEDATE, " + "TBLSALEACCT.ACCTADJSALEPRICE AS ACCTADJSALEPRICE, " + "0 AS TIMEADJUSTEDSALEPRICE, " + "ISNULL(TBLACCTNBHD.NBHDCODE, '') AS NEIGHBORHOOD, + " + "ISNULL(TBLACCTNBHD.NBHDEXTENSION, '') AS NEIGHBORHOODEXT, " + "ISNULL(TBNSUBDIVISION.SUBNAME, '') AS SUBDIVISION " + "FROM ENCOMPASS.TBLSALEACCT TBLSALEACCT " + "INNER JOIN ENCOMPASS.TBLSALE TBLSALE ON TBLSALE.RECEPTIONNO = TBLSALEACCT.RECEPTIONNO " + "INNER JOIN ENCOMPASS.TBLACCTPROPERTYADDRESS TBLACCTPROPERTYADDRESS ON TBLACCTPROPERTYADDRESS.ACCOUNTNO = TBLSALEACCT.ACCOUNTNO " + "INNER JOIN ENCOMPASS.TBLACCTNBHD TBLACCTNBHD ON TBLACCTNBHD.ACCOUNTNO = TBLSALEACCT.ACCOUNTNO " + "INNER JOIN ENCOMPASS.TBLSUBACCOUNT TBLSUBACCOUNT ON TBLSUBACCOUNT.ACCOUNTNO = TBLSALEACCT.ACCOUNTNO " + "INNER JOIN ENCOMPASS.TBNSUBDIVISION TBNSUBDIVISION ON TBNSUBDIVISION.SUBNO = TBLSUBACCOUNT.SUBNO " + "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 TBLACCTNBHD.VEREND = 99999999999 " + "AND TBLSUBACCOUNT.VEREND = 99999999999 " + "AND TBLSALE.VEREND = 99999999999 " + "ORDER BY SALEDATE DESC"; log.info("sqlQuery: " + sqlQuery); Query query = entityManager.createNativeQuery(sqlQuery, NeighborhoodSale.class); //query.setMaxResults(maxResults); allNeighborhoodSales = query.getResultList(); log.info("allNeighborhoodSales.size(): " + allNeighborhoodSales.size()); } catch (Exception ex) { throw new RuntimeException(ex); } finally { entityManager.close(); } return allNeighborhoodSales; }
From source file:us.co.douglas.assessor.dao.AccountDAOImpl.java
public List<NeighborhoodSale> getNeighborhoodSales(String zipCode, String neighborhood, String subdivision) { log.info("getAllSalesByZip..."); List<NeighborhoodSale> allSalesByZip = new ArrayList<NeighborhoodSale>(); EntityManager entityManager = getEntityManager(); try {/*from ww w . j av a2 s. c om*/ String sqlQuery = "SELECT DISTINCT TBLSALE.RECEPTIONNO AS RECEPTIONNO, " + "TBLSALE.SALEDATE AS SALEDATE, " + "TBLSALE.GRANTOR AS GRANTOR, " + "TBLSALE.GRANTEE AS GRANTEE, " + "TBLSALE.SALEPRICE AS SALEPRICE, " + "TBLSALE.PPADJAMOUNT AS PPADJAMOUNT, " + "ISNULL(TBLACCTPROPERTYADDRESS.STREETNO, '') + ' ' + ISNULL(TBLACCTPROPERTYADDRESS.UNITNAME, '') + ' ' + ISNULL(TBLACCTPROPERTYADDRESS.STREETNAME, '') + ' ' + ISNULL(TBLACCTPROPERTYADDRESS.STREETTYPE, '') as propertyStreet, " + "ISNULL(TBLACCTPROPERTYADDRESS.PROPERTYCITY, '') as propertyCity, + " + "'CO' as propertyState, + " + "ISNULL(SUBSTRING(TBLACCTPROPERTYADDRESS.PROPERTYZIPCODE, 1, 5), '') AS propertyZipCode, " + "TBLSALE.GOODWILLADJAMOUNT AS GOODWILLADJAMOUNT, " + "TBLSALE.DOCUMENTDATE AS DOCUMENTDATE, " + "TBLSALE.OTHERADJAMOUNT AS OTHERADJAMOUNT, " + "TBLSALE.TIMEADJ AS TIMEADJ, " + "TBLSALE.JURISDICTIONID AS JURISDICTIONID, " + "TBLSALEACCT.ACCOUNTNO AS ACCOUNTNO, " + "TBLSALEACCT.INVENTORYEFFECTIVEDATE AS INVENTORYEFFECTIVEDATE, " + "TBLSALEACCT.ACCTADJSALEPRICE AS ACCTADJSALEPRICE, " + "0 AS TIMEADJUSTEDSALEPRICE, " + "ISNULL(TBLACCTNBHD.NBHDCODE, '') AS NEIGHBORHOOD, + " + "ISNULL(TBLACCTNBHD.NBHDEXTENSION, '') AS NEIGHBORHOODEXT, " + "ISNULL(TBNSUBDIVISION.SUBNAME, '') AS SUBDIVISION " + "FROM ENCOMPASS.TBLSALEACCT TBLSALEACCT " + "INNER JOIN ENCOMPASS.TBLSALE TBLSALE ON TBLSALE.RECEPTIONNO = TBLSALEACCT.RECEPTIONNO " + "INNER JOIN ENCOMPASS.TBLACCTPROPERTYADDRESS TBLACCTPROPERTYADDRESS ON TBLACCTPROPERTYADDRESS.ACCOUNTNO = TBLSALEACCT.ACCOUNTNO " + "INNER JOIN ENCOMPASS.TBLACCTNBHD TBLACCTNBHD ON TBLACCTNBHD.ACCOUNTNO = TBLSALEACCT.ACCOUNTNO " + "INNER JOIN ENCOMPASS.TBLSUBACCOUNT TBLSUBACCOUNT ON TBLSUBACCOUNT.ACCOUNTNO = TBLSALEACCT.ACCOUNTNO " + "INNER JOIN ENCOMPASS.TBNSUBDIVISION TBNSUBDIVISION ON TBNSUBDIVISION.SUBNO = TBLSUBACCOUNT.SUBNO " + "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 TBLACCTNBHD.VEREND = 99999999999 " + "AND TBLSUBACCOUNT.VEREND = 99999999999 " + "AND TBLSALE.VEREND = 99999999999 " + "AND TBLACCTNBHD.NBHDCODE LIKE '%" + neighborhood + "%' " + "AND TBNSUBDIVISION.SUBNAME LIKE '%" + subdivision + "%' " + "AND TBLACCTPROPERTYADDRESS.PROPERTYZIPCODE LIKE '%" + zipCode + "%' " + "ORDER BY SALEDATE DESC"; log.info("sqlQuery: " + sqlQuery); Query query = entityManager.createNativeQuery(sqlQuery, NeighborhoodSale.class); query.setMaxResults(maxResults); allSalesByZip = query.getResultList(); log.info("allSalesByZip.size(): " + allSalesByZip.size()); } catch (Exception ex) { throw new RuntimeException(ex); } finally { entityManager.close(); } return allSalesByZip; }
From source file:us.co.douglas.assessor.dao.AccountDAOImpl.java
public Account getAccount(String accountNo) { log.info("getAccount()..."); EntityManager entityManager = getEntityManager(); try {/* w ww. j av a 2 s . c o m*/ String sqlQuery = "SELECT DISTINCT " + "TBLACCT.ACCOUNTNO AS ACCOUNTNO, " + "TBLACCT.PARCELNO AS PARCELNO, " + "TBLACCT.ACCTSTATUSCODE AS ACCTSTATUSCODE, " + "TBLACCT.ACCTTYPE AS ACCTTYPE, " + "TBLACCT.EFILEFLAG AS EFILEFLAG, " + "TBLACCT.WARD AS WARD, " + "TBLACCT.PROPERTYIDENTIFIER AS PROPERTYIDENTIFIER, " + "TBLACCT.STRIPPEDACCOUNTNO AS STRIPPEDACCOUNTNO, " + "TBLACCT.DEFAULTAPPROACHTYPE AS DEFAULTAPPROACHTYPE, " + "TBLACCT.MOBILEHOMESPACE AS MOBILEHOMESPACE, " + "TBLACCT.ASSIGNEDTO AS ASSIGNEDTO, " + "TBLACCT.DEFAULTTAXDISTRICT AS DEFAULTTAXDISTRICT, " + "TBLACCT.VALUEAREACODE AS VALUEAREACODE, " + "TBLACCT.ASSOCIATEDACCT AS ASSOCIATEDACCT," + "TBLACCT.APPRAISALTYPE AS APPRAISALTYPE, " + "TBLACCT.ECONOMICAREACODE AS ECONOMICAREACODE, " + "TBLACCT.ACCTDATECREATED AS ACCTDATECREATED, " + "TBLACCT.BUSINESSLICENSE AS BUSINESSLICENSE, " + "TBLACCT.PRIMARYUSECODE AS PRIMARYUSECODE, " + "TBLACCT.JURISDICTIONID AS JURISDICTIONID, " + "TBLACCT.BUSINESSNAME AS BUSINESSNAME, " + "TBLACCT.PROPERTYCLASSID AS PROPERTYCLASSID," + "TBLACCTLEGAL.LEGAL AS LEGAL, " + "TBLACCTLEGAL.SHORTDESCRIPTION AS LEGALSHORTDESCRIPTION, " + "TBLACCTLEGALLOCATION.QTR AS QTR, " + "TBLACCTLEGALLOCATION.SECTION AS SECTION, " + "TBLACCTLEGALLOCATION.TOWNSHIP AS TOWNSHIP, " + "TBLACCTLEGALLOCATION.RANGE AS RANGE, " + "TBLACCTNBHD.NBHDCODE AS NBHDCODE, " + "TBLACCTNBHD.NBHDEXTENSION AS NBHDEXTENSION, " + "TBLACCTNBHD.PROPERTYTYPE AS PROPERTYTYPE, " + "TBLACCTREAL.IMPONLYFLAG AS IMPONLYFLAG, " + "TBLACCTREAL.TIFFLAG AS TIFFLAG, " + "TBLACCTREAL.VACANTFLAG AS VACANTFLAG, " + "TBLACCTREAL.PARKINGSPACES AS PARKINGSPACES, " + "TBLACCTREAL.ZONINGCODE AS ZONINGCODE, " + "TBLACCTREAL.LANDWIDTH AS LANDWIDTH, " + "TBLACCTREAL.LANDDEPTH AS LANDDEPTH, " + "TBLACCTREAL.LANDEXCESSSF AS LANDEXCESSSF, " + "TBLACCTREAL.LANDGROSSSF AS LANDGROSSSF, " + "TBLACCTREAL.LANDGROSSACRES AS LANDGROSSACRES, " + "TBLACCTREAL.DEFAULTLEA AS DEFAULTLEA, " + "ISNULL(TBNSUBDIVISION.SUBNAME, '') AS SUBDIVISIONNAME " + "FROM ENCOMPASS.TBLACCT TBLACCT " + "LEFT OUTER JOIN ENCOMPASS.TBLACCTLEGAL TBLACCTLEGAL ON TBLACCTLEGAL.ACCOUNTNO = TBLACCT.ACCOUNTNO " + "LEFT OUTER JOIN ENCOMPASS.TBLACCTLEGALLOCATION TBLACCTLEGALLOCATION ON TBLACCTLEGALLOCATION.ACCOUNTNO = TBLACCT.ACCOUNTNO " + "LEFT OUTER JOIN ENCOMPASS.TBLACCTNBHD TBLACCTNBHD ON TBLACCTNBHD.ACCOUNTNO = TBLACCT.ACCOUNTNO " + "LEFT OUTER JOIN ENCOMPASS.TBLACCTOWNERADDRESS TBLACCTOWNERADDRESS ON TBLACCTOWNERADDRESS.ACCOUNTNO = TBLACCT.ACCOUNTNO " + "LEFT OUTER JOIN ENCOMPASS.TBLACCTMAILADDRESS TBLACCTMAILADDRESS ON TBLACCTMAILADDRESS.ACCOUNTNO = TBLACCT.ACCOUNTNO " + "LEFT OUTER JOIN ENCOMPASS.TBLACCTPROPERTYADDRESS TBLACCTPROPERTYADDRESS ON TBLACCTPROPERTYADDRESS.ACCOUNTNO = TBLACCT.ACCOUNTNO " + "LEFT OUTER JOIN ENCOMPASS.TBLACCTREAL TBLACCTREAL ON TBLACCTREAL.ACCOUNTNO = TBLACCT.ACCOUNTNO " + "LEFT OUTER JOIN ENCOMPASS.TBLADDRESSSECURE TBLADDRESSSECURE ON TBLADDRESSSECURE.ADDRESSCODE = TBLACCTOWNERADDRESS.ADDRESSCODE " + "LEFT OUTER JOIN ENCOMPASS.TBLPERSONSECURE TBLPERSONSECURE ON TBLPERSONSECURE.PERSONCODE = TBLACCTOWNERADDRESS.PERSONCODE " + "LEFT OUTER JOIN ENCOMPASS.TBLSUBACCOUNT TBLSUBACCOUNT ON TBLSUBACCOUNT.ACCOUNTNO = TBLACCT.ACCOUNTNO " + "LEFT OUTER JOIN ENCOMPASS.TBNSUBDIVISION TBNSUBDIVISION ON TBNSUBDIVISION.SUBNO = TBLSUBACCOUNT.SUBNO " + "WHERE " + "TBLACCT.VEREND = 99999999999 " + "AND TBLACCTLEGAL.VEREND = 99999999999 " + "AND TBLACCTLEGALLOCATION.VEREND = 99999999999 " + "AND TBLACCTNBHD.VEREND = 99999999999 " + "AND TBLACCTOWNERADDRESS.VEREND = 99999999999 " + "AND TBLACCTPROPERTYADDRESS.VEREND = 99999999999 " + "AND TBLACCTREAL.VEREND = 99999999999 " + "AND TBLADDRESSSECURE.VEREND = 99999999999 " + "AND TBLSUBACCOUNT.VEREND = 99999999999 " + "AND TBLPERSONSECURE.VEREND = 99999999999 " + "AND TBLACCT.ACCOUNTNO = :accountNo "; log.info("sqlQuery: " + sqlQuery); Query query = entityManager.createNativeQuery(sqlQuery, Account.class); query.setMaxResults(maxResults); query.setParameter("accountNo", accountNo); List<Account> accountList = query.getResultList(); if (accountList.size() > 0) { return accountList.get(0); } else { return null; } } catch (Exception ex) { throw new RuntimeException(ex); } finally { entityManager.close(); } }