1. The background
This section is to learn how to use MyBatis to add, delete, change and check.
Example 2.
Before the example starts, let’s set up the table (see bottom) and set up the entity class SysUser (see bottom).
2.1 INSERT INSERT
Edit SysUserMapper. XML
<insert id="insert">
INSERT INTO sys_user(
user_name,user_password,user_email,user_info)
values(
#{userName},#{userPassword},#{userEmail},#{userInfo})
</insert>
Copy the codeUseGeneratedKeys =”true” keyProperty=” ID “; useGeneratedKeys=”true” keyProperty=”id”
- UseGeneratedKeys specifies the use of auto-increment primary keys
- The field name of the entity class is specified after keyProperty.
Edit SysUserMapper. XML
<! <insert id="insert2" useGeneratedKeys="true" keyProperty="id"> insert INTO sys_user( user_name,user_password,user_email,user_info) values( #{userName},#{userPassword},#{userEmail},#{userInfo}) </insert>Copy the codeA call:
try (SqlSession session = sqlSessionFactory.openSession(true)) { // Insert SysUserMapper sysUserMapper = session.getMapper(SysUserMapper.class); SysUser user = new SysUser(); user.userName = "jack22"; user.userPassword = "123"; int effctrows = 0; effctrows = sysUserMapper.insert(user); Printf (" insert: effctrows=%s, MSG =%s", effctrows, user.toString()); effctrows = sysUserMapper.insert2(user); Printf (" effctrows=%s, MSG =%s", effctrows, user.toString()); }Copy the code2.2 UPDATE to UPDATE
Write this in the XML file:
<update id="updateUser" >
UPDATE sys_user SET user_name= #{userName}, user_password= #{userPassword}, user_email=#{userEmail}, user_info= #{userInfo} WHERE id=#{id}
</update>
Copy the codeModify SysUserMapper class:
public interface SysUserMapper {
int updateUser(SysUser user);
}
Copy the codePerform updates:
private static void testUpdate(SqlSessionFactory sqlSessionFactory) { try (SqlSession session = sqlSessionFactory.openSession(true)) { SysUserMapper sysUserMapper = session.getMapper(SysUserMapper.class); SysUser user = new SysUser(); user.id = 1; user.userPassword = "33"; sysUserMapper.updateUser(user); }}Copy the code2.3 DELETE DELETE
Modify the XML mapping file:
<delete id="deleteById">
DELETE FROM sys_user WHERE id=#{id}
</delete>
Copy the codeModify mapper class:
public interface SysUserMapper {
int deleteById(int id);
}
Copy the codePerform delete:
private static void testDelete(SqlSessionFactory sqlSessionFactory) { try (SqlSession session = sqlSessionFactory.openSession(true)) { SysUserMapper sysUserMapper = session.getMapper(SysUserMapper.class); sysUserMapper.deleteById(1); }}Copy the code2.4 Simple Query SELECT
In general, automatic hump naming mapping is enabled first. See the method at the end of this article. XML mapping file:
<select id="selectAll" resultType="cn.zyfvir.SysUser">
select id, user_name, user_password, user_email, user_info from sys_user
</select>
<select id="selectByPrimaryKey" resultType="cn.zyfvir.SysUser">
select id, user_name, user_password, user_email, user_info from sys_user WHERE id=#{id}
</select>
Copy the code- Note that resultType=” cn.zyfvir.sysuser “is specified here, resultType means to specify the type of the returned value.
- The SELECT statement may return one or more, so a single object can be used in a Java mapper file, or a List collection can be received. Mybatis will automatically complete serialization for us. Example:
public interface SysUserMapper {
List<SysUser> selectAll();
SysUser selectByPrimaryKey(int id);
}
Copy the codeCall example:
private static void testSelect(SqlSessionFactory sqlSessionFactory) { try (SqlSession session = sqlSessionFactory.openSession(true)) { SysUserMapper sysUserMapper = session.getMapper(SysUserMapper.class); List<SysUser> list = sysUserMapper.selectAll(); printf("list=%s", list.size()); for (SysUser item: list) { printf("user=%s", item); } SysUser user = sysUserMapper.selectByPrimaryKey(2); printf("user=%s", user); }}Copy the code2.5 Associated Query
3. The extension
Typically, Java fields are in the camel name format, such as userName, where the second name starts with a capital letter. Fields in a database are case-insensitive, so they are often distinguished by underscores (_). For example, the name user_name is in the underscore format. Therefore, Mybatis provides this configuration item.
Modify mybatis-config. XML file and add mapUnderscoreToCamelCase configuration item:
<settings> <setting name="logImpl" value="LOG4J"/> <! -- Whether to enable automatic mapping of camel names, that is, from the classic database column name A_COLUMN to the classic Java property name aColumn. --> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings>Copy the code4. The appendix
Construction sentences:
CREATE TABLE sys_user (id int NOT NULL AUTO_INCREMENT COMMENT 'id ', user_name varchar(255) NULL COMMENT' id ', User_password varchar(255) NULL COMMENT 'password ', user_email varchar(255) NULL COMMENT' password ', User_info text NULL COMMENT '中 文 ', create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT' 中 文 ', Update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update_time ', PRIMARY KEY (id)) COMMENT '表 示 '; INSERT INTO sys_user(user_name,user_password) values('zhang3',"123"), ('li4',"123"); INSERT INTO sys_user(user_name,user_password,user_email,user_info) values('zhang3',"123",'','');Copy the codeRoles and association tables
CREATE TABLE roles (id int NOT NULL AUTO_INCREMENT COMMENT 'id ', role_name varchar(255) NULL COMMENT' id ', Create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create time ', Update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update_time ', PRIMARY KEY (id)) COMMENT 'table '; INSERT INTO roles(role_name) VALUES (" administrator "), CREATE TABLE user_and_role (id int NOT NULL AUTO_INCREMENT COMMENT 'ID', user_id int NOT NULL COMMENT 'ID', role_id int NOT NULL COMMENT 'ID', Create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create time ', Update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update_time ', PRIMARY KEY (id)) COMMENT 'id '; INSERT INTO user_and_role(user_id,role_id) VALUES (2,1); INSERT INTO user_and_role(user_id,role_id) VALUES (2,2); INSERT INTO user_and_role(user_id,role_id) VALUES (3,1);Copy the codeEntity Sysuser
public class SysUser {
public long id;
public String userName;
public String userPassword;
public String userEmail;
public String userInfo;
}
Copy the codeExample of my code: github.com/vir56k/java…
Reference:
Autocommit transactions (Autocommit of transactions) When opening a session using openSession, specifying the true parameter indicates that a transaction needs to be committed automatically. Here it is:
try (SqlSession session = sqlSessionFactory.openSession(true)) { int effctrows; effctrows = sysUserMapper.insert(user); // don't write commit}Copy the codeSqlSessionFactory openSession parameterless calls, or to shut down automatically submit false said, at this moment, will need to manually commit the transaction. Here’s an example:
try (SqlSession session = sqlSessionFactory.openSession()) { int effctrows; effctrows = sysUserMapper.insert(user); session.commit(); // You need to write commit}Copy the code