Cao Felix Cao Felix - 1 year ago 74
SQL Question

how to insert into database table with different data types using Java SQL

I have a Java SQL question relative to the insert method. If I have a database with multiple tables, I want to write a single insert method that can insert a row of data into any selected table for any type of relational database. If some tables contain different data types, for examples (integer, date, varchar, and etc.). My codes:

public void insertData(String tablename, String... values)
throws SQLException {

Connection con = null;
PreparedStatement prepStmt = null;

if (values.length == 0) {

throw new SQLException("Must supply values");


con = getConnection();

String sql = "insert into "+tablename+" values(";

for (int i = 0; i < values.length; i++) {
sql += "?";
if (i != values.length-1) sql += ",";

sql += ")";

prepStmt = con.prepareStatement(sql);

for (int i = 0; i < values.length; i++) {
prepStmt.setString(i, values[i]);
finally {


For example, use case:

Table Teacher has int id, varchar(50) name, int age, text subject, int

Table Student has int id, varchar(50) name, date dateofbirth, text

Table Class has int id, text subject;

If I use:

insertData(Teacher, new String[] {"10", "Cass", "32", "Math", "10222"});

Will that data insert into the table successfully? I heard the database is only using varchar. If that is the case, then I don't have to worry about the type. If the type is varchar, should I use prepStmt.setString()?

I need to support multiple different types of database, such like SQL Server, MySQL, Oracle and others. Will that one insertData() method work for all different databases?

Answer Source

The database meta data can tell you the details of the table.

What you can do is using DatabaseMetaData to get the column names, types and size, then you can choose PreparedStatement corresponding setXXX methods:

DatabaseMetaData metadata = connection.getMetaData();
ResultSet resultSet = metadata.getColumns(null, null, "mytable", null);  // table name is mytable
while ( {
  String name = resultSet.getString("COLUMN_NAME");
  String type = resultSet.getString("TYPE_NAME");
  int size = resultSet.getInt("COLUMN_SIZE");

Another way is using ResultSetMetaDatabut you have to query from the table to get the ResultSet

  ResultSet rs = stmt.executeQuery(query);
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount() ; // returns the number of columns
    for(int i = 1; i < columnCount + 1; i++){
        rsmd.getColumnName(i); //get name

After you get the table's information(column name, type...), then you can choose proper query to update the table

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download