Accessing Oracle User-Defined Type with Spring JDBC

It's not so long I wrote some single-purpose utility which called a stored procedure in Oracle database. The procedure returned as its result an user-defined data type.

Definition of the type looked approximately as this one:

CREATE OR REPLACE TYPE Summary as OBJECT 
( 
  ID Number(10),
  Sum1 NUMBER(20,3),
  Sum2 NUMBER(20,3)

  CONSTRUCTOR FUNCTION Summary RETURN SELF AS RESULT
);

I could have specified the mappings between Oracle object and Java class by implementing the SQLData interface, the approach supported by Spring Data. But I had only Spring JDBC available so I used the SqlReturnType.

First Maven dependencies:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>

 <groupId>org.bithill.projectx</groupId>
 <artifactId>MyTestUtil</artifactId>
 <version>1.0</version>

 <dependencies>

  <dependency>
    <groupId>com.oracle.oracle</groupId>
    <artifactId>ojdbc14</artifactId>
    <version>10.2.0.4</version>
  </dependency>

  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>3.0.7.RELEASE</version>
  </dependency>

 </dependencies>
   
</project> 

The utility to read the data is really simple - just load the driver, define the SimpleJdbcCall and call it and get the result. Only thing you have to remember,is to register the hendler for the user-defined type.

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import java.sql.SQLException;

import static java.lang.System.out;
import static oracle.jdbc.OracleTypes.*;

public class Main
{
   public static void main (String... args) throws SQLException 
   {
      String connectionString = args[0]; // JDBC URL

      DriverManagerDataSource ds = new DriverManagerDataSource();
      ds.setDriverClassName("oracle.jdbc.OracleDriver");
      ds.setUrl(connectionString);

      SimpleJdbcCall sumProcCall = new SimpleJdbcCall(new JdbcTemplate(ds))
        .withSchemaName("SCHEMA").withCatalogName("PKG")
        .withProcedureName("GET_SUMMARY")
        .declareParameters
        (
          // IN params
          new SqlParameter("P_Id", NUMBER),
          new SqlParameter("P_OrgUnit", VARCHAR),

          // OUT params
          new SqlOutParameter("P_Summary", STRUCT, "SUMMARY", new SummaryHandler() ),
          new SqlOutParameter("P_err_msg", VARCHAR),
          new SqlOutParameter("P_err_code", NUMBER)
        );

       (Summary)sumProcCall.execute
       ( Integer.valueOf(1),"ou.subsidiary1").get("P_Summary");

} 

Here is the type handler converting the raw information from Oracle's STRUCT to a Java class.

import oracle.sql.STRUCT;
import org.springframework.jdbc.core.SqlReturnType;

import java.sql.CallableStatement;
import java.sql.SQLException;

public class SummaryHandler implements SqlReturnType
{
    // struct data from jdbc
    private Object[] data;

    public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName)
    throws SQLException
    {
        data = ((STRUCT)cs.getObject(INDEX_OF_SUMMARY_PARAMETER)).getAttributes();

        Summary result = new Summary
        (new SummaryLine( (BigDecimal)data[0]) /*, ...*/);

        return result;
    }
}

Using Smart Card as Keystore in Java, signing

This is the promised sequel of the article from September 8th 2011.
It does not represent the only one way how to achieve the desired result, especially when it comes to manipulation with certificates and keys certificate management.

Loading Certificate into the Card

It seems that the easiest approach is to prepare the key store on disk and load it  into the card when ready. For that we use mostly two tools - openssl to transform keys and certificates and keytool to manage the key store.

Create the key store on disk and fill with certificates

Create a private key entry, i.e. a certificate containing a private key, by converting our certificate and private key to PKCS12 format: openssl pkcs12 -export -out cert.p12 -in cert.pem -inkey key.pem

I used org.apache.commons.ssl.KeyStoreBuilder to build a keystore from the p12 file. The password is used both for decryption of private key and encryption of the newly created Java key store. 
java -cp commons-ssl.jar org.apache.commons.ssl.KeyStoreBuilder password cert.p12

Now you can check the content of the file-based key store: keytool -keystore newkeystore.jks -list

Load the file-base key store into the card

keytool -keystore NONE -storetype PKCS11 -providerName SunPKCS11-OpenSC-PKCS11 -importkeystore -srckeystore newkeystore.jks

Check the content of the on-card key store.
keytool -keystore NONE -storetype PKCS11 -providerName SunPKCS11-OpenSC-PKCS11 -list

Signing and Verification with the On-Card Certificate

All the error handling was intentionally removed to make following example shorter. I also skipped creation of PKCS7 signed messages - you can you Bouncy Castle CMSSignedDataGenerator for that easily.

import java.security.*;
import java.security.cert.*;
import java.security.cert.Certificate;
import java.io.*;

...

// loading the key from file:
KeyStore keyStore = KeyStore.getInstance("JCEKS");
FileInputStream inputStream = new FileInputStream(storeFileName);
keyStore.load(inputStream, storePassword.toCharArray());
KeyStore.ProtectionParameter protectParameter = new KeyStore.PasswordProtection(certPass.toCharArray()); }

// loading the key from token:
KeyStore keyStore = KeyStore.getInstance("PKCS11");
KeyStore.ProtectionParameter protectParameter = null;
keyStore.load(null, storePassword.toCharArray());

// the rest does not depend on the type of the store: 
String signatureAlgorithmName = "SHA1withRSA";
KeyStore.Entry entry = keyStore.getEntry(alias, protectParam);
boolean isPrivateKeyEntry = keyStore.entryInstanceOf(alias, KeyStore.PrivateKeyEntry.class);
if (isPrivateKeyEntry)
{
  Signature signatureAlgorithm = Signature.getInstance(signatureAlgorithmName);

  // signing
  KeyStore.PrivateKeyEntry privateKeyEntry = (KeyStore.PrivateKeyEntry)entry;
  PrivateKey privateKey = privateKeyEntry.getPrivateKey();  
  signatureAlgorithm.initSign (privateKey);
  signatureAlgorithm.update (message);
  byte[] signature = signatureAlgorithm.sign();

  // verification
  Certificate[] chain = privateKeyEntry.getCertificateChain();
  X509Certificate certificate = (X509Certificate) chain[chain.length-1];
  PublicKey publicKey = certificate.getPublicKey();
  signatureAlgorithm.initVerify(publicKey);
  signatureAlgorithm.update (data);
  boolean verified = signatureAlgorithm.verify(signature);
}

Maven: Creating Executable JAR with Shade Plugin

I was pleasantly surprised by versatility of Maven shade plugin. Creating a custom-named executable JAR and bundle only the dependencies you really need is a piece of cake. The documentation is partly is online, partly in the plugin's help and provides all necessary information.

<plugin>
  <groupId>org.apache.maven.plugins</groupId>
  <artifactId>maven-shade-plugin</artifactId>
  <version>1.5</version>
  <executions>
     <execution>
       <phase>package</phase>
       <goals><goal>shade</goal></goals>
       <configuration>
         <outputFile>target/tool.jar</outputFile>
         <artifactSet>
           <includes>
              <include>org.springframework:spring-core</include>
              <include>org.springframework:spring-beans</include>
           </includes>
         </artifactSet>
         <transformers>
           <transformer 
             implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
             <mainClass>org.bithill.example.Tool</mainClass>
           </transformer>
         </transformers>
       </configuration>
     </execution>
  </executions>
</plugin>