JDBC Template是Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发
使用jdbc的步骤如下:
- 创建JdbcTemplate对象。依赖于数据源DataSource
JdbcTemplate template = new JdbcTemplate(ds);
2. 调用JdbcTemplate的方法来完成CRUD的操作
(1)update():执行DML语句。增、删、改语句,返回的是该条语句影响的记录数
//将id为1001的记录的工资设为10000
String sql = "update emp set salary = 10000 where id = 1001";
int count = template.update(sql);
//添加一条记录
String sql = "insert into emp(id,ename,dept_id) values(?,?,?)";
int count = template.update(sql, 1015, "郭靖", 10);
//删除刚才添加的记录
String sql = "delete from emp where id = ?";
int count = template.update(sql, 1015);
(2)queryForMap():查询结果将结果集封装为map集合,将列名作为key,将值作为value 将这条记录封装为一个map集合
* 注意:这个方法查询的结果集长度只能是1,不能是0,或者多个
//查询id为1001的记录,将其封装为Map集合
String sql = "select * from emp where id = ? or id = ?";
Map<String, Object> map = template.queryForMap(sql, 1001,1002);
System.out.println(map);
//{id=1001, ename=孙悟空, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=20}
(3) queryForList():查询结果将结果集封装为list集合
* 注意:将每一条记录封装为一个Map集合,再将Map集合装载到List集合中
//查询表中所有记录,并将其封装为list集合
String sql = "select * from emp";
List<Map<String, Object>> list = template.queryForList(sql);
(4)query():查询结果,将结果封装为JavaBean对象
* query的参数:RowMapper
* 一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装
* new BeanPropertyRowMapper<类型>(类型.class)
//将结果封装为JavaBean对象
String sql = "select * from emp";
List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
(5) queryForObject:查询结果,将结果封装为对象
* 一般用于聚合函数的查询
String sql = "select count(id) from emp";
Long total = template.queryForObject(sql, Long.class);
//将查询结果封装为User对象
String sql="select * from user where id=?";
template.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class),id);
(6)当有多个值传入的时候可以一个一个传入,也可以传入数组
String sql="select * where name=? and salary=?"
//value为存放这两个值的list集合
template.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class),value.toArray());
(7) 实现模糊查询,LIKE后面直接跟问号,%传值的时候再传
String sql="select * where name LIKE ?"
template.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class),"%李");
(8) Mysql的动态拼接
1.定义初始化的Mysql语句
- 定义初始化的Mysql语句,技巧为定义where 1==1,这样遍历的时候格式就一致了
- 遍历MAP实现拼接,拼接时把值存入List中
public int countAll(Map<String, String[]> condition) {
//查询语句的动态拼接
//1、定义模板初始化sql
String sql = "select count(*) from User where 1=1";
//遍历Map定义sql语句
StringBuilder sb = new StringBuilder(sql);
ArrayList<Object> values = new ArrayList<>();
for (String key : condition.keySet()) {
if ("currentPage".equals(key) || "rows".equals(key)) {
continue;
}
//判断value是否有值
String value = condition.get(key)[0];
if (value != null && !"".equals(value)) {
sb.append(" and " + key + " LIKE ?");
values.add("%" + value + "%");//条件的值
}
}
System.out.println(sql);
return template.queryForObject(sb.toString(), Integer.class, values.toArray());
}
复杂例子
例子内容:
- 动态拼接
- 动态查询
需求:
对如下网页实现按照姓名、籍贯以及邮箱的模糊查询并实现分页
1、Servlet的实现:从Request作用域中获取参数,调用Service
@WebServlet("/findUserByPageServlet")
public class findUserByPageServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String _currentPage = request.getParameter("currentPage");
String _rows = request.getParameter("rows");
int currentPage = 1;
int rows = 5;
if (_currentPage != null) {
currentPage = Integer.parseInt(_currentPage);
}
if (_rows != null) {
rows = Integer.parseInt(_rows);
}
if (currentPage<1){
currentPage=1;
}
Map<String, String[]> condition = request.getParameterMap();
// int currentPage = Integer.parseInt(request.getParameter("currentPage"));
// int rows = Integer.parseInt(request.getParameter("rows"));
findUserByPageService service = new findUserByPageServiceImpl();
PageBean<User> pagebean = service.findUserByPage(currentPage, rows,condition);
request.setAttribute("pagebean", pagebean);
request.getRequestDispatcher("/list.jsp").forward(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response);
}
}
2、Service的实现
public class findUserByPageServiceImpl implements findUserByPageService {
UserDao dao = new UserDaoImpl();
@Override
public PageBean<User> findUserByPage(int currentPage, int rows, Map<String, String[]> condition) {
PageBean pagebean = new PageBean<User>();
pagebean.setCurrentPage(currentPage);
pagebean.setRows(rows);
//设置查询数据
List<User> users = dao.findUserByPage(rows * (currentPage - 1), rows, condition);
pagebean.setList(users);
//得到totalCount
int count = dao.countAll(condition);
pagebean.setTotalCount(count);
//设置总页数
int totalPage = count % rows == 0 ? count / rows : count / rows + 1;
pagebean.setTotalPage(totalPage);
return pagebean;
}
}
3、Dao实现
@Override
public int countAll(Map<String, String[]> condition) {
//查询语句的动态拼接
//1、定义模板初始化sql
String sql = "select count(*) from User where 1=1";
//遍历Map定义sql语句
StringBuilder sb = new StringBuilder(sql);
ArrayList<Object> values = new ArrayList<>();
for (String key : condition.keySet()) {
if ("currentPage".equals(key) || "rows".equals(key)) {
continue;
}
//判断value是否有值
String value = condition.get(key)[0];
if (value != null && !"".equals(value)) {
sb.append(" and " + key + " LIKE ?");
values.add("%" + value + "%");//条件的值
}
}
System.out.println(sql);
return template.queryForObject(sb.toString(), Integer.class, values.toArray());
}
@Override
public List<User> findUserByPage(int start, int rows, Map<String, String[]> condition) {
String sql = "select * from User where 1=1";
//遍历Map定义sql语句
StringBuilder sb = new StringBuilder(sql);
ArrayList<Object> values = new ArrayList<>();
for (String key : condition.keySet()) {
if ("currentPage".equals(key) || "rows".equals(key)) {
continue;
}
//判断value是否有值
String value = condition.get(key)[0];
if (value != null && !"".equals(value)) {
sb.append(" and " + key + " LIKE ?");
values.add("%" + value + "%");//条件的值
}
}
sb.append(" LIMIT ?,?");
values.add(start);
values.add(rows);
System.out.println(values);
return template.query(sb.toString(), new BeanPropertyRowMapper<User>(User.class), values.toArray());
}
Page对象实现
public class PageBean<T> {
private int totalCount;//总记录数
private int totalPage;//总页码
private List list;//每页显示的数据
private int rows;//每页显示的记录数
private int currentPage;//当前页码
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public List getList() {
return list;
}
public void setList(List list) {
this.list = list;
}
public int getRows() {
return rows;
}
public void setRows(int rows) {
this.rows = rows;
}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
@Override
public String toString() {
return "PageBean{" +
"totalCount=" + totalCount +
", totalPage=" + totalPage +
", list=" + list +
", rows=" + rows +
", currentPage=" + currentPage +
'}';
}
}