Validation Query on WebSphere DataSource

Do you remember this?? (for those who have worked before with DBCP : validationQuery)
<dataSource type="DBCP"> <property name="driverClassName" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> <property name="initialSize" value="2"/> <property name="maxActive" value="10"/> <property name="maxIdle" value="2"/> <property name="maxWait" value="-1"/> <property name="removeAbandoned" value="true"/> <property name="removeAbandonedTimeout" value="300"/> <property name="logAbandoned" value="true"/> <property name="testWhileIdle" value="true"/> <property name="testOnReturn" value="true"/> <property name="testOnBorrow" value="true"/> <property name="validationQuery" value="${validationQuery}"/> </dataSource>
I was tired to look for this attribute in Websphere and I found it :P

Tip: On DB2 the equivalent to SELECT 1 FROM DUAL is SELECT 1 FROM SYSIBM.SYSDUMMY1

Pass Collection of Objects with Attributes to a Stored Procedure using iBatis

In my last entry I had a requirement which needed to pass a List to an Oracle Stored Procedure, well, that thing is history.
But now the problem becomes more complex, because, what happen if I'm expecting on my Stored Proc and Type (STRUCT)
This is my Collection in Oracle! CREATE OR REPLACE TYPE CUST_ACCNT_MTN_PRE_ORDER AS OBJECT( cust_id NUMBER, acct_num NUMBER, mtn NUMBER, cidb_order_id NUMBER, mtn_order_state VARCHAR(1), mtn_order_type VARCHAR(1), mtn_order_state_date DATE, bdy_mtn NUMBER ); CREATE OR REPLACE TYPE CUST_ACCNT_MTN_PRE_ORDER_LIST AS TABLE OF CUST_ACCNT_MTN_PRE_ORDER;
And this is my Stored Proc (this is just a sample which receives the TABLE of Records) CREATE OR REPLACE PROCEDURE test_list_params ( p_cust_id IN NUMBER, p_acct_num IN NUMBER, p_list IN CUST_ACCNT_MTN_PRE_ORDER_LIST ) IS BEGIN FOR i in 1 .. p_list.count LOOP INSERT INTO TESTING_SP (CUST_ID, ACCT_NUM, MTN, CIDB_ORDER_ID, MTN_ORDER_STATE, MTN_ORDER_TYPE, BDY_MTN) VALUES (p_cust_id, p_acct_num, p_list(i).mtn, p_list(i).cidb_order_id, p_list(i).mtn_order_state, p_list(i).mtn_order_type, p_list(i).bdy_mtn); DBMS_OUTPUT.put_line ('STRUCT '||i); DBMS_OUTPUT.put_line ('mtn='||p_list(i).mtn||', cidb_order_id='||p_list(i).cidb_order_id||', mtn_order_state='||p_list(i).mtn_order_state); DBMS_OUTPUT.put_line ('mtn_order_type='||p_list(i).mtn_order_type||', bdy_mtn='||p_list(i).bdy_mtn); END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Some other kind of error occurred.'); raise_application_error(-20102, 'Some other kind of error occurred.'); ROLLBACK; END;
This is the table where I'm going to INSERT CREATE TABLE TESTING_SP( CUST_ID NUMBER, ACCT_NUM NUMBER, MTN NUMBER, CIDB_ORDER_ID NUMBER, MTN_ORDER_STATE VARCHAR2(1 BYTE), MTN_ORDER_TYPE VARCHAR2(1 BYTE), MTN_ORDER_STATE_DATE DATE DEFAULT SYSDATE, BDY_MTN NUMBER ) TABLESPACE ACSS_DATA01;
Obviously, the solution is Create a Handler like I did on my last entry, but is not going to be as simple, because I need to Map my Oracle STRUCT with my Java Code, so I have to do an domain object (POJO) coupled to Oracle and my Handler. import java.sql.Connection; import java.sql.SQLException; import java.util.Date; import oracle.jdbc.driver.OracleTypes; import oracle.jpub.runtime.MutableStruct; import oracle.sql.Datum; import oracle.sql.ORAData; import oracle.sql.ORADataFactory; import oracle.sql.STRUCT; /** * Java Implementation of an Oracle STRUCT/TYPE.<BR> * This is a nasty looking model because it implements ORAData and ORADataFactory. * * It's really important to follow the order/index of each attribute on the Oracle Type (see getter/setter methods) * * <PRE> * CREATE OR REPLACE TYPE CUST_ACCNT_MTN_PRE_ORDER AS OBJECT( * cust_id NUMBER, * acct_num NUMBER, * mtn NUMBER, * cidb_order_id NUMBER, * mtn_order_state VARCHAR(1), * mtn_order_type VARCHAR(1), * mtn_order_state_date DATE, * bdy_mtn NUMBER ) * </PRE> * * @author Juan Cruz * @since 09/20/2010 * * @see oracle.sql.ORAData * @see oracle.sql.ORADataFactory * @see oracle.sql.STRUCT * */ public class CustomerAccountMtnOrderStruct implements ORAData, ORADataFactory { public static final String SQL_NAME = "CUST_ACCNT_MTN_PRE_ORDER"; public static final int SQL_TYPECODE = OracleTypes.STRUCT; protected MutableStruct struct; static int[] sqlTypes = { OracleTypes.NUMBER, OracleTypes.NUMBER, OracleTypes.NUMBER, OracleTypes.NUMBER, OracleTypes.VARCHAR, OracleTypes.VARCHAR, OracleTypes.DATE, OracleTypes.NUMBER }; static ORADataFactory[] oraDataFactories = new ORADataFactory[sqlTypes.length]; static final CustomerAccountMtnOrderStruct myInstance = new CustomerAccountMtnOrderStruct(); private Long customerId; private Integer accountNumber; private Long mtn; private Integer cidbOrderId; private String mtnOrderState; private String mtnOrderType; private Date mtnOrderStateDate; private Long buddyMtn; protected CustomerAccountMtnOrderStruct(boolean init) { if (init) { struct = new MutableStruct(new Object[sqlTypes.length], sqlTypes, oraDataFactories); } } public CustomerAccountMtnOrderStruct() { this(true); } public Datum toDatum(Connection conn) throws SQLException { Datum datum = struct.toDatum(conn, SQL_NAME); return datum; } public ORAData create(Datum datum, int sqlType) throws SQLException { return create(null, datum, sqlType); } protected ORAData create(CustomerAccountMtnOrderStruct customerAccountMtnOrderStruct, Datum datum, int sqlType) throws SQLException { if (datum == null) { return null; } if (customerAccountMtnOrderStruct == null) { customerAccountMtnOrderStruct = new CustomerAccountMtnOrderStruct(false); } customerAccountMtnOrderStruct.struct = new MutableStruct((STRUCT) datum, sqlTypes, oraDataFactories); return customerAccountMtnOrderStruct; } public static ORADataFactory getORADataFactory() { return myInstance; } public Long getCustomerId() throws SQLException { return (Long)struct.getAttribute(0); } public void setCustomerId(Long customerId) throws SQLException { this.customerId = customerId; struct.setAttribute(0, customerId); } public Integer getAccountNumber() throws SQLException { return (Integer)struct.getAttribute(1); } public void setAccountNumber(Integer accountNumber) throws SQLException { this.accountNumber = accountNumber; struct.setAttribute(1, accountNumber); } public Long getMtn() throws SQLException { return (Long)struct.getAttribute(2); } public void setMtn(Long mtn) throws SQLException { this.mtn = mtn; struct.setAttribute(2, mtn); } public Integer getCidbOrderId() throws SQLException { return (Integer)struct.getAttribute(3); } public void setCidbOrderId(Integer cidbOrderId) throws SQLException { this.cidbOrderId = cidbOrderId; struct.setAttribute(3, cidbOrderId); } public String getMtnOrderState() throws SQLException { return (String)struct.getAttribute(4); } public void setMtnOrderState(String mtnOrderState) throws SQLException { this.mtnOrderState = mtnOrderState; struct.setAttribute(4, mtnOrderState); } public String getMtnOrderType() throws SQLException { return (String)struct.getAttribute(5); } public void setMtnOrderType(String mtnOrderType) throws SQLException { this.mtnOrderType = mtnOrderType; struct.setAttribute(5, mtnOrderType); } public Date getMtnOrderStateDate() throws SQLException { return (Date)struct.getAttribute(6); } public void setMtnOrderStateDate(Date mtnOrderStateDate) throws SQLException { this.mtnOrderStateDate = mtnOrderStateDate; struct.setAttribute(6, mtnOrderStateDate); } public Long getBuddyMtn() throws SQLException { return (Long)struct.getAttribute(7); } public void setBuddyMtn(Long buddyMtn) throws SQLException { this.buddyMtn = buddyMtn; struct.setAttribute(7, buddyMtn); } @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + ((accountNumber == null) ? 0 : accountNumber.hashCode()); result = prime * result + ((cidbOrderId == null) ? 0 : cidbOrderId.hashCode()); result = prime * result + ((customerId == null) ? 0 : customerId.hashCode()); result = prime * result + ((mtn == null) ? 0 : mtn.hashCode()); return result; } @Override public boolean equals(Object obj) { if (this == obj) return true; if (obj == null) return false; if (getClass() != obj.getClass()) return false; CustomerAccountMtnOrderStruct other = (CustomerAccountMtnOrderStruct) obj; if (accountNumber == null) { if (other.accountNumber != null) return false; } else if (!accountNumber.equals(other.accountNumber)) return false; if (cidbOrderId == null) { if (other.cidbOrderId != null) return false; } else if (!cidbOrderId.equals(other.cidbOrderId)) return false; if (customerId == null) { if (other.customerId != null) return false; } else if (!customerId.equals(other.customerId)) return false; if (mtn == null) { if (other.mtn != null) return false; } else if (!mtn.equals(other.mtn)) return false; return true; } public String toString() { StringBuffer buffer = new StringBuffer(); buffer.append("[CustomerAccountMtnOrderStruct:"); buffer.append(" customerId: "); buffer.append(customerId); buffer.append(" accountNumber: "); buffer.append(accountNumber); buffer.append(" mtn: "); buffer.append(mtn); buffer.append(" cidbOrderId: "); buffer.append(cidbOrderId); buffer.append(" mtnOrderState: "); buffer.append(mtnOrderState); buffer.append(" mtnOrderStateDate: "); buffer.append(mtnOrderStateDate); buffer.append(" mtnOrderType: "); buffer.append(mtnOrderType); buffer.append(" buddyMtn: "); buffer.append(buddyMtn); buffer.append("]"); return buffer.toString(); } }
Once we have our Mapper class, then let's do the Handler!!! import java.sql.Connection; import java.sql.SQLException; import java.sql.Types; import java.util.ArrayList; import java.util.List; import oracle.jdbc.driver.OracleTypes; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; import com.ibatis.sqlmap.client.extensions.ParameterSetter; import com.ibatis.sqlmap.client.extensions.ResultGetter; import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback; import com.ibatis.sqlmap.engine.type.JdbcTypeRegistry; import com.ibm.ws.rsadapter.jdbc.WSJdbcConnection; import com.ibm.ws.rsadapter.jdbc.WSJdbcUtil; import com.vzw.common.globalrouter.dao.db.domain.CustomerAccountMtnOrder; import com.vzw.common.globalrouter.dao.db.typehandler.struct.CustomerAccountMtnOrderStruct; public class CustomerAccountMtnPreOrderListTypeHandlerCallBack implements TypeHandlerCallback { private static final String STRUCT_NAME = CustomerAccountMtnOrderStruct.SQL_NAME; private static final String ARRAY_NAME = "CUST_ACCNT_MTN_PRE_ORDER_LIST"; /*static { JdbcTypeRegistry.setType(STRUCT_NAME, OracleTypes.STRUCT); JdbcTypeRegistry.setType(ARRAY_NAME, OracleTypes.ARRAY); };*/ @SuppressWarnings("unchecked") public void setParameter(ParameterSetter parameterSetter, Object param) throws SQLException { if (param ==null) { parameterSetter.setNull(Types.ARRAY); }else{ List<CustomerAccountMtnOrder> listCustomerAccountMtnOrder = (ArrayList<CustomerAccountMtnOrder>) param; Connection nativeConnection = (Connection)WSJdbcUtil.getNativeConnection((WSJdbcConnection)parameterSetter.getPreparedStatement().getConnection()); CustomerAccountMtnOrderStruct[] structArray = new CustomerAccountMtnOrderStruct[listCustomerAccountMtnOrder.size()]; for(int i=0; i<listCustomerAccountMtnOrder.size(); i++){ CustomerAccountMtnOrder customerAccountMtnOrder = listCustomerAccountMtnOrder.get(i); structArray[i] = transformCustomerAccountMtnOrder(customerAccountMtnOrder); } ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(ARRAY_NAME, nativeConnection); ARRAY dataArray = new ARRAY(arrayDescriptor, nativeConnection, structArray); parameterSetter.setArray(dataArray); } } public Object valueOf(String arg0) { // TODO Auto-generated method stub return null; } public Object getResult(ResultGetter resultGetter) throws SQLException { // TODO Auto-generated method stub return null; } private CustomerAccountMtnOrderStruct transformCustomerAccountMtnOrder(final CustomerAccountMtnOrder customerAccountMtnOrder) throws SQLException { CustomerAccountMtnOrderStruct customerAccountMtnOrderStruct = new CustomerAccountMtnOrderStruct(); customerAccountMtnOrderStruct.setAccountNumber(customerAccountMtnOrder.getAccountNumber()); customerAccountMtnOrderStruct.setBuddyMtn(customerAccountMtnOrder.getBuddyMtn()); customerAccountMtnOrderStruct.setCidbOrderId(customerAccountMtnOrder.getCidbOrderId()); customerAccountMtnOrderStruct.setCustomerId(customerAccountMtnOrder.getCustomerId()); customerAccountMtnOrderStruct.setMtn(customerAccountMtnOrder.getMtn()); customerAccountMtnOrderStruct.setMtnOrderState(customerAccountMtnOrder.getMtnOrderState()); customerAccountMtnOrderStruct.setMtnOrderStateDate(customerAccountMtnOrder.getMtnOrderStateDate()); customerAccountMtnOrderStruct.setMtnOrderType(customerAccountMtnOrder.getMtnOrderType()); return customerAccountMtnOrderStruct; } }
And this is how it looks my Map (XML) <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd"> <sqlMap namespace="SP"> <parameterMap id="parametersMapTestListParams" class="java.util.Map"> <parameter property="customerId" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN" /> <parameter property="accountNumber" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN" /> <parameter property="listCustomerAccountMtnOrder" typeHandler="com.vzw.common.globalrouter.dao.db.typehandler.CustomerAccountMtnPreOrderListTypeHandlerCallBack" mode="IN"/> </parameterMap> <procedure id="TestListParams" parameterMap="parametersMapTestListParams"> {call ${env.qualifier}.test_list_params(?,?,?)} </procedure> </sqlMap>

