Java and RDBMS Married with issues Database constraints Speaker - PowerPoint PPT Presentation

Java and RDBMS Married with issues Database constraints

Speaker Jeroen van Schagen

Situation store Java 
 Relational 
 Application Database retrieve JDBC

JDBC • J ava D ata b ase C onnectivity • Data Access API ( java.sql , javax.sql ) • JDK 1.1 (1997) • Relational Database • Many implementations

Connection connection = …; 
 Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (‘Jan’)”; 
 statement.executeUpdate(sql);

Connection connection = …; 
 Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (‘Jan’)”; 
 statement.executeUpdate(sql);

Database User Maintain data name : varchar(3) 
 NOT-NULL, 
 UNIQUE Name can only have 
 up to 3 characters Name is required Name can only occur once

Constraint types Check Not null Unique key Length Type Foreign key Primary key

User name : varchar(3) 
 NOT-NULL, 
 UNIQUE Connection connection = …; 
 Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (‘Jan’)”; 
 statement.executeUpdate(sql); 
 What happens? Assuming the user table is empty

User name : varchar(3) 
 NOT-NULL, 
 UNIQUE Connection connection = …; 
 Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (‘Jan’)”; 
 statement.executeUpdate(sql); 
 1 row updated

User name : varchar(3) 
 NOT-NULL, 
 UNIQUE Connection connection = …; 
 Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (‘Jan’)”; 
 statement.executeUpdate(sql); 
 statement.executeUpdate(sql); What will happen? What happens?

User name : varchar(3) 
 NOT-NULL, 
 UNIQUE Connection connection = …; 
 Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (‘Jan’)”; 
 statement.executeUpdate(sql); 
 statement.executeUpdate(sql); SQLIntegrityConstraint 
 What will happen? ViolationException

executeUpdate(sql) INSERT return 1 Inserted 1 Applicatio JDBC Database n executeUpdate(sql) INSERT throw Unique violation SQLIntegrityConstraint 
 ViolationException

User name : varchar(3) 
 NOT-NULL, 
 UNIQUE Connection connection = …; 
 Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (‘Jan’)”; 
 statement.executeUpdate(sql); 
 statement.executeUpdate(sql);

User name : varchar(3) 
 NOT-NULL, 
 UNIQUE Connection connection = …; 
 Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (‘Jan’)”; 
 try { 
 statement.executeUpdate(sql); 
 statement.executeUpdate(sql); 
 } catch (SQLIntegrityConstraintViolationException e) { 
 throw new RuntimeException(“Name already exists”); 
 }

User name : varchar(3) 
 NOT-NULL, 
 UNIQUE Connection connection = …; 
 Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (NULL)”; 
 statement.executeUpdate(sql); What happens?

User name : varchar(3) 
 NOT-NULL, 
 UNIQUE Connection connection = …; 
 Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (NULL)”; 
 statement.executeUpdate(sql); SQLIntegrityConstraint 
 ViolationException

User name : varchar(3) 
 NOT-NULL, 
 UNIQUE Connection connection = …; 
 Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (NULL)”; 
 try { 
 statement.executeUpdate(sql); 
 } catch (SQLIntegrityConstraintViolationException e) { 
 throw new RuntimeException(“Name is required”); 
 throw new RuntimeException(“Name already exists”); 
 }

Unique key violation SQLIntegrityConstraint 
 ViolationException Not null violation

Unique key violation SQLIntegrityConstraint 
 ViolationException Not null violation Which was violated?

SQLException + getSQLState() : int + getMessage() : String SQLIntegrityConstraint 
 ViolationException

SQLException + getSQLState() : int + getMessage() : String SQLIntegrityConstraint 
 ViolationException

State Name 23 000 Integrity constraint 23 001 Restrict violation 23 502 Not null violation 23 503 Foreign key violation 23 505 Unique violation 23 514 Check violation

User name : varchar(3) 
 NOT-NULL, 
 UNIQUE Connection connection = …; 
 Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (NULL)”; 
 try { 
 statement.executeUpdate(sql); 
 } catch (SQLIntegrityConstraintViolationException e) { 
 if (e.getSQLState() == 23502) { 
 throw new RuntimeException(“Name is required”); 
 } else if (e.getSQLState() == 23505) { 
 throw new RuntimeException(“Name already exists”); 
 } 
 }

