mybatis学习(CRUD)

mybatis的增删改查

依赖

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

实体类

public class User implements Serializable {
    private  Integer id;
    private  String username;
    private Date birthday;
    private  String sex;
    private  String address;

    public User() {
        super();
    }

    public User(Integer id, String username, Date birthday, String sex, String address) {
        this.id = id;
        this.username = username;
        this.birthday = birthday;
        this.sex = sex;
        this.address = address;
    }

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

实体映射接口类

public interface UserMapper {
    //查所有
    List<User> findAll();
    //查询一个
    User getUserById(Integer id);
    //添加
    void addUser(User user);
    //删除
    void  deleteUserById(Integer id);
    //更新
    void  updateUser(User user);
    //根据名字查所有
    List<User> getUserByName(String username);
    //查询用户总记录数
    int findTotal();
  
}

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>
  
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <!--mysql8以上版本url：需添加如下代码-->
                <!--?useUnicode=true&amp;useJDBCCompliantTimezoneShift=true&amp;useLegacyDatetimeCode=false&amp;serverTimezone=UTC-->
                <property name="url"    value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&amp;useJDBCCompliantTimezoneShift=true&amp;useLegacyDatetimeCode=false&amp;serverTimezone=UTC"/>
                <property name="username"   value="root"/>
                <property name="password" value="151630"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="zlb/dao/UserMapper.xml"></mapper>
    </mappers>
    
</configuration>

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD zlb.dao.Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="zlb.dao.UserMapper">
    <!--查询所有-->
    <select id="findAll" resultType="zlb.domain.User">
        select  * from user
    </select>

    <!--根据ID查询一个-->
    <select id="getUserById" resultType="zlb.domain.User" >
    select  * from user where id=#{id}
    </select>

    <!--添加用户-->
    <insert id="addUser">
        /*获取新增用户的id*/
        <selectKey keyProperty="id" order="AFTER" resultType="int">
            select last_insert_id()
        </selectKey>
        insert user (username, birthday, sex, address)
        VALUES (#{username},#{birthday},#{sex},#{address})
    </insert>

    <!--根据ID删除用户-->
    <delete id="deleteUserById" >
        delete from user where id=#{id}
    </delete>

    <!--更新用户-->
    <update id="updateUser">
           update user
           set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
    </update>

    <!--模糊查询-->
    <select id="getUserByName" resultType="zlb.domain.User" parameterType="String">
        select  * from user where username like concat('%',#{0},'%')
    </select>

    <!--查询总用户记录数-->
    <select id="findTotal" resultType="int">
        select count(id) from user
    </select>

</mapper>

log4j.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

    <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
        <param name="Encoding" value="UTF-8" />
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m  (%F:%L) \n" />
        </layout>
    </appender>
    <logger name="java.sql">
        <level value="debug" />
    </logger>
    <logger name="org.apache.ibatis">
        <level value="info" />
    </logger>
    <root>
        <level value="debug" />
        <appender-ref ref="STDOUT" />
    </root>
</log4j:configuration>

测试类

public class MybatisTest {
   static InputStream resourceAsStream;
    static {
        try {
            resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    static SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(resourceAsStream);
    //sqlSessionFactory.openSession(true)
    //参数为true，自动提交事务
    static SqlSession sqlSession = sqlSessionFactory.openSession(true);
    static UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    /*
    * 查询所有
    * */
    @Test
    public  void test01() {
        List<User> users = mapper.findAll();
        for (User user : users) {
            System.out.println(user);
        }
        sqlSession.close();

    }
    /*
    * 根据ID查询一个
    * */
    @Test
    public  void test02() {
        User user= mapper.getUserById(45);
        System.out.println(user);
        sqlSession.close();
    }
    /*
    *添加
    * */
    @Test
    public  void test03() throws ParseException {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yy-MM-dd");
       /* Date date1=new Date();
        simpleDateFormat.format(date1);*/
        Date date = simpleDateFormat.parse("2019-09-15");
        User user=new User(null,"曾啊牛",date,"男","湖南娄底");
        //保存前
        System.out.println(user.getId());
        mapper.addUser(user);
        //保存后
        System.out.println(user.getId());
        sqlSession.close();
    }
    /*
    * 删除
    * */
    @Test
    public  void test04() {
        mapper.deleteUserById(46);
        sqlSession.close();
    }
    /*
    * 模糊查询
    * */
    @Test
    public  void test05() {
        List<User> user = mapper.getUserByName("王");
        for (User user1 : user) {
            System.out.println(user1.getUsername());
        }
        sqlSession.close();
        System.out.println(user);
    }
    /*
    * 查询用户总记录数
    * */
    @Test
    public  void test06() {
        int total = mapper.findTotal();
        System.out.println(total);
        sqlSession.close();
    }

  
}