Mybatis3注解开发分步详解

目录

Mybatis3注解开发分步详解

1、数据库表sql

2、User实体类 

3、db.properties

4、SqlMapConfig

5、IUserDao接口

6、测试

---

Mybatis3注解开发分步详解

1、数据库表sql

/*
Navicat MySQL Data Transfer

Source Server         : zhulang
Source Server Version : 50727
Source Host           : localhost:3306
Source Database       : mybatis

Target Server Type    : MYSQL
Target Server Version : 50727
File Encoding         : 65001

Date: 2019-10-21 17:38:41
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for account
-- ----------------------------
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `ID` int(11) NOT NULL COMMENT '编号',
  `UID` int(11) DEFAULT NULL COMMENT '用户编号',
  `MONEY` double DEFAULT NULL COMMENT '金额',
  PRIMARY KEY (`ID`),
  KEY `FK_Reference_8` (`UID`),
  CONSTRAINT `FK_Reference_8` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of account
-- ----------------------------
INSERT INTO `account` VALUES ('1', '41', '10000');
INSERT INTO `account` VALUES ('2', '45', '15000');
INSERT INTO `account` VALUES ('3', '41', '20000');

-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `ID` int(11) NOT NULL COMMENT '编号',
  `ROLE_NAME` varchar(30) DEFAULT NULL COMMENT '角色名称',
  `ROLE_DESC` varchar(60) DEFAULT NULL COMMENT '角色描述',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES ('1', '院长', '管理整个学院');
INSERT INTO `role` VALUES ('2', '总裁', '管理整个公司');
INSERT INTO `role` VALUES ('3', '校长', '管理整个学校');

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名称',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `address` varchar(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('41', '老王', '2019-10-25 17:47:08', '男', '北京朝阳');
INSERT INTO `user` VALUES ('42', '小二王', '2019-10-25 17:47:08', '女', '湖北武汉');
INSERT INTO `user` VALUES ('45', '百里半', '2019-10-25 17:47:08', '男', '湖北宜昌');
INSERT INTO `user` VALUES ('48', '小马', '2019-10-25 17:47:08', '女', '湖北黄石');

-- ----------------------------
-- Table structure for user_role
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
  `UID` int(11) NOT NULL COMMENT '用户编号',
  `RID` int(11) NOT NULL COMMENT '角色编号',
  PRIMARY KEY (`UID`,`RID`),
  KEY `FK_Reference_10` (`RID`),
  CONSTRAINT `FK_Reference_10` FOREIGN KEY (`RID`) REFERENCES `role` (`ID`),
  CONSTRAINT `FK_Reference_9` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user_role
-- ----------------------------
INSERT INTO `user_role` VALUES ('41', '1');
INSERT INTO `user_role` VALUES ('45', '1');
INSERT INTO `user_role` VALUES ('41', '2');

2、User实体类 

package com.bailiban.model;

import java.util.Date;

public class User {
    private Integer id;

    private String username;

    private Date birthday;

    private String sex;

    private String address;

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username == null ? null : username.trim();
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.lang.Object#toString()
     */
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", birthday=" + birthday + ", sex=" + sex + ", address="
                + address + "]";
    }

    public void setSex(String sex) {
        this.sex = sex == null ? null : sex.trim();
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address == null ? null : address.trim();
    }
}

3、db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8&useUnicode=true
jdbc.username=root
jdbc.password=123456

4、SqlMapConfig

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
 <configuration>
 <!-- 注意：mybatis的配置文件是有顺序的 -->
 <!-- 读取数据源配置文件db.properties 两种方式任选一，推荐方式二-->
 <!--方式一：绝对路径使用url  -->
<!--  <properties url="file:\D:\mycode\mybatis02\src\main\resources\db.properties"/> -->
 <!--方式二：相对路径是用resource  -->
 <properties resource="db.properties"/>
 <!--可以配置多个environment，例如开发环境、测试环境......default通过id指定默认数据库环境  -->
  <typeAliases>
 <!-- 避免返回值类型写全限定名，可以采取取别名方式，推荐以下扫描包方式，别名为类名小写 -->
 <package name="com.bailiban.model"/>
 </typeAliases>
 <environments default="mysql">
 <environment id="mysql">
 <!-- 开启事务 -->
 <transactionManager type="JDBC"></transactionManager>
 <!--数据源类型常用pooled，带有连接池功能  -->
 <dataSource type="pooled">
 <property name="driver" value="${jdbc.driver}"/>
 <property name="url" value="${jdbc.url}"/>
 <property name="username" value="${jdbc.username}"/>
 <property name="password" value="${jdbc.password}"/>
 </dataSource>
 </environment>
 </environments>
 <mappers>
 <!-- 映射器，推荐下面这种扫描包方式，xml和注解开发均实用 -->
 <!--注意：此处若已配置映射器，在代码中就无须再绑定接口configuration.addMapper(UserDao.class), 只能选其一，推荐此方式写在配置文件 -->
 <package name="com.bailiban.dao"/>
 </mappers>
 </configuration>

