Spring Data
JDBC方式
- 创建新项目
- 选中要导入的依赖
在spring boot 中使用JDBC连接数据库也变的非常简单
- 编写基本配置项(application.yaml)
spring:
datasource:
username: root
password: nyc991019
# mysql8.0 以上需要配置时区:serverTimezone=Asia/Shanghai
url: jdbc:mysql://localhost:3306/ssm_bookstore?useUnicode=true$characterEncoding=utf-8
# mysql8.0 以上需要 com.mysql.cj.jdbc.Driver
driver-class-name: com.mysql.jdbc.Driver
- 测试
spring boot 会帮我们自动的配置,只需要注入DataSource即可
@SpringBootTest
class Springboot06DataApplicationTests {
@Autowired
DataSource dataSource;
@Test
void contextLoads() throws SQLException {
//查看默认数据源
System.out.println(dataSource.getClass()); //Hikari
//直接使用dataSource获取数据库连接
Connection connection = dataSource.getConnection(); //jdbc
System.out.println(connection);
connection.close();
}
}
- CRUD
jdbcTemplate里面有许多执行SQL的模板,相当于以前自己封装的执行SQL的方法,我们只需要注入jdbcTemplate,jdbc操作数据库也就变的非常简单
@RestController
public class JdbcController {
JdbcTemplate jdbcTemplate;
//自动注入jdbcTemplate模板
@Autowired
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
//查询所有
@RequestMapping("/getList")
public List<Map<String,Object>> bookList(){
String sql = "select * from books";
List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
return mapList;
}
//增
@RequestMapping("/addData")
public String addData(){
String sql = "insert into books values(null,'aa',11,'aaaaa')";
int i = jdbcTemplate.update(sql);
if (i > 0){
return "ok";
}
return "error";
}
//删
@RequestMapping("/delData/{id}")
public String deleteData(@PathVariable("id") int id){
String sql = "delete from books where bookId = " + id;
int i = jdbcTemplate.update(sql);
if (i > 0){
return "ok";
}
return "error";
}
//改
@RequestMapping("/updateData/{id}")
public String updateData(@PathVariable("id") int id){
String sql = "update books set bookName = ?, bookCounts = ?, bookDetail = ? where bookId = " + id;
Object[] obj = new Object[3];
obj[0] = "bb";
obj[1] = 100;
obj[2] = "bbbbbbbb";
int i = jdbcTemplate.update(sql,obj);
if (i > 0){
return "ok";
}
return "error";
}
}
自定义数据源
Druid
Druid是阿里巴巴开源平台的一个数据库连接池实现,结合了C3P0、DBCP、PROXOOL等数据连接池的优点,同时加入了日志监控。
Druid配置详解(copy)
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://${url}:${port}/${数据库名}?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false&allowMultiQueries=true&useAffectedRows=true
username: ${username}
password: ${password}
druid:
initial-size: 10 # 初始化时建立物理连接的个数。初始化发生在显示调用init方法,或者第一次getConnection时
min-idle: 10 # 最小连接池数量
maxActive: 200 # 最大连接池数量
maxWait: 60000 # 获取连接时最大等待时间,单位毫秒。配置了maxWait之后,缺省启用公平锁,并发效率会有所下降,如果需要可以通过配置
timeBetweenEvictionRunsMillis: 60000 # 关闭空闲连接的检测时间间隔.Destroy线程会检测连接的间隔时间,如果连接空闲时间大于等于minEvictableIdleTimeMillis则关闭物理连接。
minEvictableIdleTimeMillis: 300000 # 连接的最小生存时间.连接保持空闲而不被驱逐的最小时间
validationQuery: SELECT 1 FROM DUAL # 验证数据库服务可用性的sql.用来检测连接是否有效的sql 因数据库方言而差, 例如 oracle 应该写成 SELECT 1 FROM DUAL
testWhileIdle: true # 申请连接时检测空闲时间,根据空闲时间再检测连接是否有效.建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRun
testOnBorrow: false # 申请连接时直接检测连接是否有效.申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。
testOnReturn: false # 归还连接时检测连接是否有效.归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。
poolPreparedStatements: true # 开启PSCache
maxPoolPreparedStatementPerConnectionSize: 20 #设置PSCache值
connectionErrorRetryAttempts: 3 # 连接出错后再尝试连接三次
breakAfterAcquireFailure: true # 数据库服务宕机自动重连机制
timeBetweenConnectErrorMillis: 300000 # 连接出错后重试时间间隔
asyncInit: true # 异步初始化策略
remove-abandoned: true # 是否自动回收超时连接
remove-abandoned-timeout: 1800 # 超时时间(以秒数为单位)
transaction-query-timeout: 6000 # 事务超时时间
filters: stat,wall,log4j2 # stat: 监控统计, log4j: 日志 wall:防御sqlz
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
web-stat-filter:
enabled: true
url-pattern: "/*"
exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"
stat-view-servlet:
url-pattern: "/druid/*"
allow:
deny:
reset-enable: false
login-username: admin
login-password: admin
修改数据源,只需要在配置数据源中加上type
spring:
datasource:
username: root
password: nyc991019
url: jdbc:mysql://localhost:3306/ssm_bookstore?useUnicode=true$characterEncoding=utf-8
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
Druid配置类
package com.nych.springboot06data.config;
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.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class DruidConfig {
//引入配置
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druidDataSource(){
return new DruidDataSource();
}
//后台监控: web.xml
@Bean
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*");
//配置后台账号密码
HashMap<String, String> initParameters = new HashMap<>();
initParameters.put("loginUsername","admin");
initParameters.put("loginPassword","123456"); //key 是固定的;
//允许谁可以访问 initParameters.put("allow","localhost");
initParameters.put("allow","");
//禁止谁访问
// initParameters.put("xxx","ip地址");
bean.setInitParameters(initParameters); //设置初始化参数
return bean;
}
//filter 过滤器
@Bean
public FilterRegistrationBean webStatFilter(){
FilterRegistrationBean bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());
//可以过滤那些请求
Map<String, String> initParameters = new HashMap<>();
//排除不需要过滤的
initParameters.put("exclusions","*.js,*.css,/druid/*");
bean.setInitParameters(initParameters);
return bean;
}
}
如果有log4j警告爆红,在resources下添加以下配置即可
log4j.properties
log4j.rootLogger=DEBUG, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
配置完成,运行项目,访问localhost:8080/druid即可进入druid数据库监控页面
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XZ5iRKry-1638514577861)(SpringBoot.assets/image-20211003103441708.png)]
整合Mybatis
导入依赖
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
编写数据库配置
application.properties:
#配置数据库连接信息
#数据库用户名
spring.datasource.username=root
#密码
spring.datasource.password=123456
#数据库链接
spring.datasource.url=jdbc:mysql://localhost:3306/ssm_bookstore?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
#驱动
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
数据实体类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Books {
private int bookID;
private String bookName;
private int bookCounts;
private String bookDetail;
}
mapper接口
//@Mapper 表示这是一个mybatis的mapper类 也可以在主类中使用@MapperScan("com.nych.mapper")扫描mapper包
@Mapper
// @Repository 注册组件
public interface BooksMapper {
// @Select("select * from books")
List<Books> queryBooks();
Books queryBookById(@Param("id") int id);
int addBook(Books book);
int updateBooks(Books book);
int deleteBooksById(@Param("id") int id);
}
在resources下编写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.nych.mapper.BooksMapper">
<select id="queryBooks" resultType="books">
select * from books
</select>
<select id="queryBookById" resultType="books">
select * from books where bookID = #{id}
</select>
<insert id="addBook" parameterType="books">
insert into books
values(null,#{bookName},#{bookCounts},#{bookDetail})
</insert>
<update id="updateBooks" parameterType="books">
update books
set bookName = #{bookName},bookCounts=#{bookCounts},bookDetail=#{bookDetail}
where bookID = #{bookId};
</update>
<delete id="deleteBooksById" parameterType="_int">
delete from books where bookID = #{id}
</delete>
</mapper>