Mybatis入门实战

一 新建项目

SpringBoot提供了项目初始化的功能,只要在页面上点选就可以生成一个初始空白项目
Spring Initializr 地址:https://start.spring.io/
在这里插入图片描述
生成并下载springboot压缩包
在这里插入图片描述
解压后目录结构:
在这里插入图片描述
IDE会自动构建project

二 配置环境

打开pom.xml,增加项目中需要的Maven依赖文件

<dependencies>
		<!-- json -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>${fastjson.version}</version>
		</dependency>
		<!--mybatis数据库操作-->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.1</version>
		</dependency>
		<!-- mybatis分页插件 -->
		<dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper-spring-boot-starter</artifactId>
			<version>1.2.13</version>
		</dependency>
		<!--sqlserver数据库-->
		<dependency>
			<groupId>com.microsoft.sqlserver</groupId>
			<artifactId>mssql-jdbc</artifactId>
		</dependency>
		<!-- 单元测试 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

点击标题栏 File > Project Structure
在这里插入图片描述
在 Project选项点击后出现下拉框,再点击 Add SDK 在弹出的选项中点击JDK
在这里插入图片描述
弹出JDK安装路径的选择对话框,选择已经安装完成的JDK路径后,点击OK 完成JDK环境的配置

在这里插入图片描述
点击IDEA标题栏 File > Settings…选项
在这里插入图片描述
在设置界面中选择Build、Execution、Deployment > Build Tools > Maven,分别设置本地Maven的解压文件路径和勾选Settins.xml和本地仓库的路径,点击Apply,项目会自动下载依赖文件

在这里插入图片描述

三 后端编写

1 controller层 注册api路由和控制器

新建一个controller文件夹,用来放控制器,新建UserController,实现基本的增删改查

@Slf4j //log日志
@RestController //restful规范的控制器
public class UserController {
    @Autowired
    private SbUserService sbUserService;

    @GetMapping("/users")
    @ResponseBody //返回数据为json格式
    Result getUsers(@RequestParam(value = "pageNumber", defaultValue = "1") Integer pageNumber, //url中的参数
                    @RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize){
        log.info("查询User,第{}页,每页{}条", pageNumber, pageSize);
        List<SbUser> sbUsers = sbUserService.selectByPage(pageNumber, pageSize);
        return Result.success(sbUsers);
    }

    @GetMapping("/users/{userId}")
    @ResponseBody
    Result getUser(@PathVariable Integer userId){// 从请求路径中获取参数,对应uri中的同名占位符
        log.info("查询User,id:{}", userId);
        UserInfo userInfo = sbUserService.selectUserInfo(userId);
        return Result.success(userInfo);
    }

    @PostMapping("/users")
    @ResponseBody
    Result addUser(@RequestBody AUUserReq req){// json格式的请求参数( 请求参数的Content-Type 是 application/json)
        log.info("新增User:{}", req);
        Integer userId = sbUserService.addUser(req);
        return Result.success(userId);
    }

    @DeleteMapping("/users/{userId}")
    @ResponseBody
    Result deleteUser(@PathVariable Integer userId){// 从请求路径中获取参数,对应uri中的同名占位符
        log.info("删除User,id:{}", userId);
        return Result.success();
    }
    
    @PutMapping("/users/{userId}")
    @ResponseBody
    Result updateUser(@PathVariable Integer userId, @RequestBody AUUserReq req){
        log.info("更新User,id:{},信息:{}", userId, req);
        return Result.success();
    }
}

2 dto层 参数和返回

请求参数:

@Data
public class AUUserReq {
    private String name;
    private Integer age;
    private String email;
}

定义统一返回结果:

@Data
public class Result<T> {
	public static String SUCCESS_STATUS = "0";
	public static String FAIL_STATUS = "1";

	@JSONField(ordinal = 1) //规定字段的顺序
	private String code;
	@JSONField(ordinal = 2)
	private String message;
	@JSONField(ordinal = 3)
	private T result;

	private Result(String code, String message, T result) {
		this.code = code;
		this.message = message;
		this.result = result;
	}

	public static  Result success() {
		return new Result<>(SUCCESS_STATUS, "成功", null);
	}
	public static <T> Result<T> success(T result) {
		return new Result<>(SUCCESS_STATUS, "成功", result);
	}

	@Deprecated
	public static Result fail() {
		return new Result<>(FAIL_STATUS, "失败", "");
	}

