Spring 整合JdbcTemplate

目录

1 概念

2 相关方法

3 案例

添加依赖

1 数据库创建表

2 编写实体类

3  创建db.properdies文件（连接数据库文件）

4 编写spring.xml配置文件

5 编写dao层

6 编写service层

7 编写测试

---

1 概念

这玩意基本不会使用，了解下即可

Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作

2 相关方法

增加，修改，删除一般用update

查询用query

3 案例

添加依赖

<?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.star</groupId>
    <artifactId>spring_01_xmlbean</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.3.9</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>


        <!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.3.9</version>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.19</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>


        <!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.9.7</version>
        </dependency>




    </dependencies>

    <build>
        <resources>
            <resource>
                <!--   描述存放资源的目录，该路径相对POM路径-->
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>

        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.22.0</version>
                <dependencies>
                    <dependency>
                        <groupId>org.apache.maven.surefire</groupId>
                        <artifactId>surefire-junit4</artifactId>
                        <version>2.22.0</version>
                    </dependency>
                </dependencies>
                <configuration>
                    <includes>
                        <include>**/*.java</include>
                    </includes>
                </configuration>
            </plugin>
        </plugins>
    </build>


</project>

1 数据库创建表

CREATE TABLE `users` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL,
  `userpwd` varchar(30) NOT NULL,
  PRIMARY KEY (`userid`),
  UNIQUE KEY `username_uk` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

2 编写实体类

package com.star.pojo;

public class Users {

    private Integer userid;
    private String username;

    private String userpwd;

    public Users() {
    }

    public Users(Integer userid, String username, String userpwd) {
        this.userid = userid;
        this.username = username;
        this.userpwd = userpwd;
    }

    public Integer getUserid() {
        return userid;
    }

    public void setUserid(Integer userid) {
        this.userid = userid;
    }

    public String getUsername() {
        return username;
    }

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

    public String getUserpwd() {
        return userpwd;
    }

    public void setUserpwd(String userpwd) {
        this.userpwd = userpwd;
    }

    @Override
    public String toString() {
        return "Users{" +
                "userid=" + userid +
                ", username='" + username + '\'' +
                ", userpwd='" + userpwd + '\'' +
                '}';
    }
}

3  创建db.properdies文件（连接数据库文件）

prop.driverClass=com.mysql.jdbc.Driver
prop.url=jdbc:mysql://locahost:3306/user
prop.userName=root
prop.password=zjx666888

4 编写spring.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:util="http://www.springframework.org/schema/util"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"

       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
">


        <!--开启注解扫描
        1 如果扫描多个包，多个包使用逗号隔开
 2 扫描包上层目录-->
    <context:component-scan base-package="com.star"/>
    <!--开启aop注解扫描-->
    <aop:aspectj-autoproxy/>

    <!--配置数据源-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/ssm"/>
        <property name="username" value="root"/>
        <property name="password" value="zjx666888"/>
    </bean>

    <!--配置jdbcTemplate-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>


</beans>

5 编写dao层

//编写dao层接口
package com.star.dao;

import com.star.pojo.Users;

import java.util.List;

public interface UsersDao {

    Users selectUserById(Integer id);
    int addUsers(Users users);
    int updateUsers(Users users);
    int dropUserById(Integer id);
    List<Users> selectUserAll();
    void batchAdd( List<Object[]> batchArgs);
    void batchUpdate( List<Object[]> batchArgs);
    void batchDrop( List<Object[]> batchArgs);
}


//编写dao实现类
package com.star.dao.impl;

import com.star.dao.UsersDao;
import com.star.pojo.Users;
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.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Map;

@Repository
public class UsersDaoImpl implements UsersDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 根据id查询用户
     * @param id
     * @return
     */
    @Override
    public Users selectUserById(Integer id) {
        String sql = "select *from users where userid = ?";

        Users users = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Users.class), id);
        return users;
    }

    /**
     * 添加用户
     * @param users
     * @return
     */
    @Override
    public int addUsers(Users users) {
        String sql = "insert into users values(?,?,?)";
        Object[] obj = {null, users.getUsername(), users.getUserpwd()};
        int update = jdbcTemplate.update(sql, obj);
        return update;
    }

    /**
     * 更新用户
     * @param users
     * @return
     */
    @Override
    public int updateUsers(Users users) {

        String sql = "update users set username = ?,userpwd = ? where userid = ?";
        Object[] args = {users.getUsername(), users.getUserpwd(), users.getUserid()};

        return jdbcTemplate.update(sql, args);
    }

    /**
     * 删除用户
     * @param id
     * @return
     */
    @Override
    public int dropUserById(Integer id) {

        String sql = "delete from users where userid = ?";
        return jdbcTemplate.update(sql, id);
    }

    /**
     * 查询所有用户
     * @return
     */
    @Override
    public List<Users> selectUserAll() {
        String sql = "select *from users";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Users.class));

    }

    /**
     * 批量添加用户
     * @param list
     */
    @Override
    public void batchAdd(List<Object[]> list) {
        String sql = "insert into users values(?,?,?)";
        jdbcTemplate.batchUpdate(sql,list);
    }

    /**
     * 批量更新用户
     * @param batchArgs
     */
    @Override
    public void batchUpdate( List<Object[]> batchArgs) {
        String sql = "update users set username = ?,userpwd = ? where userid = ?";
        jdbcTemplate.batchUpdate(sql,batchArgs);
    }

    /**
     * 批量删除用户
     * @param batchArgs
     */
    @Override
    public void batchDrop( List<Object[]> batchArgs) {
        String sql = "delete from users where userid = ? ";
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println(Arrays.toString(ints));
    }
}

