Mybatis_CRUD笔记

Springboot下Mybatis的操作

整合mybatis和Druid数据源

导入相关依赖

<!-- 连接数据库 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- 导入druid数据源-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.10</version>
</dependency>
<!-- 导入mybatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>

application.yml

# 连接数据库
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/restful_crud?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
    username: root
    password: 123456
    type: com.alibaba.druid.pool.DruidDataSource
    #   数据源其他配置
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

# 整合mybatis
mybatis:
  # 扫描mapper文件
  mapper-locations: classpath:mapper/*.xml
  # 定义实体类所在的包
  type-aliases-package: com.ff.crud.entity
  configuration:
    # 驼峰映射
    map-underscore-to-camel-case: true

使用Druid的监控功能

@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid() {
        return new DruidDataSource();
    }

    //配置Druid的监控
    //1、配置一个管理后台的Servlet
    @Bean
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String, String> initParams = new HashMap<>();

        initParams.put("loginUsername", "admin");
        initParams.put("loginPassword", "123456");
        initParams.put("allow", "");//默认就是允许所有访问
//        initParams.put("deny", "xxx");

        bean.setInitParameters(initParams);
        return bean;
    }

    //2、配置一个web监控的filter
    @Bean
    public FilterRegistrationBean webStatFilter() {
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        Map<String, String> initParams = new HashMap<>();
        initParams.put("exclusions", "*.js,*.css,/druid/*");

        bean.setInitParameters(initParams);

        bean.setUrlPatterns(Arrays.asList("/*"));

        return bean;
    }
}

主启动类

@SpringBootApplication
@MapperScan("com.ff.crud.mapper")//扫描mapper接口所在的包
public class SpringbootRestfulCrudApplication {
    public static void main(String[] args) {
        SpringApplication.run(SpringbootRestfulCrudApplication.class, args);
    }
}

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {

    private int id;
    private String username;
    private String password;
    private Boolean gender;
    private Date registTime;
}

查询操作

以下操作#{xxx} 如果涉及到实体类，那么xxx就是对应实体类的属性。例：User.xxx

mapper.java

User findById(Integer id);

User findByIdAndName1(Integer id, String username);

//推荐
User findByIdAndName2(@Param("id") Integer id, @Param("username") String username);

/* Map也可以 */
User findByIdAndName3(User user);

//模糊查询
List<User> findByName(@Param("keyword") String keyword);

xml映射文件

注意：namespace=“com.ff.crud.mapper.UserMapper” 不是包

<select id="findById" resultType="User">
    select *
    from t_user
    where id = #{arg0}
    <!-- arg0 arg1 ... -->
</select>

<select id="findByIdAndName1" resultType="User">
    select *
    from t_user
    where id = #{param1} and username = #{param2}
    <!-- param1 param2 ... -->
</select>

<select id="findByIdAndName2" resultType="User">
    select *
    from t_user
    where id = #{id} and username = #{username}
</select>

<select id="findByIdAndName3" resultType="User">
    select *
    from t_user
    where id = #{id} and username = #{username}
</select>

<select id="findByName" resultType="User">
    select *
    from t_user
    where username like concat('%',#{keyword},'%')
</select>

Test

@Autowired
private UserMapper userMapper;

@Test
public void findById() {
    User user = userMapper.findById(1);
    System.out.println(user);
}

public void findByIdAndName1() {
    User user = userMapper.findByIdAndName1(2, "fanfan2");
    System.out.println(user);
}

public void findByIdAndName2() {
    User user = userMapper.findByIdAndName2(2, "fanfan2");
    System.out.println(user);
}

@Test
public void findByIdAndName3() {
    User user = new User();
    user.setId(2);
    user.setUsername("fanfan2");
    user = userMapper.findByIdAndName3(user);
    System.out.println(user);
}

@Test
public void findByName() {
    List<User> users = userMapper.findByName("nfa");
    System.out.println(users);
}

增、删、改（SpringBoot已经配置好了事务）

mapper.java

Interger返回值为影响了多少条数据

Integer deleteUser(@Param("id") Integer id);

Integer updateUser(User user);

Integer insertUser(User user);

xml映射文件

<delete id="deleteUser" parameterType="int">
    delete from t_user
    where id = #{id}
</delete>

<update id="updateUser" parameterType="User">
    update t_user
    set username=#{username},password=#{password},gender=#{gender},regist_time=#{registTime}
    where id = #{id}
</update>

<!-- 以下为 insert操作 -->
<insert id="insertUser" parameterType="User">
    insert into t_user(username,password,gender,regist_time)
    values(#{username},#{password},#{gender},#{registTime})
</insert>

<insert id="insertUser" parameterType="User">
    insert into t_user
    values(NULL,#{username},#{password},#{gender},#{registTime})
</insert>

<!-- 指定NULL -->
<insert id="insertUser" parameterType="User">
    insert into t_user
    values(NULL,#{username},#{password},#{gender},NULL)
</insert>

指定null可能出现的问题，id是因为自增，regist_time数据库设置了NOT NULL

Test

@Test
public void deleteUser() {
    userMapper.deleteUser(1);
    int i = 10 / 0;
}

@Test
public void updateUser() {
    User user = new User(2, "帆帆", "888888", true, new Date());
    userMapper.updateUser(user);
}

@Test
public void insertUser() {
    User user = new User(null, "fanfan6", "66666", true, null);
    Integer integer = userMapper.insertUser(user);
    System.out.println("新增了" + integer + "条数据");
}

主键回填

主键自增

这个处理方式比较有针对性（主键自增）

非主键自增

//Student.java
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    private String id;
    private String name;
    private Boolean gender;
}

//StudentMapper.java
public interface StudentMapper {

    Integer insertStu(Student student);
}

//StudentMapper.xml 截图

此时就不会报错了，刚好长度位32位

分页查询

导入相关依赖

<!-- 分页插件 -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.12</version>
</dependency>

例：基本用法

@Autowired
UserMapper userMapper;

@Test
public void test1(){
    //执行分页
    PageHelper.startPage(2,5);// 2==>pageNum	5==>pageSize 
    //执行语句
    List<User> list = userMapper.findUser2(null);
    //封装到PageInfo对象
    PageInfo<User> pageInfo = new PageInfo<>(list);
    //输出
    List<User> users = pageInfo.getList();
    for (User user : users) {
        System.out.println(user);
    }
}

pageInfo下有相关的属性