public class DBCon { // 数据库驱动对象 public static final String DRIVER = "oracle.jdbc.driver.OracleDriver"; // 数据库连接地址(数据库名) public static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl"; // 登陆名 public static final String USER = "scott"; // 登陆密码 public static final String PWD = "123456"; // 创建数据库连接对象 private Connection con = null; // 创建数据库预编译对象 private PreparedStatement ps = null; // 创建结果集 private ResultSet rs = null; // 创建数据源对象 public static DataSource source = null; public Connection getCon() { try{ Class.forName(DRIVER); con = DriverManager.getConnection(URL, USER, PWD); System.out.println("数据库连接成功!"); } catch (Exception e) { System.err.println("数据库连接失败!"); e.printStackTrace(); } return con; } public void closeAll() { if (rs != null) try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } if (ps != null) try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } if (con != null) try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } public int update(String sql,String... pras){ int resu=0; con=getCon(); try { ps=con.prepareStatement(sql); for(int i=0;i<pras.length;i++){ ps.setString(i+1,pras[i]); } resu=ps.executeUpdate(); } catch (SQLException e) { } finally{ closeAll(); } return resu; } public ResultSet query(String sql,String... pras){ con=getCon(); try { ps=con.prepareStatement(sql); if(pras!=null) for(int i=0;i<pras.length;i++){ ps.setString(i+1, pras[i]); } rs=ps.executeQuery(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return rs; } public static void main(String[] args) throws SQLException { DBCon dbCon = new DBCon(); // dbCon.getCon(); ResultSet resultSet = dbCon.query("select * from emp", null); while (resultSet.next()) { System.out.printf("%-10d",resultSet.getInt(1)); //输出10列,左对齐(-号表示左对齐) // System.out.print(resultSet.getInt(1) + "\t\t"); System.out.printf("%-10s",resultSet.getString(2)); // System.out.print(resultSet.getString(2) + "\t\t"); System.out.printf("%-20s",resultSet.getString(3)); // System.out.print(resultSet.getString(3) + "\t\t\t\t"); System.out.printf("%-10d",resultSet.getInt(4)); // System.out.print(resultSet.getInt(4) + "\t\t"); System.out.print(resultSet.getDate(5) + "\t\t"); System.out.print(resultSet.getDouble(6) + "\t\t"); System.out.print(resultSet.getDouble(7) + "\t\t"); System.out.println(resultSet.getInt(8)); } dbCon.closeAll(); } }
Jar包下载 http://yunpan.cn/QC5IeekKJvixN 提取码 243b
Java操作Oracle实现事务回滚
DBCon dbCon = new DBCon(); Connection connection = dbCon.getCon(); String sql = ""; Statement statement = null; try { connection.setAutoCommit(false);//设置事物不自动提交 sql = "update emp set sal = sal - 100 where ename = 'SMITH'"; statement = connection.createStatement(); statement.executeUpdate(sql); int i = 7/0; //抛异常。 sql = "update emp set sal = sal - 100 where ename = 'JAMES'"; statement.executeUpdate(sql); connection.commit(); } catch (Exception e) { //注意这里如果是SQLException,上面7/0抛出的异常就不会被这里捕获到,下面的rollback自然就不会执行。陷阱 try { connection.rollback();//事务回滚 } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); }finally{ dbCon.closeAll(); if (statement != null) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } }
默认情况下事物自动提交,此时上例中抛出异常后,前面的sql语句可以执行成功,后面的不会被执行。进行事物管理之后,上例中抛出异常后,事物会在下面的catch块中回滚。保证两个sql语句都不执行。