	@Deprecated
	public static Result fail(String code, String message) {
		return new Result<>(code, message, null);
	}
}

3 entity层 表结构

建立两个表,sb_user和user_detail,用user_id关联

sb_user主表结构:

@Data
public class SbUser implements Serializable {
    private Integer id;
    private String name;
    private Integer age;
    private String email;
    private static final long serialVersionUID = 1L;
}

user_detail副表结构:

@Data
public class UserDetail implements Serializable {
    private Integer id;
    private Integer userId;
    private String avatar;
    private String hobby;
    private String city;
    private String job;
    private String company;
    private LocalDateTime createdTime;
    private static final long serialVersionUID = 1L;
}

查询字段:

@Data
public class UserInfo extends SbUser {
    private Integer id;
    private String name;
    private String city;
    private String job;
}

4 mapper层 数据库映射

定义sb_user映射:

public interface SbUserMapper {
    UserInfo selectUserInfo(@Param("id") Integer id);
    List<SbUser> selectAll();
    int deleteByPrimaryKey(Integer id);
    int insert(SbUser record);
    int insertSelective(SbUser record);
    SbUser selectByPrimaryKey(Integer id);
    int updateByPrimaryKeySelective(SbUser record);
    int updateByPrimaryKey(SbUser record);
}

定义user_detail映射:

public interface UserDetailMapper {
    int deleteByPrimaryKey(Integer id);
    int insert(UserDetail record);
    int insertSelective(UserDetail record);
    UserDetail selectByPrimaryKey(Integer id);
    int updateByPrimaryKeySelective(UserDetail record);
    int updateByPrimaryKey(UserDetail record);
}

5 service层 逻辑处理

@Service
public class SbUserService{

    @Resource
    private SbUserMapper sbUserMapper;
    @Autowired
    private UserDetailService userDetailService;

    public UserInfo selectUserInfo(Integer id){
        return sbUserMapper.selectUserInfo(id);
    }

    @Transactional(rollbackFor = Exception.class) // 为方法添加事务,保证两个表同步
    public Integer addUser(AUUserReq req) {
        // 新增用户基本信息
        SbUser sbUser = new SbUser();
        sbUser.setName(req.getName());
        sbUser.setAge(req.getAge());
        sbUser.setEmail(req.getEmail());
        this.insertSelective(sbUser); // 插入成功id会自动回填到sbUser中
        // 新增用户详细信息
        UserDetail userDetail = new UserDetail();
        userDetail.setUserId(sbUser.getId());
        userDetail.setJob(req.getJob());
        userDetail.setHobby(req.getHobby());
        userDetail.setCompany(req.getCompany());
        userDetailService.insertSelective(userDetail);
        return sbUser.getId();
    }
    public List<SbUser> selectByPage(Integer pageNumber, Integer pageSize){
        PageHelper.startPage(pageNumber, pageSize);
        return sbUserMapper.selectAll();
    }
    public int deleteByPrimaryKey(Integer id) {
        return sbUserMapper.deleteByPrimaryKey(id);
    }
    public int insert(SbUser record) {
        return sbUserMapper.insert(record);
    } 
    public int insertSelective(SbUser record) {
        return sbUserMapper.insertSelective(record);
    }
    public SbUser selectByPrimaryKey(Integer id) {
        return sbUserMapper.selectByPrimaryKey(id);
    }
    public int updateByPrimaryKeySelective(SbUser record) {
        return sbUserMapper.updateByPrimaryKeySelective(record);
    }
    public int updateByPrimaryKey(SbUser record) {
        return sbUserMapper.updateByPrimaryKey(record);
    }
}
@Service
public class UserDetailService{

    @Resource
    private UserDetailMapper userDetailMapper;

    public int deleteByPrimaryKey(Integer id) {
        return userDetailMapper.deleteByPrimaryKey(id);
    }
    public int insert(UserDetail record) {
        return userDetailMapper.insert(record);
    }
    public int insertSelective(UserDetail record) {
        return userDetailMapper.insertSelective(record);
    }  
    public UserDetail selectByPrimaryKey(Integer id) {
        return userDetailMapper.selectByPrimaryKey(id);
    }
    public int updateByPrimaryKeySelective(UserDetail record) {
        return userDetailMapper.updateByPrimaryKeySelective(record);
    }
    public int updateByPrimaryKey(UserDetail record) {
        return userDetailMapper.updateByPrimaryKey(record);
    }
}

