## Module 3: Using Prepared Statements and Batch Inserts

Using prepared statements, you can precompile the statements for repeated executions. Prepared statements can help us prevent SQL injections. Furthermore, prepared statements may improve the query execution performance when the query is long and complicated, and/or the network is the bottleneck.

Batch inserts enables the users to insert multiple rows of data in a single SQL statement.

In this module, we look at examples for prepared statements and batch inserts.

### Step 1. Using prepared statements

+ On the left, in the **EXPLORER** section of the VS Code, click `DemoJdbcPreparedStatement.java` file, and read the code file.

+ There are two ways of using prepared statements. The first code snippet below shows the server side prepared statement. The second code block demonstrates client side prepared statements. In our example, you should see that the execution time is similar.

  ```java
  // Reuse PS
  connection.setAutoCommit(true);
  PreparedStatement update1_ps = connection.prepareStatement("UPDATE test.t1 SET name = ? WHERE id = 1");
  System.out.println(">>> Reuse PS Begin repeating update.");
  long s1 = System.currentTimeMillis();
  for (int i = 0; i < 200; i++) {
      update1_ps.setString(1, Integer.toString(i));
      update1_ps.executeUpdate();
  }
  update1_ps.close();
  ```

  ```java
  /**
  * Client side caching prepared statement, cache hit for prepareStatement and close.
  * Try set cachePrepStmts=false|true to see the difference on elapsed time.
  */
  PreparedStatement update2_ps = null;
  System.out.println(">>> Non-Reuse PS Begin repeating update.");
  long s2 = System.currentTimeMillis();
  for (int i = 0; i < 200; i++) {
      update2_ps = connection.prepareStatement("UPDATE test.t1 SET name = ? WHERE id = 1");
      update2_ps.setString(1, Integer.toString(i));
      update2_ps.executeUpdate();
      update2_ps.close();
  }
  ```

+ You can now execute the code, and see the results.

  ```bash
  rm -f DemoJdbcPreparedStatement.class
  javac -cp mysql-connector-j-9.1.0.jar DemoJdbcPreparedStatement.java
  sleep 1
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcPreparedStatement
  ```

  ```output
  Connection established.
  >>> Reuse PS Begin repeating update.
  >>> End repeating update, elapsed: 4364(ms).
  >>> Non-Reuse PS Begin repeating update.
  >>> End repeating update, elapsed: 4225(ms).

  Executing query: select * from test.t1
          Row#, id, name
          1) 1, 199
  Turn on autocommit.
  Connection closed.
  ```

### Step 2. Using batch inserts

+ On the left, in the **EXPLORER** section of the VS Code, click `DemoJdbcBatchInsert.java` file.

+ In this step, we will insert 1000 rows into the table. We will compare the performance between `rewriteBatchedStatements=true` and `rewriteBatchedStatements=false`.

  ```bash
  rm -f DemoJdbcBatchInsert.class
  javac -cp mysql-connector-j-9.1.0.jar DemoJdbcBatchInsert.java
  sleep 1
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcBatchInsert
  ```

  ```output
  Connection established.
  >>> Begin insert 1000 rows.
  >>> End batch insert,rewriteBatchedStatements=true,elapsed: 172 (ms).
  Connection established.
  >>> Begin insert 1000 rows.
  >>> End batch insert,rewriteBatchedStatements=false,elapsed: 13252 (ms).

  /* Executing query: select count(*), max(name) from test.t1_batchtest; */
          Row#, count(*), max(name)
          1) 1000, 999
  Turn on autocommit.
  Connection closed.
  ```