JDBC - Prepared Statements

In the previous post, we executed a SQL query.

A better way to do this is use "PreparedStatement".

PreparedStatements have several advantages:
1. They are precompiled, so execute faster.
2. They can be reused.
3. They reduce chances of data corruption by SQL injection - a way for a hacker to supply malicious input strings which distort the SQL query.

Let us rewrite TestPersistence.java to use PreparedStatement, like this:

 package org.confucius;  
   
   
 import java.sql.Connection;  
 import java.sql.PreparedStatement;  
 import java.sql.SQLException;  
   
 import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;  
   
 public class TestPersistence {  
   
      public static void main(String[] args) {  
           User user = new User();  
           user.setFirstName("Gina");  
           user.setLastName("Coles");  
             
           // Persist  
           MysqlConnectionPoolDataSource dataSource = new MysqlConnectionPoolDataSource();            
           dataSource.setUser("confucius");  
           dataSource.setPassword("changeit");  
           dataSource.setServerName("localhost");  
           dataSource.setPort(3306);  
           dataSource.setDatabaseName("confuciusDB");  
             
           try {  
                Connection conn = dataSource.getConnection();  
   
                String sqlQuery = "insert into users value(?, ?)";  
                PreparedStatement ps = conn.prepareStatement(sqlQuery);  
             
                ps.setString(1, user.getFirstName());  
                ps.setString(2, user.getLastName());  
                  
                ps.execute();  
                  
                ps.close();  
                conn.close();  
   
           } catch (SQLException e) {  
                e.printStackTrace();  
           }  
      }  
 }  
   

Here we have loaded the SQL query into the PreparedStatement, then supplied it parameters. The "?" marks in the SQL query string will get replaced by the parameter values.

R-click on TestPersistence.java in Eclipse Navigator, the select Run As --> Java Application.

Now if you look at the users table in mysql, you will see the new user persisted.

 mysql> select * from users;  
 +-----------+----------+  
 | firstName | lastName |  
 +-----------+----------+  
 | John   | Doe   |  
 | Gina   | Coles  |  
 +-----------+----------+  
 2 rows in set (0.00 sec)