Can I Insert Records into a SQL Table passing a bean as values using Apache Commons DBUtils

Question

I am using Java to access a MS SQL server. I am looking for a simple way to import data into various tables without having to write large sql querys. Some of these tables have 30 some columns.

I found the apache commons dbutils library and it works really well to read data from tables and map rows to beans. I can't seem to find any code that uses the same principlea to insert data into a table passing the bean as the data values

Is this possible?

Answer 1

Yes, it would be nice, however, I don't think it's possible. One way is to create a static function inside of your POJO class to get the "Insertion SQL statement". Although you must maintain the ordering between the two functions (insertion and parameters).

Employee.java

public static String getEmployeeInsertStatement()
{
    //if you add more fields, increase this integer
    int numberOfParameters = 5;

    String insertion = "INSERT INTO employee\n"
            + "(name\n"
            + ",address\n"
            + ",phone\n"
            + ",comments\n"
            + ",hire_date\n";            

    insertion += ")"
              + "VALUES\n"
              + getParameterQuestionMarks(numberOfParameters);

    return insertion;
}

// allow for multiple insertions
public static Object[][] getEmployeeParamters(List<Employee> employees)
{
    List<List<Object>> paramList = new ArrayList<>();

    for (Employee employee : employees)
    {
        List<Object> objectList = new ArrayList<>();

        objectList.add(employee.getName());
        objectList.add(employee.getAddress());
        objectList.add(employee.getPhone());
        objectList.add(employee.getComments());
        objectList.add(employee.getHire_date());

        paramList.add(objectList);


    }

    return paramList.stream().map(u -> u.toArray(new Object[0])).toArray(Object[][]::new);

}

public static String getParameterQuestionMarks(int number)
{
    String paramterMarks = "(";
    for (int x = 1; x <= number; x++)
    {
        paramterMarks += "?";
        if ((x + 1) <= number)
        {
            paramterMarks += ",";
        }
    }
    paramterMarks += ")";

    return paramterMarks;
}

Then in your SQLManager class, your insert method is as such:

public void insertEmployees(List<Employee> employees)
{
    Connection connection = createConnection();

    try
    {
        new QueryRunner().insertBatch(connection, Employee.getEmployeeInsertStatement(), new ScalarHandler<>(), Employee.getEmployeeParameters(employees));


    } catch (SQLException ex)
    {
        Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);

    } finally
    {
        try
        {
            DbUtils.close(connection);
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLManager.class.getName()).log(Level.SEVERE, null, ex);

        }

    }

}