WebAPI建置介紹
===
### 以 springboot 為例
Vincent
###### tags: `SpringBoot`
---
## 開始之前,有些事得說在前面
---
## 今天過後
- 能使用 **S**pring **T**ool **S**uite **4**
- 初步了解mybatis的功能
- 可以做出對資料庫檔案執行CRUD的web_api
---
## 樣版? 標準?
---
## clean code == refactoring
---
## 資料庫連線方式
- JDBC Template
- JPA
- **Mybatis**
---
## restful
- url的呈現
-- http://gfcweb/gfc/?MIval=/gfc/cntq010?item=CT&ct_no=1234
-- http://gfcweb/gfc/?MIval=/gfc/cntq010?item=CT&ct_no=1234&elev_no=1
-- http://gfcweb/gfc/?MIval=/gfc/cntq010/CT/1234
-- http://gfcweb/gfc/?MIval=/gfc/cntq010/CT/1234/1
---
---
### 安裝 mybatis generator plugin
----
### 安裝 mybatis generator plugin
----
### 安裝 mybatis generator plugin
---
---
### mybatis generator xml
```xml
<generatorConfiguration>
<properties resource="generator.properties" />
<context id="db1" targetRuntime="MyBatis3DynamicSql">
<!--可以自定義生成model的程式碼註釋 -->
<commentGenerator>
<!-- 是否去除自動生成的註釋 true：是 ： false:否 -->
<property name="suppressAllComments" value="true" />
<property name="suppressDate" value="true" />
<property name="addRemarkComments" value="true" />
</commentGenerator>
<!--配置資料庫連線 -->
<jdbcConnection driverClass="${jdbc.driverClass}"
connectionURL="${jdbc.db1.connectionURL}" userId="${jdbc.userId}"
password="${jdbc.password}">
</jdbcConnection>
<javaTypeResolver>
<property name="forceBigDecimals" value="false" />
</javaTypeResolver>
<!--指定生成model的路徑 -->
<javaModelGenerator
targetPackage="com.vt.demo16.entity.db1"
targetProject="demo-16/src/main/java">
<property name="enableSubPackages" value="true" />
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!--Build mapping file storage location -->
<sqlMapGenerator targetPackage="com.vt.demo16.dao.db1"
targetProject="demo-16/src/main/java">
<property name="enableSubPackages" value="true" />
</sqlMapGenerator>
<!--指定生成mapper介面的的路徑 -->
<javaClientGenerator type="XMLMAPPER"
targetPackage="com.vt.demo16.dao.db1"
targetProject="demo-16/src/main/java">
<property name="enableSubPackages" value="true" />
</javaClientGenerator>
```
----
### mybatis generator xml
```xml
<!-- 排除不生成column的幾種方式 -->
<table tableName="prgm000">
<ignoreColumn column="create_id" />
<ignoreColumn column="create_date" />
<ignoreColumn column="update_id" />
<ignoreColumn column="update_date" />
<table tableName="systables" mapperName="SystablesMapperDb1">
<ignoreColumnsByRegex pattern="(?i)col.*">
<except column="col01" />
<except column="col13" />
</ignoreColumnsByRegex>
</table>
<!-- 因為column命名與java保留字相同,產生ORM需更名 -->
<table tableName="syscolumnext" mapperName="SyscolumnextMapperDb1">
<columnRenamingRule searchString="^class$" replaceString="r_class" />
</table>
```
---
### Mybatis產生持久化模型的套件
- JOOQ(informix須付費)
- Mybatis Dynamic Sql
- Mybatis3(XML)
----
### Mybatis Dynamic Sql
- StatementProvider
- DSLCompleter
- [Mybatis Dynamic Sql](https://mybatis.org/mybatis-dynamic-sql/docs/introduction.html)
---
### 來寫個Web API吧
> [live demo]
----
### 來寫個Web API吧(config)
```java
package com.vt.demo16.config;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
//import org.apache.ibatis.transaction.managed.ManagedTransactionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
//import org.springframework.jdbc.datasource.DataSourceTransactionManager;
//import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
@Configuration
@MapperScan(basePackages = "com.vt.demo16.dao.db1", sqlSessionFactoryRef = "sqlSessionFactoryDb1", sqlSessionTemplateRef = "sqlSessionTemplateDb1")
public class DataSourceConfigDb1 {
@Autowired
@Bean(name = "db1Source")
@ConfigurationProperties(prefix = "spring.datasource.db1")
DataSource db1() {
return DruidDataSourceBuilder.create().build();
//return DataSourceBuilder.create().build();
}
@Bean(name = "db1TransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("db1Source") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean
SqlSessionFactory sqlSessionFactoryDb1() {
SqlSessionFactory sessionFactory = null;
try {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(this.db1());
//bean.setDataSource(null);
// bean.setTransactionFactory(new ManagedTransactionFactory());
sessionFactory = bean.getObject();
} catch (Exception e) {
e.printStackTrace();
}
return sessionFactory;
}
@Bean
SqlSessionTemplate sqlSessionTemplateDb1() {
return new SqlSessionTemplate(sqlSessionFactoryDb1());
}
}
```
----
### 來寫個Web API吧(config)
```java
package com.vt.demo16.config;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
@Configuration
@MapperScan(basePackages = "com.vt.demo16.dao.db2", sqlSessionFactoryRef = "sqlSessionFactoryDb2", sqlSessionTemplateRef = "sqlSessionTemplateDb2")
public class DataSourceConfigDb2 {
@Autowired
@Bean(name = "db2Source")
@ConfigurationProperties(prefix = "spring.datasource.db2")
DataSource db2() {
return DruidDataSourceBuilder.create().build();
}
@Bean(name = "db2TransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("db2Source") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean
SqlSessionFactory sqlSessionFactoryDb2() {
SqlSessionFactory sessionFactory = null;
try {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(this.db2());
// bean.setTransactionFactory(new ManagedTransactionFactory());
sessionFactory = bean.getObject();
} catch (Exception e) {
e.printStackTrace();
}
return sessionFactory;
}
@Bean
SqlSessionTemplate sqlSessionTemplateDb2() {
return new SqlSessionTemplate(sqlSessionFactoryDb2());
}
}
```
----
### 來寫個Web API吧(controller)
```java
package com.vt.demo16.controller;
import java.util.Date;
import java.util.List;
import java.util.Optional;
import java.util.function.Consumer;
import javax.management.loading.PrivateClassLoader;
import org.mybatis.dynamic.sql.SqlBuilder;
import org.mybatis.dynamic.sql.render.RenderingStrategies;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.mybatis.dynamic.sql.where.render.WhereClauseProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.DeleteMapping;
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.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.alibaba.druid.sql.parser.ParserException;
import com.vt.demo16.dao.db1.Cntm010DynamicSqlSupport;
import com.vt.demo16.dao.db1.Cntm010Mapper;
import com.vt.demo16.dao.db1.Cntm015DynamicSqlSupport;
import com.vt.demo16.dao.db1.Cntm015Mapper;
import com.vt.demo16.dao.db1.Cntm120DynamicSqlSupport;
import com.vt.demo16.dao.db1.Cntm120Mapper;
import com.vt.demo16.dao.db1.Join01Mapper;
import com.vt.demo16.entity.db1.Cntm010;
import com.vt.demo16.entity.db1.Cntm120;
import com.vt.demo16.model.Join01;
import com.vt.demo16.service.UpdateCntm010Service;
@RestController
@RequestMapping("/cntm010")
public class Cntm010Controller {
@Autowired
private Cntm010Mapper cntm010Mapper;
@Autowired
private Cntm015Mapper cntm015Mapper;
@Autowired
private Cntm120Mapper cntm120Mapper;
@Autowired
private Join01Mapper join01Mapper;
@Autowired
private UpdateCntm010Service updateCntm010Service;
@GetMapping("/sale/{saleNo}")
public List<Join01> SaleRead(@PathVariable Integer saleNo) {
SelectStatementProvider selectStatement = SqlBuilder
.select(Cntm010DynamicSqlSupport.item, Cntm010DynamicSqlSupport.ctNo, Cntm010DynamicSqlSupport.proCtr,
Cntm010DynamicSqlSupport.salCtr, Cntm010DynamicSqlSupport.custNo,
Cntm010DynamicSqlSupport.workZip, Cntm010DynamicSqlSupport.ctDate,
Cntm010DynamicSqlSupport.ctAmnt, Cntm015DynamicSqlSupport.bsItem, Cntm015DynamicSqlSupport.bsNo,
Cntm015DynamicSqlSupport.bsCfDate, Cntm015DynamicSqlSupport.comp2,
Cntm015DynamicSqlSupport.remark, Cntm015DynamicSqlSupport.salTntr,
Cntm015DynamicSqlSupport.srvTntr, Cntm015DynamicSqlSupport.salBegCost,
Cntm015DynamicSqlSupport.recTntr)
.from(Cntm010DynamicSqlSupport.cntm010).join(Cntm015DynamicSqlSupport.cntm015)
.on(Cntm010DynamicSqlSupport.item, SqlBuilder.equalTo(Cntm015DynamicSqlSupport.item))
.and(Cntm010DynamicSqlSupport.ctNo, SqlBuilder.equalTo(Cntm015DynamicSqlSupport.ctNo))
.where(Cntm010DynamicSqlSupport.saleNo, SqlBuilder.isEqualTo(saleNo)).build()
.render(RenderingStrategies.MYBATIS3);
List<Join01> rows = join01Mapper.selectJoin01List(selectStatement);
return rows;
}
@GetMapping("/salelist/{saleNo}")
@CrossOrigin("*")
public List<Cntm120> SaleRead2(@PathVariable Integer saleNo) {
SelectStatementProvider selectStatement = SqlBuilder
.select(Cntm120DynamicSqlSupport.item, Cntm120DynamicSqlSupport.ctNo, Cntm120DynamicSqlSupport.elevNo,
Cntm120DynamicSqlSupport.updateId)
.from(Cntm120DynamicSqlSupport.cntm120).join(Cntm010DynamicSqlSupport.cntm010)
.on(Cntm120DynamicSqlSupport.item, SqlBuilder.equalTo(Cntm010DynamicSqlSupport.item))
.and(Cntm120DynamicSqlSupport.ctNo, SqlBuilder.equalTo(Cntm010DynamicSqlSupport.ctNo))
.where(Cntm010DynamicSqlSupport.saleNo, SqlBuilder.isEqualTo(saleNo))
// .and(Cntm010DynamicSqlSupport.item, SqlBuilder.isNotEqualTo("ST"))
// .and(Cntm010DynamicSqlSupport.item, SqlBuilder.isNotIn("ST","BS","TT"))
// .fetchFirst(10).rowsOnly()
.build().render(RenderingStrategies.MYBATIS3);
return cntm120Mapper.selectMany(selectStatement);
}
@GetMapping("/get/{item}/{ctNo}")
public Optional<Cntm010> Read(@PathVariable String item, @PathVariable Integer ctNo) {
return cntm010Mapper.selectOne(c -> c.where(Cntm010DynamicSqlSupport.item, SqlBuilder.isEqualTo(item))
.and(Cntm010DynamicSqlSupport.ctNo, SqlBuilder.isEqualTo(ctNo)));
}
@GetMapping("/gets/{saleNo}")
public List<Cntm010> Read(@PathVariable Integer saleNo) throws ParserException {
try {
WhereClauseProvider whereClause = SqlBuilder
.where(Cntm010DynamicSqlSupport.saleNo, SqlBuilder.isEqualTo(saleNo)).build()
.render(RenderingStrategies.MYBATIS3);
return cntm010Mapper.selectWithWhereClause(whereClause);
} catch (Exception e) {
// TODO: handle exception
System.out.println(e);
}
return null;
}
@PostMapping("/update")
public void Update(@RequestBody List<Cntm010> requestCntm010) {
updateCntm010Service.updateTx1(requestCntm010);
// return 0;
}
@PostMapping("/update2")
public Integer Update2(@RequestBody List<Cntm010> requestCntm010) {
Integer rowCount = 0;
requestCntm010.forEach(c -> {
c.setUpdateId("Vincent");
c.setUpdateDate(new Date());
cntm010Mapper.updateByPrimaryKey(c);
});
return rowCount;
}
@DeleteMapping("/{item}/{ctNo}")
// @Transactional(rollbackFor = { RuntimeException.class,
// Exception.class }, transactionManager = "db1TransactionManager")
public Integer Delete(@PathVariable String item, @PathVariable Integer ctNo) {
Integer rowCount = 0;
rowCount += cntm120Mapper.delete(c -> c.where(Cntm120DynamicSqlSupport.item, SqlBuilder.isEqualTo(item))
.and(Cntm120DynamicSqlSupport.ctNo, SqlBuilder.isEqualTo(ctNo)));
rowCount += cntm015Mapper.deleteByPrimaryKey(item, ctNo);
rowCount += cntm010Mapper.deleteByPrimaryKey(item, ctNo);
return rowCount;
}
}
```
----
### 來寫個Web API吧(service)
```java
package com.vt.demo16.service;
import java.util.Date;
import java.util.List;
import java.util.Optional;
import org.mybatis.dynamic.sql.SqlBuilder;
import org.mybatis.dynamic.sql.render.RenderingStrategies;
import org.mybatis.dynamic.sql.update.UpdateDSL;
import org.mybatis.dynamic.sql.update.render.UpdateStatementProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import com.vt.demo16.dao.db1.Cntm010DynamicSqlSupport;
import com.vt.demo16.dao.db1.Cntm010Mapper;
import com.vt.demo16.entity.db1.Cntm010;
@Service
public class UpdateCntm010Service {
@Autowired
private Cntm010Mapper cntm010Mapper;
public void updateTx1(List<Cntm010> c1) {
c1.forEach(r -> {
System.out.printf("post:%s-%d-%s-%s-%s\n", r.getItem(), r.getCtNo(), r.getReptName(), r.getBuldName(),
r.getMemo1());
try {
UpdateStatementProvider updateStatement = SqlBuilder.update(Cntm010DynamicSqlSupport.cntm010)
.set(Cntm010DynamicSqlSupport.buldName).equalTo(r.getBuldName())
.set(Cntm010DynamicSqlSupport.reptName).equalTo(r.getReptName())
.set(Cntm010DynamicSqlSupport.memo1).equalTo(r.getMemo1())
.set(Cntm010DynamicSqlSupport.updateId).equalTo("TWH")
.set(Cntm010DynamicSqlSupport.updateDate).equalTo(new Date())
.where(Cntm010DynamicSqlSupport.item, SqlBuilder.isEqualTo(r.getItem()))
.and(Cntm010DynamicSqlSupport.ctNo, SqlBuilder.isEqualTo(r.getCtNo())).build()
.render(RenderingStrategies.MYBATIS3);
int rows = cntm010Mapper.update(updateStatement);
System.out.printf("update: %s-%d-%s-%s-%s, row: %d\n", r.getItem(), r.getCtNo(), r.getBuldName(),
r.getReptName(), r.getMemo1(), rows);
if (r.getCtNo() == 104)
throw new RuntimeException();
} catch (Exception e) {
throw e;
}
});
}
}
```
----
### 來寫個Web API吧(model)
```java
package com.vt.demo16.model;
import java.math.BigDecimal;
import java.util.Date;
public class Join01{
private String item;
private Integer ctNo;
private String proCtr;
private String salCtr;
private Integer custNo;
private String workZip;
private Date ctDate;
private BigDecimal ctAmnt;
private String bsItem;
private BigDecimal bsNo;
private Date bsCfDate;
private String comp2;
private String remark;
private String salTntr;
private String srvTntr;
private BigDecimal salBegCost;
private String recTntr;
public String getItem() {
return item;
}
public void setItem(String item) {
this.item = item == null ? null : item.trim();
}
public Integer getCtNo() {
return ctNo;
}
public void setCtNo(Integer ctNo) {
this.ctNo = ctNo;
}
public String getProCtr() {
return proCtr;
}
public void setProCtr(String proCtr) {
this.proCtr = proCtr == null ? null : proCtr.trim();
}
public String getSalCtr() {
return salCtr;
}
public void setSalCtr(String salCtr) {
this.salCtr = salCtr == null ? null : salCtr.trim();
}
public Integer getCustNo() {
return custNo;
}
public void setCustNo(Integer custNo) {
this.custNo = custNo;
}
public String getWorkZip() {
return workZip;
}
public void setWorkZip(String workZip) {
this.workZip = workZip == null ? null : workZip.trim();
}
public Date getCtDate() {
return ctDate;
}
public void setCtDate(Date ctDate) {
this.ctDate = ctDate;
}
public BigDecimal getCtAmnt() {
return ctAmnt;
}
public void setCtAmnt(BigDecimal ctAmnt) {
this.ctAmnt = ctAmnt;
}
public String getBsItem() {
return bsItem;
}
public void setBsItem(String bsItem) {
this.bsItem = bsItem == null ? null : bsItem.trim();
}
public BigDecimal getBsNo() {
return bsNo;
}
public void setBsNo(BigDecimal bsNo) {
this.bsNo = bsNo;
}
public Date getBsCfDate() {
return bsCfDate;
}
public void setBsCfDate(Date bsCfDate) {
this.bsCfDate = bsCfDate;
}
public String getComp2() {
return comp2;
}
public void setComp2(String comp2) {
this.comp2 = comp2 == null ? null : comp2.trim();
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark == null ? null : remark.trim();
}
public String getSalTntr() {
return salTntr;
}
public void setSalTntr(String salTntr) {
this.salTntr = salTntr == null ? null : salTntr.trim();
}
public String getSrvTntr() {
return srvTntr;
}
public void setSrvTntr(String srvTntr) {
this.srvTntr = srvTntr == null ? null : srvTntr.trim();
}
public BigDecimal getSalBegCost() {
return salBegCost;
}
public void setSalBegCost(BigDecimal salBegCost) {
this.salBegCost = salBegCost;
}
public String getRecTntr() {
return recTntr;
}
public void setRecTntr(String recTntr) {
this.recTntr = recTntr == null ? null : recTntr.trim();
}
}
```
----
### 來寫個Web API吧(dao)
```java
package com.vt.demo16.dao.db1;
import java.util.List;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.mybatis.dynamic.sql.util.SqlProviderAdapter;
import com.vt.demo16.model.Join01;
public interface Join01Mapper {
@SelectProvider(type=SqlProviderAdapter.class, method="select")
@Results(id="Join01Result", value = {
@Result(column="item", property="item", jdbcType=JdbcType.CHAR, id=true),
@Result(column="ct_no", property="ctNo", jdbcType=JdbcType.DECIMAL, id=true),
@Result(column="pro_ctr", property="proCtr", jdbcType=JdbcType.CHAR),
@Result(column="sal_ctr", property="salCtr", jdbcType=JdbcType.CHAR),
@Result(column="cust_no", property="custNo", jdbcType=JdbcType.DECIMAL),
@Result(column="work_zip", property="workZip", jdbcType=JdbcType.CHAR),
@Result(column="ct_date", property="ctDate", jdbcType=JdbcType.DATE),
@Result(column="ct_amnt", property="ctAmnt", jdbcType=JdbcType.DECIMAL),
@Result(column="bs_item", property="bsItem", jdbcType=JdbcType.CHAR),
@Result(column="bs_no", property="bsNo", jdbcType=JdbcType.DECIMAL),
@Result(column="bs_cf_date", property="bsCfDate", jdbcType=JdbcType.DATE),
@Result(column="comp_1", property="comp1", jdbcType=JdbcType.CHAR),
@Result(column="comp_2", property="comp2", jdbcType=JdbcType.CHAR),
@Result(column="comp_3", property="comp3", jdbcType=JdbcType.CHAR),
@Result(column="a_no", property="aNo", jdbcType=JdbcType.CHAR),
@Result(column="remark", property="remark", jdbcType=JdbcType.CHAR),
@Result(column="sal_tntr", property="salTntr", jdbcType=JdbcType.CHAR),
@Result(column="srv_tntr", property="srvTntr", jdbcType=JdbcType.CHAR),
@Result(column="sal_beg_cost", property="salBegCost", jdbcType=JdbcType.DECIMAL),
@Result(column="rec_tntr", property="recTntr", jdbcType=JdbcType.CHAR)
})
List<Join01> selectJoin01List(SelectStatementProvider selectStatement);
}
```
----
### 來寫個Web API吧(test)
```java
package com.vt.demo16;
import static org.assertj.core.api.Assertions.assertThat;
import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;
import org.junit.jupiter.api.Test;
import org.mybatis.dynamic.sql.SqlBuilder;
import org.mybatis.dynamic.sql.render.RenderingStrategies;
import org.mybatis.dynamic.sql.select.SelectDSLCompleter;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import com.vt.demo16.dao.db1.Cntm010DynamicSqlSupport;
import com.vt.demo16.dao.db1.Cntm010Mapper;
import com.vt.demo16.dao.db1.Cntm015DynamicSqlSupport;
import com.vt.demo16.dao.db1.Cntm015Mapper;
import com.vt.demo16.dao.db1.Join01Mapper;
import com.vt.demo16.entity.db1.Cntm010;
import com.vt.demo16.entity.db1.Cntm015;
import com.vt.demo16.model.Join01;
import lombok.extern.slf4j.Slf4j;
@Slf4j
@SpringBootTest
class Demo16ApplicationTests {
@Autowired
Cntm010Mapper cntm010Mapper;
@Autowired
Cntm015Mapper cntm015Mapper;
@Autowired
Join01Mapper join01Mapper;
@Test
public void selectAll1() {
List<Cntm010> cntm010List = cntm010Mapper.select(SelectDSLCompleter.allRows());
for (Cntm010 cntm010 : cntm010List) {
System.out.println(cntm010.getSaleNo());
}
}
@Test
public void selectAll2() {
List<Cntm010> cntm010List = cntm010Mapper.select(c -> c);
for (Cntm010 cntm010 : cntm010List) {
System.out.println(cntm010.getBuldName());
}
}
@Test
public void selectWhere() {
List<Cntm010> cntm010List = cntm010Mapper
.select(c -> c.where(Cntm010DynamicSqlSupport.item, SqlBuilder.isEqualTo("GF")));
for (Cntm010 cntm010 : cntm010List) {
System.out.println(cntm010.getWorkAddr());
}
}
@Test
public void selectOne() {
Optional<Cntm010> cntm010 = cntm010Mapper
.selectOne(c -> c.where(Cntm010DynamicSqlSupport.saleNo, SqlBuilder.isEqualTo(814))
.and(Cntm010DynamicSqlSupport.ctNo, SqlBuilder.isEqualTo(4121)));
if (cntm010.isPresent()) {
System.out.println(cntm010.get().getCtDate());
}
}
@Test
public void selectOne2() {
SelectStatementProvider selectStatementProvider = SqlBuilder
.select(Cntm015DynamicSqlSupport.bsNo, Cntm015DynamicSqlSupport.comp2,
Cntm015DynamicSqlSupport.salBegCost)
.from(Cntm015DynamicSqlSupport.cntm015)
.where(Cntm015DynamicSqlSupport.bsItem, SqlBuilder.isEqualTo("BS"))
.and(Cntm015DynamicSqlSupport.bsNo, SqlBuilder.isGreaterThan(new BigDecimal(20119))).build()
.render(RenderingStrategies.MYBATIS3);
List<Cntm015> cntm015List = cntm015Mapper.selectMany(selectStatementProvider);
for (Cntm015 cntm015 : cntm015List) {
System.out.println(cntm015.getBsNo());
}
}
@Test
public void testSelect() {
SelectStatementProvider selectStatement = SqlBuilder
.select(Cntm010DynamicSqlSupport.item, Cntm010DynamicSqlSupport.ctNo, Cntm010DynamicSqlSupport.proCtr,
Cntm010DynamicSqlSupport.salCtr, Cntm010DynamicSqlSupport.custNo,
Cntm010DynamicSqlSupport.workZip, Cntm010DynamicSqlSupport.ctDate,
Cntm010DynamicSqlSupport.ctAmnt, Cntm015DynamicSqlSupport.bsItem, Cntm015DynamicSqlSupport.bsNo,
Cntm015DynamicSqlSupport.bsCfDate, Cntm015DynamicSqlSupport.comp2,
Cntm015DynamicSqlSupport.remark, Cntm015DynamicSqlSupport.salTntr,
Cntm015DynamicSqlSupport.srvTntr, Cntm015DynamicSqlSupport.salBegCost,
Cntm015DynamicSqlSupport.recTntr)
.from(Cntm010DynamicSqlSupport.cntm010).join(Cntm015DynamicSqlSupport.cntm015)
.on(Cntm010DynamicSqlSupport.item, SqlBuilder.equalTo(Cntm015DynamicSqlSupport.item))
.and(Cntm010DynamicSqlSupport.ctNo, SqlBuilder.equalTo(Cntm015DynamicSqlSupport.ctNo))
.where(Cntm010DynamicSqlSupport.item, SqlBuilder.isEqualTo("GF"))
.and(Cntm010DynamicSqlSupport.saleNo, SqlBuilder.isEqualTo(814)).build()
.render(RenderingStrategies.MYBATIS3);
List<Join01> rows = join01Mapper.selectJoin01List(selectStatement);
rows.stream().forEach(c -> {
System.out.printf("%s-%d-%s-%.0f\n", c.getItem(), c.getCtNo(), c.getBsItem(), c.getBsNo());
});
assertThat(rows).hasSize(3);
}
}
```
----
### 來寫個Web API吧
---
### 其他補充
- annotation
- test
- 再次強調 **C**onvention **O**ver **C**onfiguration
- [maven repository](https://mvnrepository.com/search?q=springboot)
---
# END
---
{"metaMigratedAt":"2023-06-16T12:09:03.580Z","metaMigratedFrom":"Content","title":"WebAPI建置介紹","breaks":true,"contributors":"[{\"id\":\"4190ad5d-3e7c-40f0-84e4-9a81138fcfbf\",\"add\":26305,\"del\":711}]"}