Spring Framework 为 SQL 数据库提供了广泛的支持。从直接使用 JdbcTemplate 进行 JDBC 访问到完全的对象关系映射（object relational mapping）技术，比如 Hibernate。Spring Data 提供了更多级别的功能，直接从接口创建的 Repository 实现，并使用了约定从方法名生成查询。
1、JDBC
1、创建项目，导入需要的依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency>2、配置数据源
spring: datasource: username: root password: 123456 url: jdbc:mysql://127.0.0.1:3306/databasesname?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8 driver-class-name: com.mysql.jdbc.Driver3、测试类代码
import org.junit.jupiter.api.Test;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import javax.sql.DataSource;import java.sql.Connection;import java.sql.SQLException;@SpringBootTestclass DataApplicationTests { @Autowired DataSource dataSource; @Test void contextLoads() throws SQLException { System.out.println(dataSource.getClass()); Connection connection = dataSource.getConnection(); System.out.println(connection); connection.close(); }}//可以看到默认配置的数据源为class com.zaxxer.hikari.HikariDataSource，我们没有经过任何配置，说明springboot默认情况下支持的就是这种数据源，可以在DataSourceProperties.java文件中查看具体的属性配置4、crud操作
1、有了数据源(com.zaxxer.hikari.HikariDataSource)，然后可以拿到数据库连接(java.sql.Connection)，有了连接，就可以使用连接和原生的 JDBC 语句来操作数据库
2、即使不使用第三方第数据库操作框架，如 MyBatis等，Spring 本身也对原生的JDBC 做了轻量级的封装，即 org.springframework.jdbc.core.JdbcTemplate。
3、数据库操作的所有 CRUD 方法都在 JdbcTemplate 中。
4、Spring Boot 不仅提供了默认的数据源，同时默认已经配置好了 JdbcTemplate 放在了容器中，程序员只需自己注入即可使用
5、JdbcTemplate 的自动配置原理是依赖 org.springframework.boot.autoconfigure.jdbc 包下的 org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration 类
import org.springframework.beans.factory.annotation.Autowired;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.PathVariable;import org.springframework.web.bind.annotation.RestController;import java.util.List;import java.util.Map;@RestControllerpublic class JDBCController { @Autowired JdbcTemplate jdbcTemplate; @GetMapping("/emplist") public List<Map<String,Object>> empList(){ String sql = "select * from emp"; List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql); return maps; } @GetMapping("/addEmp") public String addUser(){ String sql = "insert into emp(empno,ename) values(1111,'zhangsan')"; jdbcTemplate.update(sql); return "success"; } @GetMapping("/updateEmp/{id}") public String updateEmp(@PathVariable("id") Integer id){ String sql = "update emp set ename=? where empno = "+id; String name = "list"; jdbcTemplate.update(sql,name); return "update success"; } @GetMapping("/deleteEmp/{id}") public String deleteEmp(@PathVariable("id")Integer id){ String sql = "delete from emp where empno = "+id; jdbcTemplate.update(sql); return "delete success"; }}2、自定义数据源DruidDataSource
通过源码查看DataSourceAutoConfiguration.java
@Configuration(proxyBeanMethods = false)@ConditionalOnClass({ DataSource.class, EmbeddedDatabaseType.class })@EnableConfigurationProperties(DataSourceProperties.class)@Import({ DataSourcePoolMetadataProvidersConfiguration.class, DataSourceInitializationConfiguration.class })public class DataSourceAutoConfiguration { @Configuration(proxyBeanMethods = false) @Conditional(EmbeddedDatabaseCondition.class) @ConditionalOnMissingBean({ DataSource.class, XADataSource.class }) @Import(EmbeddedDataSourceConfiguration.class) protected static class EmbeddedDatabaseConfiguration { } @Configuration(proxyBeanMethods = false) @Conditional(PooledDataSourceCondition.class) @ConditionalOnMissingBean({ DataSource.class, XADataSource.class }) @Import({ DataSourceConfiguration.Hikari.class, DataSourceConfiguration.Tomcat.class, DataSourceConfiguration.Dbcp2.class, DataSourceConfiguration.Generic.class, DataSourceJmxConfiguration.class }) protected static class PooledDataSourceConfiguration { } /** * {@link AnyNestedCondition} that checks that either {@code spring.datasource.type} * is set or {@link PooledDataSourceAvailableCondition} applies. */ static class PooledDataSourceCondition extends AnyNestedCondition { PooledDataSourceCondition() { super(ConfigurationPhase.PARSE_CONFIGURATION); } @ConditionalOnProperty(prefix = "spring.datasource", name = "type") static class ExplicitType { } @Conditional(PooledDataSourceAvailableCondition.class) static class PooledDataSourceAvailable { } }1、添加druid的maven配置
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.12</version></dependency>2、添加数据源的配置
spring: datasource: username: root password: 123456 url: jdbc:mysql://192.168.85.111:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8 driver-class-name: com.mysql.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource3、测试发现数据源已经更改
4、druid是数据库连接池，可以添加druid的独有配置
spring: datasource: username: root password: 123456 url: jdbc:mysql://192.168.85.111:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8 driver-class-name: com.mysql.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource #Spring Boot 默认是不注入这些属性值的，需要自己绑定 #druid 数据源专有配置 initialSize: 5 minIdle: 5 maxActive: 20 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 1 FROM DUAL testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true #配置监控统计拦截的filters，stat:监控统计、log4j：日志记录、wall：防御sql注入 #如果允许时报错 java.lang.ClassNotFoundException: org.apache.log4j.Priority #则导入 log4j 依赖即可，Maven 地址： https://mvnrepository.com/artifact/log4j/log4j filters: stat,wall,log4j maxPoolPreparedStatementPerConnectionSize: 20 useGlobalDataSourceStat: true connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500测试类，发现配置的参数没有生效
import com.alibaba.druid.pool.DruidDataSource;import org.junit.jupiter.api.Test;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import javax.sql.DataSource;import java.sql.Connection;import java.sql.SQLException;@SpringBootTestclass DataApplicationTests { @Autowired DataSource dataSource; @Test void contextLoads() throws SQLException { System.out.println(dataSource.getClass()); Connection connection = dataSource.getConnection(); System.out.println(connection); DruidDataSource druidDataSource = (DruidDataSource)dataSource; System.out.println(druidDataSource.getMaxActive()); System.out.println(druidDataSource.getInitialSize()); connection.close(); }}需要定义druidDatasource的配置类，绑定参数
import com.alibaba.druid.pool.DruidDataSource;import org.springframework.boot.context.properties.ConfigurationProperties;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import javax.sql.DataSource;@Configurationpublic class DruidConfig { @ConfigurationProperties(prefix = "spring.datasource") @Bean public DataSource druidDataSource(){ return new DruidDataSource(); }}Druid数据源还具有监控的功能，并提供了一个web界面方便用户进行查看。
加入log4j的日志依赖
<!-- https://mvnrepository.com/artifact/log4j/log4j --> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency>向DruidConfig中添加代码，配置druid监控管理台的servlet
import com.alibaba.druid.pool.DruidDataSource;import com.alibaba.druid.support.http.StatViewServlet;import com.alibaba.druid.support.http.WebStatFilter;import org.springframework.boot.context.properties.ConfigurationProperties;import org.springframework.boot.web.servlet.FilterRegistrationBean;import org.springframework.boot.web.servlet.ServletRegistrationBean;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import javax.servlet.Servlet;import javax.sql.DataSource;import java.util.Arrays;import java.util.HashMap;import java.util.Map;@Configurationpublic class DruidConfig { @ConfigurationProperties(prefix = "spring.datasource") @Bean public DataSource druidDataSource(){ return new DruidDataSource(); } @Bean public ServletRegistrationBean druidServletRegistrationBean(){ ServletRegistrationBean<Servlet> servletRegistrationBean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*"); Map<String,String> initParams = new HashMap<>(); initParams.put("loginUsername","admin"); initParams.put("loginPassword","123456"); //后台允许谁可以访问 //initParams.put("allow", "localhost")：表示只有本机可以访问 //initParams.put("allow", "")：为空或者为null时，表示允许所有访问 initParams.put("allow",""); //deny：Druid 后台拒绝谁访问 //initParams.put("msb", "192.168.1.20");表示禁止此ip访问 servletRegistrationBean.setInitParameters(initParams); return servletRegistrationBean; } //配置 Druid 监控 之 web 监控的 filter //WebStatFilter：用于配置Web和Druid数据源之间的管理关联监控统计 @Bean public FilterRegistrationBean webStatFilter() { FilterRegistrationBean bean = new FilterRegistrationBean(); bean.setFilter(new WebStatFilter()); //exclusions：设置哪些请求进行过滤排除掉，从而不进行统计 Map<String, String> initParams = new HashMap<>(); initParams.put("exclusions", "*.js,*.css,/druid/*"); bean.setInitParameters(initParams); //"/*" 表示过滤所有请求 bean.setUrlPatterns(Arrays.asList("/*")); return bean; }}3、springboot配置多数据源并动态切换
DataSource是和线程绑定的，动态数据源的配置主要是通过继承AbstractRoutingDataSource类实现的，实现在AbstractRoutingDataSource类中的 protected Object determineCurrentLookupKey()方法来获取数据源，所以我们需要先创建一个多线程线程数据隔离的类来存放DataSource，然后在determineCurrentLookupKey()方法中通过这个类获取当前线程的DataSource，在AbstractRoutingDataSource类中，DataSource是通过Key-value的方式保存的，我们可以通过ThreadLocal来保存Key，从而实现数据源的动态切换。
1、修改配置文件类
spring: datasource: local: username: root password: 123456 driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8 remote: username: root password: 123456 driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://192.168.85.111:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-82、创建数据源枚举类
public enum DataSourceType { REMOTE, LOCAL}3、数据源切换处理
创建一个数据源切换处理类，有对数据源变量的获取、设置和情况的方法，其中threadlocal用于保存某个线程共享变量。
public class DynamicDataSourceContextHolder { /** * 使用ThreadLocal维护变量，ThreadLocal为每个使用该变量的线程提供独立的变量副本， * 所以每一个线程都可以独立地改变自己的副本，而不会影响其它线程所对应的副本。 */ private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>(); /** * 设置数据源变量 * @param dataSourceType */ public static void setDataSourceType(String dataSourceType){ System.out.printf("切换到{%s}数据源", dataSourceType); CONTEXT_HOLDER.set(dataSourceType); } /** * 获取数据源变量 * @return */ public static String getDataSourceType(){ return CONTEXT_HOLDER.get(); } /** * 清空数据源变量 */ public static void clearDataSourceType(){ CONTEXT_HOLDER.remove(); }}4、继承AbstractRoutingDataSource
动态切换数据源主要依靠AbstractRoutingDataSource。创建一个AbstractRoutingDataSource的子类，重写determineCurrentLookupKey方法，用于决定使用哪一个数据源。这里主要用到AbstractRoutingDataSource的两个属性defaultTargetDataSource和targetDataSources。defaultTargetDataSource默认目标数据源，targetDataSources（map类型）存放用来切换的数据源。
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;import javax.sql.DataSource;import java.util.Map;public class DynamicDataSource extends AbstractRoutingDataSource { public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) { super.setDefaultTargetDataSource(defaultTargetDataSource); super.setTargetDataSources(targetDataSources); // afterPropertiesSet()方法调用时用来将targetDataSources的属性写入resolvedDataSources中的 super.afterPropertiesSet(); } /** * 根据Key获取数据源的信息 * * @return */ @Override protected Object determineCurrentLookupKey() { return DynamicDataSourceContextHolder.getDataSourceType(); }}5、注入数据源
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;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.context.annotation.Primary;import javax.sql.DataSource;import java.util.HashMap;import java.util.Map;@Configurationpublic class DataSourceConfig { @Bean @ConfigurationProperties("spring.datasource.remote") public DataSource remoteDataSource() { return DataSourceBuilder.create().build(); } @Bean @ConfigurationProperties("spring.datasource.local") public DataSource localDataSource() { return DataSourceBuilder.create().build(); } @Bean(name = "dynamicDataSource") @Primary public DynamicDataSource dataSource(DataSource remoteDataSource, DataSource localDataSource) { Map<Object, Object> targetDataSources = new HashMap<>(); targetDataSources.put(DataSourceType.REMOTE.name(), remoteDataSource); targetDataSources.put(DataSourceType.LOCAL.name(), localDataSource); return new DynamicDataSource(remoteDataSource, targetDataSources); }}6、自定义多数据源切换注解
设置拦截数据源的注解，可以设置在具体的类上，或者在具体的方法上
import java.lang.annotation.*;@Target(ElementType.METHOD)@Retention(RetentionPolicy.RUNTIME)@Documentedpublic @interface DataSource { /** * 切换数据源名称 */ DataSourceType value() default DataSourceType.REMOTE;}7、AOP拦截类的实现
通过拦截上面的注解，在其执行之前处理设置当前执行SQL的数据源的信息，CONTEXT_HOLDER.set(dataSourceType)这里的数据源信息从我们设置的注解上面获取信息，如果没有设置就是用默认的数据源的信息。
import org.aspectj.lang.ProceedingJoinPoint;import org.aspectj.lang.annotation.Around;import org.aspectj.lang.annotation.Aspect;import org.aspectj.lang.annotation.Pointcut;import org.aspectj.lang.reflect.MethodSignature;import org.springframework.core.annotation.Order;import org.springframework.stereotype.Component;import java.lang.reflect.Method;@Aspect@Order(1)@Componentpublic class DataSourceAspect { @Pointcut("@annotation(com.mult.DataSource)") public void dsPointCut() { } @Around("dsPointCut()") public Object around(ProceedingJoinPoint point) throws Throwable { MethodSignature signature = (MethodSignature) point.getSignature(); Method method = signature.getMethod(); DataSource dataSource = method.getAnnotation(DataSource.class); if (dataSource != null) { DynamicDataSourceContextHolder.setDataSourceType(dataSource.value().name()); } try { return point.proceed(); } finally { // 销毁数据源 在执行方法之后 DynamicDataSourceContextHolder.clearDataSourceType(); } }}8、使用切换数据源注解
import org.springframework.beans.factory.annotation.Autowired;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RestController;import java.util.List;import java.util.Map;@RestControllerpublic class EmpController { @Autowired JdbcTemplate jdbcTemplate; @GetMapping("/local") @DataSource(value = DataSourceType.LOCAL) public List<Map<String, Object>> local(){ List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from emp"); return maps; } @GetMapping("/remote") @DataSource(value = DataSourceType.REMOTE) public List<Map<String, Object>> remote(){ List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from emp"); return maps; }}9、在启动项目的过程中会发生循环依赖的问题，直接修改启动类即可
import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)public class SpringbootDataApplication { public static void main(String[] args) { SpringApplication.run(SpringbootDataApplication.class, args); }}4、springboot整合mybatis
1、导入mybatis的依赖
<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter --><dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.1</version></dependency>2、配置数据源
spring: datasource: username: root password: 123456 url: jdbc:mysql://192.168.85.111:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8 driver-class-name: com.mysql.jdbc.Driver3、测试类
import com.alibaba.druid.pool.DruidDataSource;import org.junit.jupiter.api.Test;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import javax.sql.DataSource;import java.sql.Connection;import java.sql.SQLException;@SpringBootTestclass DataApplicationTests { @Autowired DataSource dataSource; @Test void contextLoads() throws SQLException { System.out.println(dataSource.getClass()); Connection connection = dataSource.getConnection(); System.out.println(connection); System.out.println(connection.getMetaData().getURL()); connection.close(); }}4、创建实体类
import java.sql.Date;import java.util.Objects;public class Emp { private Integer empno; private String ename; private String job; private Integer mgr; private Date hiredate; private Double sal; private Double comm; private Integer deptno; public Emp() { } public Emp(Integer empno, String ename) { this.empno = empno; this.ename = ename; } public Emp(Integer empno, String ename, String job, Integer mgr, Date hiredate, Double sal, Double comm, Integer deptno) { this.empno = empno; this.ename = ename; this.job = job; this.mgr = mgr; this.hiredate = hiredate; this.sal = sal; this.comm = comm; this.deptno = deptno; } public Integer getEmpno() { return empno; } public void setEmpno(Integer empno) { this.empno = empno; } public String getEname() { return ename; } public void setEname(String ename) { this.ename = ename; } public String getJob() { return job; } public void setJob(String job) { this.job = job; } public Integer getMgr() { return mgr; } public void setMgr(Integer mgr) { this.mgr = mgr; } public Date getHiredate() { return hiredate; } public void setHiredate(Date hiredate) { this.hiredate = hiredate; } public Double getSal() { return sal; } public void setSal(Double sal) { this.sal = sal; } public Double getComm() { return comm; } public void setComm(Double comm) { this.comm = comm; } public Integer getDeptno() { return deptno; } public void setDeptno(Integer deptno) { this.deptno = deptno; } @Override public boolean equals(Object o) { if (this == o) return true; if (!(o instanceof Emp)) return false; Emp emp = (Emp) o; return Objects.equals(empno, emp.empno) && Objects.equals(ename, emp.ename) && Objects.equals(job, emp.job) && Objects.equals(mgr, emp.mgr) && Objects.equals(hiredate, emp.hiredate) && Objects.equals(sal, emp.sal) && Objects.equals(comm, emp.comm) && Objects.equals(deptno, emp.deptno); } @Override public int hashCode() { return Objects.hash(empno, ename, job, mgr, hiredate, sal, comm, deptno); } @Override public String toString() { return "Emp{" + "empno=" + empno + ", ename='" + ename + '\'' + ", job='" + job + '\'' + ", mgr=" + mgr + ", hiredate=" + hiredate + ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + '}'; }}5、配置Mapper接口类
import org.apache.ibatis.annotations.Mapper;import org.springframework.stereotype.Repository;import java.util.List;@Mapper@Repositorypublic interface EmpMapper { List<Emp> selectEmp(); Emp selectEmpById(Integer empno); Integer addEmp(Emp emp); Integer updateEmp(Emp emp); Integer deleteEmp(Integer empno);}6、在resources下创建Emp.
<?7、添加配置文件
spring: datasource: username: root password: 123456 url: jdbc:mysql://192.168.85.111:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8 driver-class-name: com.mysql.jdbc.Drivermybatis: mapper-locations: classpath:mybatis/mapper/*.8、编写controller
import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RestController;import java.util.List;@RestControllerpublic class EmpController { @Autowired private EmpMapper empMapper; //选择全部用户 @GetMapping("/selectEmp") public String selectEmp(){ List<Emp> emps = empMapper.selectEmp(); for (Emp Emp : emps) { System.out.println(Emp); } return "ok"; } //根据id选择用户 @GetMapping("/selectEmpById") public String selectEmpById(){ Emp emp = empMapper.selectEmpById(1234); System.out.println(emp); return "ok"; } //添加一个用户 @GetMapping("/addEmp") public String addEmp(){ empMapper.addEmp(new Emp(1234,"heheda")); return "ok"; } //修改一个用户 @GetMapping("/updateEmp") public String updateEmp(){ empMapper.updateEmp(new Emp(1234,"heihei")); return "ok"; } //根据id删除用户 @GetMapping("/deleteEmp") public String deleteEmp(){ empMapper.deleteEmp(1234); return "ok"; }}9、测试即可
原文转载：http://www.shaoqun.com/a/489829.html
易麦：https://www.ikjzd.com/w/2048
ask me：https://www.ikjzd.com/w/2459
naver：https://www.ikjzd.com/w/1727
SpringFramework为SQL数据库提供了广泛的支持。从直接使用JdbcTemplate进行JDBC访问到完全的对象关系映射（objectrelationalmapping）技术，比如Hibernate。SpringData提供了更多级别的功能，直接从接口创建的Repository实现，并使用了约定从方法名生成查询。1、JDBC1、创建项目，导入需要的依赖<dependency
贝贝母婴网：https://www.ikjzd.com/w/1321
心怡物流：https://www.ikjzd.com/w/1327
海南兴隆热带花园好玩吗？：http://tour.shaoqun.com/a/5882.html
世界最贵食物盘点 一碗面2020美元：http://tour.shaoqun.com/a/60298.html
亚马逊站内广告要想不烧钱，做好以下几方面很关键 ：https://www.ikjzd.com/home/15962
No comments:
Post a Comment