Back to JDBC Hub

Nearby lessons

11 of 37
JDBC - Sample CodeJDBC - StatementsJDBC - PreparedStatementJDBC - SQL InjectionJDBC - ResultSets

JDBC - PreparedStatement

📌 What You Will Learn
  • The life cycle of a SQL query inside the database
  • What PreparedStatement is and why it is better than Statement
  • Each variation in its own program: Statement vs PreparedStatement INSERT vs SELECT
  • SQL injection attack — the vulnerable version and the safe version
  • The setXxx methods for filling placeholders

PreparedStatement is a fundamental part of database programming with JDBC. This lesson explains Life Cycle of a SQL Query, Program 1: The Unsafe Way (Statement with values joined) and Program 2: The Safe Way (PreparedStatement INSERT) with complete, runnable code examples, clear step-by-step explanations, and common mistakes to avoid with exam-style MCQs at the end.

Life Cycle of a SQL Query

When your query reaches the database, it passes through three stages. Knowing this explains why PreparedStatement is faster:

StageWhat happens
ParsingThe database checks the SQL grammar — is the query correct?
CompilationThe parsed query is converted into an internal executable form.
Optimization + ExecutionThe database picks the fastest way and runs the query.

For a Statement, these three stages run every single time you execute the query. For a PreparedStatement, the query is parsed and compiled once, then executed many times with different values — so it is faster when you reuse the same query.

In simple words: PreparedStatement compiles the query once and reuses it; Statement recompiles it every time. So when the same query runs many times with different values, PreparedStatement is faster.

Program 1: The Unsafe Way (Statement with values joined)

With a normal Statement, values are written directly inside the SQL text. This is how beginners (and old code) do it — but it has two problems: the query is recompiled every time, and it is open to SQL injection.

Problems: the query is compiled again every time, and if the values come from user input, joining them with + is dangerous (SQL injection).

Example02
JCode Cell
1import java.sql.*;
2 
3public class StatementInsert {
4 public static void main(String[] args) throws Exception {
5 Connection con = DriverManager.getConnection(
6 "jdbc:mysql://localhost:3306/company", "root", "password");
7 Statement st = con.createStatement();
8 
9 // values are JOINED into the SQL text
10 String sql = "INSERT INTO employee VALUES (101, 'Rahul', 50000)";
11 int rows = st.executeUpdate(sql);
12 System.out.println("Inserted rows: " + rows);
13 con.close();
14 }
15}
Output
Inserted rows: 1

Program 2: The Safe Way (PreparedStatement INSERT)

A PreparedStatement is created with the query once, using ? as placeholders. Then we fill the placeholders with setXxx methods and execute.

The setXxx methods — one for each data type

Placeholder typeMethodExample
intsetInt(index, value)pst.setInt(1, 101)
StringsetString(index, value)pst.setString(2, "Rahul")
doublesetDouble(index, value)pst.setDouble(3, 50000)
DatesetDate(index, date)pst.setDate(4, date)
booleansetBoolean(index, value)pst.setBoolean(5, true)
Object (any)setObject(index, value)pst.setObject(6, obj)

The index is the placeholder's position: the first ? is 1, the second is 2, and so on.

In simple words: The `?` placeholders in a PreparedStatement are numbered from 1, not 0. setInt(1, ...) fills the first ?, setString(2, ...) fills the second, and so on.
Example03
JCode Cell
1import java.sql.*;
2 
3public class PreparedInsert {
4 public static void main(String[] args) throws Exception {
5 Connection con = DriverManager.getConnection(
6 "jdbc:mysql://localhost:3306/company", "root", "password");
7 
8 String sql = "INSERT INTO employee VALUES (?, ?, ?)"; // 3 placeholders
9 PreparedStatement pst = con.prepareStatement(sql); // parse + compile once
10 
11 pst.setInt(1, 102); // fills the first ?
12 pst.setString(2, "Priya"); // fills the second ?
13 pst.setDouble(3, 60000); // fills the third ?
14 
15 int rows = pst.executeUpdate();
16 System.out.println("Inserted rows: " + rows);
17 con.close();
18 }
19}
Output
Inserted rows: 1

Program 3: PreparedStatement SELECT with a WHERE placeholder

The same idea works for SELECT. The ? in the WHERE clause is filled at run time — no SQL text is joined.

