1 Introduction to mybatis
MyBatis was originally an open source project ibatis of apache. In 2010, the project was migrated from apache software foundation to google code and renamed MyBatis. Moved to Github in November 2013. MyBatis is an excellent persistence layer framework. It encapsulates the process of jdbc operating the database, so that developers only need to pay attention to SQL itself, and do not need to spend energy to deal with complicated jdbc process codes such as registering drivers, creating connection s, creating statement s, manually setting parameters, result set retrieval and so on.
Mybatis configures various statements (statement, preparedStatement and CallableStatement) to be executed through xml or annotation, and maps java objects and sql in the statement to generate the final executed sql statement. Finally, the mybatis framework executes sql, maps the results into java objects and returns them.
2 summary of programming problems using jdbc
2.1 create mysql database
First, import the sql script for creating the database into the database.
2.2 create project
1. Create a java project.
2. Import the jar package. The mysql database driver is required at this time.
2.3 jdbc programming steps:
- Load database driver
- Create and get database links
- Create a jdbc statement object
- Set sql statement
- Setting parameters in sql statements (using preparedStatement)
- Execute sql through statement and get results
- Parse the sql execution results
- Release resources (resultSet, preparedstatement, connection)
2.4 jdbc program
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//Load database driver
Class.forName("com.mysql.jdbc.Driver");
//Get database link through driver management class
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8", "root", "root");
//Define sql statements? Represents a placeholder
String sql = "select * from user where username = ?";
//Get preprocessing statement
preparedStatement = connection.prepareStatement(sql);
//Set the parameter. The first parameter is the sequence number of the parameter in the sql statement (starting from 1), and the second parameter is the set parameter value
preparedStatement.setString(1, "Wang Wu");
//Send sql to the database to execute the query and query the result set
resultSet = preparedStatement.executeQuery();
//Traversal query result set
while(resultSet.next()){
System.out.println(resultSet.getString("id")+" "+resultSet.getString("username"));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
//Release resources
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(preparedStatement!=null){
try {
preparedStatement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
The above uses the original method of jdbc (not encapsulated) to query database table records.
2.5 jdbc problems are summarized as follows:
1. The frequent creation and release of database links cause a waste of system resources, which affects the system performance. If you use database link pool, you can solve this problem.
2. sql statements are hard coded in the code, which makes the code difficult to maintain. The actual application of sql may change greatly. sql changes need to change the java code.
3. There is hard coding when using preparedStatement to transfer parameters to occupancy symbols, because the where conditions of sql statements are not necessarily, there may be more or less, and the code must be modified when modifying sql, so the system is not easy to maintain.
4. There is hard coding (query column name) for result set parsing. The change of sql leads to the change of parsing code, which is difficult to maintain. It is more convenient to parse if the database records can be encapsulated into pojo objects.
3. Mybatis architecture
1. mybatis configuration
SqlMapConfig.xml, as the global configuration file of mybatis, this file configures the running environment of mybatis and other information.
mapper. The XML file is the sql mapping file, in which the sql statements for operating the database are configured. This file needs to be in sqlmapconfig Load in XML.
2. Construct SqlSessionFactory, i.e. session factory, through configuration information such as mybatis environment
3. sqlSession is created by the session factory, that is, session. Database operation needs to be carried out through sqlSession.
4. The bottom layer of mybatis customizes the operation database of the Executor interface. The Executor interface has two implementations, one is the basic Executor and the other is the cache Executor.
5. The Mapped Statement is also a low-level encapsulation object of mybatis, which encapsulates the configuration information and sql mapping information of mybatis. mapper. An sql in the XML file corresponds to a Mapped Statement object, and the id of the sql is the id of the Mapped Statement.
6. The Mapped Statement defines the input parameters of sql execution, including HashMap, basic type and pojo. The Executor maps the input java objects into sql through the Mapped Statement before executing sql. The input parameter mapping is to set the parameters of preparedStatement in jdbc programming.
7. The Mapped Statement defines the output results of sql execution, including HashMap, basic type and pojo. The Executor maps the output results to java objects after executing sql through the Mapped Statement. The output result mapping process is equivalent to the result parsing process in jdbc programming.
4 Introduction to mybatis
4.1 mybatis Download
The code of mybaits is provided by GitHub Com management, address: https://github.com/mybatis/mybatis-3/releases
mybatis-3.2.7.jar - the core package of mybatis
lib - dependency package of mybatis
mybatis-3.2.7.pdf - mybatis user manual
4.2 requirements
Realize the following functions:
Query a user information according to the user id
Fuzzy query user information list according to user name
Add user
Update user
delete user
4.3 project construction
4.3. Step 1: create a java project
4.3. Step 2: add jar package
Add mybatis core package, dependency package and data-driven package.
4.3. 3 step 3: log4j properties
Create log4j under classpath Properties are as follows:
# Global logging configuration log4j.rootLogger=DEBUG, stdout # Console output... log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
mybatis uses log4j as the output log information by default.
4.3. Step 4: sqlmapconfig xml
Create sqlmapconfig. Config under classpath XML, 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>
<!-- and spring After integration environments Configuration will be abolished-->
<environments default="development">
<environment id="development">
<!-- use jdbc transaction management-->
<transactionManager type="JDBC" />
<!-- Database connection pool-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
</configuration>
SqlMapConfig.xml is the core configuration file of mybatis. The configuration contents of the above file are data source and transaction management.
4.3. 5 step 5: po class
The Po class is used for sql mapping as mybatis. The Po class usually corresponds to the database table, user Java is as follows:
Public class User {
private int id;
private String username;// User name
private String sex;// Gender
private Date birthday;// birthday
private String address;// address
get/set......
4.3. Step 6: sql mapping file
Create the sql mapping file users. In the sqlmap directory under the classpath xml:
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="test"> </mapper>
Namespace: namespace, which is used to isolate sql statements. We will talk about another layer's very important role later.
4.3. Step 7: load the mapping file
The mybatis framework needs to load the mapping file, and the users XML is added to SqlMapConfig.xml XML, as follows:
<mappers>
<mapper resource="sqlmap/User.xml"/>
</mappers>
4.4 query user information according to id
4.4. 1 mapping file:
In user Add to XML:
<!-- according to id Get user information -->
<select id="findUserById" parameterType="int" resultType="cn.itcast.mybatis.po.User">
select * from user where id = #{id}
</select>
4.4. 2 test procedure:
public class Mybatis_first {
//Session factory
private SqlSessionFactory sqlSessionFactory;
@Before
public void createSqlSessionFactory() throws IOException {
// configuration file
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
// Use SqlSessionFactoryBuilder to create SqlSessionFactory from an xml configuration file
sqlSessionFactory = new SqlSessionFactoryBuilder()
.build(inputStream);
}
// Query user information according to id
@Test
public void testFindUserById() {
// Database session instance
SqlSession sqlSession = null;
try {
// Create database session instance sqlSession
sqlSession = sqlSessionFactory.openSession();
// Query a single record and query user information according to user id
User user = sqlSession.selectOne("test.findUserById", 10);
// Output user information
System.out.println(user);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null) {
sqlSession.close();
}
}
}
}
4.5 query user information according to user name
4.5. 1 mapping file:
In user Add to XML:
<!-- User defined criteria query user list -->
<select id="findUserByUsername" parameterType="java.lang.String"
resultType="cn.itcast.mybatis.po.User">
select * from user where username like '%${value}%'
</select>
parameterType: defines the mapping type input into sql. Value means to replace {value} with parameters for string splicing.
Note: if it is a parameter of simple quantity type, the value in parentheses must be value
resultType: defines the result mapping type.
4.5. 2 test procedure:
// Fuzzy query of user information according to user name
@Test
public void testFindUserByUsername() {
// Database session instance
SqlSession sqlSession = null;
try {
// Create database session instance sqlSession
sqlSession = sqlSessionFactory.openSession();
// Query a single record and query user information according to user id
List<User> list = sqlSession.selectList("test.findUserByUsername", "Zhang");
System.out.println(list.size());
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null) {
sqlSession.close();
}
}
}
4.6 summary
4.6. 1 #{} and ${}
#{} represents a placeholder. Through #{}, you can set the value in the placeholder for preparedStatement and automatically convert java types and jdbc types, #{} can effectively prevent sql injection# {} can receive simple type values or pojo property values. If parameterType transfers a single simple type value, #{} the parentheses can be value or other name.
surface
show
Spell
meet
s
q
l
strand
，
through
too
{} represents the spliced sql string through
Represents a spliced sql string. Through {}, the contents passed in by parameterType can be spliced in sql without jdbc type conversion,
can
with
meet
collect
simple
single
class
type
value
or
p
o
j
o
genus
nature
value
，
as
fruit
p
a
r
a
m
e
t
e
r
T
y
p
e
pass
transport
single
individual
simple
single
class
type
value
，
{} can receive simple type values or pojo attribute values. If parameterType transmits a single simple type value,
You can receive simple type values or pojo attribute values. If parameterType transmits a single simple type value, only value can be in {} brackets.
4.6.2 parameterType and resultType
parameterType: Specifies the input parameter type. mybatis obtains the parameter value from the input object through ognl and splices it in sql.
resultType: Specifies the output result type. mybatis maps a row of record data of sql query results to objects of the type specified by resultType.
4.6.3 selectOne and selectList
selectOne queries one record. If you use selectOne to query multiple records, an exception will be thrown:
org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 3
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:70)
selectList can query one or more records.
4.7 adding users
4.7. 1 mapping file:
In user Add to XML:
<!-- Add user -->
<insert id="insertUser" parameterType="cn.itcast.mybatis.po.User">
insert into user(username,birthday,sex,address)
values(#{username},#{birthday},#{sex},#{address})
</insert>
4.7. 2 test procedure:
// Add user information
@Test
public void testInsert() {
// Database session instance
SqlSession sqlSession = null;
try {
// Create database session instance sqlSession
sqlSession = sqlSessionFactory.openSession();
// Add user information
User user = new User();
user.setUsername("Xiao Ming Zhang");
user.setAddress("Zhengzhou, Henan");
user.setSex("1");
user.setPrice(1999.9f);
sqlSession.insert("test.insertUser", user);
//Commit transaction
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null) {
sqlSession.close();
}
}
}
4.7. 3. MySQL auto increment primary key return
By modifying the sql mapping file, you can return the mysql self incremented primary key to:
<insert id="insertUser" parameterType="cn.itcast.mybatis.po.User">
<!-- selectKey Return the primary key. You need to return it again -->
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
select LAST_INSERT_ID()
</selectKey>
insert into user(username,birthday,sex,address)
values(#{username},#{birthday},#{sex},#{address});
</insert>
Add selectKey to return the primary key
keyProperty: which property is the returned primary key stored in pojo
Order: the execution order of the selectKey is relative to that of the insert statement. Because of the self increment principle of mysql, the primary key is generated after the insert statement is executed. Therefore, the execution order of the selectKey here is after
resultType: what is the returned primary key type
LAST_INSERT_ID(): a mysql function that returns auto_increment automatically increments the id value of the new record.
4.7.4 Mysql uses uuid to implement the primary key
You need to increase the UUID value obtained through 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"