Casting java.sql.Connection to oracle.jdbc.OracleConnection results in compilation error
I would like to pass java.sql.Connection
in oracle.jdbc.OracleConnection
to bind data ARRAY
to my request.
When I try the following on scala 2.10, bonecp 0.8.0 and slick 2.0.0:
import com.jolbox.bonecp.ConnectionHandle
import oracle.jdbc.OracleConnection
def failsWithCompilationError() = {
Database.forDataSource(ds).withDynTransaction {
val connection = dynamicSession.conn.asInstanceOf[ConnectionHandle].getInternalConnection
println(connection.unwrap(classOf[OracleConnection]))
// When uncommenting following two lines a compilation error "error while loading AQMessage, class file '.../ojdbc6.jar(oracle/jdbc/aq/AQMessage.class)' is broken" will occur
// val oracleConnection: OracleConnection = connection.unwrap(classOf[OracleConnection])
// println(oracleConnection)
}
}
and uncomment two lines with val
type assignment OracleConnection
and println
compilation failure
[error] error while loading AQMessage, class file '.../ojdbc6.jar(oracle/jdbc/aq/AQMessage.class)' is broken
...
I've already verified that the ojdbc6.jar shouldn't get corrupted by downloading a newer version from Oracle.
source to share
It seems that the problem was with the Scala compiler.
Once I incorporated the dependency functionality oracle.jdbc.OracleConnection
into a simple old Java class embedded in a separate .jar and linked to my Scala code, things started rolling.
This is how I got it to work:
OracleArray.java
package my.application.oracle.collections;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import oracle.sql.ARRAY;
import scala.Long;
import scala.Tuple2;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/*
Wraps usage of Oracle ARRAYs since casting java.sql.Connection to oracle.jdbc.Connection does not compile on Scala.
*/
public class OracleArray {
public static List<Tuple2<Long, Long>> fetchAssetsByIds(List ids, Connection connection) throws SQLException {
OracleConnection oracleConnection = (OracleConnection) connection;
ARRAY oracleArray = oracleConnection.createARRAY("MY_ARRAY_SQL_TYPE", ids.toArray());
String sql = "SELECT a.id, a.value" +
"FROM ASSET a " +
"WHERE a.id IN (SELECT COLUMN_VALUE FROM TABLE(?))";
PreparedStatement statement = oracleConnection.prepareStatement(sql);
try {
OraclePreparedStatement oraclePreparedStatement = (OraclePreparedStatement) statement;
oraclePreparedStatement.setArray(1, oracleArray);
ResultSet resultSet = oraclePreparedStatement.executeQuery();
try {
ArrayList<Tuple2<Long, Long>> resultTuples = new ArrayList<>();
while (resultSet.next()) {
long id = resultSet.getLong(1);
long value = resultSet.getLong(2);
resultTuples.add(new Tuple2(id, value));
}
return resultTuples;
} finally {
resultSet.close();
}
} finally {
statement.close();
}
}
}
DataUser.scala
package my.application
import my.application.oracle.collections.OracleArray
import scala.slick.driver.JdbcDriver.backend.Database
import Database.dynamicSession
import com.jolbox.bonecp.ConnectionHandle
import java.sql.Connection
import collection.JavaConversions._
/*
Uses BoneCP and Slick to connect to database and relays java.sql.Connection to
OracleArray in order to run operations that use Oracle ARRAYs
*/
object DataUser {
def doSomethingWithAssets(ids: Seq[Long]): Unit = {
Database.forDataSource(ds).withDynTransaction {
val connection: Connection = dynamicSession.conn.asInstanceOf[ConnectionHandle].getInternalConnection
val assets: Seq[(Long, Long)] = OracleArray.fetchAssetsByIds(ids, connection)
println(assets)
}
}
}
source to share
Not sure if my situation is related, but using the Play platform, this only works for me when logSql = false :
db.withConnection { implicit c =>
val oracleConnection = c.unwrap(classOf[OracleConnection])
}
When I set logSql = true I get:
com.sun.proxy. $ Proxy17 cannot be passed to oracle.jdbc.OracleConnection java.lang.ClassCastException: com.sun.proxy. $ Proxy17 cannot be dropped oracle.jdbc.OracleConnection
So something about the logSql config might cause the pivot to fail. I do not know why.
I think it might have something to do with the Hikari connection pool, but maybe the connection pool configuration is causing a similar problem.
source to share