6 resource mapper 放置SQL

定义主表查询字段和返回格式:

<resultMap id="BaseResultMap" type="com.jch.entity.SbUser">
  <id column="id" jdbcType="INTEGER" property="id" />
  <result column="name" jdbcType="VARCHAR" property="name" />
  <result column="age" jdbcType="INTEGER" property="age" />
  <result column="email" jdbcType="VARCHAR" property="email" />
</resultMap>
<sql id="Base_Column_List">
  id, [name], age, email
</sql>

构造主表SQL语句:

<select id="selectUserInfo" parameterType="java.lang.Integer" resultType="com.jch.entity.UserInfo">
  select su.id, su.name, ud.hobby, ud.company, ud.job, ud.city
  from sb_user as su
  left join user_detail ud on su.id = ud.user_id
  where su.id = #{id}
</select>
<select id="selectAll" resultMap="BaseResultMap">
  select
  <include refid="Base_Column_List" />
  from sb_user
</select>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
  select 
  <include refid="Base_Column_List" />
  from sb_user
  where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
  delete from sb_user
  where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.jch.entity.SbUser" useGeneratedKeys="true">
  insert into sb_user ([name], age, email
    )
  values (#{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}, #{email,jdbcType=VARCHAR}
    )
</insert>
<insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.jch.entity.SbUser" useGeneratedKeys="true">
  insert into sb_user
  <trim prefix="(" suffix=")" suffixOverrides=",">
    <if test="name != null and name != ''">
      [name],
    </if>
    <if test="age != null">
      age,
    </if>
    <if test="email != null and email != ''">
      email,
    </if>
  </trim>
  <trim prefix="values (" suffix=")" suffixOverrides=",">
    <if test="name != null and name != ''">
      #{name,jdbcType=VARCHAR},
    </if>
    <if test="age != null">
      #{age,jdbcType=INTEGER},
    </if>
    <if test="email != null and email != ''">
      #{email,jdbcType=VARCHAR},
    </if>
  </trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.jch.entity.SbUser">
  update sb_user
  <set>
    <if test="name != null and name != ''">
      [name] = #{name,jdbcType=VARCHAR},
    </if>
    <if test="age != null">
      age = #{age,jdbcType=INTEGER},
    </if>
    <if test="email != null and email != ''">
      email = #{email,jdbcType=VARCHAR},
    </if>
  </set>
  where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.jch.entity.SbUser">
  update sb_user
  set [name] = #{name,jdbcType=VARCHAR},
    age = #{age,jdbcType=INTEGER},
    email = #{email,jdbcType=VARCHAR}
  where id = #{id,jdbcType=INTEGER}
</update>

定义副表查询字段和返回格式:

<resultMap id="BaseResultMap" type="com.jch.entity.UserDetail">
  <id column="id" jdbcType="INTEGER" property="id" />
  <result column="user_id" jdbcType="INTEGER" property="userId" />
  <result column="avatar" jdbcType="VARCHAR" property="avatar" />
  <result column="hobby" jdbcType="VARCHAR" property="hobby" />
  <result column="city" jdbcType="VARCHAR" property="city" />
  <result column="job" jdbcType="VARCHAR" property="job" />
  <result column="company" jdbcType="VARCHAR" property="company" />
  <result column="created_time" jdbcType="TIMESTAMP" property="createdTime" />
</resultMap>
<sql id="Base_Column_List">
  id, user_id, avatar, hobby, city, job, company, created_time
</sql>

构造副表SQL语句:

