1、实体类
package bank;
public class Account {
private String cardNo;
private String password;
private String name;
private double balance;
public String getCardNo() {
return cardNo;
}
public void setCardNo(String cardNo) {
this.cardNo = cardNo;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getBalance() {
return balance;
}
public void setBalance(double balance) {
this.balance = balance;
}
public Account(String cardNo, String password, String name, double balance) {
super();
this.cardNo = cardNo;
this.password = password;
this.name = name;
this.balance = balance;
}
public Account() {
super();
}
@Override
public String toString() {
return "Account [cardNo=" + cardNo + ", password=" + password + ", name=" + name + ", balance=" + balance + "]";
}
}
2.工具类
db.properties的内容
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/bank?useUnicode=true&characterEncoding=utf8
username=root
password=123456
package bank;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DBUtils {
public static final Properties PROPERTIES=new Properties();
**private static ThreadLocal<Connection> threadLocal=new ThreadLocal<Connection>();**
static {
//读取文件的位置
InputStream inputStream=DBUtils.class.getResourceAsStream("/db.properties");
try {
//把输入流加载到PROPERTIES中
PROPERTIES.load(inputStream);
//加载驱动类
Class.forName(PROPERTIES.getProperty("driver"));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//连接
public static Connection getConnection() {
**Connection connection=threadLocal.get();
try {
if(connection==null) {
connection=DriverManager.getConnection(PROPERTIES.getProperty("url"),PROPERTIES.getProperty("username"),PROPERTIES.getProperty("password"));
threadLocal.set(connection);
}**
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
//释放资源
public static void closeAll(Connection connection,Statement pstmt,ResultSet resultSet) {
if(resultSet!=null) {
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(pstmt!=null) {
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(connection!=null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
3.数据库访问层
package bank;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class AccountDaoImpl {
//新增数据
public int insert(Account account) {
//1.获取连接
Connection connection=DBUtils.getConnection();
int result=0;
PreparedStatement pstmt=null;
try {
//2.准备PreparedStatement
pstmt=connection.prepareStatement("insert into account(cardNo,password,name,balance)values(?,?,?,?)");
//3.给占位符进行赋值
pstmt.setString(1, account.getCardNo());
pstmt.setString(2, account.getPassword());
pstmt.setString(3, account.getName());
pstmt.setDouble(4, account.getBalance());
//4.执行
result=pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBUtils.closeAll(connection, pstmt, null);
}
return result;
}
//修改数据
public int update(Account account,String cardNo1) {
//1.连接
Connection connection=DBUtils.getConnection();
int result=0;
PreparedStatement pstmt=null;
try {
//2.准备PreparedStatement
pstmt=connection.prepareStatement("update account set cardNo=?,password=?,name=?,balance=? where cardNo=?");
//3.给占位符进行赋值
pstmt.setString(1, account.getCardNo());
pstmt.setString(2,account.getPassword());
pstmt.setString(3, account.getName());
pstmt.setDouble(4, account.getBalance());
pstmt.setString(5, cardNo1);
//4.执行
result=pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBUtils.closeAll(connection, pstmt, null);
}
return result;
}
//重载
public int update(double balance1,String cardNo) {
//1.连接
Connection connection=DBUtils.getConnection();
System.out.println("connection3"+connection);
int result=0;
PreparedStatement pstmt=null;
try {
//2.准备PreparedStatement
pstmt=connection.prepareStatement("update account set balance=balance+? where cardNo=?");
//3.给占位符进行赋值
pstmt.setDouble(1, balance1);
pstmt.setString(2, cardNo);
//4.执行
result=pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBUtils.closeAll(null, pstmt, null);
}
return result;
}
//删除
public int delete(String cardNo) {
//1.连接
Connection connection=DBUtils.getConnection();
int result=0;
PreparedStatement pstmt=null;
try {
//2.准备PreparedStatement对象
pstmt=connection.prepareStatement("delete from account where cardNo=?");
//3.给占位符进行赋值
pstmt.setString(1, cardNo);
//4.执行
result=pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBUtils.closeAll(connection, pstmt, null);
}
return result;
}
//查询一条数据
public Account select(String cardNo) {
//1.连接
Connection connection=DBUtils.getConnection();
System.out.println("connection2"+connection);
Account account=null;
PreparedStatement pstmt=null;
ResultSet resultSet=null;
try {
//2.准备PreParedStatement对象
pstmt=connection.prepareStatement("select * from account where cardNo=?");
//3.给占位符尽心赋值
pstmt.setString(1, cardNo);
//4.执行
resultSet=pstmt.executeQuery();
//5.处理结果
/**
* resultSet.next(),刚一开始我写成了resultSet!=null 然后出现的错误就是before start of result set
*/
if(resultSet.next()) {
cardNo = resultSet.getString("cardNo");
String password=resultSet.getString("password");
String name=resultSet.getString("name");
double balance=resultSet.getDouble("balance");
account=new Account(cardNo, password, name, balance);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBUtils.closeAll(null, pstmt, resultSet);
}
return account;
}
//查询所有数据
public List<Account> selectAll(){
//1.连接
Connection connection=DBUtils.getConnection();
List<Account> list=new ArrayList<Account>();
Account account=null;
PreparedStatement pstmt=null;
ResultSet resultSet=null;
try {
//2.准备PreparedStatement对象
pstmt=connection.prepareStatement("select * from account");
//3.执行
resultSet=pstmt.executeQuery();
//4.处理结果集
while(resultSet.next()) {
String cardNo1 = resultSet.getString("cardNo");
String password=resultSet.getString("password");
String name=resultSet.getString("name");
double balance=resultSet.getDouble("balance");
account=new Account(cardNo1, password, name, balance);
list.add(account);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBUtils.closeAll(connection, pstmt, resultSet);
}
return list;
}
}
4.业务逻辑层
package bank;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;
public class AccountServiceImpl {
Scanner sc=new Scanner(System.in);
//转账业务
public boolean transferMoney(String cardNo1,String cardNo2,double balance) {
// AccountDaoImpl accountDao=new AccountDaoImpl();
// int b=accountDao.update(-balance, cardNo1);
// int b1=accountDao.update(balance, cardNo2);
//
// if(b+b1==2) {
// return true;
// }else {
// return false;
// }
Connection connection=null;
try {
connection= DBUtils.getConnection();
System.out.println("connection1"+connection);
connection.setAutoCommit(false);
//1组织完善的业务流程
AccountDaoImpl accountDao=new AccountDaoImpl();
Account account=accountDao.select(cardNo1);
//1.1验证carfNo1是否存在
if(account==null) {
throw new RuntimeException("卡号不存在");
}
//1.2验证密码是否正确
System.out.println("请输入密码:");
String pwd=sc.next();
if(!pwd.equals(account.getPassword())) {
throw new RuntimeException("密码错误");
}
//1.2验证余额是否充足
if(account.getBalance()<balance) {
throw new RuntimeException("余额不足");
}
//1.3验证对方卡号是否存在
Account account2=accountDao.select(cardNo2);
if(account2==null) {
throw new RuntimeException("对方卡号不存在");
}
//1.4转账
accountDao.update(-balance, cardNo1);
// int i=10/0;
accountDao.update(balance, cardNo2);
connection.commit();
return true;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
try {
connection.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}finally {
DBUtils.closeAll(connection, null, null);
}
}
return false;
}
}
5测试类
package bank;
public class TestTransferMoney {
public static void main(String[] args) {
AccountServiceImpl accountService=new AccountServiceImpl();
boolean b=accountService.transferMoney("222222", "111111",1000);
if(b) {
System.out.println("转账成功");
}else {
System.out.println("转账失败");
}
}
}
但是上述代码并不符合数据库访问层和业务逻辑层代码编写的要求(比如你在业务逻辑层里获取连接对象,这本来应该是数据库链路层里做的呀)
改2. 工具类
package bank;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DBUtils {
public static final Properties PROPERTIES=new Properties();
private static ThreadLocal<Connection> threadLocal=new ThreadLocal<Connection>();
static {
//读取文件的位置
InputStream inputStream=DBUtils.class.getResourceAsStream("/db.properties");
try {
//把输入流加载到PROPERTIES中
PROPERTIES.load(inputStream);
//加载驱动类
Class.forName(PROPERTIES.getProperty("driver"));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//连接
public static Connection getConnection() {
Connection connection=threadLocal.get();
try {
if(connection==null) {
connection=DriverManager.getConnection(PROPERTIES.getProperty("url"),PROPERTIES.getProperty("username"),PROPERTIES.getProperty("password"));
threadLocal.set(connection);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
//开启事务
public static void startTransaction() {
Connection connection=getConnection();
try {
connection.setAutoCommit(false);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//提交事务
public static void commitTransaction() {
Connection connection=null;
try {
connection=getConnection();
connection.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
closeAll(connection,null,null);
}
}
//回滚事务
public static void rollbackTransaction() {
Connection connection=null;
try {
connection.rollback();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
closeAll(connection, null, null);
}
}
//释放资源
public static void closeAll(Connection connection,Statement pstmt,ResultSet resultSet) {
if(resultSet!=null) {
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(pstmt!=null) {
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(connection!=null) {
try {
connection.close();
threadLocal.remove();//关闭连接后,移除已关闭的connection对象
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
改4.1业务逻辑层代码
package bank;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;
public class AccountServiceImpl {
Scanner sc=new Scanner(System.in);
//转账业务
public boolean transferMoney(String cardNo1,String cardNo2,double balance) {
// AccountDaoImpl accountDao=new AccountDaoImpl();
// int b=accountDao.update(-balance, cardNo1);
// int b1=accountDao.update(balance, cardNo2);
//
// if(b+b1==2) {
// return true;
// }else {
// return false;
// }
try {
//开启事务
DBUtils.startTransaction();
//1组织完善的业务流程
AccountDaoImpl accountDao=new AccountDaoImpl();
Account account=accountDao.select(cardNo1);
//1.1验证carfNo1是否存在
if(account==null) {
throw new RuntimeException("卡号不存在");
}
//1.2验证密码是否正确
System.out.println("请输入密码:");
String pwd=sc.next();
if(!pwd.equals(account.getPassword())) {
throw new RuntimeException("密码错误");
}
//1.2验证余额是否充足
if(account.getBalance()<balance) {
throw new RuntimeException("余额不足");
}
//1.3验证对方卡号是否存在
Account account2=accountDao.select(cardNo2);
if(account2==null) {
throw new RuntimeException("对方卡号不存在");
}
//1.4转账
accountDao.update(-balance, cardNo1);
// int i=10/0;
accountDao.update(balance, cardNo2);
//提交事务
DBUtils.commitTransaction();
return true;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
//回滚事务
DBUtils.rollbackTransaction();
}
return false;
}
}