JDBC 管理器

JDBC管理器通过JDBC API 将数据库的字段、字段类型、长度缓存到Ehcache中，最终拼接解析形成一条预处理sql。预处理参数封装到IData对象中（Idata继承Map对象,新增处理JDBC 方法）,IDataset为查询返回结果（继承List对象，新增JDBC 方法） JDBC管理器使用

JDBC管理器主要特点：

• 通过spring 注入sql 语句

• 支持JDBC详细日志打印

• 支持分页组件

• 支持JDBC 开发模式、生产模式。开发模式实时更新xml中的sql，生产模式只加载一次SQL。

• 无需编写pojo类

• 无需配置jdbc返回值类型及参数。

•  支持sql查询为空自动去掉条件

•  支持AOP管理事务，一个Service方法处理一条事务，防止忘记关闭连接导致连接等待。

• 支持动态获取前台参数，传递JDBC接口（通过request获取前台参数封装到IData中，IData继承Map 新增获取数据的getBlobString、getString、getInt、getLong等方法)

• 支持查看返回的结果集、字段类型、字段、长度

• 快捷方便，开发人员只需要关注业务，编写相应的SQL

• 支持db2、mysql、oracle、sqlServer、Sybase 接口

架构图如下：

序列图如下：

JDBC管理器日志打印如下：

2015-09-23 23:58:24,669 [main] DEBUG com.venustech.sql.jdbc.JdbcMySql - open db Active :1-->Idle:4-->Wait:0-->Size: 5 connection !
2015-09-23 23:58:29,387 [main] DEBUG com.venustech.dao.jdbc.StdParameterStatement - SQL 
			SELECT * FROM user WHERE username=? AND password=?
			
2015-09-23 23:58:29,387 [main] DEBUG com.venustech.dao.jdbc.StdParameterStatement - PARAM {username=111, password=111}
2015-09-23 23:58:29,389 [main] DEBUG com.venustech.dao.jdbc.StdParameterStatement - [1]BINDING[username] [111]
2015-09-23 23:58:29,390 [main] DEBUG com.venustech.dao.jdbc.StdParameterStatement - [2]BINDING[password] [111]
2015-09-23 23:58:29,397 [main] DEBUG com.venustech.dao.jdbc.StdParameterStatement - SQL execute time: 0.006s
2015-09-23 23:58:29,431 [main] DEBUG com.venustech.sql.jdbc.JdbcMySql - close db Active :0-->Idle:5-->Wait:0-->Size: 5 connection !

AOP 事物实现代码如下：

<!-- 切入点 -->
	<bean id="jdbcTransaction"
		class="com.venustech.sql.jdbc.JdbcTransaction" />
		
	<!-- AOP  -->
	<aop:config>
		<!-- 监控相应的服务类 -->
		<aop:pointcut
			expression="execution(* com.venustech.sql.service..*.*(..,com.venustech.sql.SqlSession*,..))"
			id="jdbcPoint" />
		<!-- 切面： 将哪个对象中的哪个方法，织入到哪个切入点 -->
		
		<aop:aspect id="transactionAspect"
			ref="jdbcTransaction">
			
			<aop:after-returning method="closeDB"
				returning="returnObj" pointcut-ref="jdbcPoint" />
				
			<aop:after-throwing pointcut-ref="jdbcPoint"
				method="rollbackDB" throwing="ex" />
				
		</aop:aspect>
	</aop:config>

package com.venustech.sql.jdbc;

import org.aspectj.lang.JoinPoint;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.venustech.sql.SqlSession;
import com.venustech.sql.SqlTransaction;

public class JdbcTransaction implements SqlTransaction {
	private final static Logger logger = LoggerFactory
			.getLogger(JdbcTransaction.class);

	/**
	 * AOP 遇到错误自动回滚
	 */
	@Override
	public void rollbackDB(JoinPoint jp, Throwable ex) {
		Object[] args = jp.getArgs();
		if (args != null) {
			for (Object obj : args) {
				if (obj instanceof SqlSession) {
					if (obj != null) {
						try {
							SqlSession sqlSession = (SqlSession) obj;
							sqlSession.rollback();
							sqlSession.close();
						} catch (Exception e) {
							logger.error("ERROR JdbcTransaction exception:" + e);

						}
					}
				} else if (obj instanceof SqlSession[]) {
					SqlSession[] sqlSessions = (SqlSession[]) obj;
					for (SqlSession sqlSession : sqlSessions) {
						if (sqlSession != null) {
							try {
								sqlSession.rollback();
								sqlSession.close();
							} catch (Exception e) {
								logger.error("ERROR JdbcTransaction exception:"
										+ e);
							}
						}
					}
				}
			}
		}
	}

