Study Notes - SQL & MySQL & MyBatis (2)

MySQL

MySQL is an open-source relational database management system (RDBMS). It is popular used in many websites. Java JDBC provides a standard interface to interact with any relational databases. In this post, I will introduce how to use JDBC to interact with MySQL databases.

1. What is JDBC?

JDBC API provides a standard interface for interacting with any relational database management systems (RDBMS). It consists of the following main components:

• JDBC Driver: a set of Java classes that implement JDBC interfaces for interacting with database. It translates JDBC calls into MySQL specificcalss and sends the calls directly to a specific database. Almost all database vendors provide JDBC drivers. E.g. com.mysql.cj.jdbc.Driver.
• Connection: the most important component of JDBC. In a Java application, we first load a JDBC Driver and then establist a connection to the database. Through the Connection object, we can interact with the database e.g., creating a Statement to execute SQL queries against tables. More than one connection can be opened to a database at a time.
• Statement: to execute a SQL query, e.g. SELECT, INSERT, UPDATE, DELETE, we need a Statement object. Statement ojbect is created through the Connection object. JDBC provides several types of statements for different purposes such as PreparedStatement, CallableStatement.
• ResultSet: After querying data from the database, we get a ResultSet object. The ResultSet object provides a set of API allowing us to traverse result of the query.

The typical flow of using JDBC is as follows:

• Load the JDBC Driver and create a connection to the database;
• Create a Statement and execute the query to get a ResultSet;
• Traverse and process the ResultSet;
• Close the ResultSet, Statement and Connection.

This image is from source

2. Use MySQL WorkBench to create RDBMS

3. Connect to MySQL Using JDBC Driver

• To avoid hard coding database parameters in the code, use a Java properties file to store them.

  # MySQL DB parameters
  user = root
  password = pw
  url = jdbc:mysql://localhost:3306/db_name?useUnicode=true&characterEncoding=utf8

• Import three classes, SQLException, DriverManager, and Connection from java.sql.* package.

• Create a class for handling connection creation. Call the getConnection() method of the DriverManager class and get the Connection object.

  try {
  conn = DriverManager.getConnection(url, user, password);
  // ...
  } catch (SQLException e) {
  System.out.println(e.getMessage());
  } finally {
  try {
      if (conn != null) conn.close();
  } catch (SQLException ex) {
      System.out.println(ex.getMessage());
  }
  }

4. Query data from MySQL using JDBC

• To query data from MySQL, first need to establish a connection to MySQL using Connection object.

  Connection conn = DriverManager.getConnection(url, username, password);

• Or we can develop a utility class to handle the connection.

  Connection conn = MySQLJDBCUtil.getConnection();

• Then, create a Statement object. For querying data, we just need to use the Statement object as follows:

  Statement stmt = conn.getStatement();

• Next, we can use the created Statement object to execute MySQL queries like the following:

  String sql = "SELECT * FROM `tb_product`";
  ResultSet rs = stmt.executeQuery(sql);

• Use the methods provided by ResultSet object to traverse and process data.

  • next() method returns true and move ot the next row in the ResultSet if there are rows available, otherwist it returns false.
  • getDataType()method: get column data of the current row. Pass the column name or column index.

    while (rs.next()) {
    System.out.println(rs.getString("product_name") + "\t" + rs.getString("product_desc"));
    }

• Last, close the ResultSet and Statement objects when completing traversing the data by calling close() method.

  try {
  rs.close();
  stmt.close();
  } catch(SQLException e) {
  System.out.println(e.getMessage());
  }

• From Java 7, there is another nice statement called try-catch-resources. It is automatically calls the close() method of Connection object once program finishes. So we don’t have to explicitly do it.

  String sql = String sql = "SELECT * FROM `tb_product`";
  try (Connection conn = MySQLJDBCUtil.getConnection();
     Statement stmt = conn.getStatement();
     ResultSet rs = stmt.executeQuery(sql)) {
  
  // loop through the result set
  while (rs.next()) {
      System.out.println(rs.getString("product_name") + "\t" + rs.getString("product_desc"));
  }
  } catch (SQLException e) {
  System.out.println(e.getMessage());
  }

