发布时间:2023-05-16 08:30
个人博客:http://blog.kunpw.cn/
本节讲解数据库连接以及mybatis使用
新建项目,配置依赖:
IDEA连接数据库(可选,可以使用其它软件直接打开mysql显示):
如图,我已连接自己的mysql中springboot_mybatis数据库,其中已配置简单表user:
新建application.yml
文件(可以使用默认的application.properties
),并对该项目连接mysql数据库:
spring:
datasource:
username: root
password: password
# serverTimezone=UTC是时区,
url: jdbc:mysql://localhost:3306/springboot_mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
driver-class-name: com.mysql.cj.jdbc.Driver
新建controller包,并在其下新建JDBCController.java
文件,代码如下(运行后已可正常访问并简易增删查改数据库):
package com.kun.demodata.controller;
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;
@RestController
public class JDBCController {
// Template是Spring默认配置好的模板bean,可以拿来即用
@Autowired
JdbcTemplate jdbcTemplate;
// 查询数据库所有信息,没有实体类时使用Map获取数据库中的数据
@GetMapping("/userList")
public List<Map<String,Object>> userList(){
String sql = "select * from user";
List<Map<String,Object>> mapList = jdbcTemplate.queryForList(sql);
return mapList;
}
// 增加用户信息
@GetMapping("/addUser")
public String addUser(){
String sql = "insert into springboot_mybatis.user(id,name,pwd) values(4,'小芳','ffffff')";
jdbcTemplate.update(sql);
return "addUser-ok";
}
// 修改用户信息
@GetMapping("/updateUser/{id}")
public String updateUser(@PathVariable("id") int id){
// 占位符语法格式
String sql = "update springboot_mybatis.user set name=?,pwd=? where id="+id;
// 封装
Object[] objects = new Object[2];
objects[0] = "小黄";
objects[1] = "password";
// 直接传参
jdbcTemplate.update(sql,objects);
return "updateUser-ok";
}
// 删除用户信息
@GetMapping("/deleteUser/{id}")
public String deleteUser(@PathVariable("id") int id){
String sql = "delete from springboot_mybatis.user where id=?";
// 直接传参
jdbcTemplate.update(sql,id);
return "deleteUser-ok";
}
}
Druid数据源出自阿里,它最强大的功能在于其日志监控
查看默认数据源,在test文件中查看Spring默认数据源,hikari是速度最快的数据源:
@Autowired
DataSource dataSource;
@Test
void contextLoads() {
// 查看默认数据源 com.zaxxer.hikari.HikariDataSource
System.out.println(dataSource.getClass());
}
pom.xml
增加druid依赖以及log4j依赖,并加载:
<dependency>
<groupId>com.alibabagroupId>
<artifactId>druidartifactId>
<version>1.2.4version>
dependency>
<dependency>
<groupId>log4jgroupId>
<artifactId>log4jartifactId>
<version>1.2.12version>
dependency>
application.yml
文件修改默认配置:
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
# Spring默认不注入这些属性配置,需要自己绑定,一般根据公司需要个性绑定,也是druid专有属性
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
# 配置filter,stat:监控统计;log4j:日志记录;wall:防御sql注入
filters: stat,wall,log4j
maxPoolPreparedStatmentPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
新建Config包,并在其下新建DruidConfig.java
文件,代码如下:
package com.kun.demodata.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.servlet.Filter;
import javax.sql.DataSource;
import java.util.HashMap;
@Configuration
public class DruidConfig {
// 配置绑定路径,即此处创建的属性可以直接被spring.datasource以下使用
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druidDataSource(){
return new DruidDataSource();
}
// 后台监控功能,因为SpringBoot内置了Servlet容器,所以没有web.xml配置
@Bean
public ServletRegistrationBean statViewServlet(){
// 配置请求访问路径
ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*");
// 后台登录初始配置,从源码查看参数性质再重写源码参数
HashMap<String, String> initParameters = new HashMap<>();
// 配置后台登录账号密码,且此处key值loginUsername、loginPassword唯一绑定,不可更改
initParameters.put("loginUsername","admin");
initParameters.put("loginPassword","123456");
// 允许访问,此处写入localhost或具体账户则仅可访问
initParameters.put("allow","");
// 禁止访问,initParameters.put("name","192.168.123.123");
bean.setInitParameters(initParameters);
return bean;
}
// filter过滤功能
@Bean
public FilterRegistrationBean webStatFilter(){
FilterRegistrationBean<Filter> bean = new FilterRegistrationBean<>();
bean.setFilter(new WebStatFilter());
// 设置过滤请求参数
HashMap<String, String> initParameters = new HashMap<>();
initParameters.put("exclusions","*.js,*.css,/druid/*");
bean.setInitParameters(initParameters);
return bean;
}
}
注:如果运行报错为log4j Warning,需要在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
这里依然使用上一个项目文件,连接数据库与上一致,无需重复。
在pom.xml
文件中引入依赖:
<dependency>
<groupId>org.projectlombokgroupId>
<artifactId>lombokartifactId>
dependency>
<dependency>
<groupId>org.mybatis.spring.bootgroupId>
<artifactId>mybatis-spring-boot-starterartifactId>
<version>2.1.1version>
dependency>
新建实体层pojo,下建User.java
实体类:
package com.kun.demodata.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private int id;
private String name;
private String pwd;
}
新建mapper目录,该目录实质就是dao目录,即数据访问层,下建UserMapper.java
文件:
package com.kun.demodata.mapper;
import com.kun.demodata.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import java.util.List;
// @Mapper表示这是一个mybatis接口类,@Repository即注入SpringBoot
@Mapper
@Repository
public interface UserMapper {
List<User> queryUserList();
User queryUserById();
int addUser(User user);
int updateUser(User user);
int deleteUser(int id);
}
在resources目录下新建mybatis.mapper目录,下建对应UserMapper.xml文件,用于实现sql数据访问:
<mapper namespace="com.kun.demodata.mapper.UserMapper">
<select id="queryUserList" resultType="User">
select * from user
select>
<select id="queryUserById" resultType="User">
select * from user where id = #{id}
select>
<insert id="addUser" parameterType="User">
insert into user (id,name,pwd) values (#{id},#{name},#{pwd})
insert>
<update id="updateUser" parameterType="User">
update user set name = #{name},pwd = #{pwd} where id = #{id}
update>
<delete id="deleteUser" parameterType="User">
delete from user where id = #{id}
delete>
mapper>
除此之外,还需添加application.yml
配置,用于整合连接mybatis:
# 整合mybatis,分别为实体类位置和mapper实现层位置
mybatis:
type-aliases-package: com.kun.demodata.pojo
mapper-locations: classpath:mybatis/mapper/*.xml
最后新建UserController.java
文件,实现最终的控制服务调用,此处不再重复增删改等方法,只以查询为例,并且上一篇博客中未连接的数据库会由此类mybatis整合进去:
package com.kun.demodata.controller;
import com.kun.demodata.mapper.UserMapper;
import com.kun.demodata.pojo.User;
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;
@RestController
public class UserController {
@Autowired
private UserMapper userMapper;
@GetMapping("/queryUserList")
public List<User> queryUserList(){
List<User> userList = userMapper.queryUserList();
return userList;
}
}