<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
  select 
  <include refid="Base_Column_List" />
  from user_detail
  where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
  delete from user_detail
  where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.jch.entity.UserDetail" useGeneratedKeys="true">
  <!--@mbg.generated-->
  insert into user_detail (user_id, avatar, hobby, 
    city, job, company, 
    created_time)
  values (#{userId,jdbcType=INTEGER}, #{avatar,jdbcType=VARCHAR}, #{hobby,jdbcType=VARCHAR}, 
    #{city,jdbcType=VARCHAR}, #{job,jdbcType=VARCHAR}, #{company,jdbcType=VARCHAR}, 
    #{createdTime,jdbcType=TIMESTAMP})
</insert>
<insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.jch.entity.UserDetail" useGeneratedKeys="true">
  insert into user_detail
  <trim prefix="(" suffix=")" suffixOverrides=",">
    <if test="userId != null">
      user_id,
    </if>
    <if test="avatar != null and avatar != ''">
      avatar,
    </if>
    <if test="hobby != null and hobby != ''">
      hobby,
    </if>
    <if test="city != null and city != ''">
      city,
    </if>
    <if test="job != null and job != ''">
      job,
    </if>
    <if test="company != null and company != ''">
      company,
    </if>
    <if test="createdTime != null">
      created_time,
    </if>
  </trim>
  <trim prefix="values (" suffix=")" suffixOverrides=",">
    <if test="userId != null">
      #{userId,jdbcType=INTEGER},
    </if>
    <if test="avatar != null and avatar != ''">
      #{avatar,jdbcType=VARCHAR},
    </if>
    <if test="hobby != null and hobby != ''">
      #{hobby,jdbcType=VARCHAR},
    </if>
    <if test="city != null and city != ''">
      #{city,jdbcType=VARCHAR},
    </if>
    <if test="job != null and job != ''">
      #{job,jdbcType=VARCHAR},
    </if>
    <if test="company != null and company != ''">
      #{company,jdbcType=VARCHAR},
    </if>
    <if test="createdTime != null">
      #{createdTime,jdbcType=TIMESTAMP},
    </if>
  </trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.jch.entity.UserDetail">
  update user_detail
  <set>
    <if test="userId != null">
      user_id = #{userId,jdbcType=INTEGER},
    </if>
    <if test="avatar != null and avatar != ''">
      avatar = #{avatar,jdbcType=VARCHAR},
    </if>
    <if test="hobby != null and hobby != ''">
      hobby = #{hobby,jdbcType=VARCHAR},
    </if>
    <if test="city != null and city != ''">
      city = #{city,jdbcType=VARCHAR},
    </if>
    <if test="job != null and job != ''">
      job = #{job,jdbcType=VARCHAR},
    </if>
    <if test="company != null and company != ''">
      company = #{company,jdbcType=VARCHAR},
    </if>
    <if test="createdTime != null">
      created_time = #{createdTime,jdbcType=TIMESTAMP},
    </if>
  </set>
  where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.jch.entity.UserDetail">
  update user_detail
  set user_id = #{userId,jdbcType=INTEGER},
    avatar = #{avatar,jdbcType=VARCHAR},
    hobby = #{hobby,jdbcType=VARCHAR},
    city = #{city,jdbcType=VARCHAR},
    job = #{job,jdbcType=VARCHAR},
    company = #{company,jdbcType=VARCHAR},
    created_time = #{createdTime,jdbcType=TIMESTAMP}
  where id = #{id,jdbcType=INTEGER}
</update>

最终目录结构如下:

在这里插入图片描述

三 调试

application.yml配置文件添加数据库:

## 数据源配置
spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:sqlserver://10.100.51.148:14330;database=test
    username: jch
    password: 123456
    # Hikari 连接池配置
    # 最小空闲连接数量
    hikari:
      minimum-idle: 5
      # 空闲连接存活最大时间,默认600000(10分钟)
      idle-timeout: 180000
      # 连接池最大连接数,默认是10
      maximum-pool-size: 10
      # 此属性控制从池返回的连接的默认自动提交行为,默认值:true
      auto-commit: true
      # 连接池名称
      pool-name: RcProviderHikariCP
      # 此属性控制池中连接的最长生命周期,值0表示无限生命周期,默认1800000即30分钟
      max-lifetime: 300000
      # 数据库连接超时时间,默认30秒,即30000
      connection-timeout: 30000
      # 控制将测试连接的活动性的最长时间。
      validation-timeout: 3000

修改application.yml配置文件添加mybatis配置

#mybatis的相关配置
mybatis:
  configuration:
    #开启自动驼峰命名规则
    map-underscore-to-camel-case: true
    #自动映射数据表字段与对象的属性,对所有的 resultMap 都进行自动映射
    auto-mapping-behavior: full
  #Mapper 所对应的 XML 文件位置
  mapper-locations: classpath:mapper/*.xml

现在已经可以启动了,可以右键SpringbootStartupApplication来启动web服务
在这里插入图片描述
默认启动是8080端口,为了防止冲突可以在application.yml文件首行添加

server.port: 19999

指定启动19999端口,启动成功可见如下日志

在这里插入图片描述
打开postman,用GET方式请求,得到第一页第一条数据
在这里插入图片描述
以上就是Mybatis入门实战的全部内容,相信你看完以后会对Mybatis的使用有了大致了解

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值