ubuntu

# ubuntu

## DBUtils 基本介绍

commons-dbutils是Apache组织提供的一个开源JDBC工具类库，它是对JDBC的简单封装。简化了jdbc编码的工作量

## 地址

![image-20201217194115785](ubuntu.assets/image-20201217194115785.png)

## **代码**

update()方法可用于Insert、update、delete

```java

public class JDBCTest06 {

// 删除delete

public JDBCTest06() throws SQLException {

// 1. 创建QueryRunner 的实现类

QueryRunner queryRunner = new QueryRunner();

// 2. 使用其update 方法

String sql = "DELETE FROM customers " + "WHERE id IN(?,>)";

DataSource dataSource = new ComboPooledDataSource("helloc3p0");

// 3. 在连接池获取连接

Connection connection = dataSource.getConnection();

queryRunner.update(connection,sql,12,13); // update方法可用于删除、更新、添加

connection.close();

}

```

通过实现ResultSetHandler接口实现查询

创建 ResultSetHandler接口的实现类，实现handle方法，queryRunner.query()的返回值取决于handle的返回值

```java

public class DBUtilsTest {

QueryRunner queryRunner = new QueryRunner();

// 1. 创建 ResultSetHandler接口的实现类，实现handle方法，queryRunner.query()的返回值取决于handle的返回值

class MyResultSetHandler implements ResultSetHandler{

@Override

public Object handle(ResultSet rs) throws SQLException {

List<Customer> customers = new ArrayList<>();

while (rs.next()){

Integer id = rs.getInt(1);

String name = rs.getString(2);

String email = rs.getString(3);

Date birth = rs.getDate(4);

Customer customer = new Customer(id,name,email,birth);

customers.add(customer);

}

return customers;

}

public void testQuery() throws SQLException {

Connection connection = null;

String sql = "select id,name,email,birth" + "from customers";

Object object = queryRunner.query(connection, sql, new MyResultSetHandler());

System.out.println(object);

connection.close();

}

```

queryRunner.query()源码分析

```csharp

//1. QueryRunner 类的query()方法

public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {

return this.<T>query(conn, false, sql, rsh, params);// 返回值是调用当前的类query的重载方法

}

// 2.当前的类query的重载方法

private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)

throws SQLException {

PreparedStatement stmt = null;

ResultSet rs = null;

T result = null;

try {

stmt = this.prepareStatement(conn, sql);

this.fillStatement(stmt, params);

rs = this.wrap(stmt.executeQuery()); // wrap() 返回的是ResultSet

result = rsh.handle(rs); // handle()是ResultSetHandler接口定义的方法

} catch (SQLException e) {

this.rethrow(e, sql, params);

} finally {

try {

close(rs);

} finally {

close(stmt);

if (closeConn) {

close(conn);

}

return result;

}

```

通过BeanHandler类实现查询

把结果集的第一条记录转为创建BeanHandler对象时传入的class参数对应的对象

```java

public void testBeanHandler() throws SQLException {

QueryRunner queryRunner = new QueryRunner();

Connection connection = null;

String sql = "select id,name,email,birth" + "from customers where id >= ?";

Object object = queryRunner.query(connection, sql, new BeanHandler(Customer.class),5);

System.out.println(object);

connection.close();

}

```

通过BeanListHandler类实现查询

把结果集转为一个List，该List不为null，但可能为空集合（size()方法返回为0），若SQL语句有查询记录，List中存放创建BeanListHandler转入Class对象对应的对象

```java

public void testBeanListHandler() throws SQLException {

QueryRunner queryRunner = new QueryRunner();

Connection connection = null;

String sql = "select id,name,email,birth" + "from customers";

List<Customer> customers = queryRunner.query(connection,sql,new BeanListHandler<Customer>(Customer.class));

Object object = queryRunner.query(connection, sql, new BeanHandler(Customer.class),5);

System.out.println(object);

connection.close();

}

```

通过MapHandler类实现查询

返回SQL对应的第一条记录对应的Map对象，键值对：键SQL查询的列名(不是别名)，列的值

