Does anyone have a working example of interacting with a table that populates
the PK value via a trigger (selects the sequence value from dual in before
insert trigger). The examples that I've seen read the sequence via selectKey,
which does work, but our trigger populates it.
If I was doing this in SQL, the statement would be:
insert into emp( ename, department) values ( 'eric', 'research')
returning empno into :empnum
I'd even opt for being able to call a stored proc to do the insert, but I'm
having problems passing a null into it. I started with the Struts-Ibatis demo
from Rick Reumann's site. I added this to employee.xml
<parameterMap id="EmployeeMap" class="map">
<parameter property="employeeId" jdbcType="INTEGER"
javaType="java.lang.Integer" mode="INOUT" />
<parameter property="firstName" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN" />
<parameter property="lastName" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN" />
<parameter property="age" jdbcType="INTEGER"
javaType="java.lang.Integer" mode="IN" />
<parameter property="departmentId" jdbcType="INTEGER"
javaType="java.lang.Integer" mode="IN" />
</parameterMap>
<procedure id="insertViaStoredProc" parameterMap="EmployeeMap" >
{call insert_employee(?,?,?,?,?)}
</procedure>
I changed the EmployeeDaoIbatisImpl.java to:
public void insert(Employee emp) {
try
{
HashMap map = new HashMap();
Integer numEmployeeId = null;
map.put("employeeId", numEmployeeId );
map.put("firstName", emp.getFirstName() );
map.put("lastName", emp.getLastName() );
map.put("age", emp.getAge() );
map.put("departmentId", emp.getDepartment().getDepartmentId() );
//how do I get the resulting employeeId?
Object o = sqlMap.queryForObject("Employee.insertViaStoredProc",
map);
// System.out.println("employeeid=" + employeeid );
} catch (SQLException e) {
logger.error("Error inserting Employee", e);
}
}
an iBatis debug/trace shows:
DEBUG [ApplicationServerThread-0] net.reumann.demo.action.EmployeeAction
insertOrUpdate- insertOrUpdate
DEBUG [ApplicationServerThread-0] net.reumann.demo.action.EmployeeAction
insertOrUpdate- insert
DEBUG [ApplicationServerThread-0] com.ibatis.common.jdbc.SimpleDataSource
debug- Checked out connection 538 from pool.
DEBUG [ApplicationServerThread-0] java.sql.Connection debug- {conn-100009}
Connection
DEBUG [ApplicationServerThread-0] java.sql.PreparedStatement debug-
{pstm-100010} PreparedStatement: {call insert_employee(?,?,?,?,?)}
DEBUG [ApplicationServerThread-0] java.sql.PreparedStatement debug-
{pstm-100010} Parameters: [null, Eric, Givler, 40, 10]
DEBUG [ApplicationServerThread-0] java.sql.PreparedStatement debug-
{pstm-100010} Types: [null, java.lang.String, java.lang.String,
java.lang.Integer, java.lang.Integer]
DEBUG [ApplicationServerThread-0] com.ibatis.common.jdbc.SimpleDataSource
debug- Returned connection 538 to pool.
The row does not get into the database. I'm assuming it is a problem with the
way the parameters are setup.
If I switch the <procedure> declaration to use: {call
insert_employee(#employeeId:NUMBER#,?,?,?,?)}
I get another error:
DEBUG [ApplicationServerThread-0] com.ibatis.common.jdbc.SimpleDataSource
debug- Returned connection 538 to pool.
DEBUG [ApplicationServerThread-0] net.reumann.demo.action.EmployeeAction
insertOrUpdate- insertOrUpdate
DEBUG [ApplicationServerThread-0] net.reumann.demo.action.EmployeeAction
insertOrUpdate- insert
DEBUG [ApplicationServerThread-0] com.ibatis.common.jdbc.SimpleDataSource
debug- Checked out connection 538 from pool.
DEBUG [ApplicationServerThread-0] java.sql.Connection debug- {conn-100009}
Connection
DEBUG [ApplicationServerThread-0] java.sql.PreparedStatement debug-
{pstm-100010} PreparedStatement: {call
insert_employee(#employeeId:NUMBER#,#firstName:VARCHAR#,#lastName:VARCHAR2#,#age:NUMBER#,#departmentId:NUMBER#)}
DEBUG [ApplicationServerThread-0] java.sql.PreparedStatement debug-
{pstm-100010} Parameters: [null, eric, givler, 40, 10]
DEBUG [ApplicationServerThread-0] java.sql.PreparedStatement debug-
{pstm-100010} Types: [null, java.lang.String, java.lang.String,
java.lang.Integer, java.lang.Integer]
DEBUG [ApplicationServerThread-0] com.ibatis.common.jdbc.SimpleDataSource
debug- Returned connection 538 to pool.
ERROR [ApplicationServerThread-0]
net.reumann.demo.persistence.EmployeeDaoIbatisImpl insert- Error inserting
Employee
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in net/reumann/demo/persistence/Employee.xml.
--- The error occurred while applying a parameter map.
--- Check the Employee.EmployeeMap.
--- Check the statement (update procedure failed).
--- Cause: java.sql.SQLException: ORA-06550: line 1, column 23:
PLS-00103: Encountered the symbol "#" when expecting one of the following:
( ) - + case mod new not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current exists max min prior sql stddev sum
variance execute multiset the both leading trailing forall
merge year month DAY_ hour minute second timezone_hour
timezone_minute timezone_region timezone_abbr time timestamp
interval date
<a string literal with character set specification>
I'd appreciate any tips on this one. Luckily, my hair is short, so it is hard
to pull out.
Thanks,
Eric
