1. Query
In addition to the query of a single record, here we try to query a set of records.
Add the following method to the IUserMapper interface:
List<User> getUsers(String name);
Add in User.xml:
<resultMap type="User" id="userList"><!-- type is the full name or alias of the class that returns the list element--> <id column="id" property="id" /> <result column="name" property="name" /> <result column="age" property="age" /> <result column="address" property="address" /> </resultMap> <select id="getUsers" parameterType="string" resultMap="userList"><!-- resultMap is the User List defined above--> select * from `user` where name like #{name} </select>Test method:
@Test public void queryListTest() { SqlSession session = sqlSessionFactory.openSession(); try { IUserMapper mapper = session.getMapper(IUserMapper.class); List<User> users = mapper.getUsers("%a%"); // % represents any character in sql. for (User user : users) { log.info("{}: {}", user.getName(), user.getAddress()); } } finally { session.close(); } } If the conjunction table query is returned, the compound object needs to be processed with the association keyword.
If User publishes Article, each user can publish multiple Articles, and they have a one-to-many relationship.
(1) Create an Article table and insert the test data:
-- Drop the table if exists DROP TABLE IF EXISTS `Article`; -- Create a table named 'Article' CREATE TABLE `Article` ( `id` int NOT NULL AUTO_INCREMENT, `user_id` int NOT NULL, `title` varchar(100) NOT NULL, `content` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; -- Add several test records INSERT INTO `article` VALUES ('1', '1', 'title1', 'content1'), ('2', '1', 'title2', 'content2'), ('3', '1', 'title3', 'content3'), ('4', '1', 'title4', 'content4');(2) com.john.hbatis.model.Article class:
public class Article { private int id; private User user; private String title; private String content; // Getters and setters are omitted }(3) Add in IUserMapper:
List<Article> getArticlesByUserId(int id);
(4) Add in User.xml:
<resultMap type="com.john.hbatis.model.Article" id="articleList"> <id column="a_id" property="id" /> <result column="title" property="title" /> <result column="content" property="content" /> <association property="user" javaType="User"><!-- user attribute maps to User class--> <id column="id" property="id" /> <result column="name" property="name" /> <result column="address" property="address" /> </association> </resultMap> <select id="getArticlesByUserId" parameterType="int" resultMap="articleList"> select u.id, u.name, u.age, u.address, a.id a_id, a.title, a.content from article a inner join user u on a.user_id=u.id and u.id=#{id} </select>(5) Test method:
@Test public void getArticlesByUserIdTest() { SqlSession session = sqlSessionFactory.openSession(); try { IUserMapper mapper = session.getMapper(IUserMapper.class); List<Article> articles = mapper.getArticlesByUserId(1); for (Article article : articles) { log.info("{} - {}, author: {}", article.getTitle(), article.getContent(), article.getUser().getName()); } } finally { session.close(); } } Attached:
In addition to defining the mapping of fields and attributes within the association tag, you can also reuse the resultMap of User:
<association property="user" javaType="User" resultMap="userList" />
2. Added
Add the following method to the IUserMapper interface:
int addUser(User user);
User.xml add:
<insert id="addUser" parameterType="User" useGeneratedKeys="true" keyProperty="id"><!-- useGeneratedKeys specifies the primary key that myBatis uses the database to automatically generate and populate it on the properties specified by keyProperty. If not specified, the return object cannot get the generated value --> insert into user(name,age,address) values(#{name},#{age},#{address}) </insert>Test method:
@Test public void addUserTest() { User user = new User("Lucy", 102, "Happy District"); SqlSession session = sqlSessionFactory.openSession(); try { IUserMapper mapper = session.getMapper(IUserMapper.class); int affectedCount = mapper.addUser(user); session.commit(); // Default is not automatically submitted. Call session.getConnection().getAutoCommit() to view log.info("{} new record was inserted successfully whose id: {}", affectedCount, user.getId()); } finally { session.close(); } }3. Update
Interface addition method:
int updateUser(User user);
User.xml add:
<update id="updateUser" parameterType="User"> update `user` set name=#{name}, age=#{age}, address=#{address} where id=#{id} </update>Test method:
@Test public void updateUserTest() { SqlSession session = sqlSessionFactory.openSession(); try { IUserMapper mapper = session.getMapper(IUserMapper.class); User user = mapper.getUserById(8); user.setAddress("Satisfied District"); int affectedCount = mapper.updateUser(user); // In addition to the attributes to be modified, other attributes of user must also be assigned, otherwise these attributes will be updated by the database to the initial value (null or 0, etc.). You can query it first, but this will increase unnecessary interaction with the database. The following conditions can avoid this problem. log.info("Affected count: {}", affectedCount); session.commit(); } finally { session.close(); } }4. Delete
Interface addition method:
int deleteUser(int id);
User.xml add:
<delete id="deleteUser" parameterType="int"> delete from `user` where id=#{id} </delete>Test method:
@Test public void deleteUserTest() { SqlSession session = sqlSessionFactory.openSession(); try { IUserMapper mapper = session.getMapper(IUserMapper.class); int affectedCount = mapper.deleteUser(8); log.info("Affected count: {}", affectedCount); session.commit(); } finally { session.close(); } }