CODEWITHSUNDEEP
javasqldatabase
Cao Felix
Cao Felix

Reputation: 329

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");
    }

    try{

        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]);
        }
        prepStmt.executeUpdate();
    }
    finally {

        closeStatement(prepStmt);
        closeConnection(con);
    }
}

For example, use case:

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

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

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?

Upvotes: 1

Views: 2145

Answers (2)

Haifeng Zhang
Haifeng Zhang

Reputation: 31895

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 (resultSet.next()) {
  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

Upvotes: 1

Chetan Verma
Chetan Verma

Reputation: 873

Your scenario will work only in case when you are passing value for every column, if there is any nullable column in your table and you don't want to insert value on that it will not work and will throw sql exception as you have to specify the column name too in insert statement but when in your case you can pass Object array instead of String array and then call the appropriate setXXX according to the class type of parameter which your can fine from getClass method.

Upvotes: -1

Related Questions