调用工具类Jdbc实现增删改查
添加功能
import com.sxt.util.JdbcUtil;
public class Test1 {
public static void main(String[] args) {
String sql="insert into dept values(?,?,?)";
Object[] params={ 43,"测试部","武汉"};
int i = JdbcUtil.executeUpdate(sql, params);
if(i>0){
System.out.println("添加部门成功");
}else{
System.out.println("添加部门失败");
}
}
}
修改功能
import com.sxt.util.JdbcUtil;
public class Test2 {
public static void main(String[] args) {
String sql="update dept set dname=?,loc=? where deptno=?";
int i = JdbcUtil.executeUpdate(sql,"测试2部","南京", 43);
if(i>0){
System.out.println("修改部门成功");
}else{
System.out.println("修改部门失败");
}
}
}
删除功能
import com.sxt.util.JdbcUtil;
public class Test3 {
public static void main(String[] args) {
String sql="delete from dept where deptno=?";
int i = JdbcUtil.executeUpdate(sql, 43);
if(i>0){
System.out.println("删除部门成功");
}else{
System.out.println("删除部门失败");
}
}
}
Util工具包
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcUtil {
private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
private static String user = "scott";
private static String password = "tiger";
static {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void closeAll(ResultSet rs, Statement stmt, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static int executeUpdate(String sql, Object... params) {
int count = 0;
Connection conn = JdbcUtil.getConnection();
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
stmt.setObject(i + 1, params[i]);
}
count = stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.closeAll(null, stmt, conn);
}
return count;
}
}
转账操作
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.sxt.util.JdbcUtil;
public class AccountTest {
public static void main(String[] args) {
String sql1 = "update taccount set balance=balance-100 where aid=1";
String sql2 = "update taccount set balance=balance+100 where aid=2";
Connection conn=JdbcUtil.getConnection();
PreparedStatement stmt=null;
try {
conn.setAutoCommit(false);
stmt=conn.prepareStatement(sql1);
stmt.executeUpdate();
stmt.close();
System.out.println(10/0);
stmt=conn.prepareStatement(sql2);
stmt.executeUpdate();
conn.commit();
} catch (Exception e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
JdbcUtil.closeAll(null, stmt, conn);
}
}
}