CLOB vs BLOB

CLOB:  Character Large Object(LONGTEXT/TEXT in MySQL)
BLOB:  Binary Large Object(BLOB in MySQL)

Example code:

@Test
public void writingCLOB_BLOG() {
 Connection conn = null;
 PreparedStatement ps = null;
 try {
  Class.forName("com.mysql.jdbc.Driver");
  conn = DriverManager.getConnection(
    "jdbc:mysql://192.168.56.101:3306/javaweb", "root", "root");
  ps = conn.prepareStatement("INSERT INTO `javaweb`.`users` "
    + "(`username`, `password`, `email`, `grade`, `usertext`, `headimg`) "
    + "VALUES (?,?,?,?,?,?);");
  ps.setString(1, "abcd5");
  ps.setString(2, "abc");
  ps.setClob(
    3,
    new BufferedReader(new InputStreamReader(
      new ByteArrayInputStream("aaaaaaaaa@gvace.com"
        .getBytes()))));  //put a string as stream, into clob
  ps.setInt(4, 1);
  ps.setClob(5, new FileReader("/home/yushan/a.log")); //a file as stream, into clob
  ps.setBlob(6, new FileInputStream("/home/yushan/Pictures/db_clob_blob.png")); //a file as stream, into blob
  ps.executeUpdate();
 } catch (ClassNotFoundException e) {
  e.printStackTrace();
 } catch (SQLException e) {
  e.printStackTrace();
 } catch (FileNotFoundException e) {
  e.printStackTrace();
 }
 finally{
  //Close all resources
 }
}
@Test
public void readingCLOB_BLOG() {
 Connection conn = null;
 PreparedStatement ps = null;
 ResultSet rs = null;
 Reader r = null;
 InputStream is = null;
 OutputStream os = null;
 try {
  Class.forName("com.mysql.jdbc.Driver");
  conn = DriverManager.getConnection(
    "jdbc:mysql://192.168.56.101:3306/javaweb", "root", "root");
  ps = conn.prepareStatement("SELECT * FROM `javaweb`.`users` "
    + "WHERE id=?;");
  ps.setInt(1, 34);
  rs = ps.executeQuery();
  while(rs.next()){
   Clob c = rs.getClob("usertext");
   r = c.getCharacterStream();
   char[] buf = new char[1024];
   int len = -1;
   while((len =r.read(buf))>0){
    System.out.print(new String(buf));
   }
   
   Blob b = rs.getBlob("headimg");
   is = b.getBinaryStream();
   os = new FileOutputStream("/home/yushan/Pictures/db_clob_blob_copy.png");
   len = -1;
   byte[] buf1 = new byte[1024];
   while((len = is.read(buf1))>0){
    //System.out.print(new String(buf1));
    os.write(buf1);
   }
  }
  
 } catch (ClassNotFoundException e) {
  e.printStackTrace();
 } catch (SQLException e) {
  e.printStackTrace();
 } catch (FileNotFoundException e) {
  e.printStackTrace();
 } catch (IOException e) {
  e.printStackTrace();
 }
 finally{
  //Close all resources
 }
}

Date vs Time vs Timestamp in SQL

java.util.Date

Direct Known Subclasses:

Date, Time, Timestamp

• java.sql.Date         year,month,day
• java.sql.Time         hour,minute,second
• java.sql.Timestamp         year,month,day,hour,minute,second

long represents time in milliseconds, starts from 1970/1/1 00:00:00

Date, time utils: http://gvace.blogspot.com/2016/02/dateformat.html

Example for date into sql:

 
 PreparedStatement ps = con.prepareStatement(sql);

 java.sql.Date date = new java.sql.Date(System.currentTimeMillis());
 ps.setObject(3, date);

 java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis());
 ps.setObject(4, timestamp);
 ps.execute();

Example for resultSet to date:

 
 java.sql.Time time = rs.getTime("ts");
 java.sql.Timestamp timestamp = rs.getTimestamp("ts");
 java.util.Date date = rs.getDate("ts");

Transaction

DML: INSERT, UPDATE, DELETE
DDL: CREATE TABLE, ALTER TABLE
DCL: GRANT

Transaction starts

• when the first DML runs.

Transaction ends:

• run commit() or rollback
• when run a DDL, will trigger commit()
• when run a DCL, will trigger commit()
• when database disconnect
• when run a DML, but the DML failed. Then it triggers rollback

ACID

• Atomicity: all succeed or all die
• Consistency: if error, must rollback to exactly original
• Isolation: other transactions will only see either before/after the change, cannot see in the middle
• Durability: if succeed, it will affect permanently

Isolation 4 levels(from low to high, but efficiency is from high to low)

1. Read Uncommitted
2. Read Committed(commonly used)
3. Repeatable Read
4. Serializable(lock table)

Example of Transaction

@Test
public void test(){
 List<String> SQLs = new ArrayList<String>();
 List<Object[]> parameters = new ArrayList<Object[]>();
 String sql1 = "INSERT INTO `javaweb`.`users` (`username`, `password`, `email`) VALUES (?,?,?);";
 Object[] parameters1 = new Object[]{"abcd1", "abc", "abc"};
 String sql2 = "INSERT INTO `javaweb`.`users` (`username`, `password`, `email`) VALUES (?,?,?);";
 Object[] parameters2 = new Object[]{"abcd2", "abc", "abc"};
 SQLs.add(sql1);
 SQLs.add(sql2);
 parameters.add(parameters1);
 parameters.add(parameters2);
 executeQuery(SQLs, parameters);
}
public void executeQuery(List<String> SQLs,List<Object[]> parameters){
 try {
  Class.forName("com.mysql.jdbc.Driver");
 } catch (ClassNotFoundException e) {
  return;
 }
 try(Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.56.101:3306/javaweb","root","root");){
  conn.setAutoCommit(false);
  for(int i=0;i<SQLs.size();i++){
   try (
    PreparedStatement ps = createPreparedStatement(conn,SQLs.get(i),parameters.get(i));
   ) {
    int resultCount = ps.executeUpdate();
    System.out.println("Update succeed, updated result count:"+resultCount);
   } catch (SQLException e) {
    e.printStackTrace();
    conn.rollback();
    return;
   }
   try {
    Thread.sleep(6000);
   } catch (InterruptedException e) {
    e.printStackTrace();
   }
  }
  conn.commit();
 } catch (SQLException e) {
  
 }
 return;
}

Batch

Batch:
For large amount of sql running in one time

Suggest using Statement, because PreparedStatement has limitation for big inputs, which may cause exceptions

Remember to setAutoCommit(false), then call commit() at the end

  
 DataSource ds = DBConnector.setupDataSource();
 try(
  Connection conn = ds.getConnection();
  Statement stmt = conn.createStatement();
 ){
  conn.setAutoCommit(false);
  long start = System.currentTimeMillis();
  for(int i=0;i<20000;i++){
   stmt.addBatch("INSERT INTO users(username,pwd,ts) values('a"+i+"','pass',now())");
  }
  stmt.executeBatch();
  conn.commit();
  long end = System.currentTimeMillis();
  System.out.println("Spend time:"+(end-start)+"ms"); //see how fast
 } catch (SQLException e) {
  Logger lgr = Logger.getLogger(SqlHelper.class);
  lgr.log(Level.WARNING,e.getMessage(),e);
 }

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()