Use PreparedStatement in Java

In batches and applications that require DB connection, in most cases, the following classes are used to implement the connection process. --java.sql.Connection class --java.sql.Statement class --java.sql.ResultSet class

But lately, it seems to be popular to use the java.sql.PreparedStatement class instead of the Statement class. I will write down the reason.

Reason

  1. Measures against SQL injection
  2. As the name suggests, SQL is cached in the DB, so if you repeatedly issue the same SQL statement, the processing speed will be faster.

How to use

How to use it compared to the Statement class.

For Statement class

  1. Get an object of Statement class. You can get it with the object .createStatement () of the Connection class.
  2. Execute the SQL statement using the Statement class object. It can be executed with the Statement class object .execute ().
Connection con = DriverManager(hoge,hoge,hoge);
String sql = "select name from hogeData where id = '1'";
Statement st = con.createStatement(sql);
st.execute();

For PreparedStatement class

  1. Replace the value of the SQL statement you want to execute with?. At that time, it is not necessary to enclose it in a single quote.
  2. Get an object of PreparedStaetment class. You can get it with the object .preparedStatement () of the Connection class. Pass the SQL statement as an argument.
  3. Insert a value into? In the object .setHoge () of the PreparedStatement class. At that time, setInt () for integer type, and setString () for string type value. In the argument, specify the position of?. If it is the first? From? On the left side of the SQL statement, pass 1 as the first argument and the value you want to insert as the second argument.
  4. It can be executed by the object .executeQuery () or .executeUpdate () of the PreparedStatement class. The method used depends on the presence or absence of the returned ResultSet.
Connection con = DriverManager(hoge,hoge,hoge);
String sql = "select name from hogeData where id = ?";
Statement st = con.preparedStatement(sql);
st.setInt(1, 1);
ResultSet rs = st.executeQuery();

If you want to dig deeper into why you should use it, read below. (I will add it later)

Recommended Posts

Use PreparedStatement in Java
Use OpenCV in Java
Use Redis Stream in Java
Let's use Twilio in Java! (Introduction)
[Java] Do not use "+" in append!
Use composite keys in Java Map.
How to use classes in Java?
Do you use Stream in Java?
Partization in Java
Changes in Java 11
Rock-paper-scissors in Java
[Java] Use Collectors.collectingAndThen
Pi in Java
FizzBuzz in Java
Multilingual Locale in Java How to use Locale
Use OpenCV_Contrib (ArUco) in Java! (Part 2-Programming)
[Java] Use cryptography in the standard library
[java] sort in list
Read JSON in Java
Interpreter implementation in Java
Make Blackjack in Java
Rock-paper-scissors app in Java
Constraint programming in Java
Put java8 in centos7
Combine arrays in Java
"Hello World" in Java
Callable Interface in Java
Use OpenCV_Contrib (ArUco) in Java! (Part 1-Build) (OpenCV-3.4.4)
Use java.time in Jackson
Comments in Java source
Azure functions in java
[Java] Use of final in local variable declaration
Format XML in Java
Simple htmlspecialchars in Java
Boyer-Moore implementation in Java
Use Interceptor in Spring
Hello World in Java
webApi memorandum in java
Type determination in Java
Ping commands in Java
Various threads in java
Zabbix API in Java
ASCII art in Java
Compare Lists in Java
POST JSON in Java
Use MouseListener in Processing
Use images in Rails
Express failure in Java
Use PostgreSQL in Scala
Create JSON in Java
[JAVA] [Spring] [MyBatis] Use IN () with SQL Builder
Date manipulation in Java 8
What's new in Java 8
Why use setters/getters instead of public/private in Java
What's new in Java 9,10,11
Parallel execution in Java
Initializing HashMap in Java
I want to use ES2015 in Java too! → (´ ・ ω ・ `)
[JAVA] [Spring] [MyBatis] Use GROUP BY in SQL Builder
Use of Abstract Class and Interface properly in Java
Notes on how to use regular expressions in Java