程序员最近都爱上了这个网站  程序员们快来瞅瞅吧！  it98k网:it98k.com

一、目标

       1:实现一个数据持久层框架，既能像JPA那样不用写sql直接调用框架自带方法操作数据库，又能像mybatis那样执行自定义sql语句。

       2:能够在spring中使用，支持spring事务管理。

       3:能够拦截sql，并在自定义拦截器判断是否执行sql。

       4:插入数据后能选择性将数据库自增列的值写回插入的数据对象。

二、实现思路

       1:新增接口SqlSession，提供数据库操作方法供用户操作。

       2:新增接口SqlGenerator，生成sql语句，供SqlSession实现类调用。

       3:新增接口Executor，执行Sql语句，供SqlSession实现类调用。

       4:新增接口ResultSetHandler，将结果集封装成返回对象，供Executor实现类调用。

       5:新增接口Transaction，内部包含数据库连接(Connection)成员对象，供Executor实现类调用。

       6:核心接口图:

三、核心代码

1:Sql拦截器拦截SqlSession

基于java动态代理实现，Executor拦截与此类似。

@Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        //执行拦截器
        Configuration configuration = this.sqlSession.getConfiguration();
        List<Interceptor> interceptorList = configuration.getInterceptorList();
        for (Interceptor interceptor : interceptorList) {
            List<InterceptorScopeEnum> scopeEnums = interceptor.getInterceptorScopes();
            if (scopeEnums == null || scopeEnums.isEmpty() || !scopeEnums.contains( InterceptorScopeEnum.SQL_SESSION )) continue;
 
            if ( !interceptor.process( new Invocation(method, args) ) ) return null;
        }
        //保存方法返回类型等信息，供处理结果集使用
        String methodName = method.getName();
        if ( sqlSessionMethodNames.contains(methodName) ){
            if ( configuration.getMethodSignature(methodName) == null){
                configuration.addMethodSignature(methodName, new MethodSignature(method));
            }
        }
        return method.invoke(this.sqlSession, args);
    }

2：封装结果集成返回结果

@Override
    public <E> E handleResultSetsForSingle(ResultSet resultSet, Class<E> clazz) throws SQLException {
        if ( !resultSet.next() ) return null;
 
        //属性与对应的setter方法,去除sql忽略的属性
        Map<String, Method> methodMap = this.getFieldSetterMethodMap(clazz);
        //数据库字段名与对象字段名映射关系
        Map<String, String> fieldNameMap = EdrHelper.getFieldDbRelateEntity(clazz);
        try {
            return this.generateRsObj(clazz, resultSet, methodMap, fieldNameMap);
        } catch (Exception ex) {
            LOG.error("Building result from database Error", ex);
            throw new BuildResultException("Building result from database Error", ex);
        }
    }
 
    private <E> E generateRsObj(Class<E> clazz, ResultSet resultSet, Map<String, Method> methodMap, Map<String, String> fieldNameMap) throws Exception{
        ResultSetMetaData metaData = resultSet.getMetaData();
        final int columnCount = metaData.getColumnCount();
        if (columnCount == 1){
            try {
                Constructor<E> constructor = clazz.getDeclaredConstructor(clazz);
                Object javaObj = configuration.getTypeHandler().jdbcToJavaBeanType(resultSet, 1, clazz);
                E instance = constructor.newInstance(javaObj);
                //再走一遍javaBean逻辑，尽最大努力避免出错
                return this.generateRsByJavaBean(instance, resultSet, methodMap, fieldNameMap);
            } catch (Exception ex){}
        }
        E instance = clazz.getDeclaredConstructor().newInstance();
        return this.generateRsByJavaBean(instance, resultSet, methodMap, fieldNameMap);
    }
 
private <E> E generateRsByJavaBean(E instance, ResultSet resultSet, Map<String, Method> methodMap, Map<String, String> fieldNameMap) throws Exception {
        if (methodMap.isEmpty() || fieldNameMap.isEmpty()) return instance;
 
        //走javaBean逻辑
        ResultSetMetaData metaData = resultSet.getMetaData();
        final int columnCount = metaData.getColumnCount();
        int i = 0;
        TypeHandler typeHandler = configuration.getTypeHandler();
        while (i++ < columnCount){
            String columnName = metaData.getColumnName(i);
            Method method = methodMap.get( fieldNameMap.get(columnName) );
            if (method == null){
                continue;
            }
            Object javaObj = typeHandler.jdbcToJavaBeanType(resultSet, i, method.getParameterTypes()[0]);
            method.invoke(instance, javaObj);
        }
        return instance;
    }

3:接入Spring事务管理

1:  新增SpringSqlSessionUtil类用于spring环境下SqlSession的获取关闭等操作，spring事务管理原理可参考之前的文章:https://blog.csdn.net/qq_41633199/article/details/115832730

SpringSqlSessionUtil类核心代码。

