模拟银行系统 java,mysql实现

模拟电子银行，开户，存钱，取钱，查询用户信息，修改用户信息，查询余额，查询明细，注销，退出，等等 

基本实现了相关功能。但是规定每次需要重新登录，还是很麻烦的。

另外用户注销时候，没有规定删除operator表中信息，此处会有问题。但想了半天觉得现实中这部分信息需要保留一段时间，不应该删除。orz

数据库设计：

bank.java:

package mybank;

import org.apache.commons.dbutils.QueryRunner;

//import java.sql.Date;
import java.util.*;
import java.util.Date;
import java.sql.*;
import java.text.DecimalFormat;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
public class bank {
	
//	public static Connection getConnection() throws Exception
//	{
		Class.forName("com.mysql.cj.jdbc.Driver");
		Properties info=new Properties();
		info.setProperty("user", "root");
		info.setProperty("password", "wenmiao11");
		
		Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/scsxbank?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false", info);
		
//		
//		
//		return conn;
//	}

	public static int writeoperator(String type,double num,int fromid,int toid) throws Exception
	{
		Connection conn=JdbcUtil.getConn();
		PreparedStatement pstmt = conn.prepareStatement("insert into operator "
				+ "values(0,?,?,?,?,?,?)");
		pstmt.setString(1,type);
		pstmt.setDouble(2,num);
		pstmt.setInt(3,fromid);
		pstmt.setInt(4,toid);
		//Date d=new Date();
		java.sql.Date timePara = new java.sql.Date(new Date().getTime());
		pstmt.setDate(5,timePara);
		Calendar cal = Calendar.getInstance();//日期类
        java.sql.Timestamp timestampnow = new java.sql.Timestamp(cal.getTimeInMillis());//转换成正常的日期格式
        java.util.Date current =new java.util.Date();
        timestampnow = new java.sql.Timestamp(current.getTime());
		pstmt.setTimestamp(6, timestampnow);
		int k=pstmt.executeUpdate();
		return k;
	}
	public static int testmoneyenough(int idnum,double awaymoney) throws Exception
	{
		double balance=getbalance(idnum);
		if(balance>awaymoney)
		{
			return 1;
		}
		else
		{
			System.out.println("money is not enough!");
			return 0;
		}
	}
	public static double getbalance(int idnum) throws Exception
	{
		Connection conn=JdbcUtil.getConn();
		String sql="select * from balance where id=?;";
		Object[] params2 = {idnum};
		QueryRunner qr = new QueryRunner(JdbcUtil2.getDataSource());
		Object[] obj = qr.query(conn, sql, new ArrayHandler(),params2);

		if(obj.length>0)
		{
			System.out.println(obj[1]);
			return ((double)obj[1]);
		}
		else
		{
			System.out.println("error");
			return -1;
		}
	}
	
	public static int updatebalance(double num,int idnum) throws Exception
	{
		Connection conn=JdbcUtil.getConn();
		PreparedStatement pstmt ;
		pstmt = conn.prepareStatement("update balance set balance=balance+? where id=?;");
		pstmt.setDouble(1, num);
		pstmt.setInt(2, idnum);
		int k=pstmt.executeUpdate();
		return k;
	}
	public static int testpassword() throws Exception
	{
		System.out.println("input your id number:");
		Scanner cin=new Scanner(System.in); 
		int idnum=cin.nextInt();
		System.out.println("input your password:");
		String password = cin.next();
		Connection conn=JdbcUtil.getConn();
		String sql="select * from user where id=? and password=?";
		
		Object[] params = {idnum,password,};
		
		QueryRunner qr = new QueryRunner(JdbcUtil2.getDataSource());
		Object[] obj = qr.query(conn, sql, new ArrayHandler(),params);
		
		if(obj.length>0)
		{
			return idnum;
		}
		else
		{
			System.out.println("wrong message!");
			return -1;
		}
		
	}
	
