动态数据库增删改查增强版

最新推荐文章于 2022-07-20 11:49:22 发布

原创最新推荐文章于 2022-07-20 11:49:22 发布 · 176 阅读

0 ·

CC 4.0 BY-SA版权

技术研讨专栏收录该内容

46 篇文章

订阅专栏

本文介绍了一个基于Spring框架的动态数据源配置方法，并实现了一种缓存机制来提高数据库连接效率。通过AOP切面编程实现了对数据库连接池的缓存管理，避免了每次查询都创建新的数据库连接。

实体类

package com.phone.entity;

import lombok.Data;
import lombok.ToString;

@Data
public class MyDatasource {

	private String driverClassName;
	
	private String db;
	
	private String username;
	
	private String password;
	
	private String ip;
	
	private String port;
	
	private String sql;
	
	private String dbType;
	
	private String url;
	
	private org.springframework.jdbc.datasource.DriverManagerDataSource driverManagerDataSource;

	@Override
	public String toString() {
		return "MyDatasource [driverClassName=" + driverClassName + ", db=" + db + ", username=" + username
				+ ", password=" + password + ", ip=" + ip + ", port=" + port + ", sql=" + sql + ", dbType=" + dbType
				+ ", url=" + url + ", driverManagerDataSource=" + driverManagerDataSource + "]";
	}
}

缓存机制

package com.phone.jdbc;

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

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Component;

import com.phone.entity.MyDatasource;

@Aspect
@Component
public class AdviceTest {
	private static Map<String,DriverManagerDataSource> map=new HashMap<>();
	
	// 表达式需要根据实际情况修改
	//@Around("execution(* com.phone.jdbc.*.testConnection*(..))")
	@Around("execution(* com.phone.jdbc.*.*(..))")
    public Object process(ProceedingJoinPoint point) throws Throwable {
		System.out.println(map);
        System.out.println("@Around：执行目标方法之前...");
        //访问目标方法的参数：
        Object[] args = point.getArgs();
        if (args != null && args.length > 0 && args[0].getClass() == MyDatasource.class) {
        	MyDatasource myDatasource= (MyDatasource) args[0];
        	String url = "jdbc:DBTYPE://IP:PORT/DB?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT";
    		url=url.replace("DBTYPE", myDatasource.getDbType()).replace("IP", myDatasource.getIp()).replace("PORT", myDatasource.getPort()).replace("DB", myDatasource.getDb());
    		myDatasource.setUrl(url);
    		DriverManagerDataSource myDatasource2 = map.get(url);
        	if(myDatasource2==null) {
        		System.out.println("没用缓存");
        			DriverManagerDataSource dataSource=new DriverManagerDataSource();        		
        		dataSource.setUsername(myDatasource.getUsername());
        		dataSource.setPassword(myDatasource.getPassword());
        		dataSource.setDriverClassName(myDatasource.getDriverClassName());
        		dataSource.setUrl(url);
        		myDatasource.setDriverManagerDataSource(dataSource);
        		args[0]=myDatasource;
               	Object proceed = point.proceed(args);
               	if(proceed !=null) {
               		map.put(url, dataSource);
               	}
               	return (List<Map<String,Object>>)proceed;
                //return Collections.EMPTY_LIST; 
        	}else {
        		System.out.println("使用缓存");
        		myDatasource.setDriverManagerDataSource(myDatasource2);
        		Object proceed = point.proceed(args);
        		return (List<Map<String,Object>>)proceed;
                //return Collections.EMPTY_LIST; 
        	}
        }
        //用改变后的参数执行目标方法
        System.out.println("@Around：执行目标方法之后...");
        System.out.println("@Around：被织入的目标对象为：" + point.getTarget());
		return args;
       
    }
}

查询的一个demo

package com.phone.jdbc;

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

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import com.phone.entity.MyDatasource;

@RestController
public class QueryDa {

	/**
	 * 动态使用任意数据库查询数据
	 * @param myDatasource
	 */
	@PostMapping("testConnection")
	public List<Map<String,Object>> testConnection(@RequestBody MyDatasource myDatasource) {
		JdbcTemplate jdbcTemplate=new JdbcTemplate();
		jdbcTemplate.setDataSource(myDatasource.getDriverManagerDataSource());
		String sql=myDatasource.getSql();
		List<Map<String,Object>> queryForList = jdbcTemplate.queryForList(sql);
		if(queryForList==null) {
			return Collections.EMPTY_LIST;
		}
		for (Map<String, Object> map : queryForList) {
			System.out.println("-------------------");
			for (String key : map.keySet()) {
				Object object = map.get(key);
				System.out.println("key:"+key+"|value:"+object);
			}
		}
		return queryForList;
	}
	
	
	
}