继承jdbcDaoSupport的方式实现(增删改查)

时间:2024-01-21 07:27:46

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

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

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

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

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

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

七 测试结果

 

applicationContext.xml的配置

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

数据库表

 1 drop table xdl_bank_account cascade constraints;
 2 create table xdl_bank_account(
 3         id number constraint xdl_bank_account_id_pk primary key,
 4         acc_no varchar2(30) constraint xdl_bank_account_acc_no_uk unique,
 5         acc_password varchar2(30),
 6         acc_money number
 7     );
 8 drop sequence xdl_bank_account_id_seq;
 9 create sequence xdl_bank_account_id_seq;
10 insert into xdl_bank_account values (xdl_bank_account_id_seq.nextval,'acc_abc1','1231',1234561);
View Code

Bean

 1 package com.xdl.bean;
 2 
 3 import java.io.Serializable;
 4 
 5 public class XdlBankAccount implements Serializable {
 6     /**
 7      * 
 8      */
 9     private static final long serialVersionUID = 1L;
10     private int id;
11     private String acc_no;
12     private String acc_password;
13     private String acc_money;
14 
15     public XdlBankAccount(int id) {
16         super();
17         this.id = id;
18     }
19     
20     public XdlBankAccount(String acc_no, String acc_password, String acc_money) {
21         super();
22         this.acc_no = acc_no;
23         this.acc_password = acc_password;
24         this.acc_money = acc_money;
25     }
26 
27     public XdlBankAccount(int id, String acc_no, String acc_password, String acc_money) {
28         super();
29         this.id = id;
30         this.acc_no = acc_no;
31         this.acc_password = acc_password;
32         this.acc_money = acc_money;
33     }
34 
35     public XdlBankAccount() {
36         super();
37         // TODO Auto-generated constructor stub
38     }
39 
40     public int getId() {
41         return id;
42     }
43 
44     public void setId(int id) {
45         this.id = id;
46     }
47 
48     public String getAcc_no() {
49         return acc_no;
50     }
51 
52     public void setAcc_no(String acc_no) {
53         this.acc_no = acc_no;
54     }
55 
56     public String getAcc_password() {
57         return acc_password;
58     }
59 
60     public void setAcc_password(String acc_password) {
61         this.acc_password = acc_password;
62     }
63 
64     public String getAcc_money() {
65         return acc_money;
66     }
67 
68     public void setAcc_money(String acc_money) {
69         this.acc_money = acc_money;
70     }
71 
72     @Override
73     public String toString() {
74         return "XdlBankAccount [id=" + id + ", acc_no=" + acc_no + ", acc_password=" + acc_password + ", acc_money="
75                 + acc_money + "]\n";
76     }
77 }
View Code

Dao

 1 package com.xdl.dao;
 2 
 3 import java.util.List;
 4 
 5 import com.xdl.bean.XdlBankAccount;
 6 
 7 public interface XdlBankAccountDao {
 8     // 根据银行账户acc_no查询银行账户对象
 9     XdlBankAccount getBankAccountByAccNo(String acc_no);
10 
11     // 根据id查询银行账户对象
12     XdlBankAccount getBanAccountByAccId(int id);
13 
14     // 查询所有的银行账户
15     List<XdlBankAccount> getBankAccountAll();
16 
17     // 更新数据
18     int updateBankAccount(XdlBankAccount account);
19 
20     // 向银行表中插入数据
21     int insertBankAccount(XdlBankAccount account);
22 
23     // 根据id删除银行账户
24     int deleteBankAccount(XdlBankAccount account);
25 }
View Code

Dao的实现类

 1 package com.xdl.impl;
 2 
 3 import java.util.List;
 4 
 5 import javax.annotation.Resource;
 6 import javax.sql.DataSource;
 7 
 8 import org.springframework.dao.DataAccessException;
 9 import org.springframework.jdbc.core.support.JdbcDaoSupport;