In case you have to receive the Array from your Stored Proc, you just have to write in your Handler somethig like this: public Object getResult(ResultGetter getter) { ArrayList result = new ArrayList(); try { ARRAY array = (ARRAY)getter.getObject(); Object[] employees = (Object[])array.getArray(); for (Object emp: employees) { STRUCT s = (STRUCT)emp; Employee e = new Employee(); e.setId(((BigDecimal)s.getAttributes()[0]).longValue()); e.setName((String)s.getAttributes()[1]); e.setDateOfBirth((Date)s.getAttributes()[2]); result.add(e); } } catch (Exception e) { e.printStackTrace(); } return result; }

References: Using iBatis with a stored function returning a Collection Type and Oracle UDT Collections as IN and OUT parameters with iBatis

Pass Collection to Stored Procedure using iBatis

Today I had a really crazy idea: "Why not making my query work with a list of parameters like I do with Hibernate using the operator IN???"
It sounds simple, Isn't it??? Ok, then try to do it with an Stored Procedure in Oracle :P
Then, this is my Stored Proc (I had to create a Type to have support for Arrays in Oracle, because an Array is not a simple-provided data type): CREATE OR REPLACE TYPE NUMBER_ARRAY AS TABLE OF NUMBER; CREATE OR REPLACE PROCEDURE retrieve_mtn_eligibility_state ( p_cust_id IN NUMBER, p_acct_num IN NUMBER, p_array_mtn IN NUMBER_ARRAY, p_recordset OUT SYS_REFCURSOR ) AS BEGIN OPEN p_recordset FOR SELECT mtn_eligibility_state, mtn_eligibility_state_date, mtn, acct_num, cust_id FROM CUST_ACCT_MTN_STATE WHERE cust_id = p_cust_id AND acct_num = p_acct_num AND mtn IN ( SELECT * FROM TABLE(p_array_mtn) ); --TODO: Verify MTN given has CUST_ACCT_MTN_ORDER.bdy_mtn dbms_output.put_line('This is the content of that array'); FOR i IN 1 .. p_array_mtn.count LOOP dbms_output.put_line(i||'=' || p_array_mtn(i)); END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Some other kind of error occurred.'); END; / As you can see, my dear visitor, I'm expecting some simple parameters and I'm returning a ResultSet, really straight forward, but the problem comes with the Array of NUMBER (that's why I created my

