Mybatis learning notes (2): add, delete, modify, and query databases; mybatis learning notes

Mybatis learning notes (2): add, delete, modify, and query databases; mybatis learning notes
Development Environment Construction

Set up the development environment of mybatis. Select eclipse j2ee, mysql 5.1, jdk 1.7, and mybatis3.2.0.jar. These software tools can be downloaded from their respective official websites.
First, create a dynamic web project named MyBaits.
1. maven projects can be created, dependent package mybatis-3.2.0-SNAPSHOT.jar, mysql-connector-java-5.1.22-bin.jar

<! -- Mybatis package --> <dependency> <groupId> org. mybatis </groupId> <artifactId> mybatis </artifactId> <version> 3.2.0 </version> </dependency> <! -- Mysql --> <dependency> <groupId> mysql </groupId> <artifactId> mysql-connector-java </artifactId> <version> 5.1.22 </version> </dependency>

2. The constructed project structure

Add, delete, modify, and query data

Requirements:

• Query user information by id (primary key)

• Fuzzy query of user information based on user name

• Add User

• Update user

• Delete a user

Create a source code directory config and use the following log4j attribute file under the config directory (which can be copied from the mybatis sample program ):

Preparations before coding

Add log4j

# DEBUG is used for the log level in the development environment. The log level in the production environment is ERRORlog4j. rootLogger = DEBUG, stdout
Log4j.logger.org. mybatis. example. blogMapper = TRACE # Console output... log4j. appender. stdout = org. apache. log4j. leleappenderlog4j. appender. stdout. layout = org. apache. log4j. patternLayoutlog4j. appender. stdout. layout. conversionPattern = % 5 p [% t]-% m % n

1. Set the mybatis Configuration file: Configuration. xml, which is created in the src/main/java directory.

Note: The typeAlias here is equivalent to an alias. It is used to package a User alias for com. yihaomen. mybatis. model. user.

The content is as follows:

<? 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> <typeAliases> <typeAlias alias = "User" type = "com. yihaomen. mybatis. model. user "/> </typeAliases> <! -- After spring integration, environments configuration will be abolished --> <environments default = "development"> <environment id = "development"> <! -- Use jdbc transaction management. The transaction control includes mybatis --> <transactionManager type = "JDBC"/> <! -- Database connection pool, with mybatis Management --> <dataSource type = "POOLED"> <property name = "driver" value = "com. mysql. jdbc. driver "/> <property name =" url "value =" jdbc: mysql: // 192.168.6.1: 3306/test "/> <property name =" username "value =" dev "/> <property name =" password "value =" 123qwe "/> </dataSource> </ environment> </environments> <! -- Load the ing file --> <mappers> <mapper resource = "com/yihaomen/mybatis/model/User. xml"/> </mappers> </configuration>

 

2. Create a java class corresponding to the database and a ing file.

package com.yihaomen.mybatis.model;public class User {        private int id;    private String userName;    private int userAge;    private String userAddress;        public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getUserName() {        return userName;    }    public void setUserName(String userName) {        this.userName = userName;    }    public int getUserAge() {        return userAge;    }    public void setUserAge(int userAge) {        this.userAge = userAge;    }    public String getUserAddress() {        return userAddress;    }    public void setUserAddress(String userAddress) {        this.userAddress = userAddress;    }}

3. Create the ing file "User. xml" for the User at the same time. The SQL statement here includes adding, deleting, modifying, and querying the database.

<? Xml version = "1.0" encoding = "UTF-8"?> <! DOCTYPE mapper PUBLIC "-// mybatis.org//DTD Mapper 3.0 //" http://mybatis.org/dtd/mybatis-3-mapper.dtd "> <! -- Namespace is used to classify and manage SQL statements. Note: namespace plays a special role in mapper proxy Development --> <mapper namespace = "com. yihaomen. mybatis. dao. IUserOperation "> <! -- Configure many SQL statements in the ing file --> <! -- Id identifies the SQL ing file's SQL, called the statement id. It encapsulates the SQL statement into the mappedStatement object. Therefore, the id is called statement's idparameterType: Specifies the input type resultType: specifies the java object mapped to the SQL output result, select specifies resultType to map a single-pick record to a java object --> <select id = "selectUserByID" parameterType = "int" resultType = "User"> select * from 'user' where id = # {id} </select> <insert id = "addUser" parameterType = "User" useGeneratedKeys = "true" keyProperty = "id"> insert into user (userName, userAg E, userAddress) values (# {userName}, # {userAge}, # {userAddress }) </insert> <update id = "updateUser" parameterType = "User"> update user set userName =#{ userName}, userAge =#{ userAge }, userAddress = # {userAddress} where id = # {id} </update> <delete id = "deleteUser" parameterType = "int"> delete from user where id = # {id} </delete> <select id = "list" resultType = "User"> select * from 'user' </select> <! -- $ {} Indicates concatenating an SQL string, which is the java object type mapped to the single-pick record. Using $ {} For concatenation can easily cause SQL injection $ {value }: concatenates the content of input parameters. If the input type is simple, you can only use value --> <select id = "findUserByName" parameterType = "String" resultType = "User"> select * from 'user' where username like '% $ {value} % '</select> </mapper>

　　

