java数据库操作工具类

来源：互联网发布：jdk源码分析编辑：程序博客网时间：2024/06/06 18:19

原地址：https://www.oschina.net/code/snippet_1998172_50215

感谢原博主

package com.http.utils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
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.Map.Entry;
import java.util.Set;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.ArrayUtils;

/**
* [简要描述]:数据库工具类 
* [详细描述]: 
*
* @version [revision],2015年8月12日
* @author Magic_yuan
*/
public class DBHelp
{
/**
* [简要描述]:获取主库的数据源连接 
* [详细描述]: 
*
* @return
* @exception
*/
public static Connection getConn()
{
Connection conn = null;
try
{
// Class.forName("com.mysql.jdbc.Driver");
// String url = PropertiesUtils.getValue("mysqlUrl");
// String user = PropertiesUtils.getValue("mysqlUser");
// String password = PropertiesUtils.getValue("mysqlPwd");
// conn = DriverManager.getConnection(url, user, password);

Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
DataSource ds = (DataSource) envContext.lookup("neushopDataSourceJNDI");
conn = ds.getConnection();
}
catch (Exception e)
{
e.printStackTrace();
}
return conn;
}

/**
* [简要描述]:获取查询库的数据源链接 
* [详细描述]: 
*
* @return
* @exception
*/
public static Connection getQueryConn()
{
Connection conn = null;
try
{
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
DataSource ds = (DataSource) envContext.lookup("neushopDataSourceQuery");
conn = ds.getConnection();
}
catch (Exception e)
{
e.printStackTrace();
}
return conn;
}

/**
* [简要描述]:释放资源 
* [详细描述]: 
*
* @param o
* @exception
*/
public static void closeObject(Object o)
{
if (o != null)
{
if (o instanceof Connection)
{
try
{
((Connection) o).close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
if (o instanceof Statement)
{
try
{
((Statement) o).close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
if (o instanceof PreparedStatement)
{
try
{
((PreparedStatement) o).close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
if (o instanceof ResultSet)
{
try
{
((ResultSet) o).close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
}

/**
* [简要描述]:查询记录总数 
* [详细描述]: 
*
* @param sql
* @return
* @exception
*/
public static int getUniqueResult(String sql)
{
int result = 0;
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try
{
conn = DBHelp.getConn();
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
if (rs.next())
{
result = rs.getInt(1);
}

}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
DBHelp.closeObject(rs);
DBHelp.closeObject(pst);
DBHelp.closeObject(conn);
}
return result;
}

/**
* [简要描述]:常规sql操作 
* [详细描述]: 
*
* @param sql
* @param objects
* @return
* @exception
*/
public static int doDML(String sql, Object[] objects)
{
Connection conn = null;
PreparedStatement pst = null;
int flag = 0;
try
{
conn = getConn();
pst = conn.prepareStatement(sql);
if (objects != null)
{
for (int i = 0; i < objects.length; i++)
{
pst.setObject(i + 1, objects[i]);
}
}
flag = pst.executeUpdate();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
DBHelp.closeObject(pst);
DBHelp.closeObject(conn);
}
return flag;
}

/**
* [简要描述]:根据表名和对象自动更新keys值记录 
* [详细描述]: 
*
* @param tableName
* @param t
* @param keys
* @return
* @throws ReflectiveOperationException
* @exception
*/
public static <T> int autoUpdate(String tableName, T t, String[] keys)
throws ReflectiveOperationException
{
Connection conn = null;
PreparedStatement pst = null;

Map beanMap = null;
try
{
beanMap = BeanUtils.describe(t);
}
catch (IllegalAccessException | InvocationTargetException
| NoSuchMethodException e1)
{
e1.printStackTrace();
throw e1;
}

StringBuffer sql = new StringBuffer();
StringBuffer param = new StringBuffer();
StringBuffer where = new StringBuffer(" where ");
sql.append("update ").append(tableName).append(" set ");

Set<Entry<Object, Object>> entrySet = beanMap.entrySet();

for (Entry<Object, Object> entry : entrySet)
{
if ("class".equalsIgnoreCase((String) entry.getKey()))
{
continue;
}

if (ArrayUtils.contains(keys, entry.getKey()))
{
where.append(entry.getKey()).append(" = '").append(
entry.getValue()).append("' and ");
continue;
}

if (null == entry.getValue())
{
continue;
}
else
{
param.append(entry.getKey()).append(" = '").append(
entry.getValue()).append("',");
;
}
}
sql.append(
param.toString().substring(0, param.toString().lastIndexOf(","))).append(where.substring(0, where.lastIndexOf("and")));

int flag = 0;
try
{
conn = getConn();
pst = conn.prepareStatement(sql.toString());
flag = pst.executeUpdate();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
DBHelp.closeObject(pst);
DBHelp.closeObject(conn);
}
return flag;
}

/**
* [简要描述]:根据表名和对象自动插入 
* [详细描述]: 
*
* @param tableName
* @param t
* @return
* @throws ReflectiveOperationException
* @exception
*/
public static <T> int autoInsert(String tableName, T t)
throws ReflectiveOperationException
{
Connection conn = null;
PreparedStatement pst = null;

Map beanMap = null;
try
{
beanMap = BeanUtils.describe(t);
}
catch (IllegalAccessException | InvocationTargetException
| NoSuchMethodException e1)
{
e1.printStackTrace();
throw e1;
}

StringBuffer sql = new StringBuffer();
StringBuffer param = new StringBuffer();
StringBuffer values = new StringBuffer();
sql.append("insert into ").append(tableName).append(" (");

Set<Entry<Object, Object>> entrySet = beanMap.entrySet();

for (Entry<Object, Object> entry : entrySet)
{
if ("class".equalsIgnoreCase((String) entry.getKey()))
{
continue;
}

if (null == entry.getValue())
{
continue;
}
else
{
param.append(entry.getKey()).append(",");
values.append("'").append(entry.getValue()).append("',");
}
}
sql.append(
param.toString().substring(0, param.toString().lastIndexOf(","))).append(
") values ( ").append(
values.toString().substring(0,
values.toString().lastIndexOf(","))).append(" )");

int flag = 0;
try
{
conn = getConn();
pst = conn.prepareStatement(sql.toString());
flag = pst.executeUpdate();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
DBHelp.closeObject(pst);
DBHelp.closeObject(conn);
}
return flag;
}

/**
* [简要描述]:根据columns自动封装查询结果为Map集合 
* [详细描述]: 
*
* @param sql
* @param args
* @param columns
* @return
* @exception
*/
public static List<Map<String, String>> doQuery(String sql, String[] args,
String[] columns)
{
Connection conn = null;
PreparedStatement pst = null;
ResultSet rst = null;
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
try
{
conn = getQueryConn();
pst = conn.prepareStatement(sql);
if (args != null)
{
for (int i = 0; i < args.length; i++)
{
pst.setString(i + 1, args[i]);
}
}
rst = pst.executeQuery();
while (rst.next())
{
Map<String, String> map = new HashMap<String, String>();
for (int i = 0; i < columns.length; i++)
{
map.put(columns[i], rst.getString(columns[i]));
}
list.add(map);
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
DBHelp.closeObject(rst);
DBHelp.closeObject(pst);
DBHelp.closeObject(conn);
}
return list;
}

/**
* [简要描述]:查询对象集合 
* [详细描述]: 
*
* @param sql
* @param t
* @return
* @exception
*/
public static <T> List<T> doQuery(String sql, Class<T> t)
{
Connection conn = null;
PreparedStatement pst = null;
ResultSet rst = null;

Object tempStr = null;
List<T> list = new ArrayList<T>();
try
{
conn = getQueryConn();
pst = conn.prepareStatement(sql);
rst = pst.executeQuery();
while (rst.next())
{
T obj = t.newInstance();

if (obj instanceof String)
{
obj = (T) rst.getString(1);
}
else
{
Field[] fields = obj.getClass().getDeclaredFields();
for (Field field : fields)
{
try
{
tempStr = rst.getObject(field.getName());
}
catch (SQLException e)
{
tempStr = "";
}

field.setAccessible(true);

BeanUtils.setProperty(obj, field.getName(), tempStr);
}

}

list.add(obj);
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
DBHelp.closeObject(rst);
DBHelp.closeObject(pst);
DBHelp.closeObject(conn);
}
return list;
}

/**
* [简要描述]:查询记录总数 
* [详细描述]: 
*
* @param sql
* @return
* @exception
*/
public static int queryCount(String sql)
{
Connection conn = null;
PreparedStatement pst = null;
ResultSet rst = null;
int result = 0;
try
{
conn = getQueryConn();
pst = conn.prepareStatement(sql);
rst = pst.executeQuery();
if (rst.next())
{
result = rst.getInt(1);
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
DBHelp.closeObject(rst);
DBHelp.closeObject(pst);
DBHelp.closeObject(conn);
}
return result;
}

/**
* [简要描述]:查询单个字符串 
* [详细描述]: 
*
* @param sql
* @return
* @exception
*/
public static String queryString(String sql)
{
Connection conn = null;
PreparedStatement pst = null;
ResultSet rst = null;
String result = "";
try
{
conn = getQueryConn();
pst = conn.prepareStatement(sql);
rst = pst.executeQuery();
if (rst.next())
{
result = rst.getString(1);
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
DBHelp.closeObject(rst);
DBHelp.closeObject(pst);
DBHelp.closeObject(conn);
}
return result;
}

/**
* [简要描述]:查询单个对象 
* [详细描述]: 
*
* @param sql
* @param t
* @return
* @exception
*/
public static <T> T queryOne(String sql, Class<T> t)
{
Connection conn = null;
PreparedStatement pst = null;
ResultSet rst = null;
T obj = null;
String tempStr = null;
try
{
obj = t.newInstance();
}
catch (InstantiationException e1)
{
e1.printStackTrace();
}
catch (IllegalAccessException e1)
{
e1.printStackTrace();
}
try
{
conn = getQueryConn();
pst = conn.prepareStatement(sql);
rst = pst.executeQuery();
if (rst.next())
{
Field[] fields = obj.getClass().getDeclaredFields();
for (Field field : fields)
{
if ("serialVersionUID".equalsIgnoreCase(field.getName()))
{
continue;
}

try
{
tempStr = rst.getString(field.getName());
}
catch (SQLException e)
{
tempStr = "";
}

BeanUtils.setProperty(obj, field.getName(), tempStr);
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
DBHelp.closeObject(rst);
DBHelp.closeObject(pst);
DBHelp.closeObject(conn);
}
return obj;
}

/**
* [简要描述]:批量操作 
* [详细描述]: 
*
* @param sql
* @param argsList
* @exception
*/
public static void addBatch(String sql, List<String[]> argsList)
{
Connection conn = null;
PreparedStatement prest = null;
try
{
conn = getConn();
conn.setAutoCommit(false);
prest = conn.prepareStatement(sql);

for (String[] strings : argsList)
{
for (int i = 0; i < strings.length; i++)
{
prest.setString(i + 1, strings[i]);
}
prest.addBatch();
}

prest.executeBatch();
conn.commit();
}
catch (SQLException ex)
{
ex.printStackTrace();
}
finally
{
DBHelp.closeObject(prest);
DBHelp.closeObject(conn);
}
}
}

0 0