JDBC的工具类

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

//Jdbc操作的工具类

public class JDBCUtils {
 private static String driverClass = "com.mysql.jdbc.Driver";
 private static String url = "jdbc:mysql://localhost:3306/test";
 private static String username = "root";
 private static String password = "123456";

 // 静态代码块，在类加载时执行，并且只执行一次
 static {
  try {
   Class.forName(driverClass);
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }
 }
 // 获得连接
 public static Connection getConnection() throws SQLException {
  Connection conn = DriverManager.getConnection(url, username, password);
  return conn;
 }

 // 执行更新语句，包括DML,DDL
 public static int executeUpdate(Connection conn, String sql, Object[] params)
   throws SQLException {
  PreparedStatement pst = conn.prepareStatement(sql);
  // 设置参数
  if (null != params && params.length > 0) {
   for (int i = 0; i < params.length; i++) {
    pst.setObject(i + 1, params[i]);  //设置参数从1开始，数组从0开始，要加上1
   }
  }
  // 执行返回 影响的记录数
  int count = pst.executeUpdate();
  close(pst);
  return count;
 }
 // 执行select语句
 public static List<Object[]> executeQuery(Connection conn, String sql,
   Object[] params) throws SQLException {
  PreparedStatement pst = null;
  ResultSet rs = null;
  List<Object[]> list = new ArrayList<Object[]>();
 
  try {
   pst = conn.prepareStatement(sql);
   if (null != params && params.length > 0) {
    for (int i = 0; i < params.length; i++) {
     pst.setObject(i + 1, params[i]);
    }
   }
    rs = pst.executeQuery();
 // 将Result转换成List<Object[]>
   int colCount = rs.getMetaData().getColumnCount(); // 对应数据表中的一行，然后每一列都对应数组中的一个元素
   while (rs.next()) {
    Object[] Arr = new Object[colCount]; //定义一个与列总数对应的数组
    for (int i = 0; i < Arr.length; i++) {
     Arr[i] = rs.getObject(i + 1);
    }
    list.add(Arr);
   }
   return list;
  } finally {
   close(rs);
   close(pst);
  }
 }

 // 执行select语句
 public static <T> List<T> executeQuery(Connection conn, String sql,
   Object[] params, RowMapper<T> rm) throws SQLException {
  PreparedStatement pst = null;
  ResultSet rs = null;
  List<T> list = new ArrayList<T>();
  try {
   pst = conn.prepareStatement(sql);
   if (null != params && params.length > 0) {
    for (int i = 0; i < params.length; i++) {
     pst.setObject(i + 1, params[i]);
    }
   }
   rs = pst.executeQuery();
   while (rs.next()) {
    T t = rm.getEntity(rs);  //从结果集中的一行返回一个实体对象
    list.add(t);
   }
   return list;
  } finally {
   close(rs);
   close(pst);
  }
 }

//关闭Statement
 public static void close(ResultSet rs) {
  if (null != rs) {
   try {
    rs.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }

 public static void close(Statement st) {
  if (null != st) {
   try {
    st.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }

 public static void close(Connection conn) {
  if (null != conn) {
   try {
    conn.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }

 public static void close(ResultSet rs, Statement st, Connection conn) {
  close(rs);
  close(st);
  close(conn);
 }
}