Spring与mybatis结合，单表做增删改查

Spring与mybatis结合，单表做增删改查

---

前言

总体图

前面文章有详细的创建步骤

1.导入相应的依赖

<?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.entor</groupId>
    <artifactId>test-0922</artifactId>
    <version>1.0-SNAPSHOT</version>
 
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
 
    <dependencies>
        <!--spring和mybatis结合-->
        <!--导入mybatis-spring-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>2.0.6</version>
        </dependency>
 
        <!--aop切面-->
 
        <dependency>
            <groupId>aspectj</groupId>
            <artifactId>aspectjrt</artifactId>
            <version>1.5.4</version>
        </dependency>
        
        <!--导入mybatis依赖-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>
 
        <!--导入spring依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>5.3.9</version>
        </dependency>
 
        <!--导入jdbc依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.9</version>
        </dependency>
 
        <!--导入数据库驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
 
        <!--导入阿里druid依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.6</version>
        </dependency>
 
        <!--导入lombok替代set,get等方法-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.16</version>
        </dependency>
 
        <!--导入pagehelper分页管理-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.2.1</version>
        </dependency>
 
    </dependencies>
 
</project>

2.核心配置，在资源类下建一个db.properties

package com.entor.config;
 
import com.alibaba.druid.pool.DruidDataSource;
import com.github.pagehelper.PageInterceptor;
import org.apache.ibatis.logging.stdout.StdOutImpl;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
 
import javax.sql.DataSource;
 
/**
 * 配置核心配置
 */
 
//相当于创建了一个applicationContext.xml配置文件
@Configuration
//@MapperScan,指定扫描的接口包，相当于配置文件的MapperScannerConfigurer类
//<context:component-scan base-package="com.entor"/>
@MapperScan(basePackages = "com.entor.mapper")
//
@ComponentScan(basePackages = "com.entor")
//开启事务管理，相当于配置文件的<tx:annotation-driven transaction-manager="transactionManager"/>
@EnableTransactionManagement
 
@PropertySource(value = "classpath:db.properties")
public class SpringMybatisConfig {
    //加jdbc便于与本机区分
    //读取配置文件中对应的值赋值给变量属性
    @Value("${jdbc.driver}")
    private String driver;
    @Value("${jdbc.url}")
    private String url;
    @Value("${jdbc.username}")
    private String username;
    @Value("${jdbc.password}")
    private String password;
    @Value("${jdbc.mapperLocations}")
    private String mapperLocations;
    @Value("${jdbc.typeAliasesPackage}")
    private String typeAliasesPackage;
    /**
     * 配置数据源对象，将数据库连接数据放置在这
     * @return
     */
    @Bean
    public DataSource dataSource(){
        //阿里的数据池
        DruidDataSource dataSource = new DruidDataSource();
        //将数据封装，便于灵活改动
        dataSource.setDriverClassName(driver);
        dataSource.setUrl(url);
        dataSource.setUsername(password);
        dataSource.setPassword(username);
        //将数据返回
        return dataSource;
    }
 
    /**
     * 配置事务管理对象,将数据源传入
     */
    @Bean
    public TransactionManager transactionManager(DataSource dataSource){
        //待查
        return new DataSourceTransactionManager(dataSource);
    }
 
    /**
     * 配置jdbc模板工具
     * @param dataSource
     * @return
     */
    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }
 
    /**
     * 配置mybatis的重要数据，也将数据源传入
     * @param dataSource
     * @return
     */
    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        //工厂
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        //设置数据源
        bean.setDataSource(dataSource);
        PathMatchingResourcePatternResolver patternResolver = new PathMatchingResourcePatternResolver();
        //加载映射文件
       // bean.setMapperLocations(patternResolver.getResource("mapper/*.xml"));
        bean.setMapperLocations(patternResolver.getResources(mapperLocations));
        //配置别名包
        //bean.setTypeAliasesPackage("com.entor.entity");
        bean.setTypeAliasesPackage(typeAliasesPackage);
        //设置分页
        bean.setPlugins(new PageInterceptor());
 
 
        //配置参数类，封装各种参数,来自ibatis包下
        org.apache.ibatis.session.Configuration config = new org.apache.ibatis.session.Configuration();
        //下划线转驼峰,默认是false
        config.setMapUnderscoreToCamelCase(true);
        //控制台sql日志输出
        config.setLogImpl(StdOutImpl.class);
        //开启二级缓存
        config.setCacheEnabled(true);
        //设置参数封装对象
        bean.setConfiguration(config);
        return bean.getObject();
    }
 
}

