DbUtils框架使用示例

DbUtils框架概述

为更加简单且高效地使用JDBC，Apache组织提供了数据库操作工具类commons-dbutils。该工具对JDBC进行了封装，可极大地简化JDBC的编码工作量。例如：DbUtils在查询数据时可把结果转换成List，Array，Set等集合，非常便于开发人员操作。若需使用该工具，请在官方网站免费下载即可。

DbUtils操作简单，功能强大；其中，它的大部分功能都由以下三大核心实现：
DbUtils工具类 该类主要用于关闭连接、装载JDBC驱动程序等等
ResultSetHandler接口 该接口及其实现类主要用于处理结果集
QueryRunner类 该类主要用于增，删，改，查

DbUtils 简易封装

package com.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DBUtil {
	//创建数据库连接池
	private static DataSource dataSource = new ComboPooledDataSource();
	
	//获取数据库连接池
	public static DataSource getDataSource() {
		return dataSource;
	}
	
	//创建连接
	public static Connection getConnection() {
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	 //释放连接
    public static void releaseConnection(Connection connection, Statement statement, ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            resultSet = null;
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            statement = null;
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            connection = null;
        }
    }
	
}

使用示例

数据库表：

DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
USE mydb; 
create table goddess(
 id int primary key auto_increment,
 username varchar(40),
 password varchar(40),
 cardid varchar(40),
 money int(10)
 );
insert into goddess (cardid,username,password,money) values("65313456","ljx","123456",6000);

JavaBean：

package com.ycy4;

import java.sql.SQLException;
import java.util.List;

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

import com.util.DBUtil;
import com.ycy4.Card;

public class Card {
	private String idcard;
	private String username;
	private String password;
	private int money;
	public Card() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Card( String username, String password) {
		super();
		this.username = username;
		this.password = password;
	}
	public String getIdcard() {
		return idcard;
	}
	public void setIdcard(String idcard) {
		this.idcard = idcard;
	}
	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 int getMoney() {
		return money;
	}
	public void setMoney(int money) {
		this.money = money;
	}
	
	//检查账户
	public boolean check(String username,String password) throws SQLException {
		boolean result = false;
		QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource());
		String sql = "select * from goddess";
		BeanListHandler<Card> beanListHandler = new BeanListHandler<>(Card.class);
		List<Card> query = queryRunner.query(sql, beanListHandler);
		for(Card card : query) {
			if((username.equals(card.getUsername())&&password.equals(card.getPassword()))) {
				result = true;
				break;
			}else {
				result = false;
			}
		}
		return result;
		
		
	}
	
	//查询账户余额
	public String selectMoney(String username,String password) throws SQLException {
		if(check(username, password)) {
			QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource());
			String sql = "select * from goddess where username = ?";
			Object[] param = {username};
			BeanHandler<Card> beanHandler = new BeanHandler<>(Card.class);
			Card card = queryRunner.query(sql, beanHandler, param);
			int money = card.getMoney();
			String str1 = "账户余额为:" + money;
			return str1;
		}else {
			String str2 = "账号或密码错误！";
			return str2;
		}
	}
	
	//存钱
	public String saveMoney(String username,String password,int money) throws SQLException {
		
		if(check(username, password)) {
			QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource());
			String sql = "update goddess set money = money + ? where username = ?";
			Object[] params = {money,username};
			queryRunner.update(sql, params);
			String select = selectMoney(username, password);
			String str1 = "存入" + money + " " +  select;
			return str1;
		}else {
			String str2 = "账号或密码错误！";
			return str2;
		}
		
	}
	//取钱
	public String removeMoney(String username,String password,int money) throws SQLException {
		
		if(check(username, password)) {
			QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource());
			String sql = "update goddess set money = money - ? where username = ?";
			Object[] params = {money,username};
			queryRunner.update(sql, params);
			String select = selectMoney(username, password);
			String str1 = "取出" + money + " " + select;
			return str1;
		}else {
			String str2 = "账号或密码错误！";
			return str2;
		}
		
	}
	
}

测试类：

package com.ycy4;

import java.sql.SQLException;

public class Test01 {
	public static Card card = new Card("sx", "123654");
	
	public static void main(String[] args) throws SQLException {
		Test01.select();
		//Test01.saveMoney(520);
		//Test01.removeMoney(2000);
		
	}
	public static void select() throws SQLException {
		String selectMoney = card.selectMoney(card.getUsername(), card.getPassword());
		System.out.println(selectMoney);
	}
	
	public static void saveMoney(int money) throws SQLException {
		String saveMoney = card.saveMoney(card.getUsername(),card.getPassword(), money);
		System.out.println(saveMoney);
	}
	
	public static void removeMoney(int money) throws SQLException {
		String removeMoney = card.removeMoney(card.getUsername(), card.getPassword(), money);
		System.out.println(removeMoney);
	}
	
}