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

前言        

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

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

         其次，先给出一个完整的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&amp;characterEncoding=utf8"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    </bean>

    <!--  JdbcTemplate对象  -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!--   注入dataSource     -->
        <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>

    <!--添加这个以后就是用jdk1.8编译，以致于不会出现@Override报错的情况-->
    <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、在数据库中构建表结构

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&amp;characterEncoding=utf8"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    </bean>

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

<!--  JdbcTemplate对象  -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!--   注入dataSource     -->
        <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、测试结果显示

        此时数据库中的数据为：

         得到的测试结果为：

8、总结

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

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