Archive

Posts Tagged ‘JDBC’

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 , , , ,

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

April 12th, 2009
Nowadays there are lots of tools and frameworks out there that help you mapping your Java-Objects to a relational database and vice versa. But the overwhelming functionality that’s provided by frameworks like Hibernate (one of the most popular Object-Relational-Mapping-frameworks) goes along with the effort needed for the proper configuration of these monsters. The work that’s needed for this configuration is certainly justified when you’re developing a big application that needs lots of tables to store your objects, but you surely won’t break a butterfly on a wheel when your application only needs some config-tables, for example.

An alternative might be to write SQL-statements for every config-class/table on your own, but that’s boring ;-)
Within the next two posts I’ll demonstrate another way to build a bridge between your Java-Objects and database-tables using some nice Java-features called Generics and Reflection.

The first post covers the reading from a database and the dynamically creation of object-instances with correct values, the second post will describe the writing into a database.

Gotten used to providing examples during my last posts about the proper use of Prepared Statements, the correct Closing of Database Resources and the Generation of a String from an Exception-Stacktrace, I’ll continue with the ‘example-first’-approach.

Prerequisites


Given the class listed below and a table like that:

CREATE TABLE `Test` (
`id` int(11) NOT NULL, `
name` varchar(255) NOT NULL)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

 Java |  copy code |? 
01
public class Test {
02
	private int	     id;
03
	private String     name;
04
 
05
	public Test() {}
06
 
07
	public Test(int id, String name) {
08
		super();
09
		this.id = id;
10
		this.name = name;
11
	}
12
 
13
	public int getId() {
14
		return id;
15
	}
16
 
17
	public void setId(int id) {
18
		this.id = id;
19
	}
20
 
21
	public String getName() {
22
		return name;
23
	}
24
 
25
	public void setName(String name) {
26
		this.name = name;
27
	}
28
 
29
	public String toString() {
30
	    final String TAB = "    \n";
31
 
32
	    StringBuilder retValue = new StringBuilder();
33
 
34
	    retValue.append("Test (\n ")
35
	        .append(super.toString()).append(TAB)
36
	        .append("     id = ").append(this.id).append(TAB)
37
	        .append("     name = ").append(this.name).append(TAB)
38
	        .append(" )");
39
 
40
	    return retValue.toString();
41
	}	
42
}


We have to make some assumptions here that are reflected in the code later:

  1. The name of the table must exactly match the name of the Java-class (case-sensitive).
  2. The names of the table-columns must exactly match the name of the Java-fields (case-sensitive).
  3. The class itself must follow the Java-Beans convention:
    • The class must contain a public no-arg constructor.
    • For every field in the class there must exist a getxxx- and a setxxx-operation to access and mutate the fields.
If you don’t feel comfortable with these limitations, the code gets a little more complex, because you have do define some mappings between database- and Java-world by introducing e.g. Annotations.

Before we’ll divide the code into the reading- and writing-part, let’s create an abstract class that provides methods and attributes that are needed for reading from, as well as writing into the database. The code, respectively the code-comments, should be self-explaining, but have a look at the ‘<T>’ in the class-definition. This kind of definition allows us to parametrize the AbstractDatabaseHandler-class with the type of Java-object we want to handle. But more on that later.

 Java |  copy code |? 
01
02
 
03
/**
04
 * An abstract class that handles insert/select-operations into/from a database
05
 * 
06
 * @author Tino for http://www.java-blog.com
07
 * 
08
 * @param <T>
09
 */
