DBUtils使用详解二

一，使用遵从以下步骤：

1．加载JDBC驱动程序类，并用DriverManager来得到一个数据库连接conn。
2．实例化 QueryRunner,得到实例化对象qRunner。
3. qRunner.update()方法，执行增改删的sql命令，
    qRunner.query()方法，得到结果集。

二，实战

1，连接类ConnectDb:import java.sql.DriverManager;

import java.sql.SQLException;
import java.sql.Connection; 

public class ConnectDb {
	private static String driveClassName = "com.mysql.jdbc.Driver";
	private static String url = "jdbc:mysql://192.168.1.161:3306/test?useUnicode=true&characterEncoding=utf8"; 
	
	private static String user = "root";
	private static String password = "e-playnow";
	
	public static Connection Connect(){
		Connection conn = null;
		
		//load driver
		try {
			Class.forName(driveClassName);
		} catch (ClassNotFoundException  e) {
			System.out.println("load driver failed!");
			e.printStackTrace();
		}
		
		//connect db
		try {
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			System.out.println("connect failed!");
			e.printStackTrace();
		}		
		
		return conn;
	}
}

数据库表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `age` tinyint(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

Bean：

package Beans;

public class UserBean {
	private int id; 
    private String name; 
    private int age;
    
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
}

2,Demo：

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import Beans.UserBean;

public class main {

	public static void main(String[] args) throws SQLException {
		insert_test();
		del_test();
	}
	
	static void insert_test() throws SQLException{
		Connection conn = ConnectDb.Connect();
        
        //创建SQL执行工具 
        QueryRunner qRunner = new QueryRunner(); 
        
        //执行SQL插入 
        int n = qRunner.update(conn, "insert into user(name,age) values('xxx',22)"); 
        System.out.println("成功插入" + n + "条数据！"); 
        
        //关闭数据库连接 
        DbUtils.closeQuietly(conn); 	
	} 
	
	static void select_test() throws SQLException{
		Connection conn = ConnectDb.Connect();
        
        //创建SQL执行工具 
        QueryRunner qRunner = new QueryRunner(); 
        
        @SuppressWarnings("unchecked")
		List<UserBean> list = (List<UserBean>) qRunner.query(conn, "select id,name,age from user", new BeanListHandler(UserBean.class)); 
        //输出查询结果 
        for (UserBean user : list) { 
                System.out.println(user.getAge()); 
        } 
        
        //关闭数据库连接 
        DbUtils.closeQuietly(conn); 
	} 

	static void update_test() throws SQLException{
		Connection conn = ConnectDb.Connect();
        
        //创建SQL执行工具 
        QueryRunner qRunner = new QueryRunner(); 
        //执行SQL插入 
        int n = qRunner.update(conn, "update user set name = 'xxx',age=28"); 
        System.out.println("成功更新" + n + "条数据！"); 
        
        //关闭数据库连接 
        DbUtils.closeQuietly(conn); 
	} 
	
	static void del_test() throws SQLException{
		Connection conn = ConnectDb.Connect();
        
        //创建SQL执行工具 
        QueryRunner qRunner = new QueryRunner(); 
        //执行SQL插入 
        int n = qRunner.update(conn, "DELETE from user WHERE name='xxx';"); 
        System.out.println("删除成功" + n + "条数据！"); 
        
        //关闭数据库连接 
        DbUtils.closeQuietly(conn); 
	} 
}