maven-day03(mybatis的企业开发版)

1.通过dao层和映射文件的关联完成数据库增删改查

在dao层自定义方法 然后调用自己写的方法完成所有操作

1.1实现过程

(1)在dao层创建接口

public interface UserDao {
    /*查询全部*/
    public List<User> findAll();
    /*添加用户*/
    public int addUser(User user);

    /*通过姓名和真实姓名查找*/
    public User findByNameAndRealName(@Param("username")String username,
                                      @Param("realname")String realname);
    /*通过id查询用户*/
    public User findById(int id);

    /*通过id删除用户*/
    public int deleteUser(int id);

    /*修改用户姓名和真实姓名*/
    public int update(User user);
}

(2)修改之前的映射文件 (UserMapper.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="com.wx.dao.UserDao">

    <!--添加用户-->
    <insert id="addUser"  parameterType="User"   useGeneratedKeys="true" keyProperty="userid">
        insert into tb_user values(null,#{username},#{realname})
    </insert>
    
    <!--查询一条记录-->
    <select id="findById" parameterType="int" resultType="User">
        select * from tb_user where userid=#{id}
    </select>

    <!--删除用户-->
    <delete id="deleteUser" parameterType="int" >
        delete  from tb_user where userid=#{id}
    </delete>

    <!--修改用户-->
    <update id="update" parameterType="User">
        update tb_user set username=#{username},realname=#{realname} where userid=#{userid}
    </update>
    
    <!--查询全部-->
    <select id="findAll" resultType="User">
        select * from tb_user
    </select>

    <!--通过姓名和真实姓名查询-->
    <select id="findByNameAndRealName" resultType="com.wx.entity.User">
        select * from tb_user where username=#{username} and realname=#{realname}
    </select>

</mapper>

注意:UserMapper.xml中的namespace必须和创建的接口目录一致，id与方法名一一对应 如图所示

!](https://img-blog.csdnimg.cn/7580bc0834e5480f807ac2a65b7c0726.png)

（3）测试

    //测试通过id查询用户
    @Test
    public void testSelect() throws Exception{
        Reader rd = Resources.getResourceAsReader("conf.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(rd);
        SqlSession session = factory.openSession();
        UserDao userDao = session.getMapper(UserDao.class);
        User user = userDao.findById(5);
        System.out.println(user);
        session.commit();
        session.close();
    }
    
    //测试添加用户
    @Test
    public void testAdd() throws Exception{
        Reader rd = Resources.getResourceAsReader("conf.xml");
        SqlSessionFactory factoy = new SqlSessionFactoryBuilder().build(rd);
        SqlSession session = factoy.openSession();
        User user = new User();
        user.setUsername("xd");
        user.setRealname("学都");
        UserDao userdao = session.getMapper(UserDao.class);
        int row = userdao.addUser(user);
        System.out.println(user);
        session.commit();
        session.close();
    }

    
    //删除用户测试
    @Test
    public void testDel() throws Exception{
        Reader rd = Resources.getResourceAsReader("conf.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(rd);
        SqlSession session = factory.openSession();
        UserDao userdao = session.getMapper(UserDao.class);
        int row = userdao.deleteUser(5);
        System.out.println(row);
        session.commit();
        session.close();
    }

    //测试修改
    @Test
    public void testUpdate() throws Exception{
        Reader rd = Resources.getResourceAsReader("conf.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(rd);
        SqlSession session = factory.openSession();
        User user = new User(20, "wc", "午吃");
        UserDao userdao = session.getMapper(UserDao.class);
        int row = userdao.update(user);
        System.out.println(row);
        session.commit();
        session.close();
    }

    //测试查询全部
    @Test
    public void testQueryAll() throws Exception{
        Reader rd = Resources.getResourceAsReader("conf.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(rd);
        SqlSession session = factory.openSession();
        UserDao userdao = session.getMapper(UserDao.class);
        List<User> user = userdao.findAll();
        System.out.println(user);
        session.close();
    }

2 安装mybatis插件

作用: 检查dao和映射文件是否匹配


箭头可直接跳到与UserMapper.xml 迅速检查自己的sql代码

3 解决传递多个参数问题

实例:通过姓名和真实姓名查找用户
（1）在dao层中Java代码为

/*通过姓名和真实姓名查找*/
    public User findByNameAndRealName(@Param("username")String username,
                                      @Param("realname")String realname);

（2）在UserMapper.xml中代码为

  <!--通过姓名和真实姓名查询-->
    <select id="findByNameAndRealName" resultType="com.wx.entity.User">
        select * from tb_user where username=#{username} and realname=#{realname}
    </select>

解释:在参数处使用@Param()为参数起名即可

4 添加一条记录返回其在数据库中真实id

之前的代码添加一条记录后，在控制台输出后发现id为0 所以为了解决这个问题只需要在映射文件中添加 useGeneratedKeys=“true” keyProperty="userid"即可

    <!--添加用户
		  useGeneratedKeys:设置使用生成的主键
          keyProperty: 赋值给哪个属性
	-->
    <insert id="addUser"  parameterType="User"   useGeneratedKeys="true" keyProperty="userid">
        insert into tb_user values(null,#{username},#{realname})
    </insert>

5.解决数据库字段名字和实体层名字不一致问题

数据库字段如下:

实体层；

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int id;
    private String stuName;
    private int stuAge;
    private String stuSex;
}

可发现数据库字段与实体层属性不一致
解决方法有两种:
(1) 为查询的列起别名，而别名和属性名一致

(2)使用resultMap完成列和属性之间的映射关系。(推荐)

 <resultMap id="StuMapper" type="com.wx.entity.Student">
        <id column="stu_id" property="id" />
        <result column="stu_name" property="stuName" />
        <result column="stu_age" property="stuAge"/>
        <result column="stu_sex" property="stuSex"/>
    </resultMap>
    <select id="findOne" resultMap="StuMapper">
        select * from tb_stu where stu_id=#{id}
    </select>