5、IUserDao接口

/**
 * Copyright (C) 2019 Baidu, Inc. All Rights Reserved.
 */
package com.bailiban.dao;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.bailiban.model.User;

/**
 * @author zhu
 * @version 创建时间：2019年10月31日 下午5:56:01
 */
public interface IUserDao {
    // 增，新增数据后，返回主键id，使用 @SelectKey注解，mysql是插入后更新id，因此before=false，
    @Insert("insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address})")
    /*
     * @SelectKey(statement = { "select last_insert_id()" }, before = false, keyProperty = "id", keyColumn = "id",
     * resultType = Integer.class)
     */
    // 返回主键id值也可以使用以下注解，设置useGeneratedKeys = true，两种方式均需要数据库设置主键为自动增长
    @Options(keyProperty = "id", keyColumn = "id", useGeneratedKeys = true)
    public int insert(User user);

    // 删
    @Delete("delete from user where id=#{id} ")
    public int deleteByPrimaryKey(int id);

    // 改,注意，若新set的值为空，则会覆盖原值为null，若原值有非空约束，则会报错，可以使用@UpdateProvider注解，但不推荐，推荐使用xml方式
    @Update("update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}")
    public int update(User user);

    // 查，如果数据库表对应的列名和实体类属性名一致，可以不写Results，否则返回结果服务映射到属性
    @Select("select * from user where id=#{id} ")
    @Results(id = "resultMap", value = { @Result(column = "username", property = "username") })
    public User selectByPrimaryKey(int id);

    // 模糊查询，注意不是like #{name}
    @Select("select * from user where username like concat ('%',#{name},'%')")
    public List<User> selectLikeName(String name);

    // 查询所有
    @Select("select * from user")
    public List<User> selectAll();
}

6、测试

/**
 * Copyright (C) 2019 Baidu, Inc. All Rights Reserved.
 */
package com.bailiban.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.bailiban.dao.IUserDao;
import com.bailiban.model.User;

/**
 * @author zhu
 * @version 创建时间：2019年11月1日 上午10:02:33
 */
public class MybatisAnnotation {
    SqlSessionFactoryBuilder builder;
    InputStream inputStream;
    SqlSessionFactory factory;
    SqlSession session;
    IUserDao iUserDao;

    @Before
    public void init() throws IOException {
        // 生成builder
        builder = new SqlSessionFactoryBuilder();
        // 生成工厂,需要读取配置文件
        inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        factory = builder.build(inputStream);
        /* 若SqlMapConfig中没有配置映射器，则需要使用以下代码注册绑定接口，二者只能选其一 */
        // Configuration configuration = factory.getConfiguration();
        // configuration.addMapper(IUserDao.class);
        session = factory.openSession();
        iUserDao = session.getMapper(IUserDao.class);
    }

    @After
    public void destory() {
        try {
            if (inputStream != null) {

                inputStream.close();
            }
            if (session != null) {
                session.close();
            }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    @Test
    public void insert() {
        User user = new User();
        user.setAddress("江西南昌");
        user.setBirthday(new Date(2019, 12, 23));
        user.setSex("女");
        user.setUsername("安安");
        int insert = iUserDao.insert(user);
        /* 需要手动提交事务 */
        session.commit();
        System.out.println(user.toString());
    }

    @Test
    public void deleteByPrimary() {
        int id = 56;
        int i = iUserDao.deleteByPrimaryKey(id);
        session.commit();
        System.out.println(i);
    }

    @Test
    public void update() {
        User user = new User();
        user.setAddress("湖北武汉洪山区");
        user.setBirthday(new Date(2019, 11, 01, 10, 10));
        user.setSex("男");
        user.setUsername("大大");
        user.setId(42);
        int update = iUserDao.update(user);
        session.commit();
        System.out.println(update);
    }

    @Test
    public void selectByPrimaryKey() {
        int id = 41;
        User user = iUserDao.selectByPrimaryKey(id);
        System.out.println(user);
    }

    @Test
    public void selectLikeByName() {
        List<User> list = iUserDao.selectLikeName("大");
        System.out.println(list.toString());
    }

    @Test
    public void selectAll() {
        List<User> list = iUserDao.selectAll();
        for (User user : list) {
            System.out.println(user);
        }
    }
}