JAVA与数据库连接--存款练习

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();
        }
    }
}

 

用自己的话说一下下面四个问题