Spring JPA

2018/12/28
2022/01/08 (新增連結)

基本概念

Spring Framework支援Java Persistence API(JPA)的相關元件，JPA簡化了java對資料庫的存取。不過，如果不是單純的新增、刪除、修改，使用JPA可能就會有一些效能上的問題。

設定

第一、要安裝mySQL server (或其他資料庫)

Mac OS 上安裝 MySQL 以及相關設定筆記

第二、要啟動mySQL server (或其他資料庫) (請參考投影片)

第三、要建立資料表 (或匯入資料表) (請參考投影片) (或詳參: https://dev.mysql.com/doc/workbench/en/wb-table-editor.html)

CREATE TABLE `practice`.`customer` (

`id` INT NOT NULL AUTO_INCREMENT,

`name` VARCHAR(15) NULL,

`address` VARCHAR(45) NULL,

`weight` INT NULL,

PRIMARY KEY (`id`));

以下的以下的兩個table是為了是為了資料表關聯，請先新增book_category。請記得要新增一到兩筆資料。

**注意** 在java裡，類別名稱或變數名稱有大小寫的話(如:BookCategory)，在資料表裡要命名為Book_Category，因為資料庫的資料表及欄位是不分大小寫，JPA在對應時，會自動加上底線。

CREATE TABLE `practice`.`book_category` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(15) COLLATE utf8_unicode_ci NOT NULL,

PRIMARY KEY (`id`)

);

再新增book。最下面的幾行在mySQL workbench要利用Foreign Keys去建立。Foreign Key name: book_category, referenced table: book_category, column:category (in book), referenced column: id (in book_category), on update: cascade, on delete: restrict。**注意:如果book裡已經有資料裡，book的category裡的值在book_category的id中必須存在，否則無法建立Foreign Key。(詳參: https://dev.mysql.com/doc/workbench/en/wb-table-editor-foreign-keys-tab.html)

CREATE TABLE `practice`.`book` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,

`price` int(11) DEFAULT NULL,

`category` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `book_category_idx` (`category`),

CONSTRAINT `book_category` FOREIGN KEY (`category`) REFERENCES `book_category` (`id`) ON UPDATE CASCADE

);

第四、更動pom.xml，引用JDBC及MySQL的相關packages

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-data-jpa</artifactId>

</dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-jdbc</artifactId>

</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

<scope>runtime</scope>

</dependency>

第五、設定參數 (application.properties)，以使用mySQL為例。

spring.datasource.url=jdbc:mysql://localhost/practice?verifyServerCertificate=false&useSSL=false&requireSSL=false

practice是資料庫的名稱 (在mySQL中稱為schema)，verifyServerCertificate的部分是設定不要檢查SSL，同時設定不要useSSL及requireSSL。

#的部份是註解。

spring.datasource.url=jdbc:mysql://localhost/practice?verifyServerCertificate=false&useSSL=false&requireSSL=false

#spring.datasource.url=jdbc:mysql://localhost/practice

spring.datasource.username=root

spring.datasource.password=1234

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

使用CrudRepository

要使用JPA，首先，產生一個CustomerDAO，並繼承CrudRepository，並且設定採用的Entity(如:Customer)及id的資料型態(如:Long、Integer)。

