Showing posts with label resultset. Show all posts
Showing posts with label resultset. Show all posts

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!