Let's say I have a few hypothetical tables: User, Item, and Sale.
CREATE TABLE User (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL
);
CREATE TABLE Item (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
upc VARCHAR(255) NOT NULL,
description VARCHAR(255) NOT NULL,
price DECIMAL(5,2) NOT NULL
userId INT NOT NULL,
FOREIGN KEY(userId) REFERENCES User(id)
);
CREATE TABLE Sale (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
quantity INT NOT NULL,
total DECIMAL(5,2) NOT NULL,
itemId INT NOT NULL,
FOREIGN KEY(itemId) REFERENCES Item(id)
);
Each user can add multiple items, and can sell multiple quantities of each item. A record of each sale is going to go into the Sale table, but I have to make sure that the Item ID being entered into the Sale table is actually owned by the User who is "creating" the entry.
I've thought about a couple of ways of doing this from an application layer (e.g., JDBC).
Do a
SELECT
to make sure the User owns that Item.SELECT id FROM Item WHERE id = ? AND userId = ?
If there is a match (i.e., rows returned), the User owns that Item and can insert a Sale record for that Item. This method seems a bit inefficient, however, since I have to do multiple, separate queries in order to accomplish one task. Having a connection pool (and thus reusing the same connection for each query) will help performance a little, but I'm not sure by how much.
Do a "conditional
INSERT
" viaINSERT ... SELECT
:INSERT INTO Sale(quantity, total, itemId) SELECT 4, 5.00, 3 FROM Dual WHERE EXISTS(SELECT id FROM Item WHERE id = ? AND userId = ?);
I really like the idea of this option, but there's an outstanding issue that I haven't been able to work around:
The query itself would be done from an application. Parts of the insert statement are raw values, which are not known until the last second. And since the only way to do a "conditional insert" is to SELECT
data from some table (dummy or otherwise), you can't put ?
placeholders for a prepared statement for column names.
In other words, the 4, 5.00, and 3 in the above statement are raw values, and the only way I know of to get them into the SQL string is to do concatenation:
String sql = "INSERT INTO Sale(quantity, total, itemId) SELECT "
+ quantity + ", " + total + ...;
Which leaves the door wide open to potential SQL injection attacks. It's a bit trickier to do if the Java variables quantity
and total
are numeric data types (i.e., can't have quotes), but it's still a loophole that I don't want to leave open.
Is there a good way to accomplish what I'm trying to do efficiently in one SQL statement? Or is the best way option (1) above?