与数据库建立连接的银行取钱存钱，数据库实时更新用户信息2(使用封装了对数据库增加、修改、删除、查询的DBUtils工具类)

前言

步骤：
1.加载mysql-connector-java-5.1.7-bin.jar，commons-dbutils-1.4.jar，c3p0- 0.9.1.2.jar架包并build path
2.src中加载c3p0-config.xml配置文件
3.编写DBUtils类
4.编写javabean类

DBUtils类

/**
 * 封装使用c3p0数据池与数据库建立连接和不用数据池建立连接的，对数据经行增加、修改、删除和查询
 * 注：读取资源配置文件、加载驱动类、与数据库建立连接都由数据池自动完成
 * author 脱发的小白
 */
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.mchange.v2.c3p0.ComboPooledDataSource;
//DbUtils的工具类封装基本的增删改查
public class DBUtils {
	//创建数据池
	public static DataSource dataSource=new ComboPooledDataSource();
	
   //获取数据池
	public static DataSource getDatasource () {
		return dataSource;
	}
	
	//数据池获取连接
	public static Connection getConnection() throws Exception {
		return dataSource.getConnection();
	}
	
	
	/**
	 * 封装为不用数据池的,增加、修改、删除
	 * sql:执行的数据库语句
	 * params：查询数据库语句是否有替换占位符的参数
	 */
	public static Integer executeUpateConnction(String sql,Object...params) throws Exception {
		Connection connection=getConnection();
		QueryRunner qr = new QueryRunner();
		Integer result=0;
		if(params==null) {
			result=qr.update(connection, sql);
		}
		else {
			result=qr.update(connection, sql, params);
		}
		return result;
	}
	
	/**
	 * 封装为数据池的增加、修改、删除
	 * sql:执行的数据库语句
	 * params：查询数据库语句是否有替换占位符的参数
	 */
	public static Integer executeUpateDatasource(String sql,Object ... params) throws Exception {
		Integer result=0;
		QueryRunner qr = new QueryRunner(DBUtils.getDatasource());
		result=qr.update(sql, params);
		return result;
	}
	
	/**
	 * 封装为DBUtils的查询，不用数据池获取数据库建立连接
	 * sql:执行的数据库语句
	 * params：查询数据库语句是否有替换占位符的参数
	 * cazz:是用来定义接收查询数据结果的存入类的对象中的类的类型
	 */
	public static <T> List<T> queryConnection(String sql,Class<T> cazz,Object ... params) throws Exception{
		List<T> list=null;
		Connection connection=null;
		QueryRunner qr = new QueryRunner();
		BeanListHandler<T> beanListHandler = new BeanListHandler<>(cazz);
		if(params==null) {
			qr.query(connection, sql, beanListHandler);
		}
		else {
			qr.query(connection,sql, beanListHandler, params);
		}
		return list;
	}
	
	/**
	 * 封装为DBUtils的查询，使用数据池建立连接
	 * sql:执行的数据库语句
	 * params：查询数据库语句是否有替换占位符的参数
	 * cazz:是用来定义接收查询数据结果的存入类的对象中的类的类型
	 *
	 */
	public static <T> List<T> queryDatasource(String sql,Class<T> cazz,Object ... params) throws SQLException{
		List<T> list=null;
		QueryRunner qr=new QueryRunner(DBUtils.getDatasource());
		BeanListHandler<T> beanListHandler = new BeanListHandler<>(cazz);
		list= qr.query(sql, beanListHandler, params);
		return list;
	}
	
}

Account类

/**
 * 与数据库建立连接的银行取钱存钱，数据库实时更新用户信息(使用封装了对数据库增加、修改、删除、查询的DBUtils工具类)
 *  @auther脱发的小白
 */
public class Account {
	private String username;
	private String password;
	private String number;
	private int balance;

	public Account() {
		super();
		// TODO Auto-generated constructor stub
	}

	public Account(String username, String password, String number, int balance) {
		super();
		this.username = username;
		this.password = password;
		this.number = number;
		this.balance = balance;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

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

	public String getNumber() {
		return number;
	}

	public void setNumber(String number) {
		this.number = number;
	}

	public int getBalance() {
		return balance;
	}

	public void setBalance(int balance) {
		this.balance = balance;
	}

	// 注册账号，利用DBUtil将数据存入数据库中
	public void register() throws Exception {
		String sql = "insert into account (number,username,password,balance) values (?,?,?,?)";
		Object[] params = { this.number, this.username, this.password, this.balance };
		Integer result = DBUtils.executeUpateDatasource(sql, params);
		if (result == 1) {
			System.out.println("用户注册成功");
		}
		if (result == 0) {
			System.out.println("用户注册失败");
		}
	}

	// 核对信息,卡号和密码正确返回true,否则返回false
	public boolean check() throws SQLException {
     boolean result=false;
     String sql="select * from account where number=?";
     Object[] params= {this.number};
     List<Account> list = DBUtils.queryDatasource(sql, Account.class, params);
     Account account = list.get(0);
     if(this.number.equals(account.number) & this.password.equals(account.password)) {
    	 result=true;
     }
     else {
    	 System.out.println("卡号和密码错误");
     }
     return result;
	}
	
	//查询余额
	public Integer queryBalance() throws Exception {
		Integer result=0;
		if(this.check()) {
			String sql="select balance from account where number=?;";
			Object [] params= {this.number};
			List<Account> list = DBUtils.queryDatasource(sql, Account.class, params);
			Account account = list.get(0);
			int balance2 = account.balance;//数据库中用户中的余额
			result=balance2;
		}
		return result;
	} 
	
	//存钱
	public void saveMoney(int mon) throws Exception {
		Integer result=0;
		if(this.check()) {
			String sql="update account set balance=balance+? where number=?";
			Object [] params= {mon,this.number};
			Integer res = DBUtils.executeUpateDatasource(sql, params);
			if(res==1) {
				result=this.queryBalance();
				System.out.println("存钱成功");
				System.out.println("余额为"+result);
			}
			if(res==0) {
				System.out.println("存钱失败");
			}
		}
	}
	
	//取钱
	public Integer drawMoney(int mon) throws  Exception {
		int result=0;
		if(this.check()) {
			String sql="update account set balance=balance-? where number=?";
			Object [] params= {mon,this.number};
			Integer res = DBUtils.executeUpateDatasource(sql, params);
			if(res==1) {
				result=this.queryBalance();
				System.out.println("取钱成功");
				System.out.println("余额为"+result);
			}
			if(res==0) {
				System.out.println("存钱失败");
			}
		}
		return result;
	}
}

测试类

public class Test {

	public static void main(String[] args) throws Exception {
		Account account1 = new Account("小敏", "5201314", "1003", 20000);
		account1.register();
		account1.saveMoney(1000);
		account1.drawMoney(2000);

		Account account2 = new Account("小孙", "100320", "653214", 500000);
		 account2.register();
		account2.saveMoney(2000);
		account2.drawMoney(1000);
		
	}
}

结果展示