CrudRepository提供了很多方法，如: save、findAll、findOne、delete，可以不需要使用SQL就可以存取資料庫裡的資料 (詳參: http://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/repository/CrudRepository.html )。

package com.example.demo.dao;

import org.springframework.data.repository.CrudRepository;

import com.example.entity.Customer;

public interface CustomerDAO extends CrudRepository<Customer, Long>{

}

Customer.java裡利用@Entity告訴JPA這是個Entity類別，JPA會自動連接到類別名稱對應的資料表(customer)。另外，並以@Id指定一個欄位為資料表對應的主鍵(primary key)，如果這個欄位的內容是自動產生，則必須加上@GeneratedValue(strategy=GenerationType.IDENTITY) (詳參: http://www.thoughts-on-java.org/jpa-generate-primary-keys/ )。**在Hibernate 5，設為AUTO會使用一個資料表來產生序號，所以，請改為IDENTITY或SEQUENCE (詳參: Migration from Hibernate 4 to 5)。

package com.example.demo.entity;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

@Entity

public class Customer {

@Id

@GeneratedValue(strategy=GenerationType.IDENTITY)

private Long id;

private String name;

private String address;

private int weight;

public Long getId() {

return id;

}

public void setId(Long id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getAddress() {

return address;

}

public void setAddress(String address) {

this.address = address;

}

public int getWeight() {

return weight;

}

public void setWeight(int weight) {

this.weight = weight;

}

對應的Controller中利用@Autowired串連前面的CustomerDAO:

package com.example.demo.controller;

import java.sql.SQLException;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.ModelAttribute;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.servlet.ModelAndView;

import com.example.demo.dao.CustomerDAO;

import com.example.demo.entity.Customer;

@Controller

public class CustomerController {

@Autowired

CustomerDAO dao;

@RequestMapping(value = "/customerCreate", method = RequestMethod.GET)

public ModelAndView openFormCreate() {

ModelAndView model = new ModelAndView("customerCreate");

return model;

}

@RequestMapping(value = "/customerCreate", method = RequestMethod.POST)

public ModelAndView processFormCreate(@ModelAttribute Customer cus) throws SQLException {

ModelAndView model = new ModelAndView("redirect:/customerRetrieveAll");

dao.save(cus);

//model.addObject(cus);

return model;

}

@RequestMapping(value = {"/customerRetrieveAll","/"}, method = RequestMethod.GET)

public ModelAndView retrieveCustomers() throws SQLException{

Iterable<Customer> customers = dao.findAll();

ModelAndView model = new ModelAndView("customerList");

model.addObject("customers",customers);

return model;

}

@RequestMapping(value = "/customerUpdate", method = RequestMethod.GET)

public ModelAndView openFormUpdate(@RequestParam(value="id", required=false, defaultValue="1") Long id) {

ModelAndView model = new ModelAndView("customerUpdate");

Customer customer = dao.findOne(id);

model.addObject(customer);

return model;

}

@RequestMapping(value = "/customerUpdate", method = RequestMethod.POST)

public ModelAndView processFormUpdate(@ModelAttribute Customer cus) throws SQLException {

ModelAndView model = new ModelAndView("redirect:/customerRetrieveAll");

dao.save(cus);

return model;

}

@RequestMapping(value = "/customerDelete", method = RequestMethod.GET)

public ModelAndView deleteCustomer(@RequestParam(value="id", required=false, defaultValue="1") Long id) {

ModelAndView model = new ModelAndView("redirect:/customerRetrieveAll");

dao.delete(id);

return model;

}

新增資料

CrudRepository內建了一個save的方法，把物件儲存到資料庫，可以進行資料的新增或更新。

@RequestMapping(value = "/customerCreate", method = RequestMethod.POST)

public ModelAndView processFormCreate(@ModelAttribute Customer cus) throws SQLException {

ModelAndView model = new ModelAndView("redirect:/customerRetrieveAll");

dao.save(cus);

model.addObject(cus);

return model;

}

對應的view，customerCreate.html (詳細解釋請參考Spring View)。

<!DOCTYPE html>

<head>

<title>Create New Customer</title>

</head>

<body>

姓名:<input type="text" name="name"/><br/>

地址:<input type="text" name = "address"/><br/>

重量:<input type="text" name ="weight"/><br/>

</form>

</body>

</html>

讀取資料

CrudRepository內建了一個findAll的方法，讀取所有物件。

@RequestMapping(value = {"/customerRetrieveAll","/"}, method = RequestMethod.GET)

public ModelAndView retrieveCustomers() throws SQLException{

Iterable<Customer> customers = dao.findAll();

ModelAndView model = new ModelAndView("customerList");

model.addObject("customers",customers);

return model;

}

對應的view，customerList.html (詳細解釋請參考Spring View)。

<!DOCTYPE html>

<head>

<title>Create New Customer</title>

</head>

<body>

<table>

<tr>

</tr>

<tr th:each="customer : ${customers} " th:object="${customer}">

</tr>

</table>

</body>

</html>

更新資料

通常會需要讀取該筆資料讓使用者可以更新該筆資料。利用CrudRepository內建的findOne的方法，讀取該物件的內容。findOne會使用在Entity類別(如:Customer)中@Id所指定的變數(如:id)作為搜尋的依據。

@RequestMapping(value = "/customerUpdate", method = RequestMethod.GET)

public ModelAndView openFormUpdate(@RequestParam(value="id", required=false, defaultValue="1") Long id) {

ModelAndView model = new ModelAndView("customerUpdate");

Customer customer = dao.findOne(id);

model.addObject(customer);

return model;

}

將資料送到對應的view，customerUpdate.html (詳細解釋請參考Spring View)。因為在update時，必須有id，但又不能讓使用者隨便更動id，所以，使用hidden來傳送資料給controller。

<!DOCTYPE html>

<head>

<title>Update Customer</title>

</head>

<body>

name:<input type="text" name="name" th:field="*{name}"/><br/>

address:<input type="text" name = "address" th:field="*{address}"/><br/>

weight:<input type="text" name ="weight" th:field="*{weight}"/><br/>

<input type="hidden" name ="id" th:field="*{id}"/>

</form>

</body>

</html>

將更新的資料利用送回，利用CrudRepository內建的save的方法，儲存內容。

@RequestMapping(value = "/customerUpdate", method = RequestMethod.POST)

public ModelAndView processFormUpdate(@ModelAttribute Customer cus) throws SQLException {

ModelAndView model = new ModelAndView("redirect:/customerRetrieveAll");

dao.save(cus);

return model;

}

刪除資料

CrudRepository內建了一個delete的方法，刪除特定物件。delete會使用在Entity類別(如:Customer)中@Id所指定的變數(如:id)作為刪除的依據。

@RequestMapping(value = "/customerDelete", method = RequestMethod.GET)

public ModelAndView deleteCustomer(@RequestParam(value="id", required=false, defaultValue="1") Long id) {

ModelAndView model = new ModelAndView("redirect:/customerRetrieveAll");

dao.delete(id);

return model;

}

類別與資料庫的對應

如果類別名稱與資料表名稱不一致，就可以使用@Table來指定對應的資料表名稱(如:@Table(name = "customer_category"))。如果欄位名稱不一致，就可以使用來指定對應的欄位名稱(如:@Column(name = "description"))。有時候用到資料庫的關鍵字(如: order, describe)，也可以利用@Table @Column來對應非關鍵字的資料表、欄位名稱。

package com.example.demo.entity;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.Table;

import javax.persistence.Column;

@Entity

@Table(name="customer_category")

public class CustomerCategory {

@Id

@GeneratedValue(strategy=GenerationType.AUTO)

private Long id;

@Column(name = "description")

private String name;

public Long getId() {

return id;

}

public void setId(Long id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

客製查詢

JPA提供一個利用方法名稱客製查詢的方法，例如:可以在DAO中寫findAllByOrderByWeightAsc，findAll就是回傳資料表所有資料，ByOrder，表示要以後面的變數排序，ByWeightAsc，就是要以"weight"變數由小到大(Ascending)排序，如果要由大到小(Descending)就是ByWeightDesc。也可以寫findByWeightGreaterThan，就可以找在某個重量以上的客戶，同樣的作法可以使用@Query來下Java Persistence Query Language(JPQL)查詢(語法類似SQL)，好處是方法名稱就可以自訂。 (詳參: http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation, http://docs.spring.io/spring-data/jpa/docs/1.11.1.RELEASE/reference/html/#repositories.query-methods.query-creation, http://openjpa.apache.org/builds/1.0.1/apache-openjpa-1.0.1/docs/manual/jpa_overview_query.html)。

package com.example.demo.dao;

import org.springframework.data.repository.CrudRepository;

import com.example.demo.entity.Customer;

public interface CustomerDAO extends CrudRepository<Customer, Long>{

public Iterable<Customer> findAllByOrderByWeightAsc();

public Iterable<Customer> findByWeightGreaterThan(Integer weight);

public Iterable<Customer> findByNameContaining(String name);

//in @Query, the entity name is case sensitive

@Query("select c from Customer c where c.weight > 40")

public Iterable<Customer> findOverWeight();

}

其中的"select c from Customer c where c.weight > 40" 對應的類似sql是"select * from customer c where c.weight > 40" ，不同的是sql的在from前面是欄位的名稱，但是JPQL在from前面是entity class的名稱(上面的範例是利用Customer的別名 c)。sql的在from後面是table的名稱，但是JPQL在from後面是entity class的名稱。

資料表關聯

當資料表之間有關聯時，JPA也提供簡單的方法來處理。一本書可以設定類別。與Customer相較，因為書籍類別會關聯到BookCategory，所以，多了@ManyToOne表示這個會有多本書使用同一個類別，@JoinColumn表示在資料表裡是以"category"這個欄位為Foreign Key。記得要先建好book及book_category兩個table。

package com.example.demo.entity;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.JoinColumn;

import javax.persistence.ManyToOne;

@Entity

public class Book {

@Id

@GeneratedValue(strategy = GenerationType.AUTO)

private Long id;

private String name;

private int price;

// JoinColumn refers to the column name in this table (book)

@ManyToOne

@JoinColumn(name = "category")

private BookCategory bookCategory;

public Long getId() {

return id;

}

public void setId(Long id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public int getPrice() {

return price;

}

public void setPrice(int price) {

this.price = price;

}

public BookCategory getBookCategory() {

return bookCategory;

}

public void setBookCategory(BookCategory bookCategory) {

this.bookCategory = bookCategory;

}

書籍類別 (BookCategory.java)可以利用@OneToMany去對應類別下的書籍，@mappedBy則是指Book中對應的變數名稱。最後，提供getBooks()來取得類別下的所有書籍。

package com.example.demo.entity;

import java.util.List;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.OneToMany;

import javax.persistence.Table;

@Entity

@Table(name = "book_category")

public class BookCategory {

@Id

@GeneratedValue(strategy = GenerationType.AUTO)

private Long id;

private String name;

// mappedBy refers to the variable in Book

@OneToMany(mappedBy = "bookCategory")

private List<Book> books;

public Long getId() {

return id;

}

public void setId(Long id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public Iterable<Book> getBooks() {

return books;

}

BookDAO.java中可定義了一個列出特定類別書籍的方法。

package com.example.demo.dao;

import org.springframework.data.repository.CrudRepository;

import com.example.entity.Book;

import com.example.entity.BookCategory;

public interface BookDAO extends CrudRepository<Book, Long>{

public Iterable<Book> findByBookCategory(BookCategory category);

}

BookCategoryDAO.java

package com.example.demo.dao;

import org.springframework.data.jpa.repository.Query;

import org.springframework.data.repository.CrudRepository;

import com.example.entity.BookCategory;

public interface BookCategoryDAO extends CrudRepository<BookCategory, Long>{

}

跟CustomerController相較，BookController需要BookDAO及BookCategoryDAO。新增書籍及更新書籍前，先去資料庫取得所有的書籍類別。

package com.example.demo.controller;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.ModelAttribute;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.servlet.ModelAndView;

import com.example.demo.dao.BookCategoryDAO;

import com.example.demo.dao.BookDAO;

import com.example.demo.entity.Book;

import com.example.demo.entity.BookCategory;

@Controller

public class BookController {

@Autowired

BookDAO dao;

@Autowired

BookCategoryDAO categoryDao;

@RequestMapping(value = "/bookCreate", method = RequestMethod.GET)

public ModelAndView openFormCreate() {

ModelAndView model = new ModelAndView("bookCreate");

Iterable<BookCategory> categories = categoryDao.findAll();

model.addObject("allBookCategories", categories);

return model;

}

@RequestMapping(value = "/bookCreate", method = RequestMethod.POST)

public ModelAndView processFormCreate(@ModelAttribute Book book) {

ModelAndView model = new ModelAndView("redirect:/bookRetrieveAll");

dao.save(book);

model.addObject(book);

return model;

}

@RequestMapping(value = { "/bookRetrieveAll", "/book" }, method = RequestMethod.GET)

public ModelAndView retrieveBooks() {

ModelAndView model = new ModelAndView("bookList");

Iterable<BookCategory> categories = categoryDao.findAll();

model.addObject("allBookCategories", categories);

BookCategory category = categories.iterator().next();//get first category

model.addObject("bookCategory", category);

Iterable<Book> books = dao.findAll();

model.addObject("allBooks", books);

return model;

}

@RequestMapping(value = { "/bookRetrieveByCategory" }, method = RequestMethod.POST)

public ModelAndView retrieveBooksByCategory(

@RequestParam(value = "id", required = false, defaultValue = "1") Long id) {

ModelAndView model = new ModelAndView("bookList");

Iterable<BookCategory> categories = categoryDao.findAll();

model.addObject("allBookCategories", categories);

BookCategory category = categoryDao.findOne(id);

model.addObject("bookCategory", category);

model.addObject("allBooks", category.getBooks());

return model;

}

@RequestMapping(value = "/bookUpdate", method = RequestMethod.GET)

public ModelAndView openFormUpdate(

@RequestParam(value = "id", required = false, defaultValue = "1") Long id) {

ModelAndView model = new ModelAndView("bookUpdate");

Book book = dao.findOne(id);

model.addObject(book);

Iterable<BookCategory> categories = categoryDao.findAll();

model.addObject("allBookCategories", categories);

return model;

}

@RequestMapping(value = "/bookUpdate", method = RequestMethod.POST)

public ModelAndView processFormUpdate(@ModelAttribute Book book) {

ModelAndView model = new ModelAndView("redirect:/bookRetrieveAll");

dao.save(book);

return model;

}

@RequestMapping(value = "/bookDelete", method = RequestMethod.GET)

public ModelAndView deleteBook(

@RequestParam(value = "id", required = false, defaultValue = "1") Long id) {

ModelAndView model = new ModelAndView("redirect:/bookRetrieveAll");

dao.delete(id);

return model;

}

以上範例多了一個功能，就是可以只列出特定類別的書籍。首先先從view取得使用者選擇的類別，再利用findOne取得該書籍類別，取得之後將類別下的書及回傳到view。

@RequestMapping(value = { "/bookRetrieveByCategory" }, method = RequestMethod.POST)

public ModelAndView retrieveBooksByCategory(

@RequestParam(value = "id", required = false, defaultValue = "1") Long id) {

ModelAndView model = new ModelAndView("bookList");

Iterable<BookCategory> categories = categoryDao.findAll();

model.addObject("allBookCategories", categories);

BookCategory category = categoryDao.findOne(id);

model.addObject("bookCategory", category);

model.addObject("allBooks", category.getBooks());

return model;

}

bookCreate.html中將所有書籍類別放在select裡。

<!DOCTYPE html>

<head>

<title>Create New Book</title>

</head>

<body>

名稱:<input type="text" name="name"/><br/>

類別:<select name = "bookCategory">

<option th:each="bookCategory : ${allBookCategories}"

th:value="${bookCategory.id}"

th:text="${bookCategory.name}">商業</option>

</select>

價格:<input type="number" name ="price"/><br/>

</form>

</body>

</html>

bookUpdate.html也將所有書籍類別放在select裡。

<!DOCTYPE html>

<head>

<title>Update Book</title>

</head>

<body>

名稱:<input type="text" name="name" th:field="*{name}"/><br/>

類別:<select name = "bookCategory" th:field="*{bookCategory}">

<option th:each="bookCategory : ${allBookCategories}"

th:value="${bookCategory.id}"

th:text="${bookCategory.name}">商業</option>

</select>

價格:<input type="number" name ="price" th:field="*{price}"/><br/>

</form>

</body>

</html>

bookList.html第一個部份是將所有書籍類別列出，供使用者選擇，原理與上面兩個範例類似。比較不同的是，field必須是bookCategory.id而不能是bookCategory。

<!DOCTYPE html>

<head>

<title>List ALl Books</title>

</head>

<body>

類別:<select name = "bookCategory" th:field="${bookCategory.id}">

<option th:each="bookCategory : ${allBookCategories}"

th:value="${bookCategory.id}"

th:text="${bookCategory.name}">商業</option>

</select>

</form>

<table>

<tr>

</tr>

<td th:text="*{bookCategory.name}">business</td>

</tr>

</table>

</body>

</html>

常見問題

如果我的table的主鍵是由多個欄位組合，那要如何寫呢?

要將這些欄位組合成一個類別 (詳參:

https://stackoverflow.com/questions/13032948/how-to-create-and-handle-composite-primary-key-in-jpa )。

為何資料庫不接受date?

java的date與資料庫l的date的儲存方式不同，所以，必須轉換:

http://www.developerscrappad.com/228/java/java-ee/ejb3-jpa-dealing-with-date-time-and-timestamp/

參考資料

04_DB_Environment.ppt