```dart

public void testMapHandler() throws SQLException {

QueryRunner queryRunner = new QueryRunner();

Connection connection = null;

String sql = "select id,name,email,birth" + "from customers";

Map<String,Object> customers = queryRunner.query(connection,sql,new MapHandler());

Object object = queryRunner.query(connection, sql, new BeanHandler(Customer.class),5);

System.out.println(object);

connection.close();

}

```

通过MapListHandler类实现查询

将结果集转为一个Map的List，Map对应查询的一条记录：键值堆：键SQL查询的列名（不是列的别名），值：列的值，而MapListHandler：返回的多条记录对应的Map的集合

```dart

public void testMapListHandler() throws SQLException {

QueryRunner queryRunner = new QueryRunner();

Connection connection = null;

String sql = "select id,name,email,birth" + "from customers";

List<Map<String,Object>> result = queryRunner.query(connection,sql,new MapListHandler());

Object object = queryRunner.query(connection, sql, new BeanHandler(Customer.class),5);

System.out.println(object);

connection.close();

}

```

ScalarHandler：把结果集转为一个数值（可以是任意基本数据类型和字符串，Date等）返回

```dart

public void ScalarHandler() throws SQLException {

QueryRunner queryRunner = new QueryRunner();

Connection connection = null;

String sql = "select name" + "from customers"; // 如果是两列的情况返回一列

List<Map<String,Object>> result = queryRunner.query(connection,sql,new MapListHandler());

Object object = queryRunner.query(connection, sql, new ScalarHandler<>(),5);

System.out.println(object);

connection.close();

}

```

## 做的完善的欣知商城

entity

```Java

package com.xinzhi.entity;

import com.xinzhi.Cart;

/**

* @author 杨智超

* @date 2020/12/17

public class User {

private int id;

//用户名

private String userName;

//密码

private String passWord;

//地址

private String site;

//余额

private double balance;

private Cart cart = new Cart();

public String getUserName() {

return userName;

}

public void setUserName(String userName) {

this.userName = userName;

}

public String getPassWord() {

return passWord;

}

public void setPassWord(String passWord) {

this.passWord = passWord;

}

public String getSite() {

return site;

}

public void setSite(String site) {

this.site = site;

}

public double getBalance() {

return balance;

}

public void setBalance(double balance) {

this.balance = balance;

}

public Cart getCart() {

return cart;

}

public void setCart(Cart cart) {

this.cart = cart;

}

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

@Override

public String toString() {

return "User{" +

"id=" + id +

", userName='" + userName + '\'' +

", passWord='" + passWord + '\'' +

", site='" + site + '\'' +

", balance=" + balance +

", cart=" + cart +

'}';

}

```

service

