Spring-JdbcTemplate

1.什么是JdbcTemplate

(1)spring框架对JDBC进行封装,使用JdbcTemplate方便实现对数据库操作

2.准备工作

(1) 引入相关jar

druid.jar ,mysql.jar , spring-jdbc.jar,spring-tx.jar,spring-orm.jar

(2)在spring配置 连接池

<!--数据源-->
  <bean id="ds" class="com.alibaba.druid.pool.DruidDataSource">
     <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
     <property name="username" value="root"/>
     <property name="password" value="root"/>
     <property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC"/>
  </bean>

(3)配置JdbcTemplate对象, 注入DataSource

<!--创建JDBCTemplate 对象-->
   <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
      <!--配置 数据源-->
      <property name="dataSource" ref="ds"/>
   </bean>

(4)创建service类 创建dao 类,在dao注入jdbcTemplate 对象

在配置文件中 添加: <context:component-scan base-package="com.ly.aop"/>

在service中注入dao, 在dao中注入jdbcTemplate对象

@Repository
public class BookDaoImpl implements BookDao {

    //注入JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
}

@Service
public class BookService {
    //注入dao
    @Autowired
    private BookDao bookDao;
}

3.jdbcTemplate 操作数据库(添加)  

1.对应数据库表,创建实体类

2.编写service 和dao

(1) 在dao中实现添加方法

(2)在JDBCTemplate对象里面调用update方法实现添加操作

@Repository
public class BookDaoImpl implements BookDao {

    //注入JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void save(Book book) {
        String sql = "insert into book(name,status) values(?,?)";
        Object[] args = {book.getName(),book.getStatus()};
        int row = jdbcTemplate.update(sql,args);
        System.out.println("受影响的行数"+row);
    }
}

public void saveBook(Book book){
        bookDao.save(book);
    }

3.编写测试类

@Test
    public void testJdbc(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
        BookService bs = ac.getBean("bookService",BookService.class);
        Book book = new Book("java编程思想","1");
        bs.saveBook(book);
    }

jdbcTemplate 操作数据库(修改和删除) 

@Service
public class BookService {
    //注入dao
    @Autowired
    private BookDao bookDao;


    public void saveBook(Book book){
        bookDao.save(book);
    }

    public void updateBook(Book book){
        bookDao.update(book);
    }

    public void delById(int id){
        bookDao.delete(id);
    }
}

@Repository
public class BookDaoImpl implements BookDao {

    //注入JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void save(Book book) {
        String sql = "insert into book(name,status) values(?,?)";
        Object[] args = {book.getName(),book.getStatus()};
        int row = jdbcTemplate.update(sql,args);
        System.out.println("添加-受影响的行数"+row);
    }

    @Override
    public void update(Book book) {
        String sql = "update book set name=?,status=? where id=? ";
        Object[] args = {book.getName(),book.getStatus(),book.getId()};
        int row = jdbcTemplate.update(sql,args);
        System.out.println("修改-受影响的行数"+row);
    }

    @Override
    public void delete(int id) {
        String sql = "delete from book where id="+id;
        int row = jdbcTemplate.update(sql);
        System.out.println("删除-受影响的行数"+row);
    }
}

测试类:

@Test
    public void testJdbc(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
        BookService bs = ac.getBean("bookService",BookService.class);
       // Book book = new Book("html","1");
       // bs.saveBook(book);//添加

       // bs.delById(1);// 删除

        Book b = new Book(2,"1111","3");
        bs.updateBook(b);//修改
    }

jdbcTemplate 操作数据库(查询返回某个值)

1. 查询表里 一共有多少条数据, 返回是 某个值

2. 使用JdbcTemplate实现,返回某个值

 @Override
    public int selectCount() {
        String sql = " select count(*) from book ";
        Integer count = jdbcTemplate.queryForObject(sql,Integer.class);
        return count;
    }

 

测试类
      int count =  bs.findCount();
        System.out.println(count);

jdbcTemplate 操作数据库(查询返回对象)

1.按照 主键id 查询所有信息

2.使用JdbcTemplate的queryForObject方法实现,返回对象

RowMapper是接口,返回不同类型数据

封装

 //dao
  @Override
     public Book findOne(int id){
         String sql = " select * from book where id=?";
         Book book = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<Book>(Book.class),id);
         return book;
     }
 //service
   public Book findOne(int id){
         return bookDao.findOne(id);
     }

jdbcTemplate 操作数据库(查询返回集合) 

1. 查询 全部图书信息

2. 调用jdbcTemplate方法query实现查询 返回集合

 @Override
    public List<Book> findAllBook() {
        String sql = " select * from book ";
        List<Book> list = jdbcTemplate.query(sql,new BeanPropertyRowMapper<Book>(Book.class));
        return list;
    }

 

 public List<Book> findAll(){
        return bookDao.findAllBook();
    }

jdbcTemplate 操作数据库(批量添加)

1. 批量操作: 操作表里多条记录

   2.JdbcTemplate的batchUpdate 方法实习批量添加

//批量添加
    @Override
    public void batchAdd(List<Object[]> list) {
        String sql = "insert into book(name,status) values(?,?)";
        int[] count = jdbcTemplate.batchUpdate(sql,list);
        System.out.println(Arrays.toString(count));
    }

//测试
 // 批量添加
        List<Object[]> list = new ArrayList<>();
        Object[] obj = {"lisi","1"};
        list.add(obj);
        Object[] obj1 = {"2222","2"};
        list.add(obj1);
        Object[] obj2 = {"44444","4"};
        list.add(obj2);
        bs.batchAdd(list);

jdbcTemplate 操作数据库(批量修改与删除) 

//批量修改
    @Override
    public void batchUpdate(List<Object[]> list) {
        String sql = "update book set name=?,status=? where id=? ";
        int[] count = jdbcTemplate.batchUpdate(sql,list);
        System.out.println(Arrays.toString(count));
    }

测试:      
       //批量修改
        List<Object[]> list = new ArrayList<>();
        Object[] obj = {"22","2",2};
        list.add(obj);
        Object[] obj1 = {"333","3",3};
        list.add(obj1);
        Object[] obj2 = {"4444","4",4};
        list.add(obj2);
        bs.batchUpdate(list);

@Override
    public void batchDel(List<Object[]> list) {
        String sql = "delete from book where id=? ";
        int[] count = jdbcTemplate.batchUpdate(sql,list);
        System.out.println(Arrays.toString(count));
    }

//测试
 //批量删除
        List<Object[]> list = new ArrayList<>();
        Object[] obj = {2};
        list.add(obj);
        Object[] obj1 = {3};
        list.add(obj1);
        Object[] obj2 = {4};
        list.add(obj2);
        bs.batchDel(list);