I was also struggling with this, but decided to create a modelToSqlConverter. It's not the most handsome looking code i ever wrote, but it works like a charm.
I have actually created a little wrapper around Dbutils i call GenericDataAccessor. Its a work in progress and i will put it up on github as soon as i get the time. Its a great tool for all middlesized projects that feels to small for a real ORM like Hibernate, but to big for just using JDBC. Any how. Feel fre to use this if you want, it has helped me alot.
Here is the ModelConverter
/**
* The <code>ModelToSqlConverter</code> class <br>
* <br>
* Deserializes the model and creates the sql string for update and insert.<br>
* Also generates the object array for the values from the model.
*/
public class ModelToSqlConverter{
private Object model;
private List<Object> params, keyParams, modelList;
private String fieldNameString, questionMarkString, sqlString, conditionString, updateString;
private boolean update;
private String[] keys;
private Object[][] multiParams;
/**
* Initializes a newly created <code>ModelToSqlConverter</code>
*
* @param model The model representing the ASW file.
* @param keys The keys for locating the right row in ASW file.
*/
@SuppressWarnings("unchecked")
private ModelToSqlConverter(Object model, String... keys){
this.model = model;
this.keys = keys;
params = new ArrayList<>();
keyParams = new ArrayList<>();
questionMarkString = "VALUES (";
fieldNameString = "(";
updateString = "";
conditionString = "WHERE ";
update = keys != null && keys.length > 0;
if(model instanceof List<?>){
modelList = (List<Object>) model;
convertModelListToSqlAndParams();
}else{
convertModelToSqlAndParams();
}
}
/**
* Main method for Converting Model into SQL String and to value parameters.
*/
private void convertModelToSqlAndParams(){
for(Field field : model.getClass().getDeclaredFields()){
try{
field.setAccessible(true);
Object value = field.get(model);
String fieldName = field.getName();
if(value != null && !fieldName.equalsIgnoreCase("serialVersionUID")){
if(!update){
addQuestionMark();
addNameToSql(fieldName);
addValueToObjectArray(value);
}else{
if(isKey(fieldName)){
conditionString += fieldName + " = ?,";
keyParams.add(value);
}else{
addParamAndNameToSql(fieldName);
addValueToObjectArray(value);
}
}
}
}catch(IllegalArgumentException e){
// TODO Auto-generated catch block
e.printStackTrace();
}catch(IllegalAccessException e){
// TODO Auto-generated catch block
e.printStackTrace();
}
}
sqlString = update ? getUpdateSql() : getInsertSql();
for(Object key : keyParams){
addValueToObjectArray(key);
}
}
/**
* Main method for Converting Model into SQL String and to value parameters.
*/
private void convertModelListToSqlAndParams(){
int row = 0;
boolean isKey = false;
boolean firstModel = true;
Field[] fields = modelList.get(0).getClass().getDeclaredFields();
multiParams = new Object[modelList.size()][fields.length];
for(Object model : modelList){
int col = 0;
keyParams = new ArrayList<>();
for(Field field : fields){
try{
field.setAccessible(true);
Object value = field.get(model);
String fieldName = field.getName();
if(value != null && !fieldName.equalsIgnoreCase("serialVersionUID")){
if(!update){
if(firstModel){
addQuestionMark();
addNameToSql(fieldName);
}
addValueToMultiParams(value, row, col);
}else{
isKey = isKey(fieldName);
if(isKey){
if(firstModel){
conditionString += fieldName + " = ?,";
}
keyParams.add(value);
}else{
if(firstModel)
addParamAndNameToSql(fieldName);
addValueToMultiParams(value, row, col);
}
}
}
}catch(IllegalArgumentException e){
// TODO Auto-generated catch block
e.printStackTrace();
}catch(IllegalAccessException e){
// TODO Auto-generated catch block
e.printStackTrace();
}
if(!isKey)
col++;
}
firstModel = false;
for(Object key : keyParams){
addValueToMultiParams(key, row, col);
col++;
}
row++;
}
sqlString = update ? getUpdateSql() : getInsertSql();
}
/**
* @param columnName
*/
private void addNameToSql(String columnName){
fieldNameString += columnName + ",";
}
/**
* @param value
*/
private void addValueToObjectArray(Object value){
params.add(value);
}
/**
* @param value
* @param row
* @param col
*/
private void addValueToMultiParams(Object value, int row, int col){
multiParams[row][col] = value;
}
public Object[][] getMultiValueParams(){
return removeNullsFromMultiArray(multiParams);
}
/**
* Adds question mark to string
*/
private void addQuestionMark(){
questionMarkString += "?,";
}
/**
* @param columnName
*/
private void addParamAndNameToSql(String columnName){
updateString += columnName + "= ?,";
}
/**
* @return the update SQL string.
*/
private String getUpdateSql(){
return updateString.substring(0, updateString.lastIndexOf(",")) + " " + conditionString.substring(0, conditionString.lastIndexOf(","));
}
/**
* @return the insert SQL string.
*/
private String getInsertSql(){
return fieldNameString.substring(0, fieldNameString.lastIndexOf(",")) + ") " + questionMarkString.substring(0, questionMarkString.lastIndexOf(","))
+ ")";
}
private Object[][] removeNullsFromMultiArray(Object[][] multiValuedArray){
for(int i = 0; i < multiValuedArray.length; i++){
ArrayList<Object> list = new ArrayList<Object>(); // creates a list to store the elements !=
// null
for(int j = 0; j < multiValuedArray[i].length; j++){
if(multiValuedArray[i][j] != null){
list.add(multiValuedArray[i][j]); // elements != null will be added to the list.
}
}
multiValuedArray[i] = list.toArray(new Object[list.size()]); // all elements from list to an
// array.
}
return multiValuedArray;
}
/**
* Checks if the field name is a key.
*
* @param fieldName
* @return true if the field is a key.
*/
private boolean isKey(String fieldName){
boolean isKey = false;
for(String key : keys){
if(fieldName.equalsIgnoreCase(key)){
isKey = true;
}
}
return isKey;
}
/**
* @return the params
*/
public Object[] getParams(){
return params.toArray();
}
/**
* @return the sqlString
*/
public String getSqlString(){
return sqlString;
}
/**
* @param params the params to set
*/
public void setParams(List<Object> params){
this.params = params;
}
/**
* @param sqlString the sqlString to set
*/
public void setSqlString(String sqlString){
this.sqlString = sqlString;
}
}
And here is the method that you call to insert.
/**
* Insert batch of rows from model list into the Database.
*
* @param modelsList List of model objects representing the Database table.
* @return int Array of inserted rows.
* @throws SQLException
*/
public <T> int[] insertWithListOfModels(List<T> modelsList) throws SQLException{
ModelToSqlConverter modelConverter = new ModelToSqlConverter(modelsList);
QueryRunner qryRunner = new QueryRunner();
int[] inserts = null;
System.out.println("INSERT INTO " + modelsList.get(0).getClass().getSimpleName().toUpperCase() + " " + modelConverter.getSqlString());
System.out.println(Arrays.deepToString(modelConverter.getMultiValueParams()));
inserts = qryRunner.batch(connection,
"INSERT INTO " + modelsList.get(0).getClass().getSimpleName().toUpperCase() + " " + modelConverter.getSqlString(),
modelConverter.getMultiValueParams());
return inserts;
}
The way it works is, you submit either a list of models or just a single object. The method i provided is for updating with a list of models, but you get the idea. The constructor of modelToSqlConvertor handles everything. Then, with dbutils qryRunner you declair your sql and call the getters from modelToSqlConverter for the values and parameters.
To make this work yor model must be identical to your database table. I creted my models with JPA.
Hope this helps!
