When I paid for the database chair, we only worked with the database, alone with no connection to an application that interacted externally with the DBMS. Only then was Java Database Connectivity (JDBC) introduced. But due to the rush of the course I learned from JDBC only what is needed to deal with the database.
So I have the following doubt:
What is the difference between the Statement and the PreparedStatement and when should I use one or the other?
The difference goes beyond simply adding parameters.
Most relational databases handle a JDBC / SQL query in four steps:
A Statement will always go through the four steps above for each SQL query sent to the bank.
A Prepared Statement pre-executes steps (1) through (3). So when creating a Prepared Statement some pre-optimization is done right away. The effect of this is that if you plan to run the same query over and over again by changing only the parameters of each, running Prepared Statements will be faster and with less load on the database.
Another advantage of Prepared Statements is that, if used correctly, they help prevent SQL Injection attacks. Note that this requires the parameters of the query to be assigned through the methods setInt() , setString() , etc. present in the interface PreparedStatement and not by concatenation of strings.
For a query that will be executed a few times and does not require any parameters, Statement is enough. For other cases, prefer PreparedStatement .
(Based on in this answer in link ).
The difference between them is that you can use Statement when you intend to execute fixed SQL statements, ie 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 want to execute parameterized SQL statements like the following below, you should use PreparedStatement , which also allows you to specify the parameter type passed as Int , String , Float etc, also providing a greater security in the data integrity passed to execution in the database, because 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();
PreparedStatement is a more specialized interface of Statement , so it allows you greater flexibility when interacting with the DBMS. With an object of type Statement , for example, you can send a SQL to the DBMS to do the processing, however your SQL already has to be "ready", with the values embedded in it.
Already with a PreparedStatement object, 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 stated, PreparedStatement gives you more flexibility.