Java/Database SQL JDBC/Derby

Load and save data to Derby database

 
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class MainClass {
  public static void createDatabase() {
    String data = "jdbc:derby:presidents;create=true";
    try {
      Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
      Connection conn = DriverManager.getConnection(data);
      Statement st = conn.createStatement();
      int result = st.executeUpdate("CREATE TABLE contacts (dex INTEGER NOT NULL PRIMARY KEY "
          + "GENERATED ALWAYS AS identity (START WITH 1, INCREMENT BY 1), "
          + "name VARCHAR(40), address1 VARCHAR(40), address2 VARCHAR(40))");
 
      result = st.executeUpdate("INSERT INTO contacts (name, address1, address2"
          + ") VALUES("J","Center", 1 , "GA")");
      st.close();
    } catch (Exception e) {
      System.out.println("Error - " + e.toString());
    }
  }
  public static void readDatabase() {
    String data = "jdbc:derby:presidents";
    try {
      Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
      Connection conn = DriverManager.getConnection(data, "", "");
      Statement st = conn.createStatement();
      ResultSet rec = st.executeQuery("SELECT * FROM contacts ORDER BY name");
      while (rec.next()) {
        System.out.println(rec.getString("name") + "\n" + rec.getString("address1") + "\n"
            + rec.getString("address2") + "\n" + rec.getString("phone") + "\n"
            + rec.getString("email") + "\n");
      }
      st.close();
    } catch (Exception e) {
      System.out.println("Error - " + e.toString());
    }
  }
  public static void main(String[] arguments) {
    String home, system;    
    home = System.getProperty("user.home", ".");
    system = home + File.separatorChar + ".database";
    System.setProperty("derby.system.home", system);
    createDatabase();
    readDatabase();
  }
}

Load image from Derby database

 
import java.io.ObjectInputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.swing.ImageIcon;
public class EmployeeShow {
  public static void main(String[] args) throws Exception {
    ImageIcon image;
    Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/c:\\employee");
    Statement s = con.createStatement();
    ResultSet rs = s.executeQuery("select photo from employee where name = "Duke"");
    if (rs.next()) {
      Blob photo = rs.getBlob(1);
      ObjectInputStream ois = null;
      ois = new ObjectInputStream(photo.getBinaryStream());
      image = (ImageIcon) ois.readObject();
    } 
    s.close();
  }
}

Save image to derby database

 

import java.io.ObjectOutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import javax.swing.ImageIcon;
public class EmployeeInit {
  public static void main(String[] args) throws Exception {
    Connection con;
    con = DriverManager.getConnection("jdbc:derby://localhost:1527/" + "c:\\db\\employee");
    PreparedStatement ps;
    ps = con.prepareStatement("insert into employee(name,photo) " + "values(?,?)");
    ps.setString(1, "Duke");
    Blob blob = con.createBlob();
    ImageIcon ii = new ImageIcon("duke.png");
    ObjectOutputStream oos;
    oos = new ObjectOutputStream(blob.setBinaryStream(1));
    oos.writeObject(ii);
    oos.close();
    ps.setBlob(2, blob);
    ps.execute();
    blob.free();
    ps.close();
  }
}