Spring（二）继承jdbcDaoSupport的方式实现(增删改查)

一 首先创建数据库表和相应的字段,并创建约束

二 建立项目,导入jar包(ioc,aop,dao,数据库驱动,连接池)并且将applicationContext.xml文件放在src下

三 开启组件扫描,并且配置数据源

四 编写一个实体类(提供get set)方法,toString(),有参和无参以及序列化

五 设置一个接口根据银行账号完成增删改查

六 实现Dao接口,继承jdbcDaoSupport,在实现类上加上对应的标注,将实现类对象放入容器中,给jdbcDaoSupport赋值dataSource

七 测试结果

applicationContext.xml的配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd">
    <!-- 开启组件扫描 -->
    <context:component-scan base-package="com.xdl"></context:component-scan>
    <!-- 引入外部db.properties -->
    <context:property-placeholder location="classpath:db.properties"/>
    <!-- 配置数据源 -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${driverClassName}"></property>
        <property name="url" value="${url}"></property>
        <property name="username" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>
</beans>

数据库表

drop table xdl_bank_account cascade constraints;
create table xdl_bank_account(
        id number constraint xdl_bank_account_id_pk primary key,
        acc_no varchar2(30) constraint xdl_bank_account_acc_no_uk unique,
        acc_password varchar2(30),
        acc_money number
    );
drop sequence xdl_bank_account_id_seq;
create sequence xdl_bank_account_id_seq;
insert into xdl_bank_account values (xdl_bank_account_id_seq.nextval,'acc_abc1','1231',1234561);

Bean

package com.xdl.bean;

import java.io.Serializable;

public class XdlBankAccount implements Serializable {
    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    private int id;
    private String acc_no;
    private String acc_password;
    private String acc_money;

    public XdlBankAccount(int id) {
        super();
        this.id = id;
    }
    
    public XdlBankAccount(String acc_no, String acc_password, String acc_money) {
        super();
        this.acc_no = acc_no;
        this.acc_password = acc_password;
        this.acc_money = acc_money;
    }

    public XdlBankAccount(int id, String acc_no, String acc_password, String acc_money) {
        super();
        this.id = id;
        this.acc_no = acc_no;
        this.acc_password = acc_password;
        this.acc_money = acc_money;
    }

    public XdlBankAccount() {
        super();
        // TODO Auto-generated constructor stub
    }

    public int getId() {
        return id;
    }

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

    public String getAcc_no() {
        return acc_no;
    }

    public void setAcc_no(String acc_no) {
        this.acc_no = acc_no;
    }

    public String getAcc_password() {
        return acc_password;
    }

    public void setAcc_password(String acc_password) {
        this.acc_password = acc_password;
    }

    public String getAcc_money() {
        return acc_money;
    }

    public void setAcc_money(String acc_money) {
        this.acc_money = acc_money;
    }

    @Override
    public String toString() {
        return "XdlBankAccount [id=" + id + ", acc_no=" + acc_no + ", acc_password=" + acc_password + ", acc_money="
                + acc_money + "]\n";
    }
}

Dao

package com.xdl.dao;

import java.util.List;

import com.xdl.bean.XdlBankAccount;

public interface XdlBankAccountDao {
    // 根据银行账户acc_no查询银行账户对象
    XdlBankAccount getBankAccountByAccNo(String acc_no);

    // 根据id查询银行账户对象
    XdlBankAccount getBanAccountByAccId(int id);

    // 查询所有的银行账户
    List<XdlBankAccount> getBankAccountAll();

    // 更新数据
    int updateBankAccount(XdlBankAccount account);

    // 向银行表中插入数据
    int insertBankAccount(XdlBankAccount account);

    // 根据id删除银行账户
    int deleteBankAccount(XdlBankAccount account);
}

Dao的实现类

package com.xdl.impl;

import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import com.sun.org.apache.bcel.internal.generic.RET;
import com.xdl.bean.XdlBankAccount;
import com.xdl.dao.XdlBankAccountDao;
import com.xdl.mapper.XdlBankAccountMapper;

@Repository("bankDao")
public class XdlBankAccountDaoOracleImpl extends JdbcDaoSupport implements XdlBankAccountDao {
    @Resource(name = "dataSource")
    public void setMyDataSource(DataSource dataSource) {
        // 赋值给父类
        super.setDataSource(dataSource);
    }

