Insert a record with a value partly from another table

Question

I have two tables, Proteins and Species. Protein has Species.Id as foreign key constraint. When I insert the data, I know the name of the species that protein belongs to, but not the Id. So I do the following:

    PreparedStatement query = connection.prepareStatement(
        "SELECT Id FROM Species WHERE ShortName = ?");
    query.setString(1, protein.getSpecies().getShortName());
    ResultSet result = query.executeQuery();
    if (result.next()) {
        PreparedStatement statement = connection.prepareStatement(
        "INSERT INTO Proteins(SpeciesId, UniProtKBAccessionNumber) VALUES (?, ?)");

        statement.setString(1, result.getString(1));
        statement.setString(2, protein.getUniProtKBAccessionNumber().toString());

        statement.execute();
    }
    else
        throw new IllegalArgumentException("The species of this protein is not recognized!");

Basically, I get the Id that corresponds to the name, and then insert the protein. This seems very clumsy in many ways. Is there a more elegant way to achieve this? I am working on an SQLite database.

Answer 1

Try this

INSERT INTO Proteins(SpeciesId, UniProtKBAccessionNumber) VALUES ((SELECT Id FROM Species WHERE ShortName = ?), ?)

And then use Shortname and UniProtKBAccessionNumber as your parameters