操作数据库工具类代码,Java代码库,德仔网

代码语言
代码分类
【Java】操作数据库工具类
作者:Magic_yuan / 发布于2015/8/19/ 621
自动封装数据库操作，适合不想配置各种XML的同学。。。。。

	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;
	
	/**
	* [简要描述]:数据库工具类<br/>
	* [详细描述]:<br/>
	*
	* @version [revision],2015年8月12日
	* @author Magic_yuan
	*/
	public class DBHelp
	{
	 /**
	 * [简要描述]:获取主库的数据源连接<br/>
	 * [详细描述]:<br/>
	 *
	 * @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;
	 }
	
	 /**
	 * [简要描述]:获取查询库的数据源链接<br/>
	 * [详细描述]:<br/>
	 *
	 * @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;
	 }
	
	 /**
	 * [简要描述]:释放资源<br/>
	 * [详细描述]:<br/>
	 *
	 * @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();
	 }
	 }
	 }
	 }
	
	 /**
	 * [简要描述]:查询记录总数<br/>
	 * [详细描述]:<br/>
	 *
	 * @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操作<br/>
	 * [详细描述]:<br/>
	 *
	 * @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值记录<br/>
	 * [详细描述]:<br/>
	 *
	 * @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;
	 }
	
	 /**
	 * [简要描述]:根据表名和对象自动插入<br/>
	 * [详细描述]:<br/>
	 *
	 * @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集合<br/>
	 * [详细描述]:<br/>
	 *
	 * @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;
	 }
	
	 /**
	 * [简要描述]:查询对象集合<br/>
	 * [详细描述]:<br/>
	 *
	 * @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;
	 }
	
	 /**
	 * [简要描述]:查询记录总数<br/>
	 * [详细描述]:<br/>
	 *
	 * @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;
	 }
	
	 /**
	 * [简要描述]:查询单个字符串<br/>
	 * [详细描述]:<br/>
	 *
	 * @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;
	 }
	
	 /**
	 * [简要描述]:查询单个对象<br/>
	 * [详细描述]:<br/>
	 *
	 * @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;
	 }
	
	 /**
	 * [简要描述]:批量操作<br/>
	 * [详细描述]:<br/>
	 *
	 * @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);
	 }
	 }
	}
试试其它关键字
　操作数据库工具类　　数据库工具类　　数据库　　工具类　
同语言下
可能有用的
Magic_yuan贡献的其它代码(4)