	public static void openAnAccount() throws Exception
	{
		Scanner cin=new Scanner(System.in); 
		
		System.out.println("input your name:");
		String name=cin.next();
		String password;
		String passwordagain;
		
		while(true)
		{
			System.out.println("input your password:");
			password=cin.next();
			
			System.out.println("input your password again:");
			passwordagain=cin.next();
	
			if(password.equals(passwordagain))
			{
				break;
			}
			else
			{
				System.out.println("different password, please input again!");
			}
		}
		Connection conn=JdbcUtil.getConn();
		
		PreparedStatement pstmt = conn.prepareStatement("select * from maxid");
		//String sql="select max(id) from user";
		ResultSet rs=pstmt.executeQuery();
		
		rs.next();
		
		//rs.getObject(1);
		int id=rs.getInt(1)+1;
		pstmt = conn.prepareStatement("update maxid set id=id+1");
		pstmt.executeUpdate();
		//String strmaxid=rs.getString(1);
		//System.out.println(id);
		
		//sql="insert into user values(%d,%s,%s)";
		pstmt = conn.prepareStatement("insert into user values(?,?,?)");
		
		pstmt.setInt(1, id);
		pstmt.setString(2, name);//System.out.println("again!");
		pstmt.setString(3, password);
		
		int k = pstmt.executeUpdate();
		pstmt = conn.prepareStatement("insert into balance values(?,?)");
		
		pstmt.setInt(1, id);
		pstmt.setDouble(2, 0);
		
		int l = pstmt.executeUpdate();
		
		//System.out.println("dif");
		DecimalFormat df=new DecimalFormat("0000000000");
		if(k!=0&&l!=0)
			System.out.println("create the account successfully,your id is "+df.format(id));
		//15246785055
		k=writeoperator("enroll",-1,id,0);
//		pstmt = conn.prepareStatement("insert into operator values(?,?,?,?,?)");
//		pstmt.setInt(1, 0);
//		pstmt.setString(2, "enroll");//System.out.println("again!");
//		pstmt.setInt(3, 0);
//		pstmt.setString(4, String.valueOf(id));//System.out.println("again!");
//		pstmt.setString(5, "");//System.out.println("again!");
//		k=pstmt.executeUpdate();
		if(k==1)
			System.out.println("success again");
		else
			System.out.println("TAT");
	}
	
	
	public static void showbalance() throws Exception
	{
		System.out.println("input your id number:");
		Scanner cin=new Scanner(System.in); 
		int idnum=cin.nextInt();
		//System.out.println(idnum);
		System.out.println("input your password:");
		String password = cin.next();
		
		Connection conn=JdbcUtil.getConn();
		String sql="select * from user where id=? and password=?";
		
		Object[] params = {idnum,password,};
		
		QueryRunner qr = new QueryRunner(JdbcUtil2.getDataSource());
		
		
		Object[] obj = qr.query(conn, sql, new ArrayHandler(),params);
		//System.out.println("2w2w");
		//String id=obj[0].toString();
		if(obj.length>0)
		{
			//System.out.println("-----");
			System.out.println(obj[1]+" : 你好！");
		}
		else
		{
			System.out.println("wrong message!");
			return;
		}
		int k=writeoperator("login",-1,idnum,0);
//		PreparedStatement pstmt ;
//		pstmt = conn.prepareStatement("insert into operator values(?,?,?,?,?)");
//		pstmt.setInt(1, 0);
//		pstmt.setString(2, "login");//System.out.println("again!");
//		pstmt.setInt(3, 0);
//		pstmt.setString(4, String.valueOf(id));//System.out.println("again!");
//		pstmt.setString(5, "");//System.out.println("again!");
//		int k=pstmt.executeUpdate();
		if(k==1)
			System.out.println("success again");
		else
			System.out.println("TAT");
		
		getbalance(idnum);
		
//		sql="select * from balance where id=?;";
//		Object[] params2 = {idnum};
//		qr = new QueryRunner(JdbcUtil2.getDataSource());
//		obj = qr.query(conn, sql, new ArrayHandler(),params2);
//
//		if(obj.length>0)
//			System.out.println(obj[1]);
//		else
//			System.out.println("error");
		
	}
	
