JDBC的简单封装使用,Oracle方向
driver=oracle.jdbc.driver.OracleDriver url=jdbc:oracle:thin:@localhost:1521:XE user=briup password=briup
配置文件 jdbc.properties
package com.briup.driver.form.Dao; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; //封装配置文件,注册,连接,关闭资源 //注意事项: //(1)配置类路径(2)异常处理 public class ConnectionFactory { private static String driver; private static String url; private static String user; private static String password; static Connection conn; // 配置信息 static { Properties p = new Properties(); try { p.load(ConnectionFactory.class.getResourceAsStream("jdbc.properties")); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } driver = p.getProperty("driver"); System.out.println(driver); url = p.getProperty("url"); user = p.getProperty("user"); password = p.getProperty("password"); } // 注册 public static Connection getConnection() { // 1.注册驱动 try { Class.forName(driver); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } // 2.建立连接 try { conn = DriverManager.getConnection(url, user, password); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return conn; } // 关闭资源 // PreparedStatement继承自Statement public static void close(Connection conn, Statement stmt) { close(null, conn, stmt); } public static void close(ResultSet rs, Connection conn, Statement stmt) { try { if (rs != null) rs.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } try { if (stmt != null) stmt.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } try { if (conn != null) conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } // public static void main(String[] args) {} }
package com.briup.driver.form.Dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; //封装工具类 public class JDBCUtil { public static void execute_DML_stmt(String sql) { Connection conn = null; Statement stmt = null; try { conn = ConnectionFactory.getConnection(); stmt = conn.createStatement(); stmt.execute(sql); System.out.println("操作完成"); ConnectionFactory.close(conn, stmt); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } // select,需要处理结果集 public static void executeQuery_select_stmt(String sql, IWorkAdapter work) { Connection conn = null; Statement stmt = null; try { conn = ConnectionFactory.getConnection(); stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); // 处理结果集 work.processRs(rs); ConnectionFactory.close(conn, stmt); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static int executeUpdate_DML_ps(String sql, IWorkAdapter work) { Connection conn = null; PreparedStatement ps = null; int change = 0; try { conn = ConnectionFactory.getConnection(); ps = conn.prepareStatement(sql); work.setValues(ps); change = ps.executeUpdate(); // ps.executeBatch(); ConnectionFactory.close(conn, ps); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return change; } public static void executeBatch_DML_ps(String sql, IWorkAdapter work) { Connection conn = null; PreparedStatement ps = null; try { conn = ConnectionFactory.getConnection(); ps = conn.prepareStatement(sql); work.setValues(ps); ps.executeBatch(); // ps.executeBatch(); ConnectionFactory.close(conn, ps); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } // select,prepareStatement方法 public static Object executeQuery_select_ps(String sql, IWorkAdapter work) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; Object object = null; try { conn = ConnectionFactory.getConnection(); // 预定义 ps = conn.prepareStatement(sql); // 设置参数 work.setValues(ps); rs = ps.executeQuery(); object = work.processRs(rs); ConnectionFactory.close(conn, ps); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return object; } }
package com.briup.driver.form.Dao; import java.sql.PreparedStatement; import java.sql.ResultSet; public interface IWork { // 处理结果集 public Object processRs(ResultSet rs); // PreparedStatement预定义后设置参数 public void setValues(PreparedStatement ps); }
package com.briup.driver.form.Dao; import java.sql.PreparedStatement; import java.sql.ResultSet; public class IWorkAdapter implements IWork { @Override public Object processRs(ResultSet rs) { return null; // TODO Auto-generated method stub } @Override public void setValues(PreparedStatement ps) { // TODO Auto-generated method stub } }
package com.briup.driver.form.Dao; //缺陷事务都是自动提交的,在增删改查操作的顺序可能会有一定的变化 //根据需求改变代码 import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.briup.driver.Student; public class JDBCFunction { // 插入,PreparedStatement 预定义插入 public static void insert(Student s) { String sql = "insert into t_student values(?,?,?)"; JDBCUtil.executeUpdate_DML_ps(sql, new IWorkAdapter() { @Override public void setValues(PreparedStatement ps) { try { ps.setInt(1, s.getId()); ps.setString(2, s.getName()); ps.setInt(3, s.getAge()); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }); System.out.println("插入成功"); } // 改 根据id public static void update(Student s) { String sql = "update t_student set name= ? where id = ?"; int change = JDBCUtil.executeUpdate_DML_ps(sql, new IWorkAdapter() { @Override public void setValues(PreparedStatement ps) { try { ps.setString(1, "fys"); ps.setInt(2, s.getId()); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }); if (change == 0) { System.out.println("修改的数据不存在"); } else { System.out.println("修改成功"); } } // DML删除,根据id public static void delete(Student s) { String sql = "delete from t_student where id =?"; int change = JDBCUtil.executeUpdate_DML_ps(sql, new IWorkAdapter() { @Override public void setValues(PreparedStatement ps) { try { ps.setInt(1, s.getId()); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }); if (change == 0) { System.out.println("删除的数据不存在"); } else { System.out.println("删除成功"); } } // 查,根据id public static Student select(Student s) { String sqlS = "select * from t_student where id=?"; Object object = JDBCUtil.executeQuery_select_ps(sqlS, new IWorkAdapter() { @Override public void setValues(PreparedStatement ps) { try { ps.setInt(1, s.getId()); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } @Override public Student processRs(ResultSet rs) { Student s1 = null; try { while (rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); int age = rs.getInt("age"); s1 = new Student(id, name, age); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return s1; } }); if (object == null) { System.out.println("查找的数据不存在"); return null; } else { return (Student) object; } } // 插入批处理 public static void insertBatch(List<Student> list) { String sqlI = "insert into t_student values(?,?,?)"; JDBCUtil.executeBatch_DML_ps(sqlI, new IWorkAdapter() { @Override public void setValues(PreparedStatement ps) { try { for (int i = 0; i < list.size(); i++) { ps.setLong(1, list.get(i).getId()); ps.setString(2, list.get(i).getName()); ps.setInt(3, list.get(i).getAge()); ps.addBatch(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }); System.out.println("插入完毕"); } // 插入批删除 public static void deleteBatch(List<Student> list) { String sqlI = "delete from t_student where id =?"; JDBCUtil.executeBatch_DML_ps(sqlI, new IWorkAdapter() { @Override public void setValues(PreparedStatement ps) { try { for (int i = 0; i < list.size(); i++) { ps.setInt(1, list.get(i).getId()); ps.addBatch(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }); System.out.println("删除完毕"); } }