3.在资源文件下创建mapper/UserMapper配置文件，写入方法

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace不能为空，可以是任何值用来区分不同的映射对象，不同的映射文件值不能一样-->
<mapper namespace="com.entor.mapper.UserMapper">
 
    <insert id="addMore" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
        insert into user(name,username,password,sex,age,birthday,create_time) values
        <foreach collection="list" item="user" separator=",">
            (#{user.name},#{user.username},#{user.password},#{user.sex},#{user.age},#{user.birthday},now())
        </foreach>
    </insert>
    <!--
    <insert id="add" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
        insert into user(
        <if test="name!=null and name!=''">
            name,
        </if>
        <if test="username!=null and username!=''">
            username,
        </if>
        <if test="password!=null and password!=''">
            password,
        </if>
        <if test="sex!=null">
            sex,
        </if>
        <if test="age!=null">
            age,
        </if>
        <if test="birthday!=null">
            birthday,
        </if>
        create_time
        ) values(
        <if test="name!=null and name!=''">
            #{name},
        </if>
        <if test="username!=null and username!=''">
            #{username},
        </if>
        <if test="password!=null and password!=''">
            #{password},
        </if>
        <if test="sex!=null">
            #{sex},
        </if>
        <if test="age!=null">
            #{age},
        </if>
        <if test="birthday!=null">
            #{birthday},
        </if>
        now()
        )
    </insert>
    <update id="update">
        update user
        &lt;!&ndash;set标签会自动去掉多余的逗号&ndash;&gt;
        <set>
            <if test="name!=null and name!=''">
                name = #{name},
            </if>
            <if test="username!=null and username!=''">
                username = #{username},
            </if>
            <if test="password!=null and password!=''">
                password = #{password},
            </if>
            <if test="sex!=null">
                sex = #{sex},
            </if>
            <if test="age!=null">
                age = #{age},
            </if>
            <if test="birthday!=null">
                birthday = #{birthday},
            </if>
        </set>
        where id = #{id}
    </update>
    <select id="queryByParam" resultType="User">
        select * from user
        <where>
            <if test="name!=null and name!=''">
                and name like concat('%',#{name},'%')
            </if>
            <if test="username!=null and username!=''">
                and username like concat('%',#{username},'%')
            </if>
            <if test="sex!=null">
                and sex = #{sex}
            </if>
        </where>
    </select>
    <delete id="deleteByIds">
        delete from user where id in
        <foreach collection="array" open="(" close=")" separator="," item="id">
            #{id}
        </foreach>
    </delete>
    -->
</mapper>

4.创建实体类

package com.entor.entity;
 
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
 
import java.sql.Timestamp;
import java.util.Date;
 
@Data//set,get,toString方法
@AllArgsConstructor//有参
@NoArgsConstructor//无参
public class User {
 
    private Integer id;
    private String name;
    private String username;
    private String password;
    private Integer sex;
    private Integer age;
    private Date birthday;
    private Timestamp createTime;
}

5.创建mapper写入方法

package com.entor.mapper;
 
import com.entor.entity.User;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
 
import java.util.List;
 
public interface UserMapper {
 
    //不需要返回主键
    @Insert("insert into user(name,username,password,sex,age,birthday,create_time)"+
    "values(#{name},#{username},#{password},#{sex},#{age},#{birthday},now())")
    public void add(User user);
 
    @Delete("delete from user where id = #{id}")
    public void deleteById(int id);
 
    @Delete("delete from user where id in (${ids})")
    public void deleteByIds(String ids);
 
    @Update("update user set name=#{name},username=#{username},password=#{password},sex=#{sex},age=#{age},birthday=#{birthday} where id = #{id}")
    public void update(User user);
 
    @Select("select * from user where id = #{id}")
    public User queryById(int id);
 
    @Select("select * from user")
    public List<User> queryByPage();
 
 
    public void addMore(List<User> users);
}

6.创建service层

package com.entor.service;
 
import com.entor.entity.User;
 
import java.util.List;
 
public interface UserService {
    public void add(User user);
 
    public void addMore(List<User> users);
 
    public void deleteById(int id);
 
    public void deleteByIds(String ids);
 
    public void update(User user);
 
    public User queryById(int id);
 
    public List<User> queryByPage(int pageNum,int pageSize);
}

package com.entor.service.impl;
 
import com.entor.entity.User;
import com.entor.mapper.UserMapper;
import com.entor.service.UserService;
import com.github.pagehelper.PageHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
 
import java.util.List;
//@Service将自动扫描路径下面的包，类似还有@Component、@Repository、@Controller。
@Service
//事务管理，可添加到public方法上，作用是使用jdbc的事务来进行事务控制的
@Transactional
public class UserServiceImpl implements UserService {
 
    //可以用来bean的注入
    @Autowired
    private UserMapper userMapper;
 
    @Override
    public void add(User user) {
        userMapper.add(user);
    }
 
    @Override
    public void addMore(List<User> users) {
        userMapper.addMore(users);
    }
 
    @Override
    public void deleteById(int id) {
        userMapper.deleteById(id);
    }
 
    @Override
    public void deleteByIds(String ids) {
        userMapper.deleteByIds(ids);
    }
 
    @Override
    public void update(User user) {
        userMapper.update(user);
    }
 
    @Override
    public User queryById(int id) {
        return userMapper.queryById(id);
    }
 
    @Override
    public List<User> queryByPage(int pageNum, int pageSize) {
        //使用分页插件查询分页
        PageHelper.startPage(pageNum,pageSize);
        return userMapper.queryByPage();
    }
}

7.写测试类

package com.entor.test;
 
import com.entor.config.SpringMybatisConfig;
import com.entor.entity.User;
import com.entor.service.UserService;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
 
import java.util.Date;
 
public class Test1 {
    public static void main(String[] args) {
 
        //连接
        ApplicationContext context = new AnnotationConfigApplicationContext(SpringMybatisConfig.class);
        UserService userService = context.getBean(UserService.class);
//        System.out.println(userService.queryById(67));
 
 
        //添加多个用户
      /*  List<User> list = new ArrayList<>();
        for (int i=0;i<=10;i++){
            User user = new User();
            user.setName("琳"+i);
            user.setUsername("lin");
            user.setPassword("12343");
            user.setSex(0);
            user.setAge(15);
            user.setBirthday(new Date());
            list.add(user);
        }
        userService.addMore(list);
        */
        //
 
        /*//添加一个用户
        User user = new User();
        user.setName("琳");
        user.setUsername("lin");
        user.setPassword("12343");
        user.setSex(0);
        user.setAge(15);
        user.setBirthday(new Date());
        userService.add(user);*/
 
//        //查询多个用户
//        List<User> list = userService.queryByPage(1, 10);
//        list.forEach(System.out::println);
 
       /* //删除一个多个用户
        userService.deleteByIds("53,54");
        userService.deleteById(55);*/
 
        //修改用户
        User user = new User();
        user.setId(1007);
        user.setName("库洛米");
        user.setUsername("kuromi");
        user.setPassword("66666");
        user.setSex(1);
        user.setAge(16);
        user.setBirthday(new Date());
        userService.update(user);
 
    }
}