Java学习笔记49（DBUtils工具类二）

时间：2021-03-17 19:13:35

上一篇文章是我们自己模拟的DBUtils工具类，其实有开发好的工具类

这里使用commons-dbutils-1.6.jar

事务的简单介绍：

在数据库中应用事务处理案例：转账案例

张三和李四都有有自己的存款

主键　　帐户名　　余额

1　　　　张三　　　1000

2　　　　李四　　　10

要从张三的账户余额中转账800到李四账户

SQL语句实现：

update xx set 余额 = 余额-800 where 主键=1

update xx set 余额 = 余额+800 where 主键=2

虽然操作成功，但是会出现问题，如果张三余额不足800，那么转账结束后余额是负数，显然有问题

而数据库本身不会判断是否为负数

所以数据库就将这两个操作包装成一个事务，执行结果有两种，成功或者失败，不可以分开执行

执行成功称：提交事务，执行失败称：回滚事务

对数据表的操作：

表的创建：

CREATE TABLE sort(

  sid INT PRIMARY KEY AUTO_INCREMENT,

  sname VARCHAR(100),

  sprice DOUBLE,

  sdesc VARCHAR(5000)

);

QueryRunner类的方法实现表的增删改：

package demo;

import java.sql.Connection;

import java.sql.SQLException;

import org.apache.commons.dbutils.DbUtils;

import org.apache.commons.dbutils.QueryRunner;

public class QueryRunnerDemo {

    private static Connection con = JDBCUtils.getConnection();

    public static void main(String[] args) throws SQLException {

        // 这三个方法不能同时执行，测试时候应该注释掉不使用的

        // 因为每一个方法最后都关闭了数据库连接

        // insert();

        // update();

        delete();

    }

    public static void insert() throws SQLException {

        QueryRunner qr = new QueryRunner();

        String sql = "INSERT INTO sort (sname,sprice,sdesc)VALUES(?,?,?)";

        Object[] params = { "篮球", 266, "体育用品" };

        int row = qr.update(JDBCUtils.getConnection(), sql, params);

        System.out.println(row);

        DbUtils.closeQuietly(con);

    }

    public static void update() throws SQLException {

        QueryRunner qr = new QueryRunner();

        String sql = "UPDATE sort SET sname=?,sprice=?,sdesc=? WHERE sid=?";

        Object[] params = { "足球", 255, "出售足球", 8 };

        int row = qr.update(con, sql, params);

        System.out.println(row);

        DbUtils.closeQuietly(con);

    }

    public static void delete() throws SQLException {

        QueryRunner qr = new QueryRunner();

        String sql = "DELETE FROM sort WHERE sid=?";

        int row = qr.update(con, sql, 8);

        System.out.println(row);

        DbUtils.closeQuietly(con);

    }

}

// 三种方法如果输出1表示执行成功，输出0表示执行失败

自定义的工具：

package demo;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.util.Properties;

public class JDBCUtils {

    private static Connection con;

    private static String driverClass;

    private static String url;

    private static String username;

    private static String password;

    static {

        try {

            readConfig();

            Class.forName(driverClass);

            con = DriverManager.getConnection(url, username, password);

        } catch (Exception ex) {

            throw new RuntimeException("数据库连接失败");

        }

    }

    private static void readConfig() throws Exception {

        InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("database.properties");

        Properties pro = new Properties();

        pro.load(in);

        driverClass = pro.getProperty("driverClass");

        url = pro.getProperty("url");

        username = pro.getProperty("username");

        password = pro.getProperty("password");

    }

    public static Connection getConnection() {

        return con;

    }

}

配置文件：database.properties

driverClass=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/mybase

username=root

password=xuyiqing

QueryRunner类查询：

这里有多种结果集的处理方式：

package demo;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.List;

import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;

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.apache.commons.dbutils.handlers.MapHandler;

import org.apache.commons.dbutils.handlers.MapListHandler;

import org.apache.commons.dbutils.handlers.ScalarHandler;

