JdbcOperatorUnit.java

package org.voovan.test.db;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import junit.framework.TestCase;
import org.voovan.db.CallType;
import org.voovan.db.JdbcOperate;
import org.voovan.tools.TFile;
import org.voovan.tools.TObject;
import org.voovan.tools.TProperties;
import org.voovan.tools.log.Logger;

import java.io.File;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * 类文字命名
 *
 * @author helyho
 *
 * Java Framework.
 * WebSite: https://github.com/helyho/Voovan
 * Licence: Apache v2 License
 */
public class JdbcOperatorUnit extends TestCase {
    private static DruidDataSource dataSource = null;
    private String sql = "";


    public JdbcOperatorUnit() {
        //只构建一次数据源
        if(dataSource==null) {
            try {
                String druidpath = TFile.getSystemPath("conf" + File.separator + "datasource.properties");
                Properties druidProperites = TProperties.getProperties(new File(druidpath));
                dataSource = TObject.cast(DruidDataSourceFactory.createDataSource(druidProperites));
                dataSource.init();
                Logger.info("Database connection pool init finished");
            } catch (Exception e) {
                Logger.error(e);
            }
        }
    }

    /**
     * 测试返回 List<Map>
     * @throws Exception
     */
    public void test_NoParam_ManyMapResult() throws Exception {
        JdbcOperate jOperate = new JdbcOperate(dataSource);
        //查询测试
        sql = "select * from sc_script";
        List<Map<String, Object>> manyMaps = jOperate.queryMapList(sql);
        Logger.simple("查询测试: " + manyMaps);
        assert(manyMaps.size()==2);
    }

    /**
     * 测试返回 List<Object>
     * @throws Exception
     */
    public void test_NoParam_ManyObjectResult() throws Exception {
        JdbcOperate jOperate = new JdbcOperate(dataSource);
        sql = "select * from sc_script";
        List<ScriptEntity> manyObject = jOperate.queryObjectList(sql, ScriptEntity.class);
        Logger.simple("查询并返回多个对象: " + manyObject);
        assert(manyObject.size()==2);
        assert(manyObject.get(0).getClass().equals(ScriptEntity.class));
    }

    /**
     * 非对象类型测试
     * @throws Exception
     */
    public void test_NoParam_SimpleTypeResult() throws Exception {
        JdbcOperate jOperate = new JdbcOperate(dataSource);
        sql = "select count(*) from sc_script";
        long count = jOperate.queryObject(sql, long.class);
        Logger.simple("Java基本类型: " + count);
        assert(count == 2);
    }

    /**
     * 简单对象类型测试
     * @throws Exception
     */
    public void test_NoParam_ObjectResult() throws Exception {
        JdbcOperate jOperate = new JdbcOperate(dataSource);
        sql = "select packagePath from sc_script";
        String singleField = jOperate.queryObject(sql, String.class);
        Logger.simple("Java基本类型: " + singleField);
        assert(singleField.equals("org.hocate.test"));
    }

    /**
     * Map参数 => 返回List<Map>
     * @throws Exception
     */
    public void test_MapParam_ManyMapResult_RemoveCondiction() throws Exception {
        JdbcOperate jOperate = new JdbcOperate(dataSource);
        HashMap<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("packagePath", "org.hocate.test");
        paramMap.put("version","1.0");
        sql = "select * from sc_script where PackagePath=::packagePath and version=::version";
        List<Map<String, Object>> manyMapsMapParam = jOperate.queryMapList(sql, paramMap);
        Logger.simple("Map参数 => List<Map>: " + manyMapsMapParam);
        assert(manyMapsMapParam.size()==1);
        assertEquals(manyMapsMapParam.get(0).get("packagePath"),"org.hocate.test");
    }

    /**
     * Map参数 => 返回List<Object>
     * @throws Exception
     */
    public void test_MapParam_ManyObjectResult() throws Exception {
        JdbcOperate jOperate = new JdbcOperate(dataSource);
        HashMap<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("packagePath", "org.hocate.test");

        sql = "select * from sc_script where PackagePath=::packagePath";
        List<ScriptEntity> manyObjectMapParam = jOperate.queryObjectList(sql, ScriptEntity.class, paramMap);
        Logger.simple("Map参数 => List<Object>: " + manyObjectMapParam);
        assert(manyObjectMapParam.size()==2);
        assert(manyObjectMapParam.get(0).getClass().equals(ScriptEntity.class));
    }

    /**
     * 对象参数 => 返回对象列表 List<Object>
     * @throws Exception
     */
    public void test_ObjectParam_ManyObjectResult() throws Exception {
        JdbcOperate jOperate = new JdbcOperate(dataSource);
        ScriptEntity sEntity = new ScriptEntity();
        sEntity.setPackagePath("org.hocate.test");

        sql = "select * from sc_script where PackagePath=::packagePath";
        List<ScriptEntity> manyObjectObjectParam = jOperate.queryObjectList(sql, ScriptEntity.class, sEntity);
        Logger.simple("对象参数 => List<Object>: " + manyObjectObjectParam);
        assert(manyObjectObjectParam.size()==2);
        assert(manyObjectObjectParam.get(0).getClass().equals(ScriptEntity.class));
    }

