三层架构思想和ORM思想的小案例

一、三层架构思想

在软件设计当中,为了实现软件的高内聚低耦合,会将整个代码的结构划分层次，各种完成自己对应的工作：
①.表现层（View）：收集数据，展示数据
②.业务逻辑层（Service）：分析校验处理业务逻辑程序
③.数据访问层（Dao）：直接访问数据库，获取核心数据的
eg：服务员（为客户点菜，最后把菜品上到桌上），厨师（负责加工菜品），采购员（负责采购菜）

二、ORM思想

1.object relationship mapping：对象关系的映射
2.数据库和java的对应：表——实体类，行——对象，列——属性
注意:数据库表里的字段如果是数字类型，在java中定义对应属性时最好选择包装类类型
eg：db中使用int类型，java中使用Integer类型，db中使用varchar类型，java中使用String类型
3.实体类(entity)-封装数据
①.标配:属性全部私有；提供get和set方法；提供无参构造；实现Serializable接口
②.高配: toStringo有参构造

三、案例：银行管理系统

利用了三层架构思想和orm思想
rowmapper工具类：封装减少代码冗余，处理结果集
template工具类：封装查询一条多条结果，和增删改冗余，操作减少dao层代码冗余

1、转账的三层架构描述

View（表现层）：①.负责收集用户数据②.结果展示:让用户输入转账卡号、密码、到账卡号、转账金额
Service（业务层）：负责业务处理和数据加工：验证转账卡号和到账卡号，转账密码是否正确
DAO（数据访问层）：负责数据库访问:修改卡号所对应的账户金额
DB（数据库）

2、项目包结构

3、conf包：dbcp.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc\:mysql\://localhost\:3306/mybase
username=root
password=root123
initalSize=10
maxActive=50
maxIdle=20
minIdle=5
maxWait=60000
connectionProperties=useUnicode\=true;characterEncoding\=utf-8
defaultAutoCommit=true
defaultTransactionIsolation=READ_COMMITTED

4、sql包：account.sql文件

保存Heidisql里面运行的数据库代码，以免时间过去太久忘记数据库的结构

create table account(
	cardid int(7) primary key auto_increment, 
	name varchar(15) not null,
	password varchar(6) not null,
	balance double
);
insert into account(name,password,balance) values ('wxd' , '123456', 500);
insert into account(name,password,balance) values ('1b', '123456',800);
select * from account;

5、enity实体类包：Account

package entity;

