Jihao Deng

Posted on Sep 5, 2020

DA05 Object to Relational Structural Design

#learning #design #architecture

本篇主要讲如何实现程序里的对象与关系数据库之间的记录进行对应，这个问题可以分为以下几个小问题：

程序中对象通过引用或指针来进行标注，而关系数据库则是通过键；
对象有set和list等容器，而关系数据库则没有；
关系数据库没有继承机制

Indentity Field

Saves a database ID field in an object to maintain object identity between an in-memory object and a database row.

通过保存数据库中的主键来保证对象的唯一

class KeyTable {

    public int getKey(String tableName) {
        //get the next key from the table
        String query = "SELECT nextID FROM keys WHERE name = {0} FOR UPDATE";
        String queryPrepared = DB.prepare(query, tableName);
        IDbCommand comm = new OleDbCommand(queryPrepared, DB.Connection);
        ResultSet rs = comm.executeReader();
        Record record = rs.get(0);
        int result = record.getLong(1);

        //update the table with the next key
        int nextKey = result + 1;
        String update = "UPDATE keys SET nextID = {0} WHERE name = {1}";
        String updatePrepared = DB.prepare(update, nextKey, tableName);
        comm = new OleDbCommand(queryPrepared, DB.Connection);
        comm.executeNonQuery();

        return result;
    }
}

实际上以上的例子已经很少使用，因为数据库现在支持主键自动递增了。

另外，对于之前的一些pattern，无需使用Indentity Field，例如table module, transaction script, table data gateway。它们的domain对象中已经存贮了记录在数据库中的主键。

Foreign Key Map

Maps an association between objects to a foreign key reference between tables.

如果domain对象中包含了组合关系，例如Object A的一个熟悉是Object B，那么数据库中，A对应的表和B对应的表就会有一个外键关系。

下面的例子中，Artist会有多个Album，一个Album含有多个Track。Album表中有一个Artist外键，Track表中有一个Album外键。

class Artist {
    private int id;
    private String name;
    ...
}

class Track {
    private int id;
    private String title;
    private String style;
    ...
}

class Album {
    private int id;
    private String title;
    private Artist artist;
    private ArrayList<Track> tracks;
    ...
}

Artist, Track and Album对于的数据库表如下

Artist:
    id: int
    name: varchar

Track:
    id: int
    album_id: int (foreign key)
    title: varchar

Album:
    id: int
    artist_id: int (foreign key)
    title: varchar

对于的Data Mapper：

class AlbumMapper {

    public Album find(long id) {
        String sql = "SELECT ID, artistID, title " +
            " from albums " +
            " WHERE ID = {0}";
        String sqlPrepared = DB.prepare(sql, id);
        IDbCommand comm = new OleDbCommand(sqlPrepared, DB.Connection);
        ResultSet rs = comm.executeQuery();
        rs.next();

        //get the artist information
        long artistID = rs.getLong(1);
        ArtistMapper artistMapper = new ArtistMapper();
        Artist artist = artistMapper.find(artistID);

        //get the track information
        TrackMapper trackMapper = new TrackMapper();
        Track [] tracks = trackMapper.findForAlbum(id);
        Album result = new Album(id, title, artist, tracks);
        return result;
    }
}

class TrackMapper {
    public Track [] findForAlbum(long albumId) {
        String sql = "SELECT ID, title " +
            " from tracks " +
            " WHERE albumID = {0}";
        String sqlPrepared = DB.prepare(sql, albumID);
        IDbCommand comm = new OleDbCommand(sqlPrepared, DB.Connection);
        ResultSet rs = comm.executeQuery();

        Track [] result = new Track[rs.size()];
        for (int i = 0; i < rs.size(); i++) {
            rs.next();
            result[i] = new Track(rs.getLong(0), rs.getString(1));
        }
        return result;
    }
}

但是执行Album的find方法会总共执行三次JDBC的Query，这样效率比较低，我们可以直接使用数据库的连表查询来对find方法进行修改：

