Java Blog

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

tino JDBC, Java Generics, Java, JDBC, Prepared-Statements, Reflection

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