Java(物件導向) 與 SQL 的概念衝突

The paradigm mismatch :

Java :

public class User { 
    String username; 
    String address; 
    Set billingDetails; 
    // Accessor methods (getter/setter), business methods, etc. 
}

public class BillingDetails { 
	String account; 
	String bankname; 
	User user; 
	// Accessor methods (getter/setter), business methods, etc. 
}

SQL :

create table USERS ( 
    USERNAME varchar(15) not null primary key, 
    ADDRESS varchar(255) not null 
);

create table BILLINGDETAILS ( 
	ACCOUNT varchar(15) not null primary key, 
	BANKNAME varchar(255) not null, 
	USERNAME varchar(15) not null, 
	foreign key (USERNAME) references USERS 
);

1. The problem of granularity (SQL 無法自訂 data type)

The most glaringly obvious problem with the current implementation is that you’ve designed an address as a simple String value. In most systems, it’s necessary to store street, city, state, country, and ZIP code information separately.

You have the choice of adding either several columns or a single column (of a new SQL data type).
This is clearly a problem of granularity. Broadly speaking, granularity refers to the relative size of the types you’re working with.
A new Address type (class) in Java and a new ADDRESS SQL data type should guarantee interoperability.
But you’ll find various problems if you check the support for userdefined data types (UDTs) in today’s SQL database management systems.

SQL :

create table USERS ( 
    USERNAME varchar(15) not null primary key, 
    ADDRESS address not null 
);

The pragmatic solution for this problem has several columns of built-in vendordefined SQL type.

create table USERS ( 
    USERNAME varchar(15) not null primary key, 
    ADDRESS_STREET varchar(255) not null, 
    ADDRESS_ZIPCODE varchar(5) not null, 
    ADDRESS_CITY varchar(255) not null 
);

Many simple persistence mechanisms fail to recognize this mismatch and so end up forcing the less flexible representation of SQL products on the object-oriented model, effectively flattening it.

2. The problem of subtypes (SQL 沒有繼承概念)

In Java, you implement type inheritance using superclasses and subclasses.
The result of this mismatch of subtypes is that the inheritance structure in a model must be persisted in an SQL database that doesn’t offer an inheritance mechanism.

3. The problem of identity (SQL 必需要 ID 當 PK，但 ID 卻無任何商業意義)

There are three ways to tackle this problem: two in the Java world and one in your SQL database.
Java defines two different notions of sameness:
- Instance identity (roughly equivalent to memory location, checked with a == b)
- Instance equality, as determined by the implementation of the equals() method (also called equality by value)
The identity of a database row is expressed as a comparison of primary key values.
A surrogate key column is a primary key column with no meaning to the application user.—in other words, a key that isn’t presented to the application user. Its only purpose is identifying data inside the application.

 create table USERS ( 
     ID bigint not null primary key, 
     USERNAME varchar(15) not null unique, 
     ... 
 );

 create table BILLINGDETAILS ( 
 	ID bigint not null primary key, 
 	ACCOUNT varchar(15) not null, 
 	BANKNAME varchar(255) not null, 
 	USER_ID bigint not null, 
 	foreign key (USER_ID) references USERS 
 );

4. Problems relating to associations (SQL 利用 FK 來表示關聯)

Association mapping and the management of entity associations are central concepts in any object persistence solution.
Object-oriented languages represent associations using object references.
In the relational world (SQL), a foreign key–constrained column represents an association.

Java associations can have many-to-many multiplicity :

public class User { 
    Set billingDetails; 
} 

public class BillingDetails { 
	Set users; 
}

But the foreign key declaration on the BILLINGDETAILS table is a many-to-one association: each bank account is linked to a particular user. Each user may have multiple linked bank accounts.

If you wish to represent a many-to-many association in an SQL database, you must introduce a new table, usually called a link table. In most cases, this table doesn’t appear anywhere in the domain model.

create table USER_BILLINGDETAILS ( 
    USER_ID bigint, 
    BILLINGDETAILS_ID bigint, 
    primary key (USER_ID, BILLINGDETAILS_ID), 
    foreign key (USER_ID) references USERS, 
    foreign key (BILLINGDETAILS_ID) references BILLINGDETAILS 
);

There is a fundamental difference in how you access data in Java and in a relational database.
In Java (object-oriented), when you access a user’s billing information, you call someUser.getBillingDetails().iterator().next()
Unfortunately, this isn’t an efficient way to retrieve data from an SQL database.
The single most important thing you can do to improve the performance of data access code is to minimize the number of requests to the database.
The most obvious way to do this is to minimize the number of SQL queries. (select * from USERS u left outer join BILLINGDETAILS bd on bd.USER_ID = u.ID where u.ID = 123)

SU - 程式學習筆記