    // 根据银行账户acc_no查询银行账户对象
    @Override
    public XdlBankAccount getBankAccountByAccNo(String acc_no) {
        String sql = "select * from xdl_bank_account where acc_no = ?";
        // return super.getJdbcTemplate().queryForObject(sql, requiredType, acc_no);
        try {
            return super.getJdbcTemplate().queryForObject(sql, new XdlBankAccountMapper(), acc_no);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return null;
        /**
         * Spring Dao框架没有做吧结果集翻译成对象过程
         */
    }

    // 根据id查询银行账户对象
    /*
     * @Override public XdlBankAccount getBanAccountByAccId(int id) { String sql =
     * "select * from xdl_bank_account where id = ?"; try { return
     * super.getJdbcTemplate().queryForObject(sql, new XdlBankAccountMapper(), id);
     * } catch (DataAccessException e) { e.printStackTrace(); } return null; }
     */
    // 根据id查询银行账户对象
    @Override
    public XdlBankAccount getBanAccountByAccId(int id) {
        String sql = "select * from xdl_bank_account where id = ?";
        List<XdlBankAccount> accounts = super.getJdbcTemplate().query(sql, new XdlBankAccountMapper(), id);
        return accounts.isEmpty() ? null : accounts.get(0);
    }

    // 查询所有的银行账户信息
    @Override
    public List<XdlBankAccount> getBankAccountAll() {
        String sql = "select * from xdl_bank_account";
        return super.getJdbcTemplate().query(sql, new XdlBankAccountMapper());
    }

    // 更新数据
    @Override
    public int updateBankAccount(XdlBankAccount account) {
        String sql = "update xdl_bank_account set acc_password = ?,acc_money = ? where id = ? and acc_no = ?";
        return super.getJdbcTemplate().update(sql, account.getAcc_password(), account.getAcc_money(), account.getId(),
                account.getAcc_no());
    }

    // 根据id删除银行账户
    @Override
    public int deleteBankAccount(XdlBankAccount account) {
        String sql = "delete from xdl_bank_account where id = ?";
        try {
            return super.getJdbcTemplate().update(sql, account.getId());
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // 向银行表中插入数据
    @Override
    public int insertBankAccount(XdlBankAccount account) {
        String sql = "insert into xdl_bank_account values (xdl_bank_account_id_seq.nextval,?,?,?)";
        try {
            return super.getJdbcTemplate().update(sql, account.getAcc_no(), account.getAcc_password(),
                    account.getAcc_money());
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return 0;
    }
}

RowMapper(行映射)

package com.xdl.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.xdl.bean.XdlBankAccount;

//T代表返回的类型
public class XdlBankAccountMapper implements RowMapper<XdlBankAccount> {

    @Override
    // rs是结果集,index代表数据到了第几条
    public XdlBankAccount mapRow(ResultSet rs, int index) throws SQLException {
        // 在这里写结果集中的数据如何转换成银行账户对象
        return new XdlBankAccount(rs.getInt("id"), rs.getString("acc_no"), rs.getString("acc_password"),
                rs.getString("acc_money"));
    }

}

测试类

package com.xdl.test;

import static org.junit.Assert.*;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.xdl.bean.XdlBankAccount;
import com.xdl.dao.XdlBankAccountDao;

public class Test {
    ApplicationContext ioc = new ClassPathXmlApplicationContext("applicationContext.xml");
    XdlBankAccountDao bankAccountDao = ioc.getBean("bankDao", XdlBankAccountDao.class);

    // 根据银行账户acc_no查询银行账户对象
    @org.junit.Test
    public void testGetBankAccountByAccNo() {
        XdlBankAccount bankAccount = bankAccountDao.getBankAccountByAccNo("acc_abb");
        System.out.println(bankAccount);
    }

    // 根据id查询银行账户对象
    @org.junit.Test
    public void testGetBankAccountById() {
        XdlBankAccount bankAccount = bankAccountDao.getBanAccountByAccId(2);
        System.out.println(bankAccount);
    }

    // 查询所有的银行账户信息
    @org.junit.Test
    public void testGetBankAccountAll() {
        List<XdlBankAccount> accounts = bankAccountDao.getBankAccountAll();
        System.out.println(accounts);
    }

    // 更新数据
    @org.junit.Test
    public void testUpdateBankAccount() {
        int updateBankAccount = bankAccountDao.updateBankAccount(new XdlBankAccount(2, "acc_abb", "456", "111"));
        System.out.println(updateBankAccount);
    }

    // 根据id删除银行账户数据
    @org.junit.Test
    public void testDeleteBankAccount() {
        int deleteBankAccount = bankAccountDao.deleteBankAccount(new XdlBankAccount(2));
        System.out.println(deleteBankAccount);
    }

    // 向银行表中插入数据
    @org.junit.Test
    public void testInsertBankAccount() {
        int insertBankAccount = bankAccountDao.insertBankAccount(new XdlBankAccount("wangcai", "12345", "5555"));
        System.out.println(insertBankAccount);
    }

}