10
public abstract class AbstractDatabaseHandler<T> {
11
 
12
	/**
13
	 * The type of the objects that should be created and filled with values
14
	 * from the database or inserted into the database
15
	 */
16
	protected Class<T>     type;
17
 
18
	/**
19
	 * Contains the settings to create a connection to the database like
20
	 * host/port/database/user/password
21
	 */
22
	protected DatabaseConnecter     databaseConnecter;
23
 
24
	/** The SQL-select-query */
25
	protected final String     query;
26
 
27
	/**
28
	 * Constructor
29
	 * 
30
	 * @param type
31
	 *            The type of the objects that should be created and filled with
32
	 *            values from the database or inserted into the database
33
	 * @param databaseConnecter
34
	 *            Contains the settings to create a connection to the database
35
	 *            like host/port/database/user/password
36
	 */
37
	protected AbstractDatabaseHandler(Class<T> type,
38
			DatabaseConnecter databaseConnecter) {
39
 
40
		this.databaseConnecter = databaseConnecter;
41
		this.type = type;
42
		this.query = createQuery();
43
	}
44
 
45
	/**
46
	 * Create the SQL-String to insert into / select from the database
47
	 * 
48
	 * @return the SQL-String
49
	 */
50
	protected abstract String createQuery();
51
 
52
	/**
53
	 * 
54
	 * Creates a comma-separated-String with the names of the variables in this
55
	 * class
56
	 * 
57
	 * @param usePlaceHolders
58
	 *            true, if PreparedStatement-placeholders ('?') should be used
59
	 *            instead of the names of the variables
60
	 * @return
61
	 */
62
	protected String getColumns(boolean usePlaceHolders) {
63
		StringBuilder sb = new StringBuilder();
64
 
65
		boolean first = true;
66
		/* Iterate the column-names */
67
		for (Field f : type.getDeclaredFields()) {
68
			if (first)
69
				first = false;
70
			else
71
				sb.append(", ");
72
 
73
			if (usePlaceHolders)
74
				sb.append("?");
75
			else
76
				sb.append(f.getName());
77
		}
78
 
79
		return sb.toString();
80
	}
81
}


Reading from the database


We start with the class that reads rows from a database and automatically creates and fills instances of Java-objects:

 Java |  copy code |? 
001
002
/**
003
 * 
004
 * Class that creates a list of <T>s filled with values from the corresponding
005
 * database-table.
006
 * 
007
 * @author Tino for http://www.java-blog.com
008
 * 
009
 * @param <T>
010
 */
011
public class DatabaseSelecter<T> extends AbstractDatabaseHandler<T> {
012
 
013
	public DatabaseSelecter(Class<T> type,
014
			DatabaseConnecter databaseConnecter) {
015
		super(type, databaseConnecter);
016
	}
017
 
018
	@Override
019
	protected String createQuery() {
020
 
021
		StringBuilder sb = new StringBuilder();
022
 
023
		sb.append("SELECT ");
024
		sb.append(super.getColumns(false));
025
		sb.append(" FROM ");
026
 
027
		/* We assume the table-name exactly matches the simpleName of T */
028
		sb.append(type.getSimpleName());
029
 
030
		return sb.toString();
031
	}
032
 
033
	/**
034
	 * Creates a list of <T>s filled with values from the corresponding
035
	 * database-table
036
	 * 
037
	 * @return List of <T>s filled with values from the corresponding
038
	 *         database-table
039
	 * 
040
	 * @throws SQLException
041
	 * @throws SecurityException
042
	 * @throws IllegalArgumentException
043
	 * @throws InstantiationException
044
	 * @throws IllegalAccessException
045
	 * @throws IntrospectionException
046
	 * @throws InvocationTargetException
047
	 */
048
	public List<T> selectObjects() throws SQLException,
049
			SecurityException, IllegalArgumentException,
050
			InstantiationException, IllegalAccessException,
051
			IntrospectionException, InvocationTargetException {
052
 
053
		Connection connection = null;
054
		Statement statement = null;
055
		ResultSet resultSet = null;
056
 
057
		try {
058
			connection = databaseConnecter.createConnection();
059
			statement = connection.createStatement();
060
			resultSet = statement.executeQuery(query);
061
 
062
			return createObjects(resultSet);
063
 
064
		} finally {
065
			DatabaseResourceCloser.close(resultSet, statement,
066
					connection);
067
		}
068
	}
069
 
070
	/**
071
	 * 
072
	 * Creates a list of <T>s filled with values from the provided ResultSet
073
	 * 
074
	 * @param resultSet
075
	 *            ResultSet that contains the result of the
076
	 *            database-select-query
077
	 * 
078
	 * @return List of <T>s filled with values from the provided ResultSet
079
	 * 
080
	 * @throws SecurityException
081
	 * @throws IllegalArgumentException
082
	 * @throws SQLException
083
	 * @throws InstantiationException
084
	 * @throws IllegalAccessException
085
	 * @throws IntrospectionException
086
	 * @throws InvocationTargetException
087
	 */
088
	private List<T> createObjects(ResultSet resultSet)
089
			throws SecurityException, IllegalArgumentException,
090
			SQLException, InstantiationException,
091
			IllegalAccessException, IntrospectionException,
092
			InvocationTargetException {
093
 
094
		List<T> list = new ArrayList<T>();
095
 
096
		while (resultSet.next()) {
097
 
098
			T instance = type.newInstance();
099
 
100
			for (Field field : type.getDeclaredFields()) {
101
 
102
				/* We assume the table-column-names exactly match the variable-names of T */
103
				Object value = resultSet.getObject(field.getName());
104
 
105
				PropertyDescriptor propertyDescriptor = new PropertyDescriptor(
106
						field.getName(), type);
107
 
108
				Method method = propertyDescriptor.getWriteMethod();
109
 
110
				method.invoke(instance, value);
111
			}
112
 
113
			list.add(instance);
114
		}
115
		return list;
116
	}
117
}

