JAVA JDBC Prepared Statement

Insert, Delete, Update and Read data with JAVA JDBC using Prepared Statement

1 What is a Prepared Statement

In database management systems a prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with high efficiency. Typically used with SQL statements such as queries or updates. The typical workflow of using a prepared statement is as follows:

  1. Prepare: The application creates the statement template as "INSERT INTO products (name, price) VALUES (?, ?);" and send it to the DBMS. Certain values are left unspecified, called parameters, placeholders or bind variables (labelled "?")
  2. Then, the DBMS compiles (parses, optimizes and translates) the statement template, and stores the result without executing it.
  3. Execute: At a later time, the application supplies (or binds) values for the parameters of the statement template, and the DBMS executes the statement (possibly returning a result). The application may execute the statement as many times as it wants with different values.

2 WHY using a Prepared Statement

As compared to executing statements directly, prepared statements offer two main advantages:

  • Performance: The overhead of compiling the statement is incurred only once, although the statement is executed multiple times. However not all optimization can be performed at the time the statement template is compiled, for two reasons: the best plan may depend on the specific values of the parameters, and the best plan may change as tables and indexes change over time.
  • Security: Prepared statements are resilient against SQL injection because values which are transmitted later using a different protocol are not compiled like the statement template. If the statement template is not derived from external input, SQL injection cannot occur.

2 how to use a Prepared Statement in JAVA

import java.sql.*;

public class SqlPreparedStatement {

    public static void main(String[] args) {
        Connection c = null;
        try{
            Class.forName("driver name");
            c = DriverManager.getConnection("connection information");

            //insert data
            String createQuery= "insert into User('name') values (?)";
            PreparedStatement preparedStatement = c.prepareStatement(createQuery);
            preparedStatement.setString(1, "yamicode" + Math.random());
            preparedStatement.executeUpdate();
            preparedStatement.close();

            //read data
            String readQuery= "select * from User where id=?";
            PreparedStatement stmtRead = c.prepareStatement(readQuery);
            stmtRead.setInt(1, 16);
            ResultSet resultSet = stmtRead.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println("User id: " + id + " name: " + name);
            }
            stmtRead.close();

        }catch(Exception e){
            System.out.println("Can not connect to the db" + e);
            System.exit(0);
        }
        if(c!=null){
            try {
                c.close();
            }catch (Exception e){
                System.out.println(e);
            }
        }
    }
}