TYPE NUMBER_ARRAY AS TABLE OF NUMBER

)

The solution to this problem is easier than it seems, just create a Handler dude!!! import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import java.util.ArrayList; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; import com.ibm.ws.rsadapter.jdbc.WSJdbcConnection; import com.ibm.ws.rsadapter.jdbc.WSJdbcUtil; import com.ibatis.sqlmap.client.extensions.ParameterSetter; import com.ibatis.sqlmap.client.extensions.ResultGetter; import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback; public class NumberArrayTypeHandlerCallBack implements TypeHandlerCallback { @SuppressWarnings("unchecked") public void setParameter(ParameterSetter setter, Object parameter) throws SQLException { if(parameter == null) { setter.setNull(Types.ARRAY); } else if(parameter instanceof ArrayList) { Statement stmt = setter.getPreparedStatement(); Connection nativeConnection = (Connection) WSJdbcUtil.getNativeConnection((WSJdbcConnection) stmt.getConnection()); ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUMBER_ARRAY", nativeConnection); parameter = new ARRAY(desc, nativeConnection, ((ArrayList) parameter).toArray()); } setter.setObject(parameter); } public Object getResult(ResultGetter getter) throws SQLException { return null; } public Object valueOf(String arg0) { return null; } } The magic begins when I transform the Connection (because I have to work at the connection level) provided by the Application Server (WebSphere on this case - If this happens to me on another App Server or with another Connection Pool, I'm going to be in trouble, because the issue will be how to retrieve a Native java.sql.Connection, but that will be another story).
Let's analyze this monster:
* Get Native Connection Connection nativeConnection = (Connection) WSJdbcUtil.getNativeConnection((WSJdbcConnection) stmt.getConnection());
*Create my ArrayDescriptor for my Type (these NUMBER_ARRAY is my TYPE I created previously) ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUMBER_ARRAY", nativeConnection);
*Create my parameter, an ARRAY (al fin y al cabo) and we're set! parameter = new ARRAY(desc, nativeConnection, ((ArrayList) parameter).toArray());

Finally this is my mapping file for my stored procedure <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd"> <sqlMap namespace="SP"> <parameterMap id="objectParameters" class="java.util.Map"> <parameter property="cust_id" jdbcType="INTEGER" javaType="java.lang.String" mode="IN" /> <parameter property="acc_num" jdbcType="INTEGER" javaType="java.lang.String" mode="IN" /> <parameter property="array_mtn" jdbcType="NUMBER_ARRAY" typeName="NUMBER_ARRAY" typeHandler="mx.com.company.common.db.typehandler.NumberArrayTypeHandlerCallBack" mode="IN"/> <parameter property="result" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT"/> </parameterMap> <resultMap id="resultSetMap" class="mx.com.company.MyDomainObject" > <result column="mtn_eligibility_state" property="mtnEligibilityState" /> <result column="mtn_eligibility_state_date" property="mtnEligibilityStateDate" /> </resultMap> <procedure id="retrieve_mtn_eligibility_state" parameterMap="objectParameters" resultMap="resultSetMap" > {call ${env.qualifier}.retrieve_mtn_eligibility_state(?,?,?,?)} </procedure> </sqlMap>

And my code lived happily ever after!!! Abur!!!

JDBC url for oracle RAC

If you have to connect to oracle RAC (Real Application Cluster) using JDBC with thin driver, the classic url:

jdbc:oracle:thin:@<HOST>:1521:<SID>

doesn’t work and you get the error ORA – 12505.
Instead, you must use this url:

jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)
(ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=host2) (PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=service)))

