Java and RDBMS
Married with issues
Speaker
Situation
store
retrieve
Java
Application
Relational
Database
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);
User
name : varchar(3) NOT-NULL, UNIQUE
Name can only have
up to 3 characters
Name is required
Name can only occur once
Database
Maintain data
Constraint types
Not null
Type
Length
Primary key
Foreign key
Unique key
Check
Connection connection = …;
Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (‘Jan’)”;
statement.executeUpdate(sql);
User
name : varchar(3) NOT-NULL, UNIQUEWhat happens?
Connection connection = …;
Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (‘Jan’)”;
statement.executeUpdate(sql);
User
name : varchar(3) NOT-NULL, UNIQUE1 row updated
Connection connection = …;
Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (‘Jan’)”; statement.executeUpdate(sql);
statement.executeUpdate(sql);
What will happen?
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?
User
name : varchar(3) NOT-NULL, UNIQUESQLIntegrityConstraint
ViolationException
Applicatio n JDBC Database executeUpdate(sql) INSERT return 1 Inserted 1 executeUpdate(sql) INSERT Unique violation throw SQLIntegrityConstraint ViolationException
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, UNIQUEConnection 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);
User
name : varchar(3) NOT-NULL, UNIQUEWhat happens?
Connection connection = …;
Statement statement = connection.createStatement(); String sql = “INSERT INTO user (name) VALUES (NULL)”;
statement.executeUpdate(sql);
User
name : varchar(3) NOT-NULL, UNIQUESQLIntegrityConstraint
ViolationException
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”); }
User
name : varchar(3) NOT-NULL, UNIQUE
Unique key violation
SQLIntegrityConstraint
ViolationException
Unique key violation
Not null violation
SQLIntegrityConstraint
ViolationException
SQLException
+ getSQLState() : int
+ getMessage() : String
SQLIntegrityConstraint
ViolationException
SQLException
+ getSQLState() : int
+ getMessage() : String
SQLIntegrityConstraint
ViolationException
State
Name
23000
Integrity constraint
23001
Restrict violation
23502
Not null violation
23503
Foreign key violation
23505
Unique violation
23514
Check violation
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, UNIQUEConnection 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, UNIQUEMultiple not-null values
User
name : varchar(3) NOT-NULL, UNIQUE email : varchar(30) NOT-NULL, UNIQUEuk_user_name
uk_user_email
Multiple not-null values
Multiple unique values
User
name : varchar(3) NOT-NULL, UNIQUE email : varchar(30) NOT-NULL, UNIQUESQLException
+ getSQLState() : int
+ getMessage() : String
SQLIntegrityConstraint
ViolationException
Vendor messages
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.
Oracle
ORA-00001: unique constraint (GOTO.UK_USER_NAME) violated\n
MySQL
Duplicate entry 'Jan' for key 'uk_user_name'
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
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.
Oracle
ORA-00001: unique constraint (GOTO.UK_USER_NAME) violated\n
MySQL
Duplicate entry 'Jan' for key 'uk_user_name'
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]
•
Message
•
Pattern matching
•
Vendor specific
Extract violation info
Just too difficult
JDBC needs a better exception API
( for integrity constraints )
Concrete exception classes
UniqueKeyViolationException NotNullViolationException
Access to constraint info
getColumnName() getConstraintName()
•
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;
}
Conveys
No SQL exception
Applicatio
n Database
throw new RuntimeException
User
@NotNull
private String name
User
name : varchar(3) NOT-NULL, UNIQUEApplication
Database
Duplication
User
@NotNullprivate String name
User
name : varchar(3) NOT-NULL, UNIQUEApplication
Database
Kept in sync
Unexpected SQL
exceptions
Duplication
•
Complicated
•
Depends on other rows
id
name
NULL
id
name
Jan
id
name
id
name
users
Piet
Henk
Jan
1
2
3
Jan
Requires data
No SQL exceptions
Extra query
Not atomic
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);
}
Problem: Not atomic
Applicatio
n Database
INSERT (name) VALUES (‘Jan’) INSERTED 1
Thread 2
COUNT WHERE name = ‘Jan’ return 0
Thread 1
INSERT (name) VALUES (‘Jan’)
Unique key violation
Thread 1
Decision on
old data
Unexpected
Recap
No SQL exceptions
Duplication
Error prone
Not null
Extra query
Unique key
No SQL exceptions
Error prone
Solution
Databases are good at
maintaining integrity;
Catch exception
Prevent exception
Testable in isolation
Not null
Type
Length
Check
Unique key
Foreign key
Primary key
Prevent exception
Validation
@Entity
@DatabaseConstrained
public class User {
@NotNull @Length(max=3)
private String name;
private String email;
}
User
name : varchar(3) NOT-NULL, UNIQUE email : varchar(100)No duplication
Retrieve
constraints
Database as
only truth
validate(new User(‘Henk’));
varchar(3)
not null
name = ‘Henk’
email = null
1. Loop over properties
3. Check name ‘Henk’
on metadata
Database Application
2. Get metadata
user.name
Determine column name
(Hibernate)
Database Application
name = ‘Henk’
email = null
varchar(
3
)
not null
1. Loop over properties
3. Check name ‘Henk’
on metadata
“ Name cannot be longer
than 3 characters “
2. Get metadata
user.name
validate(new User(‘Henk’));
name = null
email = null
1. Loop over properties
3. Check
null name
on metadata
validate(new User(null));
varchar(3)
not null
Database Application2. Get metadata
user.name
validate(new User(‘Henk’));
Applicationname = null
email = null
varchar(3)
not null
1. Loop over properties
3. Check
null name
on metadata
validate(new User(null));
“ Name cannot be null “
Database
2. Get metadata
validate(new User(‘Henk’));
name = ‘Jan’
email = null
1. Loop over properties
3. Check name ‘Jan’
on metadata
validate(new User(null));
validate(new User(‘Jan’));
Database Applicationvarchar(3)
not null
2. Get metadata
user.name
validate(new User(‘Henk’));
Database Applicationname = ‘Jan’
email = null
varchar(
3
)
not null
1. Loop over properties
3. Check name ‘Jan’
on metadata
2. Get metadata
user.name
validate(new User(null));
validate(new User(‘Henk’));
name = ‘Jan’
email = null
varchar(100)
1. Loop over properties
3. Check
null email
on metadata
Database Application2. Get metadata
user.email
validate(new User(null));
validate(new User(‘Jan’));
validate(new User(‘Henk’));
Database Applicationname = ‘Jan’
email = null
varchar(
100
)
1. Loop over properties
3. Check
null email
on metadata
2. Get metadata
user.email
validate(new User(null));
validate(new User(‘Henk’));
Database Applicationname = ‘Jan’
email = null
varchar(
100
)
1. Loop over properties
3. Check
null email
on metadata
2. Get metadata
user.email
validate(new User(null));
Super class
@MappedSuperclass
@DatabaseConstrained
public abstract class BaseEntity {
}
@Entity
public class User extends BaseEntity {
private String name;
private String email;
}
JDBC
@DatabaseConstrained
public class User {
private String name;
private String email;
}
Catch exception
Exception translation
Check
Unique key
Foreign key
Primary key
Translate the JDBC exception into
a proper
constraint
exception
•
Object Relation Mapping
•
Extracts constraint name from message
Hibernate
ConstraintViolationException getConstraintName()
Access to constraint name
Hardcoded names
Heavy for plain JDBC
Hardcoded names
try {
// Insert user
} catch (ConstraintViolationException e) {
if (
e.getConstraintName() == “uk_user_name”
) {
// Handle error
}
}
Too technical
Focus on domain
•
Dependency Injection
•
Templates
•
JDBC
•
DAO
•
JdbcTemplate
•
SQLExceptionTranslator
•
Error codes
•
Register own classes
•
No constraint name
Spring
DataAccessException
DataIntegrityViolationException
Consistent hierarchy
•
ORM (e.g. Hibernate)
•
PersistenceExceptionTranslator
•
Proxy
Spring$Proxy PersistenceExceptionTranslator ConstraintViolation Exception JPASystemException UserRepository
No constraint name
ConstraintViolationException getConstraintName() JPASystemException DataAccessException causeHierarchy
Weaker API
Weaker API
try {
userRepository.save(user);
} catch (JPASystemException e) {
ConstraintViolationException ce =
(ConstraintViolationException) e.getCause()
;
if (ce.getConstraintName() == “uk_user_name”) {
// Handle error
}
}
Unsafe cast
Recap
Constraint name
Hierarchy
Extensible
Hibernate
Spring
JaRB
Best of both worlds
JaRB
Concrete and domain specific exceptions.
try {
userRepository.save(new User(“Jan”));
} catch (
UserNameAlreadyExistsException
e) {
error(“User name already exists.”);
try {
userRepository.save(new User(“Jan”));
} catch (
UserNameAlreadyExistsException
e) {
error(“User name already exists.”);
} catch (
UserEmailAlreadyExistsException
e) {
error(“User email already exists.”);
Translator
SQLIntegrity
ConstraintException
UserNameAlready ExistsException
Resolver
ERROR: duplicate key value violates unique constraint \"uk_user_name\"
Detail: Key (name)=(Jan) already exists.
Extract all information from exception
SQLIntegrity
Resolver
ERROR: duplicate key value violates unique constraint \"uk_user_name\"
Detail: Key (name)=(Jan) already exists.
Vendor specific
Pattern matching
Extract all information from exception
Column name
Value
Constraint name
Version specific
SQLIntegrity
Resolvers
•
Pattern matching (default)
•
PostgreSQL
•
Oracle
•
MySQL
•
HSQL
•
H2
Factory
Default factory
UniqueKeyViolationException NotNullViolationException LengthExceededViolationExceptio n PrimaryKeyViolationException CheckFailedException InvalidTypeException ForeignKeyViolationExceptionDatabaseConstraintViolationException UniqueKeyViolationException NotNullViolationException LengthExceededViolationExceptio n PrimaryKeyViolationException CheckFailedException InvalidTypeException ForeignKeyViolationException UserNameAlreadyExistsException Constraint info
Custom exceptions
@NamedConstraint(“uk_user_name”)
public class UserNameAlreadyExistsException
extends UniqueKeyViolationException {
}
Scanned from class path
Registered on constraint
Custom exceptions
uk_user_name
UserNameAlreadyExistsExceptionInjectable arguments
@NamedConstraint(“uk_user_name”)
public class UserNameAlreadyExistsException
extends UniqueKeyViolationException {
UserNameAlreadyExistsException(…) { }
}
DatabaseConstraintViolation
Throwable (cause)
ExceptionFactory
Less concrete
try {
userRepository.save(new User(“Jan”));
} catch (
UniqueKeyViolationException
e) {
error(“User name already exists.”);
Enable in Spring
@EnableDatabaseConstraints(basePackage = “org.myproject”)
