DbUtils的使用

目录

导入相关的jar包

项目的目录结构

数据库

 c3p0-config.xml

C3P0Util.java

Account.java

DbUtilsDemo.java

---

导入相关的jar包

我们使用了c3p0连接池

c3p0-0.9.5.2.jar

commons-dbutils-1.7.jar

mchange-commons-java-0.2.11.jar

mysql-connector-java-8.0.12.jar

项目的目录结构

数据库

 c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
  <default-config>
    <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/bank?useSSL=false&amp;serverTimezone=UTC</property>
    <property name="user">root</property>
    <property name="password">123456</property>
    
    <property name="initialPoolSize">10</property>
    <property name="maxIdleTime">30</property>
    <property name="maxPoolSize">100</property>
    <property name="minPoolSize">10</property>
    <property name="maxStatements">200</property>
  </default-config>
</c3p0-config>

C3P0Util.java

package com.zhujunwei.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 C3P0Util {

	static ComboPooledDataSource dataSource = null ;
	static {
		dataSource = new ComboPooledDataSource() ;
	}
	
	/**
	 * 得到Connection连接
	 * @return dataSource
	 * @throws SQLException 
	 */
	public  static Connection getConnection() throws SQLException {
		return dataSource.getConnection();
		
	}
	
	/**
	 * 得到DataSource连接资源
	 * @return dataSource
	 */
	public static DataSource getDataSource() {
		return dataSource ;
	}
	
	/**
	 * 释放资源
	 * @param conn
	 * @param st
	 * @param rs
	 */
	public static void release(Connection conn , Statement st , ResultSet rs){
		closeRs(rs);
		closeSt(st);
		closeConn(conn);
	}
	public static void release(Connection conn , Statement st){
		closeSt(st);
		closeConn(conn);
	}

	
	private static void closeRs(ResultSet rs){
		try {
			if(rs != null){
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			rs = null;
		}
	}
	
	private static void closeSt(Statement st){
		try {
			if(st != null){
				st.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			st = null;
		}
	}
	
	private static void closeConn(Connection conn){
		try {
			if(conn != null){
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			conn = null;
		}
	}
}

Account.java

package com.zhujunwei.domain;

public class Account {
	private String name ;
	private int money ;
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getMoney() {
		return money;
	}
	public void setMoney(int money) {
		this.money = money;
	}
	@Override
	public String toString() {
		return "Account [name=" + name + ", money=" + money + "]";
	}
}

DbUtilsDemo.java

package com.zhujunwei;

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

import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import com.zhujunwei.domain.Account;
import com.zhujunwei.util.C3P0Util;


public class DbUtilsDemo {
	
	/**
	 * 插入操作
	 */
	public void testInsert() {
		try {
			DataSource dataSource = C3P0Util.getDataSource();
			QueryRunner queryRunner = new QueryRunner(dataSource);
			
			String sql = "insert into account values(null,?,?)";
			queryRunner.update(sql , "mm", 111);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 删除操作
	 */
	public void testDelete() {
		try {
			DataSource dataSource = C3P0Util.getDataSource();
			QueryRunner queryRunner = new QueryRunner(dataSource);
			
			String sql = "delete from account where name = ?";
			queryRunner.update(sql , "mm");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 修改操作
	 */
	public void testUpdate() {
		try {
			DataSource dataSource = C3P0Util.getDataSource();
			QueryRunner queryRunner = new QueryRunner(dataSource);
			
			String sql = "update account set money = ? where name = ?";
			queryRunner.update(sql , 9999, "a");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 查询操作：查询单条记录
	 */
	public void testSelectOne() {
		try {
			DataSource dataSource = C3P0Util.getDataSource();
			QueryRunner queryRunner = new QueryRunner(dataSource);
			
			String sql = "select * from account where name = ?";
			Account account = queryRunner.query(sql, new BeanHandler<Account>(Account.class), "a");
			System.out.println(account.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 查询操作：查询多条记录
	 */
	@Test	
	public void testSelectMany() {
		try {
			DataSource dataSource = C3P0Util.getDataSource();
			QueryRunner queryRunner = new QueryRunner(dataSource);
			
			String sql = "select * from account";
			List<Account> list = queryRunner.query(sql, new BeanListHandler<Account>(Account.class));
			for (Account account : list) {
				System.out.println(account.toString());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}