新建项目时,选择MysqL和jdbc Driver,导入依赖
在配置文件中配置用户名,密码,url,以及Driver
spring: datasource: username: root password: 123456 url: jdbc:MysqL://localhost:3306/mybatis?useUnicode=true&characterEnCoding=utf-8&serverTimezone=Asia/Shanghai driver-class-name: com.MysqL.cj.jdbc.Driver注意,由于SpringBoot中使用的MysqL为8.0+ 版本,要求url中必须带有时区信息
serverTimezone=Asia/Shanghai
2. 测试使用package com.wang;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;@SpringBoottestclass Springboot04DataApplicationTests { //只要把DataSource自动装配,我们就可以使用了 @autowired DataSource dataSource; @Test voID contextLoads() throws sqlException { //查看默认的数据源 : hikari.HikarIDataSource System.out.println(dataSource.getClass()); //获得数据库连接 Connection connection = dataSource.getConnection(); System.out.println(connection); //关闭数据库连接 connection.close(); }}注意
使用自动装配绑定DataSource利用getConnection方法调用jdbc默认的数据源为 hikari.HikarIDataSource3. CRUDpackage com.wang.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;@RestControllerpublic class jdbcController { //XXXX Template : SpringBoot已经配置好的模板bean,拿来即用,封装好了CRUD //jdbc //redis @autowired JdbcTemplate jdbcTemplate; //查询数据库的所有信息 //没有实体类,数据库中的东西怎样获取? ==> Map @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(5,'小明','123456')"; int update = jdbcTemplate.update(sql); if (update == 1) { return "addUser-OK"; } else { return "addUser-Fail"; } } //拼接sql,用RestFul风格传参,要用@PathVariable注解 @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] = "1233211234567"; //jdbcTemplate中的update重载了prepareStatement,直接传需要的对象即可 int update = jdbcTemplate.update(sql,objects); if (update == 1) { return "updateUser-OK"; } else { return "updateUser-Fail"; } } @GetMapPing("/deleteUser/{ID}") public String deleteUser(@PathVariable("ID") int ID) { String sql = "delete from mybatis.user where ID = ?"; //jdbcTemplate中的update重载了prepareStatement,直接传需要的对象即可 //int也是object,直接传入即可 int update = jdbcTemplate.update(sql,ID); if (update == 1) { return "deleteUser-OK"; } else { return "deleteUser-Fail"; } }}注意
XXXX Template : SpringBoot已经配置好的模板bean,封装好了CRUD除了查询,都用update语句jdbcTemplate中的update重载了prepareStatement,直接传需要的对象即可查询用query没有实体类,数据库中的东西怎样获取? ==> Map ==> List中存放Map,Map中以K-V形式储存数据库中的字段名和值,字段名为String类型,值为Object类型4. DruID1. 导入依赖<!-- https://mvnrepository.com/artifact/com.alibaba/druID --><dependency> <groupID>com.alibaba</groupID> <artifactID>druID</artifactID> <version>1.1.24</version></dependency>在配置文件中指定type并配置DruID
spring: datasource: username: root password: 123456 url: jdbc:MysqL://localhost:3306/mybatis?useUnicode=true&characterEnCoding=utf-8&serverTimezone=Asia/Shanghai driver-class-name: com.MysqL.cj.jdbc.Driver type: com.alibaba.druID.pool.DruIDDataSource #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=500package com.wang.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;@Configurationpublic class DruIDConfig { //将自定义的Bean与spring.datasource绑定 @ConfigurationPropertIEs(prefix = "spring.datasource") //将DruIDDataSource注册到Bean @Bean public DataSource druIDDataSource() { return new DruIDDataSource(); }}注意
@ConfigurationPropertIEs 绑定配置,后面可以指定前缀4. 启用后台监控package com.wang.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;@Configurationpublic class DruIDConfig { //将自定义的Bean与spring.datasource绑定 @ConfigurationPropertIEs(prefix = "spring.datasource") //将DruIDDataSource注册到Bean @Bean public DataSource druIDDataSource() { return new DruIDDataSource(); } //后台监控,访问 /druID 即可,固定写法的代码 //因为SpringBoot 内置了servlet容器,所以没有web.xml,替代方法: ServletRegistrationBean @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"); //允许谁可以访问,值为空则所有人都可以访问 initParameters.put("allow",""); //IP白名单 //initParameters.put("allow","192.168.1.12,127.0.0.1"); //IP黑名单 //initParameters.put("deny","192.168.4.23"); //是否能够重置数据 initParameters.put("resetEnable","false"); bean.setinitParameters(initParameters); //设置初始化参数 return bean; }}key如下
public static final String SESSION_USER_KEY = "druID-user";public static final String ParaM_name_USERname = "loginUsername";public static final String ParaM_name_PASSWORD = "loginPassword";public static final String ParaM_name_ALLOW = "allow";public static final String ParaM_name_DENY = "deny";public static final String ParaM_REMOTE_ADDR = "remoteAddress";使用log4j,要在resources下配置log4j.propertIEs
#将等级为DEBUG的日志信息输出到console和file这两个目的地，console和file的定义在下面的代码log4j.rootLogger=DEBUG,console,file#控制台输出的相关设置log4j.appender.console = org.apache.log4j.ConsoleAppenderlog4j.appender.console.Target = System.outlog4j.appender.console.Threshold=DEBUGlog4j.appender.console.layout = org.apache.log4j.PatternLayoutlog4j.appender.console.layout.ConversionPattern=[%c]-%m%n#文件输出的相关设置log4j.appender.file = org.apache.log4j.RollingfileAppenderlog4j.appender.file.file=./log/wang.loglog4j.appender.file.MaxfileSize=10mblog4j.appender.file.Threshold=DEBUGlog4j.appender.file.layout=org.apache.log4j.PatternLayoutlog4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n#日志输出级别log4j.logger.org.mybatis=DEBUGlog4j.logger.java.sql=DEBUGlog4j.logger.java.sql.Statement=DEBUGlog4j.logger.java.sql.ResultSet=DEBUGlog4j.logger.java.sql.PreparedStatement=DEBUG注意
JavaConfig中使用方法进行配置后,一定要注册Bean通过 ServletRegistrationBean//filter//我们要注册filter,同样的,使用 FilterRegistrationBean 注册@Beanpublic FilterRegistrationBean webStatFilter() { FilterRegistrationBean bean = new FilterRegistrationBean(); bean.setFilter(new WebStatFilter()); //可以过滤哪些请求 HashMap<String,String> initParameters = new HashMap<>(); //这些东西不进行统计 initParameters.put("exclusions","*.Js,*.CSS,/druID/*"); bean.setinitParameters(initParameters); return bean;}key如下
public final static String ParaM_name_PROfile_ENABLE = "profileEnable";public final static String ParaM_name_SESSION_STAT_ENABLE = "sessionStatEnable";public final static String ParaM_name_SESSION_STAT_MAX_COUNT = "sessionStatMaxCount";public static final String ParaM_name_EXCLUSIONS = "exclusions";public static final String ParaM_name_PRINCIPAL_SESSION_name = "principalSessionname";public static final String ParaM_name_PRINCIPAL_@R_419_5556@_name = "principal@R_419_5556@name";public static final String ParaM_name_REAL_IP_header = "realipheader";注意
与stat类似,我们需要注册filter,使用FilterRegistrationBean不要忘了注册Bean5. MyBatis1. 导入依赖mybatis-spring-boot-starter
<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter --><dependency> <groupID>org.mybatis.spring.boot</groupID> <artifactID>mybatis-spring-boot-starter</artifactID> <version>2.1.3</version></dependency>package com.wang.pojo;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;@Data@AllArgsConstructor@NoArgsConstructorpublic class User { private int ID; private String name; private String pwd;}package com.wang.mapper;import com.wang.pojo.User;import org.apache.ibatis.annotations.Mapper;import org.springframework.stereotype.Repository;import java.util.List;//这个注解表示了这是一个MyBatis的Mapper类//也可以在启动类是加@MapperScan("")扫描包@Mapper//这个注解表示了它是Dao层@Repositorypublic interface UserMapper { List<User> queryUserList(); User queryUserByID(int ID); int addUser(User user); int updateUser(User user); int deleteUser(int ID);}注意
@Mapper这个注解表示了这是一个MyBatis的Mapper类也可以在启动类是加@MapperScan("")扫描包@Repository这个注解表示了它是Dao层以上两个注解缺一不可4. 整合Mybatis在配置文件中添加
# 整合Mybatismybatis: type-aliases-package: com.wang.pojo mapper-locations: classpath:MyBatis/mapper/*.xml设置别名以及制定XXXMapper.xml的路径
注意
路径设置中,classpath: 后没有 /5. 编写XXXMapper.xml在配置文件对应的路径下(resources路径下)新建文件夹
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.wang.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,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="_int"> delete from user where ID = #{ID}; </delete></mapper>package com.wang.controller;import com.wang.mapper.UserMapper;import com.wang.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;@RestControllerpublic class UserController { @autowired private UserMapper userMapper; @GetMapPing("/userList") public List<User> queryUserList() { List<User> userList = userMapper.queryUserList(); return userList; } @GetMapPing("/addUser") public String addUser() { int i = userMapper.addUser(new User(6,"小明","123456")); return i == 1 ? "ok" : "fail"; } @GetMapPing("/updateUser") public String updateUser() { int i = userMapper.updateUser(new User(6,"小明2","654321")); return i == 1 ? "ok" : "fail"; } @GetMapPing("/deleteUser") public String deleteUser() { int i = userMapper.deleteUser(6); return i == 1 ? "ok" : "fail"; }}注意
在SpringBoot中,所有的引入要使用自动装配@autoWired,这里引入Dao的接口,实际项目中应该引入Service层的类(此处偷懒没写) 总结以上是内存溢出为你收集整理的SpringBoot-整合数据库全部内容，希望文章能够帮你解决SpringBoot-整合数据库所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）