Archive

Posts Tagged ‘Prepared-Statements’

Mapping Java-Objects to a database using Reflection and Generics (Part 2)

May 2nd, 2009
This post is the second part of the series ‘Mapping Java-Objects to a database using Reflection and Generics’. In part one we covered the process of selecting entries from a database-table and instantiating Java-Objects with these entries using Reflection and Generics. Now we’ll have a look at the other side and insert some entries into the database. If you’ve understood the code-examples of the last post you won’t have any problems with the following snippets either.

 Java |  copy code |? 
01
import java.beans.IntrospectionException;
02
import java.beans.PropertyDescriptor;
03
import java.lang.reflect.Field;
04
import java.lang.reflect.InvocationTargetException;
05
import java.lang.reflect.Method;
06
import java.sql.Connection;
07
import java.sql.PreparedStatement;
08
import java.sql.SQLException;
09
import java.util.List;
10
 
11
/**
12
 * 
13
 * Class that inserts a list of <T>s into the corresponding database-table.
14
 * 
15
 * @author Tino for http://www.java-blog.com
16
 * 
17
 * @param <T>
18
 */
19
public class DatabaseInserter<T> extends AbstractDatabaseHandler<T> {
20
 
21
	public DatabaseInserter(Class<T> type,
22
			DatabaseConnecter databaseConnecter) {
23
		super(type, databaseConnecter);
24
	}
25
 
26
	@Override
27
	protected String createQuery() {
28
 
29
		StringBuilder sb = new StringBuilder();
30
 
31
		sb.append("INSERT INTO ");
32
		sb.append(type.getSimpleName());
33
		sb.append("(");
34
		sb.append(super.getColumns(false));
35
		sb.append(")");
36
		sb.append(" VALUES (");
37
		sb.append(super.getColumns(true));
38
		sb.append(")");
39
 
40
		return sb.toString();
41
	}
42
 
43
	/**
44
	 * Inserts a list of <T>s into the corresponding database-table
45
	 * 
46
	 * @param list
47
	 *            List of <T>s that should be inserted into the corresponding
48
	 *            database-table
49
	 * 
50
	 * @throws SQLException
51
	 * @throws SecurityException
52
	 * @throws IllegalArgumentException
53
	 * @throws InstantiationException
54
	 * @throws IllegalAccessException
55
	 * @throws IntrospectionException
56
	 * @throws InvocationTargetException
57
	 */
58
	public void insertObjects(List<T> list) throws SQLException,
59
			SecurityException, IllegalArgumentException,
60
			InstantiationException, IllegalAccessException,
61
			IntrospectionException, InvocationTargetException {
62
 
63
		Connection connection = null;
64
		PreparedStatement preparedStatement = null;
65
 
66
		try {
67
			connection = databaseConnecter.createConnection();
68
			preparedStatement = connection.prepareStatement(query);
69
 
70
			for (T instance : list) {
71
				int i = 0;
72
 
73
				for (Field field : type.getDeclaredFields()) {
74
					PropertyDescriptor propertyDescriptor = new PropertyDescriptor(
75
							field.getName(), type);
76
 
77
					Method method = propertyDescriptor
78
							.getReadMethod();
79
 
80
					Object value = method.invoke(instance);
81
 
82
					preparedStatement.setObject(++i, value);
83
				}
84
 
85
				preparedStatement.addBatch();
86
			}
87
			preparedStatement.executeBatch();
88
 
89
		} finally {
90
			DatabaseResourceCloser.close(preparedStatement,
91
					connection);
92
		}
93
	}
94
}

You’ve probably noticed that Prepared Statement are used for the inserts. The proper use and the advantages/disadvanteges of Prepared Statements are covered in a post I published a while ago.

The non-trivial part of the insertObjects()-method should be familiar to you. All the Reflection- and Generics-stuff has already been discussed in part one. Instead of the PropertyDescriptor that provides the set()-method of the bean-class we use the get()-method to retrieve the value of the object here.

Putting it all together and test it


Below is a small main-method that demonstrates how the presented code can be used.
 Java |  copy code |? 
01
import java.util.ArrayList;
02
import java.util.List;
03
 
