package com.mozq.jdbc.test; import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry; 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.BeanMapHandler;
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;
import org.junit.Test; import com.mozq.domain.User;
import com.mozq.jdbc.C3P0Utils; public class DBUtilsTest_R {
/**
* 测试查询用户数量,ScalarHandler处理器，new ScalarHandler<Long>(),注意类型参数只能写Long，不能写Integer
*/
@Test
public void findAll_ScalarHandler() {
try {
//1.创建核心执行对象
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
//2.写sql
String sql = "select count(*) from t_user";
//3.准备参数
//4.执行sql，进行结果处理
Long row = queryRunner.query(sql, new ScalarHandler<Long>());
System.out.println("操作行数：" + row);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 测试根查询所有用户,MapListHandler处理器，new MapListHandler()
*/
@Test
public void findAll_MapListHandler() {
try {
//1.创建核心执行对象
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
//2.写sql
String sql = "select * from t_user";
//3.准备参数
//4.执行sql，进行结果处理
List<Map<String, Object>> users = queryRunner.query(sql, new MapListHandler());
for(Map<String, Object> user : users) {
for(Entry<String, Object> entry : user.entrySet()) {
System.out.print(entry.getKey()+":"+entry.getValue()+";");
}
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 测试根据id查询单个用户,MapHander处理器，new MapHandler()
*/
@Test
public void findById_MapHandler() {
try {
//1.创建核心执行对象
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
//2.写sql
String sql = "select * from t_user where id=?";
//3.准备参数
Object[] params = {9};
//4.执行sql，进行结果处理
Map<String, Object> user = queryRunner.query(sql, new MapHandler(), params);
for (Entry<String, Object> entry : user.entrySet()) {
System.out.println(entry.getKey()+ ":" + entry.getValue());
}
/*
id:9
name:刘备
password:liu456
*/
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 测试查询全部用户名,ColumnList处理器，按列索引处理(需要在查询语句中明确列顺序)和按列名处理
*/
@Test
public void findAllName_ColumnListHandler() {
try {
//1.创建核心执行对象
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
//2.写sql
String sql = "select password, name, id from t_user";
//3.准备参数
//4.执行sql，进行结果处理
List<String> userNames = queryRunner.query(sql, new ColumnListHandler<String>());
for (String userName : userNames) {
System.out.println(userName);
}
List<String> userNames2 = queryRunner.query(sql, new ColumnListHandler<String>("name"));
for (String userName : userNames2) {
System.out.println(userName);
} } catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 测试查询全部,BeanMap处理器，new BeanMapHandler<Integer, User>(User.class, "id")
*/
@Test
public void findAll_BeanMapHandler() {
try {
//1.创建核心执行对象
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
//2.写sql
String sql = "select id, name, password from t_user";
//3.准备参数
//4.执行sql，进行结果处理
/*用主键列来当键，完全没有问题*/
System.out.println("new BeanMapHandler<Integer, User>(User.class, \"id\")");
Map<Integer, User> users = queryRunner.query(sql, new BeanMapHandler<Integer, User>(User.class, "id"));
for (Entry<Integer, User> it : users.entrySet()) {
System.out.println(it.getValue());
} /*用可重复的列来当键，会发生记录覆盖*/
System.out.println("new BeanMapHandler<String, User>(User.class, \"name\")----------");
Map<String, User> users2 = queryRunner.query(sql, new BeanMapHandler<String, User>(User.class, "name"));
for (Entry<String, User> it : users2.entrySet()) {
System.out.println(it.getValue());
} } catch (SQLException e) {
e.printStackTrace();
//java.sql.SQLException: Cannot determine value type from string 'liu123' Query: select * from t_user Parameters: []
}
}
/**
* 测试查询全部,BeanList处理器，new BeanListHandler<User>(User.class)
*/
@Test
public void findAll_BeanListHandler() {
try {
//1.创建核心执行对象
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
//2.写sql
String sql = "select * from t_user";
//3.准备参数
//4.执行sql，进行结果处理
List<User> users = queryRunner.query(sql, new BeanListHandler<User>(User.class));
for (User user : users) {
System.out.println(user);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 测试根据id查询单个,Bean处理器，new BeanHandler<User>(User.class)
*/
@Test
public void findById_BeanHandler() {
try {
//1.创建核心执行对象
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
//2.写sql
String sql = "select * from t_user where id=?";
//3.准备参数
Object[] params = {9};
//4.执行sql，进行结果处理
User user = queryRunner.query(sql, new BeanHandler<User>(User.class), params);
System.out.println(user);//User [id=9, name=刘备, password=liu123]
} catch (SQLException e) {
e.printStackTrace();
}
}
}
package com.mozq.jdbc.test; import java.sql.SQLException; import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.junit.Test; import com.mozq.domain.User;
import com.mozq.jdbc.C3P0Utils; /**
* 测试DBUtils工具类的增删改操作
* @author jie
*
*/
public class DBUtilsTest_CUD {
/**
* 根据id删除用户
*/
@Test
public void DeleteUserById() {
try {
// 1.创建核心类
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
// 2.写sql
String sql = "delete from t_user where id=?";
// 3.准备参数
Object[] params = { 8 };
// 4.执行sql，进行结果处理
int row = queryRunner.update(sql, params);
if (row > 0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
} catch (SQLException e) {
e.printStackTrace();
}
} /**
* 根据id修改用户
*/
@Test
public void UpdateUserById() {
try {
// 1.创建核心类
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
// 2.写sql
String sql = "update t_user set password=? where id=?";
// 3.准备参数
Object[] params = { "jingdong", 8 };
int row = queryRunner.update(sql, params);
if (row > 0) {
System.out.println("修改成功");
} else {
System.out.println("修改失败");
}
} catch (SQLException e) {
e.printStackTrace();
}
} /**
* 插入用户方法
*/
@Test
public void addUser() {
try {
// 1.创建核心类
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
// 2.写sql
String sql = "insert into t_user(name, password) values(?,?)";
// 3.准备参数
Object[] params = { "京东0", "刘强东0" };
int row = queryRunner.update(sql, params);
if (row > 0) {
System.out.println("插入成功");
} else {
System.out.println("插入失败");
}
} catch (SQLException e) {
e.printStackTrace();
}
} /**
* 测试insert方法，查看返回对象
*/
@Test
public void insert2() {
try {
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "insert into t_user(id, name, password) values(?, ?, ?)";
User userObj = queryRunner.insert(sql, new BeanHandler<User>(User.class), 31, "京东5", "刘强东");
System.out.println(userObj);
} catch (SQLException e) {
e.printStackTrace();
}
}
}