CODEWITHSUNDEEP
javamysqlsqljdbc
Vins
Vins

Reputation: 411

Insert int value of Resultset in SQL-Database

I'm working with a MySQL-Server and I'm trying to select an ID from another table and insert that ID in a table but it doesn't work all the time.

Code:

public void submit() throws Exception {

    Connection connection = getConnection();

    Statement stmt = connection.createStatement();
    Statement stmt1 = connection.createStatement();

    ResultSet asset_id = stmt.executeQuery("SELECT id FROM cars.asset_type WHERE asset_type.name =" + "'" + sellables.getValue()+ "'");

    while (asset_id.next()) {
        System.out.println(asset_id.getInt("id"));
    }

    double value = parseDouble(purchased.getText());
    System.out.println(value);

    LocalDate localDate = purchased_at.getValue();

    String insert = "INSERT INTO asset (type_id, purchase_price, purchased_at) VALUES ('"+ asset_id + "','" + value +"','" + localDate +"')";
    stmt1.executeUpdate(insert);
}

I keep getting the same error message.

Caused by: java.sql.SQLException: Incorrect integer value: 'com.mysql.cj.jdbc.result.ResultSetImpl@1779d92' for column 'type_id' at row 1

Upvotes: 0

Views: 191

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221106

There's no value in doing two client/server roundtrips in your case, so use a single statement instead:

INSERT INTO asset (type_id, purchase_price, purchased_at)
SELECT id, ?, ?
FROM cars.asset_type 
WHERE asset_type.name = ?

If you really want to insert only the last ID from your SELECT query (as you were iterating the SELECT result and throwing away all the other IDs), then use this query instead:

INSERT INTO asset (type_id, purchase_price, purchased_at)
SELECT id, ?, ?
FROM cars.asset_type 
WHERE asset_type.name = ?
ORDER BY id DESC -- I guess? Specify your preferred ordering here
LIMIT 1

Or with the JDBC code around it:

try (PreparedStatement s = connection.prepareStatement(
    "INSERT INTO asset (type_id, purchase_price, purchased_at) " +
    "SELECT id, ?, ? " +
    "FROM cars.asset_type " +
    "WHERE asset_type.name = ?")) {

    s.setDouble(1, parseDouble(purchased.getText()));
    s.setDate(2, Date.valueOf(purchased_at.getValue()));
    s.setString(3, sellables.getValue());
}

This is using a PreparedStatement, which will prevent SQL injection and syntax errors like the one you're getting. At this point, I really really recommend you read about these topics!

Upvotes: 2

Related Questions