Tag: jdbc

How to read and write CLOB fields

The Character Large Object (or CLOB) is a commonly found in databases and used to store high quantity of characters.

At MySQL, for example, this field is called MEMO.

Writing CLOB field

The method setAsciiStream of PreparedStatement allow to pass data to a CLOB.

ps.setAsciiStream(bindPosition, inputStream, textLength);

bindPosition – Position in PreparedStatment’s CLOB field.
inputStream – Used to pass data.
textLength – Data (text) length.

Full code:

String sql = "INSERT INTO TABLE (text) VALUES(?)";
		try{
			String txt = readTxtFile();
			ByteArrayInputStream bais = new ByteArrayInputStream(txt.getBytes());

			PreparedStatement ps = conexao.prepareStatement(sql);
			//CLOB is '?' at first position
			ps.setAsciiStream(1, bais, txt.length());

			ps.execute();

			ps.close();
		}catch (Exception e) {
			e.printStackTrace();
		}

Retrieving CLOB data

We’re using SELECT clause, this clause returns a ResultSet to fetch data and, calling getClob method, passing column name or your position in the query, it give to you a Clob object.

rs.getClob("xml");

Full code:

String sql = "SELECT xml FROM TEST";
		try{
			PreparedStatement ps = conexao.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();

			while(rs.next()){
				Clob clob = rs.getClob("xml");
				BufferedReader reader = new BufferedReader(clob.getCharacterStream());
				StringBuffer strBuf = new StringBuffer();

				String linha = null;
				while((linha = reader.readLine()) != null){
					strBuf.append(linha);
//Character.LINE_SEPARATOR insert break line
					strBuf.append((char)Character.LINE_SEPARATOR);
				}

				System.out.println("=========== CLOB ===========");
				System.out.println(strBuf.toString());
			}

			rs.close();
			ps.close();
		}catch (Exception e) {
			e.printStackTrace();
		}

That’s it! Easy and painless 😉

Download full code here

To write a BLOB see this tutorial.


Saving files in BLOB table column of a data base

To insert a file, is it in any format, you need call the method setBinaryStream, implemented by PreparedStatement.

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

In sample, we set a table called FILEthat contains BLOB column called BIN.

//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();

I used Oracle 8i to execute this sample. I haven’t a MySQL/PostgreSQL/MS SQL Server in my dispose, then you’ll responsible for testing in this data bases and send me the results, OK 😉

Thanks! Until next time!


Retrieving objects collection from Oracle procedure

As I’ve promised in post “Learn to pass a Java Object as Oracle Procedure parameter“, I’ll show how retrieve object that have a collection of objects as attribute through of an Oracle procedure. Is highly recommended to read previous post.

For this tutorial, we’ll need create the table TBL_CLASS and add your primary key as foreign key in TBL_USER table.

--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);

Now we need to include the new types:

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;
/

The class_type type will be the Java Object. Notice that in your signature was included the arr_users type, that will be our collection of user_type (read previous post for more information), the arr_class type will be the class_type collection.

Now we’ll include the procedure that returns our class_type collection.

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;

Notice that procedure receive class_type as parameter in and returns arr_class type.

Separating code charge back and set up our objects, we have:

--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;

Oracle objects done, now the Java code!

We’ll create the object that will be interpreted by the Oracle. Called TypeClass:

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
}

We need to map types interpreted in request, this way:

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

We need to map the arrays too:

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

For request, we do:

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");
	}
}

In the end you’ll have a java.sql.Array of TypeUser in getUsers() attribute of TypeClass.

Here I fulfilled my promise. Download the source code of this sample (with previous post sample too) here.

Until next time!


Learn to pass a Java Object as Oracle Procedure parameter

In the enterprise where I work was a discussion about possibility to pass a Java objects into an Oracle procure or function, then I looked for and here is a simple solution to reach this objective.

This tutorial only work with Oracle9i , or above, and using the JDBC driver ojdbc14g, or above.

First, we create the tables, objects and procedures. Remember: the types tbl_users and user_type must be declared out of packages, as global types:

-- 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;
/

Creating specification and body of package.

--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;
/

See CAST and MULTISET about how they work.

Built database objects, we need prepare the JavaBean. It’ll an implementation of java.sql.SQLData, because it will be necessary to implement the methods:

getSQLTypeName() – Getter used to obtain the name of type.

readSQL(SQLInput, String) – Used to convert an object in Java object.

writeSQL(SQLOutput stream) – Used to mount a SQL object, used byJDBC Driver.

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
}

To define a type that can will be send to procedure be necessary add him  into type map through Connection.getTypeMap(). This method return the Map<String,Class<?>>, where type name is the key and class of SQLData implementation is the value, in own case the type TypeUser. Sample:

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

Then the connection will be:

//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);

Now, we create the insert method, it receive an instance of TypeUser and Connection:

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();
}

Now, the select method. This method return a object array, then be necessary insert the type of array in Connection TypeMap. The name passed as key should be equals of Oracle’s array name and the values will be the array class, like this:

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

To call procedure and registerOutParameter:

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();

To obtain the array, do it:

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

If until here is alright, smile, to obtain array values just iterate him and cast each index to Type User.

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()));
}

So we can pass and retrieve simple Java objects of a Oracle procedure or function. The next step, Retrieving objects collection from Oracle procedure.

See ya!

Download the source code of this tutorial here

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

  • Advertisement

  • Advertisement

  • Copyright © 1996-2010 André L. S.. All rights reserved.
    iDream theme by Templates Next | Powered by WordPress