Course Module 4. Working with databases - Lecture: Working with PreparedStatement

Types of statements

We have already seen the simplest Statement interface . And although it is quite suitable for work, it is not so well suited for complex queries. In some sources, the opinion is expressed that it is not necessary to use Statement at all - more complex and more functionally rich interfaces are suitable instead.

PreparedStatement
CallableStatement

A quite reasonable question arises - why are these interfaces needed? Let's figure it out.

First, we'll take a look at the PreparedStatement interface and other JDBC features. We will turn to the CallableStatement interface later - its use, firstly, is not so common, and secondly, after everything considered about it, the conversation can already be made quite short.

Also, PreparedStatement is a great help from the popular approach to database hacking called SQL Injection.

But more on that a little later.

PreparedStatement

If you try to translate the name PreparedStatement , you can get something like “prepared statement”. The most important word here is “prepared”. What is "preparedness"?

Before we consider this issue, I propose to see a rather important point from the point of view of convenience, which occurs very often. So, in some application, we need to insert contact data into the CONTACT table. To do this, we need to prepare a query like this:

INSERT INTO
  JC_CONTACT (FIRST_NAME, LAST_NAME, PHONE, EMAIL)
VALUES
  (
    'Harry',
    'Potter',
    '+79112345678',
    'harry@example.com'
  );

At first glance, it seems that everything is not so difficult and scary. We need to write a code that will collect the string we need from the parameters: first name, last name, address and phone number. You just need to remember that all string data must be surrounded by a single quote character.

If we do this in a separate function, then we get something like this:

public String buildInsert(String firstName,, String lastName, String phone, String email) {
    String sql = "INSERT INTO JC_CONTACT (FIRST_NAME, LAST_NAME, PHONE, EMAIL) “+
             	”VALUES ('" + firstName + "','" + lastName + "','" + phone + "','" + email + ")";
    return sql;
}

We pass the first name, last name, phone number, and address into the parameter function in the form, and from them we compose a SQL query string. Quotes spoil the picture a little, but so far it's not scary.

Ok, what about the numbers? They do not need to be surrounded by quotes. Opanki, in one case you need quotes, in the other you don’t. The situation is getting more complicated.

Now let's add one more problem - what if there is an ordinary quote (and not even one) inside the string? You must first look for such quotes and process them. Mdaaa. Somehow we begin to feel uncomfortable.

If we now add date processing, then the task becomes completely boring - you have to do a huge amount of work. Dates are generally unpleasant - different SQL servers accept different formats for dates.

So what do we see? If we need to use parameters inside the query, then manually building the query becomes very unpleasant. And not just unpleasant - I would even say boring. There are a huge number of cases to consider, and this is a terribly boring job. Basically, it is for such cases that the PreparedStatement interface was proposed .

This request allows you to do two things:

Prepare a request in advance indicating the places where the parameters will be substituted
Set parameters of a certain type and then execute a query with parameters already set

PreparedStatement example

The structure for PreparedStatement for our option of setting parameters will look like this:

// Example variables
String firstName = "Harry";
String lastName = "Potter";
String phone = "+12871112233";
String email = "harry@example.com";

// Request with indication of places for parameters in the form of "?"
String sql = "INSERT INTO JC_CONTACT (FIRST_NAME, LAST_NAME, PHONE, EMAIL) VALUES (?, ?, ?, ?)";

// Create a request. The con variable is an object of type Connection
PreparedStatement stmt = con.prepareStatement(sql);

// Set parameters
stmt.setString(1, firstName);
stmt.setString(2, lastName);
stmt.setString(3, phone);
stmt.setString(4, email);

// Execute the request
stmt.executeUpdate();

As you can see, everything is quite simple.

Firstly, when writing an SQL query, the places where the parameters will need to be substituted are written with question marks - “?”.

Second, the request is created by calling con.prepareStatement() .

Thirdly, the setting of parameters goes through the indication of the number and value. Please note that the number of parameters starts from 1, not from 0, as we are used to when working with arrays and collections.

The PreparedStatement interface contains methods for setting strings — setString() , for setting numbers — setInt() , setLong() , setDouble() , for setting dates — setDate() . And more complex types - this can be seen in the documentation.

Fourth, the call to stmt.executeUpdate() is already executed without specifying the query string.

I strongly recommend making friends with PreparedStatement - this is a very effective tool.