	public static void saving() throws Exception
	{
		Scanner cin=new Scanner(System.in); 
		int idnum=testpassword();
		if(idnum==-1)
		{
			System.out.println("wrong message!");
			return;
		}
		else
		{
			System.out.println("input saving number:");
			double accountnum=cin.nextDouble();
			System.out.println("before saving, your balance is "+getbalance(idnum)+"¥");
			updatebalance(accountnum,idnum);
			writeoperator("saving",accountnum,idnum,0);
			System.out.println("after saving, your balance is "+getbalance(idnum)+"¥");
		}	
	}
	
	public static void withdrawal() throws Exception
	{
		Scanner cin=new Scanner(System.in); 
		int idnum=testpassword();
		if(idnum==-1)
		{
			System.out.println("wrong message!");
			return;
		}
		else
		{
			System.out.println("input withdrawal number:");
			double accountnum=cin.nextDouble();
			int flag=testmoneyenough(idnum,accountnum);
			if(flag>0)
			{
				System.out.println("before withdrawal, your balance is "+getbalance(idnum)+"¥");
				updatebalance(accountnum*(-1),idnum);
				writeoperator("withdrawal",accountnum,idnum,0);
				System.out.println("after withdrawal, your balance is "+getbalance(idnum)+"¥");
			}
			else
				return;
		}
	}
	
	public static void transfer() throws Exception
	{
		Scanner cin=new Scanner(System.in);
		int idnum=testpassword();
		if(idnum>0)
		{
			System.out.println("which evil will deprive your little money?");
			int toidnum=cin.nextInt();
			System.out.println("And how much?");
			double accountnum=cin.nextInt();
			int flag=testmoneyenough(idnum,accountnum);
			if(flag>0)
			{
				System.out.println("before transfer, your balance is "+getbalance(idnum)+"¥");
				Connection conn=JdbcUtil.getConn();
				conn.setAutoCommit(false);
				String sql1 = "update balance set balance=balance-? where id=?";
				String sql2 = "update balance set balance=balance+? where id=?";
				PreparedStatement pstmt = conn.prepareStatement(sql1);
				pstmt.setDouble(1,accountnum);
				pstmt.setInt(2,idnum);
				pstmt.executeUpdate();
				pstmt = conn.prepareStatement(sql2);
				pstmt.setDouble(1,accountnum);
				pstmt.setInt(2,toidnum);
				pstmt.executeUpdate();
				conn.commit();
				writeoperator("transfer",accountnum,idnum,toidnum);
				
				System.out.println("after transfer, your balance is "+getbalance(idnum)+"¥");
			}
			else
				return;
		}
		else
			return;
	}
	
	public static void seekdetails() throws Exception
	{
		int idnum=testpassword();
		writeoperator("seek",0,idnum,0);
		if(idnum>0)
		{
			Connection conn=JdbcUtil.getConn();
			
			String sql = "select * from operator where fromid=?;";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,idnum);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next())
			{
				System.out.println(rs.getInt(1)+" "+rs.getString(2));
			}
		}
		else
		{
			return;
		}
	}
	
	public static void logoff() throws Exception
	{
		int idnum=testpassword();
		if(idnum>0)
		{
			System.out.println("are you sure?(Y/N)");
			Scanner cin=new Scanner(System.in);
			String s=cin.next();
			if(s.equals("Y"))
			{
				Connection conn=JdbcUtil.getConn();
				
				String sql = "delete  from user where id=?;";
				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1,idnum);
				int k=pstmt.executeUpdate();
				
				sql = "delete  from balance where id=?;";
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1,idnum);
				int l=pstmt.executeUpdate();
				if(k>0&&l>0)
				{
					writeoperator("delete",0,idnum,0);
					System.out.println("delete successfully!");
				}
				else
				{
					System.out.println("there must be something wrong!");
				}
			}
			else
			{
				return;
			}
		}
		else
		{
			return;
		}
	}
	
	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		Scanner cin=new Scanner(System.in);
		int flag=0;
		while(true)
		{
			System.out.println("--欢迎使用并没有钱的银行系统！--\n"
					+ "(1):开户\n"
					+ "(2):显示余额\n"
					+ "(3):存钱\n"
					+ "(4):取钱\n"
					+ "(5):转账\n"
					+ "(6):明细查询\n"
					+ "(7):注销\n"
					+ "(0):退出\n"
					+ "请输入你的操作：\n");
			 
			int i=cin.nextInt();
			switch(i)
			{
				case 1 :
				{
					openAnAccount();
					break;
				}
				case 2 :
				{
					showbalance();
					break;
				}
				case 3 :
				{
					saving();
					break;
				}
				case 4 :
				{
					withdrawal();
					break;
				}
				case 5 :
				{
					transfer();
					break;
				}
				case 6 :
				{
					seekdetails();
					break;
				}
				case 7 :
				{
					logoff();
					break;
				}
				case 0:
				{
					System.out.println("欢迎下次使用！");
					flag=1;
					break;
				}
				default:
				{
					System.out.println("输入错误，请重试！");
				}
			}
			if(flag==1)
				break;
			
		}
		cin.close();
	}

}

