自己封装的BaseDao更加灵活方便hashmap

2019独角兽企业重金招聘Python工程师标准>>>

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;

public class BaseControl {
static Logger log &＃61; Logger.getLogger(BaseControl.class);

/**
* 查询
* &＃64;param sql 查询Sql语句
* &＃64;param params 参数集合
* &＃64;return
*/
public static List> executeQuery(String sql,Object...params){
  List> lists &＃61; new ArrayList>();
  Connection conn &＃61; null;
  PreparedStatement ps &＃61; null;
  ResultSet rs &＃61; null;
  try {
   conn &＃61; DBUtil.getConn();
   ps &＃61; conn.prepareStatement(sql);
   //给SQL参数进行赋值
   int temp &＃61; 1;
   for (int i &＃61; 0; i
    ps.setObject(temp&＃43;&＃43;, params[i]);
   }
   rs &＃61; ps.executeQuery();
   ResultSetMetaData rss &＃61; rs.getMetaData();
   int columnCount &＃61; rss.getColumnCount();
   String[] columnName &＃61; new String[columnCount];//获取字段名称集合
   while(rs.next()){
    HashMap map &＃61; new HashMap();
    for (int i &＃61; 0; i
      columnName[i] &＃61; rss.getColumnName(i&＃43;1);
    }
    for (int j &＃61; 1; j <&＃61; columnCount; j&＃43;&＃43;) {
     Object columnValue &＃61; rs.getObject(j);
     map.put(columnName[j-1], columnValue);
    }
    lists.add(map);
   }
   return lists;
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
   log.error("查询SQL异常", e);
  }finally{
   DBUtil.closeConn(conn, ps, rs);
  }
  return null;
  }
/**
* 以数据库字段名为键&＃xff0c;该字段值为值返回一个map
* &＃64;param sql查询语句
* &＃64;param param单条件查询
* &＃64;return
*/
public static HashMapexecuteQueryByColumn(String sql,Object...params){
  HashMap map &＃61; new HashMap();
  Connection conn &＃61; null;
  PreparedStatement ps &＃61; null;
  ResultSet rs &＃61; null;
  conn &＃61; DBUtil.getConn();
  try {
   ps &＃61; conn.prepareStatement(sql);
   int temp &＃61; 1;
   for (int i &＃61; 0; i
    ps.setObject(temp&＃43;&＃43;, params[i]);
   }
   rs &＃61; ps.executeQuery();
   ResultSetMetaData rss &＃61; rs.getMetaData();
   int columnCount &＃61; rss.getColumnCount();
   String[] columnName &＃61; new String[columnCount];
   while(rs.next()){
    for (int i &＃61; 0; i
     columnName[i] &＃61; rss.getColumnName(i&＃43;1);
    }
    for (int i &＃61; 1; i <&＃61;columnCount; i&＃43;&＃43;) {
     Object value &＃61; rs.getObject(i);
     String Name &＃61; columnName[i-1];
     map.put(Name, value);
    }
   }
   return map;
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return null;

};
/**
* 增加&＃xff0c;修改&＃xff0c;或删除
* &＃64;param sql
* &＃64;param params
* &＃64;return
*/
public static int executeSaveOrUpdate(String sql,List params){
  Connection conn &＃61; null;
  PreparedStatement ps &＃61; null;
  try {
   conn &＃61; DBUtil.getConn();
      ps &＃61; conn.prepareStatement(sql);
   int temp &＃61; 1;
   for (Iterator p &＃61; params.iterator(); p.hasNext();) {
    ps.setObject(temp&＃43;&＃43;, p.next());
   }
   ps.addBatch();

   return ps.executeBatch().length;
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
   log.error("插入SQL异常", e);
  }finally{
   DBUtil.closeConn(conn, ps, null);
  }
  return 0;
  }
/**
* 少参数插入
* &＃64;param sql
* &＃64;param params
* &＃64;return
*/
public static int executeSaveOrUpdate(String sql,Object... params){
  Connection conn &＃61; null;
  PreparedStatement ps &＃61; null;
  try {
   conn &＃61; DBUtil.getConn();
      ps &＃61; conn.prepareStatement(sql);
   int temp &＃61; 1;
    for (int i &＃61; 0; i
     ps.setObject(temp&＃43;&＃43;, params[i]);
    }

   ps.addBatch();

   return ps.executeBatch().length;
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
   log.error("插入SQL异常", e);
  }finally{
   DBUtil.closeConn(conn, ps, null);
  }
  return 0;
  }
DBUtil类

public class DBUtil {

static Logger logger&＃61;Logger.getLogger(DBUtil.class);

static Properties properties &＃61; null;

/**
* 返回数库链接对象
* &＃64;return
*/
public static Properties getProperInfo() {
  InputStream is &＃61; Thread.currentThread().getContextClassLoader()
    .getResourceAsStream("oracle.properties");
  properties &＃61; new Properties();
  try {
   properties.load(is);
  } catch (IOException e) {
   e.printStackTrace();
   logger.error("数据库配置文件异常");
  }
  return properties;
}

/**
* 定义链接数据库方法
* &＃64;return
* &＃64;throws ClassNotFoundException
* &＃64;throws SQLException
*/
public static Connection getConn() {
   if (properties &＃61;&＃61; null) {
    properties &＃61; getProperInfo();
   }
   String url &＃61; properties.getProperty("url");
   String user &＃61; properties.getProperty("user");
   String password &＃61; properties.getProperty("password");

   //加载数据库驱动
   try {
    logger.debug("加载数据库驱动");
    Class.forName(properties.getProperty("driver"));
   } catch (ClassNotFoundException e) {
    e.printStackTrace();
    logger.error("数据驱动加载异常", e);
   }
   //建立数据连接
   try {
    logger.debug("建立数据库连接");
    return DriverManager.getConnection(url, user, password);
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    logger.error("建立数据库连接异常", e);
   }
   logger.debug("数据连接成功");
   return null;
}

/**
* 关闭链接
* &＃64;param conn
* &＃64;param ps
* &＃64;param rs
*/
public static void closeConn(Connection conn, PreparedStatement ps,ResultSet rs) {
  try {
   if (rs !&＃61; null) {
    rs.close();
   }
  } catch (SQLException e) {
   e.printStackTrace();
   logger.error("关闭ResultSet异常", e);
  }
  try {
   if (ps !&＃61; null) {
    ps.close();
   }
  } catch (SQLException e) {
   e.printStackTrace();
   logger.debug("关闭PreparedStatement异常",e);
  }
  try {
   if (conn !&＃61; null) {
    conn.close();
   }
  } catch (SQLException e) {
   e.printStackTrace();
   logger.error("关闭Connection异常", e);
  }
}