2.MyBatis Database Single-table additions and deletions to check and change

Turn from: http://www.cnblogs.com/xdp-gacl/p/4262895.html First, use MyBatis to perform CRUD operations on the table--based onimplementation of XML

1. Define SQL mapping XML file

The contents of the Usermapper.xml file are as follows:

 1 <?xml version= "1.0" encoding= "UTF-8"?> 2 <! DOCTYPE Mapper Public "-//mybatis.org//dtd mapper 3.0//en" "Http://mybatis.org/dtd/mybatis-3-mapper.dtd" > 3 <!-- Specifying a unique Namespace,namespace value for this mapper is customarily set to the package name +sql the map file name, so that the value of namespace is guaranteed to be unique 4 for example namespace= " Me.gacl.mapping.userMapper "is me.gacl.mapping (package name) +usermapper (usermapper.xml file removal suffix) 5--6 <mapper namespace=" Me.gacl.mapping.userMapper > 7 <!--The SQL statement that writes the query in the Select tab, set the ID property of the Select tag to Getuser,id property value must be unique and cannot repeat 8 using para The Metertype property indicates the type of parameter that is used when querying, and the Resulttype property indicates that the result set type returned by the query is 9 resulttype= "Me.gacl.domain.User" means that the object that encapsulates the query result as a user class returns ten user Class is the entity class corresponding to the users table.-->12 <!--13 Get a user object from ID query-->15 <select id= "GetUser" Parame tertype= "int" resulttype= "Me.gacl.domain.User" >17 select * from users where id=#{id}18 </sele         ct>19 <!--Creating user (Create)-->21 <insert id= "AddUser" parametertype= "Me.gacl.domain.User" >22 INsert into users (name,age) values (#{name},#{age}) </insert>24 <!--remove User (remove)-->26 &lt     ;d elete id= "deleteuser" parametertype= "int" >27 delete from users where id=#{id}28 </delete>29 30 <!--Modify User (update)-->31 <update id= "UpdateUser" parametertype= "Me.gacl.domain.User" >32 Update Users set Name=#{name},age=#{age} where id=#{id}33 </update>34 <!--query All users-->36 <select Id= "GetAllUsers" resulttype= "Me.gacl.domain.User" >37 select * from Users38 </select>39-</ma Pper>