04
public class Main {
05
 
06
	/** For testing purposes */
07
	public static void main(String[] args) {
08
 
09
		try {
10
 
11
			DatabaseConnecter connecter = new MySqlDatabaseConnecter(
12
					new DatabaseConnectionSettingsImpl(
13
							"127.0.0.1", 3306, "exampleDatabase",
14
							"user", "pass"));
15
 
16
			List<Test> list = new ArrayList<Test>();
17
			list.add(new Test(1, "one"));
18
			list.add(new Test(2, "two"));
19
 
20
			DatabaseInserter<Test> inserter = new DatabaseInserter<Test>(
21
					Test.class, connecter);
22
 
23
			inserter.insertObjects(list);
24
 
25
			DatabaseSelecter<Test> selecter = new DatabaseSelecter<Test>(
26
					Test.class, connecter);
27
 
28
			list = selecter.selectObjects();
29
 
30
			for (Test test : list)
31
				System.out.println(test);
32
 
33
		} catch (Exception e) {
34
			e.printStackTrace();
35
		}
36
	}
37
}

The output should look like this:
Test (
 Test@19821f    
     id = 1    
     name = one    
 )
Test (
 Test@addbf1    
     id = 2    
     name = two    
 )


So, that’s it! In the upcoming posts we’ll leave the JDBC-area and look at some other interesting Java-functionality.

Happy Coding,

Tino

Share/Save/Bookmark

tino JDBC, Java , , , ,

Prepared Statements with JDBC

March 9th, 2009
When your Java-application is talking to a database, there are several do’s and don’ts, patterns and anti-patterns you should be aware of. The probably most important ones are the proper use of indices (which is a database-structure-thing), the pooling of connections to the database (which is a Java-thing) and the reasonable use of Prepared Statements (which is also be done in Java). A blog-post concerning the first two issues might come sometimes in the future, but for now I will focus on Prepared Statements in Java using JDBC.

Because I am a fan of tutorials I’ll start with some practical examples before I’ll dive deeper into the pros and cons of Prepared Statements.

Let’s assume you have a table like this:

CREATE TABLE `userTable` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


Your application’s task: select the name for a specific id.

Listing 1 shows the code with the use of a regular Statement.

 Java |  copy code |? 
01
import java.sql.Connection;
02
import java.sql.ResultSet;
03
import java.sql.SQLException;
04
import java.sql.Statement;
05
 
06
public class StatementSelect {
07
 
08
      private Connection      connection;
09
      private Statement       statement;
10
      private ResultSet        resultSet;
11
 
12
      public StatementSelect(Connection connection) {
13
            this.connection = connection;
14
      }
15
 
16
      /**
17
       * Selects the name for a specific id from the userTable in the database
18
       *
19
       * @param id
20
       * @return
21
       * @throws SQLException
22
       */
23
      public String select(int id) throws SQLException {
24
            final String nameColumn = "name";
25
            String ret = "";
26
 
27
            String query = String.format("SELECT %s FROM userTable WHERE id = %s",
28
                        nameColumn, id); 
29
 
30
            if (statement == null || statement.isClosed())
31
                 statement = connection.createStatement();
32
 
33
           try {
34
                  resultSet = statement.executeQuery(query);
35
                  /*
36
                   * Let's assume 'id' is a primary key --> There is at most one entry
37
                   * for a specific id.
38
                   */
39
                  if (resultSet.next())
40
                        ret = resultSet.getString(nameColumn);
41
 
42
           } finally {
43
                  try {
44
                        if (resultSet != null)
45
                             resultSet.close();
46
                  } catch (SQLException e) {
47
                        e.printStackTrace();
48
                 }
49
            }
50
           return ret;
51
      }
52
 
53
     public void cleanUp() {
54
            try {
55
                  if (statement != null)
56
                        statement.close();
57
           } catch (SQLException e) {
58
                  e.printStackTrace();
59
            }
60
      }
61
}


Listing 2 makes use of a Prepared Statement.

 Java |  copy code |? 
01
import java.sql.Connection;
02
import java.sql.PreparedStatement;
03
import java.sql.ResultSet;
04
import java.sql.SQLException;
05
 
