一、JDBC
1、导入jdbc的依赖
<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、配置连接属性
-
新建一个application.yml文件
#配置数据源连接信息 spring: datasource: url: jdbc:mysql://192.168.241.135:3306/jdbc driver-class-name: com.mysql.jdbc.Driver username: root password: 123456 -
测试连接
@Autowired DataSource dataSource; @Test public void contextLoads() throws SQLException { System.out.println("数据源信息:"+dataSource.getClass()); Connection connection = dataSource.getConnection(); System.out.println("连接信息："+connection); connection.close(); } -
默认是用com.zaxxer.hikari.HikariDataSource作为数据源
数据源的相关配置都在DataSourceProperties里面
3、自动配置原理
-
在org\springframework\boot\autoconfigure\jdbc这个包下
-
参考DataSourceConfiguration，根据配置创建数据源，默认使用hikari连接池；可以使用spring.datasource.type指定自定义的数据源类型；
-
SpringBoot默认可以支持的数据源
org.apache.tomcat.jdbc.pool.DataSource、HikariDataSource、BasicDataSource -
自定义数据源类型
@Configuration(proxyBeanMethods = false) @ConditionalOnMissingBean({DataSource.class}) @ConditionalOnProperty(name = {"spring.datasource.type"}) static class Generic { Generic() {} @Bean DataSource dataSource(DataSourceProperties properties) { //使用DataSourceBuilder创建数据源，利用反射创建响应type的数据源，并且绑定相关属性 return properties.initializeDataSourceBuilder().build(); } } -
DataSourceInitializer：本质是一个ApplicationListener监听器；
作用：
1、runSchemaScripts();运行建表语句；
2、runDataScripts();运行插入数据的sql语句；
默认只需要将文件命名为：并放在类路径下
schema-*.sql、data-*.sql 默认规则：schema.sql，schema-all.sql； 可以使用 schema: - classpath:department.sql 指定位置也可以在application.yml属性文件中指定加载的sql文件配置，在springboot2.2.2中自动加载类路径下的schema-all.sql文件，需要设置
initialization-mode: always#配置数据源连接信息 spring: datasource: url: jdbc:mysql://192.168.241.135:3306/jdbc driver-class-name: com.mysql.jdbc.Driver username: root password: 123456 initialization-mode: always schema: - classpath:d.sql data: 是执行数据初始化 -
操作数据库：自动配置了JdbcTemplate操作数据库
二、整合Druid数据源
1、导入Druid数据源依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.18</version>
</dependency>
#还需要引入log4j的依赖
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
2、在application.yml文件中设置数据源的使用类型
spring:
#数据源基本配置
datasource:
url: jdbc:mysql://192.168.241.135:3306/jdbc
driver-class-name: com.mysql.jdbc.Driver
username: root
password: 123456
type: com.alibaba.druid.pool.DruidDataSource
3、数据源其他配置属性的使用
-
spring: datasource: 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，去掉后监控界面sql无法统计，'wall'用于防火墙 spring: datasource: filters: stat,wall,log4j maxPoolPreparedStatementPerConnectionSize: 20 useGlobalDataSourceStat: true connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500 -
需要自定义数据源配置类
在config包下新建一个类DruidConfig
@Configuration public class DruidConfig { //配置数据源 @Bean @ConfigurationProperties(prefix = "spring.datasource") public DataSource dataSource(){ return new DruidDataSource(); } //配置Druid监控 //1、配置一个管理后台的Servlet @Bean public ServletRegistrationBean statViewServlet(){ //创建Servlet注册实体 ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*"); Map<String,Object> initParams=new HashMap<>(); //设置登录用户名和密码 initParams.put("loginUsername","admin"); initParams.put("loginPassword","123456"); //设置ip白名单，默认允许所有访问 initParams.put("allow",""); //设置ip黑名单，禁止访问 initParams.put("deny","192.168.241.139"); bean.setInitParameters(initParams); return bean; } //2、配置一个web监控的filter @Bean public FilterRegistrationBean webStatFilter(){ //创建过滤器 FilterRegistrationBean bean = new FilterRegistrationBean(new WebStatFilter()); Map<String,Object> initParams=new HashMap<>(); //忽略过滤形式 initParams.put("exclusions","*.js,*.css,/druid/**"); //设置过滤器过滤路径 bean.setUrlPatterns(Arrays.asList("/*")); bean.setInitParameters(initParams); return bean; } }
4、访问：http://localhost:8080/druid/login.html ，输入用户名和密码登录即可。
三、整合Mybatis
1、引入Mybatis的依赖
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.1</version>
</dependency>
2、配置数据源相关信息，见上一节相关配置
spring:
#数据源基本配置
datasource:
url: jdbc:mysql://192.168.241.135:3306/mybatis
driver-class-name: com.mysql.jdbc.Driver
username: root
password: 123456
type: com.alibaba.druid.pool.DruidDataSource
#数据源其他配置
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，去掉后监控界面sql无法统计，'wall'用于防火墙
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
#执行建表
schema:
- classpath:sql/employee.sql
- classpath:sql/department.sql
initialization-mode: always
#mybatis配置(输出语句)
mybatis:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
3、给数据建表和创建JavaBean
4、注解版（编写mapper类）推荐使用
-
若在Mapper类上使用
@Repository注解，则需要在启动类上扫描mapper包，@MapperScan("com.itan.springboot.mapper")//指定这是一个操作数据库的mapper @Mapper public interface DepartmentMapper { //根据id查询 @Select("select * from department where id=#{id}") public Department getDeptById(Integer id); //删除 @Delete("delete from department where id=#{id}") public int delDeptById(Integer id); //增加 @Insert("insert into department(departmentName) values(#{departmentName})") public int addDept(Department department); //修改 @Update("update department set departmentName=#{departmentName} where id=#{id}") public int uppDept(Department department); } -
controller类
@RestController public class DeptController { @Autowired private DepartmentMapper departmentMapper; @GetMapping("dept/{id}") public Department getDepartment(@PathVariable("id") Integer id){ return departmentMapper.getDeptById(id); } @GetMapping("dept") public Department insertDept(Department department){ departmentMapper.addDept(department); System.out.println(department.getDepartmentName()); return department; } } -
自定义Mybatis的配置规则，给容器中添加一个ConfigurationCustomizer
@Configuration public class MybatisConfig { @Bean public ConfigurationCustomizer configurationCustomizer(){ return new ConfigurationCustomizer(){ @Override public void customize(org.apache.ibatis.session.Configuration configuration) { configuration.setMapUnderscoreToCamelCase(true); } }; } } -
配置扫描有三种方式
方式一：在每一个Mapper上加@Mapper注解
方式二：在启动类上加@MapperScan(basePackage={“com.itan.mapper”})
方式三：在自定义Mybatis配置类上加@MapperScan(basePackage={“com.itan.mapper”})
5、配置文件版建议使用
-
正常编写mapper类
public interface EmployeeMapper { // 获取 public Employee getEmpById(Integer id); // 添加 public void insertEmp(Employee employee); } -
在resources文件夹下新建一个mapper文件下，新建一个mapper.xml文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.itan.springboot.mapper.EmployeeMapper"> <select id="getEmpById" resultType="Employee"> SELECT * FROM employee WHERE id=#{id} </select> <insert id="insertEmp" parameterType="Employee"> insert into employee(lastName,email,gender,d_id) values(#{lastName},#{email},#{gender},#{d_id}) </insert> </mapper> -
在application.yml配置文件中引入mybatis的配置
#mybatis配置 mybatis: #开启打印SQL configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #引入mapper.xml文件 mapper-locations: - classpath:/mybatis/mapper/*.xml #别名注入 type-aliases-package: com.itan.springboot.entity
6、使用mybatis-config.xml
-
编写mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <mappers> <mapper resource="mybatis/mapper/deptMapper.xml"></mapper> <mapper resource="mybatis/mapper/EmpMapper.xml"></mapper> </mappers> </configuration> -
在application.yml配置文件中引入mybatis的配置
注意：在yml中
mybatis.config-location和mybatis.configuration不能同时存在mybatis: config-location: classpath:/mybatis/mybatis-config.xml
7、整合分页pageHelper
-
引入pageHelper的依赖
<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>5.1.2</version> </dependency> -
修改mybatis-config.xml文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!--分页插件--> <plugins> <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin> </plugins> <mappers> <mapper resource="mybatis/mapper/deptMapper.xml"></mapper> <mapper resource="mybatis/mapper/EmpMapper.xml"></mapper> </mappers> </configuration> -
测试
@Autowired private DepartmentMapper departmentMapper; @Test void contextLoads() { Page<Object> page = PageHelper.startPage(1, 5); List<Department> list = departmentMapper.getAll(); for (Department department:list){ System.out.println(department); } System.out.println("总条数："+page.getTotal()); }
8、整合pageHelper-Starter
-
引入pageHelper-Starter依赖
<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.2.13</version> </dependency> -
配置yaml
mybatis: #开启打印 configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #引入mapper.xml文件 mapper-locations: - classpath:/mybatis/mapper/*.xml #别名注入 type-aliases-package: com.itan.springboot.entity -
测试
@Autowired private DepartmentMapper departmentMapper; @Test void contextLoads() { Page<Object> page = PageHelper.startPage(1, 5); List<Department> list = departmentMapper.getAll(); for (Department department:list){ System.out.println(department); } System.out.println("总条数："+page.getTotal()); }
9、事务处理（注解）
-
事务加在service.impl（业务层）里面
-
加入spring-aop依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-aop</artifactId> </dependency> -
在启动类上启用事务，加注解
@EnableTransactionManagement和@EnableAspectJAutoProxy@SpringBootApplication @EnableTransactionManagement//开启事务注解 @EnableAspectJAutoProxy//切面 public class SpringBoot06MybatisApplication { public static void main(String[] args) { SpringApplication.run(SpringBoot06MybatisApplication.class, args); } } -
在每一个server的实现类上加
@Transactional注解1、默认是所有方法全部都参与事务
2、可以将查询的方法设置为只读事务，在方法上加
@Transactional(readOnly = true)@Service @Transactional//所有方法全部参与事务 public class DeptServiceImpl implements DeptService { @Autowired private DepartmentMapper departmentMapper; @Override @Transactional(readOnly = true)//只读事务 public Department getDeptById(Integer id) { return this.departmentMapper.getDeptById(id); } @Override public int delDeptById(Integer id) { return this.departmentMapper.delDeptById(id); } }3、controller类测试
@RestController public class DeptController { @Autowired private DeptServiceImpl deptServiceImpl; @GetMapping("dept/{id}") public Department getDepartment(@PathVariable("id") Integer id){ return deptServiceImpl.getDeptById(id); } @GetMapping("dept") public Department insertDept(Department department){ deptServiceImpl.addDept(department); return department; } }
10、使用mybatis逆向插件
- 安装插件并破解
- 选择数据源DataSource并添加
- 选择数据库并导入数据库
- 选中要导入的表右键---->选择Mybatis generator进入页面
- lombok中选择：@Data、@NoArgsConstructor、@AllArgsConstructor
- 选择生成要生成的service、service interface、或者使用mybatisPlus模板
- 点击Ok创建创建成功
四、整合SpringData JPA
1、JPA:是基于ORM的（Object Relational Mapping）
-
新建一个springboot工程导入依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency>
2、编写一个实体类（bean）和数据表进行映射，并且配置好映射关系
import javax.persistence.Entity;
import javax.persistence.Table;
//使用JPA注解配置映射关系
@Entity//告诉JPA这是一个实体类
@Table(name = "t_user")//指定和哪个数据表对应，如果省略默认表名就是类名的小写
public class User {
@Id//这是一个主键
@GeneratedValue(strategy = GenerationType.IDENTITY)//设置自增
private Integer id;
@Column(name = "last_name",length = 50)//这是和数据表对应的一个列
private String lastName;
@Column//省略默认列名就是属性名
private String email;
//省略get和set方法
}
3、编写一个Dao接口来操作实体类对应的数据表（Repository），
继承JpaRepository<实体类,实体类中主键的类型>
//继承JpaRepository来完成对数据库的操作
public interface UserDao extends JpaRepository<User,Integer> {
}
4、在application.yml对JPA配置
spring:
jpa:
#设置数据库方言
database-platform: org.hibernate.dialect.MySQL5Dialect
hibernate:
#更新或者创建数据表结构
ddl-auto: update
#显示SQL
show-sql: true
5、测试使用
@RestController
public class UserController {
@Autowired
private UserDao userDao;
//根据id查
@GetMapping("/user/{id}")
public User getUserById(@PathVariable("id") Integer id){
return userDao.getOne(id);
}
//增加
@GetMapping("/user")
public User insertUser(User user){
return userDao.save(user);
}
}
210
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
