Spring Boot JdbcTemplate MySQL 多数据源

一、单数据源

1、配置pom.xml，增加jdbc、HikariCP连接池、MySQL驱动。

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
  <groupId>com.zaxxer</groupId>
  <artifactId>HikariCP</artifactId>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
</dependency>

2、DataSourceConfig.java

package com.idodo.demo.conf;

import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;

import com.zaxxer.hikari.HikariDataSource;

@Configuration
public class DataSourceConfig {
  
  @Bean(name = "dataSource")
  public DataSource datasource(Environment env) {
    HikariDataSource ds = new HikariDataSource();
    ds.setJdbcUrl(env.getProperty("spring.datasource.url"));
    ds.setUsername(env.getProperty("spring.datasource.username"));
    ds.setPassword(env.getProperty("spring.datasource.password"));
    ds.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
    return ds;
  }

}

3、application.properties

# 端口号
server.port=8080

# 数据库url
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
# 数据库用户名
spring.datasource.username=root
# 数据库密码
spring.datasource.password=root
# 数据库驱动
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

 

4、User.java

package com.idodo.demo.entity;

import java.io.Serializable;

public class User implements Serializable{

  private static final long serialVersionUID = 7914100053493869907L;
  
  private Integer user_id ;
  private String user_code ;
  private String user_name ;
  
  public User() {
  }

  public Integer getUser_id() {
    return user_id;
  }

  public void setUser_id(Integer user_id) {
    this.user_id = user_id;
  }

  public String getUser_code() {
    return user_code;
  }

  public void setUser_code(String user_code) {
    this.user_code = user_code;
  }

  public String getUser_name() {
    return user_name;
  }

  public void setUser_name(String user_name) {
    this.user_name = user_name;
  }
  
}

5、UserService.java

package com.idodo.demo.service;

import com.idodo.demo.entity.User;

public interface UserService {
  public User geUserById(Integer user_id);
  
  public void test(Integer user_id);
  
}

6、UserDao.java

package com.idodo.demo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.idodo.demo.entity.User;

@Repository
public class UserDao {
    @Autowired
    JdbcTemplate jdbcTempalte;

    public Integer getUserTotal(Integer user_id) {
        String sql = "select count(1) from app_users where user_id=?";
        Integer count = jdbcTempalte.queryForObject(sql, Integer.class, user_id);
        return count;
    }

    public User findUserById(Integer user_id) {
        String sql = "select * from app_users where user_id=?";
        User user = jdbcTempalte.queryForObject(sql, new UserRowMapper(), user_id);
        return user;
    }

    public List<User> getUserList(String user_code) {
        String sql = "select * from app_users where user_code=?";
        List<User> list = jdbcTempalte.query(sql, new UserRowMapper(), user_code);
        return list;
    }

    public void update(User user) {
        String sql = "update app_users set user_code=?, user_name=? where user_id = ?";
        jdbcTempalte.update(sql, user.getUser_code(), user.getUser_name(), user.getUser_id());
    }

    public Integer insertUser(final User user) {
        final String sql = "insert into app_users (user_code, user_name) values (?, ?)";
        // Spring利用GeneratedKeyHolder，提供了一个可以返回新增记录对应主键值的方法。
        // user_id 为 自增主键
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTempalte.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, new String[] { "user_id" });
                ps.setString(1, user.getUser_code());
                ps.setString(2, user.getUser_name());
                return ps;
            }
        }, keyHolder);
        
        // 返回新增记录对应的主键值
        return keyHolder.getKey().intValue();
    }
    
    // 实现RowMapper接口，返回User对象
    static class UserRowMapper implements RowMapper<User> {
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user = new User();
            user.setUser_id(rs.getInt("user_id"));
            user.setUser_code(rs.getString("user_code"));
            user.setUser_name(rs.getString("user_name"));
            return user;
        }

    }

}

 

7、UserServiceImpl.java

package com.idodo.demo.service.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.idodo.demo.dao.UserDao;
import com.idodo.demo.entity.User;
import com.idodo.demo.service.UserService;

@Service
public class UserServiceImpl implements UserService {
  
  @Autowired UserDao userDao;
  
  public User geUserById(Integer user_id) {
    User user = userDao.findUserById(user_id);
    return user;
  }
  
  public void test(Integer user_id) {
    System.out.println(userDao.getUserTotal(user_id));
    
    List<User> list = userDao.getUserList("9002");
    for (User u : list) {
      System.out.println(u.getUser_name());
    }
    
    User user = new User();
    user.setUser_id(1);
    user.setUser_code("90001");
    user.setUser_name("jzhjzh");
    userDao.update(user);
    
    User vo = new User();
    //vo.setUser_id(1);
    vo.setUser_code("9009");
    vo.setUser_name("jiangzhihao2018");
    userDao.insertUser(vo);
    
  }
}

