1.Jdbc
创建springboot时勾选jdbc和sql驱动支持,这里有个坑,如果使用.yml配置时,一定要检查properties中的配置,它会自动帮你生成配置信息。
application.yml
spring:
datasource:
username: root
password: "123456"
url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
driver-class-name: com.mysql.cj.jdbc.Driver
使用template模板实现crud,编写controller
@RestController
public class JDBCController {
@Autowired
JdbcTemplate jdbcTemplate;
//查询所有信息
@GetMapping("/userList")
public List<Map<String,Object>> userList(){
String sql="select * from user";
List<Map<String, Object>> list_maps = jdbcTemplate.queryForList(sql);
return list_maps;
}
@GetMapping("/addUser")
public String addUser(){
String sql="insert into mybatis.user(id,name,pwd) values (4,'小红','asfsa')";
jdbcTemplate.update(sql);
return "addUser-ok";
}
@GetMapping("/updateUser/{id}")
public String updateUser(@PathVariable("id") int id){
String sql="update mybatis.user set name =?,pwd=? where id= "+id;
//封装
Object[] objects=new Object[2];
objects[0]="小明2";
objects[1]="zzzzzzz";
jdbcTemplate.update(sql,objects);
return "updateUser-ok";
}
@GetMapping("/deletUser/{id}")
public String deletUser(@PathVariable("id")int id){
String sql="delete from mybatis.user where id=?";
jdbcTemplate.update(sql,id);
return "deletUser-ok";
}
}
2.Druid
导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.12</version>
</dependency>
添加druib的支持
spring:
datasource:
username: root
password: "123456"
url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
添加Druid自己的配置
#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
使用了log4j,导入依赖
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
使用durid的监控功能,创建config文件夹,使用@Configuration开启扩展,使用@ConfigurationProperties(prefix = “spring.datasource”)与yam中的配置绑定,再装入bean中
@Configuration
public class DuridConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource duridDataSource(){
return new DruidDataSource();
}
}
配置durid功能
@Bean
//后台监控功能
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
//后台配置密码
HashMap<String,String> initParameters = new HashMap<>();
//增加配置
initParameters.put("loginUsername","admin");//登录的key是固定的loginUsername,loginPassword
initParameters.put("loginPassword","123456");
//允许谁可以访问
initParameters.put("allow","");
//禁止谁访问
//initParameters.put("ws","192.198.11.01");
bean.setInitParameters(initParameters);
return bean;
}
}
效果:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MFZBwxZB-1651033131644)(C:\Users\TR\AppData\Roaming\Typora\typora-user-images\image-20220426154933941.png)]
因为springboot内置了servlet容器,没有web.xml,替代方法就是用***RegistrationBean注册功能给servlet。
扩展:过滤器
//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;
}
3.mybatis
整合包用mybatis-spring-boot-starter
<!-- 引入 myBatis,这是 MyBatis官方提供的适配 Spring Boot 的,而不是Spring Boot自己的-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.0</version>
</dependency>
创建实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private int id;
private String name;
private String pwd;
}
添加lombok依赖
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
创建对应mapper
//这个注解表示这是mybaits中mapper类
@Mapper
@Repository
public interface UserMapper {
List<User> queryUserList();
User queryUserById(@Param("id") int id);
int addUser(User user);
int updateUser(User user);
int deletUser(@Param("id") int id);
}
添加properties配置
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# 数据源名称
spring.datasource.name=defaultDataSource
# 数据库连接地址
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
# 数据库用户名&密码:
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#整合mybatis
mybatis.type-aliases-package=com.liu.pojo
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
创建resources/mybatis/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.liu.mapper.UserMapper">
<select id="queryUserList" resultType="User">
select * from mybatis.user
</select>
<select id="queryUserById" parameterType="int" resultType="User">
select * from mybatis.user where id=#{id}
</select>
<insert id="addUser" parameterType="User">
insert into mybatis.user (id, name, pwd) VALUE (#{id},#{name},#{pwd})
</insert>
<update id="updateUser" parameterType="User">
update mybatis.user set name = #{name},pwd=#{pwd} where id=#{id};
</update>
<delete id="deletUser" parameterType="int">
delete from mybatis.user where id=#{id}
</delete>
</mapper>
创建controller
@RestController
public class UserController {
@Autowired
private UserMapper userMapper;
@GetMapping("/queryUserList")
public List<User> queryUserList(){
List<User> users = userMapper.queryUserList();
return users;
}
//根据id选择用户
@GetMapping("/selectUserById")
public User selectUserById(){
User user = userMapper.queryUserById(1);
System.out.println(user);
return user;
}
//添加一个用户
@GetMapping("/addUser")
public String addUser(){
userMapper.addUser(new User(5,"阿毛","456789"));
return "ok";
}
//修改一个用户
@GetMapping("/updateUser")
public String updateUser(){
userMapper.updateUser(new User(5,"阿毛","421319"));
return "ok";
}
//根据id删除用户
@GetMapping("/deleteUser")
public String deleteUser(){
userMapper.deletUser(5);
return "ok";
}
}