1、JdbcTemplate介绍
为了使 JDBC 更加易于使用,Spring 在 JDBCAPI 上定义了一个抽象层, 以此建立一个JDBC存取框架，Spring Boot Spring Data-JPA。
作为 SpringJDBC 框架的核心, JDBC 模板的设计目的是为不同类型的JDBC操作提供模板方法. 每个模板方法都能控制整个过程,并允许覆盖过程中的特定任务。
通过这种方式,可以在尽可能保留灵活性的情况下,将数据库存取的工作量降到最低。
2、JdbcTemplate方法介绍
JdbcTemplate主要提供以下五类方法:
1、execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
Execute、executeQuery、executeUpdate
2、update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句 SQL SERVCER(GO SQL语句 GO) ；
3、query方法及queryForXXX方法：用于执行查询相关语句；
4、call方法：用于执行存储过程、函数相关语句。
一.配置式：
首先DAO层(以图书为例)：
package com.jdbc.dao; import com.jdbc.entity.Book; import java.util.List; public interface IBookDao { //查询所有图书信息
public List<Book> getBook(); }
package com.jdbc.dao.impl; import com.jdbc.dao.IBookDao; import com.jdbc.entity.Book; import com.jdbc.entity.User; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.stereotype.Repository; import javax.annotation.Resource; import java.util.List; @Repository public class IBookDaoImpl implements IBookDao { @Override public List<Book> getBook() { JdbcTemplate template = this.getJdbcTemplate(); String sql="select * from book"; List<Book> list = template.query(sql, new RowMapper<Book>() { @Override public Book mapRow(ResultSet rs, int rowNum) throws SQLException { Book book = new Book(); book.setBid(rs.getString("bid")); book.setBname(rs.getString("bname")); book.setBauthor(rs.getString("bauthor")); book.setBprice(rs.getDouble("bprice")); return book; } }); return list; }
Service层（同dao层方法一样）：
package com.jdbc.service; import com.jdbc.entity.Book; import com.jdbc.entity.User; import java.util.List; public interface IBookService { public List<Book> getBook(); }
package com.jdbc.service.impl; import com.jdbc.dao.IBookDao; import com.jdbc.dao.impl.IBookDaoImpl; import com.jdbc.entity.Book; import com.jdbc.service.IBookService; import org.springframework.stereotype.Service; import javax.annotation.Resource; import java.util.List; @Service("bookService") public class IBookServiceImpl implements IBookService { @Resource private IBookDao bookDao; @Override public List<Book> getBook() { return bookDao.getBook(); } public IBookDao getBookDao() { return bookDao; } public void setBookDao(IBookDao bookDao) { this.bookDao = bookDao; } }
然后测试：
import com.jdbc.entity.Book; import com.jdbc.entity.User; import com.jdbc.service.IBookService; import org.junit.Test; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import java.util.List; public class IBookJdbcTemplateTest { /** * * 查询所有图书 * */ @Test public void bookTest(){ ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext_jdbcTemplate.xml"); IBookService bookService=(IBookService) ctx.getBean("bookService"); List<Book> book = bookService.getBook(); for (Book items:book){ System.out.println("书籍编号：" items.getBid()); System.out.println("t名称：" items.getBname()); System.out.println("t作者：" items.getBauthor()); System.out.println("t价格：" items.getBprice()); System.out.println("================"); } }
二.注解
以用户为例（我这里用的依旧是Book的接口和类）：
DAO层（增删改查都在里边）：
package com.jdbc.dao; import com.jdbc.entity.Book; import com.jdbc.entity.User; import java.util.List; public interface IBookDao {
//查询所有图书信息
public List<Book> getBook();
//查询所有用户 public List<User> getAllUser(); //添加用户 public int addUser(User user); //删除用户 public int deleteUser(int id); //修改用户 public int setUser(String name,int id); }
package com.jdbc.dao.impl; import com.jdbc.dao.IBookDao; import com.jdbc.entity.Book; import com.jdbc.entity.User; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.stereotype.Repository; import javax.annotation.Resource; import java.util.List; @Repository public class IBookDaoImpl implements IBookDao { @Resource private JdbcTemplate jdbcTemplate; @Override public List<Book> getBook() { //JdbcTemplate template = this.getJdbcTemplate();
String sql="select * from book"; /*List<Book> list = template.query(sql, new RowMapper<Book>() { @Override public Book mapRow(ResultSet rs, int rowNum) throws SQLException { Book book = new Book(); book.setBid(rs.getString("bid")); book.setBname(rs.getString("bname")); book.setBauthor(rs.getString("bauthor")); book.setBprice(rs.getDouble("bprice")); return book; } });*/ RowMapper<Book> rmapp=new BeanPropertyRowMapper<>(Book.class); List<Book> list = jdbcTemplate.query(sql, rmapp); return list; } @Override public List<User> getAllUser() { String sql="select * from user"; RowMapper<User> umapper=new BeanPropertyRowMapper<>(User.class); List<User>list = jdbcTemplate.query(sql, umapper); return list; } @Override public int addUser(User user) { String sql="insert into user (uid,uname,upwd) values(?,?,?)"; Object [] obj={user.getUid(),user.getUname(),user.getUpwd()}; int count = jdbcTemplate.update(sql,obj); return count; } @Override public int deleteUser(int id) { String sql="delete from user where uid=?"; int count = jdbcTemplate.update(sql, id); return count; } @Override public int setUser(String name,int id) { String sql="UPDATE `user` SET uname=? WHERE uid=?"; int count = jdbcTemplate.update(sql, name, id); return count; } }
Service层和前边一样这里就不拿出来了
然后是测试类：
import com.jdbc.entity.Book; import com.jdbc.entity.User; import com.jdbc.service.IBookService; import org.junit.Test; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import java.util.List; public class IBookJdbcTemplateTest { /** * * 查询所有图书 * */ @Test public void bookTest(){ ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext_jdbcTemplate.xml"); IBookService bookService=(IBookService) ctx.getBean("bookService"); List<Book> book = bookService.getBook(); for (Book items:book){ System.out.println("书籍编号：" items.getBid()); System.out.println("t名称：" items.getBname()); System.out.println("t作者：" items.getBauthor()); System.out.println("t价格：" items.getBprice()); System.out.println("================"); } } /** * * 查询所有用户 */ @Test public void UTest(){ ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext_jdbcTemplate.xml"); IBookService bookService=(IBookService) ctx.getBean("bookService"); List<User> list=bookService.getAllUser(); for (User us:list){ System.out.println(us.getUname()); } } /** * * 添加用户 */ @Test public void addTest(){ ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext_jdbcTemplate.xml"); IBookService bookService=(IBookService) ctx.getBean("bookService"); User us=new User(); us.setUid(4); us.setUname("hehe"); us.setUpwd("8888"); int count = bookService.addUser(us); System.out.println("成功！共" count "行受影响！"); } /** * * 删除用户 */ @Test public void deleteTest(){ ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext_jdbcTemplate.xml"); IBookService bookService=(IBookService) ctx.getBean("bookService"); int count = bookService.deleteUser(3); System.out.println("删除成功！t共" count "行受影响！"); } /** * * 修改用户 */ @Test public void updateUserTest(){ ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext_jdbcTemplate.xml"); IBookService bookService=(IBookService) ctx.getBean("bookService"); int count = bookService.setUser("dyuy", 2); System.out.println("修改成功！t共" count "行受影响！"); } }
执行结果就不再写出来了！