MyBatis Study--simple additions and deletions

• JDBC Program

　　When learning MyBatis, let's take a simple look at how JDBC programming works, using JDBC programming as an example:

 1 public static void main (string[] args) {2 Connection Connection = null; 3 PreparedStatement PR Eparedstatement = null; 4 ResultSet ResultSet = null;                  5 6 try {7//Load Database Drive 8 class.forname ("Com.mysql.jdbc.Driver"); 9 10//Get database links by driver management class one by one connection = Drivermanager.getconnection ("Jdbc:mys Ql://localhost:3306/shop?characterencoding=utf-8 "," root "," MySQL "); 12//define SQL statements? denotes placeholder Stri ng sql = "SELECT * from user where username =?";                 14//Get preprocessing statement15 preparedstatement = connection.preparestatement (sql); 16                 Set the parameter, the first parameter is the ordinal of the parameter in the SQL statement (starting at 1), the second parameter is the set parameter value of preparedstatement.setstring (1, "Harry"); 18                 Issue SQL execution query to database, query result set ResultSet = Preparedstatement.executequery (); 20//Traverse query result set 21 while (Resultset.nexT ()) {System.out.println (resultset.getstring ("id") + "" +resultset.getstring ("username"); 23                 }24} catch (Exception e) {e.printstacktrace ();}finally{27 Release resource (resultset!=null) {try {Resultset.close                         (); "Catch (SQLException e) {+//TODO auto-generated catch block33 E.printstacktrace ();}35}36 if (preparedstatement! =null) {PNs try {preparedstatement.close (); Lexception e) {//TODO auto-generated catch block41 e.printstacktrace ()                         ;}43}44 if (connection!=null) {try {46 ConnecTion.close (); SQLException catch (E) {//TODO auto-generated catch bloc         K49 E.printstacktrace (); 50}51}52 53}54 55 }

As you can see from the above, JDBC programming generally takes the following steps:

1. Load Database Driver

2. Create and obtain a database connection

3. Creating a JDBC Statement object

4. Set the SQL statement

5. Set parameters in SQL statement (using PreparedStatement)

6. Execute SQL via statement and get results

7. Parse the results of SQL execution

8. Releasing resources (ResultSet, PreparedStatement, connection)

You can see that all JDBC programming has many of the same steps, such as loading drivers, getting connections, and so on. There are also a number of different places, such as SQL statements, parameters, query results, and so on. JDBC Programming primarily has the following issues:

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.

2, SQL statements in code hard-coded, resulting in code is not easy to maintain, the actual application of SQL changes may be large, SQL changes need to change the Java code.

3, the use of PreparedStatement to the possession of the symbol parameters are hard-coded, because the SQL statement where the condition is not necessarily, may be more or less, modify the SQL to modify the code, the system is not easy to maintain.

4, the result set parsing has hard coding (query column name), SQL changes lead to parsing code changes, the system is not easy to maintain, if the database records can be encapsulated into Pojo object resolution is more convenient.

MyBatis is a framework that encapsulates the above steps in template mode, implements the same parts, configures different parts in the configuration file, and supports mapping of parameters and query conditions, result sets, and Java object mappings to simplify programming.

• MyBatis

　　MyBatis is an Apache open source project Ibatis, 2010 This project by the Apache Software Foundation migrated to Google Code, and renamed to MyBatis, In essence mybatis some improvements to ibatis.

MyBatis is an excellent persistence layer framework that encapsulates the process of working with databases in JDBC, allowing developers to focus on the SQL itself without having to expend effort to process such as registering drivers, creating connection, creating statement, setting parameters manually, Result set retrieval and other JDBC complex process code.

MyBatis the various statement (statement, PREPAREDSTATEMNT, CallableStatement) that will be executed by XML or annotations, The resulting SQL statements are generated by mapping Java objects and SQL in statement, and finally the SQL is executed by the MyBatis framework and the results are mapped to Java objects and returned.

The composition of the MyBatis frame is as follows:

1. MyBatis Configuration

Sqlmapconfig.xml, this file is configured as a global configuration file for the MyBatis, with information such as the MyBatis runtime environment.

The Mapper.xml file is a SQL mapping file that is configured with SQL statements that manipulate the database. This file needs to be loaded in Sqlmapconfig.xml.

2, through the MyBatis environment and other configuration information constructs Sqlsessionfactory namely the session factory

3, the session factory creates sqlsession that is the session, the operation of the database needs to be done through sqlsession.

4, MyBatis bottom Custom Executor Actuator interface operation database, executor interface has two implementations, one is the basic actuator, one is the cache executor.

5, Mapped statement is also mybatis an underlying package object, it wraps the MyBatis configuration information and SQL mapping information and so on. One SQL in the Mapper.xml file corresponds to a mapped statement object, and the SQL ID is the ID of the mapped statement.

6. Mapped statement defines SQL execution input parameters, including HashMap, basic type, pojo,executor through Mapped statement to map the input Java objects into SQL before executing SQL. The input parameter mapping is the parameter set for PreparedStatement in JDBC programming.

7. Mapped statement defines the output of SQL execution, including HashMap, basic type, pojo,executor through Mapped statement to map output results to Java objects after executing SQL. The output mapping process is equivalent to the parsing process of the results in JDBC programming.

• MyBatis instances

　　Application of MyBatis to achieve simple additions and deletions, the following are implemented:

The object classes are as follows:

View Code

The Sqlmapconfig.xml configuration is as follows:

View Code

1, User ID query a user information

The mapping file is as follows:

1 <mapper namespace= "user" >2     <select id= "Finduserbyid" parametertype= "int" resulttype= " Com.luchao.mybatis.first.po.User ">3         select * from User where id = #{id}4     </select>5 </mapper>

Note: namespace: A namespace that categorizes SQL for the isolation of SQL statements.

ID: Identifies the statement with namespace.

ParameterType: Defines the type of mapping entered into SQL, #{id} means using PreparedStatement to set placeholder symbols and pass input variable IDs to SQL.

Resulttype: Defines the result mapping type.

Code:

 1 @Before 2 public void Createsqlsessionfactory () throws ioexception{3//configuration file 4 String resource = "S Qlmapconfig.xml "; 5 InputStream InputStream = Resources.getresourceasstream (Resource); 6//Use Sqlsessionfactorybuilder to load sqlsessionfactory from an XML configuration file 7 Sqlsessionfactory = new Sqlsessionfactorybuil Der (). Build (InputStream); 8} 9//query users by ID @Test11 public void finduserbyidtest () {12//DB Session Instance sqlsession Sessio             n = null;14 try {15//Create DB Session instance SqlSession16 session = Sqlsessionfactory.opensession (); 17 Querying individual records, querying user information according to User ID ("User.finduserbyid", "Session.selectone") Tem.out.println (user), a catch (Exception e) {//TODO auto-generated catch block22 E.             Printstacktrace (); finally{24 if (session!=null) {session.close (); 26 }27}28}

2, according to the user name fuzzy query user information

Map file:

1 <select id= "Finduserbyname" parametertype= "java.lang.String" resulttype= "Com.luchao.mybatis.first.po.User" >2         SELECT * from user where username like '%${value}% ' 3 </select>

#{} represents a placeholder symbol that can be implemented by #{} to set values in the placeholder PreparedStatement, automate Java type and JDBC type conversions, and #{} 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 can be in parentheses.

Code:

1//Based on user name fuzzy query user 2     @Test 3 public     void Finduserbynametest () {4             //DB Session Instance 5             sqlsession session = NULL; 6
   try {7                 //Create a DB session instance sqlsession 8                 session = Sqlsessionfactory.opensession (); 9                 //Query multiple records, fuzzy query user information according to user's name 10                 list<user> userlist = session.selectlist ("User.finduserbyname", "Zhang");                 System.out.println (Userlist.size ());             catch (Exception e) {                 //TODO auto-generated catch block14                 E.printstacktrace ();             finally{16                 if (session!=null) {                     session.close ();                 }19             }20}

SelectOne queries a record and throws an exception if you query multiple records using SelectOne:

1 org.apache.ibatis.exceptions.TooManyResultsException:Expected One result (or null) to being returned by SelectOne (), but F Ound:32 at Org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne (defaultsqlsession.java:70)

SelectList can query one or more records.

3. Add Users

Map file:

1 <insert id= "Insertuser" parametertype= "Com.luchao.mybatis.first.po.User" >2 <selectkey keyproperty=         " ID "order=" after "resulttype=" Java.lang.Integer ">3             Select last_insert_id () 4         </selectkey>5         Insert into User (username,birthday,sex,address) value (#{username},#{birthday},#{sex},#{address}); 6     </ Insert>

Note here that if MySQL is the self-increment primary key, the mapping file is as follows:

1 Insert id= "Insertuser" parametertype= "Cn.itcast.mybatis.po.User" >2         <!--Selectkey return the primary key and need to return to-->3         <selectkey keyproperty= "id" order= "after" resulttype= "Java.lang.Integer" >4             Select last_insert_id () 5         </selectkey>6        INSERT INTO User (username,birthday,sex,address) 7         values (#{username},#{birthday} , #{sex},#{address}); 8 </insert>

Adding the Selectkey implementation returns the primary key

Keyproperty: Which property of the returned primary key is stored in Pojo

The execution order of the Order:selectkey is relative to the INSERT statement, since the MySQL self-increment principle executes the INSERT statement before the primary key is generated, so the order of execution for Selectkey here is after

Resulttype: What type of primary key is returned

LAST_INSERT_ID (): is a MySQL function that returns a new record ID value for the auto_increment self-increment column.

MySQL is the UUID implementation, the mapping file is as follows:

1 <insert  id= "Insertuser" parametertype= "Cn.luchao.mybatis.po.User" >2 <selectkey resulttype= " Java.lang.String "Order=" before "3 keyproperty=" id ">4 Select UUID () 5 </selectkey>6 INSERT into user (ID, username,birthday,sex,address) 7          values (#{id},#{username},#{birthday},#{sex},#{address}) 8 </insert>9 Note that the order used here is "before"

Oracle uses a sequence implementation, and the mapping file is as follows:

1 <insert  id= "Insertuser" parametertype= "Cn.luchao.mybatis.po.User" >2 <selectkey resulttype= " Java.lang.Integer "Order=" before "3 keyproperty=" id ">4 Select Custom sequence. Nextval from DUAL5 </selectkey>6 insert into User (id,username,birthday,sex,address) 7          values (#{id},#{ Username},#{birthday},#{sex},#{address}) 8 </insert>9 Note that the order used here is "before"

Code:

 1//Insert User 2 @Test 3 public void Insertusertest () {4//DB Session Instance 5 Sqlse Ssion session = NULL; 6 try {7//Create DB Session instance sqlsession 8 session = Sqlsessionfactory.ope Nsession (); 9//Add user information: users = new user (); user.setaddress ("Shanghai"); 1 2 User.setbirthday (New Date ()); User.setsex ("1"); User.set                     Username ("Wangxiao"), Session.insert ("Insertuser", user); 16//COMMIT TRANSACTION 17                     Session.commit (); catch (Exception e) {//TODO auto-generated catch Block20                         E.printstacktrace (); finally{22} if (session!=null) {23 Session.close ();}25}26} 

4. Delete users

Map file:

1 <delete id= "deleteuser" parametertype= "int" >2         delete from user where id=#{id}3     </delete>

Code:

1//delete User 2                 @Test 3 public                 void Deleteusertest () {4                         //DB Session Instance 5                         sqlsession session = NULL; 6                         try {7
   //creating a DB Session Instance sqlsession 8                             session = Sqlsessionfactory.opensession (); 9                             session.delete ("DeleteUser", 30); 10                             //COMMIT TRANSACTION One by one                             session.commit ();                         Exception catch (E) {                             //TODO auto-generated catch block14                             E.printstacktrace ();                         finally{16                             if (session!=null) {                                 session.close ();                             }19                         }20                 }

5, modify the User:

Map file:

1 <update id= "UpdateUser" parametertype= "Com.luchao.mybatis.first.po.User" >2         update User set username=#{ Username},birthday=#{birthday},sex=#{sex},address=#{address}3         where id=#{id}4     </update>

Code:

 1//Modify user 2 @Test 3 public void Updteausertest () {4//DB Session Instance 5 sqlsession session = NULL;  6 try {7//Create DB Session instance Sqlsession 8 session = Sqlsessionfactory.opensession ();                             9//Modify user information for users user = new user (); 11 User.setid, User.setaddress ("Shanghai"), User.setbirthday (New D                             Ate ()); User.setsex ("0"); User.setusername ("Wang Xiao 21"); 16                             Session.update ("UpdateUser", user); 17//COMMIT TRANSACTION 18 Session.commit (); catch (Exception e) {//TODO auto-generated Catch Block21 E.printstacktrace ()FINALLY{23} if (session!=null) {24 Session.close (); 25}26}27}

• MyBatis 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: Detach The SQL statement configuration file from the Java code.

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.

• Comparison of MyBatis and Habernate

　　Unlike Hibernate, MyBatis is not exactly an ORM framework, because MyBatis requires programmers to write their own SQL statements, but MyBatis can flexibly configure the SQL statements to run with XML or annotations. The Java object and the SQL statement map are generated to the final executed SQL, and the result of SQL execution is then mapped to the Java object.

MyBatis learning threshold is low, easy to learn, the programmer directly write the original ecological SQL, can strictly control the performance of SQL execution, flexibility, is very suitable for the relational data model requirements of software development, such as Internet software, enterprise operations software, etc., because such software requirements change frequently, But demand changes require a rapid output. However, the premise of flexibility is that MyBatis can not do database independence, if you need to implement software that supports a variety of databases, you need to customize multiple sets of SQL mapping files, a lot of work. Focus is SQL itself, the programmer needs to write their own SQL statements, SQL modification, optimization is more convenient. MyBatis is an incomplete ORM framework that can be simply understood as SQL Mapper, although the programmer can also implement mappings (input mappings, output mappings) by writing Sql,mybatis himself. Application scenario: Suitable for projects with more changes in demand, such as: Internet projects.

Hibernate object/Relational mapping is strong, database independence is good, for the relational model of high-demand software (such as the need for fixed custom software) if Hibernate development can save a lot of code, improve efficiency. But hibernate's learning threshold is high, the threshold is higher, and how to design the O/R mapping, how to trade off between performance and object models, and how to use hibernate with great experience and ability. is a standard ORM framework (Object Relational mapping). Entry threshold is higher, do not need to write Sql,sql statement automatically generated, the SQL statement optimization, modification is more difficult. Application scenario: Suitable for small and medium-sized projects with little change in demand, such as: Backstage management system, ERP, ORM, OA.

MyBatis Study--simple additions and deletions