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>