1、springboot整合jdbc
(1)在springboot中导入jdbc的依赖,当然要带上mysql驱动
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.3</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.francis</groupId>
<artifactId>springboot-04-data</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot-04-data</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!--JDBC依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--Web-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--Mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--测试类-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
(2)编写配置文件,指定数据源的相关属性,application.yaml
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
username: root
password: root
url: jdbc:mysql://ip:3306/mybatis?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8
(3)在测试类中测试数据源是否配置成功
package com.francis.springboot04data;
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;
@SpringBootTest
class Springboot04DataApplicationTests {
@Autowired
DataSource dataSource;
@Test
void contextLoads() throws SQLException {
//查看默认数据源
System.out.println(dataSource.getClass());
//获取连接
Connection connection = dataSource.getConnection();
System.out.println(connection);
//关闭连接
connection.close();
}
}
可以成功拿到数据源和连接。
在springboot中,会存在各种***Template的类,这是springboot配置好的模板bean,拿来即用,比如JDBCTemplate.
(4)在没有实体类的情况下,查询数据库的数据
package com.francis.springboot04data.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.RestController;
import java.util.List;
import java.util.Map;
/**
* @author
* @create 2021-04-17-15:57
*/
@RestController
public class JDBCController {
@Autowired
JdbcTemplate jdbcTemplate;
//在没有实体类时,数据库中的数据怎么获取
@GetMapping("/userList")
public List<Map<String,Object>> getUsers(){
String sql="select *from user";
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
return maps;
}
}
浏览器访问,成功获取数据
2、整合druid
(1)导入druid依赖
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.21</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
由于druid的监控使用log4j,就顺便导入,不然启动时会报错
(2)application.yaml里配置数据源
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
username: root
password: root
url: jdbc:mysql://106.14.32.142:3306/mybatis?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8
# druid数据源配置
type: com.alibaba.druid.pool.DruidDataSource
druid:
initial-size: 5
min-idle: 5
max-active: 20
max-wait: 60000
min-evictable-idle-time-millis: 300000
validation-query: SELECT ONE FROM DUAL
test-while-idle: true
test-on-borrow: false
test-on-return: false
pool-prepared-statements: true
#配置监控统计拦截的filters,stat:监控统计;log4j:日志记录;wall:防sql注入
#如果允许报错,java.lang.ClassNoFoundException:org.apache.log4j.Property
#则导入log4j依赖即可
filters: stat,wall,log4j
max-pool-prepared-statement-per-connection-size: 20
use-global-data-source-stat: true
connection-properties: druid.stat.mergSql=true;druid.stat.slowSqlMillis=500
启动项目后访问接口,同样成功
(3)绑定配置文件,使之生效,并配置druid的监控功能
配置类的代码如下:
package com.francis.springboot04data.common.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import org.springframework.boot.context.properties.ConfigurationProperties;
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;
/**
* @author
* @create 2021-04-17-21:12
*/
@Configuration
public class DruidConfig {
//绑定配置文件,使之生效
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druidDataSource(){
return new DruidDataSource();
}
//后台监控,因为springboot内置了servlet容器,没有web.xml,就采用注册bean的方式。
@Bean
public ServletRegistrationBean registrationBean(){
ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
//后台登录,配置管理员账号密码
HashMap<String, String> parameterMap = new HashMap<>();
//键不能修改
parameterMap.put("loginUsername", "admin");
parameterMap.put("loginPassword", "root");
//允许访问,""代表允许所有人访问
parameterMap.put("allow", "");
//禁止访问,可以是用户,也可以是主机
parameterMap.put("francis", "192.168.61.152");
bean.setInitParameters(parameterMap);
return bean;
}
//filter
@Bean
public FilterRegistrationBean webStatFilter() {
FilterRegistrationBean bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());
//配置过滤规则
Map<String, String> parametersMap = new HashMap<>();
parametersMap.put("exclusions", "*.js,*.css,/druid/*");
bean.setInitParameters(parametersMap);
return bean;
}
}
然后访问监控页面,用配置的账号和密码登录,成功,如图
不过不知道为啥,我的监控页面出来了,但是sql监控居然没生效,~~~~我吐了
(4)整合mybatis
新建项目并导入mybatis依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.5</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.francis</groupId>
<artifactId>springboot_05_mybatis</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot_05_mybatis</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
配置application.properties文件
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://ip:3306/mybatis?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=root
接下来编写实体类User,当然,同时得导入lombok包
package com.francis.springboot_05_mybatis.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @author
* @create 2021-04-18-0:05
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private int id;
private String phone;
private String name;
private String password;
private String permission;
}
然后是UserMapper
package com.francis.springboot_05_mybatis.mapper;
import com.francis.springboot_05_mybatis.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.stereotype.Repository;
import java.util.List;
/**
* @author
* @create 2021-04-18-0:17
*/
//该注解表示这是一个mybatis的mapper接口,为了方便,
// 也可以在启动类上加注解@MapperScan("com.francis.springboot_05_mybatis.mapper")
@Mapper
@Repository
public interface UserMapper {
List<User> getUserList();
User getUserById(int id);
int addUser(User user);
int updateUser(User user);
int deleteUserById(int id);
}
userMapper.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.francis.springboot_05_mybatis.mapper.UserMapper">
<select id="getUserList" resultType="User">
select * from user
</select>
<select id="getUserById" resultType="User">
select * from user where id=#{id}
</select>
<insert id="addUser" parameterType="User">
insert into user (id,phone,name,password,permission) values (#{id},#{phone,jdbcType=VARCHAR
},#{name,jdbcType=VARCHAR},#{password,jdbcType=VARCHAR},#{permission,jdbcType=VARCHAR})
</insert>
<update id="updateUser" parameterType="User">
update user set name=#{name},phone=#{phone},name=#{name},password=#{password},permission=#{permission} where id=#{id}
</update>
<delete id="deleteUserById" parameterType="int">
delete from user where id=#{id}
</delete>
</mapper>
值得注意的时,要让xml文件能够识别到实体类,必须得在application.properties文件中添加配置,添加后的配置文件如下:
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://ip:3306/mybatis?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=root
#整合mybatis
#别名
mybatis.type-aliases-package=com.francis.springboot_05_mybatis.pojo
#xml文件的位置
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
测试
整个项目结构如下