mybatis学习笔记二（crud）

1、mybatis的简单实现流程分析

2、环境搭建

依赖jar包

  <!--导入mybatis依赖-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.3</version>
        </dependency>
        <!--数据库驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <!--导入单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.16</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.12</version>
        </dependency>

数据库配置文件jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql:///mybatis?useSSL=false&useUnicode=true&characterEncoding=utf-8
username=root
password=

配置实现

mybatis主配置文件SqlMapConfig.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="jdbc.properties">
        <property name="driver" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
    </properties>
    <!--配置环境-->
    <environments default="mysql">
        <!--配置mysql环境-->
        <environment id="mysql">
            <!--配置事务的类型-->
            <transactionManager type="JDBC"/>
            <!--配置事务的类型-->
            <dataSource type="POOLED">
                <!--配置连接数据库的4个基本信息-->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--指定映射文件的位置 ,必须是dao接口全限定类名-->
    <mappers>
        <mapper resource="com/wjh/dao/UserDao.xml"/>
    </mappers>
</configuration>

映射文件

<?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">

<!--对应的dao接口-->
<mapper namespace="com.wjh.dao.UserDao">
    <!--id的值必须是对应的方法名-->
    <!--根据用户id查询用户-->
    <select id="selectById" parameterType="int" resultType="com.wjh.domain.User">
        select * from user where id = #{uid}
    </select>
    <!--查询所有用户-->
    <select id="getAllUser" resultType="com.wjh.domain.User">
        select * from user
    </select>
    <!--添加用户-->
    <insert id="addUser" parameterType="com.wjh.domain.User">
        insert into user(username,birthday,sex,address)values (#{username},#{birthday},#{sex},#{address})
    </insert>
    <!--修改用户-->
    <update id="updateUser" parameterType="com.wjh.domain.User">
        update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
    </update>
    <!--删除用户-->
    <delete id="deleteUser" parameterType="java.lang.Integer">
        delete from user where id=#{id}
    </delete>
    <!--根据用户名称模糊查询用户-->
    <select id="selectByName" parameterType="string" resultType="com.wjh.domain.User">
        select * from user where username like #{username}
    <!-- select * from user where username like '%${username}'-->
 </select>
 <!--查询记录总数-->
    <select id="getTotal" resultType="int">
        select count(1) from user
    </select>
</mapper>

代码实现

实体类

@Data
public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private char sex;
    private  String address;
}

dao接口

public interface UserDao {
    //根据用户id查询用户
    User selectById(int id);
    //查询所有用户
    List<User> getAllUser();
    //添加用户
    void addUser(User user);
    //修改用户
    void updateUser(User user);
    //输出用户
    void deleteUser(int id);
    //根据用户名称模糊查询用户
    List<User> selectByName(String username);
    //查询记录总数
    int getTotal();
}

测试

public class MybatisTest {

    private InputStream is = null;
    private SqlSession session = null;
    private UserDao userDao = null;

    @Before
    public void before(){
        try {
            //1、读取配置文件
            is = Resources.getResourceAsStream("SqlMapConfig.xml");
            //2、创建SqlSessionFactory工厂
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
            //3、使用工厂生成Sqlsession对象
            session = factory.openSession(true);
            //4、使用Sqlsession对象创建dao接口的代理对象
             userDao = session.getMapper(UserDao.class);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @After
    public void after(){
        //6、释放资源
        try {
            session.close();
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //根据用户id查询用户
    @Test
    public void testGetOne(){
        User user = userDao.selectById(53);
        System.out.println(user);
    }
    //查询用户
    @Test
    public void testselect(){

            //5、使用代理对象执行方法
            List<User> userList = userDao.getAllUser();
            for (User user : userList){
                System.out.println(user);
            }
    }
    //添加用户
    @Test
    public void insertUser() throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = sdf.parse("1998-10-13");
        User user = new User();
        user.setUsername("李子敬");
        user.setBirthday(new java.sql.Date(date.getTime()));
        user.setSex('女');
        user.setAddress("广东省梅州市");
        userDao.addUser(user);
    }
    //修改用户
    @Test
    public void updateUser() throws ParseException {
        User user = new User();
        user.setId(48);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = sdf.parse("1991-12-10");
        user.setBirthday(new java.sql.Date(date.getTime()));
        user.setUsername("林嘉鹏");
        user.setSex('男');
        user.setAddress("广东省潮汕");
        userDao.updateUser(user);

    }
    //删除用户
    @Test
    public void testDel(){
        userDao.deleteUser(45);
    }

    //根据用户名称模糊查询用户
    @Test
    public void testSelectByName(){
        List<User> users = userDao.selectByName("%王%");
        for (User user : users){
            System.out.println(user);
        }
    }

    //查询记录总数
    @Test
    public void testgetTotal(){
        int total = userDao.getTotal();
        System.out.println(total);
    }
}

补充：对用户名称模糊查询有两种方式，

select * from user where username like ‘%${username}’

底层用的是字符拼接，不能防止sql注入。