Java PostgreSQL arrayList<Object[]> insert code- applying to any number of colums

Question

I've got a method in Java for inserting an ArrayList into PostgreSQL.

public void postgreSQLInsert(String tblname, String col1, String col2, String col3) throws SQLException, IOException, InterruptedException {
    ArrayList<Object[]> insertList = voltdbresultarray;

    if (insertList == null) {
    } else {
        for (Object[] columnValues : insertList) {
            try {
                System.out.println(columnValues[0]);
                System.out.println(columnValues[1]);
                System.out.println(columnValues[2]);
                String sql = "INSERT INTO " + tblname + "(" + col1 + "," + col2 + "," + col3 + ") " + "VALUES (" + columnValues[0] + ", '" + columnValues[1] + "', " + columnValues[2] + " );";
                postgresStmt.executeUpdate(sql);
            } catch (PSQLException e) {
                System.out.println("Duplicate!");
                continue;
            }
        }
    }
}

Col1, col2, and col3 are all column name strings created elsewhere, and voltdbresultarray is a static array given values earlier. It all works fine, but only for object arrays with three columns. I'd like to make it applicable to object arrays with any number of columns, but I can't figure out how. Is there a way to have it take any number of column arguments and create the correct SQL query from that?

Answer 1

If I understand your question, you could use varargs and change the method signature from

public void postgreSQLInsert(String tblname, String col1, String col2, String col3)

to

public void postgreSQLInsert(String tblname, String... columns)

and then something like

if (insertList == null) {
} else {
  StringBuilder sb = new StringBuilder();
  sb.append("INSERT INTO ").append(tblname);
  sb.append(" (");

  for (int i = 0; i < columns.length; i++) {
    if (i != 0) {
      sb.append(", ");
    }
    sb.append(columns[i]);
  }
  for (Object[] columnValues : insertList) {
   StringBuilder valueSB = new StringBuilder();
   for (int i = 0; i < columnValues.length; i++) {
      if (i != 0) {
        valueSB.append(", ");
      }
      System.out.println(columnValues[i]);
      valueSB.append("'").append(columnValues[i]).append("'");
    }

    try {
      String sql = sb.toString()
          + "VALUES (" +  valueSB.toString() + ")";
      postgresStmt.executeUpdate(sql);
    } catch (PSQLException e) {
      System.out.println("Duplicate!");
      continue;
    }
  }