MySQLHelper

package hr.baple.util;

import java.sql.Connection;
import java.sql.DriverManager;
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;

public final class MysqlHelper {

 public static String driverName;
 public static String dbURL;
 public static String dbName;
 public static String dbPwd;
 public static Connection connection;

 private MysqlHelper() {

 }

 static {
  // 加载驱动
  try {
   driverName = "com.mysql.jdbc.Driver";
   dbURL = "jdbc:mysql://192.168.10.227:3306/test";
   dbName = "root";
   dbPwd = "******";
   Class.forName(driverName);
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
   throw new RuntimeException(e);
  }
 }

 //
 private static Connection getConnection() throws SQLException {
  if (connection == null || connection.isClosed()) {
   connection = DriverManager.getConnection(dbURL, dbName, dbPwd);
  }
  return connection;
 }

 private static void close(Connection connection, Statement statement) {
  try {
   if (connection != null) {
    connection.close();
   }
   if (statement != null) {
    statement.close();
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

 private static void close(ResultSet rs) {
  try {
   if (rs != null) {
    rs.close();
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

 private static List<Map<String, Object>> convert(ResultSet rs)
   throws SQLException {
  List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
  if (rs != null && !rs.isClosed()) {
   ResultSetMetaData meta = rs.getMetaData();
   int colCount = meta.getColumnCount();
   while (rs.next()) {
    Map<String, Object> map = new HashMap<String, Object>();
    for (int i = 1; i <= colCount; i++) {
     String key = meta.getColumnName(i);
     Object value = rs.getObject(i);
     map.put(key, value);
    }
    list.add(map);

   }
  }
  return list;
 }

 private static void AttachParameters(PreparedStatement statement,
   Object... values) throws Exception {
  if (values != null) {
   for (int i = 0; i < values.length; i++) {
    statement.setObject(i + 1, values[i]);
   }
  }
 }

 //

 public static int ExecuteNonQuery(String sql, Object... parameterValues)
   throws SQLException, Exception {
  PreparedStatement statement = null;
  int result = 0;
  try {
   connection = getConnection();
   statement = connection.prepareStatement(sql);
   AttachParameters(statement, parameterValues);
   result = statement.executeUpdate();
   statement.clearParameters();
  } catch (Exception e) {
   close(connection, statement);
   throw e;
  }
  return result;
 }

 public static Object ExecuteScalar(String sql, Object... parameterValues)
   throws Exception {
  ResultSet rs = null;
  Object value = null;
  try {
   rs = ExecuteResultSet(sql, parameterValues);
   if (rs != null) {
    rs.next();
    value = rs.getObject(1);
   }
  } finally {
   close(rs);
  }

  return value;
 }

 public static ResultSet ExecuteResultSet(String sql,
   Object... parameterValues) throws Exception {
  PreparedStatement statement = null;
  ResultSet rs = null;
  try {
   connection = getConnection();
   statement = connection.prepareStatement(sql);
   AttachParameters(statement, parameterValues);
   rs = statement.executeQuery();
   statement.clearParameters();
  } catch (Exception e) {
   close(connection, statement);
   throw e;
  }
  return rs;
 }

 public static List<Map<String, Object>> ExecuteList(String sql,
   Object... parameterValues) throws Exception {
  ResultSet rs = null;
  List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
  try {
   rs = ExecuteResultSet(sql, parameterValues);
   list = convert(rs);
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   close(rs);
  }
  return list;
 }

 public static ArrayList ExecuteReader(String sql, Object... parameterValues)
   throws Exception {
  ResultSet rs = null;
  ArrayList list = new ArrayList();
  try {
   rs = ExecuteResultSet(sql, parameterValues);
   ResultSetMetaData rsmd = rs.getMetaData();
   int column = rsmd.getColumnCount();
   while (rs.next()) {
    Object[] obj = new Object[column];//
    for (int i = 1; i <= column; i++) {
     obj[i - 1] = rs.getObject(i);
    }
    list.add(obj);
   }
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   close(rs);
  }
  return list;
 }

}