SqlHelper工具类封装

SQLHelper.java

 

import java.io.FileInputStream; import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
//防止 sql注入漏洞
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.Properties;  public class SQLHelper {

   // 定义需要的变量
   private static Connection ct = null;
   private static PreparedStatement ps = null;
   private static ResultSet rs = null;
   private static String driver = null;
   private static String url = null;
   private static String username = null;
   private static String password = null;
   private static Properties prop = null;
   private static CallableStatement cs = null;
   private static InputStream in = null;
   public static Connection getCt() {

      return ct;

   }

   public static PreparedStatement getPs() {

      return ps;

   }

   // 加载驱动,只需要一次

   static {

      prop = new Properties();

      try {

        // 配置信息放在src/properties下,根目录是工程目录

        // in = new FileInputStream(

        // "src/properties/dbinfo.properties");

        // 如果打jar包,根目录是src

        in = SQLHelper.class

              .getResourceAsStream("/properties/dbinfo.properties");

        prop.load(in);

        driver = prop.getProperty("driver");

        url = prop.getProperty("url");

        username = prop.getProperty("username");

        password = prop.getProperty("password");

        Class.forName(driver);

      } catch (FileNotFoundException e) {

        e.printStackTrace();

      } catch (IOException e) {

        e.printStackTrace();

      } catch (ClassNotFoundException e) {

        e.printStackTrace();

      } finally {

        try {

           in.close();

        } catch (IOException e) {

           e.printStackTrace();

           in = null;

        }

      }

   }

   // 得到链接

   private static Connection getConnection() {

      try {

        ct = DriverManager.getConnection(url, username, password);

      } catch (SQLException e) {

        e.printStackTrace();

      }

      return ct;

   }

   // update/delete/insert 单个增删改操作,不需要考虑从事务

   public static void executeUpdate(String sql, String params[]) {

      try {

        ct = getConnection();

        ps = ct.prepareStatement(sql);

        if (params != null) {

           for (int i = 0; i < params.length; i++) {

              ps.setString(i + 1, params[i]);

           }

        }

        ps.executeUpdate();
 
      } catch (SQLException e) {

        e.printStackTrace();

        throw new RuntimeException(e.getMessage());

      } finally {

        close(rs, ps, ct);

      }

   }

   // 多个 update / delete / insert 语句,考虑事务

   public static void executeUpdate(String sql[], String params[][]) {

      try {

        ct = getConnection();

        // 因为用户传入的可能是多个sql语句,设置不自动提交

        ct.setAutoCommit(false);

        for (int i = 0; i < sql.length; i++) {

           ps = ct.prepareStatement(sql[i]);

           if (params != null) {

              if (params[i] != null) {

 

                 for (int j = 0; j < params[i].length; j++) {

                    ps.setString(j + 1, params[i][j]);

                 }

              }

           }

           ps.executeUpdate();

        }

        ct.commit();

      } catch (Exception e) {

        e.printStackTrace();

        try {

           ct.rollback();

        } catch (SQLException e1) {

           e1.printStackTrace();

        }

      }
   }

   // 查询数据库

   public static ResultSet executeQuery(String sql, String params[]) {

      // 根据实际情况我们队sql语句 ?进行赋值

      try {

        ct = getConnection();

        ps = ct.prepareStatement(sql);

        if (params != null) {

           for (int i = 0; i < params.length; i++) {

              ps.setString(i + 1, params[i]);

           }

        }

        rs = ps.executeQuery();

        return rs;

      } catch (SQLException e) {

        throw new RuntimeException("查询失败");

      }

   }

   // 对查询语句进行升级
   public ArrayList<Object[]> executeQuery2(String sql, String params[]) {
      PreparedStatement preparedStatement = null;
      Connection connection = null;
      ResultSet resultSet = null;
      try {
        connection = getConnection();
        preparedStatement = connection.prepareStatement(sql);
        if (params != null) {
           for (int i = 0; i < params.length; i++) {
              ps.setString(i + 1, params[i]);
           }
        }
        resultSet = preparedStatement.executeQuery();
        ArrayList<Object[]> list = new ArrayList<Object[]>();
        ResultSetMetaData rsmd = resultSet.getMetaData();
        // 返回此 ResultSet 对象中的列数。
        int column = rsmd.getColumnCount();
        while (resultSet.next()) {
           Object obj[] = new Object[column];
           for (int i = 1; i <= column; i++) {
              obj[i - 1] = resultSet.getObject(i);
           }
           list.add(obj);
        }
        return list;
      } catch (Exception e) {
        throw new RuntimeException("查询失败" + e.getMessage());
      } finally {
        close(resultSet, preparedStatement, connection);
      }
   }

   // 调用存储过程 无返回值
   // sql {call 过程(?,?,?)}
   public static void calPro1(String sql, String params[]) {
      try {
        ct = getConnection();
        cs = ct.prepareCall(sql);
        if (params != null) {
           for (int i = 0; i < params.length; i++) {
              cs.setString(i + 1, params[i]);
           }
        }
        cs.execute();
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        close(rs, cs, ct);
      }
   }

   // 调用存储过程 有返回值
   public static CallableStatement callPro2(String sql, String[] inparameters,
        Integer[] outparameters) {
      try {
        ct = getConnection();
        cs = ct.prepareCall(sql);
        if (inparameters != null) {
           for (int i = 0; i < inparameters.length; i++) {
              cs.setObject(i + 1, inparameters[i]);
           }
        }
        // 给 out参数赋值
        if (outparameters != null) {
           for (int i = 0; i < outparameters.length; i++) {
              cs.registerOutParameter(inparameters.length + 1 + i,
                    outparameters[i]);
           }
        }
        cs.execute();
      } catch (Exception e) {
        e.printStackTrace();
        throw new RuntimeException(e.getMessage());
      } finally {
      }
      return cs;
   }

   public static void close(ResultSet rs, Statement ps, Connection ct) {
      if (rs != null) {
        try {
           rs.close();
        } catch (SQLException e) {
           e.printStackTrace();
        }
        rs = null;
      }
      if (ps != null) {
        try {
           ps.close();
        } catch (SQLException e) {
           e.printStackTrace();
        }
        ps = null;
      }
      if (null != ct) {
        try {
           ct.close();
        } catch (SQLException e) {
           e.printStackTrace();
        }
        ct = null;
      }
   }
}
配置文件 dbiofo.properties

#这是我的 oracle 配置
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
username=scott
password=tiger
#mysql 配置...
应用:
String sql1="insert into emp (empno,ename) values (1256,'康吉')";
String sql2="insert into emp (empno,ename) values (7933,'康吉')";
String sql[]={sql1,sql2};
SQLHelper.executeUpdate2(sql, null);