Spring-DBUtils

• 核心类：

  • QueryRunner 用于执行增删改查的SQL语句

  • ResultSetHandler 这是一个接口，主要作用是将数据库返回的记录封装进实体对象

    • BeanHandler 封装返回的单个结果
    • BeanListHandler 封装返回的多个结果

• 核心方法：

  • update() 用来执行增、删、改语句
  • query() 用来执行查询语句 spring配置文件

<!--new QueryRunner-->
<bean id="queryRunner" class="org.apache.commons.dbutils.QueryRunner">
    <constructor-arg name="ds" ref="dataSource"></constructor-arg>
</bean>
<!--new 连接池-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    <!--兼容mysql5.x 8.x版本-->
    <property name="url" value="jdbc:mysql://localhost:3306/spring?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=Asia/Shanghai"></property>
    <property name="username" value="root"></property>
    <property name="password" value="root"></property>
</bean>
<!--new AccountDao-->
<bean id="accountDao" class="com.eponine.spring.dao.impl.AccountDaoImpl">
    <constructor-arg name="queryRunner" ref="queryRunner"></constructor-arg>
</bean>
<!--new accountService-->
<bean id="accountService" class="com.eponine.spring.service.impl.AccountServiceImpl">
    <property name="accountDao" ref="accountDao"></property>
</bean>

dao

private QueryRunner queryRunner;


public AccountDaoImpl(QueryRunner queryRunner) {
    this.queryRunner = queryRunner;
}

//增
@Override
public void add(Account account) {
    try {
        queryRunner.update("INSERT INTO `account`(`name`,`balance`) VALUES (?,?)"
                ,account.getName(),account.getBalance());
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
//删
@Override
public void delete(Integer id) {
    try {
        queryRunner.update("DELETE FROM `account` WHERE `id`=?",id);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
//改
@Override
public void update(Account account) {
    try {
        queryRunner.update("UPDATE `account` SET `name`=?,`balance`=? WHERE `id`=?",
                account.getName(),account.getBalance(),account.getId());
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
//id查账户
@Override
public Account select(Integer id) {
    try {
        return queryRunner.query("select * from account where id = ?",
                new BeanHandler<>(Account.class)
                ,id);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}
//查所有账户
@Override
public List<Account> selectAll(){
    try {
        return queryRunner.query("select * from account",
                new BeanListHandler<>(Account.class));
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

service

private AccountDao accountDao;

public void setAccountDao(AccountDao accountDao) {
    this.accountDao = accountDao;
}


@Override
public void add(Account account) {
    accountDao.add(account);
}