数据库信息
entity
Bank实体类:
public class Bank {
private String bankNum;
private BigDecimal money;
private String bankName;
private String address;
public Bank() {
}
public Bank(String bankNum, BigDecimal money, String bankName, String address) {
this.bankNum = bankNum;
this.money = money;
this.bankName = bankName;
this.address = address;
}
public String getBankNum() {
return bankNum;
}
public void setBankNum(String bankNum) {
this.bankNum = bankNum;
}
public BigDecimal getMoney() {
return money;
}
public void setMoney(BigDecimal money) {
this.money = money;
}
public String getBankName() {
return bankName;
}
public void setBankName(String bankName) {
this.bankName = bankName;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "Bank{" +
"bankNum='" + bankNum + '\'' +
", money=" + money +
", bankName='" + bankName + '\'' +
", address='" + address + '\'' +
'}';
}
}
util
DbUtils:
public class DbUtils {
private static DataSource dataSource;
private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
//创建连接池
static{
Properties properties = new Properties();
InputStream resource = DbUtils.class.getClassLoader().getResourceAsStream("commonsDb.properties");
try {
properties.load(resource);
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//得到连接池
public static DataSource getDataSource(){
return dataSource;
}
//得到连接
public static Connection getConnection(){
try {
Connection conn = threadLocal.get();
if(conn==null) {
conn = dataSource.getConnection();
threadLocal.set(conn);
}
return conn;
}catch (SQLException e){
throw new BankException("获取连接失败...");
}
}
//开启事务
public static void beginTransaction(){
try {
Connection conn = getConnection();
if(conn != null){
conn.setAutoCommit(false);
}
}catch (SQLException e){
throw new BankException("开启事务失败...");
}
}
//提交事务
public static void commitTransaction(){
try {
Connection conn = getConnection();
if(conn != null){
conn.commit();
}
}catch (SQLException e){
throw new BankException("提交事务失败...");
}
}
//关闭事务
public static void rollbackTransaction(){
try {
Connection conn = getConnection();
if(conn != null){
conn.rollback();
}
}catch (SQLException e){
throw new BankException("回滚失败...");
}
}
//如果没开启事务,放回连接池
public static void closeAuto(){
try {
Connection conn = getConnection();
if(conn.getAutoCommit()){
conn.close();
threadLocal.remove();
}
}catch (SQLException e){
throw new BankException("关闭失败");
}
}
//开始事务,关闭连接
public static void closeNotAuto(){
try {
Connection conn = getConnection();
conn.setAutoCommit(true);
conn.close();
threadLocal.remove();
}catch (SQLException e){
throw new BankException("关闭失败...");
}
}
}
service:
BankService:
public interface BankService {
//转账
void transMoney(String fromBankNum, String toBanNum, BigDecimal money);
//查询
Bank queryByBankNum(String BankNum);
//查询所有
List<Bank> queryAll();
//查询个数
long queryCount();
//查询一列
List<String> queryByColumns(String columnName);
}
BankServiceImpl:
public class BankServiceImpl implements BankService {
private BankDao bankDao = new BankDaoImpl();
@Override
public void transMoney(String fromBankNum, String toBanNum, BigDecimal money) {
try {
DbUtils.beginTransaction();
bankDao.takeMoney(DbUtils.getConnection(),fromBankNum,money);
//假设停电...
//int num = 10/0;
bankDao.saveMoney(DbUtils.getConnection(),toBanNum,money);
//提交事务
DbUtils.commitTransaction();
}catch (Exception e){
//回滚
DbUtils.rollbackTransaction();
throw new BankException("停电了...");
}finally {
//关闭事务
DbUtils.closeNotAuto();
}
}
@Override
public Bank queryByBankNum(String BankNum) {
return bankDao.queryByBankNum(BankNum);
}
@Override
public List<Bank> queryAll() {
return bankDao.queryAllBanks();
}
@Override
public long queryCount() {
return bankDao.queryCount();
}
@Override
public List<String> queryByColumns(String columnName) {
return bankDao.queryColumns(columnName);
}
}
dao
BankDao:
public interface BankDao {
//存钱
void saveMoney(Connection conn,String bankNum, BigDecimal money);
//取钱
void takeMoney(Connection conn,String bankNum, BigDecimal money);
//查询所有
List<Bank> queryAllBanks();
//查询个人
Bank queryByBankNum(String bankNum);
//查询个数
long queryCount();
//查询一列
List<String> queryColumns(String columnName);
}
BankDaoImpl:
public class BankDaoImpl implements BankDao {
//创建dbUtils工具
private QueryRunner que = new QueryRunner(DbUtils.getDataSource());
@Override
public void saveMoney(Connection conn,String bankNum, BigDecimal money) {
String sql = "update bank set money=money+? where bankNum=?";
Object[] prams = {money, bankNum};
try {
que.update(conn,sql,prams);
}catch(SQLException e){
throw new BookException("存钱失败...");
}finally {
DbUtils.closeAuto();
}
}
@Override
public void takeMoney(Connection conn,String bankNum, BigDecimal money) {
String sql = "update bank set money=money-? where bankNum=?";
Object[] prams = {money, bankNum};
try {
que.update(conn,sql,prams);
}catch(SQLException e){
throw new BookException("存钱失败...");
}finally {
DbUtils.closeAuto();
}
}
@Override
public List<Bank> queryAllBanks() {
String sql = "select bankNum,money,bankName,address from bank";
try {
return que.query(sql,new BeanListHandler<>(Bank.class));
}catch (SQLException e){
throw new BookException("查询所有失败...");
}
}
@Override
public Bank queryByBankNum(String bankNum) {
String sql = "select bankNum,money,bankName,address from bank where bankNum = ?;";
try {
return que.query(sql,new BeanHandler<>(Bank.class),bankNum);
}catch (SQLException e){
throw new BookException("查询账号失败...");
}
}
@Override
public long queryCount() {
try {
String sql = "select count(*) from bank";
return que.query(sql,new ScalarHandler<>());
}catch (SQLException e){
throw new RuntimeException("查询个数失败...");
}
}
@Override
public List<String> queryColumns(String columnName) {
String sql = "select bankNum from bank ";
try {
return que.query(sql,new ColumnListHandler<>());
}catch (SQLException e){
throw new BankException("查询列失败...");
}
}
}
exception
BankException:
public class BankException extends RuntimeException {
public BankException() {
super();
}
public BankException(String message) {
super(message);
}
public BankException(String message, Throwable cause) {
super(message, cause);
}
public BankException(Throwable cause) {
super(cause);
}
protected BankException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) {
super(message, cause, enableSuppression, writableStackTrace);
}
}
view
public class SystemView {
private static BankService bankService = new BankServiceImpl();
public static void main(String[] args) {
try {
//showAllBanks();
//trans();
//queryByBankNum();
//queryCount();
queryColumns();
}catch (Exception e){
System.out.println(e.getMessage());
}
}
public static void showAllBanks(){
System.out.println("--------查询所有的用户信息-------");
List<Bank> banks = bankService.queryAll();
if(banks != null){
for (Bank bank : banks) {
System.out.println(bank);
}
}
}
public static void trans(){
System.out.println("------------转账------------------");
bankService.transMoney("1111", "2222", new BigDecimal(2000));
System.out.println("转账成功...");
}
public static void queryByBankNum(){
System.out.println("---------根据卡号查信息----------");
Bank bank = bankService.queryByBankNum("3333");
System.out.println(bank);
}
public static void queryCount(){
System.out.println("---------查询用户个数---------");
System.out.println(bankService.queryCount());
}
public static void queryColumns(){
System.out.println("-------------查一列----------");
List<String> bankNums = bankService.queryByColumns("bankNum");
if(bankNums != null){
System.out.println(bankNums);
}
}
}