The createQuery()- and selectObjects()-methods are trivial stuff, but the createObjects()-method deserves a closer look:

  • line 98: For every row in the resultSet a new instance of T is created.
  • line 100: We iterate over all fields that are declared in T. getDeclaredFields() returns even private fields in contrast to getFields().
  • line 103: Because we decided to have an exact match between the Java-field-names and the table-column-names, we can retrieve the value of the table-column by using the field-name.
  • line 105: The PropertyDescriptor gives us the method that can be used to mutate the value of a field (remember the Java-Bean convention mentioned above).
  • line 110: This method is invoked on the instance of type T with the value extracted from the resultSet.
  • line 113: At the end of the method we add every instance of T to a list of T’s that will be returned.
So guys, I think that’s enough for now! I’ll continue the post with the write-part and a test-method within the next days.

Happy Coding,

Tino

Share/Save/Bookmark

tino JDBC, Java , , ,

Correct closing of JDBC-resources

March 14th, 2009
After posting about the proper use of Prepared Statements last time, I have another topic today that deals with JDBC: the art of closing your database-ressources in a correct way.

Let’s assume you have a method that uses a Connection to a database, a Statement that executes a query and a ResultSet that holds the result of a query.

I’ll start with some practical examples, how you should not handle your database-resources.

In listing 1 no closing is done at all. That’s a really bad idead, folks!

 Java |  copy code |? 
01
private void badExample() throws SQLException {
02
 
03
		Connection connection = getConnection();
04
		Statement statement = connection.createStatement();
05
 
06
		ResultSet resultSet = statement
07
				.executeQuery("SELECT some crazy stuff FROM table");
08
 
09
		while (resultSet.next())
10
			System.out.println(resultSet.getString(1));
11
	}

The coder of listing 2 remembered the reasons why database-ressources should be closed, but if the statement.executeQuery()-call throws an SQLException, the closing of the resources at the end of the method is ignored.

 Java |  copy code |? 
01
private void badExample() throws SQLException {
02
 
03
		Connection connection = getConnection();
04
		Statement statement = connection.createStatement();
05
 
06
		ResultSet resultSet = statement
07
				.executeQuery("SELECT some crazy stuff FROM table");
08
 
09
		while (resultSet.next())
10
			System.out.println(resultSet.getString(1));
11
 
12
		resultSet.close();
13
		statement.close();
14
		connection.close();
15
	}

Listing 3 shows the correct use of a finally-block. The only thing that had been forgotten: resultSet.close(), statement.close() and connection.close() can throw SQLExceptions themselves!
So the Statement and the Connection remain open, if the resultSet.close()-call throws such an exception.

 Java |  copy code |? 
01
private void badExample() throws SQLException {
02
 
03
		Connection connection = null;
04
		Statement statement = null;
05
		ResultSet resultSet = null;
06
 
07
		try {
08
			connection = getConnection();
09
			statement = connection.createStatement();
10
 
11
			resultSet = statement
12
					.executeQuery("SELECT some crazy stuff FROM table");
13
 
14
			while (resultSet.next())
15
				System.out.println(resultSet.getString(1));
16
 
17
		} finally {
18
			resultSet.close();
19
			statement.close();
20
			connection.close();
21
		}
22
	}

A correct example how resources should be closed can be found in listing 4: the close()-calls are exectued in a finally-block and wrapped itself in a try-catch-clause. Even if the resultSet.close()-call fails, your application tries to close the Statement and the Connection.

 Java |  copy code |? 
01
private void goodExample() throws SQLException {
02
 
03
		Connection connection = null;
04
		Statement statement = null;
05
		ResultSet resultSet = null;
06
 
07
		try {
08
			connection = getConnection();
09
			statement = connection.createStatement();
10
 
11
			resultSet = statement
12
					.executeQuery("SELECT some crazy stuff FROM table");
13
 
14
			while (resultSet.next())
15
				System.out.println(resultSet.getString(1));
16
 
17
		} finally {
18
			try {
19
				resultSet.close();
20
			} catch (SQLException e) {
21
				e.printStackTrace();
22
			}
23
 
24
			try {
25
				statement.close();
26
			} catch (SQLException e) {
27
				e.printStackTrace();
28
			}
29
			try {
30
				connection.close();
31
			} catch (SQLException e) {
32
				e.printStackTrace();
33
			}
34
		}
35
	}

