DBUtils

最新推荐文章于 2024-05-02 12:00:00 发布

509Fu

最新推荐文章于 2024-05-02 12:00:00 发布

阅读量113

点赞数

文章标签： java java-ee intellij-idea

本文链接：https://blog.csdn.net/MysEaiZYH/article/details/124530550

版权

今日内容

一、DBUtils

一、DBUtils

1.分析jdbc中操作问题-抽取增删改方法的通用方法

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.itheima</groupId>
    <artifactId>day02_eesy_dbassit</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>

        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>
    </dependencies>
</project>

create table account(
	id int primary key auto_increment,
	name varchar(40),
	money float
)character set utf8 collate utf8_general_ci;

insert into account(name,money) values('aaa',1000);
insert into account(name,money) values('bbb',1000);
insert into account(name,money) values('ccc',1000);

package com.itheima.domain;

import java.io.Serializable;

/**
 * 账户的实体类
 */
public class Account implements Serializable {
    private Integer id;
    private String name;
    private Float money;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Float getMoney() {
        return money;
    }

    public void setMoney(Float money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

package com.itheima.dao.impl;

import com.itheima.dao.IAccountDao;
import com.itheima.dbassit.DBAssit;
import com.itheima.domain.Account;
import com.itheima.handler.impl.BeanHandler;
import com.itheima.handler.impl.BeanListHandler;
import com.itheima.utils.C3P0Util;

import java.util.List;

/**
 * 账户的持久层操作
 */
public class AccountDaoImpl implements IAccountDao{

    private DBAssit dbAssit = new DBAssit(C3P0Util.getDataSource());

    @Override
    public void save(Account account) {
        dbAssit.update("insert into account(name,money)values(?,?)",account.getName(),account.getMoney());
    }

    @Override
    public void update(Account account) {
        dbAssit.update("update account set name=?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
    }

    @Override
    public void delete(Integer accountId) {
        dbAssit.update("delete from account where id=?",accountId);
    }

    @Override
    public Account findById(Integer accountId) {
        return (Account)dbAssit.query("select * from account where id = ?",new BeanHandler(Account.class),accountId);
    }

    @Override
    public List<Account> findAll() {
        return (List<Account>)dbAssit.query("select * from account",new BeanListHandler(Account.class));
    }
}

package com.itheima.dbassit;

import com.itheima.handler.ResultSetHandler;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * 封装的工具类
 */
public class DBAssit {

    private DataSource dataSource;

    public DBAssit(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * 执行增删改的方法
     * @param sql
     * @param params
     * @return
     */
    public int update(String sql,Object... params){
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            //1.得到连接
            conn = dataSource.getConnection();
            //2.使用连接和参数的sql语句创建预处理对象
            pstm = conn.prepareStatement(sql);
            //3.得到sql语句参数的源信息（有几个参数，都什么类型等等）
            ParameterMetaData pmd = pstm.getParameterMetaData();
            //4.判断语句中参数的个数和方法参数params的个数是否一致，不一致肯定没法执行
            int parameterCount = pmd.getParameterCount();//参数的个数（问号的个数)
            if(parameterCount > 0) {
                if (params == null) {
                    throw new NullPointerException("没有sql语句执行必须的参数");
                }
                if (params.length != parameterCount) {
                    throw new RuntimeException("传入的参数个数和语句所需的参数个数不一致，语句无法执行");
                }
            }
            //5.给sql语句的参数赋值
            for(int i=0;i<parameterCount;i++){
                pstm.setObject(i+1,params[i]);
            }
            //6.执行语句
            int res = pstm.executeUpdate();
            //7.返回执行结果
            return res;
        }catch (Exception e){
            throw new RuntimeException(e);
        }finally {
            release(conn,pstm,null);
        }
    }

    private void release(Connection conn,PreparedStatement pstm,ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }

        if(pstm != null){
            try {
                pstm.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                conn.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
}

2.抽取查询方法-编写封装一个实体对象的结果集处理器

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<default-config>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/eesy</property>
		<property name="user">root</property>
		<property name="password">1234</property>
		<property name="acquireIncrement">5</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">5</property>
		<property name="maxPoolSize">20</property>
	</default-config>
</c3p0-config>

package com.itheima.utils;


import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * C3P0
 *
 */
public class C3P0Util {

	private static ComboPooledDataSource ds = new ComboPooledDataSource();
	
	public static DataSource getDataSource(){
		return ds;
	}
	
	public static Connection getConnection() throws SQLException{
		return ds.getConnection();
	}

	public static void main(String[] args) {
		System.out.println(getDataSource());
	}
}

package com.itheima;

import com.itheima.dao.IAccountDao;
import com.itheima.dao.impl.AccountDaoImpl;
import com.itheima.domain.Account;

import java.util.List;

/**
 * 测试
 */
public class Test {

    public static void main(String[] args) {

        Account account = new Account();
        account.setName("ccc");
        account.setMoney(1234f);
        IAccountDao accountDao = new AccountDaoImpl();
        accountDao.save(account); 
    }
}

package com.itheima.dbassit;

import com.itheima.handler.ResultSetHandler;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * 封装的工具类
 */
public class DBAssit {

    private DataSource dataSource;

    public DBAssit(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * 执行增删改的方法
     * @param sql
     * @param params
     * @return
     */
    public int update(String sql,Object... params){
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            //1.得到连接
            conn = dataSource.getConnection();
            //2.使用连接和参数的sql语句创建预处理对象
            pstm = conn.prepareStatement(sql);
            //3.得到sql语句参数的源信息（有几个参数，都什么类型等等）
            ParameterMetaData pmd = pstm.getParameterMetaData();
            //4.判断语句中参数的个数和方法参数params的个数是否一致，不一致肯定没法执行
            int parameterCount = pmd.getParameterCount();//参数的个数（问号的个数)
            if(parameterCount > 0) {
                if (params == null) {
                    throw new NullPointerException("没有sql语句执行必须的参数");
                }
                if (params.length != parameterCount) {
                    throw new RuntimeException("传入的参数个数和语句所需的参数个数不一致，语句无法执行");
                }
            }
            //5.给sql语句的参数赋值
            for(int i=0;i<parameterCount;i++){
                pstm.setObject(i+1,params[i]);
            }
            //6.执行语句
            int res = pstm.executeUpdate();
            //7.返回执行结果
            return res;
        }catch (Exception e){
            throw new RuntimeException(e);
        }finally {
            release(conn,pstm,null);
        }
    }

    public Object query(String sql, ResultSetHandler rsh, Object... params){
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            //1.得到连接
            conn = dataSource.getConnection();
            //2.使用连接和参数的sql语句创建预处理对象
            pstm = conn.prepareStatement(sql);
            //3.得到sql语句参数的源信息（有几个参数，都什么类型等等）
            ParameterMetaData pmd = pstm.getParameterMetaData();
            //4.判断语句中参数的个数和方法参数params的个数是否一致，不一致肯定没法执行
            int parameterCount = pmd.getParameterCount();//参数的个数（问号的个数)
            if(parameterCount > 0) {
                if (params == null) {
                    throw new NullPointerException("没有sql语句执行必须的参数");
                }
                if (params.length != parameterCount) {
                    throw new RuntimeException("传入的参数个数和语句所需的参数个数不一致，语句无法执行");
                }
            }
            //5.给sql语句的参数赋值
            for(int i=0;i<parameterCount;i++){
                pstm.setObject(i+1,params[i]);
            }
            //6.执行语句
            rs = pstm.executeQuery();
            //7.返回执行结果
            return rsh.handle(rs);
        }catch (Exception e){
            throw new RuntimeException(e);
        }finally {
            release(conn,pstm,rs);
        }
    }


    private void release(Connection conn,PreparedStatement pstm,ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }

        if(pstm != null){
            try {
                pstm.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                conn.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
}

package com.itheima.handler;

import java.sql.ResultSet;

/**
 * 结果集封装的接口
 */
public interface ResultSetHandler {

    /**
     * 结果集封装的方法
     * @param rs
     * @return
     */
    Object handle(ResultSet rs);
}

package com.itheima.handler.impl;

import com.itheima.handler.ResultSetHandler;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

/**
 * 结果集封装的具体实现类。
 * 此类实现的是把一个结果集rs的内容封装到一个指定的实体类对象中
 * 使用要求：
 *      实体类中的属性必须和表中的列名一致（sql语句查询出来的列名一致）
 * @param <T>
 */
public class BeanHandler implements ResultSetHandler {

    private Class domainClass;

    public BeanHandler(Class domainClass) {
        this.domainClass = domainClass;
    }

    /**
     * 把rs的内容封装到domainClass所表示的类中
     * @param rs
     * @return
     */
    @Override
    public Object handle(ResultSet rs) {
        try {
            //1.创建一个实体类对象
            Object bean = domainClass.newInstance();
            //2.判断是否有结果集
            if(rs.next()){
                //3.得到结果集rs中所有的列名
                //要想得到列名，得先得到结果集的源信息
                ResultSetMetaData rsmd = rs.getMetaData();
                //得到源信息之后，还要得到有多少列
                int columnCount = rsmd.getColumnCount();
                //遍历列数
                for(int i=1;i<=columnCount;i++){
                    //得到每列的名称
                    String columnName = rsmd.getColumnName(i);
                    //列名其实就是实体类的属性名称，于是就可以使用列名得到实体类中属性的描述器
                    PropertyDescriptor pd = new PropertyDescriptor(columnName,domainClass);//实体类中定义的私有类成员和它的get以及set方法
                    //获取属性的写入方法(set方法)
                    Method method = pd.getWriteMethod();
                    //获取当前列名所对应的值
                    Object columnValue = rs.getObject(columnName);
                    //通过执行写方法把得到的值给属性赋上
                    method.invoke(bean,columnValue);
                }
            }
            //4.返回
            return bean;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }
}

package com.itheima.dao.impl;

import com.itheima.dao.IAccountDao;
import com.itheima.dbassit.DBAssit;
import com.itheima.domain.Account;
import com.itheima.handler.impl.BeanHandler;
import com.itheima.handler.impl.BeanListHandler;
import com.itheima.utils.C3P0Util;

import java.util.List;

/**
 * 账户的持久层操作
 */
public class AccountDaoImpl implements IAccountDao{

    private DBAssit dbAssit = new DBAssit(C3P0Util.getDataSource());

    @Override
    public void save(Account account) {
        dbAssit.update("insert into account(name,money)values(?,?)",account.getName(),account.getMoney());
    }

    @Override
    public void update(Account account) {
        dbAssit.update("update account set name=?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
    }

    @Override
    public void delete(Integer accountId) {
        dbAssit.update("delete from account where id=?",accountId);
    }

    @Override
    public Account findById(Integer accountId) {
        return (Account)dbAssit.query("select * from account where id = ?",new BeanHandler(Account.class),accountId);
    }

    @Override
    public List<Account> findAll() {
        return null;
    }
}

package com.itheima;

import com.itheima.dao.IAccountDao;
import com.itheima.dao.impl.AccountDaoImpl;
import com.itheima.domain.Account;

import java.util.List;

/**
 * 测试
 */
public class Test {

    public static void main(String[] args) {

//        Account account = new Account();
//        account.setName("ccc");
//        account.setMoney(1234f);
        IAccountDao accountDao = new AccountDaoImpl();
//        accountDao.save(account);
        Account account = accountDao.findById(1);
		System.out.println(account);
    }
}

3.实现查询所有的封装以及dbutils介绍

package com.itheima.handler.impl;

import com.itheima.handler.ResultSetHandler;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

public class BeanListHandler implements ResultSetHandler {

    private Class domainClass;

    public BeanListHandler(Class domainClass) {
        this.domainClass = domainClass;
    }

    /**
     * 把rs的内容封装到domainClass所表示的类中
     * @param rs
     * @return
     */
    @Override
    public Object handle(ResultSet rs) {
        try {
            List list = new ArrayList<>();
            //2.判断是否有结果集
            while(rs.next()){
                //1.创建一个实体类对象
                Object bean = domainClass.newInstance();
                //3.得到结果集rs中所有的列名
                //要想得到列名，得先得到结果集的源信息
                ResultSetMetaData rsmd = rs.getMetaData();
                //得到源信息之后，还要得到有多少列
                int columnCount = rsmd.getColumnCount();
                //遍历列数
                for(int i=1;i<=columnCount;i++){
                    //得到每列的名称
                    String columnName = rsmd.getColumnName(i);
                    //列名其实就是实体类的属性名称，于是就可以使用列名得到实体类中属性的描述器
                    PropertyDescriptor pd = new PropertyDescriptor(columnName,domainClass);//实体类中定义的私有类成员和它的get以及set方法
                    //获取属性的写入方法(set方法)
                    Method method = pd.getWriteMethod();
                    //获取当前列名所对应的值
                    Object columnValue = rs.getObject(columnName);
                    //通过执行写方法把得到的值给属性赋上
                    method.invoke(bean,columnValue);
                }
                //把封装好的对象加入到集合中
                list.add(bean);
            }
            //4.返回
            return list;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }
}

package com.itheima.dao.impl;

import com.itheima.dao.IAccountDao;
import com.itheima.dbassit.DBAssit;
import com.itheima.domain.Account;
import com.itheima.handler.impl.BeanHandler;
import com.itheima.handler.impl.BeanListHandler;
import com.itheima.utils.C3P0Util;

import java.util.List;

/**
 * 账户的持久层操作
 */
public class AccountDaoImpl implements IAccountDao{

    private DBAssit dbAssit = new DBAssit(C3P0Util.getDataSource());

    @Override
    public void save(Account account) {
        dbAssit.update("insert into account(name,money)values(?,?)",account.getName(),account.getMoney());
    }

    @Override
    public void update(Account account) {
        dbAssit.update("update account set name=?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
    }

    @Override
    public void delete(Integer accountId) {
        dbAssit.update("delete from account where id=?",accountId);
    }

    @Override
    public Account findById(Integer accountId) {
        return (Account)dbAssit.query("select * from account where id = ?",new BeanHandler(Account.class),accountId);
    }

    @Override
    public List<Account> findAll() {
        return (List<Account>)dbAssit.query("select * from account",new BeanListHandler(Account.class));
    }
}

package com.itheima;

import com.itheima.dao.IAccountDao;
import com.itheima.dao.impl.AccountDaoImpl;
import com.itheima.domain.Account;

import java.util.List;

/**
 * 测试
 */
public class Test {

    public static void main(String[] args) {

//        Account account = new Account();
//        account.setName("ccc");
//        account.setMoney(1234f);
        IAccountDao accountDao = new AccountDaoImpl();
//        accountDao.save(account);
//        Account account = accountDao.findById(1);
//        System.out.println(account);
        List<Account> accounts = accountDao.findAll();
        System.out.println(accounts);
    }
}

4. dbutils的使用

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.itheima</groupId>
    <artifactId>day02_eesy_dbutils</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.4</version>
        </dependency>
    </dependencies>
</project>

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<default-config>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/eesy</property>
		<property name="user">root</property>
		<property name="password">1234</property>
		<property name="acquireIncrement">5</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">5</property>
		<property name="maxPoolSize">20</property>
	</default-config>
</c3p0-config>

package com.itheima.utils;


import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * C3P0
 *
 */
public class C3P0Util {

	private static ComboPooledDataSource ds = new ComboPooledDataSource();

	public static DataSource getDataSource(){
		return ds;
	}

	public static Connection getConnection() throws SQLException{
		return ds.getConnection();
	}

	public static void main(String[] args) {
		System.out.println(getDataSource());
	}
}

package com.itheima.domain;

import java.io.Serializable;

/**
 * 账户的实体类
 */
public class Account implements Serializable {
    private Integer id;
    private String name;
    private Float money;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Float getMoney() {
        return money;
    }

    public void setMoney(Float money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

package com.itheima.dao.impl;

import com.itheima.dao.IAccountDao;
import com.itheima.domain.Account;
import com.itheima.utils.C3P0Util;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.util.List;

/**
 * 账户的持久层操作
 */
public class AccountDaoImpl implements IAccountDao{

    private QueryRunner runner = new QueryRunner(C3P0Util.getDataSource());

    @Override
    public void save(Account account) {
        try {
            runner.update("insert into account(name,money)values(?,?)",account.getName(),account.getMoney());
        }catch (Exception e){
            throw new RuntimeException(e);
        }

    }

    @Override
    public void update(Account account) {
        try {
            runner.update("update account set name=?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
        }catch (Exception e){
            throw new RuntimeException(e);
        }

    }

    @Override
    public void delete(Integer accountId) {
        try {
            runner.update("delete from account where id=?",accountId);
        }catch (Exception e){
            throw new RuntimeException(e);
        }

    }

    @Override
    public Account findById(Integer accountId) {
        try {
            return (Account)runner.query("select * from account where id = ?",new BeanHandler<Account>(Account.class),accountId);
        }catch (Exception e){
            throw new RuntimeException(e);
        }

    }

    @Override
    public List<Account> findAll() {
        try {
            return (List<Account>)runner.query("select * from account",new BeanListHandler<Account>(Account.class));
        }catch (Exception e){
            throw new RuntimeException(e);
        }

    }
}

package com.itheima;

import com.itheima.dao.IAccountDao;
import com.itheima.dao.impl.AccountDaoImpl;
import com.itheima.domain.Account;

import java.util.List;

/**
 * 测试
 */
public class Test {

    public static void main(String[] args) {

//        Account account = new Account();
//        account.setName("ddd");
//        account.setMoney(2345f);
        IAccountDao accountDao = new AccountDaoImpl();
//        accountDao.save(account);
//        Account account = accountDao.findById(1);
//        System.out.println(account);
        List<Account> accounts = accountDao.findAll();
        System.out.println(accounts);
    }
}

509Fu

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
DBUtils

今日内容一、DBUtils1.分析jdbc中操作问题-抽取增删改方法的通用方法2.抽取查询方法-编写封装一个实体对象的结果集处理器3.实现查询所有的封装以及dbutils介绍4. dbutils的使用一、DBUtils1.分析jdbc中操作问题-抽取增删改方法的通用方法<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:x..
复制链接

扫一扫