SpringJdbcTemplate

一、SpringJdbcTemplate(概念和准备)

1.什么是jdbctmplate

(1) Spring框架对JDBC进行封装，使用JdbcTempalte

(2) 准备工作

a.引入相关jar包和依赖

<!-- https://mvnrepository.com/artifact/org.springframework/spring-orm -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>


        <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>


        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>


        <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>

b. 在spring配置文件配置数据库连接池

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

c.配置JdbcTemplate对象，注入DataSource

<!--创建JdbcTemplate对象-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!--注入datasource-->
        <property name="dataSource" ref="dataSource"/>
    </bean>

d.创建service类，创建dao类，在dao注入jdbcTemplate对象

<!--组件扫描-->
    <context:component-scan base-package="com.aotao"/>

• service

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

• dao

@Repository
public class BookDaoImpl implements BookDao {

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

2.JdbcTemplate 操作数据库(添加)

(1) 对应数据库创建实体类

public class Book {
    private String userId;
    private String userName;
    private String uStatus;

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getuStatus() {
        return uStatus;
    }

    public void setuStatus(String uStatus) {
        this.uStatus = uStatus;
    }
    
    
}

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

• 有两个参数
• 第一个参数：sql语句
• 第二个参数：可变参数，设置sql语句值

@Repository
public class BookDaoImpl implements BookDao {

    //注入JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;


    //添加行
    public void add(Book book) {
        
        //sql语句
        String sql = "insert into t_book values(?,?,?)";
        //调用方法实现
//        Object[] args = {book.getUserId(), book.getUserName(), book.getuStatus()};
        int update = jdbcTemplate.update(sql, book.getUserId(), book.getUserName(), book.getuStatus());
        System.out.println(update);
    }

    //更新行
    public void updateBook(Book book){
        String sql = "update t_book set name=?,status=? where id=?";
        Object[] args = {book.getUserName(), book.getuStatus(),book.getUserId()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }
}

(3）编写测试类 

public class TestBook {

    public static void testJdbcTemplate() {
        ApplicationContext context = new ClassPathXmlApplicationContext("booktest.xml");
        BookService bookService = context.getBean("bookService", BookService.class);

        Book book = new Book();
        book.setUserId("4");
        book.setUserName("java");
        book.setuStatus("a");
        bookService.addBook(book);
       
        Book book1 = new Book();
        book1.setUserId("1");
        book1.setUserName("java");
        book1.setuStatus("a");
        bookService.updateBook(book1);
    }

    public static void main(String[] args) {
        testJdbcTemplate();
    }

}

3.JdbcTemplate 操作数据库(查询返回某个值)

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

(2) 使用JdbcTemplate实现查询返回某个值代码

• 有两个参数
• 第一个参数：sql语句
• 第二个参数：返回类型Class

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

4.JdbcTemplate 操作数据库(查询返回对象)

(1) 场景：查询图书详情

• 有三个参数
• 第一个参数：sql语句
• 第二个参数：RowMapper，是接口，返回不同类型数据，使用这个接口里面实现类完成数据封装
• 第三个参数：sql语句值

(2) Jdbc 实现查询返回对象

4.JdbcTemplate 操作数据库(查询返回集合)

public List<Book> findAll() {
        String sql = "select * from t_book";
        List<Book> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
        return query;
    }