欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

JavaWeb之DBUtils

程序员文章站 2024-02-29 12:05:22
...
CREATE DATABASE day0528;
USE day0528;
CREATE TABLE t_user(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(40),
 PASSWORD VARCHAR(40),
 email VARCHAR(40),
 birthday DATE
);

INSERT INTO t_user(NAME,PASSWORD,email,birthday) VALUES('小龙女','16','[email protected]','2001-1-1');
INSERT INTO t_user(NAME,PASSWORD,email,birthday) VALUES('姬如雪','17','[email protected]','2002-2-2');
INSERT INTO t_user(NAME,PASSWORD,email,birthday) VALUES('女帝','18','[email protected]','2003-3-3');

SELECT * FROM t_user;

JavaWeb之DBUtils

User.java

package zh.jdbc.demo;

import java.util.Date;

public class User {

	private Integer id;
	private String name;
	private String password;
	private String email;
	private Date birthday;// java.util.Date

	public User() {

	}

	public User(Integer id, String name, String password, String email,
			Date birthday) {
		super();
		this.id = id;
		this.name = name;
		this.password = password;
		this.email = email;
		this.birthday = birthday;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", password=" + password
				+ ", email=" + email + ", birthday=" + birthday + "]";
	}

}

DBUtilsDemo.java

package zh.dbutils.demo;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.BeanMapHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import zh.jdbc.demo.User;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * DBUtils使用:QueryRunner类和ResultSetHandler接口
 * 
 * @author ZH
 *
 */
public class DBUtilsDemo {

	// c3p0连接池
	private DataSource dataSource = new ComboPooledDataSource();

	/**
	 * ArrayHandler:将单行结果集封装成数组
	 * 
	 * @throws SQLException
	 */
	@Test
	public void query1() throws SQLException {

		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "select * from t_user where id = ?";
		Object[] array = queryRunner.query(sql, new ArrayHandler(), 1);
		System.out.println(Arrays.toString(array));
		// [1, 小龙女, 16, [email protected], 2001-01-01]
	}

	/**
	 * ArrayListHandler:将结果集中每一行数据封装到一个数组,再把数组添加到List集合
	 * 
	 * @throws SQLException
	 */
	@Test
	public void query2() throws SQLException {

		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "select * from t_user";
		List<Object[]> arrayList = queryRunner.query(sql,
				new ArrayListHandler());
		for (Object[] array : arrayList) {
			System.out.println(Arrays.toString(array));
		}
		// [1, 小龙女, 16, [email protected], 2001-01-01]
		// [2, 姬如雪, 17, [email protected], 2002-02-02]
		// [3, 女帝, 18, [email protected], 2003-03-03]
	}

	/**
	 * BeanHandler:将单行结果集封装到JavaBean
	 * 
	 * @throws SQLException
	 */
	@Test
	public void query3() throws SQLException {

		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "select * from t_user where id = ?";
		User user = queryRunner
				.query(sql, new BeanHandler<User>(User.class), 1);
		System.out.println(user);
		// User [id=1, name=小龙女, password=16, [email protected],
		// birthday=2001-01-01]
	}

	/**
	 * BeanListHanlder:将结果集中每一行数据封装到一个JavaBean对象,再将对象添加到List集合
	 * 
	 * @throws SQLException
	 */
	@Test
	public void query4() throws SQLException {

		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "select * from t_user";
		List<User> userList = queryRunner.query(sql, new BeanListHandler<User>(
				User.class));
		for (User user : userList) {
			System.out.println(user);
		}
		// User [id=1, name=小龙女, password=16, [email protected],
		// birthday=2001-01-01]
		// User [id=2, name=姬如雪, password=17, [email protected],
		// birthday=2002-02-02]
		// User [id=3, name=女帝, password=18, [email protected],
		// birthday=2003-03-03]
	}

	/**
	 * BeanMapHandler:将结果集中每一行数据封装到一个JavaBean对象,再根据指定的key将对象添加到Map集合
	 * 
	 * @throws SQLException
	 */
	@Test
	public void query5() throws SQLException {

		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "select * from t_user";
		Map<Integer, User> userMap = queryRunner.query(sql,
				new BeanMapHandler<Integer, User>(User.class, "id"));

		Set<Entry<Integer, User>> entrySet = userMap.entrySet();
		for (Entry<Integer, User> entry : entrySet) {
			System.out.println(entry.getKey() + ":" + entry.getValue());
		}
		// 1:User [id=1, name=小龙女, password=16, [email protected],
		// birthday=2001-01-01]
		// 2:User [id=2, name=姬如雪, password=17, [email protected],
		// birthday=2002-02-02]
		// 3:User [id=3, name=女帝, password=18, [email protected],
		// birthday=2003-03-03]
	}

	/**
	 * MapHandler:将单行结果集数据封装到Map集合,key是列名,value是对应的值
	 * 
	 * @throws SQLException
	 */
	@Test
	public void query6() throws SQLException {

		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "select * from t_user where id = ?";
		Map<String, Object> map = queryRunner.query(sql, new MapHandler(), 1);
		System.out.println(map);
		// {id=1, name=小龙女, password=16, [email protected], birthday=2001-01-01}
	}

	/**
	 * MapListHandler:将单行结果集数据封装到Map集合,key是列名,value是对应的值;再将Map集合添加到List集合
	 * 
	 * @throws SQLException
	 */
	@Test
	public void query7() throws SQLException {

		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "select * from t_user";
		List<Map<String, Object>> mapList = queryRunner.query(sql,
				new MapListHandler());
		for (Map<String, Object> map : mapList) {
			System.out.println(map);
		}
		// {id=1, name=小龙女, password=16, [email protected], birthday=2001-01-01}
		// {id=2, name=姬如雪, password=17, [email protected], birthday=2002-02-02}
		// {id=3, name=女帝, password=18, [email protected], birthday=2003-03-03}
	}

	/**
	 * ColumnListHandler:将结果集中某一列添加到List集合
	 * 
	 * @throws SQLException
	 */
	@Test
	public void query8() throws SQLException {

		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "select * from t_user";
		List<String> columnList = queryRunner.query(sql,
				new ColumnListHandler<String>("name"));
		System.out.println(columnList);
		// [小龙女, 姬如雪, 女帝]
	}

	/**
	 * ScalarHandler:将单行结果集中某一列字段值封装到Object
	 * 
	 * @throws SQLException
	 */
	@Test
	public void query9() throws SQLException {

		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "select * from t_user where id = ?";
		String hang = queryRunner.query(sql, new ScalarHandler<String>("name"),
				1);
		System.out.println(hang);
		// 小龙女
	}

	/**
	 * KeyedHandler:将结果集中每一行封装到一个Map集合,再根据指定key,将各个Map集合封装到一个Map集合
	 * 
	 * @throws SQLException
	 */
	@Test
	public void query10() throws SQLException {

		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "select * from t_user";
		Map<Integer, Map<String, Object>> map = queryRunner.query(sql,
				new KeyedHandler<Integer>("id"));
		Set<Entry<Integer, Map<String, Object>>> entrySet = map.entrySet();
		for (Entry<Integer, Map<String, Object>> entry : entrySet) {
			Integer key = entry.getKey();
			Map<String, Object> value = entry.getValue();
			System.out.println(key + ":" + value.toString());
		}
		// 1:{id=1, name=小龙女, password=16, [email protected], birthday=2001-01-01}
		// 2:{id=2, name=姬如雪, password=17, [email protected], birthday=2002-02-02}
		// 3:{id=3, name=女帝, password=18, [email protected], birthday=2003-03-03}
	}

}