jdbcTemplate(概念和准备)
1.什么是jdbcTemplate
(1)spring框架对JDBC进行封装,使用jdbcTemplate方便实现对数据库的操作。
2,准备工作
(1)引入相关jar包
(2)在Spring配置文件中配置数据库
<!-- 直接配置德鲁伊连接池--> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://localhost:3306/book_db?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true"></property> <property name="username" value="root"></property> <property name="password" value="123456"></property> </bean>
(3)配置jdbcTemplate对象,注入DataSource
<!-- jdbcTemplate对象--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <!-- 注入dataSource--> <property name="dataSource" ref="dataSource"></property> </bean>
(4)创建service类,创建dao类,在dao注入jdbcTemplate对象
<!-- 开启组件扫描--> <context:component-scan base-package="com.spring"></context:component-scan>
@Service public class BookService { //注入dao @Autowired private BookDao bookDao; }
@Repository public class BookDaoImpl implements BookDao{ //注入jdbcTemplate @Autowired private JdbcTemplate jdbcTemplate; }
jdbcTemplate(操作数据库 添加)
1.对应数据库,写实体类
public class Book { private String bookId; private String bookName; private Integer bookPrice; public String getBookId() { return bookId; } public void setBookId(String bookId) { this.bookId = bookId; } public String getBookName() { return bookName; } public void setBookName(String bookName) { this.bookName = bookName; } public Integer getBookPrice() { return bookPrice; } public void setBookPrice(Integer bookPrice) { this.bookPrice = bookPrice; } }
2.编写service和dao
(1)在dao进行数据库的添加操作
(2) 调用jdbcTemplate对象里面的update方法实现添加操作
有两个参数
*01.sql语句
*02.可变参数,设置sql语句值
//添加的方法 @Override public void add(Book book) { //1.创建sql语句 String sql = "insert into t_book values(?,?,?)"; //2.调用方法实现 Object[] args = {book.getBookId(),book.getBookName(),book.getBookPrice()}; int update =jdbcTemplate.update(sql,args); System.out.println(update); }
(3)测试类
@Test public void testJdbcTemplate(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); BookService bookService = context.getBean("bookService", BookService.class); Book book = new Book(); book.setBookId("1"); book.setBookName("java"); book.setBookPrice(20); bookService.addBook(book); }
jdbcTemplate(操作数据库 修改和删除)
1.修改
service
//修改的方法 public void updateBook(Book book){ bookDao.updateBook(book); }
dao
public void updateBook(Book book);
//修改的方法 @Override public void updateBook(Book book) { //1.创建sql语句 String sql = "update t_book set bookName=?,bookPrice=? where bookId=? "; //2.调用方法实现 Object[] args = {book.getBookName(),book.getBookPrice(),book.getBookId()}; int update = jdbcTemplate.update(sql, args); System.out.println(update); }
test
@Test public void testJdbcTemplateupdate(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); BookService bookService = context.getBean("bookService", BookService.class); Book book = new Book(); book.setBookId("1"); book.setBookName("javaupup"); book.setBookPrice(40); bookService.updateBook(book); }
2.删除
service
//删除的方法 public void deleteBook(String id){ bookDao.deleteBook(id); }
dao
public void deleteBook(String id);
//删除的方法 @Override public void deleteBook(String id) { String sql = "delete from t_book where bookId=?"; int update = jdbcTemplate.update(sql, id); System.out.println(update); }
test
@Test public void testJdbcTemplatedelete(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); BookService bookService = context.getBean("bookService", BookService.class); bookService.deleteBook("01"); }
jdbcTemplate(操作数据库 查询返回某个值)
1.查询表里面有多少条记录,返回的是某个值
2.使用jdbcTemplate实现查询返回某个值的代码
*有两个参数
01.sql语句
02.返回类型class
//service //查询表记录数 public int findCount(){ return bookDao.selectCount(); }
//dao //查询表中的记录时 @Override public int selectCount() { String sql = "select count(*) from t_book"; Integer count = jdbcTemplate.queryForObject(sql, Integer.class); return count; }
@Test public void testFind(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); BookService bookService = context.getBean("bookService", BookService.class); int count = bookService.findCount(); System.out.println(count); }
jdbcTemplate(操作数据库 查询返回对象)
1.场景:查询图书详情
2.jdbcTemplate实现查询返回对象
*3个参数
01.sql语句
02.RowMapper,是接口,返回不同类型的数据,使用这个接口的实现类完成数据的封装
03.sql语句值
//查询返回对象 @Override public Book findBookInfo(String id) { String sql = "select * from t_book where bookId=?"; Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id); return book; }
3.Test
@Test public void testFindBook(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); BookService bookService = context.getBean("bookService", BookService.class); Book one = bookService.findOne("1"); System.out.println(one); }
jdbcTemplate(操作数据库 查询返回集合)
1.场景:查询图书列表分页
2.调用jdbcTemplate方法实现查询返回集合
ublic List<Book> findAll(){ return bookDao.findAllBook(); }
@Override public List<Book> findAllBook() { String sql = "select * from t_book"; List<Book> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class)); return query; }
@Test public void testFindAll(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); BookService bookService = context.getBean("bookService", BookService.class); List<Book> all = bookService.findAll(); System.out.println(all); }
jdbcTemplate(操作数据库 批量操作)
1.批量操作:操作表里面的多条记录
2.jdbcTemplate实现批量添加操作
batchUpdata()方法
有两个参数
01.sql语句
02.List集合,添加多条记录
public void batchAdd(List<Object[]> batchArgs){ bookDao.batchAddBook(batchArgs); }
//批量添加 @Override public void batchAddBook(List<Object[]> batchArgs) { String sql = "insert into t_book values(?,?,?)"; int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs); System.out.println(Arrays.toString(ints)); }
@Test public void testbatchAdd(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); BookService bookService = context.getBean("bookService", BookService.class); List<Object[]> batchArgs =new ArrayList<>(); Object[] o1 = {"4","JAVA",30}; Object[] o2 = {"5","JAVA",30}; batchArgs.add(o1); batchArgs.add(o2); bookService.batchAdd(batchArgs); }
3.jdbcTemplate实现批量修改和删除操作
(1)批量修改
@Override public void batchUpdateBook(List<Object[]> batchArgs) { String sql = "update t_book set bookName=?,bookPrice=? where bookId=? "; int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs); System.out.println(Arrays.toString(ints)); }
@Test public void testbatchUpdate(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); BookService bookService = context.getBean("bookService", BookService.class); List<Object[]> batchArgs =new ArrayList<>(); Object[] o1 = {"java",50,"4"}; Object[] o2 = {"5",20,"1"}; batchArgs.add(o1); batchArgs.add(o2); bookService.batchUpdate(batchArgs); }
(2)批量删除
public void batchDelete(List<Object[]> batchArgs){ bookDao.batchDeleteBook(batchArgs); }
@Override public void batchDeleteBook(List<Object[]> batchArgs) { String sql = "delete from t_book where bookId=?"; int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs); System.out.println(Arrays.toString(ints)); }
@Test public void testbatchDelete(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); BookService bookService = context.getBean("bookService", BookService.class); List<Object[]> batchArgs =new ArrayList<>(); Object[] o1 = {"1"}; batchArgs.add(o1); bookService.batchDelete(batchArgs); }