c3p0 jdbc执行sql并且返回--java反射封装进类
package com.dhcc.cdbp.util;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.tomcat.jdbc.pool.DataSource;
/**
* 类描述: 连接池管理
* @author 80965
*
*/
public class ConnectionManager {
private static Log logger = LogFactory.getLog(ConnectionManager.class);
private static DataSource ds;
//获取与指定数据库的连接
private static DataSource getInstance(String driverClass,String url,String userName,String password){
ds = new DataSource();//创建连接池实例
ds.setDriverClassName(driverClass);//设置连接池连接数据库所需的驱动
ds.setUrl(url);//设置连接数据库的URL
ds.setUsername(userName);//设置连接数据库的用户名
ds.setPassword(password);//设置连接数据库的密码
ds.setInitialSize(1);//初始化连接数
logger.info("初始化连接池");
return ds;
}
//从连接池返回一个连接
public static Connection getConnection(String driverClass,String url,String userName,String password){
//初始化数据库连接
getInstance(driverClass,url,userName,password);
Connection conn = null;
try {
logger.info("获取连接");
conn = ds.getConnection();
} catch (Exception e) {
logger.info("连接失败");
e.printStackTrace();
}
return conn;
}
//释放资源
public static void realeaseResource(ResultSet rs,PreparedStatement ps,Connection conn){
logger.info("释放资源");
if(null != rs){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != ps){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
========================================================================================================================
package com.dhcc.cdbp.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import com.dhcc.cdbp.entity.dmp.ColumnResConfig;
import com.dhcc.cdbp.entity.menu.ApplyMenu;
public class MysqlTable {
static Connection conn=null;
private static Log logger = LogFactory.getLog(MysqlTable.class);
public static List<String> queryDataBySql(Connection conn, PreparedStatement ps, List<List<String>> datalist) {
List<String> colList = new ArrayList<String>();
ResultSet rs = null;
try {
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
for(int i=1; i<=colCount; i++) {
colList.add(rsmd.getColumnName(i));
}
while(rs.next()) {
List<String> list = new ArrayList<String>();
for (int i=1; i<=colCount; i++) {
list.add(rs.getString(i));
}
datalist.add(list);
}
} catch (Exception e) {
logger.error(e.getMessage());
e.printStackTrace();
} finally {
ConnectionManager.realeaseResource(rs, ps, conn);
}
return colList;
}
public static void queryEntityDataBySql(Connection conn,PreparedStatement ps, List<Object> datalist,Object obj) {
Map<String, String> cloumMaps=columEntity.getColumnValue(obj.getClass());
List<List<String>> datain = new ArrayList<List<String>>();
List<String> colList = queryDataBySql(conn,ps,datain);
for (List<String> ss : datain) {
Map<String, String> map = new HashMap<String,String>();
for(int i=0; i<ss.size(); i++) {
String cloumMap=cloumMaps.get(colList.get(i).toUpperCase());
columEntity.setValue(obj,cloumMap, ss.get(i));
}
datalist.add(obj);
}
}
public static void queryMapDataBySql(Connection conn,PreparedStatement ps, List<Map<String,String>> datalist) {
List<List<String>> datain = new ArrayList<List<String>>();
List<String> colList = queryDataBySql(conn,ps,datain);
for (List<String> ss : datain) {
Map<String, String> map = new HashMap<String,String>();
for(int i=0; i<ss.size(); i++) {
map.put(colList.get(i).toLowerCase(), ss.get(i));
}
datalist.add(map);
}
}
/**
* 读取数据库数据转换成map
*/
public static void queryMapDataBySql(Connection conn, List<Map<String,String>> datalist, String sql) {
List<List<String>> datain = new ArrayList<List<String>>();
List<String> colList = queryDataBySql(conn,datain,sql);
for (List<String> ss : datain) {
Map<String, String> map = new HashMap<String,String>();
for(int i=0; i<ss.size(); i++) {
map.put(colList.get(i).toLowerCase(), ss.get(i));
}
datalist.add(map);
}
}
/**
* 读取数据库数据转换成实体类
*/
public static void queryEntityDataBySql(Connection conn, List<Object> datalist, String sql,Object obj) {
Map<String, String> cloumMaps=columEntity.getColumnValue(obj.getClass());
List<List<String>> datain = new ArrayList<List<String>>();
List<String> colList = queryDataBySql(conn,datain,sql);
for (List<String> ss : datain) {
Map<String, String> map = new HashMap<String,String>();
for(int i=0; i<ss.size(); i++) {
String cloumMap=cloumMaps.get(colList.get(i).toUpperCase());
columEntity.setValue(obj,cloumMap, ss.get(i));
}
datalist.add(obj);
}
}
public static List<String> queryDataBySql(Connection conn, List<List<String>> datalist, String sql) {
List<String> colList = new ArrayList<String>();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps=conn.prepareStatement(sql);
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
for(int i=1; i<=colCount; i++) {
colList.add(rsmd.getColumnName(i));
}
while(rs.next()) {
List<String> list = new ArrayList<String>();
for (int i=1; i<=colCount; i++) {
list.add(rs.getString(i));
}
datalist.add(list);
}
} catch (Exception e) {
logger.error(e.getMessage());
e.printStackTrace();
} finally {
ConnectionManager.realeaseResource(rs, ps, conn);
}
return colList;
}
/**
* 执行sql(insert/update/delete)
*/
public static int executeUpdate(Connection conn, String sql) {
int i = -1;
Statement stmt = null;
try {
stmt = conn.createStatement();
i = stmt.executeUpdate(sql);
} catch (Exception e) {
logger.error(e.getMessage());
} finally {
try {
if (null!=stmt) {
stmt.close();
}
} catch (SQLException e) {
logger.error(e.getMessage());
}
}
return i;
}
/**
*批量执行sql
*/
public static void executeBatchSql(Connection conn, List<String> sqlList) {
Statement stmt = null;
try {
stmt = conn.createStatement();
for ( String sql : sqlList) {
if (sql!=null && sql.length()>6) {
logger.info(sql);
stmt.addBatch(sql);
}
}
if (sqlList.size()>0) {
logger.debug("execute num: " + stmt.executeBatch().length);
}
} catch (Exception e) {
logger.error(e.getMessage());
e.printStackTrace();
} finally {
try {
if (null!=stmt) {
stmt.close();
}
} catch (SQLException e) {
logger.error(e.getMessage());
}
}
}
/**
* 获取数据库连接
*/
public static Connection getConnection() {
String driverClass="";
String url="";
String userName="";
String password="";
Global dataSource = null;
/*if(dataSource!=null) {
if(dataSource.getDatabaseType().equalsIgnoreCase("ORACLE")) {
driverClass="oracle.jdbc.OracleDriver";
}else if(dataSource.getDatabaseType().equalsIgnoreCase("MYSQL")) {
driverClass="com.mysql.jdbc.Driver";
}
url=dataSource.getUrl();
userName=dataSource.getUsername();
password=dataSource.getPassword();
}else {
dto.setMsg("数据库连接失败：无连接参数");
logger.info("数据库连接失败：无连接参数");
}*/
Connection connection = ConnectionManager.getConnection(Global.DRIVER, Global.URL, Global.USERNAME, Global.PASSWORD);
return connection;
}
/**
* 关闭数据路连接
*/
public static void closeConnection(Connection conn) {
try {
conn.close();
} catch (Exception e) {
logger.error(e.getMessage());
}
}
}
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
package com.dhcc.cdbp.util;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import javax.persistence.Column;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.dhcc.cdbp.entity.dmp.ColumnResConfig;
import com.dhcc.cdbp.entity.menu.ApplyMenu;
public class columEntity {
@org.junit.Test
public void test() {
ColumnResConfig Applymenu=new ColumnResConfig();
/*
columEntity.setValue(Applymenu,"jump_url","asd");
System.out.println(Applymenu.getJumpUrl());
*/
/*System.out.println(removeLine("jump_url"));*/
/*PID=pId, ID=id, NAME=name, JUMP_URL=jumpUrl*/
Map<String, String> m=getColumnValue(ColumnResConfig.class);
/*setValue(Applymenu,"jumpUrl","asd");*/
System.out.println(m);
}
private static Log logger = LogFactory.getLog(columEntity.class);
public static String removeLine(String str){
if(null != str && str.contains("_")){
/*int i = str.indexOf("_");
logger.info(i);
char ch = str.charAt(i+1);
char newCh = (ch+"").substring(0, 1).toUpperCase().toCharArray()[0];
logger.info(newCh);
String newStr = str.replace(str.charAt(i+1), newCh);
logger.info(newStr);
String newStr2 = newStr.replace("_", "");
logger.info(newStr2);
String[] strnew=str.split("_");
String newStr2=strnew[0];
for(int stenewi=1;stenewi<strnew.length;stenewi++){
newStr2=newStr2+strnew[stenewi].substring(0,1).toUpperCase()+strnew[stenewi].substring(1);
}
*/
String newStr2 =str.replace("-", "");
return newStr2;
}
return str;
}
private static Object getClassTypeValue(Class<?> typeClass, Object value){
if(typeClass == int.class || value instanceof Integer){
if(null == value){
return 0;
}
return value;
}else if(typeClass == short.class){
if(null == value){
return 0;
}
return value;
}else if(typeClass == byte.class){
if(null == value){
return 0;
}
return value;
}else if(typeClass == double.class){
if(null == value){
return 0;
}
return value;
}else if(typeClass == long.class){
if(null == value){
return 0;
}
return value;
}else if(typeClass == String.class){
if(null == value){
return "";
}
return value;
}else if(typeClass == boolean.class){
if(null == value){
return true;
}
return value;
}else if(typeClass == BigDecimal.class){
if(null == value){
return new BigDecimal(0);
}
return new BigDecimal(value+"");
}else {
return typeClass.cast(value);
}
}
/**
* 获得、执行set方法
* @param obj
* @param filedName
* @param value
*/
public static void setValue(Object obj,String filedName,Object value){
filedName= removeLine(filedName);
Class<?> clazz=obj.getClass();
//判断第二个字母是否为大小写
String methodName=null;
if ( Character.isUpperCase(filedName.charAt(1))){
methodName = "set" +filedName;
}else{
methodName = "set" + filedName.substring(0,1).toUpperCase()+filedName.substring(1);
}
Class<?> typeClass;
try {
typeClass = clazz.getDeclaredField(filedName).getType();
Method method;
try {
method = clazz.getDeclaredMethod(methodName, new Class[]{typeClass});
try {
method.invoke(obj, new Object[]{getClassTypeValue(typeClass, value)});
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (NoSuchMethodException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (NoSuchFieldException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 通过反射机制获得实体类的@Column和属性的map集合
* @param classz
* @return
*/
public static Map<String, String> getColumnValue(Class classz) {
Map<String, String> m=new HashMap<String, String>();
Field[] fields=classz.getDeclaredFields();
Field field;
String[] value=new String[fields.length];
for (int i = 0; i <fields.length ; i++) {
fields[i].setAccessible(true);
}
for(int i = 0;i<fields.length ; i++){
try {
field=classz.getDeclaredField(fields[i].getName());
Column column=field.getAnnotation(Column.class); //获取指定类型注解
if(column!=null){
value[i]=column.name();
m.put(column.name(), field.getName());
}
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
}
return m;
}
}
浙公网安备 33010602011771号