SpringBoot之数据库操作相关

川长思鸟来 2023-09-30 09:33 13阅读 0赞

### 数据库操作相关 ###

#### 整合JDBC使用 ####

> 默认数据源为：com.zaxxer.hikari.HikariDataSourc
> 
> HikariDataSource号称Java WEB当前速度最快的数据源，相比于传统的C3P0、DBCP、 Tomcat jdbc等连接池更加优秀;

1.  创建项目，选择依赖
    
    ![image-20220312105556365][]
2.  创建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
3.  进行测试查看是否连接成功
    
    > 测试类中注入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();
            }
        
        }
4.  使用配置好的模板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";
            }
        }
5.  测试是否成功

#### 整合使用自定义的Druid数据源 ####

1.  导入依赖
    
        
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>
2.  配置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
3.  测试是否切换成功
    
        @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();
            }
        
        }
4.  设置数据源连接初始化大小、最大连接数、等待时间、最小连接数 等设置项
    
        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
5.  导入log4j的依赖
    
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
6.  点击运行项目，查看是否运行成功
7.  编写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;
            }
        }
8.  运行项目进行测试
    
     *  访问http://localhost:8080/druid，输入在config中配置的账号密码后可进入后台，查看使用状态
        
        ![image-20220312144204323][]

#### 整合mybatis框架 ####

1.  新建项目，选择需要的模板
    
    ![image-20220312145024342][]
2.  导入依赖
    
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
3.  编写数据库配置和整合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
4.  编写实体类
    
        @Data
        @AllArgsConstructor
        @NoArgsConstructor
        @Component
        public class User {
              
            private int id;
            private String name;
            private String pwd;
        }
5.  编写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>
6.  编写Service层
7.  编写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;
        }
    }

1.  测试

[image-20220312105556365]: https://img-blog.csdnimg.cn/img_convert/63cb944f736b538b5fbb00b93b1563e6.png
[image-20220312144204323]: https://img-blog.csdnimg.cn/img_convert/c7f0627c6452c419c6c80b048b51c9fa.png
[image-20220312145024342]: https://img-blog.csdnimg.cn/img_convert/5e43e5e14c6025d613d6ec03a4e099bb.png