If you have an oracle client, like toad, check the tsnames.ora for the correct values of SERVICE_NAME and host.

Log SQL Statements on Hibernate

Hibernate, in the end, is all about dispatching SQL statements. SQL is at the heart of communication with an RDBMS system, and it is extremely important when struggling through performance problems or other bugs, that you know what is going on. After all, knowing the executed SQL allows you to determine the number of queries to complete an O/R mapping task, not to mention that seeing SQL queries is critical to understanding how to optimize the queries via indices and other database settings.
There are two ways (due to the legacy of Hibernate) to enable SQL logging. The first is to simply enable SQL logging in the Hibernate configuration By setting the hibernate.show_sql to TRUE

This is a nice quick and dirty solution, but it is relatively inflexible. SQL statements are always logged to System.out when that property is enabled, and on some servers, System.out isn't accessible by the average developer; or is cluttered with a million other log statements.

Alternatively, Hibernate uses the Apache-Jakarta Commons Logging package, which means that it will utilize log4j , java.util.logging , or potentially another logging framework. Typically, log messages are sent to commons logging, and then they are dispatched to one of these logging frameworks. Then, those logging frameworks determine where the message should be sent (log files, sockets, emails, database records, system out, system err, etc). It is easy to see how using these log frameworks will increase the flexibility of log statements. With Hibernate, simply setting the org.hibernate.SQL logger to 'DEBUG' or 'ALL' will ensure that all SQL statements are logged to the console.


