Spring-JdbcTemplate案例-CSDN博客

本文链接：https://blog.csdn.net/Csu__/article/details/128335368

本案例重在学会使用JdbcTemplate来实现Spring的增删改查

先导pom依赖，【我的jdk是8 mybatis也是8，其他版本不用关注】

<?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">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.nuo</groupId>
    <artifactId>day01spring</artifactId>
    <version>1.0-SNAPSHOT</version>


    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <dependencies>
        <!--spring核心依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.1.6.RELEASE</version>
        </dependency>
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>

        <!--druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.15</version>
        </dependency>

        <!--jdbcTemplate依赖所在-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.1.6.RELEASE</version>
        </dependency>

        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency>

        <!--junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
        </dependency>
    </dependencies>

</project>

创建表


CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `birthday` date DEFAULT NULL,
  `sex` char(1) DEFAULT '男',
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3
-- 数据请自行导入【因为我找的以前的表插入数据语句找不到了。。】

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	   xmlns:p="http://www.springframework.org/schema/p"
	   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	   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">
	<context:component-scan base-package="com.nuo"/>
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	<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:///heima"></property>
		<property name="username" value="root"></property>
		<property name="password" value="root"></property>
	</bean>
</beans>

创建User实体类映射数据库表，这里我用的lomBok方便一点，idea2021以后不用再下载LomBok插件，直接在pom文件导入依赖即可

package com.nuo.pojo;

import lombok.*;
import java.sql.Date;


@AllArgsConstructor
@NoArgsConstructor
@Data
public class User {
       private Integer id;
       private String userName;
       private Date birthday;
       private String sex;
       private String address;
}

创建持久层接口

package com.nuo.dao;

import com.nuo.pojo.User;

import java.util.List;

public interface UserDao {
    //增
    void add(User user);

    //根据id删
    void deleteByid(Integer id);

    //根据id改
    void updateById(User user);

    //查所有
    List<User> getAll();

    //根据id查
    User getById(Integer id);
}

创建持久层实现类

package com.nuo.dao.impl;

import com.nuo.dao.UserDao;
import com.nuo.pojo.User;
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;
@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
//JdbcTemplate的update()方法:执行DML语句，执行增删改语句：
    @Override
    public void add(User user) {
        jdbcTemplate.update("insert into user values (null,?,?,?,?)",
               user.getUserName(),user.getBirthday(),user.getSex(),user.getAddress());

    }

    @Override
    public void deleteByid(Integer id) {
        jdbcTemplate.update("delete from user where id  = ?",id);
    }

    @Override
    public void updateById(User user) {
        jdbcTemplate.update("update user set username  = ?,sex = ? where id = ?",
                user.getUserName(),user.getSex(),user.getId());
    }

    @Override
    public List<User> getAll() {
//JdbcTemplate的query()方法:得到list集合
        return jdbcTemplate.query("select * from user",new BeanPropertyRowMapper<User>(User.class));

    }

    @Override
    public User getById(Integer id) {
        //JdbcTemplate的queryForObject()方法:得到一个对象
        return jdbcTemplate.queryForObject("select * from user where id = ?",
                new BeanPropertyRowMapper<User>(User.class),id);
    }


}

UserService

package com.nuo.service;

import com.nuo.pojo.User;

import java.util.List;

public interface UserService {
    //增
    void add(User user);

    //根据id删
    void deleteByid(Integer id);

    //根据id改
    void updateById(User user);

    //查所有
    List<User> getAll();

    //根据id查
    User getById(Integer id);
}

UserServiceImpl

package com.nuo.service.impl.UserServiceImpl;

import com.nuo.dao.UserDao;
import com.nuo.pojo.User;
import com.nuo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserDao userDao;

    @Override
    public void add(User user) {
        userDao.add(user);
    }

    @Override
    public void deleteByid(Integer id) {
        userDao.deleteByid(id);
    }

    @Override
    public void updateById(User user) {
        userDao.updateById(user);
    }

    @Override
    public List<User> getAll() {
        return userDao.getAll();
    }

    @Override
    public User getById(Integer id) {
        return userDao.getById(id);
    }
}

测试类

import com.nuo.pojo.User;
import com.nuo.service.UserService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.sql.Date;
import java.util.List;

public class UserTest {
    ApplicationContext ioc = new ClassPathXmlApplicationContext("applicationContext.xml");
    @Test
    public void testAdd(){
        UserService userService = ioc.getBean(UserService.class);
        User user = new User();
        user.setId(5);
        user.setUserName("唐僧");
        user.setBirthday(Date.valueOf("1647-01-01"));
        user.setSex("男");
        user.setAddress("嘉兴");
        userService.add(user);
    }
    @Test
    public void testDelete(){
        //这里我用的user表id是其他表的外键，故删除失败，怎么删除知道就好
        UserService userService = ioc.getBean(UserService.class);
        userService.deleteByid(2);
    }
    @Test
    public void testUpdate(){
        UserService userService = ioc.getBean(UserService.class);
        User user = new User();
        user.setUserName("沙和尚");
        user.setSex("男");
        user.setId(4);
        userService.updateById(user);

    }
    @Test
    public void testGetAll(){
        UserService userService = ioc.getBean(UserService.class);
        List<User> all = userService.getAll();
        for (User user : all) {
            System.out.println("user = " + user);
        }
    }

    @Test
    public void testGetById(){
        UserService userService = ioc.getBean(UserService.class);
        User user = userService.getById(3);
        System.out.println("user = " + user);
    }
}