Java Notes
HubWhat Is Java?History of JavaFeatures of JavaJava EditionsJDK, JRE, and JVMYour First Java ProgramJava Program StructureJava Compilation ProcessIdentifiers in JavaKeywords in JavaVariables in JavaData Types in JavaType Casting in JavaOperators in JavaInput and Output in JavaComments in JavaIf-Else Statements in JavaSwitch-Case in JavaFor Loop in JavaWhile Loop in JavaDo-While Loop in JavaNested Loops in Javabreak and continue in JavaArray IntroductionOne Dimensional ArrayMulti Dimensional ArrayJagged Array in JavaArray OperationsArray Programs — FundamentalsArray Interview QuestionsString Class in JavaImmutable Strings in JavaString MethodsStringBuilder in JavaStringBuffer in JavaString Programs — FundamentalsString Interview QuestionsOOPs IntroductionClass and Object in JavaConstructor in Javathis Keyword in Javastatic Keyword in JavaEncapsulation in JavaInheritance in Javasuper Keyword in JavaPolymorphism in JavaMethod Overloading in JavaMethod Overriding in JavaAbstraction in JavaAbstract Class in JavaInterface in JavaObject Class in JavaWrapper Class in JavaInner Class in JavaAnonymous Class in JavaObject Cloning in JavaJava Collections Framework OverviewGenerics in JavaArrayList in JavaLinkedList in JavaVector in JavaStack in JavaHashSet in JavaLinkedHashSet in JavaTreeSet in JavaPriorityQueue in JavaArrayDeque in JavaDeque Interface in JavaHashMap in JavaLinkedHashMap in JavaTreeMap in JavaHashtable in JavaIterator in JavaComparable vs Comparator in JavaJava Collections Practice ProgramsException Handling IntroductionException Hierarchy in JavaTry-Catch Block in JavaFinally Block in Javathrow and throws Keywords in JavaCustom Exceptions in JavaException Handling Best PracticesFile Class in JavaByte Streams in JavaCharacter Streams in JavaBuffered Streams in JavaSerialization in JavaDeserialization in JavaJava NIO PackageLambda Expression in Java 8Functional Interface in Java 8Method Reference in Java 8Default and Static Methods in InterfacesStream API in Java 8Optional Class in Java 8Date and Time API in Java 8Thread IntroductionThread Life Cycle in JavaThread Class in JavaRunnable Interface in JavaCreating Threads in JavaThread Priority in JavaDaemon Threads in JavaSynchronization in JavaInter-Thread Communication in JavaExecutor Framework in JavaMultithreading Programs in JavaJDBC IntroductionJDBC ArchitectureMySQL Connection with JDBCStatement Interface in JDBCPreparedStatement in JDBCResultSet in JDBCCallableStatement in JDBCTransaction Management in JDBCBatch Processing in JDBCJDBC ProjectsServlet IntroductionGenericServletHttpServletServlet Life CycleRequest and Response ObjectsCookies in ServletsSession TrackingJSP IntroductionJSP Tags and JSTLExpression Language (EL)MVC ArchitectureSpring Framework IntroductionSpring Core ContainerSpring BeansDependency InjectionSpring Boot IntroductionREST API with Spring BootValidation in Spring BootCRUD ApplicationSpring Data JPAException Handling in Spring BootSpring SecurityJWT Authentication — Java ProgrammingConfig ServerEureka Server - Service DiscoveryAPI GatewayDocker DeploymentTime ComplexityRecursion in JavaLinked List in JavaStack in Java — DSA in JavaQueue in JavaHashing in JavaSorting Algorithms in JavaSearching Algorithms in JavaTree Data Structure in JavaHeap (DSA)Graph Data Structure in JavaDynamic Programming in JavaSingleton Design PatternFactory Design PatternBuilder Design PatternAdapter Design PatternObserver Design PatternMVC Pattern — Java ProgrammingBasic Java ProgramsArray Programs — PracticeString Programs — PracticeOOP ProgramsCollection ProgramsMultithreading ProgramsJDBC ProgramsPlacement Coding QuestionsStudent Management System — Java ProgrammingEmployee Management SystemLibrary Management System — Java ProgrammingBanking System — Java ProgrammingChat ApplicationE-Commerce BackendSpring Boot Fullstack ProjectCore Java Interview QuestionsOOPs Interview QuestionsCollections Interview QuestionsMultithreading Interview QuestionsJDBC Interview QuestionsSpring Framework Interview QuestionsCoding Interview QuestionsJava Cheat SheetQuick Revision Notes — Java ProgrammingImportant Java ProgramsImportant Formulas & ConceptsJava 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.
Continue the lesson path
Study next in this module
01JDBCResultSet in JDBCComplete guide to JDBC ResultSet — navigating results, scrollable and updatable cursors, ResultSetMetaData, and efficient data extraction patterns.02JDBCStatement Interface in JDBCComplete guide to JDBC Statement interface — executing static SQL, DDL operations, multiple results, batch execution, and understanding when to use Statement vs PreparedStatement.03JDBCCallableStatement in JDBCComplete guide to CallableStatement — calling stored procedures and functions, IN/OUT/INOUT parameters, and handling multiple result sets from MySQL procedures.04JDBCMySQL Connection with JDBCStep-by-step guide to connecting Java with MySQL — driver setup, connection URLs, properties, SSL configuration, and troubleshooting common errors.