035_jdbc-mysql-dbutils的使用

1. 新建一个名叫dbutils的java项目, 使用c3p0的配置

2. 添加dbutils的相关jar

3. 使用c3p0的JDBCUtil.java

package com.lywgames.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 JDBCUtil {
	private static DataSource dataSource = null;

	static {
		dataSource = new ComboPooledDataSource();
	}
	
	public static DataSource getDataSource() {
		return dataSource;
	}
	
	/**
	 * 获取连接对象
	 */
	public static Connection getConn(){
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 释放资源
	 * @param st
	 * @param rs
	 */
	public static void release(Statement st, ResultSet rs){
		closeRs(rs);
		closeSt(st);
	}

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

4. 新建一个User.java实体类

package com.lywgames.bean;

import java.sql.Timestamp;

public class User {
	private int id;
	private String username;
	private String password;
	private Timestamp registertime;
	
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", password=" + password + ", registertime=" + registertime
				+ "]";
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	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 Timestamp getRegistertime() {
		return registertime;
	}
	public void setRegistertime(Timestamp registertime) {
		this.registertime = registertime;
	}
}

5. 使用dbutils

package com.lywgames.myjdbc;

import java.sql.SQLException;
import java.sql.Timestamp;
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.lywgames.bean.User;
import com.lywgames.util.JDBCUtil;

/**
 * QueryRunner的update方法, 可以进行插入、删除、更新操作
 * QueryRunner的query方法, 可以进行查询操作
 */
public class MyJDBC {
	public static void main(String[] args) {
		try {
			QueryRunner queryRunner = new QueryRunner();
			User user = queryRunner.query(JDBCUtil.getConn(), "select * from user where id = ?", new BeanHandler<User>(User.class), 1002);
			System.out.println(user.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		} 
	}
	
	public static void selectList() {
		try {
			QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
			List<User> list = queryRunner.query("select * from user", new BeanListHandler<User>(User.class));
			for (User user : list) {
				System.out.println(user.toString());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 
	}
	
	public static void insert() {
		try {
			// 传入数据库连接池对象, QueryRunner不会创建连接
			QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
			queryRunner.update("insert into user values (null, ?, ?, ?)", "guanyu", "123456", new Timestamp(System.currentTimeMillis()));
		} catch (SQLException e) {
			e.printStackTrace();
		} 
	}
	
	public static void update() {
		try {
			QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
			queryRunner.update("update user set username = ? where id = ?", "zhangfei", 1000);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
	}
	
	public static void delete() {
		try {
			QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
			queryRunner.update("delete from user where id = ?", 1001);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
	}		
	
}