	/**
	 * 提交事物
	 */
	@Override
	public void closeDB(JoinPoint jp, Object returnObj) {
		Object[] args = jp.getArgs();
		if (args != null) {
			for (Object obj : args) {
				if (obj instanceof SqlSession) {
					if (obj != null) {
						try {
							SqlSession sqlSession = (SqlSession) obj;
							sqlSession.close();
						} catch (Exception e) {
							logger.error("ERROR JdbcTransaction exception:" + e);
						}
					}

				} else if (obj instanceof SqlSession[]) {
					SqlSession[] sqlSessions = (SqlSession[]) obj;
					for (SqlSession sqlSession : sqlSessions) {
						if (sqlSession != null) {
							try {
								sqlSession.close();
							} catch (Exception e) {
								logger.error("ERROR JdbcTransaction exception:"
										+ e);
							}
						}
					}
				}
			}
		}
	}

}

获取web页面中的参数封装到IData代码如下:

*/
	public static IData<String, Object> getDate(HttpServletRequest request)
			throws UnsupportedEncodingException {
		IData<String, Object> data = new DataMap<String, Object>();
		Enumeration<?> enums = request.getParameterNames();
		while (enums.hasMoreElements()) {
			String paramName = (String) enums.nextElement();
			String[] values = request.getParameterValues(paramName);
			for (int i = 0; i < values.length; i++) {
				data.put(paramName, URLDecoder.decode(values[i], "utf-8"));
			}
		}
		logger.info(data.toString());
		return data;
	}

JDBC配置连接池开启事物代码如下:

<!-- 加载属性文件 -->
	<bean id="configBean"
		class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="locations">
			<list>
				<value>
					file:${JDBC_CONFIG}/jdbc/jdbc.properties
				</value>
			</list>
		</property>
	</bean>


	<!--配置数据源 -->
	<bean id="saveOrace" class="com.venustech.dao.PooledDataSource">
		<!-- 指定连接数据库的URL -->
		<property name="url" value="${zjaudit.url}" />
		<!-- 指定连接数据库的驱动 -->
		<property name="driverClassName" value="${zjaudit.driverClassName}" />
		<!-- 指定连接数据库的用户名 -->
		<property name="username" value="${zjaudit.username}" />
		<!-- 指定连接数据库的密码 -->
		<property name="password" value="${zjaudit.password}" />
		<!-- 指定连接数据库的最大线程 -->
		<property name="maxActive" value="${zjaudit.maxActive}" />
		<!-- 指定连接数据库的初始化值 -->
		<property name="initialSize" value="${zjaudit.initialSize}" />
		<!-- 最大空闲数 -->
		<property name="maxIdle" value="${zjaudit.maxIdle}" />
		<!-- 指定连接数据库的最大等待时间 -->
		<property name="maxWait" value="${zjaudit.maxWait}" />
		<!-- 指定连接数据库的回收超时连接的时长 -->
		<property name="removeAbandonedTimeout" value="${zjaudit.removeAbandonedTimeout}" />
		<!--检查空闲连接的时间间隔 -->
		<property name="timeBetweenEvictionRunsMillis" value="${zjaudit.timeBetweenEvictionRunsMillis}" />
		<!--逐出空闲时间 毫秒 -->
		<property name="minEvictableIdleTimeMillis" value="${zjaudit.minEvictableIdleTimeMillis}" />
	</bean>

	<!--分页组件 -->
	<bean id="pageModel" class="com.venustech.data.PageModel">
		<!-- 默认分页大小20 -->
		<property name="pageSize" value="20" />
		<!-- 默认跳转页码 -->
		<property name="pageIndex" value="1" />
		<!--页面大小参数key -->
		<property name="pageSizeName" value="pageSize" />
		<!--跳转页面页码参数key-->
		<property name="pageIndexName" value="currentPage" />
		<!--总记录参数key -->
		<property name="pageCountName" value="totalRecords" />
		<!-- 参数总页码参数key -->
		<property name="pageTotalPage" value="totalPage" />
	</bean>
	<!-- jdbc Session 工厂  -->
	<bean id="sqlSessionFactory" class="com.venustech.sql.SqlSessionFactory">
	     <!-- 连接池 -->
		<property name="dataSource" ref="saveOrace" />
		<!-- 5 是 mysql 数据库操作 -->
		<property name="type" value="5" />
		<!-- 分页组件 -->
		<property name="pageModel" ref="pageModel" />
	</bean>

package com.venustech.sql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import com.venustech.data.PageModel;
import com.venustech.sql.jdbc.JdbcMySql;
import com.venustech.sql.jdbc.JdbcSybase;
import com.venustech.sql.jdbc.jdbcDb2;
import com.venustech.sql.jdbc.jdbcOracle;

