模拟银行转账

1 JDBC+MySQL

id         number    pk         //用户id
ano        varchar2(30)  uk     //用户卡号
apassword  varchar2(30)         //用户密码
aname      varchar2(30)         //用户名
amoney     number               //余额

//创建银行用户表
drop table bank_account;
create table    bank_account(id    number  constraint   bank_account_id_pk   primary key,ano  varchar(30) constraint bank_account_ano_uk  unique,apassword  varchar(30), aname   varchar(30),amoney  number
);
drop sequence bank_account_id_seq;
create sequence    bank_account_id_seq;

写一个java程序用来开户只要输入账号密码开户人的姓名余额
开户信息如下 6225880111887788 123456 zhangsan 99999999
6225880111887799 123456 lisi 9

实体类

public class BankAccount {public int getId() {return id;}public void setId(int id) {this.id = id;}public String getAno() {return ano;}public void setAno(String ano) {this.ano = ano;}public String getAname() {return aname;}public void setAname(String aname) {this.aname = aname;}public String getApassword() {return apassword;}public void setApassword(String apassword) {this.apassword = apassword;}public String getAmoney() {return amoney;}public void setAmoney(String amoney) {this.amoney = amoney;}@Overridepublic String toString() {return "BankAccount [id=" + id + ", ano=" + ano + ", aname=" + aname + ", apassword=" + apassword + ", amoney="+ amoney + "]";}public BankAccount(String ano, String aname, String apassword, String amoney) {super();this.ano = ano;this.aname = aname;this.apassword = apassword;this.amoney = amoney;}public BankAccount(int id, String ano, String aname, String apassword, String amoney) {super();this.id = id;this.ano = ano;this.aname = aname;this.apassword = apassword;this.amoney = amoney;}public BankAccount() {super();// TODO Auto-generated constructor stub}private int id;private String ano;private String aname;private String apassword;private String amoney;
}

开户

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
import com.xdl.util.JdbcUtil2;public class CreateBankAccount {public static void main(String[] args) {Scanner  sc = new Scanner(System.in);System.out.println("请输入开户的卡号:");String  ano = sc.nextLine();System.out.println("请输入开户的名字:");String  aname = sc.nextLine();System.out.println("请输入开户的密码:");String  apassword = sc.nextLine();System.out.println("请输入开户的钱数:");String  smoney = sc.nextLine();//double  amoney = Double.parseDouble(smoney);// 把输入的数据 包装成对象  BankAccount  ba = new BankAccount(ano, aname, apassword, smoney);Connection  conn = null;PreparedStatement  ps = null;conn = JdbcUtil2.getConnection();String  sql="insert into bank_account values(bank_account_id_seq.nextval,?,?,?,?)";try {ps = conn.prepareStatement(sql);ps.setString(1, ba.getAno());ps.setString(2, ba.getApassword());ps.setString(3, ba.getAname());ps.setDouble(4, Double.parseDouble(ba.getAmoney()));int rows = ps.executeUpdate();if(rows == 1) {System.out.println("开户成功");}else{System.out.println("开户失败");}} catch (SQLException e) {e.printStackTrace();}finally{JdbcUtil2.releaseResource(conn, ps, null);}}
}

转账
1 先登录，登录成功才可以执行转账程序

2 转账时，链接会自动进行数据的提交，这样万一转入的账户不成功，则无法回退之前的操作，为了解决操作语句需要同时成功或同事失败则需要关闭连接的自动提交

禁止自动提交 conn.setAutoCommit(false);
语句都成功就提交 conn.commit() ；否则进行回滚 conn.rollback();

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import com.xdl.util.JdbcUtil2;public class BankAccountTransfer {public static void main(String[] args) {Scanner sc = new Scanner(System.in);System.out.println("请输入登录的卡号:");String  ano  = sc.nextLine();System.out.println("请输入登录的密码:");String  apassword = sc.nextLine();Connection  conn = null;PreparedStatement ps = null;/* 定义两个转账的  PreparedStatement */PreparedStatement from = null;PreparedStatement to = null;ResultSet  rs = null;BankAccount  ba = null;conn = JdbcUtil2.getConnection();String  sql="select * from bank_account where ano=? and apassword=?";try {ps=conn.prepareStatement(sql);ps.setString(1, ano);ps.setString(2, apassword);rs = ps.executeQuery();if(rs.next()){ba = new BankAccount(rs.getInt("id"),rs.getString("ano"), rs.getString("aname"), rs.getString("apassword"),rs.getString("amoney"));}if(ba!=null){System.out.println("登录成功");// 从当前账户中扣除输入的金额System.out.println("请输入转账的金额:");String  smoney=sc.nextLine();double  money = Double.parseDouble(smoney);sql="update  bank_account set amoney=amoney-? where ano=?";// 禁止自动提交  conn.setAutoCommit(false);from = conn.prepareStatement(sql);from.setDouble(1, money);from.setString(2, ba.getAno());int  fromf=from.executeUpdate();// 提示用户输入转入的账号  和  账户名System.out.println("请输入收款的账号:");String  toano = sc.nextLine();System.out.println("请输入收款人姓名:");String  toaname= sc.nextLine();sql="update  bank_account set amoney=amoney+? where ano=? and aname=?";to=conn.prepareStatement(sql);to.setDouble(1, money);to.setString(2, toano);to.setString(3, toaname);int tof = to.executeUpdate();if(fromf==1 && tof==1){System.out.println("转账成功");conn.commit(); //提交}else{System.out.println("转账失败");conn.rollback(); //回滚}}else{System.out.println("登录失败");}} catch (SQLException e) {// TODO Auto-generated catch blocktry {conn.rollback();} catch (SQLException e1) {e1.printStackTrace();}e.printStackTrace();}finally{JdbcUtil2.releaseResource(conn, ps, rs);JdbcUtil2.releaseResource(conn, from, null);JdbcUtil2.releaseResource(conn, to, null);}}
}

另一种写法

package com.account.jdbc;import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;import javax.xml.soap.SAAJResult;
/*** 写一个转账的方法，根据传入的转出账户、转入账户和金额，修改表中账户的金额* @author Administrator**/
public class Account {public static void main(String[] args) {Properties prop = new Properties();	Scanner sc = new Scanner(System.in);System.out.println("请输入转出账户：");String roolout = sc.nextLine();System.out.println("请输入转入账户：");String roolin = sc.nextLine();System.out.println("请输入金额：");long price = sc.nextInt();	try {prop.load(new FileInputStream("config\\db.properties"));Class.forName(prop.getProperty("driver"));String user = prop.getProperty("username");String password = prop.getProperty("password");String url = prop.getProperty("url");			Connection conn = DriverManager.getConnection(url, user, password);String sql = "update account set money =? where aname=?";PreparedStatement ps = conn.prepareStatement(sql);String sqlstr = "select aname,money from account where aname =?";PreparedStatement pst = conn.prepareStatement(sqlstr);pst.setString(1, roolin);ResultSet rs = pst.executeQuery();PreparedStatement pst2 = conn.prepareStatement(sqlstr);pst2.setString(1, roolout);ResultSet rs2 = pst2.executeQuery();if(rs2.next()) {	if(rs2.getLong("money") > price) {conn.setAutoCommit(false);ps.setLong(1, rs2.getLong("money")-price);ps.setString(2, roolout);if(rs.next()) {ps.setLong(1, rs.getLong("money")+price);ps.setString(2, roolin);}else {System.out.println("转入的账户不存在");return;}ps.executeUpdate();conn.commit(); //手动提交conn.setAutoCommit(true); //恢复自动提交System.out.println("转账成功!");}else {System.out.println("转出金额过大！无法操作！");return;}}else {System.out.println("转出的账户不存在");}rs.close();rs2.close();ps.close();	conn.close();		}catch (Exception e) {e.printStackTrace();}}}