JDBC-QueryRunner操作数据库

首先将dbutils导入路径

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Handler;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import com.atguigu.bean.Customer;
import com.atguigu.util.JDBCUtil;

public class QueryRunnerTest {
	//插入测试
	@Test
	public void test1() {
		Connection conn = null;
		try {
			//QueryRunner是一个操作数据的工具类
			QueryRunner run = new QueryRunner();
			//获得数据库连接
			conn = JDBCUtil.getConnection1();
			String sql = "insert into customers(name,email,birth)values(?,?,?)";
			//通过QueryRunner实例的引用 run来修改数据
			int update = run.update(conn, sql, "王王","848484@qq.com","1997-9-7");
			System.out.println("成功插入语句：" + update);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				//Dbutils类可以直接用来关闭数据
				DbUtils.close(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	//查询操作
	@Test
	public void test2() {
		Connection conn = null;
		try {
			//获取数据库连接
			conn = JDBCUtil.getConnection1();
			String sql = "select * from customers where id = ?";
			//将QueryRunner类实例化
			QueryRunner run = new QueryRunner();
			//创建一个结果集的处理者
			BeanHandler<Customer> handler = new BeanHandler<Customer>(Customer.class);
			
			Customer query = run.query(conn, sql, handler, 29);
			
			System.out.println(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
	//查询多个结果
	@Test
	public void test3() {
		Connection conn = null;
		try {
			conn = JDBCUtil.getConnection1();
			String sql = "select * from customers where id < ?";
			QueryRunner run = new QueryRunner();
			BeanListHandler<Customer> handler = new BeanListHandler<Customer>(Customer.class);
			List<Customer> query = run.query(conn, sql, handler, 28);
			query.forEach(System.out::println);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	//查询特殊值
	@Test
	public void test4() {
		Connection conn = null;
		try {
			conn = JDBCUtil.getConnection1();
			QueryRunner run = new QueryRunner();
			String sql = "select Count(*) from customers";
			ScalarHandler handler = new ScalarHandler();
			Long  query = (Long) run.query(conn, sql, handler);
			System.out.println("有几条语句："+query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			//JDBCUtil.closeResourse(conn, null);
		}
	}