Preparedstatement and statement in java

JDBC — Difference between PreparedStatement and Statement in Java? Answer

If you have worked with database interfacing with Java using JDBC API then you may know that the JDBC API provides three types of Statements for wrapping an SQL query and sending it for execution to the database, they are aptly named as Statement , PreparedStatement, and CallableStatement. First, a Statement is used to execute normal SQL queries like select count(*) from Courses . You can also use it to execute DDL, DML, and DCL SQL statements.

The second one, PreparedStatement is specialized to execute parameterized queries like select * from Courses where courseId=? , you can execute this SQL multiple times by just changing the course parameters. They are compiled and cached at the database end, hence quite fast for repeated execution.

The third member of this family is CallableStatement , which is there to execute or call stored procedures stored in the database.

So you see, each of the Statement classes has a different purpose and you should use them for what they have been designed for. It’s very important to understand what they are and what is their purpose, along with how to use them correctly.

In this article, we will focus on understanding the difference between the first two members of this family, Statement, and PreparedStatement, so that you can use them effectively.

But, if you want to learn more about how to use them i.e. some practical code and examples, I suggest you take a look at these free JDBC online courses for Java programmers and developers.

Difference between Statement vs PreparedStatement

Anyway, without wasting any more of your time, let’s see some key differences between these two classes, they are based on syntax, purpose, performance, security, and capabilities.

1. Purpose

PreparedStatement’s sole purpose is to execute bind queries. If you need to execute a query multiple times with just different data then use PreparedStatement and use a placeholder, the question mark sign (?) for the variable data.

When you first execute the prepared SQL query, the database will compile it and cache it for future reuse, next time you call the same query but with a different parameter, then the database will return the result almost immediately. Because of this pre-compilation, this class is called PreparedStatement in Java.

It’s very useful to build search and insert queries e.g. if your application provides an interface to search some data e.g. course details, let’s say by course, name, instructor, price, or topic. You can create PreparedStatement to handle that for better performance.

On the other hand, the sole purpose of Statement object is to execute a SQL query. You give them any query and it will execute it, but unlike PreparedStatement , it will not provide pre-compilation.

2. Syntax

The syntax for Statement is the same as SQL query, you can actually copy SQL from your favorite SQL editor and pass it as String to Statement for execution, but for PreparedStatement , you need to include placeholders i.e. questions mark (? ) sign in SQL query like

select count(*) from Books; // Uses Sttement to execute select * from Books where book_id=?; // Use PreparedStatement

The actual value is set before executing the query at runtime by using the various setXXX() methods e.g. if the placeholder refers to a varchar column then you can use setString(value) to set the value. Similarly, if a placeholder refers to an integer column then you can use setInteger(value) method.

You can further see Java Platform: Working with Databases Using JDBC course on Pluralsight to learn more about how to use these objects.

Btw, you will need a Pluralsight membership to access this course, monthly cost is around $29 which is worthy of your every penny because it will give access to more than 5000+ cutting-edge courses.

Pluralsight is like NetFlix for software developers. Last, but not least, you can also get this course by signing up for a 10-day free trial without any obligation. which is simply great.

3. Performance

In general, PreparedStatement provides better performance than Statement object because of the pre-compilation of SQL query on the database server.

When you use PreparedStatement , the query is compiled the first time but after that it is cached at the database server, making subsequent run faster.

On the other hand, with the Statement object, even if you execute the same query again and again, they are always first compiled and then executed, making them slower compared to PreparedStatement queries.

If you want to learn more about JDBC or Persistence layer performance, then I suggest you go through Vlad Mihalcea’s High-Performance Java Persistence course, where he has explained how different JDBC objects and strategies can result in different performances.

4. Security

The PreparedStatement also provides safety against SQL injection, but the incorrect use of Statment can cause SQL injection. If you remember, the cause of SQL injection is malicious SQL code which is injected by malicious users.

For example, you could have written the above query which returns a book after passing Id as below:

String style=»color: #397300;»>String SQL = «select * from Books where book_id font-family: «courier new» , «courier» , monospace;»>1== 1 OR id , which will return every single book from the database.

Though books, may not sound a sensitive data it could happen with any sensitive user data as well. PreparedStatement guards against this.

Here is a quick summary of Statement , PreparedStatement, and CallableStatement classes of JDBC API for executing queries:

That’s all about the difference between Statement and PreparedStatement in Java. You can use Statement to execute SQL queries but it’s not recommended, especially if you can use PreparedStatement, which is more secure and fast approach to get the data from the database. If you have to pass parameters always use PreparedStatment , never create dynamic SQL queries by concatenating String, it’s not safe and prone to SQL injection attack.

• 10 JDBC Best Practices Every Java Programmer should follow (read)
• 6 Essential JDBC Performance tips for Java Programmers (tips)
• Difference between Type 1 and Type 4 JDBC drivers (answers)
• How to connect to MySQL database from Java Program? (tutorial)
• JDBC Batch Insert and Update example using PreparedStatement (tutorial)
• Top 10 JDBC Interview Question for Java programmers (read)
• Difference between java.sql.Date, java.sql.Timestamp, and java.util.Date in JDBC? (answer)
• 5 Free JDBC Courses for Java Developers (courses)
• 5 Free JDBC books for Java Programmers (books)

Thanks for reading this article so far. If you like this article then please share with your friends and colleagues. If you have any questions or feedback then please drop a note.

Источник

Difference between Statement and PreparedStatement in Java

Here you will learn about difference between statement and preparedstatement in Java i.e. Statement vs PreparedStatement.

The Java Database Connectivity (JDBC) API is essentially used to connect Java applications with databases. It could be any relational or OLAP database.

These JDBC interfaces offers different functionalities, properties and methods which enables us to connect to databases and execute SQL or PL/SQL commands.

Statement Interface

The Statement interface in Java is a general purpose interface used widely to execute database queries in Java applications.

This is essentially used for static SQL statements and does not allow the end user to pass any values as parameter during runtime.

Here’s how the syntax of a statement interface that can be defined in your Java applications:

public interface Statement extends Wrapper, AutoCloseable

The Statement interface may enforce the SQL injection attacks since it does not escape the special characters which are a high advantage for the SQL injection attacks to succeed.

The Statement is not acceptable for reusability purpose and is very slow in executing the SQL queries as compared to the preparedStatement interface. It is used to execute an SQL query only once.

1. public int executeUpdate(String sql)
2. public int[] executeBatch()
3. public boolean execute(String sql)
4. public ResultSet executeQuery(String sql)

PreparedStatement Interface

The PreparedStatement in Java is an advancement over the Statement interface which is used to execute SQL queries within Java based applications.

This is used for dynamic SQL statements and therefore, allows the end user to pass multiple values as parameters during runtime.

Here’s how the syntax of a preparedStatement interface that can be defined in your Java applications:

public interface PreparedStatement extends Statement

Since this interface escapes the special characters, the PreparedStatement interface prevents SQL injections.

The preparedStatement is exceptionally better for reusability purpose and is faster in executing the SQL queries as compared to the Statement interface.

1. public void setDouble(int paramIndex, double value)
2. public void setFloat(int paramIndex, float value)
3. public void setInt(int paramIndex, int value)
4. public void setString(int paramIndex, String value)

Let us discuss difference between these interfaces in detail.

Difference between Statement and PreparedStatement in Java

This article is submitted by Tushar Soni, he has a programming blog www.codingalpha.com

Comment below if you have any queries or found any information incorrect in above tutorial for difference between Statement and PreparedStatement in Java.

Источник