```Java

package com.xinzhi.service;

import com.xinzhi.Order;

import com.xinzhi.dao.impl.FileUserDaoImpl;

import com.xinzhi.dao.impl.MysqlUserDaoImpl;

import com.xinzhi.entity.User;

import com.xinzhi.dao.UserDao;

import com.xinzhi.util.ScannerUtil;

import java.util.List;

/**

* @author 杨智超

* @date 2020/12/17

public class UserService {

UserDao userDao = new MysqlUserDaoImpl();

public boolean longin(User user2){

System.out.print("请输入用户名：");

String userName = ScannerUtil.getInput();

System.out.println();

System.out.print("请输入密码：");

String passWord = ScannerUtil.getInput();

User user = userDao.getUsersByName(userName);

if (!userName.equals(user.getUserName())){

System.out.println("用户不存在，登录失败");

return true;

}

if (!passWord.equals(user.getPassWord())){

System.out.println("密码不对，登录失败");

return false;

}

user2.setUserName(userName);

user2.setPassWord(passWord);

System.out.println("登录成功欢迎" + user2.getUserName() +"用户的到来，祝您有个好的购物心情！");

return true;

}

public boolean regist(){

System.out.print("请输入用户名：");

String userName = ScannerUtil.getInput();

System.out.println();

System.out.print("请输入密码：");

String passWord = ScannerUtil.getInput();

System.out.println();

System.out.println("请输入您的收货地址：");

String site = ScannerUtil.getInput();

User user = new User();

//创建用户

user.setUserName(userName);

user.setPassWord(passWord);

user.setSite(site);

user.setBalance(0);

userDao.saveUser(user,true);

System.out.println("注册成功");

return true;

}

//查看购物车

public void lookAtCart(User user){

List<Order> cartMessage = user.getCart().getCartMessage();

System.out.println("以下为购物车：");

for (int i = 0; i < cartMessage.size(); i++) {

System.out.println((i+1) + ":" +

cartMessage.get(i).getGoodsName() + "\t" +

cartMessage.get(i).getGoodsPrice() + "\t" +

cartMessage.get(i).getGoodsCount() + "\t" +

cartMessage.get(i).getTotalMoney() + "\t"

);

}

System.out.println("--------------------------------\n");

}

//结账

public void pay(User user2) {

List<Order> cartMessage = user2.getCart().getCartMessage();

double cartTotalMoney = 0;

for (Order order : cartMessage) {

cartTotalMoney += order.getTotalMoney();

}

User user = userDao.getUsersByName(user2.getUserName());

if (user.getBalance() >= cartTotalMoney){

double balance = user.getBalance() - cartTotalMoney;

userDao.updateBalance(user2.getUserName(),balance);

user.getCart().clear();

System.out.println("支付成功");

System.out.println("本次支付：" + cartTotalMoney + "，您的余额剩余：" + balance + "元");

}else {

System.out.println("余额不足，请在充值");

}

//查看用户信息

public void userManger(User user2){

User user = userDao.getUsersByName(user2.getUserName());

System.out.println("用户名：" + user.getUserName());

System.out.println("剩余金额：" + user.getBalance());

}

//充值

public void showUserManger(User user2){

User user = userDao.getUsersByName(user2.getUserName());

System.out.println("当前余额：" + user.getBalance() + "元");

System.out.println("请输入要充值的金额");

String input = ScannerUtil.getInput();

double newBalance = user.getBalance() + Double.parseDouble(input);

userDao.updateBalance(user.getUserName(),newBalance);

System.out.println("剩余金额：" + newBalance + "元。");

}

public boolean addGoodsToCart(String id, int goodCount,User user){

user.getCart().addGoods(id, goodCount);

return true;

}

```

dao

![image-20201217194319164](ubuntu.assets/image-20201217194319164.png)

UserDao接口

```Java

package com.xinzhi.dao;

import com.xinzhi.Constant;

import com.xinzhi.entity.User;

import java.io.*;

import java.util.ArrayList;

import java.util.List;

/**

* @author 杨智超

* @date 2020/12/17

public interface UserDao {

/**

* 获取所有USER

* @param url

* @return

List<User> getAllUsers(String url);

/**

* 修改价格

* @param name

* @return

User getUsersByName(String name);

/**

* 修改余额

* @param userName

* @param newBalance

void updateBalance(String userName,double newBalance);

/**

* 保存数据

* @param user

* @param append

* @return

boolean saveUser(User user,boolean append);

}

```

FileUserDaoImpl

```Java

package com.xinzhi.dao.impl;

import com.xinzhi.Constant;

import com.xinzhi.dao.UserDao;

import com.xinzhi.entity.User;

import java.io.*;

import java.util.ArrayList;

import java.util.List;

/**

* @author 杨智超

* @date 2020/12/17

public class FileUserDaoImpl implements UserDao {

@Override

public List<User> getAllUsers(String url){

List<User> users = new ArrayList<>();

try (FileReader fs = new FileReader(url);

BufferedReader bf = new BufferedReader(fs)){

String userStr = null;

while ((userStr = bf.readLine()) != null) {

String[] str = userStr.split("---");

User user = new User();

user.setUserName(str[0]);

user.setPassWord(str[1]);

users.add(user);

}

}catch (IOException ex){

ex.printStackTrace();

}

return users;

}

@Override

public User getUsersByName(String name) {

List<User> users = getAllUsers(Constant.BASE_PATH);

for (User user : users) {

if (user.getUserName().equals(name)) {

return user;

}

return null;

}

@Override

//修改余额

public void updateBalance(String userName,double newBalance){

List<User> allUsers = getAllUsers(Constant.BASE_PATH);

if (userName == null){

return;

}

for (int i = 0; i < allUsers.size(); i++) {

if (userName.equals(allUsers.get(i).getUserName())){

allUsers.get(i).setBalance(newBalance);

}

if (i == 0){

saveUser(allUsers.get(i),false);

}else {

saveUser(allUsers.get(i),true);

}

@Override

//保存数据

public boolean saveUser(User user,boolean append){

FileWriter fw = null;

BufferedWriter bw = null;

try {

fw = new FileWriter(Constant.BASE_PATH,true);

bw = new BufferedWriter(fw);

bw.append(user.getUserName()).append("---").append(user.getPassWord()).append("---").append(user.getSite());

bw.newLine();

bw.flush();

}catch (IOException ex){

ex.printStackTrace();

}finally {

try {

if (bw != null) {

bw.close();

}

if (fw != null) {

fw.close();

}

}catch (IOException ex){

ex.printStackTrace();

}

return true;

}

```

