Inserting into mysql table from Java code

This post gives sample implementation of an interface which will insert records in a given table.

Assumptions:
1. mysql_connector driver is in build path
2. List of records is built off line.
3. Database connection is already made.

Procedure:
As shown in the snippet of code, the method takes 5 parameters, as mentioned in the comment.

/**
* @param tableName name of the table
* @param numValues number of values to insert < = num columns
* @param dataTypes their datatypes (string, int etc)
* @param columnNames column names to add values for
* @param values actual values in same sequence
*/
public void insertIntoDb(String tableName,
int numValues,
ArrayList_of_String dataTypes,
ArrayList_of_String columnNames,
ArrayList_of_ArrayList_of_String values) {

One you have a list of column names, you can easuly concatenate them to form a string say,

String col = "(col1, col2, col3)";

Similarly, for values, using numValues, you can easily form a string say,

String val = "(?, ?, ?)";

Once this is done, make a query string like:

String query = "insert into " + tableName + " " + col + " values " + val + ";";

This will form a quert string similar to following:

"insert into myTable (name, id) values (?, ?);"

Then, write a loop which will iterate through the given ArrayList of values (actual records) and using PreparedStatement interface, fill in the values for '?' in the query string. Here you will use dataTypes provided; for example, if the corresponding data type is "string", write:

cs.setString(index_of_corresponding_?, current);

else for "integer", write:

cs.setInt(index_of_corresponding_?, Integer.parseInt(current));

where "current" is the current piece of text that you can get from ArrayList of records i.e. values.

You can verify whether your final prepared statement is correctly built, using toString() method for PreparedStatement interface. It will show something like:

"objectID1: insert into myTable (name, id) values ('abc', 1);"
"objectID2: insert into myTable (name, id) values ('xyz', 2);"

Then invoke executeQuery() method of the interface PreparedStatement which will actually insert these records in the table.

Easy!! No? :)