DBUtils学习一 增删该查
2024-09-02 18:11:11
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();
}
}
}
最新文章
- css选择器万年不变的优先级和权重
- 深入理解CSS变形transform(3d)
- 【Linux】Linux 目录结构
- find常见用法
- css009 装饰网站的导航
- Swift Tips - 在 Swift 中自定义下标访问
- IOS 后台执行
- C#面向对象(一) 封装
- win2008 r2 远程桌面问题
- ruby编程语言-学习笔记5（第5章 语句和控制结构）
- [Linux]命令行模式切换
- Android 获取系统图库和相机照片 裁剪并显示
- linux 如何打包代码
- 【49】java内部类剖析
- 【JavaScript】事件捕获、事件冒泡与事件委托
- DLL 函数中使用结构体指针作函数参数（C# 调用 C++ 的 DLL）
- String 和 StringBuffer、StringBuilder
- python基础学习（十三）函数进阶
- Java8 Optional类
- java 数据类型相关的内容