Store and retrieve an object from a tableTag(s): JDBC


Assume that we have a table called "Employee", with only one field called "emp" and "long binary" or "blob" as data type. We want to insert an instance of the class "Employee" into the table "Employee". The object "Employee" will be serialized into the table "Employee" ( ideally the table "Employee" should have an index key, something like emp_id, but for the example there is none).
import java.net.URL;
import java.sql.*;
import java.io.*;

class JDBCapp  {
  static MyConnection theConn;

  public static void main (String args[]) {
    JDBCapp j = new JDBCapp();
    j.connectToDB();
    j.insertIntoEmp();
    j.selectFromEmp();
    }               

  public void connectToDB() {
    theConn = new MyConnection();
    theConn.connect("your_db_profile", "username", "password");
    }

  public void insertIntoEmp() {
    Employee employee = new Employee(42,"Real Gagnon", 98000);
    try {
      ByteArrayOutputStream baos = new ByteArrayOutputStream();
      ObjectOutputStream oos = new ObjectOutputStream(baos);
      oos.writeObject(employee);
      // serialize the employee object into a byte array
      byte[] employeeAsBytes = baos.toByteArray();
      PreparedStatement pstmt = 
         theConn.dbConn.prepareStatement
            ("INSERT INTO EMPLOYEE (emp) VALUES(?)");
      ByteArrayInputStream bais = 
         new ByteArrayInputStream(employeeAsBytes);
      // bind our byte array  to the emp column
      pstmt.setBinaryStream(1,bais, employeeAsBytes.length);
      pstmt.executeUpdate();
      theConn.dbConn.commit(); 
      pstmt.close();
      }
    catch(Exception e) {
      e.printStackTrace();
      }
    }  

  public void selectFromEmp() {
    boolean found;
    try {
      Statement stmt = theConn.dbConn.createStatement();
      ResultSet rs = 
         stmt.executeQuery("SELECT emp FROM Employe");
      // loop through the result set
      while (rs.next()) {
         // fetch the serialized object to a byte array
         byte[] st = (byte[])rs.getObject(1);
             //   or  byte[] st = rs.getBytes(1);
             //   or  Blob aBlob = rs.getBlob(1);
             //       byte[] st = aBlob.getBytes(0, (int) aBlob.length());
         ByteArrayInputStream baip = 
             new ByteArrayInputStream(st);
         ObjectInputStream ois = 
             new ObjectInputStream(baip);
         // re-create the object
         Employee emp = (Employee)ois.readObject();
         // display the result for demonstration
         System.out.println(emp.name);
         }
      stmt.close();
      rs.close();
      }
    catch(Exception e) {
      e.printStackTrace();
      }
    }
  }

class Employee implements Serializable {
  int ID;
  String name;
  double salary;
 
  public Employee(int ID, String name, double salary) {
    this.ID = ID;
    this.name = name;
    this.salary = salary;
    }
  }

class MyConnection {
   Connection dbConn = null;
   void connect(String db, String user, String passw) {
     try {
       Driver d = 
        (Driver)Class.forName
          ("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
       String URL = "jdbc:odbc:" + db;
       dbConn =
         DriverManager.getConnection(URL, user, passw); 
       }
     catch (Exception e) {
       e.printStackTrace();
       }
     }

  void disconnect() {
     try { 
       dbConn.close(); 
       }
     catch (Exception e) {
       e.printStackTrace();
       }
     }
}

blog comments powered by Disqus