当前位置:Gxlcms > 数据库问题 > 6、MySQL之常见的单表CRUD操作

6、MySQL之常见的单表CRUD操作

时间:2021-07-01 10:21:17 帮助过:19人阅读

  • CRUD
    • 增 (saveUser)
    • 删 (deleteUser)
    • 改 (updateUser)
    • 查 (findAllUser,FindUserById)
  • like (findUserByName,findUserByName2)
  • 聚合函数 (findTotal)
  • 获取保存数据的id(saveUser)
  • OGNL取值 (findUserByWorkCard)

准备工作

1、user实体类

public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String gender;
    private String address;
      //省略get、set和toString方法
}

2、WorkCard实体类

public class WorkCard {
    private User user;
     //省略get、set方法
}

3、数据库

CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘用户名称‘,
  `birthday` datetime DEFAULT NULL COMMENT ‘生日‘,
  `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘性别‘,
  `address` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘地址‘,
  PRIMARY KEY (`id`) USING BTREE
)

表结构
技术图片

接口

public interface UserMapper {
    /**
     * 查询所有用户
     * @return
     */
    List<User> findAllUser();
    /**
     * 保存用户
     */
    void saveUser(User user);
    /**
     * 更新用户
     */
    void updateUser(User user);
    /**
     * 删除用户
     */
    void deleteUser(Integer id);
    /**
     * 根据id查询用户
     */
    User findUserById(Integer id);
    /**
     * like模糊查询,第一种
     */
    List<User> findUserByName(String username);
    /**
     * like模糊查询,第二种
     */
    List<User> findUserByName2(String username);
    /**
     * 查询总用户数(聚合函数count)
     */
    int findTotal();
    /**
     * 根据WorkCard类中User属性的username模糊查询
     * WorkCard是user的封装类
     * OGNL表达式取值
     */
    List<User> findUserByWorkCard(WorkCard workCard);
}

映射文件

<?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">
<!--namespace填写的是Mapper接口的全限定名-->
<mapper namespace="com.example.mapper.UserMapper">
    <!--查询所有-->
    <select id="findAllUser" resultType="com.example.pojo.User">
        select * FROM user
    </select>
    <!--添加用户-->
    <insert id="saveUser" parameterType="com.example.pojo.User">
        <!--配置插入操作后,获取插入数据的id -->
        <selectKey keyProperty="id" keyColumn="id" resultType="int" order="AFTER">
          SELECT last_insert_id();
        </selectKey>
        INSERT INTO user(username,birthday,gender,address) VALUES (#{username},#{birthday},#{gender},#{address})
    </insert>
    <!--更新用户-->
    <update id="updateUser" parameterType="com.example.pojo.User">
        UPDATE user SET username = #{username}, birthday = #{birthday}, gender = #{gender}, address = #{address} WHERE id = #{id}
    </update>
    <!--删除用户-->
    <delete id="deleteUser" parameterType="Integer">
        <!--当参数只用一个并且参数是基本数据类型的包装类是#{占位符}占位符可以随便写 -->
        DELETE FROM user WHERE id = #{userid}
    </delete>
    <!--通过id查询用户-->
    <select id="findUserById" parameterType="Integer" resultType="com.example.pojo.User">
        select * FROM user where id = #{userId}
    </select>
    <!--通过名字模糊查询用户-->
    <select id="findUserByName" parameterType="java.lang.String" resultType="com.example.pojo.User">
        SELECT * FROM user WHERE username LIKE ‘%${username}%‘
    </select>
    <!--通过名字模糊查询用户-->
    <select id="findUserByName2" parameterType="java.lang.String" resultType="com.example.pojo.User">
        SELECT * FROM user WHERE username LIKE #{username}
    </select>
    <!--查询总用户数,聚合函数count()-->
    <select id="findTotal" resultType="int">
        SELECT count(*) FROM user
    </select>

    <!--OGNL表达式
        Object Graphic Navigation Language 对象 图 导航 语言
        它是通过对象的取值方法来获取数据,在写法上吧get给省略了
        比如:获取用户名
            通常的写法:user.getUsername();
            OGNL表达式写法 user.username
    -->
    <select id="findUserByWorkCard" parameterType="com.example.pojo.WorkCard" resultType="com.example.pojo.User">
        SELECT * FROM user WHERE username LIKE #{user.username}
    </select>
</mapper>
public class UserMapperTest {
    private UserMapper userMapper;
    private SqlSession sqlSession;
    private InputStream stream;

    @Before
    public void init() throws IOException {
        stream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
        sqlSession = sqlSessionFactory.openSession();
        userMapper = sqlSession.getMapper(UserMapper.class);
    }

    @After
    public void destory() throws IOException {
//        每次执行完都进行事务提交
        sqlSession.commit();
        sqlSession.close();
        stream.close();
    }

    /**
     * 查询所有
     */
    @Test
    public void testFindAll(){

        List<User> allUser = userMapper.findAllUser();
        for (User user: allUser
             ) {
            System.out.println(user);
        }
    }

    /**
     * 保存用户
     */
    @Test
    public void testSaveUser(){
        User user = new User();
        user.setUsername("小红");
        user.setGender("男");
        user.setBirthday(new Date());
        user.setAddress("汕头市");
        userMapper.saveUser(user);

        System.out.println(user);
    }
    /**
     * 更新用户
     */
    @Test
    public void testUpdateUser(){
        User user = new User();
        user.setUsername("小红");
        user.setGender("女");
        user.setBirthday(new Date());
        user.setAddress("汕头市");
        user.setId(52);
        userMapper.updateUser(user);
    }
    /**
     * 删除用户
     */
    @Test
    public void testDeleteUser(){
        userMapper.deleteUser(50);
    }
    /**
     * 根据用户id查询用户
     */
    @Test
    public void testFindUserById(){
        User user = userMapper.findUserById(48);
        System.out.println(user);
    }
    /**
     * 根据用户姓名模糊查询用户
     */
    @Test
    public void testFindUserByName(){
        /**
         * 第一种写法在dao层的sql语句中添加‘%${username}%‘,这种方式的缺点是不能预处理
         */
        List<User> users = userMapper.findUserByName("小");
        for (User user: users
             ) {
            System.out.println(user);
        }
    }
    /**
     * 根据用户姓名模糊查询用户
     */
    @Test
    public void testFindUserByName2(){
        /**
         * 第二种写法是在传参是就添加了%
         */
        List<User> users = userMapper.findUserByName2("%" + "小" + "%");
        for (User user: users
                ) {
            System.out.println(user);
        }
    }
    /**
     * 测试聚合函数
     */
    @Test
    public void testFindTotal(){
        int total = userMapper.findTotal();
        System.out.println(total);
    }

    /**
     * OGNL取值
     */
    /**
     * 根据用户姓名模糊查询用户
     */
    @Test
    public void findUserByWorkCard(){
        User user = new User();
        user.setUsername("%" + "小" + "%");
        WorkCard workCard = new WorkCard();
        workCard.setUser(user);
        List<User> users = userMapper.findUserByWorkCard(workCard);
        for (User u: users
             ) {
            System.out.println(u);
        }
    }
}

6、MySQL之常见的单表CRUD操作

标签:准备工作   属性   mybatis   res   date()   ring   val   保存   datetime   

人气教程排行