mybatis使用

IDEA可以安装mybatis插件，提高效率，可以自动生成

自动生成需要连接数据库，点击需要生成的表，右键第一第二项

配置文件

database.properties配置

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mytest?useSSL=false&useUnicode=true&characterEncoding=utf8
username=root
userpassword=root

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>
    <settings>
    <!--    后台打印sql语句    -->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <!-- 环境配置 -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <!-- 数据库连接相关配置 ,这里动态获取config.properties文件中的内容-->
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${userpassword}" />
            </dataSource>
        </environment>
    </environments>
    <!-- mapping文件路径配置 -->
    <mappers>
        <mapper resource="mapper/xml/UsersMapper.xml"/>
        <mapper resource="mapper/xml/DeptMapper.xml"/>
    </mappers>
</configuration>

xxxMapper.xml配置

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<mapper namespace="com.zl.mapper.UsersMapper" ><!-- namespace="XXXMapper接口文件" -->
    <resultMap id="BaseResultMap" type="com.zl.entity.Users" >
        <id column="userid" property="userid" jdbcType="INTEGER" />
        <result column="username" property="username" jdbcType="VARCHAR" />
        <result column="password" property="password" jdbcType="VARCHAR" />
        <result column="sex" property="sex" jdbcType="INTEGER" />
        <result column="state" property="state" jdbcType="VARCHAR" />
        <result column="role" property="role" jdbcType="INTEGER" />
        <result column="deptid" property="deptid" jdbcType="INTEGER" />
        <!-- <association property="deptDeptid" column="deptid" select="com.isscollege.dao.DeptDao.selectByPrimaryKey" /> -->
        <!-- 优化 -->
        <!-- 实体的属性是类对象进行联表查询 -->
        <association property="deptDeptid" javaType="com.zl.entity.Dept">
            <!-- 去掉关联查询，将另一张表的属性配置出来 -->
            <id column="deptid" property="deptid" jdbcType="INTEGER" />
            <result column="deptname" property="deptname" jdbcType="VARCHAR" />
        </association>
        <!-- 实体的属性是集合进行联表查询 -->
        <!-- <collection property="deptDeptid" ofType="com.zl.entity.Dept">
  		<id column="deptid" property="deptid" jdbcType="INTEGER" />
            <result column="deptname" property="deptname" jdbcType="VARCHAR" />
  	</collection> -->
    </resultMap>
    <sql id="Base_Column_List" >
        userid, username, `password`, sex, `state`, roleid, deptid
    </sql>

    <select id="selectByDeptid" resultMap="BaseResultMap" >
        select <include refid="Base_Column_List" /> from users where deptid =#{deptid}
    </select>

    <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
        select
        <include refid="Base_Column_List" />
        from users
        where userid = #{userid,jdbcType=INTEGER}
    </select>

    <select id="getAllUserInfo" resultMap="BaseResultMap" >
        select * from users
    </select>

    <select id="getAllUserInfo_Deptname" parameterType="com.zl.entity.Users" resultMap="BaseResultMap">
        SELECT u.*,d.deptname FROM users u ,dept d WHERE u.deptid=d.deptid  and u.username=#{username}
    </select>

    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
        delete from users
        where userid = #{userid,jdbcType=INTEGER}
    </delete>

    <insert id="insert" parameterType="com.zl.entity.Users" useGeneratedKeys="true" keyProperty="userid" >
        insert into users (username, `password`, sex,
        `state`, roleid, deptid
        )
        values (#{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, #{sex,jdbcType=INTEGER},
        #{state,jdbcType=VARCHAR}, #{roleid,jdbcType=INTEGER}, #{deptid,jdbcType=INTEGER}
        )
    </insert>

    <insert id="insertSelective" parameterType="com.zl.entity.Users" useGeneratedKeys="true" keyProperty="userid" >
        insert into users
        <trim prefix="(" suffix=")" suffixOverrides="," >
            username,
            `password`,
            sex,
            `state`,
            roleid,
            deptid,
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides="," >
            #{username,jdbcType=VARCHAR},
            #{password,jdbcType=VARCHAR},
            #{sex,jdbcType=INTEGER},
            #{state,jdbcType=VARCHAR},
            #{roleid,jdbcType=INTEGER},
            #{deptid,jdbcType=INTEGER},
        </trim>
    </insert>
    <update id="updateByPrimaryKeySelective" parameterType="com.zl.entity.Users" >
        update users
        <set >
            <if test="username != null" >
                username = #{username,jdbcType=VARCHAR},
            </if>
            <if test="password != null" >
                `password` = #{password,jdbcType=VARCHAR},
            </if>
            <if test="sex != null" >
                sex = #{sex,jdbcType=INTEGER},
            </if>
            <if test="state != null" >
                `state` = #{state,jdbcType=VARCHAR},
            </if>
            <if test="roleid != null" >
                roleid = #{roleid,jdbcType=INTEGER},
            </if>
            <if test="deptid != null" >
                deptid = #{deptid,jdbcType=INTEGER},
            </if>
        </set>
        where userid = #{userid,jdbcType=INTEGER}
    </update>
    <update id="updateByPrimaryKey" parameterType="com.zl.entity.Users" >
        update users
        set username = #{username,jdbcType=VARCHAR},
        `password` = #{password,jdbcType=VARCHAR},
        sex = #{sex,jdbcType=INTEGER},
        `state` = #{state,jdbcType=VARCHAR},
        roleid = #{roleid,jdbcType=INTEGER},
        deptid = #{deptid,jdbcType=INTEGER}
        where userid = #{userid,jdbcType=INTEGER}
    </update>
    <select id="listPermissionByRoleId" parameterType="java.util.List"
          resultType="com.zl.stu.studentInfoManage.authManagement.entity.SysPermission">
      SELECT p.permission_id,p.permission_code,permission_url
      FROM s_sys_permission AS p LEFT JOIN s_sys_role_permission AS rp
      ON p.permission_id = rp.permission_id
      WHERE rp.role_id IN
      <foreach collection="roleIdList" index="index" item="item" open="(" separator="," close=")">
          #{item}
      </foreach>
    </select>
