André L. S. Softwares Development, Technology and Games

PreparedStatemente.setBinaryStream(int index, Inputstream is, int length);

//Normal connection, as any JDBC connection
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@<IP>:<PORT>:<SID>","<USER>","<PASSWORD>");

//Reading the file and retrieving an InputStream
File file= new File("<COMPLETE_FILE_PATH>");
FileInputStream fis = new FileInputStream(file);

//Preparing statement
PreparedStatement ps = conn.prepareStatement("INSERT INTO FILE(bin) VALUES(?)");

//Passing InputStream and file length
ps.setBinaryStream(1, fis, (int)file.length());

ps.execute();

ps.close();
conn.close();

  --Creating the REF CURSOR type
  type g_cursor is ref cursor;

  procedure PRO_RETURN_CARS(
    i_id     in     tbl_car.car_id%type,
    o_cursor in out g_cursor);

open o_cursor for
          select car_id, company, model, color, hp, price
          from tbl_car
          where car_id = i_id;

create or replace package PAC_CURSOR is
  --Creating REF CURSOR type
  type g_cursor is ref cursor;

  --Procedure that return the cursor
  procedure PRO_RETURN_CARS(
    i_id     in     tbl_car.car_id%type,
    o_cursor in out g_cursor); -- Our cursor

end PAC_CURSOR;
/

create or replace package body PAC_CURSOR is
  procedure PRO_RETURN_CARS(
    i_id     in     tbl_car.car_id%type,
    o_cursor in out g_cursor) is

       begin
        --Opening the cursor to return matched rows
        open o_cursor for
          select car_id, company, model, color, hp, price
          from tbl_car
          where car_id = i_id;

  end PRO_RETURN_CARS;

end PAC_CURSOR;

CallableStatement cs = conn.prepareCall("{call PAC_CURSOR.PRO_RETURN_CARS(?,?)}");

//Calling Oracle procedure
CallableStatement cs = conn.prepareCall("{call PAC_CURSOR.PRO_RETURN_CARS(?,?)}");

//Defining type of return
cs.registerOutParameter("o_cursor", OracleTypes.CURSOR);
cs.setLong("i_id", id);

cs.execute();//Running the call

//Retrieving the cursor as ResultSet
ResultSet rs = (ResultSet)cs.getObject("o_cursor");

//Iterating the returned rows
while(rs.next()){
	//Getting column values
	System.out.println("ID: " + rs.getLong("car_id"));
	System.out.println("Manufacturer: " + rs.getString("company"));
	System.out.println("Model: " + rs.getString("model"));
	System.out.println("Color: " + rs.getString("color"));
	System.out.println("HP: " + rs.getString("hp"));
	System.out.println("Price: " + rs.getFloat("price"));
}

--num class is PK and desc_class description
create table TBL_CLASS (num_class number, desc_class varchar(100));
alter table TBL_CLASS add primary key(num_class);

alter table TBL_USER add num_class number;
alter table TBL_USER add constraint FK_CLASS foreign key(num_class) references tbl_class(num_class);

create or replace type class_type as object (num_class number, desc_class varchar2(100), users arr_users);
/
create or replace type arr_class as table of class_type;
/

procedure pro_select_class(clas in class_type, class_return in out arr_class)is
  class_ref_cur ref_cur;
  --class_type array
  classes arr_class := arr_class();

  begin
    open class_ref_cur for
      select cast(
                multiset(
                  select num_class,
                         desc_class,
                         (select cast(
                                  multiset(
                                    select user_name,
                                           height,
                                           b_date
                                    from tbl_user
                                    --JOIN with TBL_USER
                                    where tbl_user.num_class = tbl_class.num_class
                                  ) as arr_users)
                          from dual) users
                  from tbl_class
                  --Using num_class attribute of in parameter
                  where num_class = clas.num_class) as arr_class
      ) classes
    from dual;

    --including the return in array
    fetch class_ref_cur into classes;
    --transferring arrar to variable out
    class_return := classes;
end pro_select_class;

--Mount return
select cast(
        multiset(

          --Will returns the objects class_type and your attributes
          select num_class,
                 desc_class,

                 --Populate user_type collection
                 (select cast(
                          multiset(
                            select user_name,
                                   height,
                                   b_date
                            from tbl_user
                            where tbl_user.num_class = tbl_class.num_class
                          ) as arr_users)

                  from dual) users

          from tbl_class
          where num_class = clas.num_class) as arr_class
) classes
from dual;

public class TypeClass implements SQLData{
	public static final String ORACLE_OBJECT_NAME = "CLASS_TYPE"; //Type name in Oracle
	public static final String ORACLE_CLASS_ARRAY_NAME = "ARR_CLASS"; //Array name in Oracle

