mybatisCRUD操作

mybatis02

mybatisCRUD操作

注解书写方式

/**
     * 查询所有用户
     * @return
     */
    @Select("select * from user")
    List<User> findAll();

    /**
     * 添加用户
     * @return
     */
    @Insert("insert into user(username,birthday,sex,address) values (#{username},#{birthday},#{sex},#{address})")
    void saveUser(User user);

    /**
     * 修改用户
     * @return
     */
    @Update("update user set username = #{username} where id = #{id}")
    void updateUser(User user);

    /**
     * 删除用户
     * @return
     */
    @Delete("delete from user where id = #{id}")
    void deleteUser(Integer id);

    /**
     * 根据id查找用户
     * @return
     */
    @Select("select * from user where id = #{id}")
    User findById(Integer id);

    /**
     * 模糊查询
     * @return
     */
    //@Select("select * from user where username like '%${value}%'")另一张写法,value为固定写法
    @Select("select * from user where username like #{username}")
    List<User> findByName(String username);

    /**
     * 总记录数
     * @return
     */
    @Select("select count(*) from user")
    int finTotal();

测试类

private InputStream in;
    private SqlSession session;
    private UserDao userDao;
    @Before
    public void init() throws IOException {
        //1.读取配置文件
        in = Resources.getResourceAsStream("SqlMapConfig");
        //2.SqlSessionFactory
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        //3.使用工厂生产SQLSession对象
        session = factory.openSession();
        //创建dao接口的代理对象
        userDao = session.getMapper(UserDao.class);
    }

    @After
    public void destory() throws IOException {
        //事务提交
        session.commit();
        //释放资源
        session.close();
        in.close();
    }
    @Test
    public void testFindAll(){
        List<User> users = userDao.findAll();
        for (User user : users) {
            System.out.println(user);
        }
    }
    @Test
    public void testSave(){
        User user =new User();
        user.setUsername("cccc");
        user.setSex("男");
        user.setBirthday(new Date());
        user.setAddress("中国");
        userDao.saveUser(user);
    }

    @Test
    public void testUpdate(){
        User user =new User();
        user.setId(61);
        user.setUsername("cccc");
        userDao.updateUser(user);
    }
    @Test
    public void testDelete(){
        userDao.deleteUser(62);
    }

    @Test
    public void testFindById(){
        User user = userDao.findById(46);
        System.out.println(user);
    }

    @Test
    public void testFindByName(){
        List<User> users = userDao.findByName("%cc%");
        for (User user : users) {
            System.out.println(user);
        }
    }

    @Test
    public void testTotal(){
        int total = userDao.finTotal();
        System.out.println(total);
    }

xml书写方式

    <!-- 查询所有 -->
    <select id="findAll" resultMap="userMap">
        <!--select id as userId,username as userName,address as userAddress,sex as userSex,birthday as userBirthday from user;-->
        select * from user;
    </select>

    <!-- 保存用户 -->
    <insert id="saveUser" parameterType="user">
        <!-- 配置插入操作后，获取插入数据的id -->
        <selectKey keyProperty="userId" keyColumn="id" resultType="int" order="AFTER">
            select last_insert_id();
        </selectKey>
        insert into user(username,address,sex,birthday)values(#{userName},#{userAddress},#{userSex},#{userBirthday});
    </insert>

    <!-- 更新用户 -->
    <update id="updateUser" parameterType="USER">
        update user set username=#{userName},address=#{userAddress},sex=#{userAex},birthday=#{userBirthday} where id=#{userId}
    </update>

    <!-- 删除用户-->
    <delete id="deleteUser" parameterType="java.lang.Integer">
        delete from user where id = #{uid}
    </delete>
    
    <!-- 根据id查询用户 -->
    <select id="findById" parameterType="INT" resultMap="userMap">
        select * from user where id = #{uid}
    </select>

    <!-- 根据名称模糊查询 -->
    <select id="findByName" parameterType="string" resultMap="userMap">
          select * from user where username like #{name}
        <!-- select * from user where username like '%${value}%'-->
   </select>

    <!-- 获取用户的总记录条数 -->
    <select id="findTotal" resultType="int">
        select count(id) from user;
    </select>

查询条件对象

OGNL表达式：
Object Graphic Navigation Language
对象 图 导航 语言

它是通过对象的取值方法来获取数据。在写法上把get给省略了。
比如：我们获取用户的名称
类中的写法：user.getUsername();
OGNL表达式写法：user.username
mybatis中为什么能直接写username,而不用user.呢：
因为在parameterType中已经提供了属性所属的类，所以此时不需要写对象名

user代表QueryVo中的user对象

@Getter
@Setter
public class QueryVo {
    private User user;
}

 @Select("select * from user where username like #{user.username}")
    List<User> findUserByVo(QueryVo vo);

<!-- 根据queryVo的条件查询用户 -->
    <select id="findUserByVo" parameterType="com.itheima.domain.QueryVo" resultMap="userMap">
        select * from user where username like #{user.username}
    </select>
    
QueryVo vo = new QueryVo();
        User user =new User();
        user.setUsername("%王%");
        vo.setUser(user);
        List<User> users = userDao.findUserByVo(vo);
        for (User user1 : users) {
            System.out.println(user1);
        }

实体类名与数据库名不一致

 dao.xml配置
 <!-- 配置 查询结果的列名和实体类的属性名的对应关系 -->
    <resultMap id="userMap" type="uSeR">
        <!-- 主键字段的对应 -->
        <id property="userId" column="id"></id>
        <!--非主键字段的对应-->
        <result property="userName" column="username"></result>
        <result property="userAddress" column="address"></result>
        <result property="userSex" column="sex"></result>
        <result property="userBirthday" column="birthday"></result>
    </resultMap>
sqlmapconfig.xml配置
<!--    使用typealiases配置别名,只能配置domain包中的别名-->
    <typeAliases>
<!--        type实体类,  alias别名,在UserDao.xml中可以使用别名
        <typeAlias type="com.cc.domain.User" alias="user"></typeAlias>-->
<!--        要配置别名的包,包下的所有类都会生产别名,类名就是别名,不区分大小写-->
        <package name="com.cc.domain"/>
    </typeAliases>

其他优化

使用properties引用数据库外部配置文件

<!--    引用外部配置文件
        resource  指定配置文件位置,按照类路径的写法来写,必须保存类路径下
        url    按照url方式来写    协议+主机+端口+URI
        url="file:///E:/idea/mybatis/mybatisDao/src/main/resources/jdbcConfig.properties"
-->
    <properties resource="jdbcConfig.properties">
    </properties>
    
    <property name="driver" value="${driver}"></property>
    <property name="url" value="${url}"></property>
    <property name="username" value="${username}"></property>
    <property name="password" value="${password}"></property>

遇到的问题

mybatis插入数据库数据乱码

解决方式,修改链接

jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8