I'm looking for the best way to insert data into
MariaDB with JDBC. Please take a look at the following schema
CREATE TABLE color ( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL ); CREATE TABLE ball ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, color SMALLINT UNSIGNED, KEY color (color), CONSTRAINT color_fk FOREIGN KEY (color) REFERENCES color(id) );
and test data
INSERT INTO color (name) VALUES ('green'), ('yellow'), ('red'); INSERT INTO ball (name,color) VALUES ('Big Yellow Ball',(SELECT id FROM color WHERE name = 'yellow')); -- OK INSERT INTO ball (name,color) VALUES ('Small Purple Ball',(SELECT id FROM color WHERE name = 'purple')); -- DOESN'T EXIST
and as result NULL color value is inserted for Purple Ball
MariaDB [zimbra]> select * from ball; +----+-------------------+-------+ | id | name | color | +----+-------------------+-------+ | 1 | Big Yellow Ball | 7 | | 2 | Small Purple Ball | NULL | +----+-------------------+-------+
Thus I can see the following solution
- try to read required color
- then if it doesn't exist insert a new record for it
- then extract generated key
- prepare and execute next query/statement with it
which doesn't look very elegant as it requires from 2 to 3 statements preparation (in Java), with further execution and results extraction for each of them. This becomes even more ugly taking into account that there are more than one such referenced tables in my real case. Another reason to optimize this is performance: to send less SQL requests and to execute less PreparedStatements).
Ideally, I would like to be able to construct such SQL insert statement which would bother about non-existent referencing values.
Otherwise, if it is impossible, what other solution I should look at which would allow me to simplify my Java code?