If you set up a log4j category for org.hibernate.type
Get it to write out to the same log file as the org.hibernate.SQL
The type one will list the parameters for you after the SQL e.g.

2006-07-28 09:57:12,061 DEBUG org.hibernate.SQL - insert into BASKET_LINE_ALLOC (LAST_UPDATED, QUANTITY, CUSTOMER_REF, NOTES, BRANCH_ID, FUND_ID, TEMPLATE_ID, 
BASKET_LINE_ALLOC_ID) values (?, ?, ?, ?, ?, ?, ?, ?) 
2006-07-28 09:57:12,081 DEBUG org.hibernate.type.TimestampType - binding '2006-07-28 09:57:12' to parameter: 1 
2006-07-28 09:57:12,081 DEBUG org.hibernate.type.IntegerType - binding '3' to parameter: 2 
2006-07-28 09:57:12,082 DEBUG org.hibernate.type.StringType - binding '' to parameter: 3 
2006-07-28 09:57:12,082 DEBUG org.hibernate.type.StringType - binding '' to parameter: 4 
2006-07-28 09:57:12,082 DEBUG org.hibernate.type.LongType - binding '511' to parameter: 5 
2006-07-28 09:57:12,082 DEBUG org.hibernate.type.LongType - binding '512' to parameter: 6 
2006-07-28 09:57:12,082 DEBUG org.hibernate.type.LongType - binding null to parameter: 7 
2006-07-28 09:57:12,082 DEBUG org.hibernate.type.LongType - binding '180030' to parameter: 8 

