Spring_06：Spring JdbcTemplate入门

于 2021-03-31 18:05:38 发布
阅读量89 收藏
点赞数
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qianyikang/article/details/115351691
本文展示了如何在Spring框架中使用JDBC进行数据库操作，包括创建`t_user`表并插入初始数据，配置数据库连接池Druid，创建User实体类，UserDao接口及其实现类，UserService服务类，并编写测试用例进行增删改查及登录功能的测试。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

创建数据库——simonshop
在这里插入图片描述
创建表t_user插入记录
CREATE TABLEt_user(idint(11) NOT NULL AUTO_INCREMENT,usernamevarchar(20) NOT NULL,passwordvarchar(20) DEFAULT NULL,telephonevarchar(11) DEFAULT NULL,register_timetimestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,popedomint(11) DEFAULT NULL COMMENT '0：管理员；1：普通用户', PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; INSERT INTOt_userVALUES ('1', 'admin', '12345', '15734345678', '2016-12-02 08:40:35', '0'); INSERT INTOt_userVALUES ('2', '郑晓红', '11111', '13956567889', '2016-12-20 09:51:43', '1'); INSERT INTOt_userVALUES ('3', '温志军', '22222', '13956678907', '2016-12-20 09:52:36', '1'); INSERT INTOt_userVALUES ('4', '涂文艳', '33333', '15890905678', '2016-12-05 09:52:56', '1');
在这里插入图片描述
打开项目springdemo2021
先添加依赖打开_pom.xml
添加

<!--Spring数据库支持-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <!--数据库驱动工具包-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>
        <!--数据库连接池框架-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.24</version>
        </dependency>
        <!--日志框架-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

————记得刷新依赖

创建用户实体类——User

在这里插入图片描述

package net.qyk.spring.lesson06.bean;
import java.util.Date;
public class User {
    private int id;
    private String username;
    private String password;
    private String telephone;
    private Date registerTime;
    private int popedom;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getTelephone() {
        return telephone;
    }

    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }

    public Date getRegisterTime() {
        return registerTime;
    }

    public void setRegisterTime(Date registerTime) {
        this.registerTime = registerTime;
    }

    public int getPopedom() {
        return popedom;
    }

    public void setPopedom(int popedom) {
        this.popedom = popedom;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", telephone='" + telephone + '\'' +
                ", registerTime=" + registerTime +
                ", popedom=" + popedom +
                '}';
    }
}

创建用户数据访问接口 - UserDao
在这里插入图片描述

package net.qyk.spring.lesson06.dao;

import net.qyk.spring.lesson06.bean.User;

import java.util.List;


public interface UserDao {
    int insert(User user);
    int deleteById(int id);
    int update(User user);
    User findById(int id);
    List<User> findAll();
    User login(String username, String password);
}

创建用户数据访问接口实现类 - UserDaoImpl
在这里插入图片描述

package net.qyk.spring.lesson06.dao.impl;

import net.qyk.spring.lesson06.bean.User;
import net.qyk.spring.lesson06.dao.UserDao;
import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;


@Repository("userDao")
public class UserDaoImpl implements UserDao {
    @Resource
    private JdbcTemplate jdbcTemplate;

    public int insert(User user) {
        String strSQL = "INSERT INTO t_user (username, password, telephone, register_time, popedom)" +
                " values (?, ?, ?, ?, ?)";
        return jdbcTemplate.update(strSQL, user.getUsername(), user.getPassword(), user.getTelephone(),
                user.getRegisterTime(), user.getPopedom());
    }

    public int deleteById(int id) {
        String strSQL = "DELETE FROM t_user WHERE id = ?";
        return jdbcTemplate.update(strSQL, id);
    }

    public int update(User user) {
        String strSQL = "UPDATE t_user set username = ?, password = ?, telephone = ?, " +
                "register_time = ?, popedom = ? WHERE id = ?";
        return jdbcTemplate.update(strSQL, user.getUsername(), user.getPassword(), user.getTelephone(),
                user.getRegisterTime(), user.getPopedom(), user.getId());
    }

    public User findById(int id) {
        String strSQL = "SELECT * FROM t_user WHERE id = ?";
        return jdbcTemplate.queryForObject(strSQL, new RowMapper<User>() {
                    public User mapRow(ResultSet rs, int row) throws SQLException {
                        User user = new User();
                        user.setId(rs.getInt("id"));
                        user.setUsername(rs.getString("username"));
                        user.setPassword(rs.getString("password"));
                        user.setTelephone(rs.getString("telephone"));
                        user.setRegisterTime(rs.getTimestamp("register_time"));
                        user.setPopedom(rs.getInt("popedom"));
                        return user;
                    }
                }, id);
    }