public class Account {
	private Integer cardid;
	private String name;
	private String password;
	private Double balance;
	public Integer getCardid() {
		return cardid;
	}
	public void setCardid(Integer cardid) {
		this.cardid = cardid;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public Double getBalance() {
		return balance;
	}
	public void setBalance(Double balance) {
		this.balance = balance;
	}
	public Account(Integer cardid, String name, String password, Double balance) {
		super();
		this.cardid = cardid;
		this.name = name;
		this.password = password;
		this.balance = balance;
	}
	public Account() {
		super();	
	}
	@Override
	public String toString() {
		return "Account [cardid=" + cardid + ", name=" + name + ", password="
				+ password + ", balance=" + balance + "]";
	}
}

6、util包

①、JdbcUtil工具类

JdbcUtil工具类：加载驱动，获取连接，释放资源，控制事务，连接池提高资源利用率

package util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class JdbcUtil {
	static DataSource pool=null;
	static Properties pro=new Properties();
	private static ThreadLocal<Connection> tdl=new ThreadLocal<Connection>();
static{
		InputStream is=null;
		try {
			is = JdbcUtil.class.getResourceAsStream("/conf/dbcp.properties");
			pro.load(is);
			pool=BasicDataSourceFactory.createDataSource(pro);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				is.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	public static Connection getConnection() throws Exception{
		Connection conn = tdl.get();
		if(conn==null){
			conn=pool.getConnection();
			tdl.set(conn);
		}
		return conn;
	}
	public static void release(ResultSet rs,PreparedStatement pstm,Connection conn) throws Exception{
		if(rs!=null) rs.close();
		if(pstm!=null) pstm.close();
		if(conn!=null){
			conn.close();
			tdl.remove();
		}
	}
}

②、RowMapper工具类

rowmapper工具类：封装处理结果集代码，减少代码冗余
首先建一个接口RowMapper

package rowmapper;

import java.sql.ResultSet;
public interface RowMapper<T> {
	//将ResultSet结果集封装成对象
	public T mapperRow(ResultSet rs);
}

再建RowMapper的实现类AccountRowMapper

package rowmapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import entity.Account;

public class AccountRowMapper implements RowMapper {
	@Override
	public Account mapperRow(ResultSet rs) {
		Account account=new Account();
		try {
			account.setCardid(rs.getInt("cardid"));
			account.setName(rs.getString("name"));
			account.setPassword(rs.getString(3));
			account.setBalance(rs.getDouble(4));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return account;
	}	
}

③、JdbcTemplate工具类

template工具类：封装查询一条多条结果，和增删改冗余，操作减少dao层代码冗余

package util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import rowmapper.RowMapper;

import util.JdbcUtil;

public class JdbcTemplate<T>{
	//都需要变成静态的
	static Connection conn =null;
	static PreparedStatement pstm=null;
	static ResultSet rs=null;
	//查询一条,可变长参数要必须放到最后一个
	//如果是半成品sql的args就有值
	public T queryForObject(String sql,RowMapper<T> rm,Object...args){
		T t=null;
		try {
			conn = JdbcUtil.getConnection();
			pstm=conn.prepareStatement(sql);
			//给预编译sql设置值
			if(args.length!=0){
				for(int i=0;i<args.length;i++){
					pstm.setObject(i+1, args[i]);
				}
			}
			//查询
			rs=pstm.executeQuery();
			//处理结果集
			if(rs.next()){
				t = rm.mapperRow(rs);
			}
		} catch (Exception e) {
			System.out.println("数据库连接出现异常");
		}finally{
			try {
				//在service层设置手动提交事务时，不能在JdbcTemplate里面关闭连接了。
				//因为accountDao会调用JdbcTemplate工具类来操作数据库，返回后还需要当前事务的连接来提交事务
				JdbcUtil.release(rs, pstm, null);
			} catch (Exception e) {
				System.out.println("释放资源出现问题");
			}
		}
		return t;
	}
	//查询多条
	public List<T> queryForList(String sql,RowMapper<T> rm,Object...args){
		List<T> list=null;
		try {
			conn = JdbcUtil.getConnection();
			pstm=conn.prepareStatement(sql);
			if(args.length!=0){
				for(int i=0;i<args.length;i++){
					pstm.setObject(i+1, args[i]);
				}
			}
			rs=pstm.executeQuery();
			list=new ArrayList();
			while(rs.next()){
				T t = rm.mapperRow(rs);
				list.add(t);
			}
		} catch (Exception e) {
			System.out.println("数据库连接出现异常");
		}finally{
			try {
				JdbcUtil.release(rs, pstm, null);
			} catch (Exception e) {
				System.out.println("释放资源出现问题");
			}
		}
		return list;
	}
	//增删改操作
	//如果sql是完整的就不需要有参数，若sql是半成品args.length！=0则需要有参数，Object...args，是可变长参数，可以理解为数组，可以没有参数，也可以有一个两个三个参数
	public void update(String sql,Object...args){
		try {
			conn= JdbcUtil.getConnection();
			pstm=conn.prepareStatement(sql);
			//给预编译sql设置值
			if(args.length!=0){
				for(int i=0;i<args.length;i++){
					//绑定参数，不知道参数的实际类型，用最大的父类Object
					//下标是0，是第一个？的值,所以第i个下标，是第i+1个？的值
					pstm.setObject(i+1, args[i]);
				}
			}
			//修改
			pstm.executeUpdate();
		} catch (Exception e) {
			System.out.println("数据库发生异常");
		}finally{
			try {
				//使用手动控制提交，不能在Template里面关闭conn
				JdbcUtil.release(null, pstm, null);
			} catch (Exception e) {
				System.out.println("释放资源发生异常");
			}
		}
	}
}

7、view包

使用接口编程，接口可以解耦合

显示给用户看的，并且让用户输入需要选择的业务，最后返回结果

package view;

import java.util.Scanner;

import service.AccountService;
import service.AccountServiceImpl;

import entity.Account;

public class AccountView {
	//输入,sc.nextInt()输入int类型,sc.next()输入一行字符串
	//Scanner为util包下的
	private static Scanner sc=new Scanner(System.in);
	//创建一个Service的全局对象
	static AccountService accountService=new AccountServiceImpl();
	public static void main(String[] args) {
		while(true){
			System.out.println("****************欢迎讲入银行管理系统****************");
			System.out.println("1存钱                               2取钱");
			System.out.println("3查询当前余额                4转账");
			System.out.println("5注册账户                       6注销账户");
			System.out.println("7更改密码");
			System.out.println("0退出登录");
			System.out.println("请给出您的选择");
			//输入选择的业务项
			int n=sc.nextInt();
			switch(n){
				case 1:
					//存钱
					break;
				case 2:
					//取钱
					break;
				case 3:
					//查询余额
					queryBalance();
					break;
				case 4:
					//转账
					transforMoney();
					break;
				case 5:
					//注册账户
					regist();
					break;
				case 6:
					//注销账户
					break;
				case 7:
					//更改密码
					break;
				case 0:
					//退出登录
					System.out.println("您已退出登录");
					System.exit(0);//当前运行结束，正常退出
					break;
				default:
					throw new RuntimeException("输入有误，请再次输入");
			}
		}
	}
	//3.查询余额
	public static void queryBalance(){
		System.out.println("请输入您的账号");
		int cardid = sc.nextInt();
		System.out.println("请输入您的密码");
		String password = sc.next();
		try{
			Double balance=accountService.queryBalance(cardid,password);
			System.out.println("您的当前余额为:"+balance);
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
	}
	//4.转账
	public static void transforMoney(){
		System.out.println("请输入您的账号");
		int mycardid = sc.nextInt();
		System.out.println("请输入您的密码");
		String password = sc.next();
		System.out.println("请输入对方的账号");
		int othercardid = sc.nextInt();
		System.out.println("请输入您的转账金额");
		double money = sc.nextDouble();
		try{
			accountService.transforMoney(mycardid,password,othercardid,money);
			System.out.println("转账成功");
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
	}
	//5.注册
	public static void regist(){
		System.out.println("请输入您的姓名");
		String name = sc.next();
		System.out.println("请输入您的密码");
		String password = sc.next();
		System.out.println("请再次输入您的密码");
		String surepassword = sc.next();
		//将获取的姓名，密码和二次密码传给service层,让业务逻辑层对数据进行处理
		Account account=new Account(null,name,password,0.0);
		try{
			accountService.regist(account,surepassword);
			System.out.println("注册成功");
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
	}
}

8、service包

这里是将用户输入的信息进行判断和处理

注意：
//conn连接只能在service层关闭，rs和pstm要在template层关闭
//在Service要控制事物,必须要先创建连接，再设置手动提交事务
//在service层设置手动提交事务时，不能在JdbcTemplate里面关闭连接了,只能在service层最后来关闭conn连接
//view调用service，service调用dao，dao调用JdbcTemplate，是属于同一个线程
//因为accountDao会调用JdbcTemplate工具类来操作数据库，返回后还需要当前事务的连接来提交事务

①.AccountService接口

package service;

import entity.Account;

public interface AccountService {
	//业务功能
	//开户
	public void regist(Account account,String surepassword);
	//查询余额
	public Double queryBalance(int cardid, String password);
	//转账
	public void transforMoney(int mycardid, String password, int othercardid,double money);
}

②.实现类AccountServiceImpl

package service;

import java.sql.Connection;
import java.sql.SQLException;

import util.JdbcUtil;
import dao.AccountDao;
import dao.AccountDaoImpl;
import entity.Account;

public class AccountServiceImpl implements AccountService {
	//创建一个Dao的全局对象
	AccountDao accountDao=new AccountDaoImpl();
	Connection conn=null;
	//查询余额
	@Override
	public Double queryBalance(int cardid, String password) {
		Double balance=0.0;
		//根据卡号判断账户是否存在，如果存在就返回卡号对应账户
		Account account=accountDao.queryAccountByCardid(cardid);
		if(account==null){
			throw new RuntimeException("您的账号不存在");
		}
		//密码输入是否正确
		//根据返回的账户数据库中存储的密码与用户输入的密码做判断
		if(!account.getPassword().equals(password)){
			throw new RuntimeException("您的密码不正确");
		}
		try {
			//查询不需要设置手动提交，也不需要获取连接，不需要关闭conn资源
			//conn=JdbcUtil.getConnection();
			balance = account.getBalance();
		} catch (Exception e) {
			throw new RuntimeException("数据库访问异常");
		}finally{
			try {
				JdbcUtil.release(null, null, conn);
			} catch (Exception e) {
				throw new RuntimeException("关闭连接异常");
			}
		}
		return balance;
	}
	//转账
	@Override
	public void transforMoney(int mycardid, String password, int othercardid,double money) {
		//根据账号cardid来查找用户,只能调用Dao层来访问数据库
		Account myaccount=accountDao.queryAccountByCardid(mycardid);
		Account otheraccount=accountDao.queryAccountByCardid(othercardid);
		//判断我的账号是否存在
		if(myaccount==null){
			throw new RuntimeException("您的账号不存在");
		}
		//判断对方账号是否存在
		if(otheraccount==null){
			throw new RuntimeException("对方账号不存在");
		}
		//判断我的密码是否正确
		if(!myaccount.getPassword().equals(password)){
			throw new RuntimeException("您的密码不正确");
		}
		//判断我的余额是否大于转账金额
		if(myaccount.getBalance()<money){
			throw new RuntimeException("您的余额不足,无法转账");
		}
		//可以转账的情况,我的账户余额要减少money,对方账户余额要增加money
		myaccount.setBalance(myaccount.getBalance()-money);
		otheraccount.setBalance(otheraccount.getBalance()+money);
		//让Dao层去真正的更改账户的余额信息
		//要成功都要成功，要失败都失败，使用要手动控制连接
		try {
			conn = JdbcUtil.getConnection();
			conn.setAutoCommit(false);
			accountDao.updateAccount(myaccount);
			accountDao.updateAccount(otheraccount);
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception e1) {
				System.out.println("事物回滚失败,系统正在维修");
			}
		}finally{
			//conn连接以后都要在service层关闭
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println("关闭连接异常,系统正在维护");
			}
		}
	}
	//注册
	@Override
	public void regist(Account account, String surepassword) {
		String name = account.getName();
		String password = account.getPassword();
		if(name==null){
			throw new RuntimeException("用户名不能为空");
		}
		if(password==null){
			throw new RuntimeException("密码不能为空");
		}
		if(!password.equals(surepassword)){
			throw new RuntimeException("两次密码不一致");
		}
		//在Service要控制事物,必须要先创建连接，再设置手动提交事务
		//在service层设置手动提交事务时，不能在JdbcTemplate里面关闭连接了,只能在service层最后来关闭conn连接
		//view调用service，service调用dao，dao调用JdbcTemplate，是属于同一个线程
		//因为accountDao会调用JdbcTemplate工具类来操作数据库，返回后还需要当前事务的连接来提交事务
		try {
			conn = JdbcUtil.getConnection();
			conn.setAutoCommit(false);
			//若是转账，就会调用两次，一个是叫我的账户减钱，对方的账户加钱，是同一个事物，要在Service层控制事物
			accountDao.insertAccount(account);
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception e1) {
				throw new RuntimeException("事物回滚异常,系统正在维护");
			}
		}finally{
			try {
				conn.close();
			} catch (Exception e) {
				throw new RuntimeException("关闭连接异常,系统正在维护");
			}
		}
	}
}

8、dao包

直接操作数据库，对数据库进行增删改查

①.AccountDao接口

package dao;

import entity.Account;

public interface AccountDao {
	//插入
	public void insertAccount(Account account);
	//根据卡号查看账户信息
	public Account queryAccountByCardid(int cardid);
	//修改账户信息
	public void updateAccount(Account account);
}

②.实现类AccountDaoImpl

package dao;

import rowmapper.AccountRowMapper;
import util.JdbcTemplate;
import entity.Account;

public class AccountDaoImpl implements AccountDao {
	JdbcTemplate  template=new JdbcTemplate();
	//根据卡号查看账户信息
	@Override
	public Account queryAccountByCardid(int cardid) {
		String sql="select * from account where cardid=?";
		Account account=(Account)template.queryForObject(sql, new AccountRowMapper(), cardid);
		return account;
	}
	//插入
	@Override
	public void insertAccount(Account account) {
		String sql="insert into account(name,password,balance) values(?,?,?)";
		template.update(sql, account.getName(),account.getPassword(),account.getBalance());
	}
	//修改账户信息
	@Override
	public void updateAccount(Account account) {
		String sql="update account set balance=? where cardid=?";
		template.update(sql, account.getBalance(),account.getCardid());
	}
}

9、test包

①.第一代TestORM

使用ORM思想测试查询一条或多条结果，进行增删改

package Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import entity.Account;
import util.JdbcUtil;

public class TestORM {
	//查询一条结果用if
	@Test
	public void testQuery() throws Exception{
		Connection conn = JdbcUtil.getConnection();
		String sql="select * from account where name='lb'";
		PreparedStatement pstm = conn.prepareStatement(sql);
		ResultSet rs = pstm.executeQuery();
		//创建Account对象，对应查询的一条记录
		Account account=new Account();
		//处理结果集，都是相同的
		if(rs.next()){
			int cardid = rs.getInt("cardid");
			String name=rs.getString("name");
			String password=rs.getString("password");
			Double balance=rs.getDouble("balance");
			//查询的记录，给account对象属性赋值
			account.setCardid(cardid);
			account.setName(name);
			account.setPassword(password);
			account.setBalance(balance);
		}
		System.out.println(account);
	}
	//查询多条结果，用while
	@Test
	public void testQueryAll() throws Exception{
		Connection conn = JdbcUtil.getConnection();
		String sql="select * from account";
		PreparedStatement pstm = conn.prepareStatement(sql);
		ResultSet rs = pstm.executeQuery();
		//创建account集合，对应多条记录
		List<Account> list=new ArrayList<Account>();
		//处理结果集
		while(rs.next()){
			Account account=new Account();
			int cardid = rs.getInt("cardid");
			String name=rs.getString("name");
			String password=rs.getString("password");
			Double balance=rs.getDouble("balance");
			account.setCardid(cardid);
			account.setName(name);
			account.setPassword(password);
			account.setBalance(balance);
			list.add(account);
		}
		//遍历list集合
		for (Account account : list) {
			System.out.println(account);
		}
	}
	//增加
	@Test
	public void testInsert() throws Exception{
		Account account=new Account(null,"锦鲤","123456",1000.0);
		Connection conn = JdbcUtil.getConnection();
		String sql="insert into account(name,password,balance) values(?,?,?)";
		PreparedStatement pstm=conn.prepareStatement(sql);
		//pstm绑定参数
		pstm.setString(1,account.getName());
		pstm.setString(2,account.getPassword());
		pstm.setDouble(3,account.getBalance());
		pstm.executeUpdate();
	}
	//修改
	@Test
	public void testUpdate() throws Exception{
		Account account=new Account(3,"小金鱼","222222",2000.0);
		Connection conn = JdbcUtil.getConnection();
		String sql="update account set name=?,password=?,balance=? where cardid=?";
		PreparedStatement pstm=conn.prepareStatement(sql);
		//pstm绑定参数
		pstm.setString(1,account.getName());
		pstm.setString(2,account.getPassword());
		pstm.setDouble(3,account.getBalance());
		pstm.setInt(4, account.getCardid());
		pstm.executeUpdate();
	}
	//删除
	@Test
	public void testDelete() throws Exception{
		Connection conn = JdbcUtil.getConnection();
		String sql="delete from account where name=? and balance=?";
		PreparedStatement pstm=conn.prepareStatement(sql);
		//pstm绑定参数
		pstm.setString(1, "小金鱼");
		pstm.setDouble(2,2000.0);
		pstm.executeUpdate();
	}
}

②.第二代TestORM2

使用了RowMapper接口，和AccountRowMapper实现类，封装了处理结果集，主要修改了查询一条和多条结果的方法

package Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import entity.Account;

import rowmapper.AccountRowMapper;
import rowmapper.RowMapper;
import util.JdbcTemplate;
import util.JdbcUtil;

public class TestORM2 {
	//创建RowMapper对象，获得ResultSet结果集
	RowMapper rm=new AccountRowMapper();
	//查询一条结果用if
	@Test
	public void testQuery() throws Exception{
		Connection conn = JdbcUtil.getConnection();
		String sql="select * from account where name='lb'";
		PreparedStatement pstm = conn.prepareStatement(sql);
		ResultSet rs = pstm.executeQuery();
		//创建Account对象，对应查询的一条记录
		Account account=new Account();
		//处理结果集，都是相同的,所以将处理结果集封装为一个ImplResultSetTemplate对象，调用方法mapperRow(rs)即可使用
		if(rs.next()){
			account=(Account)rm.mapperRow(rs);
		}
		System.out.println(account);
	}
	//查询多条结果，用while
	@Test
	public void testQueryAll() throws Exception{
		Connection conn = JdbcUtil.getConnection();
		String sql="select * from account";
		PreparedStatement pstm = conn.prepareStatement(sql);
		ResultSet rs = pstm.executeQuery();
		//创建account集合，对应多条记录
		List<Account> list=new ArrayList<Account>();
		while(rs.next()){
			 Account account=(Account)rm.mapperRow(rs);
			list.add(account);
		}
		//遍历list集合
		for (Account account : list) {
			System.out.println(account);
		}
	}
	//增加
	@Test
	public void testInsert() throws Exception{
		Account account=new Account(null,"锦鲤","123456",1000.0);
		Connection conn = JdbcUtil.getConnection();
		String sql="insert into account(name,password,balance) values(?,?,?)";
		PreparedStatement pstm=conn.prepareStatement(sql);
		//pstm绑定参数
		pstm.setString(1,account.getName());
		pstm.setString(2,account.getPassword());
		pstm.setDouble(3,account.getBalance());
		pstm.executeUpdate();
	}
	//修改
	@Test
	public void testUpdate() throws Exception{
		Account account=new Account(3,"小金鱼","222222",2000.0);
		Connection conn = JdbcUtil.getConnection();
		String sql="update account set name=?,password=?,balance=? where cardid=?";
		PreparedStatement pstm=conn.prepareStatement(sql);
		//pstm绑定参数
		pstm.setString(1,account.getName());
		pstm.setString(2,account.getPassword());
		pstm.setDouble(3,account.getBalance());
		pstm.setInt(4, account.getCardid());
		pstm.executeUpdate();
	}
	//删除
	@Test
	public void testDelete() throws Exception{
		Connection conn = JdbcUtil.getConnection();
		String sql="delete from account where name=? and balance=?";
		PreparedStatement pstm=conn.prepareStatement(sql);
		//pstm绑定参数
		pstm.setString(1, "小金鱼");
		pstm.setDouble(2,2000.0);
		pstm.executeUpdate();
	}
}

③.第三代TestORM3

使用了JdbcTemplate模板，封装了查询一条和多条结果，和增删改的冗余操作

package Test;

import java.util.List;

import org.junit.Test;

import entity.Account;

import rowmapper.AccountRowMapper;
import rowmapper.RowMapper;
import util.JdbcTemplate;

public class TestORM3 {
	JdbcTemplate template=new JdbcTemplate();
	RowMapper rm=new AccountRowMapper();
	//查询一条结果
	@Test
	public void testQuery() throws Exception{
		String sql="select * from account where name=?";
		Account account=(Account)template.queryForObject(sql, rm, "lb");
		System.out.println(account);
	}
	//查询多条结果
	@Test
	public void testQueryAll() throws Exception{
		String sql="select * from account";
		List<Account> list=template.queryForList(sql, rm);
		for(Account account:list){
			System.out.println(account);
		}
	}
	//增加
	@Test
	public void testInsert() throws Exception{
		Account account=new Account(null,"锦鲤","123456",1000.0);
		String sql="insert into account(name,password,balance) values(?,?,?)";
		template.update(sql, account.getName(),account.getPassword(),account.getBalance());
	}
	//修改
	@Test
	public void testUpdate() throws Exception{
		Account account=new Account(4,"小金鱼","222222",2000.0);
		String sql="update account set name=?,password=?,balance=? where cardid=?";
		template.update(sql,account.getName(),account.getPassword(),account.getBalance(), account.getCardid());
	}
	//删除
	@Test
	public void testDelete() throws Exception{
		String sql="delete from account where name=? and balance=?";
		template.update(sql,"小金鱼",2000.0);
	}
}