I am using Java 5, Hibernate 3, Spring 1.2, Tomcat 5, and Oracle 9i to develop a web application. I am getting an oracle error ORA-01722 indicating an invalid number. This error is rather odd and prompted me to write here. I wonder if I am using Hibernate incorrectly or if there is a bug that should be reported. I suspect that I am doing something wrong.
In short, I have a DAO that uses a named query to load an entity ActionReason. The mapping is below:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping schema="PSFT_DATAMART">
<class
name="hr.employee.domain.ActionReason"
table="PSFT_REASON">
<composite-id
name="id"
class="hr.employee.domain.ActionReasonId">
<key-property
name="actionId"
column="ACTION"
type="string"
access="field"/>
<key-property
name="reasonId"
column="REASON"
type="string"
access="field"/>
</composite-id>
<property
name="description"
column="DESCR"
type="string"/>
<property
name="available"
column="AVAILABLE"
type="yes_no"/>
<property
name="loadedOn"
column="UPDATE_TS"
type="zion.core.dao.hibernate.usertype.TimestampUserType"/>
</class>
<query name="findByActionType"><![CDATA[
from hr.employee.domain.ActionReason as reason
where reason.id.actionId = ?
and reason.available = ?
]]></query>
</hibernate-mapping>
Here is the ActionReasonDAO so you can see the code making the call to retrieve the data:
Code:
package hr.employee.dao.hibernate;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import hr.employee.dao.ActionReasonDAO;
import hr.employee.domain.ActionReason;
import hr.employee.domain.ActionReasonId;
import hr.employee.domain.enums.ActionReasonType;
public class HibernateActionReasonDAO extends HibernateDaoSupport implements ActionReasonDAO {
public HibernateActionReasonDAO() {
super();
}
public ActionReason read(ActionReasonId id) {
return (ActionReason) getHibernateTemplate().get(ActionReason.class, id);
}
@SuppressWarnings("unchecked")
public Set<ActionReason> findByActionType(ActionReasonType type) {
List<ActionReason> list = getHibernateTemplate().findByNamedQuery("findByActionType", new Object[] { type.name(), Boolean.TRUE });
Set<ActionReason> set = new HashSet<ActionReason>(list.size());
set.addAll(list);
return set;
}
}
In addition I have a spring config file that sets up hibernate and injects my DAOs with the appropriate objects.
The ActionReason property "available" is of type Boolean. I mapped that property to a "yes_no" hibernate type because the values are stored as "Y" or "N". In the named query "findByActionType" I want to filter by those ActionReasons that are available. To do this I pass in Boolean.TRUE as a parameter to the named query.
If I run the following test case it passes:
Code:
package hr.employee.dao.test;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.HashSet;
import java.util.Set;
import javax.sql.DataSource;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.test.AbstractTransactionalDataSourceSpringContextTests;
import hr.employee.dao.ActionReasonDAO;
import hr.employee.domain.ActionReason;
import hr.employee.domain.ActionReasonId;
import hr.employee.domain.enums.ActionReasonType;
import zion.core.type.Timestamp;
public class ActionReasonDAOTest extends AbstractTransactionalDataSourceSpringContextTests {
private static final String[] CONFIG_LOCATIONS = new String[] { "hr-data.spg.xml", "hr-service.spg.xml" };
private static final String[] DELETE_FROM_TABLES = new String[0];
private static final String EMPLOYEE_DATASET = "hr/employee/dao/test/employee-dataset.xml";
private ActionReasonDAO actionReasonDAO;
private Set<ActionReason> payReasons;
private GregorianCalendar loadedOn;
public ActionReasonDAOTest() {
super();
}
public ActionReasonDAOTest(String name) {
super(name);
}
public void setActionReasonDAO(ActionReasonDAO actionReasonDAO) {
this.actionReasonDAO = actionReasonDAO;
}
public void testReadActionReason() {
final ActionReasonId id = new ActionReasonId("PAY", "MER");
ActionReason actionReason = actionReasonDAO.read(id);
assertNotNull(actionReason);
assertEquals(id, actionReason.getId());
assertEquals("Merit", actionReason.getDescription());
assertEquals(Boolean.TRUE, actionReason.getAvailable());
assertEquals(new Timestamp(loadedOn.getTimeInMillis()), actionReason.getLoadedOn());
}
public void testFindByActionType() {
Set<ActionReason> reasons = actionReasonDAO.findByActionType(ActionReasonType.PAY);
assertNotNull(reasons);
assertEquals(payReasons, reasons);
}
@Override
protected void onSetUpBeforeTransaction() throws Exception {
loadedOn = new GregorianCalendar(2006, Calendar.MAY, 25, 2, 38, 16);
payReasons = new HashSet<ActionReason>();
ActionReason reason = new ActionReason(new ActionReasonId(ActionReasonType.PAY.name(), "MER"));
reason.setDescription("Merit");
reason.setAvailable(Boolean.TRUE);
reason.setLoadedOn(new Timestamp(loadedOn.getTimeInMillis()));
payReasons.add(reason);
reason = new ActionReason(new ActionReasonId(ActionReasonType.PAY.name(), "LUM"));
reason.setDescription("Lump Sum Payment");
reason.setAvailable(Boolean.TRUE);
reason.setLoadedOn(new Timestamp(loadedOn.getTimeInMillis()));
payReasons.add(reason);
reason = new ActionReason(new ActionReasonId(ActionReasonType.PAY.name(), "PRO"));
reason.setDescription("Promotion");
reason.setAvailable(Boolean.TRUE);
reason.setLoadedOn(new Timestamp(loadedOn.getTimeInMillis()));
payReasons.add(reason);
}
protected void onSetUpInTransaction() throws Exception {
// Cannot delete from tables only defined in dataset due to constraints on other tables. This will delete those
// dependent tables first.
if (DELETE_FROM_TABLES.length > 0) {
super.deleteFromTables(DELETE_FROM_TABLES);
}
DataSource dataSource = jdbcTemplate.getDataSource();
Connection con = DataSourceUtils.getConnection(dataSource);
IDatabaseConnection dbUnitCon = new DatabaseConnection(con, "PSFT_DATAMART");
InputStream dataStream = getClass().getClassLoader().getResourceAsStream(EMPLOYEE_DATASET);
IDataSet dataSet = new FlatXmlDataSet(dataStream);
try {
DatabaseOperation.CLEAN_INSERT.execute(dbUnitCon, dataSet);
} finally {
DataSourceUtils.releaseConnection(con, dataSource);
}
}
@Override
protected String[] getConfigLocations() {
return CONFIG_LOCATIONS;
}
}
Now this is were it starts to get interesting. When I run my web application that makes a call to ActionReasonDAO.findByActionType I get the Oracle error ORA-01722 Invalid number. Here is the relevant stack trace:
Code:
org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not execute query; SQL [select actionreas0_.ACTION as ACTION, actionreas0_.REASON as REASON, actionreas0_.DESCR as DESCR13_, actionreas0_.AVAILABLE as AVAILABLE13_, actionreas0_.UPDATE_TS as UPDATE5_13_ from PSFT_DATAMART.PSFT_REASON actionreas0_ where actionreas0_.ACTION=? and actionreas0_.AVAILABLE=?]; ORA-01722: invalid number
; nested exception is java.sql.SQLException: ORA-01722: invalid number
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:235)
org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)
org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:411)
org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:371)
org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQuery(HibernateTemplate.java:911)
hr.employee.dao.hibernate.HibernateActionReasonDAO.findByActionType(HibernateActionReasonDAO.java:46)
hr.employee.domain.service.EmployeeManagerService.getActionReasons(EmployeeManagerService.java:68)
Hibernate seems to have trouble converting the Boolean parameter to a value that can be used in the named query. I use the exact same Spring config files to setup Hibernate when I run the test cases and when I run the application. So I am puzzled by this.
Thinking there was a conversion problem from Boolean to a VARCHAR I decided to create my own user type YesNoUserType but this did not solve the problem nor reveal any new information.
My next step was to try to alter the value that was passed into the named query just to see if I could get it working. Thinking Hibernate did not like the Boolean I gave it, I changed the call in the DAO to:
Code:
@SuppressWarnings("unchecked")
public Set<ActionReason> findByActionType(ActionReasonType type) {
List<ActionReason> list = getHibernateTemplate().findByNamedQuery("findByActionType", new Object[] { type.name(), "Y" });
Set<ActionReason> set = new HashSet<ActionReason>(list.size());
set.addAll(list);
return set;
}
When I ran the web application I found that this worked! I got the values I wanted. I then ran the test case and it failed. w00t!
I am completely stumped at this point! In the interim I simply changed the named query and the findByActionType method to the following as a workaround:
Code:
<query name="findByActionType"><![CDATA[
from hr.employee.domain.ActionReason as reason
where reason.id.actionId = ?
and reason.available = 'Y'
]]></query>
@SuppressWarnings("unchecked")
public Set<ActionReason> findByActionType(ActionReasonType type) {
List<ActionReason> list = getHibernateTemplate().findByNamedQuery("findByActionType", new Object[] { type.name() });
Set<ActionReason> set = new HashSet<ActionReason>(list.size());
set.addAll(list);
return set;
}
Login
FAQ
Search