public class SqlSessionFactory {
	// oracle 数据库
	public static final int DATABASE_TYPE_ORACLE = 1;
	// db2 数据库
	public static final int DATABASE_TYPE_DB2 = 2;
	// sybase 数据库
	public static final int DATABASE_TYPE_SYBASE = 3;
	// sql sqlserver数据库
	public static final int DATABASE_TYPE_SQLSERVER = 4;
	// mysql 数据库
	public static final int DATABASE_TYPE_MYSQL = 5;
	// 自动提交
	private boolean autoCommit;
	// 分页组件配置
	private PageModel pageModel;
	// 数据库类型
	private int type;
	// 连接池
	private DataSource dataSource;

	public SqlSession openSession() throws SQLException {
		SqlSession session;
		switch (type) {
		case DATABASE_TYPE_ORACLE:
			session = new jdbcOracle(dataSource, autoCommit, pageModel);
			break;
		case DATABASE_TYPE_DB2:
			session = new jdbcDb2(dataSource, autoCommit, pageModel);
			break;
		case DATABASE_TYPE_SYBASE:
			session = new JdbcSybase(dataSource, autoCommit, pageModel);
			break;
		case DATABASE_TYPE_SQLSERVER:
			session = new JdbcSybase(dataSource, autoCommit, pageModel);
			break;
		case DATABASE_TYPE_MYSQL:
			session = new JdbcMySql(dataSource, autoCommit, pageModel);
			break;
		default:
			session = new JdbcMySql(dataSource, autoCommit, pageModel);
			break;
		}
		return session;
	}

	
	public boolean isAutoCommit() {
		return autoCommit;
	}

	public void setAutoCommit(boolean autoCommit) {
		this.autoCommit = autoCommit;
	}

	public int getType() {
		return type;
	}

	public void setType(int type) {
		this.type = type;
	}

	public PageModel getPageModel() {
		return pageModel;
	}

	public void setPageModel(PageModel pageModel) {
		this.pageModel = pageModel;
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	/**
	 * 关闭流
	 * 
	 * @param result
	 * @throws Exception
	 */
	public static void closeStatement(ResultSet result) throws Exception {
		Statement statement = result.getStatement();
		if (result != null && !result.isClosed()) {
			result.close();
		}
		if (statement != null && !statement.isClosed()) {
			statement.close();
		}
	}

}

获取表结构类：

public static IData<String, IColumn> getColumnsByResult(Connection conn,
			String table_name) throws Exception {
		IData<String, IColumn> columns = new DataMap<String, IColumn>();
		PreparedStatement statement = conn.prepareStatement("select * from "
				+ table_name + " where 1 = 0");
		ResultSetMetaData metaData = statement.executeQuery().getMetaData();
		for (int i = 1; i <= metaData.getColumnCount(); i++) {
			IColumn column = new ColumnInfo();
			column.setColumnName(metaData.getColumnName(i));
			column.setColumnType(metaData.getColumnType(i));
			column.setColumnDesc(metaData.getColumnLabel(i));
			column.setColumnSize(metaData.getColumnDisplaySize(i));
			column.setDecimalDigits(metaData.getScale(i));
			column.setNullable(metaData.isNullable(i) == ResultSetMetaData.columnNoNulls ? false
					: true);

			columns.put(column.getColumnName(), column);
		}
		DaoHelper.closeStatement(statement);
		return columns;
	}

拼接insert方法：

public static Object[] getObjectsByInsert(Connection conn,
			String table_name, IData<String, Object> data) throws Exception {
		StringBuffer namestr = new StringBuffer();
		StringBuffer valuestr = new StringBuffer();
		Parameter param = new Parameter();

		IColumn[] columns = getColumns(conn, table_name);
		for (int i = 0; i < columns.length; i++) {
			Object[] colobjs = getObjectsByColumn(columns[i], data);
			namestr.append(colobjs[0] + ",");
			valuestr.append("?" + ",");
			param.add(colobjs[1]);
		}

		return new Object[] {
				getInsertSql(table_name,
						common.trimSuffix(namestr.toString(), ","),
						common.trimSuffix(valuestr.toString(), ",")), param };
	}

insert方法实现代码如下：

verride
	public boolean insert(String table_name, IData<String, Object> data)
			throws Exception {
		Object[] insobjs = DaoHelper.getObjectsByInsert(connection, table_name,
				data);
		int result = executeUpdate((String) insobjs[0], (Parameter) insobjs[1]);
		return result == 0 ? false : true;
	}

 

转载于:https://my.oschina.net/u/2470985/blog/510401