spring基础jdbcTemplate

新建maven项目

 

 导入依赖

<dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>4.3.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
            <version>4.3.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.3.6.RELEASE</version>
        </dependency>

User.java

package com.shrimpking.jdbc;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/7/6 12:34
 */
public class User
{
    private Integer id;
    private String username;
    private String password;

    public User()
    {
    }

    public User(String username, String password)
    {
        this.username = username;
        this.password = password;
    }

    public Integer getId()
    {
        return id;
    }

    public void setId(Integer 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;
    }

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

UserDao.java

package com.shrimpking.jdbc;

import java.util.List;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/7/6 12:36
 */
public interface UserDao
{
    public int addUser(User user);

    public int updateUser(User user);

    public int deleteUser(int id);

    public User selectUserById(int id);

    public List<User> selectAllUser();
}

UserDaoImpl.java

package com.shrimpking.jdbc;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.util.List;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/7/6 12:37
 */
public class UserDaoImpl implements UserDao
{
    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate)
    {
        this.jdbcTemplate = jdbcTemplate;
    }

    //增加用户
    @Override
    public int addUser(User user)
    {
        String sql = "insert into aa_user(username,password) value(?,?)";
        Object[] objects = new Object[]{
          user.getUsername(),
          user.getPassword()
        };
        int result = this.jdbcTemplate.update(sql,objects);
        return result;
    }

    //更新用户
    @Override
    public int updateUser(User user)
    {
        String sql = "update aa_user set username=?,password=? where id=?";
        Object[] objects = new Object[]{
          user.getUsername(),
          user.getPassword(),
          user.getId()
        };
        int result = this.jdbcTemplate.update(sql,objects);
        return result;
    }

    //删除用户
    @Override
    public int deleteUser(int id)
    {
        String sql = "delete from aa_user where id = ?";
        int result = this.jdbcTemplate.update(sql,id);
        return result;
    }

    //根据id查询用户
    @Override
    public User selectUserById(int id)
    {
        String sql = "select id,username,password from aa_user where id=?";
        RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
        return this.jdbcTemplate.queryForObject(sql,rowMapper,id);
    }

    //查询所有用户
    @Override
    public List<User> selectAllUser()
    {
        String sql = "select id,username,password from aa_user";
        RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
        return this.jdbcTemplate.query(sql,rowMapper);
    }
}

applicationContext.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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName"    value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/test??useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false&amp;serverTimeZone=UTC"/>
        <property name="username" value="root"/>
        <property name="password" value="mysql123"/>

    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <!-- 注入   -->
    <bean id="userDao" class="com.shrimpking.jdbc.UserDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
</beans>

JdbcTest.java

import com.shrimpking.jdbc.User;
import com.shrimpking.jdbc.UserDao;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/7/6 12:29
 */
public class JdbcTest
{
    @Test
    public void test()
    {
        //创建表
        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        JdbcTemplate jdbcTemplate = context.getBean("jdbcTemplate", JdbcTemplate.class);
        String sql = "create table aa_user(id int auto_increment primary key,username varchar(40),password varchar(40))";
        jdbcTemplate.execute(sql);
    }

    @Test
    public void add()
    {
        //插入数据
        ApplicationContext context
                = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = context.getBean("userDao", UserDao.class);
        User user = new User();
        user.setUsername("shrimpking");
        user.setPassword("123456");
        int result = userDao.addUser(user);
        if(result > 0)
        {
            System.out.println("成功插入" + result + "条数据");

        }
        else
        {
            System.out.println("插入失败");
        }

    }

    @Test
    public void update()
    {
        //修改数据
        ApplicationContext context
                = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = context.getBean("userDao", UserDao.class);
        User user = new User();
        user.setUsername("king");
        user.setPassword("333");
        user.setId(1);
        int result = userDao.updateUser(user);
        if(result > 0)
        {
            System.out.println("成功更新了" + result + "条数据");

        }
        else
        {
            System.out.println("更新失败");
        }
    }

    @Test
    public void delete()
    {
        //删除
        ApplicationContext context
                = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = context.getBean("userDao", UserDao.class);
        int result = userDao.deleteUser(1);
        if(result > 0)
        {
            System.out.println("成功删除了" + result + "条数据");
        }
        else
        {
            System.out.println("删除失败");
        }
    }

    @Test
    public void findById()
    {
        ApplicationContext context
                = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = context.getBean("userDao", UserDao.class);
        User user = userDao.selectUserById(2);
        System.out.println(user);
    }

    @Test
    public void findAll()
    {
        ApplicationContext context =
                new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = context.getBean("userDao", UserDao.class);
        List<User> users = userDao.selectAllUser();
        for (User user : users)
        {
            System.out.println(user);
        }
    }
}