模板类(重点)
- 封装通用的增删改查步骤
增删改查:封装
封装结果如下:
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;
}
}