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语句都不执行。
转载于:https://blog.51cto.com/shamrock/1532489