3- Realize CRUD operation based on proxy Dao

Requirements: 1.  The mapping configuration of the persistence layer interface and the persistence layer interface must be in the same package 2. The  value of the namespace attribute of the  mapper  tag in the  persistence layer mapping configuration must be the fully qualified class name of the persistence layer interface . 3  ,  the SQL  configuration tag statement  <select>, <insert>, <delete>, <update>  the  id  attribute must interface persistence layer The method name is the same.

1. Query by ID

Continue to operate in the mybatis01 project

1. Add the findById method to the persistence layer interface

/*** 根据 id 查询* @param userId* @return*/User findById(Integer userId);

2. Configure in the user's mapping configuration file

<!-- 根据 id 查询 -->    <select id="findById" resultType="cn.oesoft.mybatis01.domain.User" parameterType="int">        select *        from user        where id = #{uid}    </select>

detail: resultType attribute: Used to specify the type of result set. parameterType attribute  :   Used to specify the type of incoming parameters.      Use  #{}  characters in sql statement :    It represents a placeholder, which is equivalent to what   you learned in the original jdbc   part   .   It is used to replace actual data when executing a statement.    The specific data is determined by   the content in #{}   .    #{}How  to write the content:    Since the data type is a basic type, it can be written at will here.

3. Add a test in the test class

