What is the difference between Statement and Preparedstatement?

Question

When I paid for the database chair, we worked with the bank alone, with no connection to an application that interacted externally with the DBMS.
Only then was presented the JDBC(Java Database Connectivity). But due to the rush of the course I learned from JDBC only what necessary to deal with the database.

So I have the following doubt:

What is the difference between Statement and Preparedstatement and when to use one or the other?

Answer 1

The difference goes beyond the simple addition of parameters.

Most relational databases handle a query (query) JDBC / SQL in four steps:

1. Interpret (parse) the SQL query;
2. Compile the SQL query;
3. Plan and optimize the data search path;
4. Run the optimized query, searching and returning the data.

A Statement will always go through the four steps above for each SQL query sent to the database.

Already one Prepared Statement pre-executes steps (1) to (3). Then, when creating a Prepared Statement some pre-optimization is done immediately. The effect of this is that if you intend to perform the same query repeatedly changing only the parameters of each one, running using Prepared Statements will be faster and with less load on the seat.

Another advantage of Prepared Statements is that, if used correctly, help prevent SQL Injection attacks. Note that this requires the query parameters to be assigned through the methods setInt(), setString(), etc. present in the interface PreparedStatement and not by string concatenation.

For a query that will be executed a few times and requires no parameter, Statement enough. For other cases, prefer PreparedStatement.

(Based in this reply in English and in https://en.wikipedia.org/wiki/Prepared_statement).

Answer 2

The difference between them is that you can use Statement when you intend to execute fixed SQL statements, i.e., plain text instructions, such as the following:

Statement stmt = conn.createStatement();  
ResultSet rs = stmt.executeQuery("SELECT col1, col2, col3 FROM sua_tabela WHERE col1 = 'value1' AND col3 = 1");

And when you intend to execute parameterized SQL statements like the following below, you should use PreparedStatement, which also allows you to specify the type of the parameter passed as Int, String, Float etc, also providing greater security in the data integrity passed to run in the database, as it prevents you from passing invalid data in the sql statement:

PreparedStatement stmt = conn.preparedStatement("SELECT col1, col2 FROM sua_tabela WHERE col1 = ? AND col3 = ?");
stmt.setString(1, "value1");
stmt.setInt(2, 1);
ResultSet rs = stmt.executeQuery();

Answer 3

PreparedStatement is a more specialized interface of Statement, allows you greater flexibility when interacting with DBMS. With an object of type Statement, for example, you can send an SQL to the DBMS to do the processing, but your SQL already has to be "ready", with the values embedded in it.

Already with an object PreparedStatement, you can parameterize the values in your SQL. Example:

PreparedStatement stmt = conexao.preparedStatement("select * from tabela where id = ?");

stmt.setInt(1,99);

The fact is that you can use both, but as said, the PreparedStatement provides you with greater flexibility.