class AlbumMapper {
    public Album find(long id) {
        String sql = "SELECT a.ID, a.artistID, a.title, r.name " +
            " from albums a, artists r " +
            " WHERE ID = {0} and a.artistID = r.ID";
        String sqlPrepared = DB.prepare(sql, id);
        IDbCommand comm = new OleDbCommand(sqlPrepared, DB.Connection);
        ResultSet rs = comm.executeQuery();
        rs.next();

        //get the artist information
        long artistID = rs.getLong(1);
        String artistName = rs.getString(3);
        Artist artist = new Artist(artistID, artistName);

        //get the track information
        ...
    }
}

Foreign Key Map的优缺点

Pros

One-to-Many的情况会比较简单

Cons

不支持Many-to-Many的情况

Association Table Mapping

Saves an association as a table with foreign keys to the tables that are linked by the association.

对于多对多的情况，我们无法用一个外键来表示这样的关系，Association Table Mapping就仿照了关系数据库来创建一个新的表来记录这些关系。

还是Artist的例子，Artist和Instrument有多对多的关系：

class Artist {
    private int id;
    private String name;
    ...
}

class Instrument {
    private int id;
    private String name;
}

Artist:
    id: int
    name: varchar

Instrument:
    id: int
    name: varchar

artist-instruments:
    artist_id: int
    instrument_id: int

对应的，ArtistMapper会是这样：

class ArtistMapper {
    public Artist find(long id) {
        String sql = "SELECT ID, name " +
            " from artists " +
            " WHERE ID = {0}";
        String sqlPrepared = DB.prepare(sql, id);
        IDbCommand comm = new OleDbCommand(sqlPrepared, DB.Connection);
        ResultSet rs = comm.executeQuery();
        rs.next();

        //get the name
        String name = rs.getString(1);

        //get the instruments information
        Instrument [] instruments = loadInstruments(id);

        Artist result = new Artist(id, name, instruments);
        return result;
    }

    // find instruments that the artist plays in artist-instruments table
    public Instrument [] loadInstruments(long artistID) {
        String sql = "SELECT artistID, instrumentID " +
            " from artist-instruments " +
            " WHERE artistID = {0}";
        String sqlPrepared = DB.prepare(sql, id);
        IDbCommand comm = new OleDbCommand(sqlPrepared, DB.Connection);
        ResultSet rs = comm.executeQuery();

        //load the instrument details using an InstrumentMapper
        Instrument [] result = new Instrument[rs.size()];
        InstrumentMapper instrumentMapper = new InstrumentMapper();
        for (int i = 0; i < rs.size(); i++) {
            rs.next();
            result[i] = instrumentMapper.find(rs.getLong(2));
        }
        return result;
    }
}

Association Table Mapping的优缺点

Pros

巧妙

Cons

复杂

Embedded Value (Dependent Mapping)

Maps an object into several fields of another object’s table.

An embedded value maps the values of an object into the fields of its owner. When the owning object is loaded/saved, the corresponding embedded values are loaded/saved as well.

有时我们不需要为每一个类都创建一个对应的数据库表，因为有一些表会显得没有意义。

总的来说，embedded value只存在于一对一的关系。

class Employee {
    int id;
    String name;
    Period period;  // working period
    Money salary;
}

class Period {
    Date startDate;
    Date endDate;
}

class Money {
    float amount;
    String currency;
}

我们并不为上面的三个类创建三个表，而是之创建一个表：

Employee:
    id: int
    name: varchar
    startDate: Date
    endDate: Date
    salary: float
    currency: varchar

对应的Mapper如下：

class EmploymentMapping {

