package cn.itheima.jdbc.test;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;
import cn.itheima.jdbc.utils.C3P0Utils;
/**
* 测试DBUtils的增删改方法
* @author Administrator
*
*/
public class TestDBUtils {
/**
* 添加用户方法
*/
@Test
public void testAddUser(){
try {
//1.创建核心类QueryRunner
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
//2.编写sql语句
String sql = "insert into tbl_user values(null,?,?)";
//3.为占位符设置值
Object[] params = {"耿耿","余淮"};
//4.执行添加操作
int rows = qr.update(sql, params);
if(rows>0){
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 根据id修改用户方法
*/
@Test
public void updateById(){
try {
//1.创建核心类QueryRunner
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
//2.编写sql语句
String sql = "update tbl_user set upassword=? where uid=?";
//3.为占位符设置值
Object[] params = {"xxx",14};
//4.执行添加操作
int rows = qr.update(sql, params);
if(rows>0){
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 根据id删除用户方法
*/
@Test
public void upDeleteById(){
try {
//1.创建核心类QueryRunner
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
//2.编写sql语句
String sql = "delete from tbl_user where uid=?";
//3.为占位符设置值
Object[] params = {14};
//4.执行添加操作
int rows = qr.update(sql, params);
if(rows>0){
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
2:查询方法
package cn.itheima.jdbc.test;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
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.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import cn.itheima.domain.User;
import cn.itheima.jdbc.utils.C3P0Utils;
/**
* 测试dbutils查询操作
* @author Administrator
*
*/
public class TestDBUtils2 {
/**
* 查询所有
*/
@Test
public void testQueryAll(){
try {
//1.获取核心类queryRunner
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
//2.编写sql语句
String sql = "select * from tbl_user";
//3.执行查询操作
List<User> users = qr.query(sql, new BeanListHandler<User>(User.class));
//4.对结果集集合进行遍历
for (User user : users) {
System.out.println(user.getUname()+":"+user.getUpassword());
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 根据id查询
*/
@Test
public void testQueryUserById(){
try {
//1.获取核心类queryRunner
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
//2.编写sql语句
String sql = "select * from tbl_user where uid=?";
//3.为占位符设置值
Object[] params = {15};
//3.执行查询操作
User user = qr.query(sql, new BeanHandler<User>(User.class), params);
//4.对结果集集合进行遍历
System.out.println(user.getUname()+":"+user.getUpassword());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 查询所有用户的总个数
*/
@Test
public void getCount(){
try {
//1.获取核心类queryRunner
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
//2.编写sql语句
String sql = "select count(*) from tbl_user";
//3.执行查询操作
Long count = (Long) qr.query(sql, new ScalarHandler());
//4.对结果集集合进行遍历
System.out.println(count);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Test
public void testQueryAll1(){
try {
//1.获取核心类queryRunner
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
//2.编写sql语句
String sql = "select * from tbl_user";
//3.执行查询操作
List<Map<String, Object>> users = qr.query(sql, new MapListHandler());
//4.对结果集集合进行遍历
for (Map<String, Object> map : users) {
System.out.println(map);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Test
public void testQueryAll2(){
try {
//1.获取核心类queryRunner
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
//2.编写sql语句
String sql = "select * from tbl_user";
//3.执行查询操作
List<Object> query = qr.query(sql, new ColumnListHandler("uname"));
//4.对结果集集合进行遍历
for (Object object : query) {
System.out.println(object);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}