November 13, 2014

Remember PreparedStatement?

Simple example using PreparedStatement

Miss the good ole' days when interacting with the database was simple, easy to understand SQL statements and a few lines of code?

Select
public static void main(String[] args) throws Exception {
    String driver = "org.apache.derby.jdbc.EmbeddedDriver";
    
    String dbLocation 
        = String.format("./db/Sample");
    
    String connectionUrl =
        String.format("jdbc:derby:%s;create=true;user=sample;password=elpmas",dbLocation);
    
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(connectionUrl);
    StringBuilder sp = new StringBuilder();
    sp.append("select \n");
    sp.append("    integer_value \n");
    sp.append("  , decimal_value \n");
    sp.append("  , varchar_value \n");
    sp.append("  , timestamp_value \n");
    sp.append("from example \n");
    PreparedStatement stmt = conn.prepareStatement(sp.toString());
    
    ResultSet rs = stmt.executeQuery();
    System.out.printf("ResultSet\n");
    while (rs.next()) {
     int i = rs.getInt(1);
     BigDecimal bd = rs.getBigDecimal(2);
     String s = rs.getString(3);
     Timestamp ts = rs.getTimestamp(4);
     
     System.out.printf("%d\t%f\t%s\t%s\n",i,bd,s, ts.toString());
    }

    conn.commit();
    conn.close();        
    System.out.printf("Goodbye\n");
}

Insert
public static void main(String[] args) throws Exception {
    String driver = "org.apache.derby.jdbc.EmbeddedDriver";
    
    String dbLocation 
        = String.format("./db/Sample");
    
    String connectionUrl =
        String.format("jdbc:derby:%s;create=true;user=sample;password=elpmas",dbLocation);
    
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(connectionUrl);
    StringBuilder sp = new StringBuilder();
    sp.append("insert into example ( \n");
    sp.append("    integer_value \n");
    sp.append("  , decimal_value \n");
    sp.append("  , varchar_value \n");
    sp.append("  , timestamp_value \n");
    sp.append(" ) values ( \n");
    sp.append("    ? \n");
    sp.append("  , ? \n");
    sp.append("  , ? \n");
    sp.append("  , ? \n");
    sp.append(" ) \n");
    PreparedStatement stmt = conn.prepareStatement(sp.toString());
    stmt.setInt(1, 19);
    stmt.setBigDecimal(2, new BigDecimal("100.45"));
    stmt.setString(3, "Hello");        
    stmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
    
    int cnt = stmt.executeUpdate();
    
    System.out.printf("count = %d\n", cnt);

    conn.commit();
    conn.close();        
    System.out.printf("Goodbye\n");
}

Enjoy!