Dbutils工具库中操作数据库的方法的使用

获取Dbutils工具库

<!-- JDBC增删改查操作库 -->
<dependency>
	<groupId>commons-dbutils</groupId>
	<artifactId>commons-dbutils</artifactId>
	<version>1.6</version>
</dependency>

使用工具库

package test.lanou3g.demo.utils;

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

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
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.junit.Assert;
import org.junit.Test;

import com.lanou3g.demo.bean.Student;
import com.lanou3g.demo.utils.DBTools;

public class DBToolsTest {

	@Test
	public void testGetConnection() {
		Connection conn = DBTools.getConnection();
		System.out.println(conn);
		Assert.assertNotNull("获取连接失败", conn);
		
		DBTools.testConnection(conn);
		
		conn = DBTools.getConnection();
		DBTools.testConnection(conn);
	}
	
	/**
	 * 查询单条数据
	 */
	@Test
	public void testDBUtilsQueryOne() {
		Connection conn = DBTools.getConnection();
		QueryRunner runner = new QueryRunner();
		String sql = "select * from student";
		Student student = null;
		ResultSetHandler<Student> rsh = new BeanHandler<Student>(Student.class);
		try {
			student = runner.query(conn, sql, rsh);
			System.out.println(student);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBTools.closeConn(conn);
		}
	}
	
	/**
	 * 查询多条数据
	 */
	@Test
	public void testDBUtilsQuery() {
		Connection conn = DBTools.getConnection();
		QueryRunner runner = new QueryRunner();
		String sql = "select * from student";
		List<Student> studentList = null;
		ResultSetHandler<List<Student>> rsh = new BeanListHandler<Student>(Student.class);
		try {
			studentList = runner.query(conn, sql, rsh);
			System.out.println(studentList);
			System.out.println(studentList.size());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBTools.closeConn(conn);
		}
	}
	
	/**
	 * 单条插入
	 */
	@Test
	public void testDBUtilsInsert() {
		Connection conn = DBTools.getConnection();
		QueryRunner runner = new QueryRunner();
		String sql = "insert into student(sname, age, gender, nick_name) values (?,?,?,?)";
		ArrayHandler handler = new ArrayHandler();
		Object[] params = new Object[]{"周伯通", 444, 3, "老顽童"};
		try {
			Object[] retArray = runner.insert(conn, sql, handler, params);
			System.out.println("插入数据的ID为：" + retArray[0]);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBTools.closeConn(conn);
		}
	}
	
	/**
	 * 批量插入
	 */
	@Test
	public void testDBUtilsBatchInsert() {
		Connection conn = DBTools.getConnection();
		QueryRunner runner = new QueryRunner();
		String sql = "insert into student(sname, age, gender, nick_name) values (?,?,?,?)";
		ArrayListHandler handler = new ArrayListHandler();
		Object[][] params = new Object[][]{
			{"黄药师", 444, 3, "东邪"},
			{"欧阳锋", 444, 3, "西毒"},
			{"段智兴", 444, 3, "南帝"},
			{"洪七公", 444, 3, "北丐"},
			{"王重阳", 444, 3, "中神通"}};
		try {
			List<Object[]> retList = runner.insertBatch(conn, sql, handler, params);
			for(int i = 0; i < retList.size(); i++) {
				System.out.println("插入的第"+(i+1)+"数据的ID为：" + retList.get(i)[0]);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBTools.closeConn(conn);
		}
	}
	
	/**
	 * 删除
	 */
	@Test
	public void testDBUtilsDelete() {
		Connection conn = DBTools.getConnection();
		QueryRunner runner = new QueryRunner();
		String sql = "delete from student where id = ?";
		Object[] params = new Object[]{113};
		try {
			int rows = runner.update(conn, sql, params);
			System.out.println("删除了" + rows + "条数据！");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBTools.closeConn(conn);
		}
	}
	
	/**
	 * 更新
	 */
	@Test
	public void testDBUtilsUpdate() {
		Connection conn = DBTools.getConnection();
		QueryRunner runner = new QueryRunner();
		String sql = "update student set age = ? where id = ?";
		Object[] params = new Object[]{999, 112};
		try {
			int rows = runner.update(conn, sql, params);
			System.out.println("更新了" + rows + "条数据！");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBTools.closeConn(conn);
		}
	}
}