    public Employment find(long id) {
        String sql = "SELECT * from Employments WHERE id = {0}";
        String sqlPrepared = DB.prepare(sql, id);
        IDbCommand comm = new OleDbCommand(sqlPrepared, DB.Connection);
        ResultSet rs = comm.executeQuery();
        Record record = rs.get(0);

        //lookup the information from the Person table
        long personID = rs.getFloat(2);
        Person person = personMapper.find(personID);

        //create the data range and money objects
        Date startDate = rs.getDate(3);
        Date endDate = rs.getDate(4);
        DateRange dateRange = new DateRange(startDate, endState);

        float amount = rs.getFloat(5);
        String currency = rs.getString(6);
        Money money = new Money(amount, currency);

        //create the Employment instance
        Employment result = new Employment(id, person, dateRange, money);
        return result;
    }

    ...
}

Single Table Inheritance

Represents an inheritance hierarchy of classes as a single table that has columns for all fields of the various classes.

用一张表来存储所有种类的class，这张表的列包含了所有的属性，对于不同class的Object，它们所在的行只使用它的属性对应的字段，其余字段则设为空。同时，还需要一个记录其类型的字段type。

我们有三个类：Player, Footballer, Crecketer，并有以下的继承关系：

class Player {
    protected String name;
    protected int age;
}

class Footballer extends PLayer {
    protected String club;
}

class Cricketer extends PLayer {
    protected int battingAverage;
}

对应的数据库表，我们仅创建一个，包含以上所有属性以及一个type：

Player:
    name: varchar
    age: int
    club: varchar
    batting_average: int
    type: int

Single Table Inheritance的优缺点

Pros

简单
High scalability: Refactoring the design to move fields around the hierarchy does not require a change in the table, only the corresponding code.
No joins: because there is only one table

Cons

性能较差，因为只有一张表，会限制并发，frequent locking
空间利用率低

Class Table Inheritance

Represents an inheritance hierarchy of classes with one table for each class.

与Single Table Inheritance相对，为每一个类创建一个Table

每一次对子类表的数据库操作，都会与其父类表进行连表操作。

还是用上面Player的例子，这次的表是这样的：

Player:
    name: varchar
    age: int

Footballer:
    club: varchar

Cricketer:
    batting_average: int

Class Table Inheritance的优缺点

Pros

直观

Cons

性能瓶颈，因为每一次操作都会涉及到最终父类
Refactoring: Moving a field from one class to another in the inheritance hierarchy requires a change to the database as well as the code.
Joins: 连表操作

Concrete Table Inheritance

Represents an inheritance hierarchy of classes with one table per concrete class in the hierarchy.

与Class Table Inheritance类似，有多个表，子类对应的表包含了所有的属性：

Footballer:
    name: varchar
    age: int
    club: varchar

Cricketer:
    name: varchar
    age: int
    batting_average: int

Concret Table Inheritance的优缺点

Pros

简单
直观
No join
No bottleneck, load spread

Cons

需要生成唯一的主键，即使在不同的表之间，主键也要保证唯一
Refactoring: Moving a field from one class to another in the inheritance hierarchy requires a change to the database as well as the code.
维护性差，如果父类的属性修改了，那么会导致大量的表也要跟着修改
全局查询慢，如果不知道要查询内容的类型，就需要在所有的表内进行查询

DEV Community

DA05 Object to Relational Structural Design

本篇主要讲如何实现程序里的对象与关系数据库之间的记录进行对应，这个问题可以分为以下几个小问题：

Indentity Field

Foreign Key Map

Foreign Key Map的优缺点

Pros

Cons

Association Table Mapping

Association Table Mapping的优缺点

Pros

Cons

Embedded Value (Dependent Mapping)

Single Table Inheritance

Single Table Inheritance的优缺点

Pros

Cons

Class Table Inheritance

Class Table Inheritance的优缺点

Pros

Cons

Concrete Table Inheritance

Concret Table Inheritance的优缺点

Pros

Cons

Top comments (0)

Read next

I watched "So You Think You Know Git" so you don't have to

Using observers in Laravel to track model events

From Zero to Hero: Dockerizing My Go App and Hosting It on Render like a Pro 🚀

🚀 12 Dev Tools + Gen Z UX Secrets + CSS Animations (5-min read)