Java Notes
HubArray IntroductionArray OperationsArray Programs — FundamentalsArray Interview QuestionsJagged Array in JavaMulti Dimensional ArrayOne Dimensional Arraybreak and continue in JavaDo-While Loop in JavaFor Loop in JavaIf-Else Statements in JavaNested Loops in JavaSwitch-Case in JavaWhile Loop in JavaJava Compilation ProcessFeatures of JavaYour First Java ProgramHistory of JavaJava EditionsJava Program StructureJDK, JRE, and JVMWhat is Java?Immutable Strings in JavaString Class in JavaString Interview QuestionsString MethodsString Programs — FundamentalsStringBuffer in JavaStringBuilder in JavaComments in JavaData Types in JavaIdentifiers in JavaInput and Output in JavaKeywords in JavaOperators in JavaType Casting in JavaVariables in JavaAbstract Class in JavaAbstraction in JavaAnonymous Class in JavaClass and Object in JavaConstructor in JavaEncapsulation in JavaInheritance in JavaInner Class in JavaInterface in JavaMethod Overloading in JavaMethod Overriding in JavaObject Class in JavaObject Cloning in JavaOOPs IntroductionPolymorphism in Javastatic Keyword in Javasuper Keyword in Javathis Keyword in JavaWrapper Class in JavaCollections Framework OverviewCollections Practice ProgramsComparable vs Comparator in JavaGenerics in JavaIterator in JavaArrayList in JavaLinkedList in JavaStack in Java — Collections FrameworkVector in JavaHashMap in JavaHashtable in JavaLinkedHashMap in JavaTreeMap in JavaArrayDeque in JavaDeque Interface in JavaPriorityQueue in JavaHashSet in JavaLinkedHashSet in JavaTreeSet in JavaException Handling Best PracticesCustom Exceptions in JavaException Hierarchy in JavaFinally Block in JavaException Handling Introductionthrow and throws Keywords in JavaTry-Catch Block in JavaBuffered Streams in JavaByte Streams in JavaCharacter Streams in JavaDeserialization in JavaFile Class in JavaJava NIO PackageSerialization in JavaDate and Time API in Java 8Default and Static Methods in InterfacesFunctional Interface in Java 8Lambda Expression in Java 8Method Reference in Java 8Optional Class in Java 8Stream API in Java 8Creating Threads in JavaDaemon Threads in JavaExecutor Framework in JavaInter-Thread Communication in JavaMultithreading Programs in JavaRunnable Interface in JavaSynchronization in JavaThread Class in JavaThread IntroductionThread Life Cycle in JavaThread Priority in JavaBatch Processing in JDBCCallableStatement in JDBCJDBC ArchitectureJDBC IntroductionJDBC ProjectsMySQL Connection with JDBCPreparedStatement in JDBCResultSet in JDBCStatement Interface in JDBCTransaction Management in JDBCCookies in ServletsExpression Language (EL)GenericServletHttpServletJSP IntroductionJSP Tags and JSTLMVC ArchitectureRequest and Response ObjectsServlet IntroductionServlet Life CycleSession TrackingDependency InjectionAPI GatewayConfig ServerDocker DeploymentEureka Server - Service DiscoverySpring BeansCRUD ApplicationException Handling in Spring BootJWT Authentication — Java ProgrammingREST API with Spring BootSpring SecuritySpring Boot IntroductionSpring Data JPAValidation in Spring BootSpring Core ContainerSpring Framework IntroductionDynamic Programming in JavaGraph Data Structure in JavaHashing in JavaHeap (DSA)Linked List in JavaQueue in JavaRecursion in JavaSearching Algorithms in JavaSorting Algorithms in JavaStack in Java — DSA in JavaTime ComplexityTree Data Structure in JavaAdapter Design PatternBuilder Design PatternFactory Design PatternMVC Pattern — Java ProgrammingObserver Design PatternSingleton Design PatternArray Programs — PracticeBasic Java ProgramsCollection ProgramsJDBC ProgramsMultithreading ProgramsOOP ProgramsPlacement Coding QuestionsString Programs — PracticeBanking System — Java ProgrammingChat ApplicationE-Commerce BackendEmployee Management SystemLibrary Management System — Java ProgrammingSpring Boot Fullstack ProjectStudent Management System — Java ProgrammingCoding Interview QuestionsCollections Interview QuestionsCore Java Interview QuestionsJDBC Interview QuestionsMultithreading Interview QuestionsOOPs Interview QuestionsSpring Framework Interview QuestionsJava Cheat SheetImportant Formulas & ConceptsImportant Java ProgramsQuick Revision Notes — Java ProgrammingJava Master Course - Learning Roadmap
Master PreparedStatement — parameterized queries, SQL injection prevention, performance benefits, batch operations, and CRUD patterns.
What is PreparedStatement?
PreparedStatement extends Statement and represents a precompiled SQL statement with parameter placeholders (?). It's the standard way to execute parameterized queries safely and efficiently.
java example
// Statement (UNSAFE for user input)
String sql = "SELECT * FROM users WHERE name = '" + userName + "'"; // SQL injection!
// PreparedStatement (SAFE)
String sql = "SELECT * FROM users WHERE name = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userName); // Parameters are properly escaped
ResultSet rs = pstmt.executeQuery();Setting Parameters
java example
PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO employees (first_name, last_name, email, department, salary, hire_date, is_active) " +
"VALUES (?, ?, ?, ?, ?, ?, ?)");
// Parameter index starts at 1 (not 0!)
pstmt.setString(1, "John"); // VARCHAR
pstmt.setString(2, "Doe"); // VARCHAR
pstmt.setString(3, "john@example.com"); // VARCHAR
pstmt.setString(4, "Engineering"); // VARCHAR
pstmt.setDouble(5, 85000.00); // DECIMAL
pstmt.setDate(6, java.sql.Date.valueOf("2024-01-15")); // DATE
pstmt.setBoolean(7, true); // BOOLEAN
int rows = pstmt.executeUpdate();
// All setter methods:
pstmt.setInt(1, 42); // INT
pstmt.setLong(1, 123456789L); // BIGINT
pstmt.setFloat(1, 3.14f); // FLOAT
pstmt.setDouble(1, 3.14159); // DOUBLE
pstmt.setString(1, "text"); // VARCHAR, TEXT
pstmt.setBoolean(1, true); // BOOLEAN, TINYINT(1)
pstmt.setBigDecimal(1, new BigDecimal("99.99")); // DECIMAL
pstmt.setDate(1, java.sql.Date.valueOf(LocalDate.now())); // DATE
pstmt.setTime(1, java.sql.Time.valueOf(LocalTime.now())); // TIME
pstmt.setTimestamp(1, java.sql.Timestamp.valueOf(LocalDateTime.now())); // DATETIME
pstmt.setNull(1, Types.VARCHAR); // NULL value
pstmt.setBytes(1, byteArray); // BINARY, VARBINARY
pstmt.setBlob(1, inputStream); // BLOB
pstmt.setClob(1, reader); // CLOB, TEXT
pstmt.setObject(1, anyObject); // Auto-detect typeComplete CRUD with PreparedStatement
Example
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
public class EmployeeDAO {
private final String url = "jdbc:mysql://localhost:3306/java_course?useSSL=false&serverTimezone=UTC";
private final String user = "root";
private final String pass = "password";
// CREATE
public int insert(Employee emp) throws SQLException {
String sql = "INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) " +
"VALUES (?, ?, ?, ?, ?, ?)";
try (Connection conn = DriverManager.getConnection(url, user, pass);
PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
pstmt.setString(1, emp.getFirstName());
pstmt.setString(2, emp.getLastName());
pstmt.setString(3, emp.getEmail());
pstmt.setString(4, emp.getDepartment());
pstmt.setDouble(5, emp.getSalary());
pstmt.setDate(6, java.sql.Date.valueOf(emp.getHireDate()));
pstmt.executeUpdate();
try (ResultSet keys = pstmt.getGeneratedKeys()) {
if (keys.next()) return keys.getInt(1);
}
}
return -1;
}
// READ — single
public Employee findById(int id) throws SQLException {
String sql = "SELECT * FROM employees WHERE id = ? AND is_active = TRUE";
try (Connection conn = DriverManager.getConnection(url, user, pass);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, id);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
return mapRow(rs);
}
}
}
return null;
}
// READ — search with multiple criteria
public List<Employee> search(String department, Double minSalary, Double maxSalary) throws SQLException {
StringBuilder sql = new StringBuilder("SELECT * FROM employees WHERE is_active = TRUE");
List<Object> params = new ArrayList<>();
if (department != null) {
sql.append(" AND department = ?");
params.add(department);
}
if (minSalary != null) {
sql.append(" AND salary >= ?");
params.add(minSalary);
}
if (maxSalary != null) {
sql.append(" AND salary <= ?");
params.add(maxSalary);
}
sql.append(" ORDER BY last_name");
List<Employee> results = new ArrayList<>();
try (Connection conn = DriverManager.getConnection(url, user, pass);
PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(i + 1, params.get(i));
}
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
results.add(mapRow(rs));
}
}
}
return results;
}
// UPDATE
public boolean update(Employee emp) throws SQLException {
String sql = "UPDATE employees SET first_name = ?, last_name = ?, " +
"email = ?, department = ?, salary = ? WHERE id = ?";
try (Connection conn = DriverManager.getConnection(url, user, pass);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, emp.getFirstName());
pstmt.setString(2, emp.getLastName());
pstmt.setString(3, emp.getEmail());
pstmt.setString(4, emp.getDepartment());
pstmt.setDouble(5, emp.getSalary());
pstmt.setInt(6, emp.getId());
return pstmt.executeUpdate() > 0;
}
}
// DELETE
public boolean delete(int id) throws SQLException {
String sql = "UPDATE employees SET is_active = FALSE WHERE id = ?";
try (Connection conn = DriverManager.getConnection(url, user, pass);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, id);
return pstmt.executeUpdate() > 0;
}
}
// Helper: map ResultSet row to Employee object
private Employee mapRow(ResultSet rs) throws SQLException {
Employee emp = new Employee();
emp.setId(rs.getInt("id"));
emp.setFirstName(rs.getString("first_name"));
emp.setLastName(rs.getString("last_name"));
emp.setEmail(rs.getString("email"));
emp.setDepartment(rs.getString("department"));
emp.setSalary(rs.getDouble("salary"));
emp.setHireDate(rs.getDate("hire_date").toLocalDate());
emp.setActive(rs.getBoolean("is_active"));
return emp;
}
}
Handling NULL Values
java example
// Setting NULL
pstmt.setNull(4, Types.VARCHAR); // Explicitly set NULL
pstmt.setString(4, null); // Also works for most drivers
// Reading NULL
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
double salary = rs.getDouble("salary");
if (rs.wasNull()) {
System.out.println("Salary is NULL");
}
// Better approach: use wrapper types
Integer age = (Integer) rs.getObject("age"); // Returns null if DB NULL
String phone = rs.getString("phone"); // Returns null if DB NULL
}Output
Salary is NULL
Batch Operations with PreparedStatement
Example
public void batchInsert(List<Employee> employees) throws SQLException {
String sql = "INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) " +
"VALUES (?, ?, ?, ?, ?, ?)";
try (Connection conn = DriverManager.getConnection(url, user, pass);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
conn.setAutoCommit(false); // Important for batch performance
int count = 0;
for (Employee emp : employees) {
pstmt.setString(1, emp.getFirstName());
pstmt.setString(2, emp.getLastName());
pstmt.setString(3, emp.getEmail());
pstmt.setString(4, emp.getDepartment());
pstmt.setDouble(5, emp.getSalary());
pstmt.setDate(6, java.sql.Date.valueOf(emp.getHireDate()));
pstmt.addBatch();
count++;
// Execute in chunks of 1000
if (count % 1000 == 0) {
pstmt.executeBatch();
pstmt.clearBatch();
}
}
// Execute remaining
pstmt.executeBatch();
conn.commit();
System.out.println("Inserted " + count + " employees in batch");
}
}
Output
Inserted " + count + " employees in batch
PreparedStatement for Complex Queries
Example
// IN clause (dynamic number of parameters)
public List<Employee> findByIds(List<Integer> ids) throws SQLException {
String placeholders = ids.stream()
.map(id -> "?")
.collect(Collectors.joining(", "));
String sql = "SELECT * FROM employees WHERE id IN (" + placeholders + ")";
try (Connection conn = DriverManager.getConnection(url, user, pass);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
for (int i = 0; i < ids.size(); i++) {
pstmt.setInt(i + 1, ids.get(i));
}
List<Employee> results = new ArrayList<>();
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) results.add(mapRow(rs));
}
return results;
}
}
// LIKE clause
public List<Employee> searchByName(String keyword) throws SQLException {
String sql = "SELECT * FROM employees WHERE " +
"(first_name LIKE ? OR last_name LIKE ?) AND is_active = TRUE";
try (Connection conn = DriverManager.getConnection(url, user, pass);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
String pattern = "%" + keyword + "%";
pstmt.setString(1, pattern);
pstmt.setString(2, pattern);
List<Employee> results = new ArrayList<>();
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) results.add(mapRow(rs));
}
return results;
}
}
Interview Questions
Q1: Why is PreparedStatement preferred over Statement?
Answer: (1) Prevents SQL injection by separating SQL from data, (2) Better performance through query plan caching, (3) Type-safe parameter binding, (4) Supports binary data (BLOB/CLOB), (5) More readable code without string concatenation.
Q2: How does PreparedStatement prevent SQL injection?
Answer: Parameters (?) are treated as data, not SQL code. The database driver escapes special characters and the database engine never interprets parameter values as SQL syntax — they're bound after parsing.
Q3: What is the difference between setNull() and passing null to setString()?
Answer: Both set the parameter to SQL NULL. setNull(index, Types.X) is explicit and works for all types. setString(index, null) works for most drivers but setNull() is more portable and required for some column types.
Q4: How does batch processing improve performance?
Answer: Instead of N network round-trips for N inserts, batch processing sends all statements in one network call. Combined with rewriteBatchedStatements=true in MySQL, it can convert individual INSERTs into multi-row INSERT which is 10-50x faster.
Q5: What happens if you call setString(1, value) when the column is INT?
Answer: The database will attempt type conversion. If the string is a valid number (e.g., "42"), it works. Otherwise, you get a SQLException. Always use the correct setXxx() method matching the column type.
Q6: Can you reuse a PreparedStatement with different parameters?
Answer: Yes! That's the main advantage. Call clearParameters(), set new values, and execute again. The compiled execution plan is reused, saving parse time.
Summary
In this chapter, we learned about PreparedStatement in JDBC in detail. Here are the key points:
- Basic introduction to the concept and why it is needed
- Syntax and structure with complete examples
- Internal working and best practices
- Real-world applications and use cases
- Common mistakes and how to avoid them
- How this topic is asked in interviews
To practice these concepts, write and run the code in your IDE and verify the output. Modify each example and experiment so that you deeply understand the concept.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for PreparedStatement in JDBC.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this Java Master Course topic.
Search Terms
java-master-course, java master course, java, master, course, jdbc, preparedstatement, preparedstatement in jdbc
Related Java Master Course Topics
Continue learning this concept
JDBCBatch Processing in JDBCMaster JDBC batch processing — Statement and PreparedStatement batching, performance optimization, error handling, and bulk data import patterns.JDBCCallableStatement in JDBCComplete guide to CallableStatement — calling stored procedures and functions, IN/OUT/INOUT parameters, and handling multiple result sets from MySQL procedures.JDBCJDBC ArchitectureDeep dive into JDBC architecture — two-tier and three-tier models, driver manager, connection pooling, DataSource, and internal execution flow.JDBCJDBC IntroductionComplete introduction to JDBC — Java Database Connectivity concepts, API overview, driver types, and how Java applications communicate with databases.JDBCJDBC ProjectsHands-on JDBC projects — Student Management System, Employee Payroll, Library Management with full working code including DAO pattern, connection pooling, and menu-driven interface.JDBCMySQL Connection with JDBCStep-by-step guide to connecting Java with MySQL — driver setup, connection URLs, properties, SSL configuration, and troubleshooting common errors.