一 首先创建数据库表和相应的字段,并创建约束
二 建立项目,导入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>
数据库表
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);
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 }
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 }
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 }
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 }
测试类
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 }