8、UserController.java

package com.idodo.demo.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.idodo.demo.entity.User;
import com.idodo.demo.service.UserService;

@Controller
public class UserController {
  @Autowired
  UserService userService;
  
    // http://localhost:8080/user/1
  // {"user_id":1,"user_code":"9001","user_name":"jzh"}
  @RequestMapping("/user/{user_id}")
  public @ResponseBody User getUser(@PathVariable Integer user_id) {
    User user = userService.geUserById(user_id);
    
    // 测试
    userService.test(user_id);
    
    return user;
  }
}

二、多数据源【2023-03-16 更新】

1、application.properties。需要注意的是url要改成jdbc-url。

# 端口号
server.port=8080

# 数据库url
spring.datasource.jdbc-url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
# 数据库用户名
spring.datasource.username=root
# 数据库密码
spring.datasource.password=root
# 数据库驱动
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# 数据库url
spring.datasource.iot.jdbc-url=jdbc:mysql://127.0.0.1:3306/iot?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
# 数据库用户名
spring.datasource.iot.username=root
# 数据库密码
spring.datasource.iot.password=root
# 数据库驱动
spring.datasource.iot.driver-class-name=com.mysql.cj.jdbc.Driver

2、DataSourceConfig.java。需要注意的是要增加主数据源描述@Primary。第二个数据源、自定义JdbcTemplate。

package com.aotuo.iot.conf;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {
    // 主数据源
    @Bean(name = "dataSource")
    @Primary
    public DataSource datasource(Environment env) {
        HikariDataSource ds = new HikariDataSource();
        ds.setJdbcUrl(env.getProperty("spring.datasource.jdbc-url"));
        ds.setUsername(env.getProperty("spring.datasource.username"));
        ds.setPassword(env.getProperty("spring.datasource.password"));
        ds.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
        return ds;
    }

    // 从数据源
    @Bean(name= "iotDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.iot")
    public DataSource iotDataSource() {
        DataSource ds = DataSourceBuilder.create().build();
        return ds;
    }

    @Bean(name="jdbcTempalteIot")
    public JdbcTemplate jdbcTempalteIot (@Qualifier("iotDataSource") DataSource dataSource ) {
        return new JdbcTemplate(dataSource);
    }
}

3、UserDao.java。增加JdbcTemplate jdbcTempalteIot 调用。

package com.aotuo.iot.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.aotuo.iot.entity.User;

@Repository
public class UserDao {
    @Autowired
    JdbcTemplate jdbcTempalte;

    @Autowired
    JdbcTemplate jdbcTempalteIot;

    public Integer getUserTotal(Integer user_id) {
        String sql = "select count(1) from app_users where user_id=?";
        Integer count = jdbcTempalte.queryForObject(sql, Integer.class, user_id);
        return count;
    }

    public User findUserById(Integer user_id) {
        String sql = "select * from app_users where user_id=?";
        User user = jdbcTempalte.queryForObject(sql, new UserRowMapper(), user_id);
        return user;
    }

    public List<User> getUserList(String user_code) {
        String sql = "select * from app_users where user_code=?";
        List<User> list = jdbcTempalte.query(sql, new UserRowMapper(), user_code);
        return list;
    }

    public void update(User user) {
        String sql = "update app_users set user_code=?, user_name=? where user_id = ?";
        jdbcTempalte.update(sql, user.getUser_code(), user.getUser_name(), user.getUser_id());
    }

    public void update_iot(User user) {
        String sql = "update app_users set user_code=?, user_name=? where user_id = ?";
        jdbcTempalteIot.update(sql, user.getUser_code(), user.getUser_name(), user.getUser_id());
    }

    public Integer insertUser(final User user) {
        final String sql = "insert into app_users (user_code, user_name) values (?, ?)";
        // Spring利用GeneratedKeyHolder，提供了一个可以返回新增记录对应主键值的方法。
        // user_id 为 自增主键
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTempalte.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, new String[] { "user_id" });
                ps.setString(1, user.getUser_code());
                ps.setString(2, user.getUser_name());
                return ps;
            }
        }, keyHolder);
        
        // 返回新增记录对应的主键值
        return keyHolder.getKey().intValue();
    }
    
    // 实现RowMapper接口，返回User对象
    static class UserRowMapper implements RowMapper<User> {
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user = new User();
            user.setUser_id(rs.getInt("user_id"));
            user.setUser_code(rs.getString("user_code"));
            user.setUser_name(rs.getString("user_name"));
            return user;
        }

    }

}