@Autowired and @Qualifier

"Your best friends could be @Autowired and @Qualifier"
I have to migrate data from DB2 to Oracle, both databases have the same structure, well, my Oracle has better design, but I'm reusing the some objects, like POJO's and DAO's, the only thing I changed were my mappings, because I have to keep my mappings for DB2 and for Oracle.
If I have the same DAO for both databases, what can I do?
daoApplicationContext.xml <?xml version="1.0" encoding="UTF-8" ?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jee="http://www.springframework.org/schema/jee" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-2.5.xsd"> <!-- ========================= Data Sources ============================= --> <bean id="db2DS" class="org.apache.tomcat.dbcp.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="COM.ibm.db2.jdbc.app.DB2Driver"/> <property name="url" value="jdbc:db2:best"/> <property name="username" value="${userId}"/> <property name="password" value="${pwd}"/> </bean> <bean id="oracleDS" class="org.apache.tomcat.dbcp.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/> <property name="url" value="jdbc:oracle:thin:@twsifcmtzdt01.tdc.vzwcorp.com:1535:mkssbxdb"/> <property name="username" value="${userId}"/> <property name="password" value="${pwd}"/> </bean> <!--============================= SessionFactory ===============================--> <bean id="sessionFactoryDB2" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean"> <property name="dataSource" ref="db2DS" /> <property name="configLocation"> <value>classpath:com/vzw/dvs/mapping/mappingDB2.cfg.xml</value> </property> <property name="hibernateProperties"> <props> <prop key="hibernate.dialect"> org.hibernate.dialect.DB2Dialect </prop> <prop key="hibernate.show_sql">false</prop> <prop key="hibernate.format_sql">true</prop> <prop key="hibernate.order_updates">true</prop> <prop key="hibernate.generate_statistics">true</prop> <prop key="hibernate.use_sql_comments">false</prop> <prop key="hibernate.bytecode.use_reflection_optimizer"> false </prop> <prop key="hibernate.jdbc.batch_size">15</prop> <prop key="hibernate.jdbc.batch_versioned_data"> true </prop> <prop key="hibernate.query.factory_class"> org.hibernate.hql.ast.ASTQueryTranslatorFactory </prop> <prop key="hibernate.connection.isolation">4</prop> </props> </property> </bean> <bean id="sessionFactoryOracle" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean"> <property name="dataSource" ref="oracleDS" /> <property name="configLocation"> <value>classpath:com/vzw/dvs/mapping/mappingOracle.cfg.xml</value> </property> <property name="hibernateProperties"> <props> <prop key="hibernate.dialect"> org.hibernate.dialect.Oracle10gDialect </prop> <prop key="hibernate.show_sql">false</prop> <prop key="hibernate.format_sql">true</prop> <prop key="hibernate.order_updates">true</prop> <prop key="hibernate.generate_statistics">true</prop> <prop key="hibernate.use_sql_comments">false</prop> <prop key="hibernate.bytecode.use_reflection_optimizer"> false </prop> <prop key="hibernate.jdbc.batch_size">15</prop> <prop key="hibernate.jdbc.batch_versioned_data"> true </prop> <prop key="hibernate.query.factory_class"> org.hibernate.hql.ast.ASTQueryTranslatorFactory </prop> <prop key="hibernate.connection.isolation">4</prop> </props> </property> </bean> <!-- ============================ DAOs DB2 ================================== --> <bean id="UserDAOdb2" class="com.vzw.dvs.core.persistence.dao.impl.UserDAOImpl"> <property name="sessionFactory" ref="sessionFactoryDB2" /> </bean> <!-- ============================ DAOs Oracle ================================== --> <bean id="UserDAO" class="com.vzw.dvs.core.persistence.dao.impl.UserDAOImpl"> <property name="sessionFactory" ref="sessionFactoryOracle" /> </bean> </beans>
As you can see, I have 2 different DAO's sharing the same Interface & Implementation, but each one belongs to a different "sessionFactory" <bean id="UserDAOdb2" class="com.vzw.dvs.core.persistence.dao.impl.UserDAOImpl"> <property name="sessionFactory" ref="sessionFactoryDB2" /> </bean> <bean id="UserDAO" class="com.vzw.dvs.core.persistence.dao.impl.UserDAOImpl"> <property name="sessionFactory" ref="sessionFactoryOracle" /> </bean> serviceApplicationContext.xml <?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:util="http://www.springframework.org/schema/util" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-2.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd"> <!-- =============== JTA ==========================================--> <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager"> <property name="sessionFactory"> <ref bean="sessionFactoryOracle" /> </property> </bean> <!--====================== Transaction Managers with Annotations =====================--> <tx:annotation-driven transaction-manager="transactionManager" /> <!--====================== Enable Autowire =====================--> <context:annotation-config /> <!--================== Implementation of Services =====================--> <bean id="MigrateTablesService" class="com.vzw.dvs.core.business.service.web.impl.MigrateTablesServiceImpl"/> </beans>
Nothing special, right? But now here it comes the interesting part, because @Autowired by itself it will inject the given bean in your ServiceImpl, but you must have only one bean of that type. In my case I have more than 1, so @Autowired will throw an error when loading the context.
Then it comes @Qualifier which will match based on the given name, and problem solved chavos!!!
public class MigrateTablesServiceImpl implements MigrateTablesService { private static final Logger log = Logger.getLogger(MigrateTablesServiceImpl.class); @Autowired @Qualifier("UserDAOdb2") private UserDAO userDAOdb2; @Autowired @Qualifier("UserDAO") private UserDAO userDAO; @Transactional(readOnly = false, propagation = Propagation.REQUIRED) public boolean doMigrateUser() { log.debug("Migrating User"); boolean result = false; try{ List<User> listItems = this.userDAOdb2.getAll(); for(User transientInstance: listItems){ log.debug("Item retrieved from DB2 "+transientInstance); User user = new User(); BeanUtils.copyProperties(transientInstance, user); //Trim Strings user.setDbUserId(StringUtils.trim(transientInstance.getDbUserId())); user.setBudgetmanagerind(StringUtils.trim(transientInstance.getBudgetmanagerind())); user.setDashboardaccess(StringUtils.trim(transientInstance.getDashboardaccess())); user.setDirectorusercd(StringUtils.trim(transientInstance.getDirectorusercd())); user.setEmail(StringUtils.trim(transientInstance.getEmail())); user.setFirstname(StringUtils.trim(transientInstance.getFirstname())); user.setFunctionalareacd(StringUtils.trim(transientInstance.getFunctionalareacd())); user.setHomephone(StringUtils.trim(transientInstance.getHomephone())); user.setInternalit(StringUtils.trim(transientInstance.getInternalit())); user.setLastname(StringUtils.trim(transientInstance.getLastname())); user.setMobilephone(StringUtils.trim(transientInstance.getMobilephone())); user.setPassword(StringUtils.trim(transientInstance.getPassword())); user.setUserid(StringUtils.trim(transientInstance.getUserid())); user.setUserstatuscd(StringUtils.trim(transientInstance.getUserstatuscd())); user.setVpid(StringUtils.trim(transientInstance.getVpid())); user.setUtilizationtarget(StringUtils.trim(transientInstance.getUtilizationtarget())); user.setWorkphone(StringUtils.trim(transientInstance.getWorkphone())); if(user.getDbTimestamp()==null){ user.setDbTimestamp(new Date()); } this.userDAO.save(user); } result = true; }catch (Exception ex) { log.warn(ex); } log.debug("Done"); return result; }
Notes: I used org.springframework.beans.BeanUtils to create a copy of my bean (pojo) returned from DB2 into a non-persistent Pojo which I'll manipulate before inserting into Oracle (DB2 completes each column value with spaces to achieve the given size of that column, so each time you query a DB2 column you'll get a lot of extra spaces)

EmbeddedId in Hibernate, AnnotatedClasses

I had to do some inserts to a table with a composite PK:



Nothing special, I decided to use Hibernate with annotations (but what about the PK: just create another class -duh, but How can I tell hibernate this is my PK? @EmbeddedId and @Embeddable):

@Embeddable

Defines my composite PK @Embeddable public class RimMessageId implements Serializable { private static final long serialVersionUID = -1L;//Just 4 fun ;) @Column(name = "dvs_server", length = 10, nullable = false) private String server; @Column(name = "dvs_yyyy", length = 4, nullable = false) private String year; @Column(name = "dvs_month", length = 2, nullable = false) private String month; @Column(name = "dvs_dd", length = 2, nullable = false) private String day; @Column(name = "dvs_hh", length = 2, nullable = false) private String hour; @Column(name = "dvs_mm", length = 2, nullable = false) private String minute; @Column(name = "dvs_ss", length = 2, nullable = false) private String second; @Column(name = "dvs_millis", length = 6, nullable = false) private String millis; //Gette's and Setter's }

@EmbeddedId

Includes my composite Pk in my main Pojo @Entity @Table(name = "RIM_MESSAGES") public class RimMessage implements Serializable { private static final long serialVersionUID = 1L;;//Just 4 fun ;) @EmbeddedId private RimMessageId id; @Column(name = "mdn", length = 10) private String mdn; @Column(name = "bun", length = 20) private String bun; @Column(name = "evt", length = 6) private String evt; @Column(name = "bsh", length = 2) private String bsh; @Column(name = "bex", length = 10) private String bex; @Column(name = "lng", length = 3) private String lng; @Column(name = "rim_tod", length = 4) private String rimTod; @Column(name = "rim_doy", length = 8) private String rimDoy; @Column(name = "rim_ip", length = 15) private String rimIp; //Gette's and Setter's }
If you want to include in your sessionFactory (daoApplicationContext.xml) my annotated pojos, add this entry <property name="annotatedClasses"> <list> <value>com.vzw.gsb.core.persistence.integration.RimMessage</value> </list> </property>
As a reminder, if you want to use a propeties file to provide parameter for your DataSource, you have to do this: <!-- ========================= Properties ============================= --> <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="location"> <value>classpath:rim-rtr-messages.properties</value> </property> </bean> <!-- ========================= Data Sources ============================= --> <bean id="myDataSource" class="org.apache.tomcat.dbcp.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="${driverClassName}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </bean>