Mybatis连接Mysql进行增删查改

Mybatis简单介绍

MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO（Plain Old Java Objects，普通老式 Java 对象）为数据库中的记录。

利用maven下载jar包

 <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.2.2</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.25</version>
    </dependency>

创建配置文件

先创建资源文件夹 resource，然后分别创建资源文件

创建database.properties文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.146.222:3306/mybatisstu?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
user=root
password=1

创建mybatis-config.xml 文件

<?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>
    <properties resource="database.properties"></properties>

    <typeAliases>
        <package name="test.pojo"/>
    </typeAliases>

    <environments default="default">
        <environment id="default">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${user}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/RoleDao.xml"></mapper>
    </mappers>

</configuration>

创建mapper文件夹及RoleDao.xml文件

<?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">
<mapper namespace="test.dao.RoleDao">
<select id="getAll" resultType="Role">
         SELECT
          `id`,`roleCode`,`roleName`,`createBy`,`createionDate`,
          `modifyBy`,`modifyDate`
           FROM `mbrole`
    </select>
<insert id="addRole">
        insert into `mbrole`(`id`,`roleCode`,`roleName`,`createBy`,`createionDate`)
        values(null,#{roleCode},#{roleName},#{createBy},#{createionDate})
    </insert>

<update id="updateRole">
        update `mbrole` set
        `roleCode`=#{roleCode},
        `roleName`=#{roleName},
        `modifyBy`=#{modifyBy},
        `modifyDate`=#{modifyDate}
        where `id`=#{id}
    </update>

    <delete id="deleteRole">
        delete from `mbrole` where `id`=#{id}
    </delete>

    <select id="getAll2" resultMap="roleMap">
         SELECT
          r.`id`,r.`roleCode`,r.`roleName`,r.`createBy`,r.`createionDate`,
          r.`modifyBy`,r.`modifyDate`,
          u.`id` uid,u.`userCode`,u.`userName`,u.`userPassword`,u.`gender`,
          u.`birthday`,u.`userRole`
           FROM `mbrole` r left  join `mbuser` u on r.`id`=u.`userRole`
    </select>
    <resultMap id="roleMap" type="Role">
        <id property="id" column="id"></id>
        <result property="roleCode" column="roleCode"></result>
        <result property="roleName" column="roleName"></result>
        <result property="createBy" column="createBy"></result>
        <result property="createionDate" column="createionDate"></result>
        <result property="modifyBy" column="modifyBy"></result>
        <result property="modifyDate" column="modifyDate"></result>
        <collection property="users" ofType="User">
            <id property="id" column="uid"></id>
            <result property="userCode" column="userCode"></result>
            <result property="userName" column="userName"></result>
            <result property="userPassword" column="userPassword"></result>
            <result property="gender" column="gender"></result>
            <result property="birthday" column="birthday"></result>
        </collection>
    </resultMap>

</mapper>

全部配置完如图所示：

提交创建好两张表及插入数据

Role 表

CREATE TABLE mbrole(
id int not null auto_increment PRIMARY KEY,
roleCode VARCHAR(32),
roleName VARCHAR(32),
createBy int,
createionDate DATE,
modifyBy int,
modifyDate date
);

INSERT into mbrole(roleCode,roleName,createBy,createionDate) VALUES("r0001","admin",1,now()),("r0002","leader",1,now()),("r0003","worker",1,now())

SELECT * FROM mbrole;

User表

create table mbuser(
id int not null auto_increment PRIMARY KEY,
userCode VARCHAR(32),
userName VARCHAR(32),
userPassword VARCHAR(32),
gender int,
birthday VARCHAR(32),
userRole int
);

INSERT INTO mbuser(userCode,userName,userPassword,gender,birthday,userRole) VALUES("0001","admin","admin",1,"1982-12-12",1),
("0002","leader","leader",1,"1983-2-11",2),
("0003","leader2","leader2",0,"1984-4-15",2),
("0004","work1","work1",1,"1974-6-20",3),
("0005","work2","work2",0,"1987-9-5",3);

SELECT * FROM mbuser;

编写代码

dao接口

public interface RoleDao {
    List<Role> getAll(); //   select * from mbrole;

    Integer addRole(Role role);

    Integer updateRole(Role role);

    Integer deleteRole(Integer roleId);


    List<Role> getAll2();

}

Role 类

public class Role {
    private Integer id;
    private String roleCode;
    private String roleName;
    private Integer createBy;
    private Date createionDate;
    private Integer modifyBy;
    private Date modifyDate;

    //很多user  对应一个 role
     private List<User> users;

    public Integer getId() {
        return id;
    }

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

    public String getRoleCode() {
        return roleCode;
    }

    public void setRoleCode(String roleCode) {
        this.roleCode = roleCode;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public Integer getCreateBy() {
        return createBy;
    }

    public void setCreateBy(Integer createBy) {
        this.createBy = createBy;
    }

    public Date getCreateionDate() {
        return createionDate;
    }

    public void setCreateionDate(Date createionDate) {
        this.createionDate = createionDate;
    }

    public Integer getModifyBy() {
        return modifyBy;
    }

    public void setModifyBy(Integer modifyBy) {
        this.modifyBy = modifyBy;
    }

    public Date getModifyDate() {
        return modifyDate;
    }

    public void setModifyDate(Date modifyDate) {
        this.modifyDate = modifyDate;
    }

    public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", roleCode='" + roleCode + '\'' +
                ", roleName='" + roleName + '\'' +
                ", createBy=" + createBy +
                ", createionDate=" + createionDate +
                ", modifyBy=" + modifyBy +
                ", modifyDate=" + modifyDate +
                '}';
    }

    public Role() {
    }

    public Role(Integer id, String roleCode, String roleName, Integer createBy, Date createionDate, Integer modifyBy, Date modifyDate, List<User> users) {
        this.id = id;
        this.roleCode = roleCode;
        this.roleName = roleName;
        this.createBy = createBy;
        this.createionDate = createionDate;
        this.modifyBy = modifyBy;
        this.modifyDate = modifyDate;
        this.users = users;
    }
}

User类

public class User {
    private Integer id;
    private String userCode;
    private String userName;
    private String userPassword;
    private Integer gender;
    private String birthday;
    private Role role;   //关系对应  一个用户对应一个角色


    public Integer getId() {
        return id;
    }

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

    public String getUserCode() {
        return userCode;
    }

    public void setUserCode(String userCode) {
        this.userCode = userCode;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserPassword() {
        return userPassword;
    }

    public void setUserPassword(String userPassword) {
        this.userPassword = userPassword;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public String getBirthday() {
        return birthday;
    }

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

    public Role getRole() {
        return role;
    }

    public void setRole(Role role) {
        this.role = role;
    }

    public User() {
    }

    public User(Integer id, String userCode, String userName, String userPassword, Integer gender, String birthday, Role role) {
        this.id = id;
        this.userCode = userCode;
        this.userName = userName;
        this.userPassword = userPassword;
        this.gender = gender;
        this.birthday = birthday;
        this.role = role;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userCode='" + userCode + '\'' +
                ", userName='" + userName + '\'' +
                ", userPassword='" + userPassword + '\'' +
                ", gender=" + gender +
                ", birthday='" + birthday + '\'' +
                ", role=" + role +
                '}';
    }
}

测试类TestMybatis

public class TestMybatis {
    SqlSessionFactory sessionFactory =null;
    SqlSession sqlSession = null;
    RoleDao roleDao = null;

    @Before
    public void start(){
        InputStream resource = TestMybatis.class.getClassLoader()
                .getResourceAsStream("mybatis-config.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        sessionFactory = builder.build(resource);
        sqlSession = sessionFactory.openSession();
        roleDao = sqlSession.getMapper(RoleDao.class);


    }

    @Test
    public void test(){
        System.out.println(sqlSession);
        System.out.println("roledao:" + roleDao);
    }

    @Test
    public void testGetAll(){
        List<Role> roles = roleDao.getAll();
        for (Role r : roles) {
            System.out.println(r.toString());
        }
    }

    @Test
    public void testAddRole(){
        Role role = new Role();
        role.setRoleCode("0010");
        role.setRoleName("test");
        role.setCreateBy(1);
        role.setCreateionDate(new Date());
        Integer integer = roleDao.addRole(role);
        System.out.println(integer);
    }

    @Test
    public void testUpdateRole(){
        Role role = new Role();
        role.setId(5);
        role.setRoleCode("r0010");
        role.setRoleName("testRole");
        role.setModifyBy(1);
        role.setModifyDate(new Date());
        Integer integer = roleDao.updateRole(role);
        System.out.println(integer);
    }

    @Test
    public void testDelete(){
        Integer integer = roleDao.deleteRole(1);
        System.out.println(integer);
    }

    @Test
    public void testGetAll2(){
        List<Role> role2 = roleDao.getAll2();
        for (Role r : role2) {
            System.out.println(r.toString());
            List<User> users = r.getUsers();
            if (users!=null){
                for (User user : users) {
                    System.out.println(user.toString());
                }
            }
        }
    }

    @After
    public void end(){
        sqlSession.commit();
        sqlSession.close();
        System.out.println("over");
    }

}