public class QueryRunnerDemo {

    private static Connection con = JDBCUtils.getConnection();

    public static void main(String[] args) throws SQLException {

        arrayHandler();

        arrayListHandler();

        beanHandler();

        beanListHandler();

        scalarHandler();

        mapHandler();

        mapListHandler();

    }

    public static void arrayHandler() throws SQLException {

        QueryRunner qr = new QueryRunner();

        String sql = "SELECT * FROM sort";

        Object[] result = qr.query(con, sql, new ArrayHandler());

        for (Object obj : result) {

            System.out.print(obj + "\t");

        }

        // 打印第一行的数据

    }

    public static void arrayListHandler() throws SQLException {

        QueryRunner qr = new QueryRunner();

        String sql = "SELECT * FROM sort";

        List<Object[]> result = qr.query(con, sql, new ArrayListHandler());

        for (Object[] objs : result) {

            for (Object obj : objs) {

                System.out.print(obj + "\t");

            }

            System.out.println();

        }

        // 打印了全部的数据

    }

    public static void beanHandler() throws SQLException {

        QueryRunner qr = new QueryRunner();

        String sql = "SELECT * FROM sort";

        Sort sort = qr.query(con, sql, new BeanHandler<Sort>(Sort.class));

        System.out.println(sort);

        // 第一行的数据变成sort对象，打印对象的toString方法

        // 这种方法需要特别注意：sort类中必须有空参构造器

    }

    public static void beanListHandler() throws SQLException {

        QueryRunner qr = new QueryRunner();

        String sql = "SELECT * FROM sort";

        List<Sort> list = qr.query(con, sql, new BeanListHandler<Sort>(Sort.class));

        for (Sort s : list) {

            System.out.println(s);

        }

        // 所有数据变成sort对象，装入list集合，打印出多个对象

    }

    public static void scalarHandler() throws SQLException {

        QueryRunner qr = new QueryRunner();

        String sql = "SELECT COUNT(*) FROM sort";

        long count = qr.query(con, sql, new ScalarHandler<Long>());

        System.out.println(count);

        // 适合于处理单结果集

    }

    public static void mapHandler() throws SQLException {

        QueryRunner qr = new QueryRunner();

        String sql = "SELECT * FROM sort";

        Map<String, Object> map = qr.query(con, sql, new MapHandler());

        for (String key : map.keySet()) {

            System.out.println(key + "\t" + map.get(key));

        }

        // 打印第一行数据，键值对

    }

    public static void mapListHandler() throws SQLException {

        QueryRunner qr = new QueryRunner();

        String sql = "SELECT * FROM sort";

        List<Map<String, Object>> list = qr.query(con, sql, new MapListHandler());

        for (Map<String, Object> map : list) {

            for (String key : map.keySet()) {

                System.out.println(key + "\t" + map.get(key));

            }

        }

        // 所有数据转成map集合，再存入List集合

    }

}

附加：

这里用到了和表名一致的一个自定义类，sort类：

package demo;

public class Sort {

    private int sid;

    private String sname;

    private double sprice;

    private String sdesc;

    public Sort(int sid, String sname, double sprice, String sdesc) {

        this.sid = sid;

        this.sname = sname;

        this.sprice = sprice;

        this.sdesc = sdesc;

    }

    public Sort() {

    }

    public int getSid() {

        return sid;

    }

    public void setSid(int sid) {

        this.sid = sid;

    }

    public String getSname() {

        return sname;

    }

    public void setSname(String sname) {

        this.sname = sname;

    }

    public double getSprice() {

        return sprice;

    }

    public void setSprice(double sprice) {

        this.sprice = sprice;

    }

    public String getSdesc() {

        return sdesc;

    }

    public void setSdesc(String sdesc) {

        this.sdesc = sdesc;

    }

    @Override

    public String toString() {

        return "Sort [sid=" + sid + ", sname=" + sname + ", sprice=" + sprice + ", sdesc=" + sdesc + "]";

    }

}

相关文章