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

    前言        

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

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

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

    1. "1.0" encoding="UTF-8"?>
    2. <beans xmlns="http://www.springframework.org/schema/beans"
    3.        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    4.        xmlns:aop="http://www.springframework.org/schema/aop"
    5.        xmlns:context="http://www.springframework.org/schema/context"
    6.        xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
    7.         http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
    8.         http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
    9.  
    10.     
    11.     <context:component-scan base-package="com.atguigu.spring5">context:component-scan>
    12.  
    13.     
    14.     <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
    15.           destroy-method="close">
    16.         <property name="url" value="jdbc:mysql:///user_db?useUnicode=true&characterEncoding=utf8"/>
    17.         <property name="username" value="root"/>
    18.         <property name="password" value="123456"/>
    19.         <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    20.     bean>
    21.  
    22.     
    23.     <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    24.         
    25.         <property name="dataSource" ref="dataSource"/>
    26.     bean>
    27.  
    28.  
    29. beans>

    目录

    前言        

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

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

    3、在java包下编写实体类

    4、编写业务层和dao层

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

    6、编写测试类

    7、测试结果显示

    8、总结

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

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

    1. "1.0" encoding="UTF-8"?>
    2. <project xmlns="http://maven.apache.org/POM/4.0.0"
    3.          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    4.          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    5.     <parent>
    6.         <artifactId>atguiguLearnSpring5artifactId>
    7.         <groupId>org.examplegroupId>
    8.         <version>1.0-SNAPSHOTversion>
    9.     parent>
    10.     <modelVersion>4.0.0modelVersion>
    11.  
    12.     <groupId>com.examplegroupId>
    13.     <artifactId>Spring5_demo5artifactId>
    14.  
    15.     <dependencies>
    16.         <dependency>
    17.             <groupId>com.alibabagroupId>
    18.             <artifactId>druidartifactId>
    19.             <version>1.1.10version>
    20.         dependency>
    21.         <dependency>
    22.             <groupId>mysqlgroupId>
    23.             <artifactId>mysql-connector-javaartifactId>
    24.             <version>5.1.6version>
    25.         dependency>
    26.         <dependency>
    27.             <groupId>org.springframeworkgroupId>
    28.             <artifactId>spring-aopartifactId>
    29.             <version>5.0.2.RELEASEversion>
    30.         dependency>
    31.         <dependency>
    32.             <groupId>org.springframeworkgroupId>
    33.             <artifactId>spring-contextartifactId>
    34.             <version>5.0.2.RELEASEversion>
    35.         dependency>
    36.         <dependency>
    37.             <groupId>org.springframeworkgroupId>
    38.             <artifactId>spring-txartifactId>
    39.             <version>5.0.2.RELEASEversion>
    40.         dependency>
    41.         <dependency>
    42.             <groupId>org.springframeworkgroupId>
    43.             <artifactId>spring-coreartifactId>
    44.             <version>5.3.22version>
    45.         dependency>
    46.         <dependency>
    47.             <groupId>org.springframeworkgroupId>
    48.             <artifactId>spring-jdbcartifactId>
    49.             <version>5.0.2.RELEASEversion>
    50.         dependency>
    51.         <dependency>
    52.             <groupId>org.springframeworkgroupId>
    53.             <artifactId>spring-ormartifactId>
    54.             <version>5.3.3version>
    55.         dependency>
    56.         <dependency>
    57.             <groupId>org.springframeworkgroupId>
    58.             <artifactId>spring-aspectsartifactId>
    59.             <version>5.0.5.RELEASEversion>
    60.         dependency>
    61.         <dependency>
    62.             <groupId>junitgroupId>
    63.             <artifactId>junitartifactId>
    64.             <version>4.12version>
    65.             <scope>compilescope>
    66.         dependency>
    67.     dependencies>
    68.  
    69.     
    70.     <build>
    71.         <plugins>
    72.             <plugin>
    73.                 <groupId>org.apache.maven.pluginsgroupId>
    74.                 <artifactId>maven-compiler-pluginartifactId>
    75.                 <version>3.1version>
    76.                 <configuration>
    77.                     <source>1.8source>
    78.                     <target>1.8target>
    79.                 configuration>
    80.             plugin>
    81.         plugins>
    82.     build>
    83.  
    84.  
    85. project>

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

    3、在java包下编写实体类

            Book.java

    1. package com.atguigu.spring5.entity;
    2.  
    3. /**
    4.  * @Author 不要有情绪的  ljy
    5.  * @Date 2022/9/20 14:44
    6.  * @Description:
    7.  */
    8. public class Book {
    9.     private String UserId;
    10.     private String username;
    11.     private String ustatus;
    12.  
    13.     public String getUserId() {
    14.         return UserId;
    15.     }
    16.  
    17.     public void setUserId(String userId) {
    18.         UserId = userId;
    19.     }
    20.  
    21.     public String getUsername() {
    22.         return username;
    23.     }
    24.  
    25.     public void setUsername(String username) {
    26.         this.username = username;
    27.     }
    28.  
    29.     public String getUstatus() {
    30.         return ustatus;
    31.     }
    32.  
    33.     public void setUstatus(String ustatus) {
    34.         this.ustatus = ustatus;
    35.     }
    36.  
    37.     @Override
    38.     public String toString() {
    39.         return "Book{" +
    40.                 "UserId='" + UserId + '\'' +
    41.                 ", username='" + username + '\'' +
    42.                 ", ustatus='" + ustatus + '\'' +
    43.                 '}';
    44.     }
    45. }

    4、编写业务层和dao层

            BookDao.java

    1. package com.atguigu.spring5.dao;
    2.  
    3. import com.atguigu.spring5.entity.Book;
    4.  
    5. /**
    6.  * @Author 不要有情绪的  ljy
    7.  * @Date 2022/9/20 14:38
    8.  * @Description:
    9.  */
    10. public interface BookDao {
    11.     void add(Book book);
    12.  
    13.     void deleteBook(String id);
    14.  
    15.     void updateBook(Book book);
    16.  
    17.     void selectCount();
    18.  
    19.     void selectByUser_id(String id);
    20.  
    21.     void selectAll();
    22.  
    23.     void selectAllByUsername(String username);
    24. }

        BookDaoImpl.java

    1. package com.atguigu.spring5.dao;
    2.  
    3. import com.atguigu.spring5.entity.Book;
    4. import org.springframework.beans.factory.annotation.Autowired;
    5. import org.springframework.jdbc.core.BeanPropertyRowMapper;
    6. import org.springframework.jdbc.core.JdbcTemplate;
    7. import org.springframework.stereotype.Repository;
    8.  
    9. import java.util.List;
    10.  
    11. /**
    12.  * @Author 不要有情绪的  ljy
    13.  * @Date 2022/9/20 14:38
    14.  * @Description:
    15.  */
    16.  
    17. @Repository
    18. public class BookDaoImpl implements BookDao {
    19.  
    20.     //注入JdbcTemplate
    21.     @Autowired
    22.     private JdbcTemplate jdbcTemplate;
    23.  
    24.     @Override
    25.     public void add(Book book) {
    26.         //1 创建sql语句
    27.         String sql = "insert into t_book values(?,?,?)";
    28.         //2 调用方法
    29.         Object[] args = {book.getUserId(), book.getUsername(), book.getUstatus()};
    30.         int update = jdbcTemplate.update(sql, args);
    31.         System.out.println(update);
    32.  
    33.     }
    34.  
    35.     @Override
    36.     public void deleteBook(String id) {
    37.         String sql = "delete from t_book where user_id = ?";
    38.         int delete = jdbcTemplate.update(sql, id);
    39.         System.out.println(delete);
    40.     }
    41.  
    42.     @Override
    43.     public void updateBook(Book book) {
    44.         String sql = "update t_book set username = ?,ustatus=? where user_id = ?";
    45.         Object[] args = {book.getUsername(), book.getUstatus(), book.getUserId()};
    46.         int update = jdbcTemplate.update(sql, args);
    47.         System.out.println(update);
    48.     }
    49.  
    50.     @Override
    51.     public void selectCount() {
    52.         String sql = "select count(*) from t_book";
    53.         Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
    54.         System.out.println(integer);
    55.     }
    56.  
    57.     @Override
    58.     public void selectByUser_id(String id) {
    59.         String username = "java";
    60.         String sql = "select * from t_book where user_id = ?";
    61.         Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(Book.class), id);
    62.         System.out.println(book);
    63.     }
    64.  
    65.     @Override
    66.     public void selectAll() {
    67.         String sql = "select * from t_book";
    68.         List books = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Book.class));
    69.         System.out.println(books);
    70.     }
    71.  
    72.     @Override
    73.     public void selectAllByUsername(String username) {
    74.         String sql = "select * from t_book where username = ?";
    75.         List books = jdbcTemplate.query(sql, new Object[]{username}, new BeanPropertyRowMapper<>(Book.class));
    76.         List books1 = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Book.class), username);
    77.         System.out.println(books);
    78.         System.out.println(books1);
    79.     }
    80. }

        BookService.java

    1. package com.atguigu.spring5.service;
    2.  
    3. import com.atguigu.spring5.dao.BookDao;
    4. import com.atguigu.spring5.entity.Book;
    5. import org.springframework.beans.factory.annotation.Autowired;
    6. import org.springframework.stereotype.Service;
    7.  
    8. /**
    9.  * @Author 不要有情绪的  ljy
    10.  * @Date 2022/9/20 14:38
    11.  * @Description:
    12.  */
    13. @Service
    14. public class BookService {
    15.     @Autowired
    16.     private BookDao bookDao;
    17.  
    18.     //添加的方法
    19.     public void addBook(Book book){
    20.         bookDao.add(book);
    21.     }
    22.  
    23.     //删除的方法
    24.     public void deleteBook(String id) {
    25.         bookDao.deleteBook(id);
    26.     }
    27.  
    28.     //更新的方法
    29.     public void updateBook(Book book) {
    30.         bookDao.updateBook(book);
    31.     }
    32.  
    33.     //查询返回一共含有多少记录
    34.     public void selectCount(){
    35.         bookDao.selectCount();
    36.     }
    37.  
    38.     //查询得到一个对象
    39.     public void selectByUser_id(String id) {
    40.         bookDao.selectByUser_id(id);
    41.     }
    42.  
    43.     public void selectAll() {
    44.         bookDao.selectAll();
    45.     }
    46.  
    47.     public void selectAllByUsername(String username) {
    48.         bookDao.selectAllByUsername(username);
    49.     }
    50. }

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

            bean1.xml

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

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

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

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

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

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

            以上就完成了配置工作！

    6、编写测试类

            TestBook.java

    1. package com.atguigu.spring5.test;
    2.  
    3. import com.atguigu.spring5.entity.Book;
    4. import com.atguigu.spring5.service.BookService;
    5. import org.junit.Test;
    6. import org.springframework.context.ApplicationContext;
    7. import org.springframework.context.support.ClassPathXmlApplicationContext;
    8.  
    9. /**
    10.  * @Author 不要有情绪的  ljy
    11.  * @Date 2022/9/20 18:38
    12.  * @Description:
    13.  */
    14. public class TestBook {
    15.     @Test
    16.     public void testJdbcTemplate(){
    17.         ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    18.         BookService bookService = context.getBean("bookService", BookService.class);
    19.         Book book = new Book();
    20.         book.setUserId("1");
    21.         book.setUsername("java");
    22.         book.setUstatus("a");
    23.  
    24. //        bookService.addBook(book);
    25. //        bookService.updateBook(book);
    26. //        bookService.deleteBook("1");
    27. //        bookService.selectCount();
    28. //        bookService.selectByUser_id("2");
    29. //        bookService.selectAll();
    30.         bookService.selectAllByUsername("java");
    31.  
    32.     }
    33. }

    7、测试结果显示

            此时数据库中的数据为：

             得到的测试结果为：

    8、总结

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

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

  • 相关阅读:
    Springboot项目部署到服务器
    家庭的破碎与人性的考验
    Java常用类
    在Github上封神的JDK源码，看完竟吊打了面试官，厉害了
    Yapi浏览器插件
    时序处理的一些命令
    ps抠图怎么抠出来，自学ps软件photoshop2022，ps怎么抠出想要的部分-笔记记录
    Linux的目录结构
    车联网白皮书（2021.12）中国信息通信研究院
    基于springboot在线考试报名系统毕业设计源码031706
  • 原文地址：https://blog.csdn.net/qq_40834643/article/details/126974980