Spring中的jdbcTemplate模块操作数据库（MySQL）

怼烎@ 2024-04-06 11:34 10阅读 0赞

### 前言 ###

学习spring中自带的jdbcTemplate是如何操作数据库的。

先来个项目目录结构截图：

![5c4beb571dfe4e40b7e32568d313d7e1.png][]

其次，先给出一个完整的xml配置文件，具体的信息解释会在后面说。

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xmlns:aop="http://www.springframework.org/schema/aop"
           xmlns:context="http://www.springframework.org/schema/context"
           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">
    
        
        <context:component-scan base-package="com.atguigu.spring5"></context:component-scan>
    
        
        <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
              destroy-method="close">
            <property name="url" value="jdbc:mysql:///user_db?useUnicode=true&characterEncoding=utf8"/>
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
            <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        </bean>
    
        
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
            
            <property name="dataSource" ref="dataSource"/>
        </bean>
    
    
    </beans>

**目录**

前言

1、在pom文件中引入相关的依赖

2、在数据库中构建表结构

3、在java包下编写实体类

4、编写业务层和dao层

5、创建spring配置文件进行配置

6、编写测试类

7、测试结果显示

8、总结

--------------------

### 1、在pom文件中引入相关的依赖 ###

这儿粘贴了整个pom文件，自己使用时只拿依赖部分即可

<?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>atguiguLearnSpring5</artifactId>
            <groupId>org.example</groupId>
            <version>1.0-SNAPSHOT</version>
        </parent>
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>com.example</groupId>
        <artifactId>Spring5_demo5</artifactId>
    
        <dependencies>
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.10</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.6</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-aop</artifactId>
                <version>5.0.2.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>5.0.2.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-tx</artifactId>
                <version>5.0.2.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-core</artifactId>
                <version>5.3.22</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>5.0.2.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-orm</artifactId>
                <version>5.3.3</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-aspects</artifactId>
                <version>5.0.5.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
                <scope>compile</scope>
            </dependency>
        </dependencies>
    
        
        <build>
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <version>3.1</version>
                    <configuration>
                        <source>1.8</source>
                        <target>1.8</target>
                    </configuration>
                </plugin>
            </plugins>
        </build>
    
    
    </project>

### 2、在数据库中构建表结构 ###

![ba083f05a2be4981aa0bf93016942d79.png][]

### 3、在java包下编写实体类 ###

Book.java

package com.atguigu.spring5.entity;
    
    /**
     * @Author 不要有情绪的  ljy
     * @Date 2022/9/20 14:44
     * @Description:
     */
    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;
        }
    
        @Override
        public String toString() {
            return "Book{" +
                    "UserId='" + UserId + '\'' +
                    ", username='" + username + '\'' +
                    ", ustatus='" + ustatus + '\'' +
                    '}';
        }
    }

### 4、编写业务层和dao层 ###

BookDao.java

package com.atguigu.spring5.dao;
    
    import com.atguigu.spring5.entity.Book;
    
    /**
     * @Author 不要有情绪的  ljy
     * @Date 2022/9/20 14:38
     * @Description:
     */
    public interface BookDao {
        void add(Book book);
    
        void deleteBook(String id);
    
        void updateBook(Book book);
    
        void selectCount();
    
        void selectByUser_id(String id);
    
        void selectAll();
    
        void selectAllByUsername(String username);
    }

BookDaoImpl.java

package com.atguigu.spring5.dao;
    
    import com.atguigu.spring5.entity.Book;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Repository;
    
    import java.util.List;
    
    /**
     * @Author 不要有情绪的  ljy
     * @Date 2022/9/20 14:38
     * @Description:
     */
    
    @Repository
    public class BookDaoImpl implements BookDao {
    
        //注入JdbcTemplate
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        @Override
        public void add(Book book) {
            //1 创建sql语句
            String sql = "insert into t_book values(?,?,?)";
            //2 调用方法
            Object[] args = {book.getUserId(), book.getUsername(), book.getUstatus()};
            int update = jdbcTemplate.update(sql, args);
            System.out.println(update);
    
        }
    
        @Override
        public void deleteBook(String id) {
            String sql = "delete from t_book where user_id = ?";
            int delete = jdbcTemplate.update(sql, id);
            System.out.println(delete);
        }
    
        @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 selectCount() {
            String sql = "select count(*) from t_book";
            Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
            System.out.println(integer);
        }
    
        @Override
        public void selectByUser_id(String id) {
            String username = "java";
            String sql = "select * from t_book where user_id = ?";
            Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
            System.out.println(book);
        }
    
        @Override
        public void selectAll() {
            String sql = "select * from t_book";
            List<Book> books = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Book.class));
            System.out.println(books);
        }
    
        @Override
        public void selectAllByUsername(String username) {
            String sql = "select * from t_book where username = ?";
            List<Book> books = jdbcTemplate.query(sql, new Object[]{username}, new BeanPropertyRowMapper<>(Book.class));
            List<Book> books1 = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Book.class), username);
            System.out.println(books);
            System.out.println(books1);
        }
    }

