Jdbctemplate utilities for insert statement

Question

Is there any existing utility to do in a better/faster way a DB insert? Now this is what I'm using (the are a lot of fields, I truncated the field list):

public void insert(Ing ing){

        String[] fields=new String[]{"field1","field2","field3"};
        Object[] params=new Object[]{ing.getField1(),ing.getField2(),ing.getField3()};

        String[] paramsPH=new String[fields.length];
        for(int i=0;i<paramsPH.length;i++) paramsPH[i]="?";

        String sql= "INSERT INTO ing("+StringUtils.join(fields,",")+") VALUES ("+StringUtils.join(paramsPH,",")+");";

        getJdbcTemplate().update(sql,params);
    }

Answer 1

Check this :

import java.util.LinkedHashMap;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;

JdbcTemplate jt = new JdbcTemplate...// some instance... ;
String tableName="nameDateTable";//your happy table

LinkedHashMap<String,Object>map= new LinkedHashMap<String,Object>();
map.put("col1Name","blabla"); //column name and value 
map.put("dateAdd",new Date());//column name and value 
// etc..

//  You can place any map here (LinkedHashMap!). Here is a magical query:

String sql = "INSERT INTO "+tableName+" (\""+StringUtils.join(map.keySet(), "\",\"")+"\") VALUES ("+StringUtils.repeat("?", ",", map.size())+");";
jt.update(sql, map.values().toArray());

The most important in this solution is

 String sql = "INSERT INTO "+tableName+" 
(\""+StringUtils.join(map.keySet(), "\",\"")+"\") VALUES ("+StringUtils.repeat("?", ",", map.size())+");";
jt.update(sql, map.values().toArray());

and LinkedHashMap.

Answer 2

In my Spring JdbcTemplate projects, I ususally create a generic BaseDao<T> class that has a method saveObject(T obj).
to achieve this, I use SimpleJdbcInsert like this:

//Constants, from BaseDAO interface that this method implements
String TABLE_NAME = "tableName";
String GENERATED_KEY = "generatedKey";

/**
 * Save an object using a {@link BaseObjectMapper} returned from the method {@link #getObjectMapper()}
 * Returns the generated key if the map generated by the {@link BaseObjectMapper} contains an entry for {@value #GENERATED_KEY}
 * @param the object to be saved
 */
@Override
public int saveObject(T obj){
    MapSqlParameterSource params = new MapSqlParameterSource();
    //the mapper must transform an object to a map
    //and add the table name where to insert, and if any, a generated key
    Map<String, Object> paramsMap = getObjectMapper().mapObject(obj);
    String table = (String) paramsMap.remove(TABLE_NAME);
    if(table == null){
        throw new IllegalArgumentException("The ObjectMapper of "+obj.getClass()+" must return the table name among the result map of mapObject method");
    }

    String generatedKey = (String) paramsMap.remove(GENERATED_KEY);

    String[] colNames = paramsMap.keySet().toArray(new String[paramsMap.keySet().size()]);

    for(String col: colNames){
        params.addValue(col, paramsMap.get(col));
    }
    //You can have it as a class attribute and create it once the DAO is being instantiated
    SimpleJdbcInsert genericJdbcInsert = new SimpleJdbcInsert(jdbcInsert.getJdbcTemplate().getDataSource())
                                        .withSchemaName(currentSchema).withTableName(table)
                                        .usingColumns(colNames);

    if(generatedKey != null){
        genericJdbcInsert = genericJdbcInsert.usingGeneratedKeyColumns(generatedKey);
        return genericJdbcInsert.executeAndReturnKey(paramsMap).intValue();
    }else{
        genericJdbcInsert.execute(params);
    }
    return 0;
}


protected BaseObjectMapper<T> getObjectMapper(){
//Implement it in your concrete DAO classes
    throw new UnsupportedOperationException("You must implemnt this method in your concrete DAO implementation");
}

Here is the BaseObjectMapper interface:

import java.util.Map;

import org.springframework.jdbc.core.RowMapper;

import com.atlasaas.ws.dao.BaseDao;
import com.atlasaas.ws.entities.BaseEntity;

public interface BaseObjectMapper<T extends BaseEntity> extends RowMapper<T>{

/**
 * Method to transform an object into a {@link Map}
 * The result map must contain all columns to be inserted as keys
 * It also must contain the Table name corresponding to the given object
 * The table name must be associated to the key of value: {@link BaseDao#TABLE_NAME}
 * Optionally, if you want your save methods to return a generated primary key value
 * you should include an entry referencing the the generated column name. This entry
 * must then be associated to the key of value: {@link BaseDao#GENERATED_KEY}
 * @param obj The object to be transformed
 * @return the result of this object transformation
 */
    Map<String, Object> mapObject(T obj);
}

If you really want to use SQL in your code, you can use:

org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations#(String sql, SqlParameterSource paramSource)

where your SQL string would be something like this: insert into SOME_TABLE(COL1,COL2,COL3) values (:col1Val,:col2Val,:col3Val) and your SqlParameterSource is built this way:

MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("col1Val", val1);
params.addValue("col2Val", val2);
params.addValue("col3Val", val3);

I hope this helps

Answer 3

You can use parameterized SQL to make it a bit simpler

Your code would look something like this

String sql = "INSERT INTO ing(field1, field2, field3) values(?, ?, ?)"; 
Object[] params=new Object[]{ing.getField1(),ing.getField2(),ing.getField3()};
getJdbcTemplate().update(sql,params);