SpringBoot_扩展数据库操作_DBUtils

你的名字 2021-10-06 03:28 568阅读 0赞

### SpringBoot\_扩展数据库操作\_DBUtils ###

*  依赖pom.xml
 *  配置类
 *  配置源码

# 依赖pom.xml #

<dependency>
    		<groupId>commons-dbutils</groupId>
    		<artifactId>commons-dbutils</artifactId>
    	</dependency>
    	<dependency>
    		<groupId>c3p0</groupId>
    		<artifactId>c3p0</artifactId>
    		<version>0.9.1.2</version>
    	</dependency>

# 配置类 #

//pingruan.base.c3p0.data.use-ext-data=true
    //pingruan.base.c3p0.data.driver-class=com.mysql.jdbc.Driver
    //pingruan.base.c3p0.data.jdbc-url=jdbc:mysql://192.168.164.100:3306/manage-a?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
    //pingruan.base.c3p0.data.user=root
    //pingruan.base.c3p0.data.password=qwe123

# 配置源码 #

@Component
    @ConfigurationProperties(prefix="pingruan.base.c3p0.data")
    @Data
    public class C3p0Properties {
    	private String useExtData = "false";
    	private String driverClass;
    	private String jdbcUrl;
    	private String user;
    	private String password;
    	private int initialPoolSize = 10;
    	private int minPoolSize = 5;
    	private int maxPoolSize = 20;
    }
    /**
     * 获取数据库连接
     *
     * @author vander
     * @date 2018年11月26日
     */
    public class Db {
    	
    	private static final QueryRunner runner = new QueryRunner();
    	private static ComboPooledDataSource ds = null;
    	private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
    	
    	Db(C3p0Properties c3p0){
    		try {
    			ds = new ComboPooledDataSource();
    			ds.setDriverClass(c3p0.getDriverClass());
    			ds.setJdbcUrl(c3p0.getJdbcUrl());
    			ds.setUser(c3p0.getUser());
    			ds.setPassword(c3p0.getPassword());
    			ds.setInitialPoolSize(c3p0.getInitialPoolSize());
    			ds.setMinPoolSize(c3p0.getMinPoolSize());
    			ds.setMaxPoolSize(c3p0.getMaxPoolSize());
    		} catch (Exception e) {
    			throw new ExceptionInInitializerError(e);
    		}
    	}
    	
    	/**
    	 * 获取连接
    	 * 
    	 * @return
    	 * @throws SQLException
    	 */
    	private Connection getConnection() throws SQLException {
    		Connection conn = threadLocal.get();
    		if (null == conn) {
    			conn = getDataSource().getConnection();
    			threadLocal.set(conn);
    		}
    		return conn;
    	}
    
    	/**
    	 * 开启事务
    	 */
    	private void startTransaction() {
    		try {
    			Connection conn = threadLocal.get();
    			if (null == conn) {
    				conn = getConnection();
    				threadLocal.set(conn);
    			}
    			conn.setAutoCommit(false);
    		} catch (SQLException e) {
    			throw new RuntimeException(e);
    		}
    	}
    
    	/**
    	 * 事务提交
    	 */
    	private void commit() {
    		try {
    			Connection conn = threadLocal.get();
    			if (conn != null) {
    				conn.commit();
    			}
    		} catch (SQLException e) {
    			throw new RuntimeException(e);
    		}
    	}
    
    	/**
    	 * 事务回滚
    	 */
    	private void rollback() {
    		try {
    			Connection conn = threadLocal.get();
    			if (conn != null) {
    				conn.rollback();
    			}
    		} catch (SQLException e) {
    			throw new RuntimeException(e);
    		}
    	}
    
    	/**
    	 * 释放连接
    	 */
    	private void close() {
    		try {
    			Connection conn = threadLocal.get();
    			if (conn != null) {
    				conn.close();
    				threadLocal.remove();
    			}
    		} catch (SQLException e) {
    			throw new RuntimeException(e);
    		}
    	}
    
    	/**
    	 * 获取数据源
    	 * 
    	 * @return
    	 */
    	private DataSource getDataSource() {
    		return ds;
    	}
    
    
    	/**
    	 * 查询（返回Array结果）
    	 * @param sql
    	 * @param params
    	 * @return
    	 */
    	public Object[] queryArray(String sql, Object... params) {
    		Object[] result = null;
    		try {
    			result = runner.query(getConnection(), sql, new ArrayHandler(), params);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally {
    			close();
    		}
    		return result;
    	}
    
    	/**
    	 * 查询（返回ArrayList结果）
    	 * @param sql
    	 * @param params
    	 * @return
    	 */
    	public List<Object[]> queryArrayList(String sql, Object... params) {
    		List<Object[]> result = null;
    		try {
    			result = runner.query(getConnection(), sql, new ArrayListHandler(), params);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally {
    			close();
    		}
    		return result;
    	}
    
    	/**
    	 * 查询（返回Map结果）
    	 * @param sql
    	 * @param params
    	 * @return
    	 */
    	public Map<String, Object> queryMap(String sql, Object... params) {
    		Map<String, Object> result = null;
    		try {
    			result = runner.query(getConnection(), sql, new MapHandler(), params);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally {
    			close();
    		}
    		return result;
    	}
    
    	/**
    	 * 查询（返回MapList结果）
    	 * @param sql
    	 * @param params
    	 * @return
    	 */
    	public List<Map<String, Object>> queryMapList(String sql, Object... params) {
    		List<Map<String, Object>> result = null;
    		try {
    			result = runner.query(getConnection(), sql, new MapListHandler(), params);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally {
    			close();
    		}
    		return result;
    	}
    
    	/* 查询（返回Bean结果） */
    	public <T> T queryBean(Class<T> cls, Map<String, String> map, String sql,
    			Object... params) {
    		T result = null;
    		try {
    			if (map != null) {
    				result = runner.query(getConnection(), sql, new BeanHandler<T>(cls, new BasicRowProcessor(new BeanProcessor(map))),
    						params);
    			} else {
    				result = runner.query(getConnection(), sql, new BeanHandler<T>(cls), params);
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally {
    			close();
    		}
    		return result;
    	}
    
    	/**
    	 * 查询（返回BeanList结果）
    	 * @param cls
    	 * @param map
    	 * @param sql
    	 * @param params
    	 * @return
    	 */
    	public <T> List<T> queryBeanList(Class<T> cls, Map<String, String> map, String sql,
    			Object... params) {
    		List<T> result = null;
    		try {
    			if (map != null) {
    				result = runner.query(getConnection(), sql,
    						new BeanListHandler<T>(cls, new BasicRowProcessor(new BeanProcessor(map))), params);
    			} else {
    				result = runner.query(getConnection(), sql, new BeanListHandler<T>(cls), params);
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally {
    			close();
    		}
    		return result;
    	}
    
    	/**
    	 * 查询指定列名的值（单条数据）
    	 * @param column
    	 * @param sql
    	 * @param params
    	 * @return
    	 */
    	public <T> T queryColumn(String column, String sql, Object... params) {
    		T result = null;
    		try {
    			result = runner.query(getConnection(), sql, new ScalarHandler<T>(column), params);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally {
    			close();
    		}
    		return result;
    	}
    
    	/**
    	 * 查询指定列名的值（多条数据）
    	 * @param column
    	 * @param sql
    	 * @param params
    	 * @return
    	 */
    	public <T> List<T> queryColumnList(String column, String sql, Object... params) {
    		List<T> result = null;
    		try {
    			result = runner.query(getConnection(), sql, new ColumnListHandler<T>(column), params);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally {
    			close();
    		}
    		return result;
    	}
    
    	/**
    	 * 查询指定列名对应的记录映射
    	 * @param column
    	 * @param sql
    	 * @param params
    	 * @return
    	 */
    	public <T> Map<T, Map<String, Object>> queryKeyMap(String column, String sql,
    			Object... params) {
    		Map<T, Map<String, Object>> result = null;
    		try {
    			result = runner.query(getConnection(), sql, new KeyedHandler<T>(column), params);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally {
    			close();
    		}
    		return result;
    	}
    
    	/**
    	 * 更新（包括UPDATE、INSERT、DELETE，返回受影响的行数）
    	 * @param sql
    	 * @param params
    	 * @return
    	 */
    	public int update( String sql, Object... params) {
    		int result = 0;
    		try {
    			startTransaction();
    			result = runner.update(getConnection(), sql, params);
    			commit();
    		} catch (SQLException e) {
    			rollback();
    			e.printStackTrace();
    		}finally {
    			close();
    		}
    		return result;
    	}
    }
    
    //@Autowired
    //Db db;
    /**
     * 扩展数据源
     * 
     *
     * @author vander
     * @date 2018年11月26日
     */
    @Configuration
    @ConditionalOnProperty(value="pingruan.base.c3p0.data.use-ext-data",havingValue="true")
    public class DbConfig {
    	
    	@Autowired
    	C3p0Properties c3p0Properties;
    	
    	@Bean
    	public Db dbs() {
    		return new Db(c3p0Properties);
    	}
    
    }