Spring总结八：jdbcTemplate的简单使用

简介：

　　Srping毕竟是一站式框架，所以也有操作数据库的东西，那就是jdbcTemplate，介绍一下jdbcTemplate的简单使用。

 

除了要引入必要的包以外，还需要引入 spring-jdbc

直接使用：

//配置数据库连接
DriverManagerDataSource dataSource= new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/temp_db");
dataSource.setUsername("root");
dataSource.setPassword("root");
//创建jdbcTemplate
JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource);
//执行sql
String sql="insert into users values(null,'张惠妹',17)";
jdbcTemplate.execute(sql);

和配置文件结合使用：

Book实体类和BookDao：

package com.zy.domain;

public class Book {
    private int id;
    private String name;
    private double price;

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", price=" + price +
                '}';
    }
}

public class BookDao {
    private JdbcTemplate jdbcTemplate;

    //set方法
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    //新增
    public void addBook(Book book) {
        String sql = "insert into book values(null,?,?)";
        Object[] paramArr = {book.getName(), book.getPrice()};
        jdbcTemplate.update(sql, paramArr);
    }

    //修改
    public void editBook(Book book) {
        String sql = "update book set name=?, price=? where id=?";
        Object[] paramArr = {book.getName(), book.getPrice(), book.getId()};
        jdbcTemplate.update(sql, paramArr);
    }

    //删除
    public void delBook(int id) {
        String sql = "delete form book where id=?";
        jdbcTemplate.update(sql, id);
    }

    // 查询书名
    public String getNameById(int id){
        String sql = "select name from book where id=?";
        String bookname = jdbcTemplate.queryForObject(sql, String.class, id);
        return bookname;
    }

    // 查询总记录数
    public int getCount() {
        String sql = "select count(*) from book";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count;
    }

    // 查询某本书信息
    public Book getBookById(int id) {
        String sql = "select * from book where id=?";
        Book book = jdbcTemplate.queryForObject(sql,  ParameterizedBeanPropertyRowMapper.newInstance(Book.class), id);
        return book;
    }

    // 查询所有书集合
    public List<Book> getAllBooks() {
        String sql = "select * from book";
        List<Book> bookList = jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Book.class));
        return bookList;
    }
}

添加数据库配置文件jdbc.properties

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/temp_db?useUnicode=true&characterEncoding=utf8&autoReconnect=true\
  &allowMultiQueries=true
jdbc.username=root
jdbc.password=root

然后配置我们的spring配置文件 applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="
       http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context.xsd
">

    <!--1 引入jdbc配置文件-->
    <context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder>

    <!--2 根据jdbc配置文件 配置c3po连接池-->
    <bean id="c3poDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/temp_db"/>
        <property name="user" value="root"/>
        <property name="password" value="root"/>
    </bean>

    <!--3 jdbc 模板-->
    <bean id="jdbcTemplate123" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="c3poDataSource"/>
    </bean>

    <!--4 配置bookdao使用的jdbcTemplate-->
    <bean id="bookDao" class="com.zy.dao.BookDao">
        <property name="jdbcTemplate" ref="jdbcTemplate123"></property>

        <!--下面这个配置 在我们的BookDao继承了JdbcDaoSupport以后 可以直接略过第三步 直接给bookDao配置数据库连接-->
        <!--<property name="dataSource" ref="c3poDataSource"></property>-->
    </bean>
</beans>

测试：

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class BookDaoTest {
    @Value("#{bookDao}")
    BookDao bookDao;

    @Test
    public void setJdbcTemplate() throws Exception {
        Book book = new Book();
        book.setName("凌波微步");
        book.setPrice(20);

        bookDao.addBook(book);
    }

    @Test
    public void getNameById() throws Exception {
        String name = bookDao.getNameById(1);
        System.out.println(name);
    }

    @Test
    public void getAllBooks() throws Exception {
        List<Book> list = bookDao.getAllBooks();
        for (Book book : list) {
            System.out.println(book);
        }
    }


}

--------------------------------------------------------------------以上。