Saving Java objects into database and read it back from database using JDBC
Question:- How we can write binary objects into a database table, for that what type of data type should we use for the creation of database tables?
To Map an object with relational databases and vice versa is always a difficult task.
Very good solutions for that are serialize each Java object using the object stream and preserve the result into a database as a binary blob. As this is a valid scenario and the JDBC explicitly supports to perform this.
Questions: - What is Blob data type?
Answer:-
Binary large object or basic large object: - Large object data types store data ranging in size from 0 bytes to 2 GB. A BLOB is a collection of binary data stored as a single entity in a database. This data type can store binary data larger than VARBINARY (32K limit). Blobs are typically any objects, images, audio, other multimedia objects or other types of business or application-specific data. A BLOB is a varying-length binary string that can be up to 2,147,483,647 characters long.
For storing java objects into the database we can use BLOB datatype in the table.We can create a table using below CREATE TABLE command in MYSQL database.
Table Structure for MySql Database:-
CREATE TABLE persist_java_objects (
System Requirements:-
jdk1.5 and above(I am using jdk1.7)
Eclipse Editor or other(I am using Eclipse Helios).
Required Jars:-
mysql-connector-java-5.0.4-bin.jar
CREATE TABLE persist_java_objects (
object_id int(14) NOT NULL auto_increment,
object_name varchar(30) default NULL,
java_object blob,
PRIMARY KEY (object_id)
)
System Requirements:-
jdk1.5 and above(I am using jdk1.7)
Eclipse Editor or other(I am using Eclipse Helios).
Required Jars:-
mysql-connector-java-5.0.4-bin.jar
Sample Example to save a List of Objects into database and read back from database :-
SaveObject2Database.java
packagecom.gaurav.persistjavaobjects;
importjava.io.ByteArrayInputStream;
importjava.io.ByteArrayOutputStream;
importjava.io.IOException;
importjava.io.ObjectInputStream;
importjava.io.ObjectOutputStream;
importjava.sql.Blob;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.util.ArrayList;
importjava.util.Date;
importjava.util.List;
publicclass SaveObject2Database {
/** This method will help to get mysql connection from database*/
private static Connection getConnection() throws Exception {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "root";
Class.forName(driver);
Connection con = DriverManager.getConnection(url, username, password);
return con;
}
/** This method will help to convert any object into byte array*/
private static byte[] convertObjectToByteArray(Object obj) throws IOException {
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
ObjectOutputStream objectOutputStream = new ObjectOutputStream(byteArrayOutputStream);
objectOutputStream.writeObject(obj);
returnbyteArrayOutputStream.toByteArray();
}
/** This method will help to save java objects into database*/
private static long saveBlob(Connection con, Object javaObject2Persist) {
byte[] byteArray = null;
PreparedStatement preparedStatement = null;
String SQLQUERY_TO_SAVE_JAVAOBJECT = "INSERT INTO persist_java_objects(object_name, java_object) VALUES (?, ?)";
int persistObjectID = -1;
try {
byteArray = convertObjectToByteArray(javaObject2Persist);
preparedStatement = con.prepareStatement(
SQLQUERY_TO_SAVE_JAVAOBJECT,
PreparedStatement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1, javaObject2Persist.getClass()
.getName());
preparedStatement.setBytes(2, byteArray);
preparedStatement.executeUpdate();
System.out
.println("Query - "
+ SQLQUERY_TO_SAVE_JAVAOBJECT
+ " is successfully executed for Java object serialization ");
//Trying to get the Generated Key
ResultSet rs = preparedStatement.getGeneratedKeys();
if (rs.next()) {
persistObjectID = rs.getInt(1);
System.out
.println("Object ID while saving the binary object is->"
+ persistObjectID);
}
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return persistObjectID;
}
/** This method will help to read java objects from database*/
private static byte[] getBlob(Connection con, long objectId) {
String SQLQUERY_TO_READ_JAVAOBJECT= "SELECT java_object FROM persist_java_objects WHERE object_id = ?;";
PreparedStatement pstmt = null;
ResultSet resultSet = null;
Blob blob = null;
byte[] bytes = null;
try {
pstmt = con.prepareStatement(SQLQUERY_TO_READ_JAVAOBJECT);
System.out.println("Reading the saved Object from the database where the object Id is:->" + objectId);
pstmt.setLong(1, objectId);
resultSet = pstmt.executeQuery();
while (resultSet.next()) {
blob = resultSet.getBlob(1);
}
bytes = blob.getBytes(1, (int) (blob.length()));
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return bytes;
}
@SuppressWarnings("unchecked")
public static void main(String args[]) throws Exception {
Connection connection = null;
byte[] retrievedArrayObject = null;
try {
connection = getConnection();
List<Object> listToSaveInDB = new ArrayList<Object>();
listToSaveInDB.add(new Date());
listToSaveInDB.add(new String("KUMAR GAURAV"));
listToSaveInDB.add(new Integer(55));
long persistObjectID = saveBlob(connection, listToSaveInDB);
System.out.println(listToSaveInDB + " Object is saved sucessfully");
retrievedArrayObject = getBlob(connection, persistObjectID);
ObjectInputStream objectInputStream = null;
if (retrievedArrayObject != null)
objectInputStream = new ObjectInputStream(
new ByteArrayInputStream(retrievedArrayObject));
Object retrievingObject = objectInputStream.readObject();
List<Object> dataListFromDB = (List<Object>) retrievingObject;
for (Object object : dataListFromDB) {
System.out.println("Retrieved Data is :->" + object.toString());
}
System.out
.println("Successfully retrieved java Object from Database");
} catch (Exception e) {
e.printStackTrace();
} finally {
connection.close();
}
}
}
Result:->
Query - INSERT INTO persist_java_objects(object_name, java_object) VALUES (?, ?) is successfully executed for Java object serialization
Object ID while saving the binary object is->13
[Fri Aug 02 07:17:26 IST 2013, KUMAR GAURAV, 10055] Object is saved sucessfully
Reading the saved Object from the database where the object Id is:->13
Retrieved Data is :->Fri Aug 02 07:17:26 IST 2013
Retrieved Data is :->KUMAR GAURAV
Retrieved Data is :->10055
Successfully retrieved java Object from Database
Object ID while saving the binary object is->13
[Fri Aug 02 07:17:26 IST 2013, KUMAR GAURAV, 10055] Object is saved sucessfully
Reading the saved Object from the database where the object Id is:->13
Retrieved Data is :->Fri Aug 02 07:17:26 IST 2013
Retrieved Data is :->KUMAR GAURAV
Retrieved Data is :->10055
Successfully retrieved java Object from Database
Table Status after insertion java objects into database:-
No comments:
Post a Comment