依賴 | 版本 |
---|---|
Springboot | 2.1.8 |
tk | 4.0.3 |
mapper | 4.0.3 |
druid | 1.1.10 |
依賴:
<!--核心配置-->
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.8.RELEASE</version>
<relativePath/>
</parent>
<!--版本控制-->
<properties>
<mybatis.version>2.0.0</mybatis.version>
<mapper.version>4.0.3</mapper.version>
<druid.version>1.1.10</druid.version>
</properties>
<!--依賴管理-->
<dependencies>
<!--web專案-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--test-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--mysql連線池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
<!--aspectj-->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
</dependency>
<!--mybatis依賴 @Select,@insert,@Delete,@Update等註解-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis.version}</version>
</dependency>
<!--tk-->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper</artifactId>
<version>${mapper.version}</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!--mysql驅動-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>
配置檔案:
server:
port: 80
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/db_parent?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&serverTimezone=UTC
username: root
password: dhdadj.98
type: com.alibaba.druid.pool.DruidDataSource
#監控
druid:
#連線池配置
max-active: 30
initial-size: 3
min-idle: 3
max-wait: 1200
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 30000
pool-prepared-statements: true
max-open-prepared-statements: 30
validation-query: select 1 from dual
test-while-idle: true
test-on-borrow: false
test-on-return: false
#URI監控
web-stat-filter:
enabled: true
#監控所有uri
url-pattern: /*
#不監控以下uri
exclusions: /druid/*
#session監控
session-stat-enable: true
profile-enable: true
#spring監控
aop-patterns: com.database.*
#登入配置
stat-view-servlet:
login-username: root
login-password: touzhen.98
mybatis:
mapper-locations: classpath:mapper/*.xml
#對映實體類
type-aliases-package: com.database.entity
#實體類屬性駝峰轉換
configuration.map-underscore-to-camel-case: true
#列印sql
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
實體類:
package com.database.entity.db1;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
import java.util.Date;
/**
* (User)實體類
*
* @author makejava
* @since 2020-08-04 22:33:26
*/
@Data
@Table(name = "user")
public class User implements Serializable {
private static final long serialVersionUID = 913577582974147917L;
@Id
private Integer id;
/**
* 使用者
*/
private String name;
/**
* 備註
*/
private String remart;
/**
* 郵箱地址
*/
private String email;
/**
* 手機號碼
*/
private String mobile;
/**
* 密碼
*/
private String pwd;
/**
* 使用者建立時間
*/
@DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss")
private Date createDate;
public User() {
}
private User(Builder builder) {
this.id = builder.id;
this.name = builder.name;
this.remart = builder.remart;
this.email = builder.email;
this.mobile = builder.mobile;
this.pwd = builder.pwd;
this.createDate = builder.createDate;
}
@Override
public String toString() {
return "Builder{" +
"id=" + id +
", name='" + name + '\'' +
", remart='" + remart + '\'' +
", email='" + email + '\'' +
", mobile='" + mobile + '\'' +
", pwd='" + pwd + '\'' +
", createDate=" + createDate +
'}';
}
public static class Builder {
private Integer id;
/**
* 使用者
*/
private String name;
/**
* 備註
*/
private String remart;
/**
* 郵箱地址
*/
private String email;
/**
* 手機號碼
*/
private String mobile;
/**
* 密碼
*/
private String pwd;
/**
* 使用者建立時間
*/
private Date createDate;
public Builder setId(Integer id) {
this.id = id;
return this;
}
public Builder setName(String name) {
this.name = name;
return this;
}
public Builder setRemart(String remart) {
this.remart = remart;
return this;
}
public Builder setEmail(String email) {
this.email = email;
return this;
}
public Builder setMobile(String mobile) {
this.mobile = mobile;
return this;
}
public Builder setPwd(String pwd) {
this.pwd = pwd;
return this;
}
public Builder setCreateDate(Date createDate) {
this.createDate = createDate;
return this;
}
public User build() {
return new User(this);
}
}
}
package com.database.entity.db1;
import lombok.Getter;
import lombok.Setter;
import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
import java.util.Date;
@Table(name="t_sys_database")
@Getter
@Setter
public class Database {
/**
*
*/
private static final long serialVersionUID = 2959567867207055588L;
@Id
@Column(name = "id")
protected Integer id;
private String code;
private String name;
private String type;
private String dc;
private String url;
private String userName;
private String passWord;
private String creator;
private Object creatorId;
private Date createDate;
private Boolean deleted;
}
package com.database.entity.db2;
import lombok.Data;
import javax.persistence.Table;
import java.io.Serializable;
import java.util.Date;
/**
* (AdRole)實體類
*
* @author makejava
* @since 2020-08-17 09:41:28
*/
@Data
@Table(name = "ad_role")
public class AdRole implements Serializable {
private static final long serialVersionUID = 500075010950193252L;
private Integer id;
private String name;
private Date addDate;
private String remart;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getAddDate() {
return addDate;
}
public void setAddDate(Date addDate) {
this.addDate = addDate;
}
public String getRemart() {
return remart;
}
public void setRemart(String remart) {
this.remart = remart;
}
}
通用Mapper:
package com.database.common;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;
/**
* @projectName(專案名稱): sb-parent
* @Description(描述): 整合
* @Author(開發人員): 樑港生
* @date(日期): 2020.08.04 22:35
* @Version(版本): V1.0
*/
public interface TkMapper<T> extends Mapper<T>, MySqlMapper<T> {
}
自定義sql和tk並用的Mapper:
package com.database.dao.db1;
import com.database.common.TkMapper;
import com.database.entity.db1.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
/**
* @projectName(專案名稱): sb-parent
* @Description(描述):mybatis mapper測試
* @Author(開發人員): 樑港生
* @date(日期): 2020.08.04 22:34
* @Version(版本): V1.0
*/
public interface UserMapper extends TkMapper<User> {
/**
* @Description(描述): 查詢所有
* @Author(開發人員): Kukyu
* @date(日期): 2020.08.05 00:35
* @Param(引數):
* @return(返回值):
*/
@Select("select * from user")
List<User> findAll();
/**
* @Description(描述): 新增使用者
* @Author(開發人員): Kukyu
* @date(日期): 2020.08.05 00:38
* @Param(引數):
* @return(返回值):
*/
@Insert("insert into user(`name`,pwd,email,mobile,remart) values(#{name},#{pwd},#{email},#{mobile},#{remart})")
@SelectKey(statement = "select last_insert_id()", keyProperty = "id", before = false, resultType = int.class)
int insert(User user);
/**
* @Description(描述): 根據使用者更新密碼
* @Author(開發人員): Kukyu
* @date(日期): 2020.08.05 00:39
* @Param(引數):
* @return(返回值):
*/
@Update("update user set pwd = #{pwd} where name = #{name}")
int update(User user);
/**
* @Description(描述): 根據id刪除
* @Author(開發人員): Kukyu
* @date(日期): 2020.08.05 00:40
* @Param(引數):
* @return(返回值):
*/
@Delete("delete FROM user where id = #{id}")
int deleteById(User user);
/**
* @Description(描述): 根據id查詢
* @Author(開發人員): Kukyu
* @date(日期): 2020.08.05 01:17
* @Param(引數):
* @return(返回值):
*/
@SelectProvider(method = "selectById", type = Provider.class)
User selectById(int id);
/**
* @Description(描述): 根據id更新
* @Author(開發人員): Kukyu
* @date(日期): 2020.08.05 01:17
* @Param(引數):
* @return(返回值):
*/
@UpdateProvider(method = "updateById", type = Provider.class)
int updateById(User user);
/**
* @Description(描述): 根據id刪除
* @Author(開發人員): Kukyu
* @date(日期): 2020.08.05 01:17
* @Param(引數):
* @return(返回值):
*/
@DeleteProvider(method = "deleteByIds", type = Provider.class)
int deleteByIds(int id);
/**
* @Description(描述): 新增資料
* @Author(開發人員): Kukyu
* @date(日期): 2020.08.05 01:22
* @Param(引數):
* @return(返回值):
*/
@InsertProvider(method = "insertByUser",type = Provider.class)
int insertByUser(User user);
/**
* @Description(描述): 動態sql拼接
* @Author(開發人員): Kukyu
* @date(日期): 2020.08.05 01:17
* @Param(引數):
* @return(返回值):
*/
class Provider {
public static String selectById(int id) {
StringBuilder sb = new StringBuilder();
sb.append("select * from user where id = #{id}");
return sb.toString();
}
public static String updateById(User user) {
StringBuilder sb = new StringBuilder();
sb.append("update user set name = #{name} where id = #{id}");
return sb.toString();
}
public static String deleteByIds(int id) {
StringBuilder sb = new StringBuilder();
sb.append("delete from user where id = #{id}");
return sb.toString();
}
public static String insertByUser(User user) {
StringBuilder sb = new StringBuilder();
sb.append("insert into user(`name`,pwd,email,mobile,remart) values(#{name},#{pwd},#{email},#{mobile},#{remart})");
return sb.toString();
}
}
}
package com.database.dao.db2;
import com.database.common.TkMapper;
import com.database.entity.db2.AdRole;
/**
* @projectName(專案名稱): sb-parent
* @Description(描述):
* @Author(開發人員): 樑港生
* @date(日期): 2020.08.17 09:42
* @Version(版本): V1.0
*/
public interface AdRoleMapper extends TkMapper<AdRole> {
}
省略服務層跳控制層:
package com.database.controller.db1;
import com.database.dao.db1.UserMapper;
import com.database.entity.db1.User;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.List;
/**
* @projectName(專案名稱): sb-parent
* @Description(描述):
* @Author(開發人員): 樑港生
* @date(日期): 2020.08.17 09:43
* @Version(版本): V1.0
*/
@RestController
@RequestMapping("/user")
public class UserController {
@Resource
private UserMapper userMapper;
@RequestMapping("/findAll")
public List<User> findAll(){
List<User> all = userMapper.findAll();
return all;
}
}
package com.database.controller.db2;
import com.database.dao.db2.AdRoleMapper;
import com.database.dataSource.DataSourceSwitch;
import com.database.entity.db2.AdRole;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.List;
/**
* @projectName(專案名稱): sb-parent
* @Description(描述):
* @Author(開發人員): 樑港生
* @date(日期): 2020.08.17 09:44
* @Version(版本): V1.0
*/
@RestController
@RequestMapping("/role")
public class AdRoleController {
@Resource
private AdRoleMapper adRoleMapper;
@RequestMapping("/findAll")
public List<AdRole> findAll(){
DataSourceSwitch.set("db_admin");
List<AdRole> all = adRoleMapper.selectAll();
DataSourceSwitch.set(null);
return all;
}
}
druid監控管理地址(http://127.0.0.1/druid)
主資料來源測試介面(http://127.0.0.1/user/findAll)
其他資料來源測試介面(http://127.0.0.1/role/findAll)
自問自答:
1、如何切換資料來源?
- 先要在資料庫表寫入資料來源配置
- 修改資料來源配置類(程式碼太多不貼上出來給原始碼)DynamicDataSourceRegister的MapperScan註解掃描路徑
- DataSourceSwitch.set(“db_admin”)切換資料來源,使用完記得切換回主資料來源DataSourceSwitch.set(null)
2、druid作用?
- 檢視專案效能
- 細化自己的操作
- 賬號密碼在yml配置檔案裡
3、專案結構說明
原始碼地址(gitee):(HTTPS)gitee.com/kukycol/sb-parent.git
本作品採用《CC 協議》,轉載必須註明作者和本文連結