Search...
FAQs
Subscribe
Pie
FAQs
Recent topics
Flagged topics
Hot topics
Best topics
Search...
Search within Object Relational Mapping
Search Coderanch
Advance search
Google search
Register / Login
Post Reply
Bookmark Topic
Watch Topic
New Topic
programming forums
Java
Mobile
Certification
Databases
Caching
Books
Engineering
Micro Controllers
OS
Languages
Paradigms
IDEs
Build Tools
Frameworks
Application Servers
Open Source
This Site
Careers
Other
Pie Elite
all forums
this forum made possible by our volunteer staff, including ...
Marshals:
Campbell Ritchie
Jeanne Boyarsky
Ron McLeod
Paul Clapham
Liutauras Vilda
Sheriffs:
paul wheaton
Rob Spoor
Devaka Cooray
Saloon Keepers:
Stephan van Hulst
Tim Holloway
Carey Brown
Frits Walraven
Tim Moores
Bartenders:
Mikalai Zaikin
Forum:
Object Relational Mapping
Missing Time when retrieving from HQL
Jigar Naik
Ranch Hand
Posts: 763
posted 11 years ago
Number of slices to send:
Optional 'thank-you' note:
Send
Hi I am facing strange issue, when i execute HQL my result only show DATE instead of TIMESTAMP.
Below are my mapping and Domain Object
from hbm.xml
<property name="runDt" type="java.sql.Timestamp" column="RUN_DT" length="7" />
from Domain
private java.util.Date runDt;
And below is my HQL
StringBuffer queryBuffer = new StringBuffer(); queryBuffer.append("SELECT eb.elec_bor_id as elec_bor_id,") .append(" eb.bor_type_cd as bor_type_cd,") .append(" eb.territory_id as territory_id,") .append(" eb.load_dt as file_dt, eb.sender_cd as sender_cd, eb.run_dt as run_dt,") .append(" eb.elec_bor_status_cd as elec_bor_status_cd,") .append(" eb.bor_min_dt as min_dt,") .append(" eb.bor_max_dt as max_dt,") .append(" eb.bor_total_count as total_count,") .append(" ebo_func_pkg.f_get_suspended_count (eb.elec_bor_id, eb.territory_id) as suspended_count,") .append(" ebo_func_pkg.f_get_suspended_errors (eb.elec_bor_id, eb.territory_id) as suspended_errors,") .append(" ebo_func_pkg.f_get_elec_bor_circ_short_name(eb.elec_bor_id) as circuit_short_name,") .append(" ebo_func_pkg.f_has_multiple_circuits(eb.elec_bor_id) as has_multiple_circuits_ind,") .append(" eb.file_complete_ind, eb.circuit_id") .append(" from elec_bor eb where"); if(0 < elecBORSearchTO.getTerritoryId()) { queryBuffer.append(" eb.territory_id="+ new Long(elecBORSearchTO.getTerritoryId())); } if (null != elecBORSearchTO.getBookingNo() && elecBORSearchTO.getBookingNo().longValue() > 0) { queryBuffer.append(" and exists (select 1 from Elec_Bor_Detail ebd, booking b where ebd.booking_id = b.booking_id") .append(" and ebd.elec_bor_id = eb.elec_bor_id") .append(" and b.booking_no="+ elecBORSearchTO.getBookingNo()+")") ; chk="N"; } else { chk = "Y"; if (null != elecBORSearchTO.getRunDt()) { queryBuffer.append(" and eb.run_dt >= :startdate"); if (null == elecBORSearchTO.getRunDtTo()) { queryBuffer.append(" and eb.run_dt <= :enddate"); } } if (null != elecBORSearchTO.getRunDtTo()) { queryBuffer.append(" and eb.run_dt <= :enddate"); } queryBuffer.append(" and eb.file_complete_ind='" + FILE_COMPLETE_IND_YES + "'"); if (null != elecBORSearchTO.getBranchidList() && elecBORSearchTO.getBranchidList().size() > 0) { String branchIdStr = StringUtil.convertListToString(elecBORSearchTO.getBranchidList(), ","); queryBuffer.append(" and exists (select 1 from Elec_Bor_Detail ebd where ebd.elec_bor_id = eb.elec_bor_id").append( " and ebd.branch_id in (" + StringUtil.addQuotesToString(branchIdStr, ",") + "))"); } if (StringUtil.isNotBlank(elecBORSearchTO.getSender())) { queryBuffer.append(" and eb.sender_cd='" + elecBORSearchTO.getSender() + "'"); } if (null != elecBORSearchTO.getMinPlaywkStartDt()) { queryBuffer.append(" and eb.bor_min_dt >=:minStartdate"); } if (null != elecBORSearchTO.getMaxPlaywkEndDt()) { queryBuffer.append(" and eb.bor_max_dt <=:minEnddate"); } if (StringUtil.isNotBlank(elecBORSearchTO.getBorStatusCd())) { queryBuffer.append(" and eb.elec_bor_status_cd='" + elecBORSearchTO.getBorStatusCd() + "'"); } if (StringUtil.isNotBlank(elecBORSearchTO.getBorTypeCd())) { queryBuffer.append(" and eb.bor_type_cd='" + elecBORSearchTO.getBorTypeCd() + "'"); } if (null != elecBORSearchTO.getCircuitId() && elecBORSearchTO.getCircuitId().longValue() > 0) { queryBuffer.append(" and eb.circuit_id=" + elecBORSearchTO.getCircuitId()); } } queryBuffer.append(" order by eb.bor_min_dt"); SQLQuery sqlQuery = session.createSQLQuery(queryBuffer.toString()); if("Y".equalsIgnoreCase(chk)){ if (null != elecBORSearchTO.getRunDt()) { Date runDateStrt = DateUtil.addOrSubstractDays(DateUtil.getOnlyDatePart(elecBORSearchTO.getRunDt()), -1); java.sql.Timestamp runDateStrtTime = new java.sql.Timestamp(runDateStrt.getTime()); runDateStrtTime.setHours(23); runDateStrtTime.setMinutes(59); runDateStrtTime.setSeconds(59); sqlQuery.setDate("startdate", runDateStrtTime); if (null == elecBORSearchTO.getRunDtTo()) { Date runDateEnd = DateUtil.addOrSubstractDays(DateUtil.getOnlyDatePart(elecBORSearchTO.getRunDt()), 1);// for // 22472 java.sql.Timestamp runDateEndTime = new java.sql.Timestamp(runDateEnd.getTime()); runDateEndTime.setHours(00); runDateEndTime.setMinutes(00); runDateEndTime.setSeconds(00); sqlQuery.setDate("enddate", runDateEndTime); } } if (null != elecBORSearchTO.getRunDtTo()) { Date runDateEnd = DateUtil.addOrSubstractDays(DateUtil.getOnlyDatePart(elecBORSearchTO.getRunDtTo()), 1);// for java.sql.Timestamp runDateEndTime = new java.sql.Timestamp(runDateEnd.getTime()); runDateEndTime.setHours(00); runDateEndTime.setMinutes(00); runDateEndTime.setSeconds(00); sqlQuery.setDate("enddate", runDateEndTime); } if (null != elecBORSearchTO.getMinPlaywkStartDt()) { sqlQuery.setDate("minStartdate", elecBORSearchTO.getMinPlaywkStartDt()); } if (null != elecBORSearchTO.getMaxPlaywkEndDt()) { sqlQuery.setDate("minEnddate", elecBORSearchTO.getMaxPlaywkEndDt()); } } List vwElecBorList = sqlQuery.list(); return vwElecBorList;
Jigar Naik
Bill Gorder
Bartender
Posts: 1682
7
I like...
posted 11 years ago
Number of slices to send:
Optional 'thank-you' note:
Send
Consider using criteria for dynamic queries like this. What is the data type of you column in the database. Have you verified that time is indeed being stored correctly?
[
How To Ask Questions
][
Read before you PM me
]
Consider Paul's
rocket mass heater
.
reply
reply
Bookmark Topic
Watch Topic
New Topic
Boost this thread!
Similar Threads
Hibernate Select - Criteria with a Date
hibernate undefined alias : 1 error
Hibernate Date Issue
values change unexpectedly due to threading issue?
Prepared Statement and Date class - Problem still exists
More...