06
public class PreparedStatementSelect {
07
 
08
      private Connection           connection;
09
      private PreparedStatement    preparedStatement;
10
      private ResultSet            resultSet;
11
 
12
      public PreparedStatementSelect(Connection connection) {
13
            this.connection = connection;
14
      }
15
 
16
      /**
17
       * Selects the name for a specific id from the userTable in the database
18
       *
19
       * @param id
20
       * @return
21
       * @throws SQLException
22
       */
23
      public String select(int id) throws SQLException {
24
            final String nameColumn = "name";
25
            String ret = "";
26
 
27
            String query = String.format("SELECT %s FROM userTable WHERE id = ?",
28
                        nameColumn);
29
 
30
            if (preparedStatement == null || preparedStatement.isClosed())
31
                  preparedStatement = connection.prepareStatement(query);
32
 
33
            preparedStatement.setInt(1, id);
34
 
35
            try {
36
                  resultSet = preparedStatement.executeQuery();
37
 
38
                  /*
39
                   * Let's assume 'id' is a primary key --> There is at most one entry
40
                   * for a specific id.
41
                   */
42
                  if (resultSet.next())
43
                        ret = resultSet.getString(nameColumn);
44
 
45
            } finally {
46
                  try {
47
                        if (resultSet != null)
48
                             resultSet.close();
49
                  } catch (SQLException e) {
50
                        e.printStackTrace();
51
                  }
52
            }
53
 
54
            return ret;
55
      }
56
 
57
      public void cleanUp() {
58
            try {
59
                  if (preparedStatement != null)
60
                        preparedStatement.close();
61
            } catch (SQLException e) {
62
                  e.printStackTrace();
63
            }
64
      }
65
}



Did you spot the differences?

  • Line 27: the queries are not identical: instead of directly specifying for which id the name should be selected in listing 1, a ‘?’-placeholder is used in listing 2 when preparing the Prepared Statement.

  • Line 31: listing 1 creates a regular Statement-object; a Prepared Statement is created in listing 2.

  • Line 33: to tell your database for which id you’d like to have the name, an additional step is needed in listing 2: the ‘?’ that was used to prepare the Prepared Statement is replaced by the correct id.

  • Line 34/36: in listing 1 the query is used as parameter in the executeQuery()-call. Listing 2 doesn’t use a parameter.


So now let’s talk about the two major advantages that come with the use of Prepared Statements:

  • If the above query is executed multiple times, the query in the regular statement has to be compiled by the database every time before execution. With Prepared Statements it has to be compiled only once (but this compilation needs an extra roundtrip to the database). This once-vs-multiple-compilation may save you time, depending on the structure of your requests. As a rule of thumb: The more often you execute a query with the same structure, the more likely it is that you gain a performance boost through Prepared Statements.


  • In regular queries every String in the where-condition has to be escaped by surrounding it with ”. This job is done for you by Prepared Statements automatically.

    The consequences? You don’t have to worry about escaping and you are less vulnerable to SQL-injections.


But there are some points you should have in mind, when going for Prepared Statements:

  • Prepared Statements are built per connection. This means, if you have multiple connections to your database and the same query should be executed a couple of times, you should make sure, that the Prepared Statement for that query is always using the same connection. Otherwise you have to recreate the Prepared Statement for every connection, which produces overhead.

  • The intention of Prepared Statements is reusability, so make your Prepared Statements reusable (like I did in listing 2)! A lot of overhead would be produced, if you recreate the Prepared Statement every time when the query is executed.


  • If you’re executing a query only once, maybe regular Statements will better fit your needs: keep in mind that the preparation of Prepared Statements needs an extra database-call. But don’t forget to validate your input first to prevent SQL-injection!


  • If you’re using MySQL < 5.1.17, the query cache is not used by Prepared Statements. So if you have few modifications on your datasets and you execute the same query over and over, have a close look if the use of Prepared Statements really increases performance.


  • If you think about parametrizing the tablename (e.g. ‘SELECT name FROM ?’): forget it! That won’t work!


So after this short trip through Prepared Statements I hope some of the advantages and disadvantes have become obvious.

If you find any mistakes in my post, or if you are of a different opinion, feel free to leave a comment or use the contact-site.


Happy coding,

Tino.

Share/Save/Bookmark

tino JDBC, Java , , ,