Though the example above is a correct one, it’s very ugly to have all that try-catch-stuff multiple times in your method. Suppose how the finally-block would look like, if you need more than one Connection, multiple Statement-objects and a couple of ResultSets in your method!

Unfortunately the classes mentioned above do not implement a common Closeable-interface with a close()-method. If they would do, a single method would be sufficient, that takes Closeables, checks upon != null and closes the Closeables (wrapped in a try-catch-block, of course). By the way: you can do that with the various InputStream-/OutputStream- and Reader-/Writer-classes.

Because the described Closeable is not available, we have to go a different way: we combine the concept of overloading methods with a feature the Java-guys provided with Java 5: Varargs!

A sample-implementation that uses overloading and varargs is listed below.

 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
/**
07
 * A class that closes any open database-resources.
08
 * 
09
 * @author Tino for http://www.java-blog.com
10
 * 
11
 */
12
public class JDBCCloser {
13
 
14
	/**
15
	 * Closes the provided ResultSets
16
	 * 
17
	 * @param resultSets
18
	 *            ResultSets that should be closed
19
	 */
20
	public static void close(ResultSet... resultSets) {
21
 
22
		if (resultSets == null)
23
			return;
24
 
25
		for (ResultSet resultSet : resultSets)
26
			if (resultSet != null)
27
				try {
28
					resultSet.close();
29
				} catch (SQLException e) {
30
					/* Do some exception-logging here. */
31
					e.printStackTrace();
32
				}
33
	}
34
 
35
	/**
36
	 * Closes the provided Statements
37
	 * 
38
	 * @param statements
39
	 *            Statements that should be closed
40
	 */
41
	public static void close(Statement... statements) {
42
		/*
43
		 * No need to create methods for PreparedStatement and
44
		 * CallableStatement, because they extend Statement.
45
		 */
46
 
47
		if (statements == null)
48
			return;
49
 
50
		for (Statement statement : statements)
51
			if (statement != null)
52
				try {
53
					statement.close();
54
				} catch (SQLException e) {
55
					/* Do some exception-logging here. */
56
					e.printStackTrace();
57
				}
58
	}
59
 
60
	/**
61
	 * Closes the provided Connections
62
	 * 
63
	 * @param connections
64
	 *            Connections that should be closed
65
	 */
66
	public static void close(Connection... connections) {
67
 
68
		if (connections == null)
69
			return;
70
 
71
		for (Connection connection : connections)
72
			if (connection != null)
73
				try {
74
					connection.close();
75
				} catch (SQLException e) {
76
					/* Do some exception-logging here. */
77
					e.printStackTrace();
78
				}
79
	}
80
}

It can be called very easily like that:

 Java |  copy code |? 
01
private void goodAndElegantExample() throws SQLException {
02
 
03
		Connection connection = null;
04
		Statement statement = null;
05
		ResultSet resultSet = null;
06
 
07
		try {
08
			connection = getConnection();
09
			statement = connection.createStatement();
10
 
11
			resultSet = statement
12
					.executeQuery("SELECT some crazy stuff FROM table");
13
 
14
			while (resultSet.next())
15
				System.out.println(resultSet.getString(1));
16
 
17
		} finally {
18
			JDBCCloser.close(resultSet);
19
			JDBCCloser.close(statement);
20
			JDBCCloser.close(connection);
21
		}
22
	}

Looks much more elegant, doesn’t it?


Why should database-resources be closed after use at all?


  • Closing resources gives chance to the garbage collector to recollect memory as soon as possible. Keep in mind that especially ResultSets may occupy lots of memory depending on your query.

  • If your JDBC-driver uses Connection- or Statement-pooling, non-closed Connections/Statements might not be returned to the pool and are therefore not marked as free. This may cause performance issues.

  • A JDBC application does not only allocate resources in Java, but also on your database-server. So wasting resources affects your Java application as well as the database you’re connecting to.

  • And last but not least from the Java docs : ” It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources.”

I will end my posts with two additional comments:

  • It’s essential that your resources are closed in the opposite order to that in which you opened them! This means: close ResultSets, then close Statements, then close Connections.

  • It’s a good idead to close the resources on the same layer, where they had been created/occupied. This reduces headaches when some resources are being adressed that should be open (but are closed instead). It also decreases the risk that closing is forgotten completely.

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 , , ,