JDBC已经能够满足大部分用户最基本的需求,但是在使用JDBC时,必须自己来管理数据库资源如:获取PreparedStatement,设置SQL语句参数,关闭连接等步骤。
JdbcTemplate是Spring对JDBC的封装,目的是使JDBC更加易于使用。JdbcTemplate是Spring的一部分。JdbcTemplate处理了资源的建立和释放。他帮助我们避免一些常见的错误,比如忘了总要关闭连接。他运行核心的JDBC工作流,如Statement的建立和执行,而我们只需要提供SQL语句和提取结果。
Spring源码地址:https://github.com/spring-projects/spring-framework
在JdbcTemplate中执行SQL语句的方法大致分为3类:
execute:可以执行所有SQL语句,一般用于执行DDL语句。update:用于执行INSERT、UPDATE、DELETE等DML语句。queryXxx:用于DQL数据查询语句。在所有的的dao中注入JdbcTemplate
@Autowired JdbcTemplate jdbcTemplate;在所有的的service中注入dao
@Autowired private BookDao bookDao;在Test中注入service
public static BookService getBean(){ ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); return context.getBean("bookServiceImpl", BookService.class); }1、queryForLong返回一个long整数
API介绍
public long queryForLong(String sql) 执行查询语句,返回一个long类型的数据。2、queryForInt返回一个int整数
API介绍
public int queryForInt(String sql) 执行查询语句,返回一个int类型的值。3、queryForObject返回指定的数据类型
API介绍
public <T> T queryForObject(String sql, Class<T> requiredType) 执行查询语句,返回一个指定类型的数据。具体实现
BookDaoImpl.java public Integer selectCounts() { return jdbcTemplate.queryForObject("select count(*) from ssmbuild.books", Integer.class); } BookServiceImpl.java public Integer selectCounts() { return bookDao.selectCounts(); } Test @Test public void selectCounts(){ BookService bean = getBean(); Integer integer = bean.selectCounts(); System.out.println(integer); }4、queryForMap返回一个Map集合
API介绍
public Map<String, Object> queryForMap(String sql,@Nullable Object... args) 执行查询语句,将一条记录放到一个Map中。具体实现
BookDaoImpl.java public Map selectById(Integer id) { return jdbcTemplate.queryForMap("select * from ssmbuild.books where bookid = ?",id); } BookServiceImpl.java public Map selectById(Integer id) { return bookDao.selectById(id); } Test @Test public void selectBookByID(){ BookService bean = getBean(); Map map = bean.selectById(2); System.out.println(map); }5、queryForList返回一个List集合
API介绍
public List<Map<String, Object>> queryForList(String sql) 执行查询语句,返回一个List集合,List中存放的是Map类型的数据。具体实现
BookDaoImpl.java public List<Map<String, Object>> selectAll() { return jdbcTemplate.queryForList("select * from ssmbuild.books"); } BookServiceImpl.java public List<Map<String, Object>> selectAll() { return bookDao.selectAll(); } Test @Test public void selectAll(){ BookService bean = getBean(); List<Map<String, Object>> list= bean.selectAll(); for (Map<String, Object> map : list) { System.out.println(map); } }6、RowMapper返回自定义对象
API介绍
public <T> List<T> query(String sql, RowMapper<T> rowMapper) 执行查询语句,返回一个List集合,List中存放的是RowMapper指定类型的数据。具体实现
BookDaoImpl.java public List<Book> selectAllTwo() { return jdbcTemplate.query("select * from ssmbuild.books", new RowMapper<Book>() { public Book mapRow(ResultSet resultSet, int i) throws SQLException { Book book = new Book(); book.setBookId(resultSet.getInt("bookid")); book.setBookName(resultSet.getString("bookname")); book.setBookCounts(resultSet.getString("bookcounts")); book.setDetail(resultSet.getString("detail")); return book; } }); }使用JdbcTemplate对象的query方法,并传入RowMapper匿名内部类
在匿名内部类中将结果集中的一行记录转成一个Product对象
BookServiceImpl.java public List<Book> selectAllTwo() { return bookDao.selectAllTwo(); } Test @Test public void selectAllTwo(){ BookService bean = getBean(); List<Book> books = bean.selectAllTwo(); for (Book book : books) { System.out.println(book); } }7、BeanPropertyRowMapper返回自定义对象
API介绍
public <T> List<T> query(String sql, RowMapper<T> rowMapper) 执行查询语句,返回一个List集合,List中存放的是RowMapper指定类型的数据。 public class BeanPropertyRowMapper<T> implements RowMapper<T> BeanPropertyRowMapper类实现了RowMapper接口具体实现
BookDaoImpl.java public List<Book> selectAllThree() { return jdbcTemplate.query("select * from ssmbuild.books",new BeanPropertyRowMapper<Book>(Book.class)); } BookServiceImpl.java public List<Book> selectAllThree() { return bookDao.selectAllThree(); } Test @Test public void selectAllThree(){ BookService bean = getBean(); List<Book> books = bean.selectAllThree(); for (Book book : books) { System.out.println(book); } }具体实现
BookDaoImpl.java public int deleteBookById(Integer id) { return jdbcTemplate.update("delete from ssmbuild.books where bookid = ?",id); } BookServiceImpl.java public int deleteBookById(Integer id) { return bookDao.deleteBookById(id); } Test @Test public void deleteBookById(){ BookService bean = getBean(); int i = bean.deleteBookById(4); String message = i > 0?"删除成功":"删除失败"; System.out.println(message); }具体实现
BookDaoImpl.java public int updateBookById(Book book) { return jdbcTemplate.update("update ssmbuild.books set bookname = ? where bookid = ?",book.getBookName(),book.getBookId()); } BookServiceImpl.java public int updateBookById(Book book) { return bookDao.updateBookById(book); } Test @Test public void updateBookById(){ BookService bean = getBean(); Map map = bean.selectById(1); System.out.println("=========改之前========="); System.out.println(map); Book book = new Book(); book.setBookName("嘿嘿嘿"); book.setBookId(1); bean.updateBookById(book); Map map2 = bean.selectById(1); System.out.println("=========改之前========="); System.out.println(map2); }具体实现
BookDaoImpl.java public int addBook(Book book) { return jdbcTemplate.update("insert into ssmbuild.books (bookname,bookcounts,detail) values (?,?,?)",book.getBookName(),book.getBookCounts(),book.getDetail()); } BookServiceImpl.java public int addBook(Book book) { return bookDao.addBook(book); } Test @Test public void addBook(){ Book book = new Book(); book.setBookName("html"); book.setBookCounts("10"); book.setDetail("很棒"); int i = getBean().addBook(book); String message = i>0?"添加成功":"添加失败"; System.out.println(message); }