Java Blog

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

tino JDBC, Java Generics, Java, JDBC, Reflection