[Java] 利用Spring Data JPA 接駁 SQL server

2018-07-06 14_33_46-localhost_8080_main_findByStaffID_staffID=47

Java Persistence API (JPA) 是 Java 內建的library 用作ORM 互動. 著名的衍生品之一就是Hibernate, 而Spring 亦基於Hibernate, 在Spring Boot 中建立了Spring Data JPA. 在示範中, 會以Gradle project 接駁SQL server 及存取資料.示範步驟如下.

加入Spring 及SQL server library.
修改Build.gradle 如下:

/*
 * This build file was generated by the Gradle 'init' task.
 *
 * This generated file contains a sample Java Library project to get you started.
 * For more details take a look at the Java Libraries chapter in the Gradle
 * user guide available at https://docs.gradle.org/4.3/userguide/java_library_plugin.html
 */
buildscript {
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:2.0.3.RELEASE")
    }
}

// Apply the java-library plugin to add support for Java Library
apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'idea'
apply plugin: 'org.springframework.boot'
apply plugin: 'io.spring.dependency-management'

// In this section you declare where to find the dependencies of your project
repositories {
    mavenCentral()
    maven { url "https://repository.jboss.org/nexus/content/repositories/releases" }
}https://www.chunho-ling.com/wp-admin/post-new.php#
sourceCompatibility = 1.8
targetCompatibility = 1.8

dependencies {
    compile("org.springframework.boot:spring-boot-starter-web")
    compile("org.springframework.boot:spring-boot-starter-data-jpa")
    compile("com.fasterxml.jackson.core:jackson-databind")
    compile("com.microsoft.sqlserver:mssql-jdbc")
    testCompile("junit:junit")
}

取得library.
於Project 中按右鍵並選取 Gradle > Refresh Gradle Project.
設定資料庫.
於/src/main/resources/ 建立檔案 application.property, 並修改內容如下.
```
spring.jpa.hibernate.ddl-auto=none
spring.datasource.url=jdbc:sqlserver://[[SQL server name]]:[[Port no.]];databaseName=[[Database name]]
spring.datasource.username=[[User name here]]
spring.datasource.password=[[Password]]
```
spring.jpa.hibernate.ddl-auto 是spring JPA 對database 的結構設定, 類似於Entity Framework 的code first. 而spring.datasource.url 指的是connection string. 會因應不同database 而有所分別.

建立Model.
於/src/main/java/models 中建立檔案Staff.java, 並輸入以下內容.

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;

import com.fasterxml.jackson.annotation.JsonIdentityInfo;
import com.fasterxml.jackson.annotation.ObjectIdGenerators;

@Entity
public class Staff {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int StaffID;
	private String Name;
	
	public int getStaffID() {
		return StaffID;
	}

	public void setStaffID(int staffID) {
		StaffID = staffID;
	}

	public String getName() {
		return Name;
	}

	public void setName(String name) {
		Name = name;
	}
}

@Entity 是JPA annotation, 說明這是database entity, 若database table 與class name 不同, 則須要用@Table(name=”[[Table Name]]”) 說明. @Id 說明這是table 內的primary key, 而@GenerateValue 則說明如果建立key. 在這裡利用AUTO, 即是使用database 預設.

建立Repository.
於/src/main/java/repositories 中建立檔案StaffRepository.java, 並輸入以下內容.

import java.util.List;

import org.matilda.backend.roi.models.Staff;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

public interface StaffRepository extends CrudRepository<Staff, Integer> {
	@Query(value="SELECT s FROM Staff s WHERE StaffID=:staffID")
	public Staff findByStaffID(@Param("staffID") int staffID);
	@Query(value="SELECT s FROM Staff s WHERE Name=:name", nativeQuery=true)
	public List<Staff> findByName(@Param("name") String name);
}

@Query 是建立Hibernate Query Language (HQL), 一種類似SQL 的query. 而@Param 則設定@Query 內的參數.

建立Controller.
於/src/main/java/controllers 中建立檔案Staff.java, 並輸入以下內容.

import java.util.List;

import org.matilda.backend.roi.Repositories.StaffRepository;
import org.matilda.backend.roi.models.Staff;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
@RequestMapping(path="/main")
public class MainController {
	private static final Logger log = LoggerFactory.getLogger(MainController.class);
	@Autowired
	private StaffRepository staffRepository;
	
	@GetMapping(path="/findByStaffID")
	public @ResponseBody Staff findByStaffID(@RequestParam int staffID) {
		log.info("findByStaffID() started. staffID="+staffID);
		Staff result=staffRepository.findByStaffID(staffID);
		return result;
	}
	
	@GetMapping(path="/findByStaffName")
	public @ResponseBody List<Staff> findByStaffName(@RequestParam String name) {
		log.info("findByStaffName() started. name="+name);
		List<Staff> result=staffRepository.findByName(name);
		return result;
	}
}

@Controller 會在spring 中認定為controller class; @RequestMapping 設定了Controller 的root path; @Autowired 則是Spring boot 中一個DI , 將之前要設定好的bean 或compoent 叫用. @GetMapping 是@RequestMapping(method=GET) 的縮寫.

建立Spring Application.
於/src/main/java/中建立檔案Application.java, 並輸入以下內容.

@SpringBootApplication
public class Application {
	private static final Logger log = LoggerFactory.getLogger(Application.class);
	public static void main(String[] args) {
		log.info("Backend service ROI Starting...");
		SpringApplication.run(Application.class);
		log.info("Backend service ROI Startup completed.");
	}
}

@SpringBootApplication 會執行三個annotation 的動作: