JAVA 基础 DBUtils的使用

• 1 DBUtils的使用 具体看注释
  

package demo1;

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

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.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

public class Test1 {

	/*
	 * 获取结果集需要使用以下方法：其实就是对集合的操作，需要多做练习即可。
	    QueryRunner.query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params)
	    需要传一个ResultSetHandler类来处理返回的结果集。
	   
  		ResultSetHandler结果集处理类
	 	ArrayHandler
		将结果集中的第一条记录封装到一个Object[]数组中，数组中的每一个元素就是这条记录中的每一个字段的值
		ArrayListHandler
		将结果集中的每一条记录都封装到一个Object[]数组中，将这些数组在封装到List集合中。
		BeanHandler
		将结果集中第一条记录封装到一个指定的javaBean中。
		BeanListHandler
		将结果集中每一条记录封装到指定的javaBean中，将这些javaBean在封装到List集合中
		ColumnListHandler
		将结果集中指定的列的字段值，封装到一个List集合中
		ScalarHandler
		它是用于单数据。例如select count(*) from 表操作。
		MapHandler
		将结果集第一行封装到Map集合中,Key 列名, Value 该列数据
		MapListHandler
		将结果集第一行封装到Map集合中,Key 列名, Value 该列数据,Map集合存储到List集合
	 * 
	 */
	public static void main(String[] args) throws Exception {
		//增删改操作
//		insert();
//		update();
//		delete();
		//查询操作
		//	1)ArrayHandler
		//		将结果集中的第一条记录封装到一个Object[]数组中，数组中的每一个元素就是这条记录中的每一个字段的值
//		Object[] objArr = arrayHandlerDemo();
//		System.out.println(Arrays.toString(objArr));
		
		//	2)ArrayListHandler
		//		将结果集中的每一条记录都封装到一个Object[]数组中，将这些数组在封装到List集合中。
//		List<Object[]> list = arrayListHandlerDemo();
//		for (Object[] objects : list) {
//			for (Object obj : objects) {
//				System.out.print(obj.toString()+ "\t");
//			}
//			System.out.println();
//		}
		
		//	3)BeanHandler
		//		将结果集中第一条记录封装到一个指定的javaBean中。
		//		JAVA BEAN要新建一个java文件，需要public修饰
//		Student1 s = beanHandlerDemo();
//		System.out.println(s.toString());
		
		// 4)BeanListHandler
		//		将结果集中每一条记录封装到指定的javaBean中，将这些javaBean在封装到List集合中
//		List<Student1> stu1 = beanListHandlerDemo();
//		for (Student1 student1 : stu1) {
//			System.out.println(student1.toString());
//		}
		
		// 5)ColumnListHandler
		//		将结果集中指定的列的字段值，封装到一个List集合中
//		List<String> list = columnListHandlerDemo();
//		for (String string : list) {
//			System.out.println(string);
//		}
		
		// 6)ScalarHandler
		//		它是用于单数据。例如select count(*) from 表操作。
//		Long count = scalarHandlerDemo();
//		System.out.println("共有记录: " + count);
		
		// 7)MapHandler
		//		将结果集第一行封装到Map集合中,Key 列名, Value 该列数据
//		Map<String, Object> map = mapHanlderDemo();
//		for(String key : map.keySet()) {
//			System.out.print(map.get(key)+"\t");
//		}
		// 8)MapListHandler
		//		将结果集第一行封装到Map集合中,Key 列名, Value 该列数据,Map集合存储到List集合
		List<Map<String, Object>> list = mapListHandlerDemo();
		for (Map<String, Object> map : list) {
			for( String s : map.keySet() ) {
				System.out.print(map.get(s)+"\t");
			}
			System.out.println();
		}
	}
	
	
	private static List<Map<String, Object>> mapListHandlerDemo() {
		Connection conn = null;
		QueryRunner qr = new QueryRunner();
		try {
			conn = JDBCutilDemo1.getConn();
			String sql = "select * from student";
			Object[] params = {2};
			List<Map<String, Object>> list = qr.query(conn, sql, new MapListHandler());
			return list;
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}


	private static Map<String, Object> mapHanlderDemo() {
		Connection conn = null;
		QueryRunner qr = new QueryRunner();
		try {
			conn = JDBCutilDemo1.getConn();
			String sql = "select * from student where id=?";
			Object[] params = {2};
			Map<String, Object> map = qr.query(conn, sql, new MapHandler(), params);
			return map;
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}


	private static Long scalarHandlerDemo() {
		Connection conn = null;
		QueryRunner qr = new QueryRunner();
		try {
			conn = JDBCutilDemo1.getConn();
			String sql = "select count(*) from student";
			Object[] params = {2};
			Long count = qr.query(conn, sql, new ScalarHandler<Long>());
			return count;
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return 0L;
	}


	private static List<String> columnListHandlerDemo() {
		Connection conn = null;
		QueryRunner qr = new QueryRunner();
		try {
			conn = JDBCutilDemo1.getConn();
			String sql = "select * from student";
			Object[] params = {2};
			List<String> list = qr.query(conn, sql, new ColumnListHandler<String>("name"));
			return list;
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}


	private static List<Student1> beanListHandlerDemo() {
		Connection conn = null;
		QueryRunner qr = new QueryRunner();
		try {
			conn = JDBCutilDemo1.getConn();
			String sql = "select * from student";
			Object[] params = {2};
			List<Student1> list = qr.query(conn, sql, new BeanListHandler<Student1>(Student1.class));
			return list;
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}


	private static Student1 beanHandlerDemo() {
		Connection conn = null;
		QueryRunner qr = new QueryRunner();
		try {
			conn = JDBCutilDemo1.getConn();
			String sql = "select * from student where id=3";
			Object[] params = {2};
			Student1 s = qr.query(conn, sql, new BeanHandler<Student1>(Student1.class));
			return s;
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
		
		return null;
	}


	private static List<Object[]> arrayListHandlerDemo() {
		Connection conn = null;
		QueryRunner qr = new QueryRunner();
		try {
			conn = JDBCutilDemo1.getConn();
			String sql = "select * from student";
			List<Object[]> list = qr.query(conn, sql, new ArrayListHandler());
			return list;
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}


	private static Object[] arrayHandlerDemo() {
		Connection conn = null;
		QueryRunner qr = new QueryRunner();
		
		try {
			conn = JDBCutilDemo1.getConn();
			String sql = "select * from student where name=?";
			Object[] params = {"张秀花"};
			Object[] arr = qr.query(conn, sql, new ArrayHandler(), params);
			return arr;
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}


	private static void delete() {
		Connection conn = null;
		try {
			//获得链接
			conn = JDBCutilDemo1.getConn();
//			System.out.println(conn);
			//创建queryrunner实例
			QueryRunner qr = new QueryRunner();
			//sql语句
			String sql = "delete from student where name=?";
			//设置object类型的参数数组
			Object[] params = {"测试姓名1"};
			//用update函数来处理增、删、改
			int row = qr.update(conn, sql, params);
			System.out.println("供应响了："+ row + "条!");
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("删除条目失败，请联系管理员。");
		}finally {
			try {
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}	
		
	}

	public static void insert() {
		
		Connection conn = null;
		try {
			//获得链接
			conn = JDBCutilDemo1.getConn();
//			System.out.println(conn);
			//创建queryrunner实例
			QueryRunner qr = new QueryRunner();
			//sql语句
			String sql = "insert into student (name, sex, score) values(?,?,?) ";
			//设置object类型的参数数组
			Object[] params = {"测试姓名1", "男", 22};
			//用update函数来处理增、删、改
			int row = qr.update(conn, sql, params);
			System.out.println("供应响了："+ row + "条!");
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("插入条目失败，请联系管理员。");
		}finally {
			try {
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		

	}
	
	public static void update() {
		Connection conn = null;
		try {
			//获得链接
			conn = JDBCutilDemo1.getConn();
//			System.out.println(conn);
			//创建queryrunner实例
			QueryRunner qr = new QueryRunner();
			//sql语句
			String sql = "update student set score=? where name=? ";
			//设置object类型的参数数组
			Object[] params = {80, "测试姓名1"};
			//用update函数来处理增、删、改
			int row = qr.update(conn, sql, params);
			System.out.println("供应响了："+ row + "条!");
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("修改条目失败，请联系管理员。");
		}finally {
			try {
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}	
	}
	
	
	
}

JAVA BEAN

eclipse 生成即可

package demo1;

import java.io.Serializable;

public class Student1 implements Serializable {

	private int id;
	private String name;
	private String sex;
	private int score;
	
	public Student1() {}

	public Student1(int id, String name, String sex, int score) {
		super();
		this.id = id;
		this.name = name;
		this.sex = sex;
		this.score = score;
	}

	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 String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public int getScore() {
		return score;
	}

	public void setScore(int score) {
		this.score = score;
	}

	@Override
	public String toString() {
		return "Student1 [id=" + id + ", name=" + name + ", sex=" + sex + ", score=" + score + "]";
	}
	
	
	
	
}