MysqlUserDaoImpl

```Java

package com.xinzhi.dao.impl;

import com.xinzhi.dao.UserDao;

import com.xinzhi.entity.User;

import com.xinzhi.util.PollUtil;

import org.apache.commons.dbutils.QueryRunner;

import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

/**

* @author 杨智超

* @date 2020/12/17

public class MysqlUserDaoImpl implements UserDao {

ResultSetHandler<User> userHandler = new ResultSetHandler<User>() {

@Override

public User handle(ResultSet resultSet) throws SQLException {

User user = new User();

resultSet.next();

user.setId(resultSet.getInt("id"));

user.setUserName(resultSet.getString("name"));

user.setPassWord(resultSet.getString("password"));

user.setSite(resultSet.getString("site"));

return null;

}

};

ResultSetHandler<List<User>> userListHandler = new ResultSetHandler<List<User>>() {

@Override

public List<User> handle(ResultSet resultSet) throws SQLException {

List<User> users = new ArrayList<>();

while (resultSet.next()) {

User user = new User();

user.setId(resultSet.getInt("id"));

user.setUserName(resultSet.getString("name"));

user.setPassWord(resultSet.getString("password"));

user.setSite(resultSet.getString("site"));

users.add(user);

}

return users;

}

};

@Override

public List<User> getAllUsers(String url) {

QueryRunner runner = new QueryRunner(PollUtil.getDataSource());

List<User> users = null;

try {

users = runner.query("select * from comsumer",userListHandler);

} catch (SQLException throwables) {

throwables.printStackTrace();

}

return users;

}

@Override

public User getUsersByName(String name) {

QueryRunner runner = new QueryRunner(PollUtil.getDataSource());

User user = null;

try {

user = (User) runner.query("select * from comsumer where name=?",userListHandler,name);

} catch (SQLException throwables) {

throwables.printStackTrace();

}

return user;

}

@Override

public void updateBalance(String userName, double newBalance) {

QueryRunner runner = new QueryRunner(PollUtil.getDataSource());

try {

runner.update("update consumer set site=? where name=?",newBalance,userName);

} catch (SQLException throwables) {

throwables.printStackTrace();

}

@Override

public boolean saveUser(User user, boolean append) {

QueryRunner runner = new QueryRunner(PollUtil.getDataSource());

int rowd = 0;

try {

rowd = runner.update("insert into consumer (name,password,site) values(?,?,?)",user.getId(),user.getUserName(),user.getPassWord(),user.getSite());

} catch (SQLException throwables) {

throwables.printStackTrace();

}

return rowd > 0;

}

```

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 214,922评论 6赞 497
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 91,591评论 3赞 389
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 160,546评论 0赞 350
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 57,467评论 1赞 288
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,553评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,580评论 1赞 293
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,588评论 3赞 414
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,334评论 0赞 270
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,780评论 1赞 307
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,092评论 2赞 330
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,270评论 1赞 344
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,925评论 5赞 338
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,573评论 3赞 322
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,194评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,437评论 1赞 268
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,154评论 2赞 366
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,127评论 2赞 352

ubuntu

推荐阅读更多精彩内容