Getting started with Mybatis
1. Use of mybatis
Write SQL into xml file to reduce the coupling between SQL and program
The biggest change: before writing the UserDao interface, write the implementation class UserDaoImp, and now write the UserMapper.xml configuration file
1. Create Maven project and add dependency mybatis
In the example, (the database created is called mybatis, and the table created is called user)
2. Create a tool class
The last SqlSession is returned by reading in the xml file, which is similar to the connection in JDBC
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//Fixed writing method, read in through the configuration file, and get the factory class from the factory construction
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();//Obtain the sqlSession class from the factory class (similar to the database connection class)
}
}
3. Write the mybatis-config.xml configuration file
In the configuration file, modify the four variables as database connection, and keep the < mappers > tag
<?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">
<!--Core profile-->
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf8&useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/roy/dao/UserMapper.xml"/>
</mappers>
</configuration>
4. Write entity class pojo.User
5. Write UserDao interface (later changed to UserMapper)
6. Write UserMapper.xml (equivalent to the previous UserDaoImp)
<?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="com.roy.dao.UserDao">//Equivalent to inherited interface
<select id="getUserList" resultType="com.roy.pojo.User">
// id is the method in the interface, and result is the returned entity class object (whether map or list is returned here, write the type in the generic type, and here is the entity class)
select * from mybatis.user //select statement to execute
</select>
</mapper>
Add mapper to the mybatis-config.xml configuration file, and add:
<mappers>
<mapper resource="com/roy/dao/UserMapper.xml"/>
// Here, it is configured to the path of UserMapper.xml, separated by /
</mappers>
7. test
The test needs to obtain the SqlSession object first, get the result through the object, and traverse
@Test
public void test(){
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.getSqlSession();// Get database connection from tool class
// Through the reflection of the interface, the implementation class of the interface is obtained, and then an instance object is new
UserDao mapper = sqlSession.getMapper(UserDao.class);
// Through the instance object, call the method to obtain the return value
List<User> userList = mapper.getUserList();
// Traversal results
for (User user : userList) {
System.out.println(user);
}
}finally {
// Close database connection
sqlSession.close();
}
}
8. Notes:
-
The UserMapper.xml file written is in the java folder. The XML file in the java folder must be filtered in the pom.xml configuration file of maven project to ensure smooth compilation
//pom.xml configuration of maven <build> <resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> <include>**/*.properties</include> </includes> </resource> </resources> </build> -
Add the written UserMapping.xml resource path to the mybatis-config.xml file
<mappers> <mapper resource="com/roy/dao/UserMapper.xml"/> </mappers> -
When promoting a variable scope, remember to delete the variable type declaration before promotion
2. CRUD
UserMapper.java:
public interface UserMapper {
List<User> getUserList();
User getUserById(int id);
int addUser(User user);
int updateUser(User user);
int deleteUser(int id);
}
UserMapper.xml implements the UserMapper interface
<?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="com.roy.dao.UserMapper">
<select id="getUserList" resultType="com.roy.pojo.User">
select * from mybatis.user
</select>
<select id="getUserById" parameterType="int" resultType="com.roy.pojo.User">
select * from mybatis.user where id = #{id}
</select>
<!-- 1. Insert statement to write insert Label, can't write select label-->
<!-- 2. insert The tag has no return value, no need to write resultset attribute-->
<!-- 3. insert of value Add the attribute of the object in the. You can write the attribute directly (provided that the type is an entity class) without using the object.get value-->
<!-- 4. The addition, deletion and modification can be executed successfully only after the transaction is committed, otherwise the rollback needs to be added in the test sqlSession.commit();-->
<insert id="addUser" parameterType="com.roy.pojo.User">
insert into mybatis.user(`id`, `name`, `pwd`) values (#{id},#{name},#{pwd})
</insert>
<update id="updateUser" parameterType="com.roy.pojo.User">
update mybatis.user set `name`=#{name}, `pwd`=#{pwd} where `id`=#{id}
</update>
<delete id="deleteUser" parameterType="int">
delete mybatis.user where id = #{id}
</delete>
</mapper>
1. select query
xml file implementing UserDao interface:
Write the select tag. The id is the method name defined by the interface. The parameterType is the incoming parameter type. The resultType is the return value type. If it is a list, it is a list generic type
<select id="getUserById" parameterType="int" resultType="com.roy.pojo.User">
select * from mybatis.user where id = #{id}
</select>
Test class:
@Test
public void getUserById(){
SqlSession sqlSession = null;
try{
sqlSession = MybatisUtils.getSqlSession();//Get database connection
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);// Get the interface class through reflection and instantiate an object
User userById = userMapper.getUserById(4);//Call the function and return
System.out.println(userById);
}finally {
sqlSession.close();
}
}
In Mybatis, add, delete and modify transactions must be submitted!!!!!
2. Insert
<!-- 1. Insert statement to write insert Label, can't write select label-->
<!-- 2. insert The tag has no return value, no need to write resultset attribute-->
<!-- 3. insert of value Add the attribute of the object in the. You can write the attribute directly (provided that the type is an entity class) without using the object.get value-->
<!-- 4. The addition, deletion and modification can be executed successfully only after the transaction is committed, otherwise the rollback needs to be added in the test sqlSession.commit();-->
<insert id="addUser" parameterType="com.roy.pojo.User">
insert into mybatis.user(`id`, `name`, `pwd`) values (#{id},#{name},#{pwd})
</insert>
Test class:
@Test
public void testOKInsert(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User insertUser = new User(4, "forth", "123");
mapper.addUser(insertUser);
sqlSession.commit();
sqlSession.close();
}
3. update
<update id="updateUser" parameterType="com.roy.pojo.User">
update mybatis.user set `name`=#{name}, `pwd`=#{pwd} where `id`=#{id}
</update>
Test class:
@Test
public void updateUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.updateUser(new User(4, "afterUpdate", "999"));
sqlSession.commit();
sqlSession.close();
}
4. delete
<delete id="deleteUser" parameterType="int">
delete mybatis.user where id = #{id}
</delete>
Test class:
@Test
public void deleteUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.deleteUser(4);
sqlSession.commit();
sqlSession.close();
}
3. Map and fuzzy query
1. Map usage
When a database has many attributes, the fields in the insert statement often need to correspond to each other. In this case, you can use map
Advantages: the value value in the xml file does not need to correspond to the one-to-one value in the database, and it is ok to correspond to the key value of the map
- UserMapper.java interface:
int addUser2(Map<String, Object> map);
- UserMapper.xml interface implementation:
<insert id="addUser2" parameterType="map">
insert into mybatis.user(`id`, `name`, `pwd`) values (#{key1_Userid},#{key2_UserName},#{key3_password})
</insert>
- Test class:
@Test
public void testaddUser2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("key1_Userid", 5);
map.put("key2_UserName", "keyvalue");
map.put("key3_password", "mapPassword");
mapper.addUser2(map);
sqlSession.commit();
sqlSession.close();
}
Parameters passed in UserMapper.xml:
- If there is only one parameter, parameterType can not be written!!!, For example, the method of querying by id
- If the parameter is an entity class, the value in the sql statement must correspond to the attribute
- If the parameter is map, the value in the sql statement is the key value of map
2. Fuzzy query
For the implementation of fuzzy query, pass in the parameters in the test class:
When testing class, call the method in UserMapper.xml:
List<User> userList = mapper.getUserLike("Lee%");//Will go to match all the people surnamed Li
Or define in. xml: (id, define that the incoming parameter is String, eg can be used when "1" is passed in, but not if pT is int)
<select id="getUserById" parameterType="String" resultType="com.roy.pojo.User">
select * from mybatis.user where id like "%"#{id} / / write% in the sql statement to implement fuzzy query
</select>