JdbcUtil.java:

package mybank;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;

/*
 * 为了提高代码复用性，进行简单的封装
 * */
public class JdbcUtil {
	/*public static final String DRIVERNAME = "com.mysql.jdbc.Driver";
	public static final String URL = "jdbc:mysql:///test";
	public static final String USER = "root";
	public static final String PASSWORD = "123456";*/
	
/*	public static String driver;
	public static String url;
	public static String user;
	public static String password;
	*/
	//加载配置文件
	public static Properties ps = null;
	static{
		ps = new Properties();
		Reader r = null;
		try {
			r = new FileReader("src/db.properties");
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			ps.load(r);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//driver = ps.getProperty(driver);
	}
	
	static{
		try {
			Class.forName(ps.getProperty("driver"));
		} catch (ClassNotFoundException e) {
			System.out.println("注册驱动失败。。");
			e.printStackTrace();
		}
	}
	/*static{
		try {
			Class.forName(DRIVERNAME);
		} catch (ClassNotFoundException e) {
			System.out.println("注册驱动失败。。");
			e.printStackTrace();
		}
	}*/
	
	//提供获取连接对象的方法
	public static Connection getConn(){
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(ps.getProperty("url"), ps.getProperty("user"), ps.getProperty("password"));
		} catch (SQLException e) {
			System.out.println("获取连接失败。");
			e.printStackTrace();
		}
		return conn;
	}
	
	//提供一个专门执行dml语句的方法
	public static int executeUDI(String sql,Object[] params) throws Exception{
		Connection conn = getConn();
		PreparedStatement pstmt = conn.prepareStatement(sql);
		for(int i=0;params!=null&&i<params.length;i++){
			pstmt.setObject(i+1, params[i]);
		}
		return pstmt.executeUpdate();
	}
	
	//提供一个转么执行select语句的方法，返回对象集合
	public static ArrayList executeQuery(String sql,Object[] params){
		//....
		return null;
	}
}

JdbcUtil2.java:

package mybank;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSource;



public class JdbcUtil2 {
	//加载配置文件
	public static Properties ps = null;
	static{
		ps = new Properties();
		Reader r = null;
		try {
			r = new FileReader("src/db.properties");
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			ps.load(r);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//driver = ps.getProperty(driver);
	}
	
	/*
	 * 创建连接池
	 * */
	public static BasicDataSource dataSource = new BasicDataSource();
	/*
	 * 对连接池进行配置，基本配置
	 * */
	static{
		dataSource.setDriverClassName(ps.getProperty("driver"));
		dataSource.setUrl(ps.getProperty("url"));
		dataSource.setUsername(ps.getProperty("user"));
		dataSource.setPassword(ps.getProperty("password"));
	}
	
	/*
	 * 返回连接对象
	 * */
	public static DataSource getDataSource(){
		return dataSource;
	}
}

db.properties:

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/scsxbank?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
user=root
password=xxx