5. Update data into table using JDBC PreparedStatement

PreparedStatement interface

This interface extends the Statement interface. We can use any statement such as SELECT, INSERT, DELETE, UPDATE with this interface. It provides some more advaced features as follows:

• Add parameters into SQL statement using placeholders in the form of question marks(?). This helps avoid SQL injection.

• Reuse the PreparedStatement with new parameters in case you need to execute the SQL statement multiple times with different parameters.

• Help increase performance of executed statement by precompiling the SQL statment.

  public void update() {
  String sqlUpdate = "UPDATE tb_product SET product_name = ?" + 
                   "WHERE product_id = ?";
  
  try (// Step 1: get Connection Object
       Connection conn = MySQLJDBCUtil.getConnection();
       // Step 2: get PreparedStatement Object
       PreparedStatement pstmt = conn.getPreparedStatement(sqlUpdate)) {
  
    // Step 3: supply values for placeholders one-by-one using setYYY() method where YYY is the datatype of the placeholder.
    String product_name = "phone";
    int product_id = 10001;
  
    pstmt.setString(1, product_name);
    pstmt.setInt(2, product_id);
  
    // Step 4: execute update
    int rowAffected = pstmt.executeUpdate();
  
    // reuse pstmt
    String product_name = "laptop";
    int product_id = 10002;
  
    pstmt.setString(1, product_name);
    pstmt.setInt(2, product_id);
    int rowAffected = pstmt.executeUpdate();
  } catch (SQLException e) {
      System.out.println(e.getMessage());
  }
  }

6. Insert data into table using JDBC PreparedStatement

Example:

public int insert (String productName, String productDesc) {
  ResultSet rs = null;
  int productId = 0;

  String sql = "INSERT INTO tb_product (product_name, product_desc)" + 
               "VALUES (?, ?)";
  try ( // get Connection Object
          Connection conn = MySQLJDBCUtil.getConnection();
          // get PreparedStatement Object
          PreparedStatement pstmt = conn.getPreparedStatement(sql, Statement.RETURN_GENERATED_KEYS);) {

    // set parameters
    pstmt.setStirng(1, "Computer");
    pstmt.setStirng(2, "Lenovo");

    // execute sql
    int rowAffected = pstmt.executeUpdate();
    if (rowAffected == 1) {
      // get product id
      rs = pstmt.getGeneratedKeys();
      if (rs.next()) productId = rs.getInt(1);
    }
  } catch (SQLException ex) {
      System.out.println(ex.getMessage());
  } finally {
    try {
      if(rs != null)  rs.close();
    } catch (SQLException e) {
      System.out.println(e.getMessage());
    }
  }

  return productId;
}

6. JDBC Transaction

What is transaction & Why Transaction

A Transaction is a unit of work that you want to treat as “a whole”. It has to either happen in full or not at all. A classical example is transfering money from one bank account to another. First withdraw the amount from the source account, and then deposit it to the destination account. The operation has to succeed in full. If get failed in one step, the monry will be lost!

Transaction in db has two main purposes:

• To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure, when execution stops (completely or partially) and many operations upon a database remain uncompleted, with unclear status.
• To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the programs’ outcomes are possibly erroneous.

A transaction in a db must maintain Atomicity, Consistency, Isolation, and Durability (ACID).

How to commit and rollback a transaction in JDBC

• Setting auto-commit mode: by default it is set to true. So the changes will be applied to the database once the statement successfully executed. In case, we want to control when to commit the transaction, we need to do first set auto-commit to be false.

  Connection conn = DriverManager.getConnection(url, user, password);
  conn.setAutoCommit(false);

• Committing and rolling back a transaction: once the auto-commit mode is set to false, we can commit or rollback the transaction.

  try (Connection conn = DriverManager.getConnection(url, user, password);) {
  conn.setAutoCommit(false);
  //operations e.g insert, update, delete
  
  // commit the transaction
  conn.commit();
  } catch(SQLException e) {
  conn.rollback();
  }