The Unit test class code is as follows:

 1 package me.gacl.test; 2 3 Import java.util.List; 4 Import Me.gacl.domain.User; 5 Import Me.gacl.util.MyBatisUtil; 6 Import org.apache.ibatis.session.SqlSession; 7 Import Org.junit.Test; 8 9 public class Testcrudbyxmlmapper {ten @Test12 public void Testadd () {//sqlsession sqlsession = My          Batisutil.getsqlsession (false); Sqlsession sqlsession = Mybatisutil.getsqlsession (true); 15/**16 * Mapping SQL Identification String, Me.gacl.mapping.userMapper * is the value of the namespace attribute of the mapper tag in the Usermapper.xml file, and AddUser is the INS ert the id attribute value of the tag, you can find the SQL19 */20 String statement = "Me.gacl.mapping.userMapper.addUser" by the id attribute value of the insert tag; /Map SQL identification string of user user = new user (); User.setname ("User aloof Wolf"); User.setage (20); 24//execution         insert operation int retresult = Sqlsession.insert (statement,user); 26//Manually submit transaction//sqlsession.commit (); 28    You need to close SqlSession29 sqlsession.close () after executing SQL with sqlsession; 30     System.out.println (retresult)}32 @Test34 public void Testupdate () {sqlsession sqlses sion = Mybatisutil.getsqlsession (true); 36/**37 * Map SQL identification String, * Me.gacl.mapping.userMapper is US          The value of the namespace property of the mapper tag in the Ermapper.xml file, which is the id attribute value of the update tag, is found by the ID attribute value of the update tag and the SQL40 */41 to be executed.         String statement = "Me.gacl.mapping.userMapper.updateUser";//the identity string of the mapped SQL user user = new user (); 43  User.setid (3); User.setname ("Aloof pale Wolf"); User.setage (25); 46//Perform modification operation, int retresult =         Sqlsession.update (Statement,user); 48//You need to close SqlSession49 sqlsession.close () after executing SQL with sqlsession; 50  System.out.println (retresult)}52 @Test54 public void Testdelete () {sqlsession sqlsession = Mybatisutil.getsqlsession (true); 56/**57 * Mapped SQL identification string, Me.gacl.mapping.userMapper * is Userma The name of the mapper tag in the Pper.xml fileThe value of the Space property, the deleteuser is the id attribute value of the delete tag, and the value of the delete tag's ID property can be found to perform the SQL60 */61 String statement = "Me.gacl.mapping.userMapper.deleteUser";//Mapping SQL Identification string 62//Perform delete operation Retresult int = Sqlsession.delete (state ment,5); 64//Use sqlsession to close SqlSession65 sqlsession.close () after executing SQL; SYSTEM.OUT.PRINTLN (retresu LT);}68 @Test70 public void Testgetall () {sqlsession sqlsession = Mybatisutil.getsqlsessio n (); 72/**73 * Mapped SQL identification string, Me.gacl.mapping.userMapper * is the namespace attribute of mapper tag in Usermapper.xml file The value of getallusers is the id attribute value of the select tag, and the ID property value of the Select tag can be found to perform the SQL76 */77 String statement = "Me.ga Cl.mapping.userMapper.getAllUsers ";//Map SQL Identification string 78//Execute query operation, automatically encapsulate query result into list<user> return to List<user&gt ; Lstusers = sqlsession.selectlist (statement); 80//Use sqlsession to close SqlSession81 sqlsession.close after SQL Execution (); 8 2 SYSTEM.OUT.PRintln (lstusers); 83}84} 

Ii. using MyBatis to perform CRUD operations on tables-based on annotationsthe implementation

1 . Interface for defining SQL mappings

The code for the Usermapperi interface is as follows:

 1 package me.gacl.mapping; 2 3 Import java.util.List; 4 Import Me.gacl.domain.User; 5 Import Org.apache.ibatis.annotations.Delete; 6 Import Org.apache.ibatis.annotations.Insert; 7 Import Org.apache.ibatis.annotations.Select; 8 Import Org.apache.ibatis.annotations.Update; 9/**11 * @author gacl12 * Defines the interface of the SQL map, using annotations to indicate the SQL13 */14 public interface Usermapperi {15 16//using @insert annotations indicate     The Add method executes the SQL17 @Insert ("Insert into Users (name, age) VALUES (#{name}, #{age})"), and public int add (user user); 19 20//Use the @delete annotation to indicate the SQL21 @Delete to be performed by the Deletebyid method ("Delete from Users where Id=#{id}"), the public int Deletebyid (in T ID); 23 24//Use @update annotations to indicate the SQL25 @Update to be performed by the Update method ("Update users set Name=#{name},age=#{age} where Id=#{id}" ) the public int update (user user); 27 28//Use the @select annotation to indicate the SQL29 @Select to be performed by the GetByID method ("SELECT * from Users wher E id=#{id} ") Public User getById (int id); 31 32//Use @select annotations to indicate the SQL33 @Select to be performed by the GetAll method (" SELECT *From users ") list<user> getAll (); 35} 

What needs to be explained is that we do not need to write specific implementation class code for Usermapperi interface, this concrete implementation class is built by MyBatis to help us dynamically, we just need to use it directly.

2. register this mapping interface in the Conf.xml file

 1 <?xml version= "1.0" encoding= "UTF-8"?> 2 <! DOCTYPE configuration Public "-//mybatis.org//dtd Config 3.0//en" "Http://mybatis.org/dtd/mybatis-3-config.dtd" > 3             <configuration> 4 <environments default= "Development" > 5 <environment id= "Development" > 6 <transactionmanager type= "JDBC"/> 7 <!--configuration database connection Information-8 <datasource Typ E= "Pooled" > 9 <property name= "Driver" value= "Com.mysql.jdbc.Driver"/>10 <prop Erty name= "url" value= "Jdbc:mysql://localhost:3306/mybatis"/>11 <property name= "username" value= "ro OT "/>12 <property name=" password "value=" XDP "/>13 </datasource>14 &lt         ;/environment>15 </environments>16 <mappers>18 <!--registration Usermapper.xml file, 19       Usermapper.xml is located under Me.gacl.mapping This package, so resource written me/gacl/mapping/usermapper.xml-->20  <mapper resource= "Me/gacl/mapping/usermapper.xml"/>21 <!--register Usermapper map Interface-->22 <mapper class= "Me.gacl.mapping.UserMapperI"/>23 </mappers>24 </configuration>

The code for the Unit test class is as follows:

 1 package me.gacl.test; 2 3 Import java.util.List; 4 Import Me.gacl.domain.User; 5 Import Me.gacl.mapping.UserMapperI; 6 Import Me.gacl.util.MyBatisUtil; 7 Import org.apache.ibatis.session.SqlSession; 8 Import Org.junit.Test;  9 public class Testcrudbyannotationmapper {one @Test13 public void Testadd () {sqlsession sqlsession = Mybatisutil.getsqlsession (true); 15//Get Usermapperi Interface Implementation class object, Usermapperi interface implementation class object by Sqlsession.getmapper (usermapper          I.class) dynamically constructed Usermapperi mapper = Sqlsession.getmapper (usermapperi.class); + User user = new user (); 18 User.setname ("User Xdp"), User.setage (+), int add = mapper.add (user), 21//Use Sqlsession You need to close SqlSession22 sqlsession.close () After you finish SQL, SYSTEM.OUT.PRINTLN (add),}25 @Test27 PU Blic void Testupdate () {sqlsession sqlsession = Mybatisutil.getsqlsession (true); 29//Get Usermapperi Interface implementation class An Usermapperi object that implements the class object of an interface by Sqlsession.getmappER (usermapperi.class) dynamically constructed out of usermapperi mapper = Sqlsession.getmapper (usermapperi.class); User user =         New User (); User.setid (3); User.setname ("Aloof _xdp"); User.setage (26); 35//Perform modification operation 36          int retresult = mapper.update (user), 37//SqlSession38 Sqlsession.close () needs to be closed after executing SQL with sqlsession; 39 System.out.println (Retresult);}41 @Test43 public void Testdelete () {sqlsession sq Lsession = Mybatisutil.getsqlsession (true); 45//Get Usermapperi Interface Implementation class object, Usermapperi interface implementation class object by Sqlsession.getmapper (Us         Ermapperi.class) dynamically constructed Usermapperi mapper = Sqlsession.getmapper (usermapperi.class); 47//delete operation 48         int retresult = Mapper.deletebyid (7); 49//You need to close SqlSession50 sqlsession.close () after executing SQL with sqlsession; 51 System.out.println (Retresult),}53 @Test55 public void Testgetuser () {+ sqlsession Sqlse Ssion = mybatisutil.getsqLsession (); 57//Get Usermapperi Interface Implementation class object, Usermapperi interface implementation class object is dynamically built by Sqlsession.getmapper (Usermapperi.class) 58 Usermapperi mapper = Sqlsession.getmapper (usermapperi.class); 59//Perform a query operation to automatically encapsulate query results into user user = Ma Pper.getbyid (8); 61//Use sqlsession to close SqlSession62 sqlsession.close () after executing SQL; System.out.println (user);}65 @Test67 public void Testgetall () {sqlsession sqlsession = mybatisutil.getsqlses Sion (); 69//Get Usermapperi Interface Implementation class object, Usermapperi interface implementation class object is dynamically constructed by Sqlsession.getmapper (Usermapperi.class) Mapperi mapper = Sqlsession.getmapper (Usermapperi.class); 71//Perform query operation, automatically package query results into list<user> return to LIST&L T         user> lstusers = Mapper.getall (); 73//You need to close SqlSession74 sqlsession.close () after using sqlsession to execute SQL; 75 System.out.println (lstusers); 76}77}

The Mybatisutil tool class code used is as follows:

 1 package me.gacl.util; 2 3 Import Java.io.InputStream; 4 5 Import Org.apache.ibatis.session.SqlSession; 6 Import Org.apache.ibatis.session.SqlSessionFactory; 7 Import Org.apache.ibatis.session.SqlSessionFactoryBuilder;     8 9 public class Mybatisutil {10 11/**12 * Get SQLSESSIONFACTORY13 * @return SqlSessionFactory14 */15 public static Sqlsessionfactory Getsqlsessionfactory () {inputstream String resource = "Conf.xml"; is = MyBatisUtil.class.getClassLoader (). getResourceAsStream (Resource); Sqlsessionfactory factory = new Sqlsessio Nfactorybuilder (). Build (IS), return factory;20}21 22/**23 * Get SQLSESSION24 * @return SQL Session25 */26 public static sqlsession getsqlsession () {return getsqlsessionfactory (). Opensession (); 2 8}29 30/**31 * Get SQLSESSION32 * @param isautocommit * True indicates that the Sqlsession object created is executing the sq L then automatically commits the transaction. * False indicates the created SqlsesThe Sion object does not commit the transaction automatically after executing the SQL, so we need to manually call Sqlsession.commit () to commit the transaction (). * @return SqlSession36 */37 public static Sqlse  Ssion getsqlsession (Boolean isautocommit) {return getsqlsessionfactory (). Opensession (Isautocommit); 39}40}

Second, the connection database configuration is placed in a single properties file

Previously, we wrote the connection configuration information of the database directly in the MyBatis Conf.xml file, as follows:

1 <?xml version= "1.0" encoding= "UTF-8"?> 2 <! DOCTYPE configuration Public "-//mybatis.org//dtd Config 3.0//en" "Http://mybatis.org/dtd/mybatis-3-config.dtd" > 3 <configuration> 4     <environments default= "Development" > 5         <environment id= "Development" > 6             <transactionmanager type= "JDBC"/> 7             <!--configuration database connection Information-8             <datasource type= "Pooled" > 9< C5/><property name= "Driver" value= "Com.mysql.jdbc.Driver"/>10                 <property name= "url" value= "Jdbc:mysql ://localhost:3306/mybatis "/>11                 <property name=" username "value=" root "/>12                 <property name=" Password "value=" XDP "/>13             </datasource>14         </environment>15     </environments>16     </configuration>

In fact, we can completely write the connection configuration information of the database in a properties file, and then reference the properties file in the Conf.xml file, as follows:

1. Create a new db.properties file in the SRC directory, as shown in:

　　

In the Db.properties file to write the connection database required to use the database driver, the connection URL address, user name, password, as follows:

1 driver=com.mysql.jdbc.driver2 url=jdbc:mysql://localhost:3306/mybatis3 Name=root4 Password=XDP

2. Refer to the Db.properties file in the MyBatis Conf.xml file as follows:

 1 <?xml version= "1.0" encoding= "UTF-8"?> 2 <! DOCTYPE configuration Public "-//mybatis.org//dtd Config 3.0//en" "Http://mybatis.org/dtd/mybatis-3-config.dtd" > 3     <configuration> 4 5 <!--reference Db.properties profile--6 <properties resource= "Db.properties"/> 7 <!--8 Development: Development Mode 9 work: Working mode-->11 <environments default= "development" ; <environment id= "Development" >13 <transactionmanager type= "JDBC"/>14 < !--Configuring database connection Information-->15 <datasource type= "Pooled" >16 <!--Value property value reference in Db.properties configuration file  Configured value-->17 <property name= "Driver" value= "${driver}"/>18 <property name= "url" Value= "${url}"/>19 <property name= "username" value= "${name}"/>20 <property Name= "Password" value= "${password}"/>21 </datasource>22 </environment>23 </environments>24 </configuration> 

2.MyBatis to Database single table additions and deletions to check and change