本人已参与[新人创作礼]活动，一起开启掘金创作之路。

一、JdbcTemplate是什么和准备工作

Spring框架对JDBC进行封装，使用JDBCTemplate方便实现对数据库操作.

1.1.引入相关maven依赖.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>spring2022</artifactId>
        <groupId>cn.hncj</groupId>
        <version>1.0</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>springJDBCTemplate</artifactId>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.3.16</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.9.8</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>5.8.2</version>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-engine</artifactId>
            <version>5.8.2</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
            <scope>provided</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.3.17</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-orm -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>5.3.17</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.17</version>
        </dependency>


    </dependencies>
</project>

1.2在spring配置文件配置数据库连接

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       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
                           http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="jdbc.mysql.cj.jdbc.Driver"></property><!--驱动名称-->
        <property name="url" value="jdbc:mysql:///db"></property><!--数据库地址-->
        <property name="username" value="root"></property><!--连接数据库用户名-->
        <property name="password" value=""></property><!--连接数据库用户密码-->
    </bean>
</beans>

1.3配置JdbcTemplate对象，注入DateSource

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

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

package cn.hncj.dao;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
public interface BookDao {
}





package cn.hncj.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
@Repository
public class BookDaoImpl implements BookDao{
    //注入jdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;

}




package cn.hncj.service;

import cn.hncj.dao.BookDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
@Service
public class BookService {
    //注入dao
    @Autowired
    private BookDao bookdao;

}

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       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
                           http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="jdbc.mysql.cj.jdbc.Driver"></property><!--驱动名称-->
        <property name="url" value="jdbc:mysql:///db"></property><!--数据库地址-->
        <property name="username" value="root"></property><!--连接数据库用户名-->
        <property name="password" value=""></property><!--连接数据库用户密码-->
    </bean>
    <!--JDBCTemplate对象-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!--注入dataSource-->
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <!--开启组件扫描-->
    <context:component-scan base-package="cn.hncj"></context:component-scan>
</beans>

二、jdbcTemplate操作数据库：添加功能

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

2.编写service和dao
		*1.在dao进行数据库添加操作
		*2.调用JdbcTemplate对象里面update方法实现添加操作
					*第一个参数：sql语句
					*第二个参数：可变参数，设置sql语句值

步骤如下： **创建t_book表

package cn.hncj.dao;

import cn.hncj.entity.Book;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
public interface BookDao {
    //添加的方法
    void add(Book book);
}





package cn.hncj.dao;

import cn.hncj.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
@Repository
public class BookDaoImpl implements BookDao{
    //注入jdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
    //添加方法
    @Override
    public void add(Book book) {
        //创建sql语句
        String sql="insert into t_book value(?,?,?)";
        Object[] args= {book.getUserId(), book.getUsername(), book.getUstatus()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }
}





package cn.hncj.entity;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
public class Book {
    private String UserId;
    private String username;
    private String ustatus;

    public String getUserId() {
        return UserId;
    }

    public void setUserId(String userId) {
        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;
    }
}





package cn.hncj.service;

import cn.hncj.dao.BookDao;
import cn.hncj.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
@Service
public class BookService {
    //注入dao
    @Autowired
    private BookDao bookdao;

    //添加的方法
    public void addBook(Book book){
        bookdao.add(book);
    }
}





import cn.hncj.entity.Book;
import cn.hncj.service.BookService;
import org.junit.jupiter.api.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
public class TestBook {
    @Test
    public void testJdbcTemplate() {
        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        Book book =new Book();
        book.setUserId("1");
        book.setUsername("java");
        book.setUstatus("a");
        bookService.addBook(book);
    }
}

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       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
                           http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property><!--驱动名称-->
        <property name="url" value="jdbc:mysql:///db"></property><!--数据库地址-->
        <property name="username" value="root"></property><!--连接数据库用户名-->
        <property name="password" value=""></property><!--连接数据库用户密码-->
    </bean>
    <!--JDBCTemplate对象-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!--注入dataSource-->
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <!--开启组件扫描-->
    <context:component-scan base-package="cn.hncj"></context:component-scan>
</beans>

三、jdbcTemplate操作数据库：修改和删除功能

package cn.hncj.dao;

import cn.hncj.entity.Book;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
public interface BookDao {
    //添加的方法
    void add(Book book);

    void updateBook(Book book);

    void delete(String id);
}



package cn.hncj.dao;

import cn.hncj.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
@Repository
public class BookDaoImpl implements BookDao {
    //注入jdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;

