sqlHelper用于操作数据对象(从韩顺平老师教学视频中总结而来).docx

package com.zz.utils;import java.io.InputStream; /导入java.io.InputStream类import java.sql.*; /导入java.sql包中的所有类import java.util.ArrayList;import java.util.Properties; /导入java.util.Properties类/* * * author administrator */public class ConnDB private static final ArrayList al = null;private static Connection conn = null; / 声明Connection对象的实例private static PreparedStatement stmt = null; / 声明Statement对象的实例private static ResultSet rs = null; / 声明ResultSet对象的实例private static String propFileName = connDB.properties; / 指定资源文件保存的位置private static Properties prop = new Properties(); / 创建并实例化Properties对象的实例private static String dbClassName = com.mysql.jdbc.Driver;/定义保存数据库驱动的变量private static String dbUrl = jdbc:mysql:/:3306/database?user=root&password=root&useUnicode=true;public ConnDB() /定义构造方法try /捕捉异常/将Properties文件读取到InputStream对象中InputStream in = getClass().getResourceAsStream(propFileName);prop.load(in); / 通过输入流对象加载Properties文件dbClassName = prop.getProperty(DB_CLASS_NAME); / 获取数据库驱动dbUrl = prop.getProperty(DB_URL, dbUrl);/获取URL catch (Exception e) e.printStackTrace(); / 输出异常信息public static Connection getConnection() Connection conn = null;try /连接数据库时可能发生异常因此需要捕捉该异常Class.forName(dbClassName).newInstance();/装载数据库驱动/建立与数据库URL中定义的数据库的连接conn = DriverManager.getConnection(dbUrl); catch (Exception ee) ee.printStackTrace();/输出异常信息if (conn = null) System.err.println(警告: ConnDB.getConnection() 获得数据库链接失败.rn链接类型:+ dbClassName+ rn链接位置:+ dbUrl);/在控制台上输出提示信息return conn;/返回数据库连接对象/* * 功能:执行更新操作 */public static void excuteUpdate(String sql ,String parameters) try / 捕捉异常conn = getConnection(); / 调用getConnection()方法构造Connection对象的一个实例connstmt=conn.prepareStatement(sql);System.out.println(到达这里离);/给问号赋值if(parameters!=null)for(int i=0;iparameters.length;i+)stmt.setString(i+1, parametersi);/执行stmt.executeUpdate(); catch (SQLException ex) System.err.println(ex.getMessage(); / 输出异常信息throw new RuntimeException(ex.getMessage();/finally/* * * * */public static ArrayList executeQuery2(String sql,String parameters)try conn=getConnection();stmt=conn.prepareStatement(sql);if(parameters!=null & !sql.isEmpty()for(int i=0;iparameters.length;i+)stmt.setString(i+1, parametersi);rs=stmt.executeQuery(); ArrayList al=new ArrayList(); ResultSetMetaData rsmd=rs.getMetaData(); int column =rsmd.getColumnCount(); while(rs.next() Object ob=new Objectcolumn; for(int i=1;i=column;i+) obi-1=rs.getObject(i); al.add(ob); return al; catch (Exception e) System.err.println(e.getMessage(); / 输出异常信息throw new RuntimeException(e.getMessage();/finally close();/* * 功能：执行查询语句 */public static ResultSet executeQuery(String sql,String parameters)try conn=getConnection();stmt=conn.prepareStatement(sql);if(parameters!=null & !sql.isEmpty()for(int i=0;iparameters.length;i+)stmt.setString(i+1, parametersi);rs=stmt.executeQuery(); catch (Exception e) System.err.println(e.getMessage(); / 输出异常信息throw new RuntimeException(e.getMessage();/return rs;/如果有多个update、delete、insertpublic static void excuteUpdate2(String sql ,String parameters) try /核心/1、获得链接conn=getConnection();/用户可能创建多个sql语句conn.setAutoCommit(false);for(int i=0;isql.length;i+)if(parametersi!=null)stmt=conn.prepareStatement(sqli);for(int j=0;jparametersi.length;j+)stmt.setString(j+1,parametersij); stmt.executeUpdate(); mit(); catch (Exception e) e.printStackTrace();throw new RuntimeException(e.getMessage();/ TODO: handle exception/* * 功能:关闭数据库的连接 */public static void close() try / 捕捉异常if (rs != null) / 当ResultSet对象的实例rs不为空时rs.close(); / 关闭ResultSet对象if (stmt != null) / 当