Java mysql execute update

Question

So I have a method that looks up a foreign key in a database. If the foreign key does not exist it will add an entry into the database. Now what I am doing from that point after inserting the new record, is re-querying again to get the foreign key. Is this overkill or is this the right way to do this? Thanks

private String getTestType(TestResult testResult) {
    String testTypeId = "";

    String query = String.format("SELECT id FROM test_types WHERE " +
            "name='%s'", testResult.getTestType());

    try {
        st = con.prepareStatement(query);
        rs = st.executeQuery();

        if (rs.next()) {
            testTypeId = rs.getString("id");
        } else {
            st = con.prepareStatement("INSERT INTO test_types (name, " +
                    "created_at) VALUES (?, ?)");
            st.setString(1, testResult.getTestType());
            st.setTimestamp(2, new java.sql.Timestamp(System
                    .currentTimeMillis()));

            st.executeUpdate();



            st = con.prepareStatement(query);
            rs = st.executeQuery();

            if (rs.next()) {
                testTypeId = rs.getString("id");
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
        System.out.println("There was an issue getting and or creating " +
                "test Type");
    }

    return testTypeId;
}

Sabuj Hassan · Accepted Answer · 2014-03-09 16:33:16Z

1

Since you are inserting a new row into DB, you have to do a query to get back the auto increment field(id). Currently they way you are doing is workable. But there are few alternatives in query:

Obtaining the id using last_insert_id():

rs = st.executeQuery("select last_insert_id() as last_id");
id= rs.getString("last_id");

Another approach can be doing the MAX over the id column of the table.

I believe these are will be much faster than your query as you are doing string comparison in where clause.

answered Mar 9, 2014 at 16:33

Sabuj Hassan

39.7k14 gold badges83 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Java mysql execute update

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related