    /**
     * 对象参数 => 返回对象列表 List<Map>
     * @throws Exception
     */
    public void test_ObjectParam_ManyMapResult() throws Exception {
        JdbcOperate jOperate = new JdbcOperate(dataSource);
        ScriptEntity sEntity = new ScriptEntity();
        sEntity.setPackagePath("org.hocate.test");

        sql = "select * from sc_script where PackagePath=::packagePath";
        List<Map<String, Object>> manyMapsObjectParam = jOperate.queryMapList(sql, sEntity);
        Logger.simple("对象参数 => List<Map>: " + manyMapsObjectParam);

        assert(manyMapsObjectParam.size()==2);
    }

    /**
     * 不定个数参数 => 返回一个复杂对象:
     * @throws Exception
     */
    public void test_ManyParam_ObjectResult() throws Exception {
        JdbcOperate jOperate = new JdbcOperate(dataSource);

        sql = "select * from sc_script where PackagePath=::1 and version=::2";
        ScriptEntity singleObjectArrayParam = jOperate.queryObject(sql, ScriptEntity.class, "org.hocate.test", 2.0);
        Logger.simple("不定个数参数 => Object: " + singleObjectArrayParam);
        assertEquals(singleObjectArrayParam.getClass(), ScriptEntity.class);
    }

    /**
     * 没有传入 PackagePath 和 version 参数自动移除 SQL 中的 PackagePath 和 version 条件
     * 具体SQL的变化,查看日志实际执行 sql 的输出
     * @throws Exception
     */
    public void test_RemoveCondiction() throws Exception {
        JdbcOperate jOperate = new JdbcOperate(dataSource);

        sql = "select * from sc_script where PackagePath=::1 and version=::2";
        List<ScriptEntity> manyObjectArrayParam = jOperate.queryObjectList(sql, ScriptEntity.class);
        Logger.simple("自动移除无对应参数的 SQL 查询条件: " + manyObjectArrayParam);
        assertEquals(manyObjectArrayParam.size(),2);
    }

    /**
     * 数据库中表的列名和对象中的属性名模糊匹配
     * 不区分大小写
     * packagePath 列名转换为 paCKAge_Path,SouRCEPath 列名转换为 Source_Path
     * 自动对应到ScriptEntity对象的属性上
     * @throws Exception
     */
    public void test_FillObjectIgnoreCase() throws Exception {
        JdbcOperate jOperate = new JdbcOperate(dataSource);

        sql = "select ID,packagePath as paCKAge_Path,SouRCEPath as Source_Path from sc_script";
        List<ScriptEntity> manyObjectIgnoreCaseField = jOperate.queryObjectList(sql, ScriptEntity.class);
        Logger.simple("数据库中表的列名和对象中的属性名模糊匹配: " + manyObjectIgnoreCaseField);
        assertEquals(manyObjectIgnoreCaseField.size(),2);
    }

    /**
     * 更新和事物测试
     * @throws Exception
     */
    public void test_Update_Trancation() throws Exception {
        JdbcOperate jOperate = new JdbcOperate(dataSource);

        jOperate = new JdbcOperate(dataSource, true);
        sql = "update sc_script set version=::1";
        int updateCount = jOperate.update(sql,-1);
        assert(updateCount==2);
        Logger.simple("事务更新记录数:" + updateCount);
        List<Map<String, Object>> updateResult = jOperate.queryMapList("select version from sc_script");
        assertEquals(updateResult.get(0).get("version"),Float.valueOf(-1));
        Logger.simple("事务回滚前:" + updateResult);
        jOperate.rollback();
        List<Map<String, Object>> rollbackResult = jOperate.queryMapList("select version from sc_script");
        Logger.simple("事务误回滚后:" + rollbackResult);
        assert(!rollbackResult.get(0).get("version").equals(Float.valueOf(-1)));
    }

    public void test_Procedure() throws Exception {
        JdbcOperate jOperate = new JdbcOperate(dataSource);

        jOperate = new JdbcOperate(dataSource);
        Map<String, Object> procParam = new HashMap<String, Object>();
        procParam.put("arg1", "tttt");
        sql = "{call test(::arg1)}";
        List<Object> callWithMap = jOperate.call(sql, new CallType[]{CallType.INOUT}, procParam);
        assert(callWithMap.get(0).equals("org.hocate.test"));
        Logger.simple("存储过程测试: " + callWithMap);
        sql = "{call test(::1)}";
        List<Object> callWithParam = jOperate.call(sql, new CallType[]{CallType.INOUT}, "1111");
        Logger.simple("存储过程测试: " + callWithParam);
        assert(callWithParam.get(0).equals("org.hocate.test"));
    }

}