The following explains the configuration files:
1. configuration. xml is used by mybatis to establish sessionFactory. It mainly contains database connection related items and aliases corresponding to java classes, such as <typeAlias alias = "User" type = "com. yihaomen. mybatis. model. user "/> this alias is very important. In the ing of specific classes, such as User. in xml, the resultType corresponds to this. To ensure consistency, the resultType here also has a separate definition method, which will be discussed later.
2. <mapper resource = "com/yihaomen/mybatis/model/User. xml"/> in Configuration. xml is an xml Configuration file containing the class to be mapped.
3. the User. xml file mainly defines various SQL statements, parameters of these statements, and types to be returned.

4. to program in interface mode and be a better method, use an interface that reasonably describes parameters and return values of SQL statements (such as IUserOperation. class), so that the code is simpler and safer now, and there are no string text and conversion errors. the detailed process is as follows:

Create the com. yihaomen. mybatis. dao package in the directory and create the interface class IUserOperation. The content is as follows:

Package com. yihaomen. mybatis. dao; import java. util. list; import com. yihaomen. mybatis. model. article; import com. yihaomen. mybatis. model. user; // note: the interface name must be the same as the namespace name in xml. 2. Each name of the interface implementation method corresponds to the id in xml. public interface IUserOperation {// query the data public User selectUserByID (int id ); // Add public void addUser (User user); // update public void updateUser (User user); // Delete public void deleteUser (int id ); // joint query public List <Article> getUserArticles (int id); // list to obtain public List <User> list (); // fuzzy query public List <User> findUserByName (String name );}

　　

5. Start the Test. Test class:

