PostgreSql？JDBC事务操作方法详解

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import java.sql.*;
@SpringBootTest
class JdbcTrasationTests {
private final String url=”jdbc:p6spy:postgresql://localhost:5432/postgres”;
private final String user=”postgres”;
private final String password=”112233″;

public Connection connect() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
@Test
void testTrasation() {
ProRank proRank=new ProRank();
proRank.setLine(“Mid”);
proRank.setName(“Faker”);
proRank.setTeam(“T1″);
proRank.setRank(0);
//调用
addProAndUpdateRank(proRank,2222);
}

private void close(AutoCloseable closeable) {
try {
if (closeable !=null) {
closeable.close();
}
} catch (Exception e) {
System.out.println(e.getMessage());
}
}

public void addProAndUpdateRank(ProRank proRank, Integer rank) {
Connection conn=null;
PreparedStatement pstmt=null;
PreparedStatement pstmt2=null;
ResultSet rs=null;
// 插入一条数据
String SQL=”INSERT INTO pro_rank(name,team,line,rank) VALUES(?,?,?,?)”;
// 更新他的rank值
String SQLUpdateRank=”UPDATE pro_rank SET rank=? WHERE id=?;”;
int id=0;
try {
// 链接数据库
conn=connect();
conn.setAutoCommit(false);
// 插入一条数据
pstmt=conn.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
pstmt.setString(1, proRank.getName());
pstmt.setString(2, proRank.getTeam());
pstmt.setString(3, proRank.getLine());
pstmt.setInt(4, proRank.getRank());
int affectedRows=pstmt.executeUpdate();
// 判断是否生效
if (affectedRows > 0) {
// 获取返回的id
rs=pstmt.getGeneratedKeys();
if (rs.next()) {
id=rs.getInt(1);
if (id > 0) {
pstmt2=conn.prepareStatement(SQLUpdateRank);
pstmt2.setInt(2, id);
pstmt2.setInt(1, rank);
pstmt2.executeUpdate();
}
}
} else {
// 如果新增数据失败，回滚
conn.rollback();
}
// 提交事务
conn.commit();
System.out.println(“插入选手数据成功！更新选手rank成功，数据id：” + id);
} catch (SQLException sqlException) {
System.out.println(sqlException.getMessage());
sqlException.printStackTrace();
// 回滚事务
System.out.println(“回滚事务…”);
try {
if (conn !=null) {
conn.rollback();
}
} catch (SQLException e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
} finally {
close(rs);close(pstmt);close(pstmt2);close(conn);
}
}
}