JAVA实验3：Java-MySQL实现银行转账系统

运行结果

实验中解决了用户登录时SQL注入问题，提高了系统的安全性
并且关闭了事务自动提交，开启了事务回滚功能，保证了用户资金的安全性

PowerDesigner16.5建模

数据库源码

drop table if exists userlogin;

/*==============================================================*/
/* Table: userlogin                                             */
/*==============================================================*/
create table userlogin
(
   username             varchar(20) not null,
   password             varchar(20),
   primary key (username)
);

drop table if exists userstate;

/*==============================================================*/
/* Table: userstate                                             */
/*==============================================================*/
create table userstate
(
   username             varchar(20) not null,
   money                bigint,
   primary key (username)
);

insert into userlogin(username,password) values('zrs','333');
insert into userlogin(username,password) values('llf','222');
insert into userstate(username,money) values('zrs',20000);
insert into userstate(username,money) values('llf',0);

使用的java模块

java源码

Bank主函数入口：

package Bank;

import Mysql.Mysql;
import InitUI.InitUI;
import TransfroUI.*;
import UserLogin.UserLogin;
import java.sql.*;
import java.util.Map;

public class Bank {
    public static void main(String[] args){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try{
            conn = Mysql.connection("bank");
            Map<String,String> map = InitUI.initUI();
            String uname = map.get("username");
            String pwd = map.get("password");
            UserLogin.userLogin(uname,pwd,conn);

            conn.setAutoCommit(false);
            transfor(uname,conn);
            conn.commit();

        }catch(SQLException e){
            if(conn != null){
                try{
                    conn.rollback();
                    System.out.println("事务回滚");
                }catch(Exception a){
                    a.printStackTrace();;
                }
            }
            e.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            Mysql.close(conn,pstmt,rs);
        }
    }

    private static void transfor(String uname,Connection conn) throws SQLException{
        Map<String,String> trans = TransforUI.transforUI();
        String tname = trans.get("transforname");
        String money = trans.get("money");
        String[] sql = {"update userstate set money = money+? where username = ?;",
                        "update userstate set money = money-? where username = ?",
                        "select money from userstate where username = ?"};
        PreparedStatement pstmt_0 = conn.prepareStatement(sql[0]);
        PreparedStatement pstmt_1 = conn.prepareStatement(sql[1]);
        PreparedStatement pstmt_2 = conn.prepareStatement(sql[2]);
        pstmt_0.setString(1,money);
        pstmt_0.setString(2,tname);
        pstmt_1.setString(1,money);
        pstmt_1.setString(2,uname);
        pstmt_2.setString(1,uname);
        int count = pstmt_0.executeUpdate();
        count += pstmt_1.executeUpdate();
        ResultSet rs = pstmt_2.executeQuery();
        rs.next();
        if(count==2 && rs.getInt("money")>=0){
            System.out.println("转账成功");
        }else{
            System.out.println("余额不足");
            throw new SQLException();
        }
    }
}

Mysql封装源码：

package Mysql;

import java.sql.*;

public class Mysql{
    private Mysql(){}

    //注册驱动
    static{
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    //连接数据库
    public static Connection connection(String database_name) throws Exception{
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/"+database_name,
                                                "root","feifei123");
    }

    //释放资源
    public static void close(Connection conn, PreparedStatement pstmt){
        if(pstmt != null){
            try{
                pstmt.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        if(conn != null){
            try{
                conn.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
    }

    //重载释放资源方法
    public static void close(Connection conn,PreparedStatement pstmt,ResultSet rs){
        if(rs != null){
            try{
                rs.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        if(pstmt != null){
            try{
                pstmt.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        if(conn != null){
            try{
                conn.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
    }
}

用户输入模块源码：

package InitUI;

import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;

public class InitUI {
    private InitUI(){}

    public static Map<String, String> initUI() {
        Map<String,String> map = new HashMap();
        Scanner s = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String username = s.next();
        System.out.println("请输入密码：");
        String password = s.next();
        map.put("username",username);
        map.put("password",password);
        return map;
    }
}

用户登录检测源码：

package UserLogin;

import java.sql.*;
import java.sql.SQLException;
import java.util.Map;
import java.util.ResourceBundle;


public class UserLogin {
    private UserLogin(){}

    public static void userLogin(String username, String password,Connection conn) throws Exception{
        String sql = "select * from userlogin where username = ? and password = ?;";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,username);
        pstmt.setString(2,password);

        ResultSet rs = pstmt.executeQuery();
        boolean flag = rs.next()==true?true:false;
        if(flag){
            System.out.println(username+"欢迎回来");
        }else{
            System.out.println("登录失败，请重新登录");
            Map<String,String> map =InitUI.InitUI.initUI();
            String uname = map.get("username");
            String pwd = map.get("password");
            userLogin(uname,pwd,conn);
        }
    }
}

用户转账检测源码：

package TransfroUI;

import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;

public class TransforUI {
    private TransforUI(){}

    public static Map<String, String> transforUI() {
        Map<String,String> map = new HashMap();
        Scanner s = new Scanner(System.in);
        System.out.println("请输入要转账的用户名：");
        String username = s.next();
        System.out.println("请输入要转账的金额：");
        String password = s.next();
        map.put("transforname",username);
        map.put("money",password);
        return map;
    }
}