Package com. yihaomen. mybatis. ui; import java. io. reader; import java. util. list; import org. apache. ibatis. io. resources; import org. apache. ibatis. session. sqlSession; import org. apache. ibatis. session. sqlSessionFactory; import org. apache. ibatis. session. sqlSessionFactoryBuilder; import com. yihaomen. mybatis. dao. IUserOperation; import com. yihaomen. mybatis. model. user; public class Test {private static SqlSessionFac Tory sqlSessionFactory; private static Reader reader; // create a session factory and input the mybatis configuration file information static {try {// get the configuration file stream reader = Resources. getResourceAsReader ("Configuration. xml "); // create a session factory and input the configuration file information of mybatis sqlSessionFactory = new SqlSessionFactoryBuilder (). build (reader);} catch (Exception e) {e. printStackTrace () ;}}// public method, return the initialized sqlSessionFactory object public static SqlSessionFactory getSession () {return sqlSessionFa Ctory;} // query the public void select () {// obtain sqlsession SqlSession session = sqlSessionFactory through the factory. openSession (); try {// use SqlSession to operate the database // The first parameter: id of the statement in the ing file // The second parameter: specify the parameter parameterType that matches the ing file. User user = (User) session. selectOne ("com. yihaomen. mybatis. model. user. selectUserByID ", 1); System. out. println (user. getUserAddress (); System. out. println (user. getUserName ();} finally {session. close () ;}}// Add public void addUser (String address, String name) {// create user Object User user = new User (); user. setUserAddress (address); user. setUserName (name); user. setUserAge (80); // obtain SqlSession session = sqlSessionFactory through the factory. openSession (); try {IUserOperation userOperation = session. getMapper (IUserOperation. class); // Add userOperation data. addUser (user); // submit the session. commit (); // System. out. println ("The added user id is:" + user. getId ();} finally {session. close () ;}// update the public void updateUser (int id, String address) {// obtain the user, modify it, and submit it. SqlSession session = sqlSessionFactory. openSession (); try {IUserOperation userOperation = session. getMapper (IUserOperation. class); User user = userOperation. selectUserByID (id); user. setUserAddress (address); userOperation. updateUser (user); session. commit (); System. out. println ("Update successful !! ");} Finally {session. close () ;}// Delete the public void deleteUser (int id) {SqlSession session = sqlSessionFactory. openSession (); try {IUserOperation userOperation = session. getMapper (IUserOperation. class); userOperation. deleteUser (id); session. commit (); System. out. println ("delete data: id =" + id);} finally {session. close () ;}// list to obtain public void getList () {SqlSession session = sqlSessionFactory. openSession (); try {IUserOperation userOperation = session. getMapper (IUserOperation. class); List <User> us = userOperation. list (); session. commit (); // System. out. println ("generate list:" + us. size ();} finally {session. close () ;}// fuzzy query public void geFindUserByName (String name) {SqlSession session = sqlSessionFactory. openSession (); try {IUserOperation userOperation = session. getMapper (IUserOperation. class); List <User> us = userOperation. findUserByName (name); System. out. println (us. size (); session. commit ();} finally {session. close () ;}} public static void main (String [] args) {Test test = new Test (); test. getList (); test. geFindUserByName ("small"); // test. addUser ("Hangzhou", "Xiaojiang ");}}

Result log:

[20:22:33] DEBUG [main]-Logging initialized using 'class org. apache. ibatis. logging. slf4j. slf4jImpl 'adapter. [20:22:33] DEBUG [main]-PooledDataSource forcefully closed/removed all connections. [20:22:33] DEBUG [main]-PooledDataSource forcefully closed/removed all connections. [20:22:33] DEBUG [main]-PooledDataSource forcefully closed/removed all connections. [20:22:33] DEBUG [main]-PooledDataSource forcefully closed/removed all connections. [20:22:33] DEBUG [main]-Opening JDBC Connection [20:22:34] DEBUG [main]-Created connection 238157928. [20:22:34] DEBUG [main]-Setting autocommit to false on JDBC Connection [com. mysql. jdbc. JDBC4Connection @ e320068] [20:22:34] DEBUG [main]-==> Preparing: select * from 'user' where username like '% small %' [20:22:34] DEBUG [main]-=> Parameters: [20:22:34] DEBUG [main]-<= Total: 77 [20:22:34] DEBUG [main]-Resetting autocommit to true on JDBC Connection [com. mysql. jdbc. JDBC4Connection @ e320068] [20:22:34] DEBUG [main]-Closing JDBC Connection [com. mysql. jdbc. JDBC4Connection @ e320068] [20:22:34] DEBUG [main]-Returned connection 238157928 to pool.

Summary

• ParameterType specifies the type of the input parameter in the parameing file through parameterType.

• ResultType specifies the type of the output result through resultType in the ing file.

• #{}And $ {}

#{}Represents a placeholder,#{}Receives input parameters. The type can be simple, pojo, or hashmap. If you receive a simple type, you can enter value or another name in.
#{}Receive the value of a pojo object, read the property value of the object through OGNL, and obtain the property value of the object using the property. Property. Property... method.
$ {} Indicates a splicing symbol, which references SQL injection. Therefore, $ {} is not recommended {}.
$ {} Receives input parameters. The type can be simple, pojo, or hashmap. If you receive a simple type, $ {} can only be written as value.
$ {} Receives the value of a pojo object, reads the property value from the object through OGNL, and obtains the property value of the object through properties.

• SelectOne and selectList

  • SelectOne indicates that a record is queried for ing. If you use selectOne, you can use selectList or (The list contains only one object ).

  • SelectList indicates that a list (multiple records) is queried for ing. If you use selectList to query multiple records, you cannot use selectOne.

If selectOne is used, the following error occurs:

org.apache.ibatis.exceptions.TooManyResultsException: Expected oneresult (or null) to be returned by selectOne(), but found: 4

Reference

1. mybatis Study Notes (1) -- basic crud operation: https://segmentfault.com/a/1190000003771372

2, mybatis learning notes entry: http://www.cnblogs.com/hellokitty1/p/5216025.html

3, mybatis practical Tutorial: http://blog.csdn.net/techbirds_bao/article/details/9233599/

4, a deep understanding of the principle of mybatis: http://blog.csdn.net/column/details/mybatis-principle.html? Page = 1