Suppose you need to integrate with a legacy 0r4cl3 database containing dozens of tables. Following an old-fashioned-approach you could programmatically write ad-hoc query whenever you need it and scatter this un-integration in your application code.
ORM frameworks (and DTO patterns in general) indulged us in working with objects at 360°, at every level of our architecture stack. No way, dealing with objects transparently against a database is much more confortable and faster than writing sql queries.
Let’s back to our scenario: since we have dozens of tables, not only (accordingly do DTO pattern) we should write dozens of respective classes (in order to map class’ attributes to table’ columns), but we should write dozens of CRUD (Create-Read-Update-Delete) DAOs to handle this DTOs. Definitely unflexible and unportable.
Note: we could have used a secondary persistence.xml to integrate with this legacy db, but we probably would have encountered other issues I don’t want bother you with…
By getting hands dirty with Java Reflection, this is how me and my colleague Stefano Monti faced this issue: just write really generic APIs, providing only the class of the DTO you are going to handle and a Map of parameters, and build sql queries on the fly!
After you get you ResultSet from jdbc connection then you have to automatically and generically instantiate and populate your DTO: that’s where Java Reflection comes again on stage
public static <T> List<T> findByParameters(Class<T> clazz, Map<String, Object> parameters) {
List<T> results = new ArrayList<T>();
Connection conn = OracleConnectionFactory.createConnection();
try {
PropertyDescriptor[] propertyDescriptors = PropertyUtils.getPropertyDescriptors(clazz);
String query = "";
query +="SELECT * FROM ";
// Getting the table name
String tableName = (String) clazz.getField("ciaViewName").get(null);
query += tableName;
// Adding conditions depending on how many parameters have been passed in input
Set<Entry<String, Object>> rawParameters = parameters.entrySet();
if (rawParameters.size() > 0) {
query += " WHERE ";
Iterator<Entry<String, Object>> i = rawParameters.iterator();
while (i.hasNext()) {
Entry<String, Object> entry = i.next();
String columnName = entry.getKey().toUpperCase();
Object value = entry.getValue();
// if I encounter a List then I know I have to build a IN ('','','') statement
if (List.class.isAssignableFrom(value.getClass())) {
List<String> values = (List<String>) value;
query += columnName + " IN (";
Iterator<String> j = values.iterator();
while(j.hasNext()) {
query += "'"+j.next()+"'";
if(j.hasNext()) {
query += ",";
}
else {
query += ") ";
}
}
}
// otherwise I add a condition column='value'
else {
query += columnName + "='"+value+"'";
}
if (i.hasNext()) {
query += " AND ";
}
}
}
PreparedStatement stmt = conn.prepareStatement(query);
stmt.clearParameters();
// fetching the results from the db
ResultSet resultSet = stmt.executeQuery();
// from the retrieved ResultSet now I create and iteratively populate the DTO
while(resultSet.next()) {
Object obj = clazz.newInstance();
for (PropertyDescriptor propertyDescriptor : propertyDescriptors) {
if (propertyDescriptor.getName().equals("class"))
continue;
if (propertyDescriptor.getName().equals("null"))
continue;
if (propertyDescriptor.getName().equals("id"))
continue;
if (propertyDescriptor.getName().equals("ciaViewName"))
continue;
if (propertyDescriptor.getName().equals("additionalDataType"))
continue;
Object value = resultSet.getObject(propertyDescriptor.getName().toUpperCase());
if (value != null)
PropertyUtils.setSimpleProperty(obj, propertyDescriptor.getName(), value);
}
results.add((T) obj);
}
resultSet.close();
stmt.close();
} catch (Exception e) {
logger.error(e.getMessage());
return null;
}
finally {
OracleConnectionFactory.closeConnection(conn);
}
return results;
}
And now the same trick for generate INSERT queries:
public static void insert(Object object) {
Set propertyDescriptors =
new HashSet(
Arrays.asList(
PropertyUtils.getPropertyDescriptors(
object.getClass() ) ) );
String insertQuery = "";
insertQuery += "INSERT INTO ";
String tableName = " ";
try {
tableName = (String) object.getClass().getField("ciaViewName").get(null);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
insertQuery += tableName;
String columnNames = "(";
String values = " VALUES (";
String columnName = "";
Object value = null;
Iterator i = propertyDescriptors.iterator();
while (i.hasNext()) {
PropertyDescriptor propertyDescriptor = i.next();
if (propertyDescriptor.getName().equals("class"))
continue;
if (propertyDescriptor.getName().equals("null"))
continue;
if (propertyDescriptor.getName().equals("id"))
continue;
if (propertyDescriptor.getName().equals("id_imp_fattura")) {
columnName = propertyDescriptor.getName().toUpperCase();
value = ReflectionUtils.getPropertyValue(object, propertyDescriptor.getName());
}
else {
Class type = ReflectionUtils.getType(object, propertyDescriptor.getName());
try {
if (type.equals(Class.forName("java.util.Date")) || type.equals(Class.forName("java.sql.Date"))) {
columnName = propertyDescriptor.getName().toUpperCase();
java.util.Date date = (Date) ReflectionUtils.getPropertyValue(object, propertyDescriptor.getName());
if (date != null) {
// es: 30-MAY-02
SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_PATTERN);
value = "to_date('"+sdf.format(date)+"', 'YY-MON-DD')";
}
else {
value = "NULL";
}
}
else {
columnName = propertyDescriptor.getName().toUpperCase();
value = ReflectionUtils.getPropertyValue(object, propertyDescriptor.getName());
if (value != null) {
value = "'"+value+"'";
}
else {
value = "NULL";
}
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
columnNames += columnName;
values += value;
if(i.hasNext()) {
columnNames += ", ";
values += ", ";
}
else {
columnNames += " )";
values += " )";
}
}
insertQuery += columnNames;
insertQuery += values;
Connection conn = OracleConnectionFactory.createConnection();
try {
PreparedStatement stmt = conn.prepareStatement(insertQuery);
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
finally {
OracleConnectionFactory.closeConnection(conn);
}
}
This is an example of a DTO matching 1:1 table’s columns. The attribute “ciaViewName” has the only purpose to store the name of the view on the legacy database. We use it while generating the SQL query.
public class Banca extends AdditionalData implements Serializable {
private static final long serialVersionUID = 1L;
public static final String ciaViewName = "EPOCA_BANCA";
private String codice;
private String descrizione;
private String abi;
private String cab;
private String numero_conto;
private Date dacr;
private Date duva;
private String utuv;
private String tipo;
private String intestazione;
private String cin;
private String iban;
private String bic;
public Banca() {
super.setAdditionalDataType(this.getClass().getSimpleName());
}
// Lots of setters and getters :)
first excellent articulo for using reflection and i want execute this code but i can find ReflectionUtils class where i must get this?
gretting from Tarija, Bolvia..
-Manuel Rodriguez Coria
ReflectionUtils is just a simple class I wrote providing static helper methods:
public static Object getPropertyValue(Object obj, String propertyName) {
try {
Field field = obj.getClass().getDeclaredField(propertyName);
field.setAccessible(true);
return field.get(obj);
} catch (SecurityException e) {
e.printStackTrace();
return null;
} catch (NoSuchFieldException e) {
e.printStackTrace();
return null;
} catch (IllegalArgumentException e) {
e.printStackTrace();
return null;
} catch (IllegalAccessException e) {
e.printStackTrace();
return null;
}
}
public static Class getType(Object obj, String propertyName) {
Field field;
try {
field = obj.getClass().getDeclaredField(propertyName);
field.setAccessible(true);
return field.getType();
} catch (SecurityException e) {
e.printStackTrace();
return null;
} catch (NoSuchFieldException e) {
e.printStackTrace();
return null;
}
}
if (List.class.isAssignableFrom(value.getClass()))
should be written as
if (value instanceof List)
have you think in use hibernate or JPA? o.O
oh yes, definitely! I use them everyday, but I couldn’t use them in that precise scenario :(