Example04
JCode Cell
1import java.sql.*;
2 
3public class PreparedSelect {
4 public static void main(String[] args) throws Exception {
5 Connection con = DriverManager.getConnection(
6 "jdbc:mysql://localhost:3306/company", "root", "password");
7 
8 String sql = "SELECT * FROM employee WHERE salary > ?";
9 PreparedStatement pst = con.prepareStatement(sql);
10 pst.setDouble(1, 40000); // fill the WHERE placeholder
11 
12 ResultSet rs = pst.executeQuery();
13 while (rs.next()) {
14 System.out.println(rs.getInt(1) + " " + rs.getString(2)
15 + " " + rs.getDouble(3));
16 }
17 con.close();
18 }
19}
Output
102 Priya 60000.0 103 Anil 45000.0

The Safe Login — PreparedStatement Version

The same login written safely with PreparedStatement. Now the values are sent separately as data — they can never change the meaning of the SQL.

Trainer's Note: Even if pass = x' OR '1'='1, the database treats it as a plain value — it can never change the SQL meaning. That is why PreparedStatement is both faster and safer — the two reasons every professional uses it.
In simple words: With PreparedStatement the values travel as data, separate from the SQL. Even a tricky input like x' OR '1'='1 is treated as a plain string, so it can never change what the query means.
Example05
JCode Cell
1import java.sql.*;
2import java.util.Scanner;
3 
4public class SafeLogin {
5 public static void main(String[] args) throws Exception {
6 Connection con = DriverManager.getConnection(
7 "jdbc:mysql://localhost:3306/company", "root", "password");
8 Scanner sc = new Scanner(System.in);
9 
10 System.out.print("Enter user: ");
11 String user = sc.nextLine();
12 System.out.print("Enter password: ");
13 String pass = sc.nextLine();
14 
15 // SAFE - values go as placeholders, never joined into the SQL
16 String sql = "SELECT * FROM users WHERE name=? AND pwd=?";
17 PreparedStatement pst = con.prepareStatement(sql);
18 pst.setString(1, user);
19 pst.setString(2, pass);
20 
21 ResultSet rs = pst.executeQuery();
22 if (rs.next())
23 System.out.println("LOGIN SUCCESS");
24 else
25 System.out.println("Login failed");
26 con.close();
27 }
28}

Program 4: Full CRUD with PreparedStatement (INSERT + SELECT)

Bring it all together — insert a record from user input, then select records above a salary, all with PreparedStatement:

Trainer's Note: Notice the pattern: ? in the query, setXxx(index, value) to fill it, then executeQuery() (for SELECT) or executeUpdate() (for INSERT/UPDATE/DELETE) with no SQL text. The placeholder values are never part of the SQL string.
Example06
JCode Cell
1import java.sql.*;
2import java.util.Scanner;
3 
4public class PreparedCRUD {
5 public static void main(String[] args) throws Exception {
6 Connection con = DriverManager.getConnection(
7 "jdbc:mysql://localhost:3306/company", "root", "password");
8 Scanner sc = new Scanner(System.in);
9 
10 // INSERT with placeholders
11 String ins = "INSERT INTO employee VALUES (?, ?, ?)";
12 PreparedStatement pst = con.prepareStatement(ins);
13 System.out.print("Enter id: "); pst.setInt(1, sc.nextInt());
14 System.out.print("Enter name: "); pst.setString(2, sc.next());
15 System.out.print("Enter salary: "); pst.setDouble(3, sc.nextDouble());
16 System.out.println("Inserted rows: " + pst.executeUpdate());
17 
18 // SELECT with a placeholder (WHERE clause)
19 String sel = "SELECT * FROM employee WHERE salary > ?";
20 PreparedStatement pst2 = con.prepareStatement(sel);
21 pst2.setDouble(1, 40000);
22 ResultSet rs = pst2.executeQuery();
23 while (rs.next())
24 System.out.println(rs.getInt(1) + " " + rs.getString(2));
25 
26 con.close();
27 }
28}
📝 Key Takeaways
  • Query life cycle: Parsing → Compilation → Optimization + Execution.
  • Statement compiles the query every time; PreparedStatement compiles once.
  • Separate programs: Statement INSERT, PreparedStatement INSERT, PreparedStatement SELECT.
  • SQL injection: user input joined into SQL can change its meaning ('1'='1' always true).
  • Compare the UnsafeLogin (vulnerable) and SafeLogin (protected) programs.
  • Use PreparedStatement for all value-based SQL in real applications.

🧠 Test Your Knowledge

4 Questions
Progress: 0 / 4