6 编写service层

//编写service层接口
package com.star.service;

import com.star.pojo.Users;

public interface UsersService {
    void selectUserById(Integer id);
    void addUsers(Users users);
    void updateUsers(Users users);
    void dropUserById(Integer id);
    void selectUserAll();

    void addUsersBatch();
    void updateUsersBatch();
    void dropUserBatch();
}



//编写service层实现类
package com.star.service.impl;

import com.star.dao.UserDao;
import com.star.dao.UsersDao;
import com.star.pojo.Users;
import com.star.service.UsersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

@Service
public class UsersServiceImpl implements UsersService {
    @Autowired
    private UsersDao usersDao;
    @Override
    public void selectUserById(Integer id) {
        Users users = usersDao.selectUserById(id);
        System.out.println(users);
    }

    @Override
    public void addUsers(Users users) {
        int i = usersDao.addUsers(users);
        System.out.println(i);
    }

    @Override
    public void updateUsers(Users users) {
        usersDao.updateUsers(users);
    }

    @Override
    public void dropUserById(Integer id) {
        usersDao.dropUserById(id);
    }

    @Override
    public void selectUserAll() {
        System.out.println(usersDao.selectUserAll());

    }

    @Override
    public void addUsersBatch() {
        List<Object[]> list = new ArrayList<>();
        Object[] o1 = {null,"111","111"};
        Object[] o2 = {null,"222","222"};
        Object[] o3 = {null,"333","333"};
        list.add(o1);
        list.add(o2);
        list.add(o3);
        usersDao.batchAdd(list);
    }

    @Override
    public void updateUsersBatch() {
        List<Object[]> list = new ArrayList<>();
        Object[] o1 = {"aaa","aaaa",2};
        Object[] o2 = {"aaa","aaaa",2};
        Object[] o3 = {"aaa","aaaa",2};
        list.add(o1);
        list.add(o2);
        list.add(o3);
        usersDao.batchUpdate(list);
    }

    @Override
    public void dropUserBatch() {
        List<Object[]> list = new ArrayList<>();
        Object[] o1 = {2};
        Object[] o2 = {3};
        Object[] o3 = {4};
        list.add(o1);
        list.add(o2);
        list.add(o3);
        usersDao.batchDrop(list);
    }
}

7 编写测试

@Test
    public void testSpringJdbc(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("classpath:spring5jdbc.xml");
        UsersService bean = applicationContext.getBean(UsersService.class);

        //bean.selectUserById(1); //查询
        //bean.addUsers(new Users(null,"lq","zjx111")); //insert

        //bean.updateUsers(new Users(1,"ywjz","ywjz")); //update

        //bean.dropUserById(1); //delete

        //bean.selectUserAll(); selectAll
        //bean.addUsersBatch(); batchAdd
        //bean.updateUsersBatch();
        bean.dropUserBatch();
        
    }