使用jdbc连接池同步数据

配置文件

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/数据库名?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC
username=root
password=root

工具类 

import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;


public class DataSourceConfig {

    private static BasicDataSource dataSource;
    private static ThreadLocal<Connection> tl;

    static {
        try {
            tl = new ThreadLocal<>();
            Properties prop = new Properties();
			//读取配置文件
            Resource resource= new ClassPathResource("db.properties");
            prop.load(resource.getInputStream());
            //初始化数据库连接池
            dataSource = new BasicDataSource();
            dataSource.setDriverClassName(prop.getProperty("driver"));
            dataSource.setUrl(prop.getProperty("url"));
            dataSource.setUsername(prop.getProperty("username"));
            dataSource.setPassword(prop.getProperty("password"));
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 获取一个数据库连接
     *
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        if(tl.get() == null){
            Connection conn = dataSource.getConnection();
            tl.set(conn);
            return conn;
        }else {
            return tl.get();
        }
    }

    /**
     * 关闭数据库连接
     *
     * @param conn
     */
    public static void closeConnection() {
        try {
            Connection conn = tl.get();
            if (conn != null) {
                conn.close();
            }
            tl.remove();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 开启事务
     *
     * @throws SQLException
     */
    public static void TransBegin() throws SQLException {
        Connection conn = tl.get();
        conn.setAutoCommit(false);
    }

    /**
     * 关闭事务
     *
     * @throws SQLException
     */
    public static void TransEnd() throws SQLException {
        Connection conn = tl.get();
        conn.setAutoCommit(true);
    }

    /**
     * 提交事务
     *
     * @throws SQLException
     */
    public static void commit() throws SQLException {
        Connection conn = tl.get();
        conn.commit();
    }

    /**
     * 回滚事务
     *
     * @throws SQLException
     */
    public static void rollback() throws SQLException {
        Connection conn = tl.get();
        conn.rollback();
    }
}

使用示例 

public interface DataSync {
	List<Map<String, Object>> getResulst();
}
 
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//yml配置文件配置一个值，控制在存在环境属性且具有特定值的情况下才启用Bean注册
@ConditionalOnProperty(name="datasync.value", havingValue="dev")
@Component
public class DataSyncImpl implements DataSync{
 
 
 public List<Map<String, Object>> getResulst() {
				
        List<Map<String, Object>> mapList = new ArrayList<>();
        try{
            //获得连接
            Connection conn = DataSourceConfig.getConnection();
            StringBuffer sb = new StringBuffer();
            String sql = "select code,name from 表名 ";
            sb.append(sql);
            sb.append(" GROUP BY code");
            //执行sql
            PreparedStatement ps = conn.prepareStatement(sb.toString());
			//获得结果
            ResultSet rs = ps.executeQuery();
			//遍历
            while (rs.next()){
                Map<String, Object> map = new HashMap<>();
				//取出查询的列值
                map.put("code",rs.getString(1));
                map.put("value",rs.getString(2));
                mapList.add(map);
            }
            ps.close();
            rs.close();
          
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            DataSourceConfig.closeConnection();
        }
        return mapList;
    }
	
	}