Java for Web学习笔记（一百）：持久化初探（5）Enum、时间和lob的类型匹配

ENUM

缺省的，ENUM对应为0,1,2,3，但这种方式可读性较差，且如果代码的顺序发生变化，将出现严重错误。更好地采用字符串方式，在数据库中可以是VARCHAR，也可以用ENUM。我们对小例子的Author表进行改造

CREATE TABLE `Authors` (
  `AuthorId` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `AuthorName` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `EmailAddress` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `Gender` enum('MALE','FEMAIL','UNSPECIFIED') COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`AuthorId`),
  KEY `Publishers_Names` (`AuthorName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

在代码中创建一个Gender的Enum

public enum Gender {
	MALE,
	FEMALE,
	UNSPECIFIED
}

在Entity中添加Gender属性，不采用@Basic的类型适配，而是给出@Enumerated，并指定类型为字符串EnumType.STRING。

@Entity
@Table(name = "Authors",
	indexes = { @Index(name = "Authors_Names", columnList = "AuthorName")})
public class Author implements Serializable{
    private Gender gender;

    //......
    @Enumerated(EnumType.STRING)
    public Gender getGender() {
        return gender;
    }
    public void setGender(Gender gender) {
        this.gender = gender;
    }
}

时间转换

JPA2.1的时间不支持Java SE 8的新的时间日期类，要JPA2.2才支持。SQL中一般为date，time，datetime（timestamp），存储的时区为SQL Server的时区，即本身不带时区信息。下面提供一个将date转为Calendar的例子。我们将定所有的服务器都在同一个时区，即不考虑时区差异。在Publishers表格中添加一个时间列。具体如下：

CREATE TABLE `Publishers` (
  `PublisherId` bigint(20) unsigned NOT NULL,
  `PublisherName` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `Address` varchar(1024) COLLATE utf8_unicode_ci NOT NULL,
  `DateFounded` date NOT NULL DEFAULT '2000-01-01',
  PRIMARY KEY (`PublisherId`),
  KEY `Publishers_Names` (`PublisherName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

在Entity中使用@Temporal来指明数据库中的格式，date，time，datetime（timestamp）分别对应为TemporalType.DATE，TemporalType.TIME，TemporalType.TIMESTAMP。因为JPA2.1还不能支持LocalDateTime的数据格式转换，因此如果我们需要转为这个格式，需要另外写。例子中给出了一个public LocalDateTime getFounded()作为格式转换，但是founded不对应到数据库表格的列，要注明@javax.persistence.Transient。

@Entity(name="PublisherEntity")
@Table(name = "Publishers",
	indexes = {@Index(name = "Publishers_Names", columnList = "PublisherName")})
public class Publisher implements Serializable{
    //... ...
    private Calendar dateFounded;
    
    //使用@Temporal来指明数据库中的格式
    @Temporal(TemporalType.DATE)
    public Calendar getDateFounded() {
        return dateFounded;
    }
    public void setDateFounded(Calendar dateFounded) {
        this.dateFounded = dateFounded;
    }
 
    //getFounded只作为数据格式的转换，并不作为数据库表格的列映射项，因此加上@Transient
    @Transient
    public LocalDateTime getFounded(){
        return LocalDateTime.ofInstant(this.dateFounded.toInstant(), this.dateFounded.getTimeZone().toZoneId());
    }
}

LOB(Large Object)

在mysql的表格中，每一行最大的容量为65535，如果存储的内容很大，例如直接存储一个图片，需要采用TEXT和BLOB格式，TEXT和BLOB的具体内容不直接存放在表格中，在表格中只占有9~12字节。虽然可以，但我们并不会使用TEXT和BLOB的数据作为index，也不会进行排序（默认的只选择签名1024字节进行排序）。我们根据存储容量 [1]需要，选择具体的格式：

• TINYBLOB，TINYTEXT: 255 (2^8- 1) bytes/charaters
• BLOB，TEXT: 65,535 (2^16 - 1) bytes/charaters
• MEDIUMBLOB，MEDUIMTEXT: 16,777,215 or 16M (2^24 - 1) bytes/charaters
• LONGBLOB，LONGTEXT: 4,294,967,295 or 4GB (2^32 - 1) bytes/charaters

A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold. The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types and have the same maximum lengths and storage requirements.[2]
The internal representation of a table has a maximum row size of 65,535 bytes, even if the storage engine is capable of supporting larger rows. This figure excludes BLOB or TEXT columns, which contribute only 9 to 12 bytes toward this size. For BLOB and TEXT data, the information is stored internally in a different area of memory than the row buffer. Different storage engines handle the allocation and storage of this data in different ways, according to the method they use for handling the corresponding types.[3]

我们在小例子的Books表中加上一个列，直接binary存放预告pdf。

CREATE TABLE `Books` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Isbn` varchar(13) COLLATE utf8_unicode_ci NOT NULL,
  `Title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `Author` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `Price` decimal(6,2) NOT NULL,
  `Publisher` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `PreviewPdf` mediumblob,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Books_ISBNs` (`Isbn`),
  KEY `Books_Titles` (`Title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- SQL查看二进制的方式为hex()
SELECT Id, Isbn, Title, Author, Price, Publisher, hex(PreviewPdf) FROM Books;

相应地在Entity中增加下面的代码，类型映射中指明为@Lob。@Lob可用于byte[]以及Serializable，String和char[]。

@Entity
@Table(name="Books",
	uniqueConstraints = {
        @UniqueConstraint(name = "Books_ISBNs", columnNames = { "Isbn" })},
	indexes = {
	        @Index(name = "Books_Titles", columnList = "Title")
	})
public class Book {
	// ... ...
	private byte[] previewPdf;   
	//... ...

	@Lob
	public byte[] getPreviewPdf() {
		return previewPdf;
	}
	public void setPreviewPdf(byte[] previewPdf) {
		this.previewPdf = previewPdf;
	} 
}

相关链接： 我的Professional Java for Web Applications相关文章