JDBC连接工具

JDBC连接工具

JDBCUtils

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.Arrays;

public class JDBCUtils {

  private JDBCUtils() {
  }

  public static Connection getConnection() {
    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url = "jdbc:mysql://localhost:3306/user?useSSL=false";
      String user = "root";
      String password = "root";
      Connection conn =
          DriverManager.getConnection(url, user, password);
      return conn;
    } catch (Exception e) {
      e.printStackTrace();
    }

    return null;
  }

  public static void close(Connection conn, Statement st, ResultSet rs) {
    if (rs != null) {
      try {
        rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        rs = null;
      }
    }
    if (st != null) {
      try {
        st.close();
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        st = null;
      }
    }
    if (conn != null) {
      try {
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        conn = null;
      }
    }

  }


  /**
   * @param sql      执行的SQL
   * @param valueArr 占位符数组
   * @return int executeUpdate?(String sql)
   * 执行给定的SQL语句，这可能是 INSERT ， UPDATE ，或 DELETE语句，或者不返回任何内容，如SQL DDL语句的SQL语句。
   */
  public static int addOrUpdate(String sql, String[] valueArr) {
    int ret = 0;

    try {
      //链接
      Connection connection = JDBCUtils.getConnection();
      //生成PreparedStatement容器对象
      PreparedStatement stm = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_UPDATABLE);

      //有占位符的？的要先赋值
      if (valueArr != null && valueArr.length > 0) {
        for (int i = 0; i < valueArr.length; i++) {
          stm.setString(i + 1, valueArr[i]);
        }
      }

      //执行更新语句，获取结果
      ret = stm.executeUpdate();
      //关闭操作
      close(connection, stm, null);


    } catch (SQLException e) {
      e.printStackTrace();
    }

    return ret;
  }


  /**
   * 查询当前的个数
   *
   * @param sql
   * @param valueArr
   * @return
   */
  public static int queryCount(String sql, String[] valueArr) {

    //数据库链接上
    Connection connection = JDBCUtils.getConnection();
    int n = 0;//查询的条数
    try {
      PreparedStatement stmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_UPDATABLE);
      //有占位符的？的要先赋值
      if (valueArr != null && valueArr.length > 0) {
        for (int i = 0; i < valueArr.length; i++) {
          stmt.setString(i + 1, valueArr[i]);
        }
      }

      //执行sql语句，返回执行结果
      ResultSet rs = stmt.executeQuery();

      //处理结果
      //结果集的行的动态获取大小
      rs.last();
      n = rs.getRow();

      //关闭资源
      close(connection, stmt, rs);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return n;
  }


  /**
   * @param sql
   * @param arrValue 有占位符
   * @return
   */
  public static Object[][] queryList(String sql, String[] arrValue) {
    ArrayList<Object> retList = new ArrayList<>();
    Object[][] data = null;
    //数据库链接上
    Connection connection = JDBCUtils.getConnection();
    try {
      //生成PreparedStatement容器对象
      PreparedStatement stmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_UPDATABLE);

      //有占位符的？的要先赋值
      if (arrValue != null && arrValue.length > 0) {
        for (int i = 0; i < arrValue.length; i++) {
          stmt.setString(i + 1, arrValue[i]);
        }
      }

      //执行sql语句，返回执行结果
      ResultSet rs = stmt.executeQuery();

      //处理结果
      //结果集的行的动态获取大小
      rs.last();
      int n = rs.getRow();

      //结果集列的动态获取大小
      ResultSetMetaData rsMetaData = rs.getMetaData();
      int m = rsMetaData.getColumnCount();
      data = new Object[n][m];

      rs.beforeFirst();//移到第一行之前
      int i = 0;
      while (rs.next()) {//顺序查询
        for (int j = 0; j < m; j++) {
          data[i][j] = rs.getString(j + 1);
        }
        i++;
      }

      //关闭资源
      close(connection, stmt, rs);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return data;
  }


  public static ResultSet getResultSet(String sql) {
    Connection connection = getConnection();
    //生成PreparedStatement容器对象
    try {
      PreparedStatement stmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_UPDATABLE);
      return stmt.executeQuery(sql);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return null;
  }


  public static void main(String[] args) {
    Object[][] objects = queryList("select * from user", null);
    System.out.println(objects);
    for (int i = 0; i < objects.length; i++) {
      System.out.println(Arrays.toString(objects[i]));
    }

  }
}