1
\$\begingroup\$

I need to make a UI where I can edit/insert/remove records of my SQL tables through my app. Edits and deletes of records works fine except of insertions when it comes to tables with auto-incremented PKs. I created a combobox populated with the names of tables and by using the sql schema I try to generate dynamic code for each table.

This method returns the model for the box.

public static String[] getPartType() {
        ResultSet rs = null;
        List<String> list = new ArrayList<String>();
        list.add("None");
        try {
            stmt = ccd_db_connection.getConnection().prepareStatement(
                    "SELECT table_name FROM information_schema.tables t WHERE table_catalog = 'xxxx' AND t.TABLE_TYPE='BASE TABLE' ");
            rs = stmt.executeQuery();
            while (rs.next()) {
                list.add(rs.getString(1));
            }
            stmt.close();
            rs.close();
        } catch (SQLException e) {
            new ConnectionMessage(e);
        }
        return list.toArray(new String[0]);
    }

After selecting the table I populate my custom JTable and project the corresponding records customizing just the FROM part of the query. To keep this post sort I'll jump right on insertion since everything else seems to be working.

This is the method where I do the insertion

public static void insertParts(String tableName, Vector<String> colNames, Vector<Object> data) throws SQLException {
    StringBuilder query = new StringBuilder("INSERT INTO " + tableName + " (");
    for (int j = 0; j < colNames.size(); j++) {
        query.append(colNames.get(j));
        if (j != (colNames.size() - 1)) {
            query.append(", ");
        } else {
            query.append(") VALUES(");
        }
    }
    for (int i = 0; i < colNames.size(); i++) {
        query.append("?");
        if (i != (colNames.size() - 1)) {
            query.append(", ");
        } else {
            query.append(")");
        }
    }
    stmt = ccd_db_connection.getConnection().prepareStatement(query.toString());
    for (int j = 0; j < colNames.size(); j++) {
        stmt.setObject(j + 1, data.get(j));
    }
    stmt.execute();
    stmt.close();
}

And here is the big Question(s) of mine.

  • Am I trying to reinvent the wheel?
  • Is this dynamic usage of queries bad practice and should I code specific methods for each and every table separately?
  • If this code is acceptable ... The insertion works fine except on tables where there is a primary key with auto incrementation. and i think thats cause setObject sets null while the query should only have the explicit values. Is there any way to bypass that.
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.