Spring Boot EP 16:再論存取資料庫 – Insert、Update、Delete
前言
在【Spring Boot EP 10:透過Spring Data JPA與Hibernate讀取(select)資料庫】已經分享如何透過Spring Data JPA與Hibernate從資料庫讀取資料,不過資料庫的操作還包括了新增資料(Insert)、更新資料(Update)、刪除資料(Delete),本篇就針對上述三個行為來實作。
接續EP 10的的內容,新的Insert、Update、Delete語法直接在”Repositories/MarketRepository.java”實作。
Insert – 新增市場
步驟1:在Repositories/MarketRepository.java加入以下程式片段,來實作Insert資料。
// 註記為事務交易
@Transactional
// 註記為資料異動
@Modifying
// 使用標準的SQL Insert語法來寫入資料,冒號+名稱來表示要帶入的參數
@Query(value = "INSERT INTO STOCK_MARKET.MARKETS(MARKET_CODE, MARKET_NAME) VALUES (:MARKET_CODE, :MARKET_NAME);", nativeQuery = true)
// 回傳Int表示新增的資料筆數,透過@Param("")來對應SQL語法裡面的參數,@Param("MARKET_CODE")對應:MARKET_CODE,以此類推
public int addMarket(@Param("MARKET_CODE") String MARKET_CODE, @Param("MARKET_NAME") String MARKET_NAME);
步驟2:編輯Controllers/MarketController.java,新增一個函數,將Restful API與剛剛的SQL語法對應起來。
// 定義一個HTTP Post方法與URL Routing
@PostMapping("market/add")
// @RequestBody定義資料由HTTP Body帶入以MarketModel為模型的資料
public RspBody addMarket(@RequestBody MarketModel MarketModel) {
// 呼叫方才建立的函數來寫入資料,並取得資料寫入數量的回傳值
int intEffect = marketRepository.addMarket(MarketModel.getMarketCode(), MarketModel.getMarketName());
// 宣告HTTP Response的內容
String rspMessage = null;
// 如果資料異動大於0,表示成功,小於等於0表示沒有寫入任何資料
if (intEffect > 0) {
rspMessage = "Market Added. Effect row: " + intEffect;
} else {
rspMessage = "Nothing Added.";
}
return new RspBody("0000", "Success", rspMessage);
}
Update – 更新市場名稱
步驟1:在Repositories/MarketRepository.java加入以下程式片段,來實作Update資料。
@Transactional
@Modifying
@Query(value = "UPDATE STOCK_MARKET.MARKETS SET MARKET_NAME=:MARKET_NAME WHERE MARKET_CODE=:MARKET_CODE", nativeQuery = true)
public int modifyByMarketCode(@Param("MARKET_NAME") String MARKET_NAME, @Param("MARKET_CODE") String MARKET_CODE);
步驟2:編輯Controllers/MarketController.java,新增一個函數,將Restful API與剛剛的SQL語法對應起來。
@PostMapping("market/modify")
public RspBody modifyByMarketCode(@RequestBody MarketModel MarketModel) {
// 將更新的資料數量回傳
int intEffect = marketRepository.modifyByMarketCode(MarketModel.getMarketName(), MarketModel.getMarketCode());
String rspMessage = null;
if (intEffect > 0) {
rspMessage = "Market Modified. Effect row: " + intEffect;
} else {
rspMessage = "Nothing Modified.";
}
return new RspBody("0000", "Success", rspMessage);
}
Delete – 刪除市場
步驟1:在Repositories/MarketRepository.java加入以下程式片段,來實作Delete資料。
@Transactional
@Modifying
@Query(value = "DELETE FROM STOCK_MARKET.MARKETS WHERE MARKET_CODE=:MARKET_CODE", nativeQuery = true)
public int removeMarket(@Param("MARKET_CODE") String MARKET_CODE);
步驟2:編輯Controllers/MarketController.java,新增一個函數,將Restful API與剛剛的SQL語法對應起來。
@PostMapping("market/remove")
// 參數為一個簡單的String,不需使用Json傳入
public RspBody removeMarket(@RequestBody String MARKET_CODE) {
int intEffect = marketRepository.removeMarket(MARKET_CODE);
String rspMessage = null;
if (intEffect > 0) {
rspMessage = "Market Removed. Effect row: " + intEffect;
} else {
rspMessage = "Nothing Removed.";
}
return new RspBody("0000", "Success", rspMessage);
}
測試
步驟1:新增一個新的市場資訊。
步驟2:更新市場名稱。
步驟3:刪除剛剛新增的市場資訊。(參數為String,不需要使用Json來當作參數載體)
範例原始碼
Repositories/MarketRepository.java
package stockmarket.jovepater.com.stockmarket.Repositories;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
import javax.transaction.Transactional;
import stockmarket.jovepater.com.stockmarket.Models.MarketModel;
@Repository
// JpaRepository(T, ID),T=資料表的類別(Model),ID=Primary Key的型別
public interface MarketRepository extends JpaRepository<MarketModel, String> {
// 讀取全部的市場清單
@Query(value = "SELECT MARKET_NAME, MARKET_CODE FROM STOCK_MARKET.MARKETS;", nativeQuery = true)
// 將資料存放進一個List回傳
public List<MarketModel> findAllDetails();
// 依照市場代碼為條件撈出市場清單
// MARKET_CODE=:MARKET_CODE,冒號後面表示要帶入的參數
@Query(value = "SELECT MARKET_NAME, MARKET_CODE FROM STOCK_MARKET.MARKETS WHERE MARKET_CODE=:MARKET_CODE", nativeQuery = true)
// @Param("MARKET_CODE") String
// MARKET_CODE,@Param內要與SQL語法內的參數一致,後面的函數參數則可以自訂,通常會一致,比較好辨認
public List<MarketModel> findByMarketCode(@Param("MARKET_CODE") String MARKET_CODE);
// 註記為事務交易
@Transactional
// 註記為資料異動
@Modifying
// 使用標準的SQL Insert語法來寫入資料,冒號+名稱來表示要帶入的參數
@Query(value = "INSERT INTO STOCK_MARKET.MARKETS(MARKET_CODE, MARKET_NAME) VALUES (:MARKET_CODE, :MARKET_NAME);", nativeQuery = true)
// 回傳Int表示新增的資料筆數,透過@Param("")來對應SQL語法裡面的參數,@Param("MARKET_CODE")對應:MARKET_CODE,以此類推
public int addMarket(@Param("MARKET_CODE") String MARKET_CODE, @Param("MARKET_NAME") String MARKET_NAME);
@Transactional
@Modifying
@Query(value = "UPDATE STOCK_MARKET.MARKETS SET MARKET_NAME=:MARKET_NAME WHERE MARKET_CODE=:MARKET_CODE", nativeQuery = true)
public int modifyByMarketCode(@Param("MARKET_NAME") String MARKET_NAME, @Param("MARKET_CODE") String MARKET_CODE);
@Transactional
@Modifying
@Query(value = "DELETE FROM STOCK_MARKET.MARKETS WHERE MARKET_CODE=:MARKET_CODE", nativeQuery = true)
public int removeMarket(@Param("MARKET_CODE") String MARKET_CODE);
}
Controllers/MarketController.java
package stockmarket.jovepater.com.stockmarket.Controllers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;
import stockmarket.jovepater.com.stockmarket.Classes.RspBody;
import stockmarket.jovepater.com.stockmarket.Models.MarketModel;
import stockmarket.jovepater.com.stockmarket.Repositories.MarketRepository;
@RestController
public class MarketController {
// 宣告使用MarketRepository
@Autowired
MarketRepository marketRepository;
@GetMapping("markets")
public RspBody getAllMarkets() {
// 呼叫marketRepository.findAllDetails()取得所有市場清單
return new RspBody("0000", "Success", marketRepository.findAllDetails());
}
// 在URL中設定一個名為MARKET_CODE的參數,參數的部分要用{}刮起來
@GetMapping("market/{MARKET_CODE}")
// @PathVariable("MARKET_CODE")表示這個參數從URL帶進來,與@GetMapping中定義的名稱要一致
public RspBody getMarketByMarketCode(@PathVariable("MARKET_CODE") String MARKET_CODE) {
// 透過marketRepository.findByMarketCode()並帶入參數取得指定市場代碼的市場清單
return new RspBody("0000", "Success", marketRepository.findByMarketCode(MARKET_CODE));
}
// 定義一個HTTP Post方法與URL Routing
@PostMapping("market/add")
// @RequestBody定義資料由HTTP Body帶入以MarketModel為模型的資料
public RspBody addMarket(@RequestBody MarketModel MarketModel) {
// 呼叫方才建立的函數來寫入資料,並取得資料寫入數量的回傳值
int intEffect = marketRepository.addMarket(MarketModel.getMarketCode(), MarketModel.getMarketName());
// 宣告HTTP Response的內容
String rspMessage = null;
// 如果資料異動大於0,表示成功,小於等於0表示沒有寫入任何資料
if (intEffect > 0) {
rspMessage = "Market Added. Effect row: " + intEffect;
} else {
rspMessage = "Nothing Added.";
}
return new RspBody("0000", "Success", rspMessage);
}
@PostMapping("market/modify")
public RspBody modifyByMarketCode(@RequestBody MarketModel MarketModel) {
// 將更新的資料數量回傳
int intEffect = marketRepository.modifyByMarketCode(MarketModel.getMarketName(), MarketModel.getMarketCode());
String rspMessage = null;
if (intEffect > 0) {
rspMessage = "Market Modified. Effect row: " + intEffect;
} else {
rspMessage = "Nothing Modified.";
}
return new RspBody("0000", "Success", rspMessage);
}
@PostMapping("market/remove")
// 參數為一個簡單的String,不需使用Json傳入
public RspBody removeMarket(@RequestBody String MARKET_CODE) {
int intEffect = marketRepository.removeMarket(MARKET_CODE);
String rspMessage = null;
if (intEffect > 0) {
rspMessage = "Market Removed. Effect row: " + intEffect;
} else {
rspMessage = "Nothing Removed.";
}
return new RspBody("0000", "Success", rspMessage);
}
}
相關資源
~ END ~