Spring JdbcTemplate操作

JdbcTemplate（概念和准备）

  1. 什么是JdbcTemplate

    1）Spring框架对JDBC进行封装，使用JdbcTemplate方便实现对数据库操作。

  2. 准备工作

    1）引入相关依赖

    <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.10</version>
</dependency>c
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.1.2.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>4.1.2.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>2.5.6</version>
</dependency>

    2）在Spring配置文件中配置数据库连接池

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql://localhost:3306/test"></property>
    <property name="username" value="root"></property>
    <property name="password" value="root"></property>
</bean>

    3）配置JdbcTemplate对象，注入DataSource

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"></property>
</bean>

    4）创建service和dao类，在dao注入JdbcTemplate对象

import nuc.edu.jdbctemplate.dao.BookDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class BookDaoImpl extends BookDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
}

import nuc.edu.jdbctemplate.dao.BookDao;
import org.springframework.beans.factory.annotation.Autowired;

public class BookService {
    @Autowired
    private BookDao bookDao;
}

Jdbctemplate操作数据库（添加）

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

    public class Book {
    private int id;
    private String username;
    private String status;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }
}

  2. 编写service和dao

    1）在dao进行数据库添加操作

    import nuc.edu.jdbctemplate.dao.BookDao;
import nuc.edu.jdbctemplate.emtity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class BookDaoImpl implements BookDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
}

    2）调用jdbctemplate里面的uodate方法实现添加操作

    @Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void add(Book book) {
    String sql = "insert into book values(?,?,?)";
    int update = jdbcTemplate.update(sql, book.getId(), book.getUsername(), book.getStatus());
    System.out.println(update);
}

    ​ ①有两个参数

    ​ ②第一个参数:sql语句

    ​ ③第二个语句：可变参数，设置sql语句值

  3. 测试类

    import nuc.edu.jdbctemplate.emtity.Book;
import nuc.edu.jdbctemplate.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class BookTest {
    @Test
    public void show(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        Book book = new Book();
        book.setId(1);
        book.setUsername("zhangsan");
        book.setStatus("y");
        bookService.addBook(book);
    }
}

Jdbctemplate操作数据库（修改和删除）

@Override
public void update(Book book) {
    String sql = "update book set username = ?,status=? where id = ?";
    Object[] args = {book.getUsername(),book.getStatus(),book.getId()};
    int update = jdbcTemplate.update(sql, args);
    System.out.println(update);
}

@Override
public void delete(int id) {
    String sql = "delete from book where id = ?";
    int update = jdbcTemplate.update(sql, id);
    System.out.println(update);
}

Jdbctemplate操作数据库（查询返回某个值）

  1. 查询表里面有多少条记录，返回是某个值

  2. 使用Jdbctemplate实现查询返回多个值的方法

    • 有两个参数
      • 第一个参数，sql语句
      • 第二个参数，返回类型Class
    @Override
public int select() {
    String sql = "select count(*) from book";
    Integer line = jdbcTemplate.queryForObject(sql, Integer.class);
    return line;
}

Jdbctemplate操作数据库（查询返回对象）

  1. 场景：查询图书详情

  2. Jdbctemplate实现查询返回对象

    • 有三个参数
      • 第一个参数：sql语句
      • 第二个参数：RowMapper，是一个接口，针对返回不同类型数据，使用这个接口里面实现类完成数据封装
      • 第三个参数，sql语句值
    public Book selectOne(int id) {
    String sql = "select * from book where id = ?";
    Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class),id);
    return book;
}

Jdbctemplate操作数据库（查询返回集合）

  1. 场景：查询图书列表分页...

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

    public void selectAll(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    List<Book> bookList = bookService.selectAll();
    System.out.println(bookList);
}

Jdbctemplate操作数据库（批量操作）

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

  2. Jdbctemplate实现批量添加操作

    1. Snipaste_2020-10-21_19-08-47

      • 有两个参数
        • 第一个参数：sql语句
        • 第二个参数：List集合，添加多条记录数据
      @Override
public void batchAddBook(List<Object[]> batchArgs) {
    String sql = "insert into book values(?,?,?)";
    int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
    System.out.println(Arrays.toString(ints));
}

      public void batchAdd(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    List<Object[]> batchArgs = new ArrayList<>();
    Object[] o1 = {5,"tianqi","Y"};
    Object[] o2 = {6,"yangba","Y"};
    Object[] o3 = {7,"houjiu","N"};
    batchArgs.add(o1);
    batchArgs.add(o2);
    batchArgs.add(o3);
    bookService.bathAdd(batchArgs);
}

  3. 测试结果

    Snipaste_2020-10-21_19-23-30

Jdbctemplate实现批量修改操作

@Override
public void batchUpdate(List<Object[]> batchArgs) {
    String sql = "update book set username=?,status=? where id=?";
    int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
    System.out.println(Arrays.toString(ints));
}

@Test
public void bathUpdate(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    ArrayList<Object[]> batchArgs = new ArrayList<>();
    Object[] o1 = {"jishi","Y",2};
    batchArgs.add(o1);
    bookService.bathUpdate(batchArgs);
}

​ 结果：

Snipaste_2020-10-21_19-38-49

Jdbctemplate实现批量删除操作

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

@Test
public void bathDelete(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    ArrayList<Object[]> batchArgs = new ArrayList<>();
    Object[] o1 = {1};
    Object[] o2 = {2};
    batchArgs.add(o1);
    batchArgs.add(o2);

    bookService.bathDelete(batchArgs);
}

​ 结果：

Snipaste_2020-10-21_19-45-45

posted @ 2020-10-21 19:48  codeFiler  阅读(65)  评论(0)  编辑  收藏  举报