三层架构---增删改查操作封装

以account账户数据表为例，演示封装增删改操作。

(1)给出account账户表
(2)封装增删改操作
(3)testInsert
(4)testUpdate
(5)testDelete
(6)testQuery
(7)testQueryAll

---

以account账户数据表为例，演示封装增删改操作。

(1)给出account账户

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('小A','123456',500);
INSERT INTO ACCOUNT(NAME,PASSWORD,balance) VALUES('小B','123456',800);

SELECT * FROM ACCOUNT;

(2)封装增删改查操作

package util;

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

import rowmapper.RowMapper;

public class JdbcTemplate<T> {
	/*
	 * 操作方法 void update(sql,object...args)
	 * 
	 * 查询方法 1.查询结果1条 TqueryForObject(sql,object...args,RowMapper rm); 2.查询结果多条
	 * List<T> queryForList(sql,object...args,RowMapper rm);
	 */
	static Connection conn = null;
	static PreparedStatement pstm = null;
	static ResultSet rs = null;

	// select_one
	public T queryForObject(String sql, RowMapper<T> rm, Object... args) {
		T t = null;
		try {
			conn = JdbcUtil3.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();
			if (rs.next()) {
				t = rm.mappreRow(rs);
			}
		} catch (Exception e) {
			System.out.println("数据库连接异常");
		} finally {
			try {
				JdbcUtil3.release(rs, pstm, conn);
			} catch (Exception e) {
				System.out.println("释放资源出现问题");
			}
		}
		return t;
	}

	//select_more
	public List<T> queryForList(String sql,RowMapper<T> rm,Object...args){
		List<T> list = null;
		try {
			conn = JdbcUtil3.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.mappreRow(rs);
				list.add(t);
			}
		} catch (Exception e) {
			System.out.println("数据库连接异常");
		} finally {
			try {
				JdbcUtil3.release(rs, pstm, conn);
			} catch (Exception e) {
				System.out.println("释放资源出现问题");
			}
		}
		return list;
	}
	
	//updata操作
	public void update(String sql, Object... args) {

		try {
			conn = JdbcUtil3.getConnection();
			pstm = conn.prepareStatement(sql);
			// args的长度如果不是0,就说明有参数,那它就是个半成品
			if (args.length != 0) {
				for (int i = 0; i < args.length; i++) {
					pstm.setObject(i + 1, args[i]);
				}
			}
			pstm.executeUpdate();
		} catch (Exception e) {
			System.out.println("数据库异常!");
		} finally {
			try {
				JdbcUtil3.release(null, pstm, conn);
			} catch (Exception e) {
				System.out.println("关闭连接异常!");
			}
		}
	}
}

(3)testInsert

	@Test
	// 插入
	public void testInsert() throws Exception {
		Account account = new Account(null, "蝶", "123456", 1000.0);
		Connection conn = JdbcUtil3.getConnection();
		String sql = "insert into account(name,password,balance) values(?,?,?)";
		template.update(sql,account.getName(),account.getPassword(),account.getBalance());
	}

(4)testUpdate

	@Test
	// 修改
	public void testUpdate() throws Exception {
		Account account = new Account(2, "古月", "333", 2000.0);
		Connection conn = JdbcUtil3.getConnection();
		String sql = "update account set name=?,password=?,balance=? where cardid=?";
		template.update(sql,account.getName(),account.getPassword(),account.getBalance(),account.getCardid());
	}

(5)testDelete

	@Test
	//删除
	public void testDelete() throws Exception{
		Connection conn = JdbcUtil3.getConnection();
		String sql = "delete from account where name=? and balance=?";
		template.update(sql, "小A",500);

(6)testQuery

	@Test
	// 查询一条记录
	public void testQuery() throws Exception {
		Connection conn = JdbcUtil3.getConnection();
		String sql = "select * from account where name =?";

//		PreparedStatement pstm = conn.prepareStatement(sql);
//		ResultSet rs = pstm.executeQuery();
		// 创建Account对象,对应查询的一条记录
//		Account account = new Account();

		Account account = (Account)template.queryForObject(sql, rm, "蝶");
		// 处理结果集
//		if (rs.next()) {
//			account = (Account) rm.mappreRow(rs);
			// 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);
	}

单条查询成功：

(7)testQueryAll

	@Test
	// 查询多条记录
	public void testQueryAll() throws Exception {
//		Connection conn = JdbcUtil3.getConnection();
		String sql = "select * from account";
		List<Account> list = template.queryForList(sql, rm);
//		PreparedStatement pstm = conn.prepareStatement(sql);
//		ResultSet rs = pstm.executeQuery();
		// 创建Account集合,对应多条记录
//		List<Account> list = new ArrayList<Account>();
//		Account account = new Account();
//		while (rs.next()) {
//			account = (Account) rm.mappreRow(rs);
			// int cardid = rs.getInt("cardid");
			// String name = rs.getString("name");
			// String password = rs.getString("password");
			// double balance = rs.getDouble("balance");
			// // 将查询的一条记录封装到一个Account对象中
			// Account account = new Account();
			// account.setCardid(cardid);
			// account.setName(name);
			// account.setPassword(password);
			// account.setBalance(balance);
//			list.add(account);
//		}
		// 遍历list
		for (Account account1 : list) {
			System.out.println(account1);
		}
	}

多条查询成功：