Spring Boot JDBCTemplate

工程地址：https://github.com/huiyiwu/spring-boot-simple/spring-boot-jdbctemplate

文章目录

• 1.动态连接数据库
• • 1.1 添加依赖项：
  • 1.2. 新建UserController.java
  • 1.3. 新建UserService.java
  • 1.4. 测试
• 2. 配置文件配置DataSource
• • 2.1. 添加依赖 参考1.1.
  • 2.2. 添加配置文件
  • 2.3. 修改文件
  • 2.4. 测试参考1.4.
• 3. 注意事项

1.动态连接数据库

1.1 添加依赖项：

<!-- 引入jdbc支持 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!-- 连接MySQL数据库 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
           

1.2. 新建UserController.java

package com.huchx.jdbctemplate.controller;

import com.huchx.jdbctemplate.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.HashMap;
import java.util.Map;

@RestController
@RequestMapping("user")
public class UserController {
    @Autowired
    UserService userService;

    @RequestMapping("select")
    public Map<String,Object> getUsers(){
        Map<String,Object> result =new HashMap<>();
        result.put("code","200");
        result.put("msg","success");
        result.put("result", userService.select());
        return result;
    }
    @RequestMapping("insert")
    public Map<String,Object> addUser(String name){
        Map<String,Object> result =new HashMap<>();
        result.put("code","200");
        result.put("msg","success");
        userService.insert(name);
        return result;
    }
    @RequestMapping("update")
    public Map<String,Object> updateUser(String id,String name){
        Map<String,Object> result =new HashMap<>();
        result.put("code","200");
        result.put("msg","success");
        userService.update(Integer.parseInt(id),name);
        return result;
    }
    @RequestMapping("delete")
    public Map<String,Object> delUser(String id){
        Map<String,Object> result =new HashMap<>();
        result.put("code","200");
        result.put("msg","success");
        userService.delete(Integer.parseInt(id));
        return result;
    }
}

           

1.3. 新建UserService.java

package com.huchx.jdbctemplate.service;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Service
public class UserService {
   private JdbcTemplate initJdbcTemplate(){
       DriverManagerDataSource dataSource = new DriverManagerDataSource();
       dataSource.setDriverClassName("com.mysql.jdbc.Driver");
       dataSource.setUrl("jdbc:mysql://localhost:3306/spring_boot?serverTimezone=UTC");
       dataSource.setUsername("root");
       dataSource.setPassword("123456");
       JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
       return jdbcTemplate;
   }
    /**
     * 查询
     * @return
     */
    public List<Map<String,Object>> select() {
        JdbcTemplate jdbcTemplate = initJdbcTemplate();
        String sql = "Select * from m_user";
        List<Map<String,Object>> users = jdbcTemplate.queryForList(sql);
        return users;
    }
    /**
     * 插入
     */
    public void insert(String name) {
        JdbcTemplate jdbcTemplate = initJdbcTemplate();
        String sql = "insert into m_user (id,name) values (null,?)";
        jdbcTemplate.update(sql,name);
    }
    /**
     * 更新
     */
    public void update(int id,String name) {
        JdbcTemplate jdbcTemplate = initJdbcTemplate();
        String sql = "update m_user set name = ? where id = ?";
        jdbcTemplate.update(sql,name,id);
    }
    /**
     * 删除
     */
    public void delete(int id) {
        JdbcTemplate jdbcTemplate = initJdbcTemplate();
        String sql = "delete from m_user where id = ?";
        jdbcTemplate.update(sql,id);
    }
}
           

1.4. 测试

在浏览器中输入查看结果：

• http://localhost:8080/user/insert?name=huchx
• http://localhost:8080/user/update?name=aa&id=3
• http://localhost:8080/user/select
• http://localhost:8080/user/delete?id=3

2. 配置文件配置DataSource

在1. 动态连接数据库基础上修改

2.1. 添加依赖 参考1.1.

2.2. 添加配置文件

application.yaml

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/spring_boot?serverTimezone=UTC
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
           

2.3. 修改文件

• JdbcTemplateApp.java

//@SpringBootApplication(exclude= {DataSourceAutoConfiguration.class})//动态连接数据库时使用
@SpringBootApplication
public class JdbcTemplateApp
{
    public static void main( String[] args )
    {
        SpringApplication.run(JdbcTemplateApp.class,args);
    }
}

           

*

UserService.java

package com.huchx.jdbctemplate.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Service
public class UserService {
	@Autowired
   JdbcTemplate jdbcTemplate;
        /**
     * 查询
     * @return
     */
    public List<Map<String,Object>> select() {

        String sql = "Select * from m_user";
        List<Map<String,Object>> users = jdbcTemplate.queryForList(sql);
        return users;
    }

    /**
     * 插入
     */
    public void insert(String name) {
        String sql = "insert into m_user (id,name) values (null,?)";
        jdbcTemplate.update(sql,name);
    }

    /**
     * 更新
     */
    public void update(int id,String name) {
        String sql = "update m_user set name = ? where id = ?";
        jdbcTemplate.update(sql,name,id);
    }

    /**
     * 删除
     */
    public void delete(int id) {
        String sql = "delete from m_user where id = ?";
        jdbcTemplate.update(sql,id);
    }
}

           

2.4. 测试参考1.4.

3. 注意事项

1. 动态连接数据库时，需要在主类上添加

   @SpringBootApplication(exclude= {DataSourceAutoConfiguration.class})

   这样将禁止必须在配置文件中配置Datasource。
2. 数据库连接驱动，

   mysql-connector-java 5

   使用

   com.mysql.jdbc.Driver

   ，

   mysql-connector-java 6

   使用

   com.mysql.cj.jdbc.Driver