Java - 银行系统JDBC练习 September 20，2019

package domain;

public class User {
    //创建一个User对象，用来存储数据库中的信息
    private Integer id;//id
    private String name;//账号
    private String password;//密码
    private Float money;//余额
    public User(){}
    public User(Integer id, String name, String password, Float money) {
        this.id = id;
        this.name = name;
        this.password = password;
        this.money = money;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Float getMoney() {
        return money;
    }

    public void setMoney(Float money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", money=" + money +
                '}';
    }
}

 

package util;

import domain.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;

public class UserUtils {
        //将数据库中的数据一次读取出来，存入集合中
        public HashMap<String,User> loadSQL(){
            HashMap<String,User> userBox = new HashMap<String,User>();
            Connection connection = null;
            Statement statement = null;
            ResultSet resultSet = null;
            try {
                connection = JDBCUtils.getConnection();
                String sql ="select *from bank";
                statement = connection.createStatement();
                resultSet = statement.executeQuery(sql);
                while (resultSet.next()){
                    int id = resultSet.getInt("id");
                    String username = resultSet.getString("name");
                    String password = resultSet.getString("password");
                    Float money = resultSet.getFloat("money");
                    userBox.put(username,new User(id,username,password,money));
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.close(resultSet,statement,connection);
            }
            return userBox;
        }
        //将经过操作好的集合 存入数据库中  使用createStatement
        public void commit(HashMap<String,User> userBox){
            Connection connection = null;
            Statement statement = null;
            try {
                Iterator<String> iterator = userBox.keySet().iterator();
                while (iterator.hasNext()){
                    String name = iterator.next();
                    User user = userBox.get(name);//获取user对象
                    Float money = user.getMoney();
                    Integer id = user.getId();
                    connection = JDBCUtils.getConnection();//获取Connection对象
                    String sql = "update bank set money ='"+money+"' where id = '"+id+"'";//定义sql
                    statement = connection.createStatement();//获取执行sql对象
                    int count = statement.executeUpdate(sql);//执行sql
                    if(count>0){
                        System.out.println("SQL修改成功");
                    }else{
                        System.out.println("SQL修改失败");
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.close(statement,connection);
            }
        }
    //将经过操作好的集合 存入数据库中 使用prepareStatement
    public void commit2(HashMap<String,User> userBox){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            Iterator<String> iterator = userBox.keySet().iterator();
            while (iterator.hasNext()){
                String name = iterator.next();
                User user = userBox.get(name);//获取user对象
                Float money = user.getMoney();
                Integer id = user.getId();
                connection = JDBCUtils.getConnection();//获取connection对象
                String sql = "update bank set money = ? where id = ? ";//定义sql
                preparedStatement = connection.prepareStatement(sql);//获取sql执行对象
                preparedStatement.setFloat(1,money);
                preparedStatement.setInt(2,id);
                int count = preparedStatement.executeUpdate();
                if(count>0){
                    System.out.println("SQL修改成功");
                }else{
                    System.out.println("SQL修改失败");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(preparedStatement,connection);
        }
    }
}

 

 

package util;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

//JDBC工具类，可以有效减少JDBC的代码量
public class JDBCUtils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    static {
        try {
            Properties pro = new Properties();
            ClassLoader classLoader = JDBCUtils.class.getClassLoader();
            URL res  = classLoader.getResource("jdbc.properties");
            String path = res.getPath();
            pro.load(new BufferedReader(new FileReader(path)));
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //返回Connection对象
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }
    //关闭
    public static void close(Statement stmt, Connection conn){
        if( stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if( conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(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();
            }
        }
    }
}

 

package service;

import dao.UserDao;
import domain.User;

public class UserService {
    private UserDao userDao = new UserDao();
    //登录
    public boolean login(String username,String password){
        if(username==null||password==null){
            return false;
        }
        User user = userDao.getUser(username);
        if(user!=null&&user.getPassword().equals(password)){
            return true;
        }
        return false;
    }
    //查询余额
    public Float lookOver(String username){
        User user = userDao.getUser(username);
        return user.getMoney();
    }
    //转账
    public void transfer(String username,String transferName,Float transferMoney){
        User user = userDao.getUser(username);
        User transferUser = userDao.getUser(transferName);
        if(user.getMoney()>transferMoney){
           if(transferUser!=null){
               user.setMoney(user.getMoney()-transferMoney);
               userDao.updateUser(user);
               transferUser.setMoney(transferUser.getMoney()+transferMoney);
               userDao.updateUser(transferUser);
               System.out.println("已向"+transferName+"转账"+transferMoney+"元");
           }else {
               System.out.println("收款账户不存在");
           }
        }else {
            System.out.println("余额不足");
        }
    }
    //存钱
    public void deposit(String username,Float money){
        User user = userDao.getUser(username);
        user.setMoney(money+user.getMoney());
        userDao.updateUser(user);
        System.out.println("存入成功");
    }
    //取钱
    public void takeOut(String username,Float money){
        User user = userDao.getUser(username);
        if(money<user.getMoney()){
            user.setMoney(user.getMoney()-money);
            userDao.updateUser(user);
            System.out.println("取款成功");
        }else{
            System.out.println("余额不足");
        }

    }
}

 

package dao;

import domain.User;
import util.UserUtils;

import java.util.HashMap;

public class UserDao {
    //读取集合
    private UserUtils userUtils = new UserUtils();
    private HashMap<String, User> userBox = userUtils.loadSQL();
    //获取user
    public User getUser(String username){
        return userBox.get(username);
    }
    //修改数据
    public void updateUser(User user){
        userBox.put(user.getName(),user);
        userUtils.commit2(userBox);
    }
}

 

package jdbc;

import service.UserService;

import java.util.Scanner;

public class Start {
    public void run(){
        UserService userService = new UserService();
        Scanner input = new Scanner(System.in);
        System.out.println("请输入用户名:");
        String username = input.nextLine();
        System.out.println("请输入密码:");
        String password = input.nextLine();
        boolean flag = userService.login(username,password);
        if(flag){
            System.out.println("登录成功");
            t:while (true){
                System.out.println("请输入您的操作:\n1.取钱\n2.存钱\n3.转账\n4.查询余额\n5.退出");
                String i = input.nextLine();
                switch (i){
                    case "1":
                        System.out.println("请输入取款金额：");
                        String money = input.nextLine();
                        Float money1 = Float.parseFloat(money);
                        userService.takeOut(username,money1);
                        break;
                    case "2":
                        System.out.println("请输入存款金额：");
                        String money2 = input.nextLine();
                        Float money3= Float.parseFloat(money2);
                        userService.deposit(username,money3);
                        break;
                    case "3":
                        System.out.println("请输入您需要转款的账户：");
                        String username2 = input.nextLine();
                        System.out.println("请输入转账金额：");
                        String money4 = input.nextLine();
                        Float money5 = Float.parseFloat(money4);
                        userService.transfer(username,username2,money5);
                        break;
                    case "4":
                        System.out.println(username+"的余额为:"+userService.lookOver(username));
                        break;
                    default:
                        System.out.println("正在退出，请收好您的物件.");
                        break t;
                }
            }
        }else {
            System.out.println("用户名或密码错误");
        }
    }
}

package jdbc;

public class Main {
    public static void main(String[] args) {
       new Start().run();
    }
}