[关闭]
@duyao 2015-05-03T12:56:51.000000Z 字数 5065 阅读 1345

sqlhelper

sql codeTool

对数据库操作的工具类

  1. package com.dy.tools;
  2. import java.sql.*;
  3. import java.util.ArrayList;
  5. public class SqlTool {
  7.     // 定义变量
  8.     private static Connection ct = null;
  9.     // 大多数情况下用preparedstatement替代statement
  10.     private static PreparedStatement ps = null;
  11.     private static ResultSet rs = null;
  13.     // 连接数据库的参数
  14.     private static String url = "jdbc:sqlserver://localhost:1433;DatabaseName=mylab";
  15.     private static String username = "sa";
  16.     private static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  17.     private static String passwd = "sa";
  19.     private static CallableStatement cs = null;
  21.     public static CallableStatement getCs() {
  22.         return cs;
  23.     }
  25.     // private static Properties pp = null;
  26.     // private static InputStream fis = null;
  27.     // 加载驱动，只需要一次，用静态代码块
  28.     static {
  29.         try {
  30.             Class.forName(driver);
  31.         } catch (ClassNotFoundException e1) {
  32.             // TODO Auto-generated catch block
  33.             e1.printStackTrace();
  34.         }
  35.         // try
  36.         // {
  37.         // //从dbinfo.properties
  38.         // pp = new Properties();
  39.         // fis=SqlHelper.class.getClassLoader().getResourceAsStream("mysql.properties");
  40.         // //fis = new FileInputStream();
  41.         // pp.load(fis);
  42.         // url = pp.getProperty("url");
  43.         // username = pp.getProperty("username");
  44.         // driver = pp.getProperty("driver");
  45.         // passwd = pp.getProperty("passwd");
  46.         // Class.forName(driver);
  47.         //              
  48.         // }
  49.         // catch (Exception e)
  50.         // {
  51.         // e.printStackTrace();
  52.         // }
  53.         // finally
  54.         // {
  55.         // try
  56.         // { fis.close();}
  57.         // catch(IOException e) {e.printStackTrace();}
  58.         // fis = null;//垃圾回收站上收拾
  59.         // }
  61.     }
  63.     // 得到连接
  64.     public static Connection getConnection() {
  65.         try {
  66.             ct = DriverManager.getConnection(url, username, passwd);
  67.         } catch (Exception e) {
  68.             e.printStackTrace();
  69.         }
  70.         return ct;
  71.     }
  73.     // *************callPro1存储过程函数1*************
  74.     public static CallableStatement callPro1(String sql, String[] parameters) {
  75.         try {
  76.             ct = getConnection();
  77.             cs = ct.prepareCall(sql);
  78.             if (parameters != null) {
  79.                 for (int i = 0; i < parameters.length; i++) {
  80.                     cs.setObject(i + 1, parameters[i]);
  81.                 }
  82.             }
  83.             cs.execute();
  84.         } catch (Exception e) {
  85.             e.printStackTrace();
  86.             throw new RuntimeException(e.getMessage());
  87.         } finally {
  88.             close(rs, cs, ct);
  89.         }
  90.         return cs;
  91.     }
  93.     // *******************callpro2存储过程2************************
  94.     public static CallableStatement callPro2(String sql, String[] inparameters,
  95.             Integer[] outparameters) {
  96.         try {
  97.             ct = getConnection();
  98.             cs = ct.prepareCall(sql);
  99.             if (inparameters != null) {
  100.                 for (int i = 0; i < inparameters.length; i++) {
  101.                     cs.setObject(i + 1, inparameters[i]);
  102.                 }
  103.             }
  104.             // cs.registerOutparameter(2,oracle.jdbc.OracleTypes.CURSOR);
  105.             if (outparameters != null) {
  106.                 for (int i = 0; i < outparameters.length; i++) {
  107.                     cs.registerOutParameter(inparameters.length + 1 + i,
  108.                             outparameters[i]);
  109.                 }
  110.             }
  111.             cs.execute();
  112.         } catch (Exception e) {
  113.             e.printStackTrace();
  114.             throw new RuntimeException(e.getMessage());
  115.         } finally {
  117.         }
  118.         return cs;
  119.     }
  121.     public static ResultSet executeQuery(String sql, String[] parameters) {
  122.         try {
  123.             ct = getConnection();
  124.             ps = ct.prepareStatement(sql);
  125.             if (parameters != null) {
  126.                 for (int i = 0; i < parameters.length; i++) {
  127.                     ps.setString(i + 1, parameters[i]);
  128.                 }
  129.             }
  130.             rs = ps.executeQuery();
  131.         } catch (Exception e) {
  132.             e.printStackTrace();
  133.             throw new RuntimeException(e.getMessage());
  134.         } finally {
  136.         }
  137.         return rs;
  138.     }
  139.     //查询的增强版，这里可以及时关闭resultset，返回arraylist
  140.     public static ArrayList executeQuery1(String sql, String[] parameters) {
  141.         try {
  142.             ct = getConnection();
  143.             ps = ct.prepareStatement(sql);
  144.             if (parameters != null) {
  145.                 for (int i = 0; i < parameters.length; i++) {
  146.                     ps.setString(i + 1, parameters[i]);
  147.                 }
  148.             }
  149.             rs = ps.executeQuery();
  150.             ResultSetMetaData resultSetMetaData=rs.getMetaData();
  151.             //得到列数，下面的循环将每一行的数据作为对象放入list中
  152.             int column=resultSetMetaData.getColumnCount();
  153.             ArrayList  arrayList =new ArrayList();
  154.             while(rs.next()){
  155.                 //rs是一行，objects存放每列的对象
  156.                 Object [] objects=new Object[column];
  157.                 //对象个数是列数
  158.                 for(int i=1;i<=column;i++){
  159.                     objects[i-1]=rs.getObject(i);
  160.                 }
  161.                 //得到全部列的对象，放入arraylist中
  162.                 arrayList.add(objects);
  164.             }
  165.             return arrayList;
  166.         } catch (Exception e) {
  167.             e.printStackTrace();
  168.             throw new RuntimeException(e.getMessage());
  169.         } finally {
  170.             close(rs, ps, ct);
  172.         }
  174.     }
  177.     public static Connection getCt() {
  178.         return ct;
  179.     }
  181.     public static PreparedStatement getPs() {
  182.         return ps;
  183.     }
  185.     public static ResultSet getRs() {
  186.         return rs;
  187.     }
  188.     //执行多条语句
  189.     public static void executeUpdate2(String[] sql, String[][] parameters) {
  190.         try {
  191.             ct = getConnection();
  192.             ct.setAutoCommit(false);
  194.             for (int i = 0; i < sql.length; i++) {
  196.                 if (null != parameters[i]) {
  197.                     ps = ct.prepareStatement(sql[i]);
  198.                     for (int j = 0; j < parameters[i].length; j++) {
  199.                         ps.setString(j + 1, parameters[i][j]);
  200.                     }
  201.                     ps.executeUpdate();
  202.                 }
  204.             }
  206.             ct.commit();
  208.         } catch (Exception e) {
  209.             e.printStackTrace();
  210.             try {
  211.                 ct.rollback();
  212.             } catch (SQLException e1) {
  213.                 e1.printStackTrace();
  214.             }
  215.             throw new RuntimeException(e.getMessage());
  216.         } finally {
  217.             close(rs, ps, ct);
  218.         }
  220.     }
  222.     // 先写一个update、delete、insert
  223.     // sql格式：update 表名 set 字段名 =？where 字段=？
  224.     // parameter神应该是（”abc“,23）
  225.     public static boolean executeUpdate(String sql, String[] parameters) {
  226.         boolean b=true;
  227.         try {
  228.             ct = getConnection();
  229.             ps = ct.prepareStatement(sql);
  230.             if (parameters != null) {
  231.                 for (int i = 0; i < parameters.length; i++) {
  232.                     ps.setString(i + 1, parameters[i]);
  233.                 }
  235.             }
  236.             ps.executeUpdate();
  237.         } catch (Exception e) {
  238.             b=false;
  239.             e.printStackTrace();// 开发阶段
  240.             // 抛出异常
  241.             // 可以处理，也可以不处理
  242.             throw new RuntimeException(e.getMessage());
  243.         } finally {
  244.             close(rs, ps, ct);
  245.         }
  246.         return b;
  247.     }
  249.     public static void close(ResultSet rs, Statement ps, Connection ct) {
  250.         // 关闭资源(先开后关)
  251.         if (rs != null) {
  252.             try {
  253.                 rs.close();
  254.             } catch (SQLException e) {
  255.                 e.printStackTrace();
  256.             }
  257.             rs = null;
  258.         }
  259.         if (ps != null) {
  260.             try {
  261.                 ps.close();
  262.             } catch (SQLException e) {
  263.                 e.printStackTrace();
  264.             }
  265.             ps = null;
  266.         }
  267.         if (null != ct) {
  268.             try {
  269.                 ct.close();
  270.             } catch (SQLException e) {
  271.                 e.printStackTrace();
  272.             }
  273.             ct = null;
  274.         }
  275.     }
  278. }

通过executeQuery1查询到的数据返回的是ArrayList，这里用get方法得到的是Object []，因此先转Object [],再转成所需要的int等

  1. ArrayList aList=SqlHelper.executeQuery1(sql, parameters);
  2. //先转成Object[]
  3. Object[] objects=(Object[]) aList.get(0);
  4. //再转成String
  5. String nameString=(String) objects[0];
添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注