Smart Card Communication-APDU Format

Java Smart Cards are different in sense that there is an on card virtual machine which resides in the card chip that is actually responsible for processing the instructions. The instructions to the card are sent by a host application. The host application may be a server side application or a desktop application or another smart card applet. 
Applets are applications that reside on the smart card. The host application and the smart cards communicate in a specific sequence. The host application sends commands to the applets and the applets sends back a response. 
This communication takes place with the help of Application Protocol Data Unit or APDU. The APDU is basically divided into two parts namely: 

• Command APDU: Sent by the host application to the Java Card. The structure of the same is displayed below: 

  Mandatory Header				Optional Body
  CLA	INS	P1	P2	Lc	Data Field	Le

• Response APDU: Response sent by the Java Card applet to the host application. The structure of the same is displayed below: 

  Optional Body	Mandatory Trailer
  Data Field	SW1	SW2

Using oracle.sql.ARRAY with Spring JdbcTemplate

Many a times, you may be required to do bulk insert of data into Oracle tables. For this purpose, you might use oracle.sql.ARRAY to pass the bulk data. Many of us know how to do this using native JDBC, but here's my example for those using Spring framework's JdbcTemplate.

We have two master tables namely:
EMP (employee master) and PROJECTS (employee's projects). The structure of the same is as follows:

EMP_ID	EMP_NAME
1	Jack
2	Jim

PRJ_ID	PRJ_NAME
I001	Inventory
B0021	BFSI

As you can see the relation between EMP and Projects is many to many. So we have one more table namely EMP_PROJECTS. The structure of the same is as follows:

EMP_ID	PRJ_ID
1	I001
1	B0021

To insert multiple bulk values in the EMP_PROJECTS table, we might consider using oracle.sql.ARRAY. So we declare a test array object as follows:

CREATE OR REPLACE TYPE TEST_ARR AS TABLE OF VARCHAR2(1000);

Now we have a stored procedure that uses the above mentioned type to insert data into the EMP_PROJECTS table as follows:

CREATE OR REPLACE PROCEDURE PRC_INS_EMP_PRJ(EMP_ID IN VARCHAR2, PRJ_ARR IN TEST_ARR)
AS
BEGIN
INSERT INTO EMP_PROJECTS (EMP_ID, PRJ_ID) VALUES (SELECT EMP_ID, COLUMN_VALUE FROM FROM TABLE (PRJ_ARR));
END;

In the above procedure, we can insert multiple project id's against a single employee id. To call the above procedure using Spring JdbcTemplate, we first need to set the SQL parameters as follows:

SqlParameter inEmpId = new SqlParameter(Types.VARCHAR);
SqlParameter inPProjectIdArray = new SqlParameter(Types.ARRAY);

List<SqlParameter> sqlParameterList = new ArrayList<SqlParameter>();
sqlParameterList.add(inEmpId);
sqlParameterList.add(inPProjectIdArray);

List<String> projectIdList = new ArrayList<String>();
projectIdList.add("1001");
projectIdList.add("B0021");

The code for calling the above procedure is as follows:

Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {
                   
                    @Override
                    public CallableStatement createCallableStatement(Connection connection)
                            throws SQLException {
                       
                        ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(TEST_ARR, connection);
                        ARRAY inputArray = new ARRAY(arrayDescriptor, connection, projectIdList.toArray());
                       
                        CallableStatement cs = connection.prepareCall(sqlQuery);
                        cs.setString(1, empId);
                        cs.setString(2, inputArray);                       
                        return cs;
                    }
                }, sqlParameterList);

The above post is one of the ways to use oracle.sql.ARRAY in your JdbcTemplate code.