public class MybastisCRUDTest {    private InputStream in;    private SqlSessionFactory factory;    private SqlSession session;    private IUserDao userDao;     @Test    public void testFindOne() {//6.执行操作        User user = userDao.findById(41);        System.out.println(user);    }     @Before//在测试方法执行之前执行    public void init() throws Exception {//1.读取配置文件        in = Resources.getResourceAsStream("SqlMapConfig.xml");//2.创建构建者对象        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();//3.创建 SqlSession 工厂对象        factory = builder.build(in);//4.创建 SqlSession 对象        session = factory.openSession();//5.创建 Dao 的代理对象        userDao = session.getMapper(IUserDao.class);    }}

Two, save operation

1. Add a new method to the persistence layer interface

/*** 保存用户* @param user* @return 影响数据库记录的行数*/int saveUser(User user);

2. Configure in the user's mapping configuration file

<!-- 保存用户-->    <insert id="saveUser" parameterType="cn.oesoft.mybatis01.domain.User">        insert into user(username, birthday, sex, address)        values (#{username}, #{birthday}, #{sex}, #{address})    </insert>

detail:    parameterType   attribute:    Represents the type of the parameter, because we are passing in an object of a class, so the type is the full name of the class.       Use   #{}   characters in sql statement :    It represents a placeholder, which is equivalent to what   you learned in the original jdbc   part   .   It is used to replace actual data when executing a statement.    The specific data is determined by   the content in #{}   .    #{}How   to write the content:    Since the parameter of our save method is a   User   object, here we need to write   the attribute name in the User   object.    It uses   ognl   expressions.    ognl   expression:    It is   an expression language provided by apache   , its full name is:    Object Graphic Navigation Language   object graph navigation language    It obtains data in accordance with a certain grammatical format.     The syntax used is   # {   objects   .   Object   }   manner      #{user.username}    It will first find the    user    object, then    find the username attribute in the user    object    , and call         The getUsername()    method takes the value out. But we    specified the entity class name on the parameterType    attribute, so    user can be omitted .      Instead, write    username    directly .

3. Add the test method in the test class

@Test    public void testSave(){        User user = new User();        user.setUsername("modify User property");        user.setAddress("北京市顺义区");        user.setSex("男");        user.setBirthday(new Date(System.currentTimeMillis()));        System.out.println("保存操作之前:"+user);//5.执行保存方法        userDao.saveUser(user);        System.out.println("保存操作之后:"+user);    }

I opened the Mysql database and found that no records were added. What is the reason?This is the same as jdbc. We must control the commit of transactions when we implement additions, deletions, and modifications, so how to control transactions in mybatisSubmit it?You can use: session.commit(); to achieve transaction commit. The code after joining the transaction commit is as follows:

@After//在测试方法执行完成之后执行public void destroy() throws Exception{session.commit();//7.释放资源session.close();in.close();}

4. Question extension: add the return value of the user id   After adding a new user, it will also return the   id   value of the   current new user at the same time , because the id   is realized by the automatic growth of the database, so the corresponding    When we want to automatically increase   the value of auto_increment   after adding it , return it.

<!-- 保存用户-->    <insert id="saveUser" parameterType="cn.oesoft.mybatis01.domain.User">        <!-- 配置保存时获取插入的 id -->        <selectKey keyColumn="id" keyProperty="id" resultType="int">            select last_insert_id();        </selectKey>        insert into user(username, birthday, sex, address)        values (#{username}, #{birthday}, #{sex}, #{address})    </insert>

Three, user update

1. Add an update method to the persistence layer interface

/*** 更新用户* @param user* @return 影响数据库记录的行数*/int updateUser(User user);

2. Configure in the user's mapping configuration file

<!-- 更新用户 -->    <update id="updateUser" parameterType="cn.oesoft.mybatis01.domain.User">        update user        set username=#{username},            birthday=#{birthday},            sex=#{sex},            address=#{address}        where id = #{id}    </update>

3. Add updated test method

@Test    public void testUpdateUser()throws Exception{//1.根据 id 查询        User user = userDao.findById(50);//2.更新操作        user.setAddress("北京市顺义区");        int res = userDao.updateUser(user);        System.out.println(res);    }

Four, user deletion

/**     * 根据 id 删除用户     * @param userId     * @return     */    int deleteUser(Integer userId);
<!-- 删除用户 -->    <delete id="deleteUser" parameterType="java.lang.Integer">        delete        from user        where id = #{uid}    </delete>
 @Test    public void testDeleteUser() throws Exception {//6.执行操作        int res = userDao.deleteUser(50);        System.out.println(res);    }

Five, user fuzzy query

1. The first

/*** 根据名称模糊查询* @param username* @return*/List<User> findByName(String username);
<!-- 根据名称模糊查询 -->    <select id="findByName" resultType="cn.oesoft.mybatis01.domain.User" parameterType="String">        select *        from user        where username like #{username}    </select>
@Test    public void testFindByName(){        //5.执行查询一个方法        List<User> users = userDao.findByName("%王%");        for(User user : users){            System.out.println(user);        }    }

The executed    SQL    statement output in the console is as follows:

We did not add     %     to the configuration file as the condition of the fuzzy query, so when the string argument is passed in, the standard of the fuzzy query needs to be given.        Know     %     . The     #{username}     in the configuration file is only a placeholder, so the     SQL     statement is displayed as "?".

2. Another configuration method of fuzzy query

<!-- 根据名称模糊查询 -->    <select id="findByName" resultType="cn.oesoft.mybatis01.domain.User" parameterType="String">        select *        from user        where username like '%${value}%'    </select>

We changed the original      #{}      placeholder      above to ${value}      . If      parameterType      transmits a single simple type value,      only value can be enclosed in ${}      brackets      .                 Modify the test method, remove the% in the parameter

@Test    public void testFindByName(){        //5.执行查询一个方法        List<User> users = userDao.findByName("王");        for(User user : users){            System.out.println(user);        }    }

The executed SQL statement output in the console is as follows:

The above is like this

It can be found that we don’t need to add the fuzzy query matching symbol       %       in the program code. The effect of these two methods is the same, but the execution            The statement is not the same.

3. The difference between #{} and ${}       #{}      indicates a placeholder            Through       #{},       you can       set the value of preparedStatement       to the placeholder and automatically       convert between java       type and       jdbc       type.            #{}       can effectively prevent       sql       injection.       #{}       Can receive simple type values ​​or       pojo       attribute values. If       parameterType       transmits a single simple class            Type value,       #{}       brackets can be       value       or other names.            ${}      means splicing      sql      string            Through       ${}       , the content passed in parameterType can be       spliced ​​in sql without jdbc type conversion, ${} can receive simplified                                                      Single type value or       pojo       attribute value, if       parameterType       transmits a single simple type value, the       ${}       brackets can only be       value       .

4. ${value} source code analysis of fuzzy query

Let's take a look at the source code of the TextSqlNode class:

There is a problem with the dark horse's explanation: this means that the name of the read key specified in the source code is "value" , so we can only call the name of the value when binding parameters            Up.

Six, query using aggregate functions

/*** 查询总记录条数* @return*/int findTotal();
<!-- 查询总记录条数 -->    <select id="findTotal" resultType="int">        select count(*)        from user;    </select>
@Testpublic void testFindTotal() throws Exception {//6.执行操作int res = userDao.findTotal();System.out.println(res);}

Seven, the comparison of Mybatis and JDBC programming        1.        Frequent database link creation and release cause waste of system resources and thus affect system performance. This problem can be solved if the database link pool is used.              solve:                      Configure the data link pool in SqlMapConfig.xml        , and use the connection pool to manage database links.                2. Sql        statement written in the code makes the code difficult to maintain, and the actual application of        sql        may change greatly, and the        change of sql        needs to change the        java        code.              solve:                      Separate the Sql        statement configuration        from the java code in the XXXXmapper.xml        file        .                       3. It is        troublesome to        pass parameters to the sql        statement, because        the where condition of the sql        statement is        not necessarily, it may be more or less, and the placeholder needs to correspond to the parameter.                     solve:               Mybatis        automatically        maps java        objects to        sql        statements, and        defines the input parameters through the parameterType in the statement                                   Types of.                 4.        It is troublesome        to parse the result set. The sql        change causes the parsing code to change, and it needs to be traversed before parsing. If the database records can be encapsulated into        pojo        pairs              Image analysis is more convenient.               solve:               Mybatis        automatically        maps the sql        execution result to the        java        object, and        defines the output result through the resultType in the statement                                   Types of.