/**
* Copyright 2007 - 2011 Skyway Software, Inc.
*/
package org.skyway.spring.util.dao.call.oracle;
import java.beans.PropertyDescriptor;
import java.sql.Array;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Struct;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.skyway.common.util.ConversionUtils;
import org.skyway.spring.util.dao.call.VendorTypeHandler;
import org.skyway.spring.util.dao.query.SQLResultsProvider;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.beans.PropertyAccessorFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.metadata.CallParameterMetaData;
import org.springframework.jdbc.core.metadata.OracleCallMetaDataProvider;
import org.springframework.jdbc.datasource.ConnectionProxy;
@SuppressWarnings("nls")
public class OracleTypeHandler extends OracleCallMetaDataProvider implements VendorTypeHandler{
private static transient Log _logger = LogFactory.getLog(OracleTypeHandler.class);
public static String ORACLE_PL_SQL_RECORD_TYPE_NAME = "PL/SQL RECORD";
public static String ORACLE_REF_CURSOR_TYPE_NAME = "REF CURSOR";
public static String ORACLE_ARRAY_TYPE_NAME = "TABLE";
public static String ORACLE_FLOAT_TYPE_NAME = "FLOAT";
private List<OracleArgument> oracleArguments = new ArrayList<OracleArgument>();
private List<String> columnNames;
public OracleTypeHandler(DatabaseMetaData databaseMetaData) throws SQLException {
super(databaseMetaData);
}
public Object transformInputValue(JdbcTemplate jdbcTemplate, SqlParameter sqlParameter, Object value){
ConnectionCallback connectionCallback;
switch (sqlParameter.getSqlType()){
case Types.STRUCT:
case Types.ARRAY:
connectionCallback = new TransformInputConnectionCallback(sqlParameter, value);
value = jdbcTemplate.execute(connectionCallback);
}
return value;
}
public Object transformOutputValue(JdbcTemplate jdbcTemplate, SqlParameter sqlParameter, Object value, Class<?> type) {
ConnectionCallback connectionCallback;
switch (sqlParameter.getSqlType()){
case Types.STRUCT:
case Types.ARRAY:
case OracleTypes.CURSOR:
connectionCallback = new TransformOutputConnectionCallback(sqlParameter, value, type);
value = jdbcTemplate.execute(connectionCallback);
}
return value;
}
private class TransformInputConnectionCallback implements ConnectionCallback{
private SqlParameter sqlParameter;
private Object value;
public TransformInputConnectionCallback(SqlParameter sqlParameter, Object value){
this.sqlParameter = sqlParameter;
this.value = value;
}
public Object doInConnection(Connection connection) throws SQLException, DataAccessException{
if (connection instanceof ConnectionProxy){
connection = ((ConnectionProxy)connection).getTargetConnection();
}
if (sqlParameter.getSqlType() == Types.STRUCT){
value = createStruct(connection, sqlParameter.getTypeName(), value);
}else if (sqlParameter.getSqlType() == Types.ARRAY){
value = createArray(connection, sqlParameter.getTypeName(), value);
}
return value;
}
}
private class TransformOutputConnectionCallback implements ConnectionCallback{
private SqlParameter sqlParameter;
private Object value;
private Class<?> type;
public TransformOutputConnectionCallback(SqlParameter sqlParameter, Object value, Class<?> type){
this.sqlParameter = sqlParameter;
this.value = value;
this.type = type;
}
public Object doInConnection(Connection connection) throws SQLException, DataAccessException{
if (connection instanceof ConnectionProxy){
connection = ((ConnectionProxy)connection).getTargetConnection();
}
if (sqlParameter.getSqlType() == getRefCursorSqlType()){
value = ((SQLResultsProvider)value).getResultsAsSet(sqlParameter.getName(), type);
}else if (sqlParameter.getSqlType() == Types.STRUCT){
value = getStruct(connection, value, type, sqlParameter.getTypeName());
}else if (sqlParameter.getSqlType() == Types.ARRAY){
value = getArray(connection, value, type);
}
return value;
}
}
@Override
public void initializeWithProcedureColumnMetaData(DatabaseMetaData databaseMetaData, String catalogName, String schemaName, String procedureName) throws SQLException {
super.initializeWithProcedureColumnMetaData(databaseMetaData, catalogName, schemaName, procedureName);
initializeArguments(databaseMetaData, schemaName, procedureName);
}
@Override
public SqlParameter createDefaultInParameter(String parameterName, CallParameterMetaData meta) {
if (meta.getSqlType() == Types.OTHER) {
if (isRefCursorType(meta)){
throw new UnsupportedOperationException("A ref cursor is not supported as an input parameter.");
}else if (isPLSQLRecordType(meta)){
throw new UnsupportedOperationException("A PL/SQL record is not currently supported as an input parameter.");
}else if (isArrayType(meta)){
return new SqlParameter(parameterName, getArrayType(), getArgumentType(meta));
}else if (isFloatType(meta)){
return new SqlParameter(parameterName, OracleTypes.FLOAT);
}else{
return new SqlParameter(parameterName, getStructType(), meta.getTypeName());
}
}
return super.createDefaultInParameter(parameterName, meta);
}
@Override
public SqlParameter createDefaultOutParameter(String parameterName, CallParameterMetaData meta) {
if (meta.getSqlType() == Types.OTHER) {
if (isRefCursorType(meta)){
return new SqlOutParameter(parameterName, getRefCursorSqlType(), new OracleSQLResultsProvider());
}else if (isPLSQLRecordType(meta)){
throw new UnsupportedOperationException("A PL/SQL record is not currently supported as an output parameter.");
}else if (isArrayType(meta)){
return new SqlOutParameter(parameterName, getArrayType(), getArgumentType(meta));
}else if (isFloatType(meta)){
return new SqlOutParameter(parameterName, OracleTypes.FLOAT);
}else{
return new SqlOutParameter(parameterName, getStructType(), meta.getTypeName());
}
}else{
return super.createDefaultOutParameter(parameterName, meta);
}
}
@Override
public int getRefCursorSqlType(){
return OracleTypes.CURSOR;
}
public int getArrayType(){
return Types.ARRAY;
}
public int getStructType(){
return Types.STRUCT;
}
protected int getParameterPosition(CallParameterMetaData parameter){
List<CallParameterMetaData> parameters = getCallParameterMetaData();
int index = 0;
for (CallParameterMetaData currentParameter : parameters){
if (parameter.getParameterName().equals(currentParameter.getParameterName())){
return index;
}
index++;
}
return -1;
}
protected String getArgumentType(CallParameterMetaData parameter) {
OracleArgument oracleArgument = getArgument(parameter);
return oracleArgument.getTypeOwner() + "." + oracleArgument.getTypeName();
}
protected OracleArgument getArgument(CallParameterMetaData parameter) {
List<OracleArgument> argumentsByPosition = getArgumentsByPosition(parameter, getParameterPosition(parameter) + 1);
if (argumentsByPosition.size() == 0) {
throw new RuntimeException("Unable to find arguments for procedure parameter: " + parameter.getParameterName());
} else{
return argumentsByPosition.get(0);
}
}
protected List<OracleArgument> getArgumentsByPosition(CallParameterMetaData parameter, int position) {
List<OracleArgument> argumentsByPosition = new ArrayList<OracleArgument>();
for (OracleArgument oracleArgument : oracleArguments){
if (oracleArgument.getPosition() == position){
argumentsByPosition.add(oracleArgument);
}
}
return argumentsByPosition;
}
protected void initializeArguments(DatabaseMetaData databaseMetaData, String schemaName, String procedureName) throws SQLException {
PreparedStatement parametersStatement;
OracleArgument oracleArgument;
Connection connection;
ResultSet resultSet;
connection = databaseMetaData.getConnection();
parametersStatement = connection.prepareStatement("select * from all_arguments where owner = ? and object_name = ? order by position, sequence, data_level");
parametersStatement.setString(1, schemaName);
parametersStatement.setString(2, procedureName);
resultSet = parametersStatement.executeQuery();
while (resultSet.next()) {
oracleArgument = new OracleArgument();
oracleArgument.setOwner(resultSet.getString("owner"));
oracleArgument.setObjectName(resultSet.getString("object_name"));
oracleArgument
.setArgumentName(resultSet.getString("argument_name"));
oracleArgument.setPosition(resultSet.getInt("position"));
oracleArgument.setSequence(resultSet.getInt("sequence"));
oracleArgument.setDataLevel(resultSet.getInt("data_level"));
oracleArgument.setDataType(resultSet.getString("data_type"));
oracleArgument.setInOut(resultSet.getString("in_out"));
oracleArgument.setTypeOwner(resultSet.getString("type_owner"));
oracleArgument.setTypeName(resultSet.getString("type_name"));
oracleArguments.add(oracleArgument);
}
}
protected boolean isRefCursorType(CallParameterMetaData parameter){
if (parameter.getTypeName() != null && parameter.getTypeName().equals(ORACLE_REF_CURSOR_TYPE_NAME)){
return true;
}else{
return false;
}
}
protected boolean isPLSQLRecordType(CallParameterMetaData parameter){
if (parameter.getTypeName() != null && parameter.getTypeName().equals(ORACLE_PL_SQL_RECORD_TYPE_NAME)){
return true;
}else{
return false;
}
}
protected boolean isArrayType(CallParameterMetaData parameter){
if (parameter.getTypeName() != null && parameter.getTypeName().equals(ORACLE_ARRAY_TYPE_NAME)){
return true;
}else{
return false;
}
}
protected boolean isFloatType(CallParameterMetaData parameter){
if (parameter.getTypeName() != null && parameter.getTypeName().equals(ORACLE_FLOAT_TYPE_NAME)){
return true;
}else{
return false;
}
}
protected Struct createStruct(Connection connection, String structName, Object value) throws SQLException {
Map<String, Object> structMap = new HashMap<String, Object>();
Map<String, PropertyDescriptor> propertyDescriptors;
OracleStructDescriptor structDescriptor;
ResultSetMetaData metaData;
int attributeCount;
Struct structValue;
structDescriptor = new OracleStructDescriptor(structName, connection);
metaData = structDescriptor.getMetaData();
attributeCount = structDescriptor.getLength();
propertyDescriptors = getPropertyDescriptors(metaData, attributeCount, value);
BeanWrapper beanWrapper = new BeanWrapperImpl (value);
int index = 0;
for (String columnName : getColumnNames(metaData, attributeCount)) {
PropertyDescriptor propertyDescriptor = propertyDescriptors.get(columnName);
if (propertyDescriptor != null) {
Object inValue = beanWrapper.getPropertyValue(propertyDescriptor.getName());
structMap.put(columnName, getConvertedValue(inValue, propertyDescriptor.getReadMethod().getReturnType()));
}
index++;
}
structValue = new OracleSTRUCT(structDescriptor, connection, structMap).getInstance();
return structValue;
}
protected Array createArray(Connection connection, String arrayName, Object value) throws SQLException {
OracleArrayDescriptor arrayDescriptor;
Struct[] structs;
Array arrayValue;
int size;
if (value instanceof Collection<?> == false) {
throw new UnsupportedOperationException("An array can only be created from a collection");
}
size = ((Collection<?>) value).size();
structs = new Struct[size];
arrayDescriptor = new OracleArrayDescriptor(arrayName, connection);
String structName = arrayDescriptor.getBaseName();
int index = 0;
for (Object element : ((Collection<?>) value)) {
structs[index++] = createStruct(connection, structName, element);
}
arrayValue = new OracleARRAY(arrayDescriptor, connection, structs).getInstance();
return arrayValue;
}
protected <T> T getStruct(Connection connection, Object value, Class<T> targetClass, String structName) throws SQLException {
T convertedValue = null;
if (value instanceof Struct) {
convertedValue = populateStruct(connection, (Struct) value, targetClass, structName);
}
return convertedValue;
}
public <T> Set<T> getArray(Connection connection, Object value, Class<T> targetClass) throws SQLException {
Set<T> results = new LinkedHashSet<T>();
Array arrayValue;
ResultSet resultSet;
if (value instanceof Array) {
arrayValue = (Array) value;
resultSet = arrayValue.getResultSet();
while (resultSet.next()) {
Object object = resultSet.getObject(1);
object = resultSet.getObject(2);
if (object instanceof Struct) {
results.add(populateStruct(connection, (Struct) object, targetClass, arrayValue.getBaseTypeName()));
}
}
}
return results;
}
protected <T> T populateStruct(Connection connection, Struct structValue, Class<T> targetClass, String structName) throws SQLException {
Map<String, PropertyDescriptor> propertyDescriptors;
OracleStructDescriptor structDescriptor;
ResultSetMetaData metaData;
T targetInstance = null;
int attributeCount;
structDescriptor = new OracleStructDescriptor(structName, connection);
metaData = structDescriptor.getMetaData();
attributeCount = structDescriptor.getLength();
try {
targetInstance = targetClass.newInstance();
} catch (Exception x) {
throw new RuntimeException("Could not create instance of class: "
+ targetClass.getName(), x);
}
propertyDescriptors = this.getPropertyDescriptors(metaData, attributeCount, targetInstance);
BeanWrapper beanWrapper = new BeanWrapperImpl (targetInstance);
int index = 0;
for (String columnName : getColumnNames(metaData, attributeCount)) {
PropertyDescriptor propertyDescriptor = propertyDescriptors
.get(columnName);
if (propertyDescriptor != null) {
Object value = structValue.getAttributes()[index];
beanWrapper.setPropertyValue(propertyDescriptor.getName(), getConvertedValue(value, propertyDescriptor.getReadMethod().getReturnType()));
}
index++;
}
return targetInstance;
}
protected <T> T getConvertedValue(Object value, Class<T> targetClass) {
T convertedValue = ConversionUtils.convert(value, targetClass);
return convertedValue;
}
protected Map<String, PropertyDescriptor> getPropertyDescriptors(ResultSetMetaData metaData, int columns, Object resultObject) throws SQLException{
BeanWrapper beanWrapper = PropertyAccessorFactory.forBeanPropertyAccess(resultObject);
Map<String, PropertyDescriptor> columnPropertyDescriptors;
PropertyDescriptor[] beanPropertyDescriptors;
PropertyDescriptor propertyDescriptor;
if (_logger.isDebugEnabled()){
_logger.debug("Matching properties for class: " + resultObject.getClass().getName());
}
beanPropertyDescriptors = beanWrapper.getPropertyDescriptors();
columnPropertyDescriptors = new HashMap<String, PropertyDescriptor>();
for (String columnName : getColumnNames(metaData, columns)) {
propertyDescriptor = getPropertyDescriptor(beanPropertyDescriptors, columnName, false);
if (propertyDescriptor != null){
if (_logger.isDebugEnabled()){
_logger.debug("Matching property found: " + propertyDescriptor.getName() + " for column: " + columnName);
}
columnPropertyDescriptors.put(columnName, propertyDescriptor);
}
}
return columnPropertyDescriptors;
}
protected PropertyDescriptor getPropertyDescriptor(PropertyDescriptor[] propertyDescriptors, String columnName, boolean caseSensitive){
String propertyName;
for (PropertyDescriptor propertyDescriptor : propertyDescriptors){
propertyName = propertyDescriptor.getName();
if (propertyName != null && (caseSensitive ? propertyName.equals(columnName) : propertyName.equalsIgnoreCase(columnName))){
return propertyDescriptor;
}
}
return null;
}
protected List<String> getColumnNames(ResultSetMetaData metaData, int columns) throws SQLException {
String columnName;
if (columnNames == null) {
columnNames = new ArrayList<String>();
for (int index = 1; index <= columns; index++) {
columnName = metaData.getColumnName(index);
columnNames.add(columnName);
}
}
return columnNames;
}
}