数据库操作相关
整合JDBC使用
默认数据源为：com.zaxxer.hikari.HikariDataSourc
HikariDataSource号称Java WEB当前速度最快的数据源，相比于传统的C3P0、DBCP、 Tomcat jdbc等连接池更加优秀;
-
创建项目，选择依赖
-
创建application.yml，将数据库信息写入
spring: datasource: username: root password: root url: jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai driver-class-name: com.mysql.cj.jdbc.Driver -
进行测试查看是否连接成功
测试类中注入DataSource
@SpringBootTest class Springboot04DataApplicationTests { @Autowired DataSource dataSource; @Test void contextLoads() throws SQLException { //查看一下默认的数据源:com.zaxxer.hikari.HikariDataSourc System.out.println(dataSource.getClass()); //获得数据库连接 Connection connection = dataSource.getConnection(); System.out.println(connection); //****Template:SpringBoot已经配置好模板bean，拿来即用 CRUD connection.close(); } } -
使用配置好的模板bean来进行crud，查询数据库信息
SpringBoot配置好的模板：JdbcTemplate，直接使用即可
@RestController public class JDBCController { @Autowired JdbcTemplate jdbcTemplate; //查询数据库的所有信息 @RequestMapping("/userList") public List<Map<String,Object>> userList(){ String sql="select * from user"; List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql); return maps; } @RequestMapping("/addUser") public String addUser(){ String sql="insert into mybatis.user(id,name,pwd) values (4,'小明','123456')"; jdbcTemplate.update(sql); return "update-ok"; } @RequestMapping("/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]="zzzzzzzz"; jdbcTemplate.update(sql,objects); return "updateUser-ok"; } @RequestMapping("/deleteUser/{id}") public String deleteUser(@PathVariable("id") int id){ String sql="delete from mybatis.user where id=?"; jdbcTemplate.update(sql,id); return "deleteUser-ok"; } } -
测试是否成功
整合使用自定义的Druid数据源
-
导入依赖
<!-- druid--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.8</version> </dependency> -
配置application.yml，修改默认数据源
spring: datasource: username: root password: root url: jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai driver-class-name: com.mysql.cj.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource -
测试是否切换成功
@SpringBootTest class Springboot04DataApplicationTests { @Autowired DataSource dataSource; @Test void contextLoads() throws SQLException { //查看一下默认的数据源:com.zaxxer.hikari.HikariDataSourc System.out.println(dataSource.getClass()); //获得数据库连接 Connection connection = dataSource.getConnection(); System.out.println(connection); //****Template:SpringBoot已经配置好模板bean，拿来即用 CRUD connection.close(); } } -
设置数据源连接初始化大小、最大连接数、等待时间、最小连接数 等设置项
spring: datasource: username: root password: root url: jdbc:mysql://localhost:3306/mybatis?useSSL=false&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=500 -
导入log4j的依赖
<dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> -
点击运行项目，查看是否运行成功
-
编写DruidConfig类
因为SpringBoot内置了servlet容器，所以没有web.xml，需要配置的东西需要在config类中进行配置
@Configuration public class DruidConfig { @ConfigurationProperties(prefix = "spring.datasource") @Bean public DataSource druidDataSource(){ return new DruidDataSource(); } //后台监控: web.xml ServletRegistrationBean //因为SpringBoot内置了servlet容器，所以没有web.xml,替代方法：ServletRegistrationBean @Bean public ServletRegistrationBean statViewServlet(){ ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*"); //后台需要有人登录，账号密码配置 HashMap<String, String> initParameters = new HashMap<>(); //增加配置 initParameters.put("loginUsername","admin");//key是固定的 initParameters.put("loginPassword","123456");//key是固定的 //允许谁可以访问 // value:localhost只有本机可以访问 //value为空则所有人都可以访问 initParameters.put("allow",""); //禁止谁能够访问 // initParameters.put("lzj","192.168.11.111"); bean.setInitParameters(initParameters);//设置初始化参数 return bean; } //filter 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; } } -
运行项目进行测试
-
访问http://localhost:8080/druid，输入在config中配置的账号密码后可进入后台，查看使用状态
-
整合mybatis框架
-
新建项目，选择需要的模板
-
导入依赖
<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.2.2</version> </dependency> -
编写数据库配置和整合mybatis配置
application.properties
spring.datasource.username=root spring.datasource.password=root spring.datasource.url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver # 整合mybatis mybatis.type-aliases-package=com.lzj.entity mybatis.mapper-locations=classpath:mybatis/mapper/*.xml -
编写实体类
@Data @AllArgsConstructor @NoArgsConstructor @Component public class User { private int id; private String name; private String pwd; } -
编写UserMapper和UserMapper.xml
UserMapper
//这个注解表示了这是员工mybatis的mapper类 @Mapper @Repository public interface UserMapper { List<User> queryUserList(); User queryUserById(int id); int addUser(User user); int updateUser(User user); int deleteUser(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.lzj.mapper.UserMapper"> <select id="queryUserList" resultType="User"> select * from user </select> <select id="selectUserById" 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"> delete from user where id=#{id} </delete> </mapper> -
编写Service层
-
编写controller层
@RestController public class UserController { @Autowired private UserService userService; @RequestMapping("/queryUserList") public List<User> queryUserList(){ List<User> userList = userService.queryUserList(); return userList; } }
编写controller层
@RestController
public class UserController {
@Autowired
private UserService userService;
@RequestMapping("/queryUserList")
public List<User> queryUserList(){
List<User> userList = userService.queryUserList();
return userList;
}
}
- 测试
245
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
