JDBC

Class.forName("com.mysql.jdbc.Driver");

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");

Closing Resources

Before Java 6 Closing sequence:

1. ResultSet
2. Statement
3. Connection

Separate the close in different try/catch

From Java 7

Simply do try with resources, the resources will auto closed.

 DataSource ds = DBConnector.setupDataSource();
  
 try (
  Connection conn = ds.getConnection();
  PreparedStatement ps = createPreparedStatement(conn,sql,parameters);
  ResultSet rs = ps.executeQuery();
 ) {
  CachedRowSet rowset = new CachedRowSetImpl();
  rowset.populate(rs);
  return rowset;
 } catch (SQLException e) {
  Logger lgr = Logger.getLogger(SqlHelper.class);
  lgr.log(Level.WARNING,e.getMessage(),e);
 }

Three interface

• Statement: basic sql, no parameter accepted
• PreparedStatement: create by prepareStatement
• CallableStatement: create by prePareCall

Basic usage:
String sql = "SELECT * FROM abc WHERE name=?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setObject(1, "bar"); //element index starts from 1

More usage:
PreparedStatement ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
//this will return the inserted record's primary key

To get the key:
ps.executeUpdate();
try(ResultSet rs = ps.getGeneratedKeys();){
   rs.next();
   key = rs.getInt(1);
}

Statement functions

• execute(): return true/false, for if there is result
• executeQuery(): run select sqls, return ResultSet
• executeUpdate(): run insert/update/delete, return changed rows number

ResultSet

• next(): starts from null, call rs.next() to get first record
• getInt(), getString()