rajasekhar rajasekhar - 2 years ago 79
Java Question

Insert query in mysql using metadata

I have a list which consists of the column name of a table

String tableName="employee";
ResultSet rs = Statement.executeQuery("SELECT * FROM"+tableName);
ResultSetMetaData rmd = rs.getMetaData();
int columnCount = rmd.getColumnCount();
List columnsList = new ArrayList();
for (int i = 1; i <= columnCount; i++) {

List values=[some values];

now columnsList =[emp_id, name, salary, department];

now i want to write insert query using these column names.But i don't want to hard code the column name i want to get these names from the list and to pass them to column names like

String subQuery= "insert into"+tableName+......here i want to get column names from list.....+.......here values from another list......;

is there any way to get this

Answer Source

If you already have the column names in a List then you can use StringUtils.join from Apache Commons Lang to build a comma-separated list and include that in your SQL statement:

// the following code assumes: import org.apache.commons.lang.*;
String tableName="employee";
List<String> columnsList = new ArrayList<String>();
columnsList.add("emp_id");  //
columnsList.add("name");    // sample data
columnsList.add("salary");  //
String colString = StringUtils.join(columnsList, ',');
String paramString = StringUtils.repeat(",?", columnsList.size());
String sql = String.format(
        "INSERT INTO %s (%s) VALUES (%s)", 
        tableName, colString, paramString.substring(1));

resulting in

INSERT INTO employee (emp_id,name,salary) VALUES (?,?,?)

Edit: Note that for safety's sake you probably should enhance the above code to enclose the table and column names in backticks, e.g.

INSERT INTO `employee` (`emp_id`, ...
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download