SpringBoot整合JDBC使用JdbcTemplate

怼烎@ 2021-09-21 18:50 408阅读 0赞

### 目录 ###

*  导入依赖
 *  配置文件
 *  使用 JDBCTemplate

# 导入依赖 #

JDBC 和 MySQL 驱动, web依赖

<dependency>
    	<groupId>org.springframework.boot</groupId>
    	<artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    
    
    <dependency>
    	<groupId>mysql</groupId>
    	<artifactId>mysql-connector-java</artifactId>
    	<scope>runtime</scope>
    </dependency>
    
    
    <dependency>
    	<groupId>org.springframework.boot</groupId>
    	<artifactId>spring-boot-starter-web</artifactId>
    </dependency>

# 配置文件 #

这是在 application.yml 文件中配置的, 也可以在 application.properties 中配置都差不多

spring:
      datasource:
        username: root
        password: 123456
        # serverTimezone=UTC 解决时区的报错问题
        url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
        # mysql5 的驱动是 com.mysql.jdbc.Driver, mysql8的驱动是 com.mysql.cj.jdbc.Driver
        driver-class-name: com.mysql.cj.jdbc.Driver

测试查看一下数据源和连接有没有问题

@SpringBootTest
    class Springboot02DataApplicationTests { 
    	@Autowired
    	DataSource dataSource;
    
    	@Test
    	void contextLoads() throws SQLException { 
    		// 看一下默认数据源
    		System.out.println("getClass ---> " + dataSource.getClass());
    		Connection connection = dataSource.getConnection();
    		// 查看连接
    		System.out.println("connection ---> " + connection);
    		// 关闭连接
    		connection.close();
    	}
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk1MzIyNw_size_16_color_FFFFFF_t_70]

# 使用 JDBCTemplate #

JdbcTemplate 是Spring对原生JDBC做的一个轻量级分装的类

在 Controller 中测试使用一下

package com.pro.controller;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.web.bind.annotation.PathVariable;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    @RestController
    public class JDBCController { 
    
        @Autowired
        JdbcTemplate jdbcTemplate;
    
        @RequestMapping("/user/list")
        public List userList() { 
            String sql = "select * from users";
    
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
    
            return maps;
        }
    
        @RequestMapping("/user/add")
        public Object userAdd() { 
            Map<String, Object> hashMap = new HashMap<>();
    
            Object[] objects = new Object[]{ "ControllerAddUser", "123controller"};
    
            String sql = "insert into users(name,pwd) values(?,?)";
    
            int i = jdbcTemplate.update(sql, objects);
            if (i > 0) { 
                hashMap.put("code", 0);
                hashMap.put("msg", "新增成功");
                return hashMap;
            }
    
            hashMap.put("code", 1);
            hashMap.put("msg", "新增失败");
    
            return hashMap;
        }
    
        @RequestMapping("/user/{id}")
        public Map userDelete(@PathVariable("id") int id) { 
            Map<String, Object> hashMap = new HashMap<>();
    
            String sql = "delete from users where id = ?";
    
            int i = jdbcTemplate.update(sql, id);
            if (i > 0) { 
                hashMap.put("code", 0);
                hashMap.put("msg", "删除成功");
                return hashMap;
            }
    
            hashMap.put("code", 1);
            hashMap.put("msg", "删除失败");
    
            return hashMap;
        }
    
    }

访问成功ok  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk1MzIyNw_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk1MzIyNw_size_16_color_FFFFFF_t_70]: /images/20210920/28316ca11e1a45bfafab82a829faf373.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk1MzIyNw_size_16_color_FFFFFF_t_70 1]: /images/20210920/d77fe5920eec472d92a235e5717d4a78.png