//sql session是否处于Transactional作用范围
    public static boolean isSqlSessionTransactional(SqlSession session, SqlSessionFactory sessionFactory) {
        Assert.notNull(session, "No SqlSession specified");
        Assert.notNull(sessionFactory, "No SessionFactory specified");
//通过SqlSessionFactory获取以便兼容多数据源情况下含有多个连接池的情况。
        SqlSessionHolder holder = (SqlSessionHolder) TransactionSynchronizationManager.getResource(sessionFactory);
 
        return holder != null && holder.getSqlSession() == session;
    }
 
    public static SqlSession getSqlSession(SqlSessionFactory sessionFactory) {
        Assert.notNull(sessionFactory, "No SqlSession specified");
 
        SqlSessionHolder holder = (SqlSessionHolder) TransactionSynchronizationManager.getResource(sessionFactory);
 
        SqlSession session = sessionTransactionHolder(holder);
        if (session != null) {
            if ( session.isClosed() ){
                LOG.warn("SqlSession has been closed!");
            } else {
                return session;
            }
        }
 
        session = sessionFactory.openSession();
        //注册到spring事务管理中去
        registerSessionHolder(sessionFactory, session);
        return session;
    }
 
    public static void closeSqlSession(SqlSession session, SqlSessionFactory sessionFactory) {
        Assert.notNull(session, "No SqlSession specified");
        Assert.notNull(sessionFactory, "No SessionFactory specified");
 
        SqlSessionHolder holder = (SqlSessionHolder) TransactionSynchronizationManager.getResource(sessionFactory);
        if (holder != null && holder.getSqlSession() == session) {
            holder.released();
        } else {
            session.close();
        }
    }
 
    //获取Transaction范围内的sqlSession
    private static SqlSession sessionTransactionHolder(SqlSessionHolder holder) {
        SqlSession session = null;
        if (holder != null && holder.isSynchronizedWithTransaction()) {
            holder.requested();
 
            session = holder.getSqlSession();
        }
        return session;
    }
 
    private static void registerSessionHolder(SqlSessionFactory sessionFactory, SqlSession session) {
        SqlSessionHolder holder;
        if ( TransactionSynchronizationManager.isSynchronizationActive() ) {//当前线程是否存在活跃的事务同步器
            TransactionFactory transactionFactory = session.getConfiguration().getTransactionFactory();
 
            if (transactionFactory instanceof SpringManagedTransactionFactory) {
 
                holder = new SqlSessionHolder(session);
                //sqlSession写入threadLocal
                TransactionSynchronizationManager.bindResource(sessionFactory, holder);
                //注册事务回调
                TransactionSynchronizationManager.registerSynchronization(new SqlSessionTransactionSyncAdapter(sessionFactory, holder));
                holder.setSynchronizedWithTransaction(true);
                holder.requested();
            } else {
                //检查是否设置了数据连接池到spring事务环境
                if (TransactionSynchronizationManager.getResource( session.getConfiguration().getDataSource() ) == null) {
                    if ( LOG.isWarnEnabled() ) {
                        LOG.warn("SqlSession [{}] was not registered for synchronization because DataSource is not transactional", session);
                    }
                } else {
                    throw new TransientDataAccessResourceException(
                            "SqlSessionFactory must be using a SpringManagedTransactionFactory in order to use Spring transaction synchronization");
                }
            }
        } else {
            if ( LOG.isWarnEnabled() ) {
                LOG.warn("SqlSession [{}] was not registered for synchronization because synchronization is not active", session);
            }
        }
    }

2:  SpringSqlSessionUtil类用到的事务回调SqlSessionTransactionSyncAdapter继承了spring事务的TransactionSynchronizationAdapter类，其关键方法如下:

@Override//事务终止回调
    public void suspend() {
        if (this.sqlSessionHolderActive){
            TransactionSynchronizationManager.unbindResource(this.sqlSessionFactory);
        }
    }
 
    @Override
    public void resume() {//重试
        if (this.sqlSessionHolderActive){
            TransactionSynchronizationManager.bindResource(this.sqlSessionFactory, this.sqlSessionHolder);
        }
    }
 
    @Override
    public void beforeCommit(boolean readOnly) {
        if ( TransactionSynchronizationManager.isActualTransactionActive() ){
            this.sqlSessionHolder.getSqlSession().commit();
        }
    }
 
    @Override
    public void beforeCompletion() {
        if ( !this.sqlSessionHolder.isOpen() ) return;
        //从threadLocal移除
        TransactionSynchronizationManager.unbindResource(this.sqlSessionFactory);
        //释放数据库连接
        this.sqlSessionHolder.getSqlSession().close();
    }
 
    @Override
    public void afterCompletion(int status) {
        if (this.sqlSessionHolderActive) {
            TransactionSynchronizationManager.unbindResourceIfPossible(this.sqlSessionFactory);
            this.sqlSessionHolderActive = false;
            this.sqlSessionHolder.getSqlSession().close();
        }
        this.sqlSessionHolder.reset();
    }

3：新增数据库连接容器类SpringManagedTransaction，实现Transaction接口，内部包含数据库连接对象Connection，这里需要在获取数据库连接和关闭数据库连接方法中接入到spring:

public Connection getConnection() throws SQLException {
        if (this.connection != null) return this.connection;
 
        this.openConnection();
        return this.connection;
    }
 
    protected void openConnection() throws SQLException {
        if (this.dataSource == null) return;
 
        this.connection = DataSourceUtils.getConnection(this.dataSource);
        if (this.connection == null) return;
        this.autoCommit = this.connection.getAutoCommit();
        this.closed = false;
 
        this.isConnectionTransactional = DataSourceUtils.isConnectionTransactional(this.connection, this.dataSource);
    }
 
public void close() {
        DataSourceUtils.releaseConnection(this.connection, this.dataSource);
        this.closed = true;
    }

4:  新增类SpringManagedSqlSessionProxy，基于java动态代理，用来将SqlSession接入spring管理中，并支持事务回滚等，核心代码如下:

@Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        SqlSession sqlSession = SpringSqlSessionUtil.getSqlSession(this.sqlSessionFactory);
 
        try {
            Object rs = method.invoke(sqlSession, args);
            //没有纳入spring事务中，直接提交
            if ( !SpringSqlSessionUtil.isSqlSessionTransactional(sqlSession, this.sqlSessionFactory) ){
                sqlSession.commit();
            }
            return rs;
        } catch (Throwable throwable){
            throw throwable;
        } finally {
            if (sqlSession != null){
                SpringSqlSessionUtil.closeSqlSession(sqlSession, this.sqlSessionFactory);
            }
        }
    }

5:新增类SpringManagedSqlSessionFactory用于生成SpringManagedSqlSessionProxy代理对象，核心代码:

@Override
    public SqlSession openSession() {
        SqlSessionFactory defaultSqlSessionFactory = new DefaultSqlSessionFactory(this.configuration, this.sqlGenerator, this.resultSetHandler);
        //构建代理对象spring manage
        return (SqlSession) Proxy.newProxyInstance(SqlSession.class.getClassLoader(), new Class[]{SqlSession.class}, new SpringManagedSqlSessionProxy(defaultSqlSessionFactory));
    }

4:构建spring-boot-starter

        将SqlSessionFactory对象存入spring bean容器，以便业务代码中能自动注入该对象再获取SqlSession。核心代码如下:

/**
 *持久化框架 edr自动配置
 * @author tfq qq:1394965066
 */
@Configuration
@ConditionalOnMissingBean(SqlSessionFactory.class)
@ConditionalOnClass(DefaultSqlSessionFactory.class)
@ConditionalOnBean({DataSource.class, Environment.class})
@AutoConfigureAfter(DataSourceAutoConfiguration.class)//指定顺序
public class SmpEdrAutoConfiguration {
 
    @Bean
    public SqlSessionFactory buildDefaultSessionFactory(DataSource dataSource, Environment environment){
        LoggerFactory.getLogger(SmpEdrAutoConfiguration.class).debug("Init DefaultSqlSessionFactory of SmpEdr");
//拦截器
        String interceptors = environment.getProperty("com.lauor.smpedr.interceptors");
 
        com.lauor.smpedr.Configuration configuration = new com.lauor.smpedr.Configuration(dataSource, new SpringManagedTransactionFactory());
        configuration = this.bindInterceptors(interceptors, configuration);
 
        SqlSessionFactory sqlSessionFactory = new SpringManagedSqlSessionFactory(configuration, new SqlGeneratorMysql());
        return sqlSessionFactory;
    }
 
    //设置拦截器
    private com.lauor.smpedr.Configuration bindInterceptors(String interceptors, com.lauor.smpedr.Configuration configuration){
        if ( Str.isEmpty(interceptors) ) return configuration;
 
        String[] interceptorArr = interceptors.split(",");
        for (String interceptor : interceptorArr) {
            try {
                Class interceptorCls = ClassUtils.forName(interceptor, this.getClass().getClassLoader());
                Constructor constructor = interceptorCls.getDeclaredConstructor();
                configuration.addInterceptor( (Interceptor) constructor.newInstance() );
            } catch (Exception ex){
                LoggerFactory.getLogger(SmpEdrAutoConfiguration.class).error(String.format("error to add interceptor: %s", interceptor), ex);
                System.exit(-1);
            }
        }
        return configuration;
    }
}

5:springboot使用

      引入依赖后直接通过sqlSessionFactory bean操作DBMS。

6:使用JMeter进行压力测试

   1：测试机器性能参数

        处理器: Intel(R) Core(TM) i7-8550U CPU @ 1.8GHZ

        内存: 8GB

        硬盘: 固态硬盘/嵌入式多媒体控制器 (EMMC) 1 238GB, Micron_1100_MTFDDAV256TBN

        系统:windows10家庭中文版

    2:  测试样例

         一分钟1200次查询单表前10条数据，时间排序，无缓存，总记84万三千次请求。

    3:  测试结果

7:源码地址

     完整代码:https://gitee.com/tandatda/smpedr

     使用完整demo:https://gitee.com/tandatda/demo-edr-smpdb