    //添加方法
    @Override
    public void add(Book book) {
        //创建sql语句
        String sql = "insert into t_book value(?,?,?)";
        Object[] args = {book.getUserId(), book.getUsername(), book.getUstatus()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }

    //修改
    @Override
    public void updateBook(Book book) {
        String sql = "update t_book set username=?,ustatus=? where user_id=?";
        Object[] args = {book.getUsername(), book.getUstatus(), book.getUserId()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }

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





package cn.hncj.entity;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
public class Book {
    private String UserId;
    private String username;
    private String ustatus;

    public String getUserId() {
        return UserId;
    }

    public void setUserId(String userId) {
        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;
    }
}



package cn.hncj.service;

import cn.hncj.dao.BookDao;
import cn.hncj.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
@Service
public class BookService {
    //注入dao
    @Autowired
    private BookDao bookdao;

    //添加的方法
    public void addBook(Book book){
        bookdao.add(book);
    }
    //修改方法
    public void updateBook(Book book){
        bookdao.updateBook(book);
    }
    //删除方法
    public void deleteBook(String id){
        bookdao.delete(id);
    }
}



import cn.hncj.entity.Book;
import cn.hncj.service.BookService;
import org.junit.jupiter.api.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
public class TestBook {
    @Test
    public void testJdbcTemplate() {
        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        Book book = new Book();
//        //添加
//        book.setUserId("1");
//        book.setUsername("java");
//        book.setUstatus("a");
//        bookService.addBook(book);
        //修改
        book.setUserId("1");
        book.setUsername("javaupup");
        book.setUstatus("cn.hncj");
        bookService.updateBook(book);
    }
}

测试类中添加如下代码:

  bookService.deleteBook("1");

四、jdbcTemplate操作数据库查询功能

4.1查询返回某个值

1*查询表里有多少条级联，返回是某个值
2*使用JdbcTemplate实现查询返回某个值代码
			*有两个参数： 第一个参数：sql语句; 第二个参数：返回类型class

package cn.hncj.dao;

import cn.hncj.entity.Book;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
public interface BookDao {
    //添加的方法
    void add(Book book);

    void updateBook(Book book);

    void delete(String id);

    int selectCount();
}




package cn.hncj.dao;

import cn.hncj.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
@Repository
public class BookDaoImpl implements BookDao {
    //注入jdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;

    //添加方法
    @Override
    public void add(Book book) {
        //创建sql语句
        String sql = "insert into t_book value(?,?,?)";
        Object[] args = {book.getUserId(), book.getUsername(), book.getUstatus()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }

    //修改
    @Override
    public void updateBook(Book book) {
        String sql = "update t_book set username=?,ustatus=? where user_id=?";
        Object[] args = {book.getUsername(), book.getUstatus(), book.getUserId()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }

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

    //查询表记录数
    @Override
    public int selectCount() {
        String sql = "select count(*) from t_book";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count;
    }
}


package cn.hncj.entity;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
public class Book {
    private String UserId;
    private String username;
    private String ustatus;

    public String getUserId() {
        return UserId;
    }

    public void setUserId(String userId) {
        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;
    }
}

package cn.hncj.service;

import cn.hncj.dao.BookDao;
import cn.hncj.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
@Service
public class BookService {
    //注入dao
    @Autowired
    private BookDao bookdao;

    //添加的方法
    public void addBook(Book book) {
        bookdao.add(book);
    }

    //修改方法
    public void updateBook(Book book) {
        bookdao.updateBook(book);
    }

    //删除方法
    public void deleteBook(String id) {
        bookdao.delete(id);
    }

    //查询表记录数
    public int findCount() {
        return bookdao.selectCount();

    }
}


import cn.hncj.entity.Book;
import cn.hncj.service.BookService;
import org.junit.jupiter.api.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 * Created on 2022/3/22.
 *
 * @author Hou chaof
 */
public class TestBook {
    @Test
    public void testJdbcTemplate() {
        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        BookService bookService = context.getBean("bookService", BookService.class);
        Book book = new Book();
//        //添加
//        book.setUserId("1");
//        book.setUsername("java");
//        book.setUstatus("a");
//        bookService.addBook(book);
//        //修改
//        book.setUserId("1");
//        book.setUsername("javaupup");
//        book.setUstatus("cn.hncj");
//        bookService.updateBook(book);
//
//        bookService.deleteBook("1");
        //查询返回某个值
        int count = bookService.findCount();
        System.out.println(count);
    }

}