实现增删该查的jdbc封装
import java.io.IOException; import java.io.InputStream; 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; import java.util.Properties; public class DbUtil { private static String user = null; private static String password = null; private static String driver = null; private static String url = null; private static Connection conn = null; private static Properties p =null; //单利模式 --懒汉式(双重锁定)保证线程的安全性 public static DbUtil db = null; private DbUtil(){ } public static DbUtil getInstance(){ if(db == null){ synchronized(DbUtil.class){ if(db == null){ db = new DbUtil(); } } } return db; } //读取配置文件且加载数据库驱动 static{ //实例化一个properties对象用来解析我们的配置文件 p = new Properties(); //通过类加载器来读取我们的配置文件,以字节流的形式读取 InputStream in = DbUtil.class.getClassLoader().getResourceAsStream("/config.properties"); try { //将配置文件自如到Propreties对象,来进行解析 p.load(in); //读取配置文件 driver = p.getProperty("driver"); url = p.getProperty("url"); user = p.getProperty("user"); password = p.getProperty("password"); //加载驱动 Class.forName(driver); } catch (IOException e1) { e1.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } //建立数据库的连接 public Connection getConn(){ try { return DriverManager.getConnection(url, user, password); } catch (SQLException e) { e.printStackTrace(); return null; } } //查询返回List容器 public List<Map<String,Object>> query(String sql,Object...params){ PreparedStatement pst = null; ResultSet rs = null; try { //获得连接 conn = getConn(); //获得preparedSttement对象进行预编译(?占位符) pst = conn.prepareStatement(sql); int paramsIndex = 1; for(Object p : params){ pst.setObject(paramsIndex++, p); } //执行sql语句获得结果集的对象 rs = pst.executeQuery(); //获得结果集中列的信息 ResultSetMetaData rst = rs.getMetaData(); //获得结果集的列的数量 int column = rst.getColumnCount(); //创建List容器 List<Map<String,Object>> rstList = new ArrayList<Map<String,Object>>(); //处理结果 while(rs.next()){ //创建Map容器存取每一列对应的值 Map<String,Object> m = new HashMap<String,Object>(); for(int i=1;i<=column;i++){ m.put(rst.getColumnName(i), rs.getObject(i)); } //将Map容器放入List容器中 rstList.add(m); } return rstList; } catch (SQLException e) { e.printStackTrace(); return null; }finally{ //关闭资源 close(rs, pst, conn); } } public List<Map<String,Object>> query(String sql,List<Object> params){ PreparedStatement pst = null; ResultSet rs = null; try { //获得连接 conn = getConn(); //获得preparedSttement对象进行预编译(?占位符) pst = conn.prepareStatement(sql); int paramsIndex = 1; for(Object p : params){ pst.setObject(paramsIndex++, p); } //执行sql语句获得结果集的对象 rs = pst.executeQuery(); //获得结果集中列的信息 ResultSetMetaData rst = rs.getMetaData(); //获得结果集的列的数量 int column = rst.getColumnCount(); //创建List容器 List<Map<String,Object>> rstList = new ArrayList<Map<String,Object>>(); //处理结果 while(rs.next()){ //创建Map容器存取每一列对应的值 Map<String,Object> m = new HashMap<String,Object>(); for(int i=1;i<=column;i++){ m.put(rst.getColumnName(i), rs.getObject(i)); } //将Map容器放入List容器中 rstList.add(m); } return rstList; } catch (SQLException e) { e.printStackTrace(); return null; }finally{ //关闭资源 close(rs, pst, conn); } } //分页查询总共有多少条记录totleSize public long queryLong(String sql,Object...params){ PreparedStatement pst = null; ResultSet rs = null; try { //获得连接 conn = getConn(); //获得preparedSttement对象进行预编译(?占位符) pst = conn.prepareStatement(sql); int paramsIndex = 1; for(Object p : params){ pst.setObject(paramsIndex++, p); } //执行sql语句获得结果集的对象 rs = pst.executeQuery(); while(rs.next()){ return Long.valueOf(rs.getLong(1)); } return 0; } catch (SQLException e) { e.printStackTrace(); return 0; } } //插入 public boolean insert(String sql,Object...params){ PreparedStatement pst = null; ResultSet rs = null; try { //获得连接 conn = getConn(); //获得PrepareStatement对象进行预编译 pst = conn.prepareStatement(sql); //处理将数据插入占位符 int paramsIndex = 1; for(Object p : params){ pst.setObject(paramsIndex++, p); } //执行sql语句 pst.executeUpdate(); return true; } catch (SQLException e) { e.printStackTrace(); return false; }finally{ //关闭资源 close(null, pst, conn); } } //修改 public boolean update(String sql,Object...params){ PreparedStatement pst = null; ResultSet rs = null; try { //获得连接 conn = getConn(); //获得PrepareStatement对象进行预编译 pst = conn.prepareStatement(sql); //处理将数据插入占位符 int paramsIndex = 1; for(Object p : params){ pst.setObject(paramsIndex++, p); } //执行sql语句 pst.executeUpdate(); return true; } catch (SQLException e) { e.printStackTrace(); return false; }finally{ //关闭资源 close(null, pst, conn); } } //删除 public boolean delete(String sql,Object...params){ PreparedStatement pst = null; ResultSet rs = null; try { //获得连接 conn = getConn(); //获得PrepareStatement对象进行预编译 pst = conn.prepareStatement(sql); //处理将数据插入占位符 int paramsIndex = 1; for(Object p : params){ pst.setObject(paramsIndex++, p); } //执行sql语句 pst.executeUpdate(); return true; } catch (SQLException e) { e.printStackTrace(); return false; }finally{ //关闭资源 close(null, pst, conn); } } //关闭资源 public static void close(ResultSet rs,PreparedStatement pst,Connection conn){ if(rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } rs = null; } if(pst!=null){ try { pst.close(); } catch (SQLException e) { e.printStackTrace(); } pst = null; } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } conn = null; } } }
配置文件config.properties:
user=root password=root driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/cp_data