Java/Database SQL JDBC/Derby

Load and save data to Derby database

   <source lang="java">

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

}

</source>
   
  
 
  

Load image from Derby database

   <source lang="java">

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

}

</source>
   
  
 
  

Save image to derby database

   <source lang="java">

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

}

</source>