1.查询emp表中的所有记录为例
2.测试类
public Emp getByNameAndEmail(String name, String email){
String sql = "select * from emp where name = ? and email = ?";
return DbUtils.query(sql, new ExtractData<Emp>() { //将次结果集封装为一个对象传过去
@Override
public Emp extract(ResultSet rs) throws SQLException {
Emp e = new Emp();
e.setId(rs.getInt(1));
e.setName(rs.getString(2));
e.setEmail(rs.getString(3));
e.setDept_no(rs.getInt(4));
e.setSalary(rs.getDouble(5));
return e;
}
}, name, email);
}
3.结果集处理的泛型的接口
public interface ExtractData<T> {
/**
* 从结果集中提取数据封装成T对象
* @param rs-sql执行之后的结果集
* @return
*/
public T extract(ResultSet rs) throws SQLException;
}
4.jdbc和curd的封装
public class DbUtils {
private static final String DRIVERCLASS = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://127.0.0.1:3306/shop?useUnicode=true&characterEncoding=utf8";
private static final String USERNAME = "root";
private static final String PASSWORD = "12345";
static {
try {
Class.forName(DRIVERCLASS);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection conn = null;
/**
* 获取数据库连接
* @return
* @throws SQLException
*/
try {
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
/**
* 获取statement对象
* @param conn 数据库连接对象
* @param sql
* @return
* @throws SQLException
*/
public static PreparedStatement getStatement(Connection conn, String sql) {
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareCall(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pstmt;
}
public static PreparedStatement getStatement(Connection conn, String sql, int key) {
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareCall(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pstmt;
}
/**
* 关闭连接,回收资源
* @param conn
* @param stmt
*/
public static void destory(Connection conn, PreparedStatement pstmt) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 执行多行行查询的方法
*/
public static <T> List<T> query(String sql, RowMapper<T> ed) {
Connection conn;
List<T> list = new ArrayList<T>();
try {
conn = getConnection();
PreparedStatement pstmt = getStatement(conn, sql);
// 执行sql
ResultSet rs = pstmt.executeQuery();
list = ed.row(rs);
rs.close();
destory(conn, pstmt);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
}