        //Attibutes of TBL_CLASS table
	private Long number;
	private String desc;
	private Array users; //This will be user_type collection (or TypeUser in Java)

	public String getSQLTypeName() throws SQLException {
		return ORACLE_OBJECT_NAME;
	}

	public void readSQL(SQLInput stream, String typeName) throws SQLException {
		setNumber(stream.readLong());
		setDesc(stream.readString());
		setUsers(stream.readArray());//Used by JDBC driver to read the collection
	}

	public void writeSQL(SQLOutput stream) throws SQLException {
		stream.writeLong(getNumber());
		stream.writeString(getDesc());
		stream.writeArray(getUsers());//Used by JDBC driver to write the collection
	}
	//Getters and setters omitted
}

Map> typeMaps = connection.getTypeMap();
typeMaps.put(TypeUser.ORACLE_OBJECT_NAME, TypeUser.class);
typeMaps.put(TypeClass.ORACLE_OBJECT_NAME, TypeClass.class);

typeMaps.put(TypeClass.ORACLE_CLASS_ARRAY_NAME, TypeClass[].class);//returned by procedure
typeMaps.put(TypeUser.ORACLE_USER_ARRAY_NAME, TypeUser[].class);//returned by class_type collection

cs = conn.prepareCall("{call PAC_BEAN.PRO_SELECT_CLASS(?,?)}");
//registering out type, that will be a TypeClass array
cs.registerOutParameter("class_return", OracleTypes.ARRAY, TypeClass.ORACLE_CLASS_ARRAY_NAME);

//passing parameter object
cs.setObject("clas", classQry);

cs.execute();
//retrieving and looping the TypeClass array
Object[] array = (Object[])cs.getArray("class_return").getArray();

for(Object obj : array){
	TypeClass objClass = ((TypeClass)obj);

	System.out.println("Description: "+objClass.getDesc());

        //Here we obtains user_type(TypeUser) array returned by query.
	Object[] userArray = (Object[])objClass.getUsers().getArray();
	for(Object user : userArray){
		System.out.println("\tName: " + ((TypeUser)user).getName());
		System.out.println("\tHeight: " + ((TypeUser)user).getHeight());
		System.out.println("\tBirth: " + sdf.format(((TypeUser)user).getBirth())+ "\r\n");
	}
}

iBatis tutorial, learning the basic

When we talk about the persistence framework, we think in Hibernate/JPA. Recently I was presented to iBatis, a framework that so easy to install, to configure and to use. You can download it in your sponsor site, Apache, clicking here.

Setting iBatis

Unlike another frameworks, to configure iBatis you need only one XML file, called SqlMapConfig.

The mains sections of XML are:

<properties resource="tuto/ibatis/config/SqlMap.properties"/>

This code is optional and specifies the .properties file that'll be used to declare variables used in configuration.

<typeAlias alias="car" type="tuto.ibatis.beans.Car"/>

Defines the JavaBean used and your alias. You can set much lines, depending of modeling complexity.
In example, we'll Car bean below:

public class Car {
	private Long carId;
	private String company;
	private String model;
	private String color;
	private Integer	hp;
	private Float price;

	//Setters and getters omitted
}

<transactionManager type="JDBC">
    <dataSource type="SIMPLE">
        <property name="JDBC.Driver" value="${driver}"/>
        <property name="JDBC.ConnectionURL" value="${url}"/>
        <property name="JDBC.Username" value="${username}"/>
        <property name="JDBC.Password" value="${password}"/>
    </dataSource>
</transactionManager>

Parameters used in database connection. The variables ${driver}, ${url}, ${username} and ${password} are defined in .properties file in section properties. If you prefer, can put the values directly in fields.

See the complete file:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
        "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
    <properties resource="tuto/ibatis/config/SqlMap.properties"/>

    <settings
        cacheModelsEnabled="true"
        enhancementEnabled="true"
        lazyLoadingEnabled="true"
        maxRequests="32"
        maxSessions="10"
        maxTransactions="5"
        useStatementNamespaces="false" />

    <typeAlias alias="car" type="tuto.ibatis.beans.Car"/>

    <transactionManager type="JDBC">
        <dataSource type="SIMPLE">
            <property name="JDBC.Driver" value="${driver}"/>
            <property name="JDBC.ConnectionURL" value="${url}"/>
            <property name="JDBC.Username" value="${username}"/>
            <property name="JDBC.Password" value="${password}"/>
        </dataSource>
    </transactionManager>

    <sqlMap resource="tuto/ibatis/sqlmaps/CarSqlMap.xml"/>
</sqlMapConfig>

The properties have this content:

driver=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@<host>:<porta>:<sid>
username=<login>
password=<senha>

Next you need to configure the SqlMap. This XML contains the querys used in application and your name need be equals described in sqlMap section of SqlMapConfig, in our case will be CarSqlMap.xml

In example only we will see utilization of tags select, insert, update and delete.

<select id="getCars" resultClass="tuto.ibatis.beans.Car"
	parameterClass="java.lang.Long">
    SELECT COMPANY  as company,
           MODEL    as model,
           COLOR    as color,
           HP       as hp,
           PRICE    as price
    FROM TBL_CAR
    WHERE CAR_ID = #var#
</select>

Execute the select statement can return a single object or one collection of objects, the type is same of resultClass attribute, o parameterClass is the type sent to execute the query and the id is the query identification call.

We will use the SqlMap below:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
	"http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Car">
    <select id="getCars" resultClass="tuto.ibatis.beans.Car"
    parameterClass="java.lang.Long">
        SELECT COMPANY  as company,
               MODEL    as model,
               COLOR    as color,
               HP       as hp,
               PRICE    as price
        FROM TBL_CAR
        WHERE CAR_ID = #var#
    </select>

    <insert id="addCar" parameterClass="tuto.ibatis.beans.Car">
        INSERT INTO TBL_CAR (CAR_ID, COMPANY, MODEL, COLOR, HP, PRICE)
        VALUES (#carId#, #company#, #model#, #color#, #hp#, #price#)
    </insert>

    <delete id="delCar" parameterClass="java.lang.Long">
        DELETE FROM TBL_CAR WHERE CAR_ID = #var#
    </delete>

    <update id="updCar" parameterClass="tuto.ibatis.beans.Car">
        UPDATE TBL_CAR
          SET COMPANY = #company#,
              MODEL = #model#,
              COLOR = #color#,
              HP = #hp#,
              PRICE = #price#
        WHERE CAR_ID = #carId#
    </update>
</sqlMap>

Data base connection configured, now we will implements the singleton class the will used as SqlMapClient, called OracleMapConfig.

package tuto.ibatis.connection;

import java.io.Reader;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

public class OracleMapConfig {
	private static final SqlMapClient sqlMapClient;

	static{
		try{
			//Defining path of SqlMapConfig and creating reader
			String res = "tuto/ibatis/config/SqlMapConfig.xml";
			Reader reader = Resources.getResourceAsReader(res);

			//Retrieving the client to SqlMap
			sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
		} catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

	//Method used to retrieve the client
	public static SqlMapClient getSqlMapClient(){
		return sqlMapClient;
	}
}

The client is responsible for execute the querys configured in SqlMap and return the results.

Executing Querys and treating the return

To call any query is too simple, only execute the correspondent method of client class.

The select can be called this way:

OracleMapConfig.getSqlMapClient().queryForObject("<id>", <parâmetro>);

Theid need be equals of id specified in SqlMap

Only one line is return in code above, to get all lines change to queryForList, this way:

OracleMapConfig.getSqlMapClient().queryForList("<id>", <parâmetro>);

Will be returned a Collection containing the objects;

Select

try{
	Car car = (Car)OracleMapConfig.getSqlMapClient().queryForObject("getCars",
		new Long(readKeyboard()));

	System.out.println("Company: "+car.getCompany());
	System.out.println("Model: "+car.getModel());
	System.out.println("Color: "+car.getColor());
	System.out.println("HP: "+car.getHp());
	System.out.println("Price: "+car.getPrice());
}catch (Exception e) {
	e.printStackTrace();
}

The id "getCars" are defined in select attributes of SqlMap, providing one Long type and retrieving a Car type, both defined in line <select id="getCars" resultClass="tuto.ibatis.beans.Car" parameterClass="java.lang.Long">.

Insert

try{
	OracleMapConfig.getSqlMapClient().insert("addCar", newCar);
}catch (Exception e) {
	e.printStackTrace();
}

Now we will provide as parameter a Car type, <insert id="addCar" parameterClass="tuto.ibatis.beans.Car">, and call the methods using sharp (#), this way:

INSERT INTO TBL_CAR (CAR_ID, COMPANY, MODEL, COLOR, HP, PRICE)
VALUES (#carId#, #company#, #model#, #color#, #hp#, #price#)

Delete

try{
	int lines = OracleMapConfig.getSqlMapClient().delete("delCar",
		new Long(readKeyboard()));

	System.out.println(lines + " lines deleted");
}catch (Exception e) {
	e.printStackTrace();
}

The method delete of client return a type int, this represents the number of rows deleted.

Update

try{
	int lines = OracleMapConfig.getSqlMapClient().update("updCar", car);

	System.out.println(lines + " cars updated");
}catch (Exception e) {
	e.printStackTrace();
}

Update return a type int, this represents the number of rows affected by update.

How you see, with only three XML and three classes we built a simple storage management and price consulting system.

You can download the source code of this tutorial clicking here.

Until next post!

-- Creating table
create table tbl_user(user_name varchar2(100), height number, b_date date);
/
--Creating type user_type (own bean)
create or replace type user_type as object (user_name varchar2(100), height number, birth_date date);
/
--Creating type arr_users, table of user_type (array of user_type)
create or replace type arr_users as table of user_type;
/

--Spec
create or replace package PAC_BEAN is
  type ref_cur is ref cursor;

  -- Procedure used to insert values
  procedure pro_insert_user(usu in user_type);

  -- Procedure used to select
  procedure pro_select_user(usu in user_type, user_return in out arr_users);
end PAC_BEAN;
/

--Body
create or replace package body PAC_BEAN is
  --The insert procedure will receive user_type and put him into table tbl_user.
  procedure pro_insert_user(usu in user_type) is
    begin
      insert into tbl_user (user_name, height, b_date)
      values (usu.user_name, usu.height, usu.birth_date);

      commit;
    exception
      when others then
        rollback;
  end pro_insert_user;

  --The procedure used for select will receive a user_type (where clause) and will return the array arr_users
  procedure pro_select_user(usu in user_type, user_return in out arr_users)is
    user_ref_cur ref_cur;

    --Instancing the array
    users arr_users := arr_users();

    begin
      --Opening the cursor that will return the array
      open user_ref_cur for
        select cast(
                 multiset(
                   select user_name,
                          height,
                          b_date
                   from tbl_user
                   where user_name like '%'||usu.user_name||'%'
                 ) as arr_users
              ) arr
        from dual;

      --Putting the cursor into arr_users instance.
      fetch user_ref_cur into users;

      --Returning the instance through OUT variable
      user_return := users;
  end pro_select_user;
end PAC_BEAN;
/

public class TypeUser implements SQLData{
	//Name declared in Oracle
	public static final String ORACLE_OBJECT_NAME = "USER_TYPE";
	//Array name declared in Oracle
	public static final String ORACLE_USER_ARRAY_NAME = "ARR_USERS";

	//The attributes
	private String name;
	private Float height;
	private Date birth;

	public TypeUser() {
		height = 0F;
	}

	public String getSQLTypeName() throws SQLException {
		return ORACLE_OBJECT_NAME;
	}

	public void readSQL(SQLInput stream, String typeName) throws SQLException {
		setName(stream.readString());
		setHeight(stream.readFloat());
		setBirth(stream.readDate());
	}

	public void writeSQL(SQLOutput stream) throws SQLException {
		stream.writeString(getName());
		stream.writeFloat(getHeight());
		stream.writeDate(getBirth() != null ?
				new java.sql.Date(getBirth().getTime()) : null);
	}

        //getters and setters omitted
}

Map<String,Class<?>> typeMaps = connection.getTypeMap();
typeMaps.put(TypeUser.ORACLE_OBJECT_NAME, TypeUser.class);

//Making connection
Class.forName("oracle.jdbc.driver.OracleDriver");
connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:<host>:<port>:<db>","<user>","<pass>");

//Mapping necessary tipes
Map<String,Class<?>> typeMaps = connection.getTypeMap();
typeMaps.put(TypeUser.ORACLE_OBJECT_NAME, TypeUser.class);

CallableStatement cs = null;
try {
	//call the procedure
	cs = conn.prepareCall("{call PAC_BEAN.PRO_INSERT_USER(?)}");

	//defining the instance of TypeUser as variable IN "usu"
	cs.setObject("usu", typeUser);

	cs.execute();
} catch (SQLException e) {
	e.printStackTrace();
}

connection.getTypeMap().put(TypeUser.ORACLE_USER_ARRAY_NAME, TypeUser[].class);

cs = conn.prepareCall("{call PAC_BEAN.PRO_SELECT_USER(?,?)}");
cs.registerOutParameter("user_return", OracleTypes.ARRAY, TypeUser.ORACLE_USER_ARRAY_NAME);
cs.setObject("usu", typeUserQry);

cs.execute();

//user_return is the OUT variable name
Object[] array = (Object[])cs.getArray("user_return").getArray();

for(Object obj : array){
	System.out.println("Nome: " + ((TypeUser)obj).getName());
	System.out.println("Altura: " + ((TypeUser)obj).getHeight());
	System.out.println("Data de Nascimento: " + sdf.format(((TypeUser)obj).getBirth()));
}

<host>:<porta>:<bd>","<usuario>","<senha>"