How to insert values in database using Java where number of arguments to be inserted might vary

Question

I am creating a function, where the user needs to provide the table name and the path of a json file where the values are stored, and it will be inserted to a database: insertData(String tablename, String jsonFilePath)

Now if i would have known in advance what is the number of values to be inserted, it would have been easy to create a PreparedStatement something like this:

String strQuery = "INSERT INTO tablename VALUES (?, ?)";
            PreparedStatement stmt = conn.prepareStatement(strQuery);//prepare the SQL Query
            stmt.setString(1, "abc");//insert homeid
            stmt.setInt(2, "def");//insert featureid.
            stmt.executeQuery();//execute query

But since i don't know what is the tablename in advance, and the number of columns per table may vary, how do i go ahead with it because i need to create the SQL query string "strQuery" in advance where i need to put the ?

@IsaiahvanderElst I don't need to, but even if i don't, you are saying i will be looping and adding the question marks, later? Is there any shorter and smarter way, where i can skip adding the question marks? — RajSanpui
– RajSanpui, Commented Feb 25, 2015 at 16:26
I don't really see a benefit in using prepared statements in this scenario. You could just generate the statement on the fly — Isaiah van der Elst
– Isaiah van der Elst, Commented Feb 25, 2015 at 16:27
@IsaiahvanderElst Sorry, can you show, how do i do that? Am relatively new to Java — RajSanpui
– RajSanpui, Commented Feb 25, 2015 at 16:28
You could write code to generate the PreparedStatement code you posted, based on what you find in the JSON. You would use StringBuilders. — Gilbert Le Blanc
– Gilbert Le Blanc, Commented Feb 25, 2015 at 16:28

Isaiah van der Elst · Accepted Answer · 2015-02-25 16:45:39Z

Because the structure of the insert statement is likely to change, the statements can be generated on the fly. There's no real benefit to using prepared statements if you're not going to reuse it. The following is an example generating the statements on the fly using a statement (as opposed to a prepared statement:

public boolean insert(Connection conn, String table, List<String> columns, List<String> values) throws SQLException {
    StringBuilder sb = new StringBuilder();

    sb.append("INSERT INTO ");
    sb.append(table);
    sb.append(" (");

    // add columns
    Iterator<String> it = columns.iterator();
    sb.append(it.next());
    while(it.hasNext()) {
        sb.append(',');
        sb.append(it.next());
    }

    sb.append(") VALUES (");

    // add values
    it = values.iterator();
    sb.append(it.next());
    while(it.hasNext()) {
        sb.append(',');
        sb.append(it.next());
    }

    sb.append(");");

    // insert
    try (Statement stmt = conn.createStatement()) {
        return stmt.execute(sb.toString());
    }
}

Note that this code does not perform the proper validation. For example, an error will occur if the any of the two lists are empty.

antonio · Accepted Answer · 2015-02-25 16:37:59Z

You can use a solution like this to dynamically generate the Prepared Statement (the DataField array can also be used for the setXXX):

public String generateStatement(String tablename, DataField[] datafields) {
    String stmt = "INSERT INTO " + tablename + " (";

    for (DataField dataField : datafields) {
        stmt += dataField.getName() + ",";
    }

    // Remove last ','
    stmt = stmt.substring(0, stmt.length() - 1);
    stmt += ") VALUES(";

    for (DataField dataField : datafields) {
        stmt += "?,";
    }

    // Remove last ','
    stmt = stmt.substring(0, stmt.length() - 1);
    stmt += ")";

    return stmt.toString();
}

public class DataField {
    private String name;
    private String value;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }
}

Improvements to be done:

Accept several type of parameters (String, Integer, etc...). You can create an ENUM on DataField and a type on datafield to manage this.
Check if the parameters passed to generateStatement are correct
Use StringBuffer instead of String to generate the Prepared Statement
Create an object to hold both the table name and the array of DataField if needed

Collectives™ on Stack Overflow

How to insert values in database using Java where number of arguments to be inserted might vary

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related