10 import org.springframework.stereotype.Repository;
11 
12 import com.sun.org.apache.bcel.internal.generic.RET;
13 import com.xdl.bean.XdlBankAccount;
14 import com.xdl.dao.XdlBankAccountDao;
15 import com.xdl.mapper.XdlBankAccountMapper;
16 
17 @Repository("bankDao")
18 public class XdlBankAccountDaoOracleImpl extends JdbcDaoSupport implements XdlBankAccountDao {
19     @Resource(name = "dataSource")
20     public void setMyDataSource(DataSource dataSource) {
21         // 赋值给父类
22         super.setDataSource(dataSource);
23     }
24 
25     // 根据银行账户acc_no查询银行账户对象
26     @Override
27     public XdlBankAccount getBankAccountByAccNo(String acc_no) {
28         String sql = "select * from xdl_bank_account where acc_no = ?";
29         // return super.getJdbcTemplate().queryForObject(sql, requiredType, acc_no);
30         try {
31             return super.getJdbcTemplate().queryForObject(sql, new XdlBankAccountMapper(), acc_no);
32         } catch (DataAccessException e) {
33             e.printStackTrace();
34         }
35         return null;
36         /**
37          * Spring Dao框架没有做吧结果集翻译成对象过程
38          */
39     }
40 
41     // 根据id查询银行账户对象
42     /*
43      * @Override public XdlBankAccount getBanAccountByAccId(int id) { String sql =
44      * "select * from xdl_bank_account where id = ?"; try { return
45      * super.getJdbcTemplate().queryForObject(sql, new XdlBankAccountMapper(), id);
46      * } catch (DataAccessException e) { e.printStackTrace(); } return null; }
47      */
48     // 根据id查询银行账户对象
49     @Override
50     public XdlBankAccount getBanAccountByAccId(int id) {
51         String sql = "select * from xdl_bank_account where id = ?";
52         List<XdlBankAccount> accounts = super.getJdbcTemplate().query(sql, new XdlBankAccountMapper(), id);
53         return accounts.isEmpty() ? null : accounts.get(0);
54     }
55 
56     // 查询所有的银行账户信息
57     @Override
58     public List<XdlBankAccount> getBankAccountAll() {
59         String sql = "select * from xdl_bank_account";
60         return super.getJdbcTemplate().query(sql, new XdlBankAccountMapper());
61     }
62 
63     // 更新数据
64     @Override
65     public int updateBankAccount(XdlBankAccount account) {
66         String sql = "update xdl_bank_account set acc_password = ?,acc_money = ? where id = ? and acc_no = ?";
67         return super.getJdbcTemplate().update(sql, account.getAcc_password(), account.getAcc_money(), account.getId(),
68                 account.getAcc_no());
69     }
70 
71     // 根据id删除银行账户
72     @Override
73     public int deleteBankAccount(XdlBankAccount account) {
74         String sql = "delete from xdl_bank_account where id = ?";
75         try {
76             return super.getJdbcTemplate().update(sql, account.getId());
77         } catch (DataAccessException e) {
78             e.printStackTrace();
79         }
80         return 0;
81     }
82 
83     // 向银行表中插入数据
84     @Override
85     public int insertBankAccount(XdlBankAccount account) {
86         String sql = "insert into xdl_bank_account values (xdl_bank_account_id_seq.nextval,?,?,?)";
87         try {
88             return super.getJdbcTemplate().update(sql, account.getAcc_no(), account.getAcc_password(),
89                     account.getAcc_money());
90         } catch (DataAccessException e) {
91             e.printStackTrace();
92         }
93         return 0;
94     }
95 }
View Code

RowMapper(行映射)

 1 package com.xdl.mapper;
 2 
 3 import java.sql.ResultSet;
 4 import java.sql.SQLException;
 5 
 6 import org.springframework.jdbc.core.RowMapper;
 7 
 8 import com.xdl.bean.XdlBankAccount;
 9 
10 //T代表返回的类型
11 public class XdlBankAccountMapper implements RowMapper<XdlBankAccount> {
12 
13     @Override
14     // rs是结果集,index代表数据到了第几条
15     public XdlBankAccount mapRow(ResultSet rs, int index) throws SQLException {
16         // 在这里写结果集中的数据如何转换成银行账户对象
17         return new XdlBankAccount(rs.getInt("id"), rs.getString("acc_no"), rs.getString("acc_password"),
18                 rs.getString("acc_money"));
19     }
20 
21 }
View Code

测试类

 1 package com.xdl.test;
 2 
 3 import static org.junit.Assert.*;
 4 
 5 import java.util.List;
 6 
 7 import org.springframework.context.ApplicationContext;
 8 import org.springframework.context.support.ClassPathXmlApplicationContext;
 9 
10 import com.xdl.bean.XdlBankAccount;
11 import com.xdl.dao.XdlBankAccountDao;
12 
13 public class Test {
14     ApplicationContext ioc = new ClassPathXmlApplicationContext("applicationContext.xml");
15     XdlBankAccountDao bankAccountDao = ioc.getBean("bankDao", XdlBankAccountDao.class);
16 
17     // 根据银行账户acc_no查询银行账户对象
18     @org.junit.Test
19     public void testGetBankAccountByAccNo() {
20         XdlBankAccount bankAccount = bankAccountDao.getBankAccountByAccNo("acc_abb");
21         System.out.println(bankAccount);
22     }
23 
24     // 根据id查询银行账户对象
25     @org.junit.Test
26     public void testGetBankAccountById() {
27         XdlBankAccount bankAccount = bankAccountDao.getBanAccountByAccId(2);
28         System.out.println(bankAccount);
29     }
30 
31     // 查询所有的银行账户信息
32     @org.junit.Test
33     public void testGetBankAccountAll() {
34         List<XdlBankAccount> accounts = bankAccountDao.getBankAccountAll();
35         System.out.println(accounts);
36     }
37 
38     // 更新数据
39     @org.junit.Test
40     public void testUpdateBankAccount() {
41         int updateBankAccount = bankAccountDao.updateBankAccount(new XdlBankAccount(2, "acc_abb", "456", "111"));
42         System.out.println(updateBankAccount);
43     }
44 
45     // 根据id删除银行账户数据
46     @org.junit.Test
47     public void testDeleteBankAccount() {
48         int deleteBankAccount = bankAccountDao.deleteBankAccount(new XdlBankAccount(2));
49         System.out.println(deleteBankAccount);
50     }
51 
52     // 向银行表中插入数据
53     @org.junit.Test
54     public void testInsertBankAccount() {
55         int insertBankAccount = bankAccountDao.insertBankAccount(new XdlBankAccount("wangcai", "12345", "5555"));
56         System.out.println(insertBankAccount);
57     }
58 
59 }
View Code