BookService.java

package com.atguigu.spring5.service;
    
    import com.atguigu.spring5.dao.BookDao;
    import com.atguigu.spring5.entity.Book;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    /**
     * @Author 不要有情绪的  ljy
     * @Date 2022/9/20 14:38
     * @Description:
     */
    @Service
    public class BookService {
        @Autowired
        private BookDao bookDao;
    
        //添加的方法
        public void addBook(Book book){
            bookDao.add(book);
        }
    
        //删除的方法
        public void deleteBook(String id) {
            bookDao.deleteBook(id);
        }
    
        //更新的方法
        public void updateBook(Book book) {
            bookDao.updateBook(book);
        }
    
        //查询返回一共含有多少记录
        public void selectCount(){
            bookDao.selectCount();
        }
    
        //查询得到一个对象
        public void selectByUser_id(String id) {
            bookDao.selectByUser_id(id);
        }
    
        public void selectAll() {
            bookDao.selectAll();
        }
    
        public void selectAllByUsername(String username) {
            bookDao.selectAllByUsername(username);
        }
    }

### 5、创建spring配置文件进行配置 ###

bean1.xml

首先开启组件扫描，即告诉程序要扫描的包，记得在上面的类中添加扫描注解（例如@Service，@Component等）

<context:component-scan base-package="com.atguigu.spring5"></context:component-scan>

配置连接数据库的数据源等信息：

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
              destroy-method="close">
            <property name="url" value="jdbc:mysql:///user_db?useUnicode=true&characterEncoding=utf8"/>
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
            <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        </bean>

在xml文件中配置生成jdbcTemplate对象，并注入数据源属性：

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

以上就完成了配置工作！

### 6、编写测试类 ###

TestBook.java

package com.atguigu.spring5.test;
    
    import com.atguigu.spring5.entity.Book;
    import com.atguigu.spring5.service.BookService;
    import org.junit.Test;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    
    /**
     * @Author 不要有情绪的  ljy
     * @Date 2022/9/20 18:38
     * @Description:
     */
    public class TestBook {
        @Test
        public void testJdbcTemplate(){
            ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
            BookService bookService = context.getBean("bookService", BookService.class);
            Book book = new Book();
            book.setUserId("1");
            book.setUsername("java");
            book.setUstatus("a");
    
    //        bookService.addBook(book);
    //        bookService.updateBook(book);
    //        bookService.deleteBook("1");
    //        bookService.selectCount();
    //        bookService.selectByUser_id("2");
    //        bookService.selectAll();
            bookService.selectAllByUsername("java");
    
        }
    }

### 7、测试结果显示 ###

此时数据库中的数据为：

![e35f8b6c8b214646acc800109dfea520.png][]

得到的测试结果为：

![a566b3eda10b46e6adb8496e0c84aae1.png][]

### 8、总结 ###

spring使用jdbcTemplate可以完成与数据库的交互，可以手写所有的sql语句，给开发人员一种比较直观的感觉。整体上就是引入相关依赖，构建业务逻辑，然后编写相应的sql语句完成数据库操作。当然里面关于所有的sql操作还没有进行完整的学习，例如批量增加等等，我想所有的操作类似而已。

学习之所以会想睡觉，是因为那是梦开始的地方。  
ଘ(੭ˊᵕˋ)੭ （开心） ଘ(੭ˊᵕˋ)੭ （开心）ଘ(੭ˊᵕˋ)੭ （开心）ଘ(੭ˊᵕˋ)੭ （开心）ଘ(੭ˊᵕˋ)੭ （开心）  
\------不写代码不会凸的小刘

[5c4beb571dfe4e40b7e32568d313d7e1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/81dad373477e4a998b918c4068306eca.png
[ba083f05a2be4981aa0bf93016942d79.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/5ef310777a2a4e8b9fcf728b2829dd46.png
[e35f8b6c8b214646acc800109dfea520.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/98ba513262a141ca982c38a7a312b2c0.png
[a566b3eda10b46e6adb8496e0c84aae1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/647a950923da40d09d99776f618d7357.png