--- title: "Using PreparedStatement in JDBC With Examples | CoderPad" description: "In this post, we'll see why and how to work with JDBC and PreparedStatement. We will also learn along with examples using code." canonical: "https://coderpad.io/blog/development/using-preparedstatement-in-jdbc-with-examples/" last_modified: "2026-04-15 16:20:51" --- [Back to blog](https://coderpad.io/blog/) # Using PreparedStatement in JDBC With Examples March 17, 2023 7 Minute Read By Manoj Mokashi [Development](https://coderpad.io/blog/development/) The [JD®BC](https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html) (Java database connectivity) API defines a standard interface for Java applications to connect to different relational database (RDBMS) implementations. Most popular relational [database](https://coderpad.io/resources/docs/interview/databases/) vendors provide JDBC-compliant drivers for their databases. The JDBC API provides the [`Statement`](https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/Statement.html) object to execute queries, updates, and DDL statements on data in a database. The [`PreparedStatement`](https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/PreparedStatement.html) is a subclass of``Statement`that provides better security, portability across vendors, and performance. The prepared in the name comes from the fact that it’s prepared/compiled initially and then reused rather than having to be compiled for each execution. This post will explain why and how to work with`PreparedStatements`. ## Usage Since a`PreparedStatement`is a subclass of`Statement`, it can do what a`Statement`can do, plus more. The fact that the`PreparedStatement`is pre-compiled is transparent to its users. Where it differs in usage is in its ability to specify parameters. The JDBC driver handles these parameters and thus, the handling is naturally compatible with the database we’re using. This leads to some benefits, as we will see later. Below is an example of using the`PreparedStatement`with two parameters: ``` PreparedStatement insStm = conn.prepareStatement("insert into emp values(?,?)"); insStm.setInt(1, 1); insStm.setString(2, "Harry Potter"); insStm.execute();Code language: Java (java) ``` As shown above, we’re inserting records into the`emp`table. In the SQL command string, we denote the places where a parameter should be with a question mark. When specifying parameter values, we identify a parameter by its position in the SQL command. The starting position is 1 instead of the usual 0. If we use the same parameter value in multiple positions, we’ll have to set it for each position. The first parameter is an integer, so we use the`setInt`method. The second is a string, so we use`setString`. Using the strongly typed methods ensures that we won’t pass the wrong type of parameter by mistake. But if we were getting the parameters dynamically, say, from an array or collection, it would be cumbersome to check the type of each parameter and then use the corresponding set method. The`setObject`method is helpful here. Using it, we can set a parameter of any [supported type](https://docs.oracle.com/cd/E19830-01/819-4721/beajw/index.html). A PreparedStatement object is not`thread-safe;`we should not use the same instance concurrently from multiple threads. ### Improper usage We can only use parameters where we would use values. If we try to use parameters to build other parts of the SQL command, it won’t work. For example, what we see here would fail with an`SQLSyntaxErrorException`: ``` PreparedStatement pStm = conn.prepareStatement("select * from ?"); pStm.setString(1, "emp"); pStm.execute();Code language: Java (java) ``` Here, we’re trying to provide the table name as a parameter. However, this isn’t allowed, nor would any other syntax part be, like column names,` [where clause, group by, order](https://coderpad.io/blog/development/sql-functions-and-techniques-every-data-person-should-know/)`by, etc. ## Security A`PreparedStatement`provides better security against certain hacking attacks compared with its parent, the Statement. The`Statement`object executes a string command. If it needs to use some parameters, we must concatenate them as strings with the SQL command. This creates a risk of the command being changed to something else using the parameters. Consider the query below using string concatenation to validate a user’s login: ``` "select * from user where user_name = '" + usrName + "'"Code language: SQL (Structured Query Language) (sql) ``` Here,`usrName`is a string that the application’s user has provided. If a malicious user were to enter usrName as`' or '1'='1`(note the single quotes used), the query string now becomes ``` select * from user where user_name = '' or '1'='1'Code language: SQL (Structured Query Language) (sql) ``` This SQL command now circumvents the check for`user_name`, since the added OR condition is always true. If the database allows us to run multiple commands in a go (separated by a semicolon, for example), then a hacker could append a destructive command like delete or drop as well. This type of hacking attack using injected parameters to alter the SQL command is called an SQL injection attack. Now, let’s try using a`PreparedStatement`instead. Using`PreparedStatement`, the above code would become ``` PreparedStatement loginStm = conn.prepareStatement("select * from user where user_name = ?"); loginStm.setInt(1, usrName); loginStm.execute();Code language: Java (java) ``` What’s different here? The JDBC driver is handling the parameters. It’ll send the parameters to the database as variables bound to the pre-compiled SQL command. The database will escape the special character within them if needed. For example, it usually escapes a single quote with an additional single quote. Thus, the malicious value remains a value, and it cannot change the SQL command. Note that we can use a`Statement`and escape the values ourselves before concatenating, but the escaping syntax can differ from database to database and hence, it’s best to let the JDBC driver handle it. Also, there are still other advantages to using a`PreparedStatement`. Read on. ## Portability and ease of use When we use string concatenation to inject parameters into the SQL command, we have to format and escape the values too. Also, this part could be database specific, making our code less portable across different database implementations. We’ve seen an example of this in the SQL injection section above. Parameters in PreparedStatement make our life easier and keep the code portable since the JDBC driver handles them. Let’s see another example using dates. Let’s assume the user has entered a date and wants to filter records by that date. If we were building the query as a string, then we would need to convert the date into a string: ``` "select * from sales where sale_date > '" + user_date_string + "'"Code language: SQL (Structured Query Language) (sql) ``` What should be the format of the date string? For example, should it be`dd-MM-YY`or`MM-dd-YY`, etc.? This would work only if the format of`user_date_string`matches the default date format for the database. The default date format can vary from one database instance to another, so we cannot be sure about it. We could use some database function like`TO_DATE`that will parse the`user_date_string`with the given format and convert it back into the date. ``` "select * from sales where sale_date > TO_DATE( 'DD-MM-YYYY', '" + user_date_string + "')"Code language: SQL (Structured Query Language) (sql) ``` However, the syntax of functions like`TO_DATE`is specific to a database vendor; others might not have the same name or parameters. Some [functions are supported by the JDBC](https://docs.oracle.com/javadb/10.8.3.0/ref/rrefjdbc88908.html) spec using the`fn`keyword, but many JDBC drivers don’t have implementations for these. So, it’s`PreparedStatement`to the rescue: ``` PreparedStatement dateFilterStm = conn.prepareStatement("select * from sales where sale_date > ?)"; dateFilterStm.setDate( 1, usrDate);Code language: Java (java) ``` All we need to do is pass the date as`java.util.Date`or`java.sql.Date`to the`PreparedStatement`. There’s no worrying about escaping or formatting or portability. PreparedStatement rocks! ## Performance ### Pre-compilation We mentioned at the start that the SQL command used in a PreparedStatement is pre-compiled or prepared initially and then reused. What does pre-compile mean, and who does it? When we run an SQL command on the database, the database will first validate, parse, and build an execution plan to run it. These steps come under pre-compiling. When the JDBC driver creates a prepared statement, it will ask the database to pre-compile that SQL command. Then, the database will store the pre-compiled command in a cache for the current database connection. If the same SQL command is to be run multiple times, using the pre-compiled version is better for performance since the compilation part has to be done only once. The database has a limited cache for pre-compiled SQL commands and hence, we cannot have too many of them. Defining parameters helps make the command reusable and therefore reduces the number of distinct commands. If the parameters weren’t separate, the same command with different parameters would each become a separate command. For example: ``` select * from emp where name = 'Goldstein'; select * from emp where name = 'Mukherjee'; select * from emp where name = ?;Code language: Java (java) ``` In the above examples, the first two SQL commands are distinct since we didn’t define the parameters separately. Whereas, if we had used a PreparedStatement as in the third, then they could have used the same SQL command. ### Reusing Using the same`PreparedStatement`multiple times in the same database session/connection will be better for performance. Each time, we need to set the parameters and execute the`PreparedStatement`. For example, we could use it to repeatedly insert data into a table ``` PreparedStatement insStm = conn.prepareStatement("insert into emp values(?,?)"); insStm.setInt(1, 1); insStm.setString(2, "Harry Potter"); insStm.execute(); insStm.setInt(1, 2); insStm.setString(2, "Tom Riddle"); insStm.execute(); insStm.close();Code language: Java (java) ``` or we could do the set parameters and`execute()`inside a loop. This is still not the best solution since each`execute()`will mean a round trip to the database. ### Batching We can send n number of statements in a single batch to the database, where n is up to us to decide. This will reduce the round trips and greatly improve performance. Below is an example using batching. ``` PreparedStatement insStm = conn.prepareStatement("insert into emp values(?,?)"); List names = List.of( "Wareen Buffet", "Steve Jobs", "Bill Gates"); boolean moreRows = false; int i=0; for( i=0; i< names.size(); i++) { insStm.setInt(1, i+1); insStm.setString(2,names.get(i)); insStm.addBatch(); if( i % 100 == 0) { // execute every 100 rows int[] rowCounts = insStm.executeBatch(); } } if( i % 100 != 0) { // lef tover rows int[] rowCounts = insStm.executeBatch(); } insStm.close();Code language: Java (java) ``` Here, we call set parameters and`addBatch()`for each row in the loop. The`addBatch()`will store the current parameters for later execution. When the counter is a multiple of 100, we’ll execute the batched rows in one go. In our case, that means 100 at a time. `executeBatch()`returns an array of integers corresponding to the number of rows affected by each statement in the batch. The SQL command needs to be sent to the database only once for pre-compilation. The batch needs to send a reference to the pre-compiled command and the parameters for all the rows in the batch. ## Conclusion In this post, we’ve seen when and why we should use PreparedStatements, along with examples using code. PreparedStatements are the preferred way to execute SQL commands using the JDBC API since they offer better security, ease of use, portability, and performance. This post was written by Manoj Mokashi. [Manoj](https://www.linkedin.com/in/manojmokashi/) has more than 25 years of experience as a developer, mostly on java, web technologies, and databases. He’s also worked on PHP, Python, Spark, AI/ML, and Solr and he really enjoys learning new things. ## Subscribe for more CoderPad insights ## Trending Articles Previous slide Next slide [Development ## What is Prompt Engineering? Introducing AI tools such as ChatGPT and Bard helps increase the efficiency of tasks such as data generation, code generation and completion, text summarization, etc. How were these AI tools trained to answer almost every question you ask them? The answer, prompt engineering, is discussed in this blog post. Read the post](https://coderpad.io/blog/development/what-is-prompt-engineering/) [Development ## A Guide To Using React’s useCallback() Hook Are you looking to optimize your React applications? Have you heard of the useCallback hook? useCallback is a powerful React hook that helps you optimize your application by preventing unnecessary re-renders. In this post, we'll dive into React's useCallback() hook, define referential equality and callback functions and how to tie it all together. Read the post](https://coderpad.io/blog/development/a-guide-to-using-reacts-usecallback-hook/) [Development ## MySQL vs. PostgreSQL: How Do They Compare? A database is a convenient tool that allows users to access information, maintain records, and apply commonly used operations, such as insertion, modification, deletion, and data organization. Learn about MySQL vs PostgreSQL, their features, advantages, and disadvantages. Finally, check out which one is the best choice for you. Read the post](https://coderpad.io/blog/development/mysql-vs-postgresql-how-do-they-compare/) [Development ## PostgreSQL LIKE Operator: A Detailed Guide When using databases, searching for values that match a given pattern is a familiar necessity. Learn how you can retrieve entries with queries including a particular string using Postgres LIKE operator. Read the post](https://coderpad.io/blog/development/postgresql-like-operator-a-detailed-guide/) [Development ## How To Use DataProvider In TestNG With Examples When it comes to testing there are many tools and techniques available. In this post, we'll discuss how to use DataProvider with TestNG. Read the post](https://coderpad.io/blog/development/how-to-use-dataprovider-in-testng-with-examples/) [Development ## How To Get Up And Running With Django Migrations: A Guide Django migrations are a way of handling the application of changes to a database schema. By following the steps outlined in this guide, you can easily set up and use migrations in your Django projects. Read the post](https://coderpad.io/blog/development/how-to-get-up-and-running-with-django-migrations-a-guide/) [Development ## Writing A Parameterized Test In JUnit With Examples A JUnit parameterized test allows you to decouple data from structure, generating cleaner and more reliable tests. Learn more in this guide. Read the post](https://coderpad.io/blog/development/writing-a-parameterized-test-in-junit-with-examples/) [Development ## How To Use Assert In JUnit With Examples The JUnit Assert class is what you use to write unit testing assertions in JUnit. Learn more about it in this guide, full of examples. Read the post](https://coderpad.io/blog/development/how-to-use-assert-in-junit-with-examples/) [Development ## A Simple Guide To TypeORM: What It Is And How To Use It Considering current languages have object-oriented programming features, most database management systems are based on the relational model, with tables instead of objects. For these reasons, mapping relational objects is very important in development. In this article, you will learn what TypeORM, one of the main tools used today, is. Read the post](https://coderpad.io/blog/development/a-simple-guide-to-typeorm-what-it-is-and-how-to-use-it/) [Development ## React vs React Native: The Differences Explained React and React Native are similar in that they both allow web developers to build applications based on the unique concept of components. But the two frameworks do this in radically different ways. In this post, we cover the key differences between react and react native frameworks, implementation, and which is an appropriate choice. Read the post](https://coderpad.io/blog/development/react-vs-react-native-the-differences-explained/) [Development ## Logging in Django The most challenging part of fixing a problem is identifying it. Unfortunately, this statement is exceptionally true when it comes to debugging. Learn how to use the power of logging into your Django applications to improve your debug process in this article. Read the post](https://coderpad.io/blog/development/logging-in-django/) [Development ## How To Get Started With TypeScript in Node.js TypeScript was the third most popular programming language in 2022, following Rust and Python. It’s widely admired by the JavaScript community and used by many companies to build front-end and back-end applications. In this post, let's dive into TypeScript, learn how to get started with TypeScript in a Node.js application, and then cover ts-node. Read the post](https://coderpad.io/blog/development/how-to-get-started-with-typescript-in-node-js/) ## Hire with confidence. See why engineering teams trust CoderPad to evaluate developers with realistic, AI-ready hiring workflows. [Request a demo](https://coderpad.io/sales/)