package BankTest;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.sql.*;
import java.util.Scanner;
public class AccountTest {
private static final String URL="jdbc:mysql://localhost:3306/Back?useUnicode=true&characterEncoding=utf-8&&useSSL=false";
private static final String USER="root";
private static final String PASSWORD="root";
private static final String DRIVER="com.mysql.jdbc.Driver";
private Connection conn=null;
static Scanner sc=new Scanner(System.in);
static {
try {
Class.forName(DRIVER);
System.out.println("mysql驱动注册成功");
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println("mysql驱动注册失败");
}
}
@Before
public void before(){
try {
conn= DriverManager.getConnection(URL,USER,PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
}
@After
public void after(){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void testInsert(){
String sql="insert into account values(?,?,?)";
try {
PreparedStatement ps=conn.prepareStatement(sql);
ps.setObject(1,"S003");
ps.setObject(2,"老詹");
ps.setObject(3,"10000.00");
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void testUpdate()throws SQLException{
System.out.println("请输入存款账户id:");
String id=sc.next();
System.out.println("请输入存款金额");
double money=sc.nextDouble();
String sql="update account set balance=balance+"+money+"where id= '" + id + "'";
System.out.println(sql);
Statement s=conn.createStatement();
int rows=s.executeUpdate(sql);
// String sql="update account set balance=balance+? where id=?";
// PreparedStatement ps=conn.prepareStatement(sql);
// ps.setObject(1,money);
// ps.setObject(2,id);
// int rows=ps.executeUpdate();
System.out.println("受影响的行数:"+rows);
}
@Test
public void testTransfer(){
System.out.println("请输入转出账户id:");
String fromId=sc.next();
System.out.println("请输入转入账户id:");
String toId=sc.next();
System.out.println("请输入转帐金额");
double money=sc.nextDouble();
try {
conn.setAutoCommit(false);//关闭自动提交=开启事务
String sql="update account set balance=balance+? where id=?";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setObject(1,(0-money));
ps.setObject(2,fromId);
ps.executeUpdate();//执行转出
//int i=1/0;//这里是模拟故障(如果没有这个故障,try能执行完,那么commit提交就可以执行,如果有,则进入catch执行rollback(回滚))
ps.setObject(1,money);
ps.setObject(2,toId);
ps.executeUpdate();//执行转入
//conn.commit();
System.out.println("是否提交?");
if (sc.nextInt()==1){
conn.commit();
}else {
conn.rollback();
}
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
@Test
public void test() throws SQLException{
System.out.println("请输入查询的账户id");
String id=sc.next();
String sql="select * from account where id= ?";
conn.setAutoCommit(false);
//隔离级别默认为4
//Connection.TRANSACTION_READ_UNCOMMITTED=1;
//Connection.TRANSACTION_READ_COMMITTED=2;
//Connection.TRANSACTION_REPEATABLE_READ=4;
//Connection.TRANSACTION_SERIALIZABLE=8;
//查看事务的默认隔离级别
System.out.println(conn.getTransactionIsolation());
//设置隔离级别是读未提交
//conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
//设置隔离级别是读已提交
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
PreparedStatement ps=conn.prepareStatement(sql);
ps.setObject(1,id);
ResultSet rs=ps.executeQuery();
while (rs.next()){
System.out.println(rs.getString("id")+"\t"
+rs.getString("name")+"\t"
+rs.getDouble("balance"));
}
System.out.println("是否再读一次?");
if (sc.nextInt()==1){
test();
}
}
}
用自己的话说一下下面四个问题