Statement vs PreparedStatement

In JDBC programming, you might have come across the Statement and PreparedStatement interfaces. In this blog post, I will provide a comparison between the two.

You can use both Statement and PreparedStatement to execute a query on a database table via JDBC. However, there are several differences between the two.

Difference 1  – PreparedStatement is more efficient as compared to Statement

A Statement object directly executes the SQL statement. In contrast, a PreparedStatement is a pre-compiled SQL statement. It parses and compiles the underlying query only once. After that, the query can be executed with different parameters. So if you need to run a particular query several times but with different parameters, a PreparedStatement is more efficient.

Consider the following code snippet (Code Listing 1)

 

Class.forName("com.mysql.jdbc.Driver");// Open a connectionconn = DriverManager.getConnection("url", "username", "password");//Execute a queryStatement stmt = conn.createStatement();String query = "select * from person where id="+id;ResultSet rs = stmt.executeQuery(query);

 

Each time the code is executed, the SQL statement will be parsed and compiled before the query is executed.

Now, consider the following code snippet: (Code Listing 2)

// Register JDBC driverClass.forName("com.mysql.jdbc.Driver");// Open a connectionconn = DriverManager.getConnection("jdbc:mysql://localhost/PersonDB", "root", "learnjava");//Execute a queryPreparedStatement stmt =conn.prepareStatement("select * from person where id=?");stmt.setInt(1,5);ResultSet rs = stmt.executeQuery();

In this case, when the conn.prepareStatement method is invoked, it will parse and compile the SQL statement. The PreparedStatement interface has several setter methods to set values for parameters. Here, the stmt.setInt method is used. When the code invokes this method, it replaces the’?’ at the position specified with the value specified. So, in this case it will replace the ‘?’ at position 1 with the value 5.  After that. when the code invokes the stmt.executeQuery method, it will simple execute the query. So if you want to run the query for some other id values, you simple need to invoke the setter method to set that id value. So if you need to run the code several times for different values, this code is a lot more efficient since the query parsing and compilation happens only once.

Difference 2 – PreparedStatement prevents SQL injection attacks

SQL injection attack can occur when a user passes some malicious values to your code. Suppose that in the example in Code Listing 1, the id value is passed in from a UI by a user. In such scenarios, a user can pass in some malicious values.

In the Code Listing 1 above, if a user passes id as 5, the following query will be executed:

select * from person where id=5

This will only return the details of the person record with id 5. However, if a user passes id as 1 or 1=1 then the following query will be executed:

select * from person where id=1 or 1=1

This will return the details about all the person records in the database.

In contrast, using a PreparedStatement prevents SQL injection attacks.  So even if a value like  1 or 1=1 is passed, the PreparedStatement automatically bypasses the special characters. So an SQL injection attack will not occur.

 

Summary

In addition to the above, there are several other differences between a Statement and PreparedStatement . The following table summarizes the differences

Edit
StatementPreparedStatement
Parses and compiles the SQL query each timeParses and compiles the SQL query only once, so is more efficient
Prone to SQL injection attacksEscapes all special characters, so SQL injection attacks cannot occur
Parameters are concatenated in the query itself, so error prone and messyParameter values are set separately, so less error prone
Difficult to set datatypes other than int and StringEasy to set other datatypes like Date, Clob setter as setter methods are provided

 

Comments

Post a Comment

Popular posts from this blog

How to use logging in SpringBoot with code samples

Image
In this article, we will learn how to use logging in SpringBoot. Introduction There are several Java logging frameworks like logback, log4j, and so on. Spring Boot supports out-of-the-box support for logback. Let us learn how to use logging in SpringBoot with logback. Step 1 – Create a New Maven Project Create a new Maven project. (Refer to  this  blog post). This will create a project in Eclipse as follows:   Step 2 – Add the Spring Boot Dependencies Add the spring-boot-starter-web dependency to the pom.xml . So, the pom.xml should look as follows: <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.learnjava</groupId> <artifactId>learnjava-springboot-logging-example</artifactId> <version>0.0.1-SNAPSHOT&
Read more

Python While Loop with code samples

Much like a Java while loop, you can use a Python while loop to repeatedly execute a block of code as long as a condition is true. In this article, we will learn more about the Python while loop. Python While loop Syntax A while loop in Python has the following syntax: while <condition>: loop body The while keyword is followed by a condition. If the condition evaluates to true, the body of the loop is executed. Once the body of the loop is completed, the condition is checked again and this is repeated until the condition evaluates to false at which point the loop is exited. While loop Sample Code The following code demonstrates how you can use the while loop: num=10counter=1total=0while counter<=num: total += counter counter+=1print("sum of first 10 numbers is",total) This code adds the numbers from 1 to 10 and prints the sum. It uses a while loop to iterate from 1 to 10. The while loop checks the condition counter <= num and if so adds the current value of count
Read more

How to convert a List to a Set

In this blog post ,I will be showing you how you can convert a List to a Set. Consider the following code snippet: public class ListToSetDemo {public static void main(String[] args) {List<Integer> list = Arrays.asList(5,3,11,15,9);//Method 1Set<Integer> set = new HashSet<Integer>(list);System.out.println("Set is "+set);//method 2Set<Integer> set2 = new HashSet<Integer>();set2.addAll(list);System.out.println("Set is "+set2);}} This code demonstrates two ways to convert a List to a Set. Method 1 Here, we are creating a new HashSet . We are invoking the constructor that accepts a Collection object. Here, we are passing in the input list to this constructor.   Method 2 Here again, we are creating a new HashSet . However, we are using the default constructor. We are then invoking the addAll method and passing in the input list.   When you run this code, it will print the following output: Set is [3, 5, 9, 11, 15]Set is [3, 5, 9, 11, 15] No
Read more