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 retrieve

Java
 Application Relational
 Database

JDBC

• Java Database Connectivity
• Data Access API ( java.sql, javax.sql )
• JDK 1.1 (1997)
• Relational Database
• Many implementations

JDBC

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,
 UNIQUE

What happens? Assuming the user table is empty

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


User

name : varchar(3)
 NOT-NULL,
 UNIQUE

1 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

What happens?

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

SQLIntegrityConstraint
 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,
 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);

User

name : varchar(3)
 NOT-NULL,
 UNIQUE

What happens?

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

User

name : varchar(3)
 NOT-NULL,
 UNIQUE

SQLIntegrityConstraint
 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

Not null violation

Unique key violation Not null violation

SQLIntegrityConstraint
 ViolationException Which was violated?

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,
 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

Complicated Boilerplate Assumptions

Multiple not-null values

User

name : varchar(3)
 NOT-NULL,
 UNIQUE email : varchar(30)
 NOT-NULL, 
 UNIQUE

uk_user_name uk_user_email Multiple not-null values Multiple unique values

User

name : varchar(3)
 NOT-NULL,
 UNIQUE email : varchar(30)
 NOT-NULL, 
 UNIQUE

Which was violated?

SQLException

+ 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]

They are all difgerent

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]

The info is there

• Message
• Pattern matching
• Vendor specific

Extract violation info

Just too diffjcult Focus on application logic

JDBC needs a better exception API


( for integrity constraints )

Concrete exception classes

UniqueKeyViolationException NotNullViolationException

Access to constraint info

getColumnName() getConstraintName()

Workaround

Prevent violations

• Data integrity checks in application layer.

Prevent violations

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 Less database interaction

Applicatio n Database throw new RuntimeException

Less interaction

User

@NotNull
 private String name

User

name : varchar(3)
 NOT-NULL,
 UNIQUE

Application Database

Duplication

User

@NotNull
 private String name

User

name : varchar(3)
 NOT-NULL,
 UNIQUE

Application Database

Kept in sync Unexpected SQL exceptions

Duplication

• Complicated
• Depends on other rows

Prevent unique violation

id name NULL

Testable in isolation

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


• ld data

Unexpected Uncaught

Recap

No SQL exceptions Error prone Duplication

Not null

Extra query

Unique key

No SQL exceptions Error prone

Lack proper solution

Solution

Java Repository Bridge - JaRB

Databases are good at
 maintaining integrity; 


let them!

Catch exception Prevent exception

Testable in isolation

Not null Type Length Check Unique key Foreign key Primary key

Prevent exception

Validation

Not null Type Length

@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

• nly truth

validate(new User(‘Henk’));

varchar(3)
 not null

name = ‘Henk’
 email = null

• 1. Loop over properties
• 3. Check name ‘Henk’

• n 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’

• n metadata

“ Name cannot be longer
 than 3 characters “

• 2. Get metadata


user.name

validate(new User(‘Henk’));

validate(new User(‘Henk’));

name = null
 email = null

• 1. Loop over properties
• 3. Check null name

• n metadata

validate(new User(null));

varchar(3)
 not null

Database Application

• 2. Get metadata


user.name

validate(new User(‘Henk’));

Application

name = null
 email = null

varchar(3)
 not null

• 1. Loop over properties
• 3. Check null name

• n metadata

validate(new User(null));

“ Name cannot be null “

Database

• 2. Get metadata


user.name

validate(new User(‘Henk’));

name = ‘Jan’
 email = null

• 1. Loop over properties
• 3. Check name ‘Jan’

• n metadata

validate(new User(null)); validate(new User(‘Jan’));

Database Application

varchar(3)
 not null

• 2. Get metadata


user.name

validate(new User(‘Henk’));

Database Application

name = ‘Jan’
 email = null

varchar(3)
 not null

• 1. Loop over properties
• 3. Check name ‘Jan’

• n metadata
• 2. Get metadata


user.name

validate(new User(null)); validate(new User(‘Jan’));

validate(new User(‘Henk’));

name = ‘Jan’
 email = null

varchar(100)

• 1. Loop over properties
• 3. Check null email

• n metadata

Database Application

• 2. Get metadata


user.email

validate(new User(null)); validate(new User(‘Jan’));

validate(new User(‘Henk’));

Database Application

name = ‘Jan’
 email = null

varchar(100)

• 1. Loop over properties
• 3. Check null email

• n metadata
• 2. Get metadata


user.email

validate(new User(null)); validate(new User(‘Jan’));

validate(new User(‘Henk’));

Database Application

name = ‘Jan’
 email = null

varchar(100)

• 1. Loop over properties
• 3. Check null email

• n metadata
• 2. Get metadata


user.email

validate(new User(null)); validate(new User(‘Jan’));

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;
 }

Custom schema mapper

Catch exception

Exception translation

Check Unique key Foreign key Primary key

Translate the JDBC exception into a proper constraint exception

Existing translators

• Object Relation Mapping
• Extracts constraint name from message

Hibernate

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

Spring

• JdbcTemplate
• SQLExceptionTranslator
• Error codes
• Register own classes
• No constraint name

Spring JDBC

Spring

DataAccessException DataIntegrityViolationException

Consistent hierarchy Extensible

• ORM (e.g. Hibernate)
• PersistenceExceptionTranslator
• Proxy

Spring DAO


 
 Spring$Proxy PersistenceExceptionTranslator

ConstraintViolation
 Exception JPASystemException

UserRepository

No constraint name

ConstraintViolationException getConstraintName() JPASystemException DataAccessException

cause

Hierarchy 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

Why isn’t this easier?

Recap

Constraint name Hierarchy Extensible Hibernate Spring JaRB Best of both worlds

JaRB

Concrete and domain specific exceptions.

Map each constraint to a custom exception.

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
 ConstraintException

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
 ConstraintException

Resolvers

• Pattern matching (default)
• PostgreSQL
• Oracle
• MySQL
• HSQL
• H2
• Hibernate: constraint name only

Factory

Create a concrete exception

Default factory

UniqueKeyViolationException NotNullViolationException LengthExceededViolationExceptio n PrimaryKeyViolationException CheckFailedException InvalidTypeException ForeignKeyViolationException

DatabaseConstraintViolationException

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

UserNameAlreadyExistsException

uk_user_email

UniqueKeyViolationException

Injectable 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.”);
 }

How?

Enable in Spring

@EnableDatabaseConstraints(basePackage = “org.myproject”)

Enable exception translation Enable database validation Resolve database vendor Register custom exceptions

<jarb:enable-constraints base-package=“org.myproject”/>

Get source

<dependency> <groupId>org.jarbframework</groupId> <artifactId>jarb-constraints</artifactId> <version>2.1.0</version> </dependency>

Maven central

http://www.jarbframework.org

Github

Questions?