Monday, February 1, 2016



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();
  return rowset;
 } catch (SQLException e) {
  Logger lgr = Logger.getLogger(SqlHelper.class);

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:
try(ResultSet rs = ps.getGeneratedKeys();){;
   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

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

No comments:

Post a Comment