MyBatis (2) Increase and deletion check

All contents of this study : mybatislearning

Check:query user information according to ID, get a user informationadd code in the User.xml file:

<mapper namespace= "Test" >    <!--Configure a lot of SQL statements--          <!--query Labels--     <!-- The ID is used to uniquely identify this SQL query     -<!--#{} identity Placeholder-     <!--parametertype: Specifies the type of input parameter:--     <!--#{id}: Accept the input parameter, the ID represents the input parameter, the parameter name is the ID, if the input parameter is a simple type, the argument in #{} can be any character-     <!--resulttype: The type of the output result of the Java type Object that is mapped. The heads-up record is mapped to a Java object--          <select id= "Finduserbyid" parametertype= "int" resulttype= "com. MrChengs.po.User ">           select * from User where id=#{id}     </select></mapper> 

In the test code, Test.java:

Query user information by ID, get a user information      @Test public     void Finduserbyid () throws ioexception{                      //configuration file           String Resource = "Sqlmapconfig.xml";           Get configuration file stream           inputstream inputstream = resources.getresourceasstream (resource);                      Create a session project           sqlsessionfactory sessionfactory = new Sqlsessionfactorybuilder (). Build (InputStream);                By engineering get session           sqlsession session = sessionfactory.opensession ();//through sqlsession operation database// Session.selectone (arg0, arg1)///FIRST parameter: ID of the Map file statement, equals Namespace.statement//second parameter: refers to the type parameter of Parametype in the map file User user = Session.selectone ("Test.finduserbyid", ten); SYSTEM.OUT.PRINTLN (user); Close Resource session.close ();}        

The code here is similar to JDBC using try{}finally{} and is temporarily unused! Results of the console:

DEBUG [main]- Opening JDBC connectiondebug [main]-Created connection 733957003. DEBUG [main]-Setting autocommit to False on JDBC Connection [[Email protected]]debug [main]-==>  Preparing: SELECT * from user where id=? Debug [main]-==> parameters:10(Integer) debug [main]-<==      total:1User [id=10, Username= Zhang San, birth Day=thu Jul 00:00:00 CST, sex=1, address=  Beijing]DEBUG [main]-resetting autocommit to true on JDBC Connection [[Email protected]]debug [main]- Closing JDBC Connection [[Email protected]]debug [main]-Returned Connection 733957003 to pool.       

Fuzzy query user information according to user name:add code in the User,xml file:

     <!--fuzzy query     -<!--resulttype: Specifies the type of the heads-up record mapped--     <select id= "Findbyname" parametertype= " java.lang.String "resulttype=" com. MrChengs.po.User ">           SELECT * from User where username like #{username}     </select> 

The rest does not change to the test codein the test code, Test.java:

Fuzzy query based on user name      @Testpublic void Finduserbyname () throws ioexception{                      //configuration file           String resource = " Sqlmapconfig.xml ";           Get configuration file stream           inputstream inputstream = resources.getresourceasstream (resource);                      Create a session project           sqlsessionfactory sessionfactory = new Sqlsessionfactorybuilder (). Build (InputStream);                By engineering get session           sqlsession session ="% xiaoming"); for(User user:users) { SYSTEM.OUT.PRINTLN (user); }//Close resources         

Note the red marked portion.

Think: Can we not add in the test classpercent percent? We have improved this code based on this program: in the User,xml file:

<!--fuzzy query     -<!--resulttype: Specifies the type that the heads-up record maps--          <!--${}: Represents the concatenation of SQL strings, will receive the contents of the parameters without any retouching stitching in SQL     -<!--using ${} stitching SQL, easy SQL injection--     <!--${}  accept the contents of the parameter if the incoming type is simple Type  $ {} can only use value--     <select id= "Findbyname" parametertype= "java.lang.String" resulttype= "com. MrChengs.po.User "> '           %${value}% '     </select>

When testing here, '%${value}% ', the rest of the characters get no results,Use only valuein the test code Test.java:

Fuzzy query based on user name      @Testpublic void Finduserbyname () throws ioexception{                      //configuration file           String resource = " Sqlmapconfig.xml ";           Get configuration file stream           inputstream inputstream = resources.getresourceasstream (resource);                      Create a session project           sqlsessionfactory sessionfactory = new Sqlsessionfactorybuilder (). Build (InputStream);                By engineering get session           sqlsession session ="xiaoming"); for(User user:users) {System.out.println ( user); }

At this moment, no % is used

Results:

DEBUG [main]- Opening JDBC connectiondebug [main]-Created connection 213193302. debug [main]-Setting autocommit to False on JDBC Connection [[email protected]]debug [main]-==>  Prepari Ng:select * from user where username like '% xiaoming 'DEBUG [main]-==> parameters:debug [main]-<==      T Otal:3User [id=16, Username= Zhang Xiaoming, birthday=null, Sex=1, address= Henan Zhengzhou]user [id=22, Username= Chen Xiaoming, birthday=null, Sex=1, address= Henan Zhengzhou]user [id=25, Username= Chen Xiaoming, birthday=null, Sex=1, address= Henan Zhengzhou]debug [main]-resetting Autocommit to True on JDBC Connection [[Email protected]]debug [main]- Closing JDBC Connection [email protecte D]]debug [main]-returned connection 213193302 to pool.          

at this time is a direct spell, no placeholder, the use of this method, easy to be injected! Query Summary:1.parameterType: Specifies the input parameter type, MyBatis gets the parameter value from the input object by OGNL stitching in SQL. Resulttype: Specifies the output result type, MyBatis maps a row of record data for the SQL query results to resulttype objects of the specified type. 2.selectOne queries a record and throws an exception if you query multiple records using SelectOne: SelectList can query one or more records. 3.#{} & ${} #{} represents a placeholder that can be implemented by #{} to set values in placeholders, automate Java types and JDBC type conversions, and PreparedStatement} can effectively prevent SQL injection. #{} can receive either a simple type value or a Pojo property value. If ParameterType transmits a single simple type value, #{} can be a value or other name in parentheses. ${} is a concatenation of SQL strings, through ${} can be parametertype incoming content splicing in SQL and no JDBC type conversion, ${} can receive simple type values or Pojo property values, if ParameterType transport a single simple type value, ${} Only value is in parentheses----------------------------------------------------Gorgeous split-line---------------------------------------------------- Add Users:in the User,xml file:The primary key ID is self-increment and the primary key can also be omitted at this time

    <!--Add users-     <!--parametertype: Specifies that the input parameter type is Pojo (including user information)-     <!--#{} to specify the property name of the Pojo. The property value of the Pojo object received, MyBatis gets the property value of the object by Ognl-     <insert id= "addUser" parametertype= "com. MrChengs.po.User ">           INSERT INTO User (id,username,birthday,sex,address)            value (#{id},#{username},#{ Birthday},#{sex},#{address})     </insert>

In the test class Test.java:

Add user      @Testpublic void AddUser () throws ioexception{           String resource = "Sqlmapconfig.xml";           InputStream InputStream = resources.getresourceasstream (resource);           Sqlsessionfactory sessionfactory = new Sqlsessionfactorybuilder (). Build (InputStream);           sqlsession session = sessionfactory.opensession ();///Added user information users = new user (); User.setusername ("Mrcheng "); User.setsex (1); User.setbirthday (new Date ()); User.setaddress ("Beijing"); Session.insert ("Test.adduser" , user); Need to submit things session.commit ();//Close Session Session.close ();}          

View results:

DEBUG [main]- Opening JDBC connectiondebug [main]-Created connection 1384722895. debug [main]-Setting autocommit to False on JDBC Connection [[email protected]]debug [main]-==>  Prepari Ng:insert into User (id,username,birthday,sex,address) value (?,?,?,?,?  ) DEBUG [main]-==> parameters:0 (Integer), Mrcheng (String), 2018-10-05 13:09:06.928 (Timestamp), 1  (Integer), Beijing (String)DEBUG [main]-<==    Updates:1DEBUG [main]- committing JDBC Connection [[Email protected]]debug [main]-resetting autocommit to TR UE on JDBC Connection [[Email protected]]debug [main]- Closing JDBC Connection [[Email protected]]debug [main] -Returned connection 1384722895 to pool.        

Let's say we get the primary key value at the same time we add the user:

Self-increment primary key return: MySQL auto-increment primary key, perform insert commit before automatically generate a primary key by MySQL function get self-increment primary key to modify the previous code in the User.xml file:

LAST_INSERT_ID (): Is a function of MySQL, return auto_increment self-increment new record ID value

     <!--Add Users-     <!--parametertype: Specifies that the input parameter type is Pojo (including user information)-     <!--#{} to specify the property name of the Pojo. The property value of the Pojo object received, MyBatis gets the property value of the object by OGNL-     <insert id= "addUser" parametertype= "com. MrChengs.po.User ">                <!--returns the inserted data primary key back to the User object--           <!--SELECT last_insert_id () : Get the primary key value of insert insert--           <!--Keyproperty: Set the primary key you are querying to parametertype the properties of the specified object--           <!--order: execution order, Relative           to insert-<!--resulttype: Specifies the type of result--           <selectkey keyproperty= "id" order= " After " resulttype=" Java.lang.Integer ">                Select last_insert_id ()           </selectKey>                 INSERT INTO User (id,username,birthday,sex,address)            value (#{id},#{username},#{birthday},#{sex},#{ Address})     </insert> 

In the test class Test.java:

@Testpublic void AddUser () throws ioexception{           String resource = "Sqlmapconfig.xml";           InputStream InputStream = resources.getresourceasstream (resource);           Sqlsessionfactory sessionfactory = new Sqlsessionfactorybuilder (). Build (InputStream);           sqlsession session = sessionfactory.opensession (); User user = new user (), User.setusername ("Mrcheng"), User.setsex (1); User.setbirthday (new Date ()); User.setaddress ("Beijing"); Session.insert ("Test.adduser"  System.out.println (User.getid ()); // Need to submit things session.commit ();//Close Session Session.close ();}          

Results:

DEBUG [main]-Opening JDBC Connectiondebug [main]-Created connection 22429093. DEBUG [main]-Setting autocommit to False on JDBC Connection [[Email protected]]debug [main]-==> preparing:ins ert into User (id,username,birthday,sex,address) value (?,?,?,?,?) DEBUG [main]-==> parameters:0 (integer), Mrcheng (String), 2018-10-05 14:11:48.735 (Timestamp), 1(integer), Beijing ( String) Debug [main]-<== updates:1Debug [main]-==> preparing:select last_insert_id () debug [main]-= = = Parameters:debug [main]-<==total:1DEBUG [main]- committing JDBC Connection [email Protected]]debug [main]-resetting autocommit to True on JDBC Connection [[Email protected]]debug [main]- Clos ing JDBC Connection [[Email protected]]debug [main]-returned Connection 22429093 to pool.     

Non-self-increment primary key return: using the MySQL uuid () function to generate gradual, need to modify the table ID string, length set to 35 bits

Need to increase the UUID value by the Select UUID () <insert  id= "Insertuser" parametertype= "Cn.itcast.mybatis.po.User" >< Selectkey resulttype= "java.lang.String" order= "before" keyproperty= "id" >select uuid () </selectkey>insert into user (id,username,birthday,sex,address) VALUES (#{id},#{username},#{birthday},#{sex},#{address}) </insert > Note that the order used here is "before"

　　

----------------------------------------------------Gorgeous split-line----------------------------------------------------

To delete a user:

In the mapping file: User.xml file:

<!--Delete users-     <!--by ID--     <delete id= "DeleteUser" parametertype= "Java.lang.Integer" >            Delete from user where id=#{id}     </delete> 

Test class:

    Delete User     @Test public     void DeleteUser () throws ioexception{                String resource = "Sqlmapconfig.xml" ;                InputStream InputStream = resources.getresourceasstream (resource);                Sqlsessionfactory sessionfactory = new Sqlsessionfactorybuilder (). Build (InputStream);                sqlsession session = sessionfactory.opensession (); Session.delete ("Test.deleteuser");//need to submit things  Session.commit (); Close Session Session.close ();}        

Console:

DEBUG [main]- Opening JDBC connectiondebug [main]-Created connection 22429093. debug [main]-Setting autocommit to False on JDBC Connection [[email protected]]debug [main]-==>  Prepari Ng:delete from user where id=?  Debug [main]-==> parameters:28(Integer) debug [main]-<==    updates:1Debug [main]- committing JDBC Connection [[Email protected]]debug [main]-resetting autocommit to true on jdbc Connection [[Email protected] ]debug [main]- Closing JDBC Connection [[Email protected]]debug [main]-returned Connection 22429093 to pool. 

----------------------------------------------------Gorgeous split-line----------------------------------------------------

Update data:

User.xml

    <!--update users     -<!--update users by ID-     <!--parametertype: Specify the user object, including the ID update information, and Note that the ID must be present -     <update id= "updateUser" parametertype= "com. MrChengs.po.User ">           update User set username=#{username},sex=#{sex} where id = #{id}          </update > 

Test class:

Update @Test public void UpdateUser () throws  ioexception{String resource = "SQLM                Apconfig.xml ";                InputStream InputStream =  resources.getresourceasstream (Resource);                Sqlsessionfactory sessionfactory = new  Sqlsessionfactorybuilder (). Build (InputStream); sqlsession session =  sessionfactory.opensession (); User user = new  user (); User.setusername ("mrchegns" ); User.setsex (2 );    User.setid (    +         ) ;    Session.update ("Test.updateuser" , user);//need to submit things  session.commit ();//Close Session  Session.close ();  

NOTE: The ID must be set on. Console:

DEBUG [main]- Opening JDBC connectiondebug [main]-Created connection 22429093. DEBUG [main]-Setting autocommit to False on JDBC Connection [[Email protected]]debug [main]-==>  Preparing: Update user set username=?,sex=? WHERE id =? Debug [main]-==> parameters:mrchegns (String), 2 (integer),(integer) debug [main]-<==    updates:1
           

M Ybatis solving problems with JDBC programming

1, the database link creation, the release frequently causes the system resources to waste thus affects the system performance, if uses the database link pool solves this problem.

Workaround: Configure the data link pool in Sqlmapconfig.xml and use connection pooling to manage database links.

2, SQL statements written in the code cause code is not easy to maintain, the actual application of SQL changes may be large, SQL changes need to change the Java code.

WORKAROUND: Configure the SQL statement to be detached from the Java code in the Xxxxmapper.xml file.

3. It is troublesome to pass arguments to SQL statements because the WHERE condition of the SQL statement is not necessarily, possibly more or less, and the placeholder needs to correspond to parameter one by one.

Resolution: MyBatis Automatically maps Java objects to SQL statements, defining the type of input parameters through ParameterType in statement.

4, the result set parsing trouble, SQL changes lead to parsing code changes, and need to traverse before parsing, if the database records can be encapsulated into Pojo object parsing is more convenient.

Resolution: MyBatis automatically maps SQL execution results to Java objects, defining the type of output results through Resulttype in statement.

When you see this, the Basic starter program has been explained!!!

MyBatis (2) Increase and deletion check