数据库开发（十三 JDBC篇五） 模板类

模板类（重点）

• 封装通用的增删改查步骤

增删改查：封装

封装结果如下：

PreparedStatementSetter接口：

package com.zhang.util;

import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
 * 设置占位符值的接口
 * @author zygycp
 *
 */
public interface PreparedStatementSetter {
	//设置占位符的值
	public void setValues(PreparedStatement stmt) throws SQLException;
}

ResultSetHandler接口：

package com.zhang.util;

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

/**
 * 设置占位符值的接口
 * @author zygycp
 *
 */
public interface ResultSetHandler {
	//设置占位符的值
	public void handleRS(ResultSet rs) throws SQLException;
}

JDBCTemplate模板类：

package com.zhang.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 模板类
 * 封装通用的增删改查
 * @author zygycp
 *
 */
public class JDBCTemplate {
	//增删改
	public int update(String sql,PreparedStatementSetter setter) throws SQLException {
		//1,2
		Connection conn = ConnectionFactory.getConnection();
		//3
		PreparedStatement pstmt = conn.prepareStatement(sql);
		//4
		//先设置占位符的值
		if(setter!=null) {
			setter.setValues(pstmt);
		}
		//执行
		int rows = pstmt.executeUpdate();
		//6
		DBUtils.close(pstmt,conn);
		return rows;
	}

	//查询
	public void query(String sql,PreparedStatementSetter setter,
					  ResultSetHandler handler) throws SQLException {
		//1,2
		Connection conn = ConnectionFactory.getConnection();
		//3
		PreparedStatement pstmt = conn.prepareStatement(sql);
		//4
		if(setter!=null) {
			setter.setValues(pstmt);
		}
		ResultSet rs = pstmt.executeQuery();
		//5.处理结果集
		if(handler!=null){
			handler.handleRS(rs);
		}

		//6
		DBUtils.close(rs,pstmt,conn);


	}

}

UserJDBCOfTemplate类：

package com.zhang.jdbc;

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

import com.zhang.entity.User;
import com.zhang.util.*;

public class UserJDBCOfTemplate extends JDBCTemplate{
	

	public int addUser(User user) throws ClassNotFoundException, SQLException {
		String sql = "INSERT INTO t_user(id,username,password,sex,id_number,tel,addr)" 
				+ "VALUES(t_user_id_seq.NEXTVAL,?,?,?,?,?,?)";
		//调用通用的增删改方法
		return update(sql,new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException{
				pstmt.setString(1, user.getUsername());
				pstmt.setString(2, user.getPassword());
				pstmt.setInt(3, user.getSex());
				pstmt.setString(4, user.getIdNumber());
				pstmt.setString(5, user.getTel());
				pstmt.setString(6, user.getAddr());
			}
		});
		
	}

	//通过用户名查找用户
	public List<User> queryUser(String username) throws ClassNotFoundException, SQLException {
		List<User> list = new ArrayList<>();
		String sql = "SELECT id,username,password,sex,"
				+ "id_number,tel,addr FROM t_user WHERE username=?";
		query(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				//放置设置占位符的值的代码
				pstmt.setString(1,username);
			}
		}, new ResultSetHandler() {
			@Override
			public void handleRS(ResultSet rs) throws SQLException {
				//放置结果集处理的代码
				if(rs.next()) {
					User user = new User(rs.getLong(1),rs.getString(2),rs.getString(3),
							rs.getInt(4),rs.getString(5),rs.getString(6),rs.getString(7));
					list.add(user);
				}
			}
		});

		return list;
	}

}

练习1：通过用户id修改用户的密码，调用通用方法完成

内部类补充：

• 内部类可以直接使用外部类属性
• 内部类可以使用外部类的局部变量，要求局部变量加 final
  • jdk1.8开始可以不用加，编译时默认添加final

练习1结果：

UserJDBCOfTemplatePractice1类：

package com.zhang.jdbc;

import com.zhang.util.JDBCTemplate;
import com.zhang.util.PreparedStatementSetter;

import java.sql.PreparedStatement;
import java.sql.SQLException;

public class UserJDBCOfTemplatePractice1 extends JDBCTemplate {

	//通过用户id修改用户的密码，调用通用方法完成
	public int updatePasswordById(final long id,final String password) throws SQLException {
		//上述两个final1.8版本可以不用加final，1.7则需要加上
		String sql = "UPDATE t_user SET password=? WHERE id=?";
		return update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1,password);
				pstmt.setLong(2,id);
			}
		});
	}
}

练习2：查找全部用户并返回

练习2结果：

package com.zhang.jdbc;

import com.zhang.entity.User;
import com.zhang.util.JDBCTemplate;
import com.zhang.util.ResultSetHandler;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserJDBCOfTemplatePractice2 extends JDBCTemplate {
	public static void main(String[] args) {
		UserJDBCOfTemplatePractice2 userJDBC = new UserJDBCOfTemplatePractice2();

		try {
			List<User> list = userJDBC.queryALLUser();
			for (User user:list) {
				System.out.println(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}


	}

	//查找全部用户并返回
	public List<User> queryALLUser() throws SQLException {
		List<User> list = new ArrayList<>();
		String sql = "SELECT id,username,password,sex,"
				+ "id_number,tel,addr FROM t_user";
		query(sql, null, new ResultSetHandler() {
			@Override
			public void handleRS(ResultSet rs) throws SQLException {
				//放置结果集处理的代码
				while(rs.next()) {
					User user = new User(rs.getLong(1),rs.getString(2),rs.getString(3),
							rs.getInt(4),rs.getString(5),rs.getString(6),rs.getString(7));
					list.add(user);
				}
			}
		});

		return list;
	}
}