</mapper>

mapper接口文件

package com.zl.mapper;

import com.zl.entity.Users;

import java.util.List;

public interface UsersMapper {
    //根据部门id查询人员
    List<Users> selectByDeptid(int deptid);
    //根据用户编号查询用户
    Users selectByPrimaryKey(Integer userid);
    //获取用户全部信息
    List<Users> getAllUserInfo();
    //根据用户名字查询用户信息
    Users getAllUserInfo_Deptname(Users users);
    //根据用户编号删除用户
    int deleteByPrimaryKey(Integer userid);
    //添加用户
    int insert(Users users);
    //添加用户
    int insertSelective(Users users);
    //根据用户编号进行修改
    int updateByPrimaryKeySelective(Users users);
    //根据用户编号进行修改
    int updateByPrimaryKey(Users users);
}

编写mybatis获取连接的工具类

package com.zl.utils;

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 java.io.IOException;
import java.io.Reader;

public class SqlSessionFactoryUtiles{
    //饿汉模式 线程安全
    private static SqlSessionFactoryUtiles sqlSessionFactoryUtiles=new SqlSessionFactoryUtiles();

    private SqlSessionFactory sqlMapper;
    private SqlSessionFactoryUtiles(){
        //定义读取文件名
        String resources = "mybatis-config.xml";
        //创建流
        Reader reader= null;
        try {
            reader = Resources.getResourceAsReader(resources);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //初始化mybatis,创建SqlSessionFactory类的实例
        sqlMapper=new SqlSessionFactoryBuilder().build(reader);
    }
    public static SqlSessionFactoryUtiles getSSqlSessionFactoryUtiles(){
        return sqlSessionFactoryUtiles;
    }
    public SqlSession getSession(Boolean bool){
        return sqlMapper.openSession(bool);
    }
}

编写测试类

package com.zl.test;

import com.zl.entity.Users;
import com.zl.mapper.UsersMapper;
import com.zl.utils.SqlSessionFactoryUtiles;

public class MyDeptTest {

    /**
     * @param args
     */
    public static void main(String[] args) {
        UsersMapper usersMapper = SqlSessionFactoryUtiles.getSSqlSessionFactoryUtiles()
                .getSession(true)
                .getMapper(UsersMapper.class);
        Users user = new Users();
        user.setUsername("lisi");
        user = usersMapper.getAllUserInfo_Deptname(user);
        System.err.println(user);
        if(user.getDeptDeptid()!=null){
            System.out.println(user.getDeptDeptid());
        }else{
            System.out.println("没有拿到");
        }
    }

}

结果

posted @ 2021-09-28 09:34 快乐小洋人阅读(38) 评论(0) 收藏举报

刷新页面返回顶部

下个路口。

mybatis使用

mybatis使用

公告