package org.voovan.test.db;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
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;
public class JdbcOperateDemo {
public static void main(String[] args) throws Exception {
DruidDataSource 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);
}
JdbcOperate jOperate = new JdbcOperate(dataSource);
String sql = "";
//查询测试
sql = "select * from sc_script";
List<Map<String, Object>> manyMaps = jOperate.queryMapList(sql);
Logger.simple("查询测试: " + manyMaps);
//查询并返回多个对象
sql = "select * from sc_script";
List<ScriptEntity> manyObject = jOperate.queryObjectList(sql, ScriptEntity.class);
Logger.simple("查询并返回多个对象: " + manyObject);
//Java基本类型(int)
sql = "select count(*) from sc_script";
long count = jOperate.queryObject(sql, long.class);
Logger.simple("Java基本类型: " + count);
//Java基本类型(String)
sql = "select packagePath from sc_script";
String singleField = jOperate.queryObject(sql, String.class);
Logger.simple("Java基本类型: " + singleField);
//Map参数 => 返回List<Map>
HashMap<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("packagePath", "org.hocate.test");
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);
//Map参数 => 返回List<Object>
sql = "select * from sc_script where PackagePath=::packagePath";
List<ScriptEntity> manyObjectMapParam = jOperate.queryObjectList(sql, ScriptEntity.class, paramMap);
Logger.simple("Map参数 => 返回List<Object>: " + manyObjectMapParam);
//对象参数 => 返回对象列表 List<Object>
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);
//对象参数 => 返回对象列表 List<Map>
sql = "select * from sc_script where PackagePath=::packagePath";
List<Map<String, Object>> manyMapsObjectParam = jOperate.queryMapList(sql, sEntity);
Logger.simple("查询并返回多个对象: " + manyMapsObjectParam);
//不定个数参数 => 返回一个复杂对象
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("不定个数参数 => 返回一个复杂对象: " + singleObjectArrayParam);
//不定个数参数 => 返回一个复杂对象
sql = "select * from sc_script where PackagePath=::1 and version=::2";
List<ScriptEntity> manyObjectArrayParam = jOperate.queryObjectList(sql, ScriptEntity.class);
Logger.simple("自动移除无对应参数的 SQL 查询条件: " + manyObjectArrayParam);
//数据库中表的列名和对象中的属性名模糊匹配
//packagePath 列名转换为 paCKAge_Path
//SouRCEPath 列名转换为 Source_Path
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);
//事务测试
jOperate = new JdbcOperate(dataSource, true);
sql = "update sc_script set version=::1";
int updateCount = jOperate.update(sql,-1);
Logger.simple("事务更新记录数:" + updateCount);
List<Map<String, Object>> updateResult = jOperate.queryMapList("select version from sc_script");
Logger.simple("事务回滚前:" + updateResult);
jOperate.rollback();
List<Map<String, Object>> rollbackResult = jOperate.queryMapList("select version from sc_script");
Logger.simple("事务误回滚后:" + rollbackResult);
//存储过程测试
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);
Logger.simple("存储过程测试: " + callWithMap);
sql = "{call test(::1)}";
List<Object> callWithParam = jOperate.call(sql, new CallType[]{CallType.INOUT}, "1111");
Logger.simple("存储过程测试: " + callWithParam);
}
}