JdbcTemplate个人练习

JdbcTemplate

什么 是JdbcTemplate

1、Spring框架对Jdbc进行了封装，可以更好的对数据库进行操作。

bean1.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: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">
 <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
     <property name="url" value="jdbc:mysql:///user_db" />
     <property name="username" value="root" />
     <property name="password" value="126694" />
     <property name="driverClassName" value="com.mysql.jdbc.Driver" />
 </bean>
<!---->
    <context:component-scan base-package="com.akurisu"></context:component-scan>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"></property>
 </bean>
</beans>

User

package com.akurisu.pojo;

/**
 * @author akurisu
 * @create 2021-05-01 10:28
 */
public class User {
    private String id;
    private String name;
    private String password;
    private String address;
    private String phone;

    public User() {
    }

    public String getId() {
        return id;
    }

    public User(String id, String name, String password, String address, String phone) {
        this.id = id;
        this.name = name;
        this.password = password;
        this.address = address;
        this.phone = phone;
    }

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

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

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

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
}

UserDao

package com.akurisu.dao;

import com.akurisu.pojo.User;

import java.util.List;

/**
 * @author akurisu
 * @create 2021-05-01 10:18
 */
public interface UserDao {
    /*
    * 添加用户
    * */
    void add(User user);
    /*
    * 更新用户信息
    * */
    void update(User user);
    /*
    * 按照id删除信息
    * */
    void delete(String id);
    /*
    * 查找表内信息数目
    * */
    int selectCount();
    /*
    * 按照id查找用户名
    * */
    User selectUser(String id);
    /*
    * 查找所有的用户
    * */
    List<User> selectAll();
    /*
    * 批量添加
    * */
    void batchAdd(List<Object[]> batch);
    /*
    * 批量删除
    * */
    void batchDelete(List<Object[]> batch);
    /*
    * 批量修改
    * */
    void batchUpdate(List<Object[]> batch);
}

UserDaoImpl

package com.akurisu.dao;

import com.akurisu.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;

/**
 * @author akurisu
 * @create 2021-05-01 10:18
 */
@Repository
public class UserDaoImpl implements UserDao{
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public void add(User user){
        String sql="insert into user values(?,?,?,?,?)";
        jdbcTemplate.update(sql,user.getId(),user.getName(),user.getPassword(),user.getAddress(),user.getPhone());

    }
    public void delete(String id){
       String sql="delete  from user where id=?";
       jdbcTemplate.update(sql,id);
    }
    public void update(User user){
       String sql="update user set name=?,password=?,address=?,phone=?  where id=?";
       Object[] args={user.getName(),user.getPassword(),user.getAddress(),user.getPhone(),user.getId()};
       jdbcTemplate.update(sql,args);
    }
    public int selectCount(){
        String sql="select count(*) from user";
        return (int)jdbcTemplate.queryForObject(sql,Integer.class);
    }
    public User selectUser(String id){
        String sql="select * from user where id=?";
        return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class),id);
    }
    public List<User> selectAll(){
        String sql="select * from user";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<User>(User.class));
    }
    public void batchAdd(List<Object[]> batch){
        String sql="insert into user values(?,?,?,?,?)";
        jdbcTemplate.batchUpdate(sql,batch);
    }
    public void batchDelete(List<Object[]> batch){
          String sql="delete from user where id=?";
          jdbcTemplate.batchUpdate(sql,batch);
    }
    public void batchUpdate(List<Object[]> batch){
        String sql="update user set name=?,password=?,address=?,phone=? where id=?";
        jdbcTemplate.batchUpdate(sql,batch);
    }
}

UserService

package com.akurisu.service;

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

import java.util.List;

/**
 * @author workholic Yin
 * @create 2021-05-01 10:19
 */
@Service
public class UserService {
    @Autowired
    private UserDao userDao;
    public void addUser(User user){
        userDao.add(user);
    }
    public void deleteUser(String id){
       userDao.delete(id);
    }
    public void update(User user){
     userDao.update(user);
    }
    public int findCount(){
        return userDao.selectCount();
    }
    public User findUser(String id){
        return userDao.selectUser(id);
    }
    public List<User> findAll(){
        return userDao.selectAll();
    }
    public void batchAll(List<Object[]> args){
        userDao.batchAdd(args);
    }
    public void batchUpdate(List<Object[]> args){
        userDao.batchUpdate(args);
    }
    public void batchDelete1(List<Object[]> args){
        userDao.batchDelete(args);
    }
}

jdbcTemplate操作数据库

queryForObject(sql,RowMappe,args[])

第二个参数使用new BeanPropertyRowMapper(User.class)，来接收对象，会自动将接受到的数据赋值给对象。

总结:增删改查用update()，查单个返回值用queryForObject()，查多个返回值用query()

批量添加

jdbcTemplate.batchUpdate(String sql,List<Object[]> batchArgs);