JDBC has been used as the major Java Database Access technique for long time. However, JDBC access always need try-catch block and repeated JDBC codes, such as connection, statement and resultset.
And recently, JDK 6.0 already released a simplified JDBC access approach. This article will introduce a similar and very simple design to reduce most of JDBC codes
1. Typical JDBC Code Template
A typical JDBC code template is as following -
1 public void example() {
2
3 Connection connection = null;
4 PreparedStatement cstmt = null;
5 ResultSet resultSet = null;
6
7 try {
8 connection = AccessUtil.getInstance().getConnection();
9 cstmt = connection.prepareStatement("SELECT * FROM VENDOR");
10 // cstmt.setString(1, getIdString(hotelIds, ","));
11 cstmt.executeQuery();
12 resultSet = cstmt.getResultSet();
13 // handle result set
14 while (resultSet.next()) {
15 //get result one by one
16 }
17
18 } catch (SQLException e) {
19 log.error(e.getMessage(), e);
20 throw new CommonSearchRuntimeExcetion(e);
21 } finally {
22 try {
23 if (resultSet != null) {
24 resultSet.close();
25 }
26 } catch (Exception e) {
27 /* swallow */
28 }
29
30 try {
31 if (cstmt != null) {
32 cstmt.close();
33 }
34 } catch (Exception e) {
35 /* swallow */
36 }
37
38 try {
39 if (connection != null) {
40 connection.close();
41 }
42 } catch (Exception e) {
43 /* swallow */
44 }
45 }
46 }
As you see in the above example, actually, we only need execute one query "SELECT * FROM VEDNOR", however, we used 46 lines of codes to execute this query and most of codes are exactly same among different access method. It caused big block mess codes.
We need remove them.
2. Use Annotation & Proxy to Remove most of repeated JDBC codes
Let's think about what are the necessary information to execute a query.
- the SQL to be executed
- the SQL parameters
- because of JDBC update and query need be handled separated, we also need know it is update or select operation
So, the design could be
- a Method annotation to get SQL statement and operation type (udpate including insert or select)
- an InvocationHandler (proxy) to execute the query behind the DAO interface
Method Annotation Implementation
1 @Target(ElementType.METHOD)
2 @Retention(RetentionPolicy.RUNTIME)
3 public @interface DataAccessor {
4
5
6 /**
7 * query string
8 * NOTE: the query string can be "SELECT * FROM VS_VendorMeetingRooms WHERE vendorUniqueID in (??)"
9 * However, if it is collection value, need use "??" instead of "?"
10 */
11 String query() default "";
12
13 /**
14 * Data operation type
15 * @return
16 */
17 DataOperationType type() default DataOperationType.SELECT_OPERATION;
18
19
20 }
The annotation is very easy to understand, however, here is just one thing. Because JDBC cannot directly set collection parameters, so, if we want to set an collection parameter, we have to do a little tricky thing. So, the SQL used "??" to represent a collection parameter. You can see the example on how to use it
Parameter Annotation Example
1 @Target(ElementType.PARAMETER)
2 @Retention(RetentionPolicy.RUNTIME)
3 public @interface ParamIndex {
4
5 int index();
6
7 }
InvocationHandler Implementation Example
1 public class DataAccessorInvocationHandler implements InvocationHandler {
2
3 /**
4 * Just sits here to hold onto the representation of the finder method so we
5 * don't have to setup the context each time.
6 */
7 private static final Map<String, DataAccessorDescriptor> finderCache = new WeakHashMap<String, DataAccessorDescriptor>();
8
9 private static final Log log = LogFactory.getLog(DataAccessorInvocationHandler.class);
10
11
12 /**
13 * This is the basic method interceptor that the proxy builds on.
14 */
15 public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
16
17
18 DataAccessorDescriptor descriptor = null;
19 RowSetDynaClass rowSetDynaClass = null;
20 try {
21
22 // obtain a cached finder descriptor (or create a new one)
23 descriptor = getFinderDescriptor(method);
24
25 createConnection(descriptor);
26
27 //fill parameters
28 buildPreparedStatement(descriptor, args);
29
30 //get the result
31 rowSetDynaClass = createReturnResult(descriptor);
32
33 } finally {
34
35 if (descriptor != null) {
36
37 ResultSet rs = descriptor.resultSet;
38 PreparedStatement pstmt = descriptor.statement;
39 Connection dbConn = descriptor.connection;
40
41 try {
42 if (rs != null) {
43 rs.close();
44 }
45 } catch (Exception e) {
46 log.error(e.getMessage(), e);
47 throw new CommonSearchRuntimeException(e);
48 }
49
50
51 try {
52 if (pstmt != null) {
53 pstmt.close();
54 }
55 } catch (Exception e) {
56 log.error(e.getMessage(), e);
57 throw new CommonSearchRuntimeException(e);
58 }
59
60 try {
61 if (dbConn != null) {
62 dbConn.close();
63 }
64 } catch (Exception e) {
65 log.error(e.getMessage(), e);
66 throw new CommonSearchRuntimeException(e);
67 }
68 }
69
70
71
72 }
73 return rowSetDynaClass;
74 }
75
76 /**
77 * Creates return result
78 * @param pstmt
79 * @param descriptor
80 * @return
81 * @throws SQLException
82 * @throws IllegalAccessException
83 * @throws InstantiationException
84 */
85 private RowSetDynaClass createReturnResult(DataAccessorDescriptor descriptor)
86 throws SQLException, InstantiationException, IllegalAccessException {
87
88 PreparedStatement statement = descriptor.statement;
89
90 if (DataOperationType.SELECT_OPERATION.equals(descriptor.operationType)) {
91
92 ResultSet rs = statement.executeQuery();
93
94 RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(rs);
95
96 return rowSetDynaClass;
97 } else if (DataOperationType.UPDATE_OPERATION.equals(descriptor.operationType)) {
98 statement.executeUpdate();
99 return null;
100 }
101
102 return null;
103 }
104
105 /**
106 *
107 * @param descriptor
108 */
109 private void createConnection(DataAccessorDescriptor descriptor) {
110 descriptor.connection = AccessUtil.getInstance().getConnection();
111 }
112
113
114 /**
115 * This method will handle the binding of named parameters
116 *
117 * @param hibernateQuery
118 * @param descriptor
119 * @param arguments
120 * @throws SQLException
121 */
122 private void buildPreparedStatement(DataAccessorDescriptor descriptor,
123 Object[] arguments) throws SQLException {
124
125 class SQLParameter {
126 //0 = collection parameter, which means the "?" will be replaced by a string,
127 //1 = normal JDBC parameter, use PreparedStatement.setObject to set parameters
128 int type;
129 Object value;
130 };
131
132 //ordered parameter map, the
133 SQLParameter[] parameters = null;
134 if (arguments == null) {
135 parameters = new SQLParameter[0];
136 } else {
137 parameters = new SQLParameter[arguments.length];
138 for (int i = 0; i < arguments.length; i++) {
139 Object argument = arguments[i];
140 Object annotation = descriptor.parameterAnnotations[i];
141
142 if (null == annotation){
143 continue; // skip param as it's not bindable
144 } else if (annotation instanceof ParamIndex) {
145
146 ParamIndex param = (ParamIndex) annotation;
147 SQLParameter parameter = new SQLParameter();
148 //if collection, we have to build Query string by ourselves
149 if (argument instanceof Collection) {
150 Collection<?> collection = (Collection<?>)argument;
151 parameter.type = 0;
152 parameter.value = StringAppendUtil.buildStringSmartly(collection, ",");
153 } else {
154 parameter.type = 1;
155 parameter.value = argument;
156 }
157 parameters[param.index() - 1] = parameter;
158 }
159 }
160 }
161
162 //firstly, replace all collection parameters by string value
163 if (parameters.length > 0) {
164 for (int i = 0; i < parameters.length; i++) {
165 SQLParameter parameter = parameters[i];
166 if (parameter.type == 0) {
167 descriptor.query = descriptor.query.replaceFirst("\\?\\?", (String)parameter.value);
168 }
169 }
170 }
171 //secondly, create statement
172 descriptor.statement = descriptor.connection.prepareStatement(descriptor.query);
173 //finally, fill parameters
174 if (parameters.length > 0) {
175 int index = 1;
176 for (int i = 0; i < parameters.length; i++) {
177 SQLParameter parameter = parameters[i];
178 if (parameter.type == 1) {
179 descriptor.statement.setObject(index, parameter.value);
180 index++;
181 }
182 }
183 }
184 }
185
186 /**
187 * This is the method that goes ahead and looks at the method to create the
188 * descriptor of it.
189 *
190 * @param method
191 * @return
192 */
193 private DataAccessorDescriptor getFinderDescriptor(Method method) {
194
195 String messageKey = getMethodKey(method);
196
197 DataAccessorDescriptor descriptor = finderCache.get(messageKey);
198 if (null != descriptor) {
199 return descriptor;
200 }
201
202 // otherwise reflect and cache finder info
203 descriptor = new DataAccessorDescriptor();
204 finderCache.put(messageKey, descriptor);
205
206 DataAccessor accessor = method.getAnnotation(DataAccessor.class);
207 String query = accessor.query();
208 if (!"".equals(query.trim())) {
209 descriptor.query = query;
210 }
211
212 descriptor.operationType = accessor.type();
213
214 // determine parameter annotations
215 Annotation[][] parameterAnnotations = method.getParameterAnnotations();
216 Object[] discoveredAnnotations = new Object[parameterAnnotations.length];
217 for (int i = 0; i < parameterAnnotations.length; i++) {
218 Annotation[] annotations = parameterAnnotations[i];
219 // each annotation per param
220 for (Annotation annotation : annotations) {
221 Class<? extends Annotation> annotationType = annotation.annotationType();
222 if (ParamIndex.class.equals(annotationType)) {
223 discoveredAnnotations[i] = annotation;
224 //finderDescriptor.isBindAsRawParameters = false;
225 break;
226 }
227 }
228 }
229
230 // set the discovered set to our finder cache object
231 descriptor.parameterAnnotations = discoveredAnnotations;
232
233 return descriptor;
234 }
235
236 /**
237 * Constructs a String key by method name and parameters
238 * @param method
239 * @return
240 */
241 private String getMethodKey(Method method){
242 StringBuilder methodString = new StringBuilder();
243 methodString.append(method.getName());
244 for(Class<?> clazz: method.getParameterTypes()){
245 methodString.append(clazz.getName());
246 }
247 return methodString.toString();
248 }
249
250 /**
251 * A simple class that is used in the cache for information about a finder
252 * method.
253 */
254 private static class DataAccessorDescriptor {
255
256 Object[] parameterAnnotations;
257
258 //the query string to be executed
259 String query;
260
261 DataOperationType operationType = DataOperationType.SELECT_OPERATION;
262
263 Connection connection = null;
264
265 PreparedStatement statement = null;
266
267 ResultSet resultSet = null;
268 }
269
270 }
NOTE:
(1) for every JDBC call, we will always return
RowSetDynaClass, which is one dynamic java bean implementation provided by Apache Common BeanUtils lib. In this way, we don't need any complicated O-R mapping tool.
(2) Collection parameters such as the parameters in "in(??)" and single parameter are handled seperatedly, you will see the example as following.
DatabaseAccessor Example
Now, the implementation of DatabaseAccssor will be very simple and straight-forward.
1 public interface VendorAccessor {
2 //SELECT example
3 @DataAccessor(query = "SELECT i.* FROM VS_Image i WHERE i.vendorID in (??)")
4 public RowSetDynaClass getVendorImage(@ParamIndex(index = 1) List<Long> vendorIds);
5
6 //UPDATE example
7 @DataAccessor(query = "update VS_VendorSearch set changeFlag = ?",
8 type = DataOperationType.UPDATE_OPERATION)
9 public void updateChangeFlagAsTrueForVendorSearch(@ParamIndex(index = 1) String changeFlag);
10 }
DatabaseAccessorFactory Example
1 public class DataAccessorFactory {
2
3 private static final Map<Class<?>, Object> proxyCache = new HashMap<Class<?>, Object>();
4
5 /**
6 * Create proxy for accessor
7 * @param accessor
8 * @return
9 */
10 public synchronized static Object getInstance(Class<?> accessor) {
11
12 Object accessorProxy = null;
13 if ((accessorProxy = proxyCache.get(accessor)) == null) {
14 accessorProxy = Proxy.newProxyInstance(DataAccessorInvocationHandler.class.getClassLoader(),
15 new Class[] {accessor}, new DataAccessorInvocationHandler());
16 proxyCache.put(accessor, accessorProxy);
17 }
18
19 return accessorProxy;
20 }
21
22 }
Accessor Client Example
private static final ExampleAccessor accessor = (ExampleAccessor)DataAccessorFactory.getInstance(ExampleAccessor.class);
public List<Vendor> getVendors(Boolean needAll) {
List<Vendor> vendors = new ArrayList<Vendor>();
RowSetDynaClass beans = accessor.getVendors();
for(Object o : beans.getRows()) {
DynaBean bean = (DynaBean) o;
Vendor v = new Vendor();
v.setID((Long)getProperty(bean, "id"));
purchasedVendors.add(v);
}
return purchasedVendors;
}
As you see, the client is very clean and straight-forward, there is no "try-catch, connection, statement, result set" etc any more.
3. Why we need this design
Let me summarize it again
- for the DAO class (accessor), it is very clean, such as
1 public interface VendorAccessor {
2 //SELECT example
3 @DataAccessor(query = "SELECT i.* FROM VS_Image i WHERE i.vendorID in (??)")
4 public RowSetDynaClass getVendorImage(@ParamIndex(index = 1) List<Long> vendorIds);
5
6 //UPDATE example
7 @DataAccessor(query = "update VS_VendorSearch set changeFlag = ?",
8 type = DataOperationType.UPDATE_OPERATION)
9 public void updateChangeFlagAsTrueForVendorSearch(@ParamIndex(index = 1) String changeFlag);
10 }
- for the DAO class client, the usage on DAO class is also very clean and straight-forwar. such as
1 private static final ExampleAccessor accessor = (ExampleAccessor)DataAccessorFactory.getInstance(ExampleAccessor.class);
2 public List<Vendor> getVendors(Boolean needAll) {
3 List<Vendor> vendors = new ArrayList<Vendor>();
4
5 RowSetDynaClass beans = accessor.getVendors();
6
7 for(Object o : beans.getRows()) {
8 DynaBean bean = (DynaBean) o;
9 Vendor v = new Vendor();
10 v.setID((Long)getProperty(bean, "id"));
11
12
13 purchasedVendors.add(v);
14 }
15
16 return purchasedVendors;
17 }
Any comment, please share with me .