User name : varchar(3) 
 NOT-NULL, 
 UNIQUE Connection connection = …; 
 Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (NULL)”; 
 try { 
 statement.executeUpdate(sql); 
 } catch (SQLIntegrityConstraintViolationException e) { 
 if (e.getSQLState() == 23502) { 
 throw new RuntimeException(“Name is required”); 
 } else if (e.getSQLState() == 23505) { 
 throw new RuntimeException(“Name already exists”); 
 } 
 } Complicated Boilerplate Assumptions

Multiple not-null values User name : varchar(3) 
 NOT-NULL, 
 UNIQUE email : varchar(30) 
 NOT-NULL, 
 UNIQUE

Multiple not-null values Multiple unique values Which was violated? User name : varchar(3) 
 NOT-NULL, 
 uk_user_name UNIQUE email : varchar(30) 
 NOT-NULL, 
 uk_user_email UNIQUE

SQLException + getSQLState() : int + getMessage() : String SQLIntegrityConstraint 
 ViolationException

Vendor messages They are all di fg erent Oracle ORA-00001: unique constraint (GOTO.UK_USER_NAME) violated\n MySQL Duplicate entry 'Jan' for key 'uk_user_name' HSQL integrity constraint violation: unique constraint or index violation; 
 UK_USER_NAME table: USER PostgreSQL ERROR: duplicate key value violates unique constraint \"uk_user_name\" 
 Detail: Key (name)=(Jan) already exists. H2 Unique index or primary key violation: "UK_USER_NAME_INDEX_1 ON GOTO.USER(NAME)"; 
 SQL statement:\ninsert into user (name) values (?) [23505-171]

Vendor messages The info is there Oracle ORA-00001: unique constraint (GOTO.UK_USER_NAME) violated\n MySQL Duplicate entry 'Jan' for key 'uk_user_name' HSQL integrity constraint violation: unique constraint or index violation; 
 UK_USER_NAME table: USER PostgreSQL ERROR: duplicate key value violates unique constraint \"uk_user_name\" 
 Detail: Key (name)=(Jan) already exists. H2 Unique index or primary key violation: "UK_USER_NAME_INDEX_1 ON GOTO.USER(NAME)"; 
 SQL statement:\ninsert into user (name) values (?) [23505-171]

Extract violation info • Message Just too di ffj cult • Pattern matching Focus on application logic • Vendor specific

Concrete exception classes UniqueKeyViolationException NotNullViolationException JDBC needs a better exception API 
 ( for integrity constraints ) Access to constraint info getColumnName() getConstraintName()

Workaround

Prevent violations

Prevent violations • Data integrity checks in application layer.

Prevent not-null if (user.getName() == null) { 
 throw new RuntimeException(“Name is required”); 
 }

Javax validation public class User { 
 @NotNull 
 private String name; 
 } No SQL exception Conveys Less database interaction

Less interaction throw new RuntimeException Applicatio Database n

Duplication Application Database User User @NotNull 
 name : varchar(3) 
 private String name NOT-NULL, 
 UNIQUE

Duplication Application Database User User @NotNull 
 name : varchar(3) 
 private String name NOT-NULL, 
 UNIQUE Kept in sync Unexpected SQL exceptions

Prevent unique violation • Complicated • Depends on other rows

id name NULL Testable in isolation

id name Jan

id name Jan Requires data users id name Piet 1 2 Jan 3 Henk

No SQL exceptions if (countUsersWithName(user.getName()) > 0) { 
 throw new RuntimeException(“Name already exists”); 
 } private int countUsersWithName(String name) { 
 return jdbcTemplate.queryForObject( 
 “SELECT COUNT(1) FROM user where name = ?”, 
 name, Long.class); 
 } Extra query Not atomic

Problem: Not atomic Thread 1 COUNT WHERE name = ‘Jan’ return 0 Thread 2 INSERT (name) VALUES (‘Jan’) Applicatio Database n INSERTED 1 Uncaught Thread 1 Unexpected INSERT (name) VALUES (‘Jan’) Decision on 
 Unique key violation old data

Recap Lack proper solution Not null No SQL exceptions Duplication Error prone Unique key No SQL exceptions Extra query Error prone

Solution Ja va R epository B ridge - JaRB


 Databases are good at 
 maintaining integrity; 
 let them!