    public List<User> findAll() {
        String strSQL = "SELECT * FROM t_user";
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(strSQL);
        List<User> users = new ArrayList<User>();
        for (Map<String, Object> row : rows) {
            User user = new User();
            user.setId((Integer) row.get("id"));
            user.setUsername((String) row.get("username"));
            user.setPassword((String) row.get("password"));
            user.setTelephone((String) row.get("telephone"));
            user.setRegisterTime((Date) row.get("register_time"));
            user.setPopedom((Integer) row.get("popedom"));
            users.add(user);
        }
        return users;
    }

    public User login(String username, String password){
        String strSQL = "SELECT * FROM t_user WHERE username = ? AND password = ?";
        return jdbcTemplate.queryForObject(strSQL,
                new RowMapper<User>() {
                    public User mapRow(ResultSet rs, int row) throws SQLException {
                        User user = new User();
                        user.setId(rs.getInt("id"));
                        user.setUsername(rs.getString("username"));
                        user.setPassword(rs.getString("password"));
                        user.setTelephone(rs.getString("telephone"));
                        user.setRegisterTime(rs.getTimestamp("register_time"));
                        user.setPopedom(rs.getInt("popedom"));
                        return user;
                    }
                }, username, password);
    }
}

创建用户服务类 - UserService
在这里插入图片描述

package net.qyk.spring.lesson06.service;

import net.qyk.spring.lesson06.bean.User;
import net.qyk.spring.lesson06.dao.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;



@Service("userService")
public class UserService {
    @Autowired
    private UserDao userDao;

    public int addUser(User user) {
        return userDao.insert(user);
    }

    public int deleteUserById(int id) {
        return userDao.deleteById(id);
    }

    public int updateUser(User user) {
        return userDao.update(user);
    }

    public User findUserById(int id) {
        return userDao.findById(id);
    }

    public List<User> findAllUsers() {
        return userDao.findAll();
    }

    public User login(String username, String password) {
        return userDao.login(username, password);
    }
}

创建数据库配置属性文件 - jdbc.properties
在这里插入图片描述
创建配置文件在这里插入图片描述

<?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: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
       https://www.springframework.org/schema/context/spring-context.xsd">
<!--组件扫描-->
    <context:component-scan base-package="net.qyk.spring.lesson06"/>
<!--声明属性占位符-->
    <context:property-placeholder location="jdbc.properties"/>
<!--定义数据源Bean-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>


    </bean>
</beans>

创建测试类——TestUserService
在这里插入图片描述

package net.qyk.spring.lesson06.service;

import net.qyk.spring.lesson06.bean.User;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestUserService {
    private UserService userService;
    private ClassPathXmlApplicationContext context;
    @Before
    public void init() {
        context=new ClassPathXmlApplicationContext("jdbc/spring_config.xml");
        userService =(UserService) context.getBean("userService");
    }
    @Test
    public void testFindUserById(){
        int id=1;
        try{
            User user = userService.findUserById(id);
            System.out.println(user);
        }catch (Exception e){
            System.out.println("编号为["+ id +"]的用户未找到");
        }
    }
    @After
    public void destroy() {
        context.close();
    }
}

在这里插入图片描述
当用户没有找到是将会抛出异常所以要添加异常处理在这里插入图片描述
运行测试在这里插入图片描述
运行testAddUser()方法
在这里插入图片描述
运行testAddUser()方法
在这里插入图片描述
在这里插入图片描述

运行测试插入 更新 登录方法

@Test
    public  void testUpdateUser(){
        int id=5;
        User user = userService.findUserById(id);
        System.out.println("更新前："+user);
        user.setUsername("2号");
        user.setPassword("111213");
        user.setTelephone("14783278227");
        user.setRegisterTime(new Date());
        user.setPopedom(0);
        int count = userService.updateUser(user);
        if (count >0) {
            System.out.println("记录更新成功");
            System.out.println("更新前："+user);
        }else {
            System.out.println("记录插入失败");
        }
    }
    @Test
    public void testDeleteUserById() {
        int id  = 5;
        User user  = userService.findUserById(id);
        System.out.println("待删除记录：" + user);
        int count = userService.deleteUserById(id);
        if (count > 0) {
            System.out.println("删除用户成功！");
        } else {
            System.out.println("删除用户失败！");
        }
    }
    @Test
    public void testLogin() {
        String username = "三号";
        String password = "111111";
        try {
            User user = userService.login(username, password);
            System.out.println("恭喜，"+username+"登录成功！");
        } catch (Exception e) {
            System.out.println("遗憾，"+username+"登录失败！");
        }
    }

在这里插入图片描述

星灵梦境
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值