In JDBC, there are two ways of executing SQL queries: using Statement and using PreparedStatement. PreparedStatement is preferred over Statement for the following reasons:
1. Improved Performance: PreparedStatements are precompiled which means that the SQL query is compiled only once, and then the same compiled query can be used multiple times with different parameters. On the other hand, a Statement is compiled every time the query is executed. This makes PreparedStatements much faster than Statements when executing the same query multiple times or running a batch of queries.
2. Protection against SQL injection attacks: PreparedStatements provide protection against SQL injection attacks. SQL injection attacks happen when malicious SQL statements are inserted into input fields, which can harm the database or steal information. PreparedStatement parameters are treated as a single unit, which eliminates the possibility of malicious code being injected into the SQL statement.
3. Automatic Type Conversion: PreparedStatements provide automatic type conversion. JDBC handles the conversion from Java types to SQL types of the prepared statement parameters, which avoids errors that can occur when using Statements where the programmer has to manually convert the types.
4. Better readability and maintainability: PreparedStatements make the code more readable and maintainable as the query and parameter values are more clearly separated. This makes it easier for another programmer to understand the SQL statement and the input parameters.
Example: Here is an example of using PreparedStatement to insert data in a database table:
String insertSQL = "INSERT INTO employee (id, name, salary) VALUES (?, ?, ?)";
PreparedStatement pstmt = connection.prepareStatement(insertSQL);
pstmt.setInt(1, 1); // set the value of the first parameter to 1
pstmt.setString(2, "John"); // set the value of the second parameter to "John"
pstmt.setDouble(3, 1000.00); // set the value of the third parameter to 1000.00
pstmt.executeUpdate();In the above example, preparedStatement is prepared with an insert query with 3 parameter placeholders indicated by ’?’. Then, we set the values of each parameter using the appropriate setter method according to the parameter data type. Finally, executing preparedStatement using the executeUpdate method inserts a row into the "employee" table.