DbUtils的使用

妖狐艹你老母 2022-03-28 05:24 213阅读 0赞

**目录**

导入相关的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

## 项目的目录结构 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyOTY1MTg3_size_16_color_FFFFFF_t_70][]

## 数据库 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyOTY1MTg3_size_16_color_FFFFFF_t_70 1][]

##  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&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();
    		}
    	}
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyOTY1MTg3_size_16_color_FFFFFF_t_70]: /images/20220328/1be4a5b1456a4398a9785c36d00829c9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyOTY1MTg3_size_16_color_FFFFFF_t_70 1]: /images/20220328/8a065dfaf41f47f286613ac7026c4d6c.png