【Spring5】JdbcTemplate

JdbcTemplate实现对数据库增删改查

步骤

1. 导入Jar包

mysql-connector-java-8.0.28.jar：mysql数据库连接的相关依赖

spring-tx-5.2.6.RELEASE.jar：Spring对数据库事务操作封装的相关依赖

spring-jdbc-5.2.6.RELEASE.jar：Spring对数据库连接操作封装的相关依赖

spring-orm-5.2.6.RELEASE.jar：Spring整合其他框架如Mybatis、Template需要用到的依赖。

druid-1.1.9.jar：德鲁伊连接池依赖

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

    <context:property-placeholder location="JDBC.properties"></context:property-placeholder>

    <bean id="DruidDataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${p.driverClassName}"></property>
        <property name="url" value="${p.url}"></property>
        <property name="username" value="${p.username}"></property>
        <property name="password" value="${p.password}"></property>
    </bean>

3. 配置JdbcTemplate对象，并注入DataSource

xml

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${p.driverClassName}"></property>
        <property name="url" value="${p.url}"></property>
        <property name="username" value="${p.username}"></property>
        <property name="password" value="${p.password}"></property>
    </bean>

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

BookDaoImpl

@Repository
public class BookDaoImpl implements BookDao {
    @Autowired
    @Qualifier(value = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

@Override
public int add(Book book) {
    String sql = "insert into book(book_name, book_author) values (?, ?)";
    int result = jdbcTemplate.update(sql, book.getBookName(), book.getAuthor());
    return result;
}

4. 创建service和dao类，在service注入Dao，然后在dao里注入JdbcTemplate进行数据库操作

@Service
public class BookService {
@Autowired
private BookDao bookDao;

public void add(Book book) {
    bookDao.add(book);
}

public BookDao getBookDao() {
     return bookDao;
}

public void setBookDao(BookDao bookDao) {
     this.bookDao = bookDao;
}

BookDao

public interface BookDao {
 int add(Book book);
 }

5. 测试

@Test
public void test() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean.xml");
BookService bookService = (BookService) context.getBean("bookService");
//        System.out.println(bookService.bookDao);
           Book book = new Book();
           book.setBookName("黑猫");
           book.setAuthor("爱·伦坡");
           bookService.add(book);
}

修改和删除

BookDaoImpl

@Repository
public class BookDaoImpl implements BookDao {
    @Autowired
    @Qualifier(value = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;


    @Override
    public int add(Book book) {
        String sql = "insert into book(book_name, book_author) values (?, ?)";
        int result = jdbcTemplate.update(sql, book.getBookName(), book.getAuthor());
        return result;
    }

    @Override
    public int modify(int id, Book book) {
        String sql = "update book set book_name=?, book_author=? where book_id=?";
        int result = jdbcTemplate.update(sql, book.getBookName(), book.getAuthor(), id);
        return result;
    }

    @Override
    public int delete(int id) {
        String sql = "delete from book where book_id=?";
        int result = jdbcTemplate.update(sql, id);
        return result;
    }

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

    @Override
    public Book findOne(int id) {
        String sql = "SELECT book_name bookName, book_author author FROM book WHERE book_id = ?";
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
        return book;
    }

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


}

BookDao

public interface BookDao {
 int add(Book book);
 int modify(int id, Book book);
 int delete(int id);
}

BookService

@Service
public class BookService {
    @Autowired
    private BookDao bookDao;
    public void add(Book book) {
    bookDao.add(book);

    public void modifyBook(int id, Book book) {
         bookDao.modify(id, book);
    }

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

测试

@Test
public void test() {
    ApplicationContext context =
                             new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = (BookService) context.getBean("bookService"); 
// Book book = new Book();
// book.setBookName("白夜行");
// book.setAuthor("东野圭吾");
// bookService.add(book);
    bookService.deleteBook(4);
}

@Test
public void test2() {
    ApplicationContext context =
            new ClassPathXmlApplicationContext("bean.xml");
    BookService bookService = (BookService) context.getBean("bookService");

    Book book = new Book();
    book.setBookName("ABC谋杀案");
    book.setAuthor("阿加莎·克里斯蒂");

    bookService.modifyBook(2, book);
}

返回聚合函数的某个值：public T queryForObject(String sql, Class requiredType)

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

查询返回某个对象:public T queryForObject(String sql, RowMapper rowMapper, @Nullable Object... args)

rowMapper是一个接口，new BeanPropertyRowMapper(Book.class)为其接口实现类

@Override
public Book findOne(int id) {
    String sql = "SELECT book_name bookName, book_author author FROM book WHERE book_id = ?";
    Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
    return book;
}

这里sql语句中对字段加入了别名，防止数据库字段名与类名不一致，实际上方法实现了对属性映射的封装，使得能够根据属性名和值得到一个实例

返回集合:public List query(String sql, RowMapper rowMapper)

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

JdbcTemplate实现批量添加功能：

public int[] batchAdd(List<Object[]> batchArgs)，Object数组元素为包含多个字段的一个记录

BookDaoImpl

    @Override
    public int[] batchAdd(List<Object[]> batchArgs) {
        String sql = "insert into book(book_name, book_author) values (?, ?)";
        int[] is = jdbcTemplate.batchUpdate(sql, batchArgs);
        return is;
    }

测试

    @Test
    public void batchAddTest() {
        List<Object[]> list = new ArrayList<>();
        Object[] o1 = {"米泽穗信", "冰菓"};
        Object[] o2 = {"有栖川有栖", "孤岛之谜"};
        Object[] o3 = {"岛田庄司", "斜屋犯罪"};
        list.add(o1);
        list.add(o2);
        list.add(o3);

        ApplicationContext context =
                new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        int[] is = bookService.batchAdd(list);
        System.out.println(Arrays.toString(is));
    }