Mybatis基础操作

Mybatis基础使用Mybatis编程式开发mybatis和MySQL jar包依赖xmldependencies !-- MyBatis 核心 -- dependency groupIdorg.mybatis/groupId artifactIdmybatis/artifactId version3.5.10/version /dependency !-- MySQL 驱动 -- dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId version8.0.33/version /dependency !-- 连接池可选推荐 -- dependency groupIdcom.zaxxer/groupId artifactIdHikariCP/artifactId version5.0.1/version /dependency /dependencies全局配置文件mybatis-config.xml配置文件对应标签可以看官方文档MyBatis 3 | Configuration – mybatisxml?xml version1.0 encodingUTF-8? !DOCTYPE configuration PUBLIC -//mybatis.org//DTD Config 3.0//EN http://mybatis.org/dtd/mybatis-3-config.dtd configuration !-- 1. 加载外部属性文件 -- properties resourcejdbc.properties/ !-- 2. 全局设置 -- settings !-- 开启下划线到驼峰命名自动映射 -- setting namemapUnderscoreToCamelCase valuetrue/ !-- 开启二级缓存 -- setting namecacheEnabled valuetrue/ !-- 延迟加载的触发方法 -- setting namelazyLoadTriggerMethods value/ !-- 查询时关闭关联对象即时加载 -- setting namelazyLoadingEnabled valuetrue/ !-- 设置超时时间 -- setting namedefaultStatementTimeout value3000/ !-- 使用列标签代替列名 -- setting nameuseColumnLabel valuetrue/ !-- 允许JDBC支持自动生成主键 -- setting nameuseGeneratedKeys valuetrue/ /settings !-- 3. 类型别名配置 -- typeAliases !-- 扫描包自动注册别名 -- package namecom.example.entity/ !-- 也可以单独配置 -- !-- typeAlias typecom.example.entity.User aliasUser/ -- /typeAliases !-- 4. 环境配置可配置多个通过default属性切换 -- environments defaultdevelopment !-- 开发环境 -- environment iddevelopment !-- 事务管理器 -- transactionManager typeJDBC property namecloseConnection valuefalse/ /transactionManager !-- 数据源配置 -- dataSource typePOOLED property namedriver value${jdbc.driver}/ property nameurl value${jdbc.url}/ property nameusername value${jdbc.username}/ property namepassword value${jdbc.password}/ !-- 连接池配置 -- property namepoolMaximumActiveConnections value20/ property namepoolMaximumIdleConnections value10/ property namepoolMaximumCheckoutTime value20000/ property namepoolTimeToWait value20000/ /dataSource /environment !-- 测试环境 -- environment idtest transactionManager typeJDBC/ dataSource typePOOLED property namedriver value${jdbc.driver}/ property nameurl value${jdbc.test.url}/ property nameusername value${jdbc.test.username}/ property namepassword value${jdbc.test.password}/ /dataSource /environment /environments !-- 5. 映射器配置 -- mappers !-- 方式1通过resource指定XML文件 -- mapper resourcecom/example/mapper/UserMapper.xml/ !-- 方式2通过class指定接口需要接口和XML同名同路径 -- !-- mapper classcom.example.mapper.UserMapper/ -- !-- 方式3扫描包下所有mapper接口 -- !-- package namecom.example.mapper/ -- /mappers /configuration映射器 Mapper.xmlxml?xml version1.0 encodingUTF-8 ? !DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd mapper namespacecom.example.mapper.UserMapper !-- 基础ResultMap映射 -- resultMap idBaseResultMap typeUser id propertyid columnid/ result propertyusername columnusername/ result propertypassword columnpassword/ result propertyemail columnemail/ result propertyage columnage/ result propertystatus columnstatus/ result propertycreateTime columncreate_time/ result propertyupdateTime columnupdate_time/ !-- 枚举类型处理 -- result propertygender columngender typeHandlerorg.apache.ibatis.type.EnumOrdinalTypeHandler/ /resultMap !-- 包含订单的ResultMap一对多 -- resultMap idUserWithOrdersResultMap typeUser extendsBaseResultMap !-- 一对多关联用户的订单 -- collection propertyorders ofTypeOrder columnid selectcom.example.mapper.OrderMapper.selectByUserId/ /resultMap !-- 插入用户 -- insert idinsert parameterTypeUser useGeneratedKeystrue keyPropertyid INSERT INTO users (username, password, email, age, status, create_time, update_time, gender) VALUES (#{username}, #{password}, #{email}, #{age}, #{status}, #{createTime}, #{updateTime}, #{gender, typeHandlerorg.apache.ibatis.type.EnumOrdinalTypeHandler}) /insert !-- 批量插入用户 -- insert idbatchInsert parameterTypejava.util.List useGeneratedKeystrue keyPropertyid INSERT INTO users (username, password, email, age, status, create_time, update_time) VALUES foreach collectionlist itemuser separator, (#{user.username}, #{user.password}, #{user.email}, #{user.age}, #{user.status}, #{user.createTime}, #{user.updateTime}) /foreach /insert !-- 根据ID删除 -- delete iddeleteById parameterTypeLong DELETE FROM users WHERE id #{id} /delete !-- 根据用户名删除 -- delete iddeleteByUsername parameterTypeString DELETE FROM users WHERE username #{username} /delete !-- 更新用户 -- update idupdate parameterTypeUser UPDATE users set if testusername ! nullusername #{username},/if if testpassword ! nullpassword #{password},/if if testemail ! nullemail #{email},/if if testage ! nullage #{age},/if if teststatus ! nullstatus #{status},/if if testupdateTime ! nullupdate_time #{updateTime},/if /set WHERE id #{id} /update !-- 更新用户状态 -- update idupdateStatus UPDATE users SET status #{status} WHERE id #{id} /update !-- 根据ID查询 -- select idselectById parameterTypeLong resultMapBaseResultMap SELECT * FROM users WHERE id #{id} /select !-- 查询所有用户 -- select idselectAll resultMapBaseResultMap SELECT * FROM users ORDER BY create_time DESC /select !-- 根据用户名查询模糊查询 -- select idselectByUsername parameterTypeString resultMapBaseResultMap SELECT * FROM users WHERE username LIKE CONCAT(%, #{username}, %) /select !-- 条件查询动态SQL -- select idselectByCondition parameterTypemap resultMapBaseResultMap SELECT * FROM users where if testusername ! null and username ! AND username LIKE CONCAT(%, #{username}, %) /if if testemail ! null and email ! AND email #{email} /if if testminAge ! null AND age #{minAge} /if if testmaxAge ! null AND age lt; #{maxAge} /if if teststatus ! null AND status #{status} /if if teststartTime ! null AND create_time #{startTime} /if if testendTime ! null AND create_time lt; #{endTime} /if /where ORDER BY id DESC /select !-- 分页查询 -- select idselectByPage resultMapBaseResultMap SELECT * FROM users ORDER BY id DESC LIMIT #{offset}, #{pageSize} /select !-- 统计数量 -- select idcount resultTypeint SELECT COUNT(*) FROM users /select !-- 查询用户及其订单关联查询 -- select idselectUserWithOrders parameterTypeLong resultMapUserWithOrdersResultMap SELECT * FROM users WHERE id #{userId} /select /mapperMapper接口javapackage com.example.mapper; import com.example.entity.User; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; import org.apache.ibatis.annotations.Update; import java.util.List; import java.util.Map; /** * User 数据访问接口 * 注意不使用Spring时这个接口不需要添加Repository等注解 */ public interface UserMapper { // 增 /** * 插入用户 */ int insert(User user); /** * 批量插入用户 */ int batchInsert(ListUser users); // 删 /** * 根据ID删除用户 */ int deleteById(Long id); /** * 根据用户名删除用户 */ int deleteByUsername(String username); // 改 /** * 更新用户 */ int update(User user); /** * 更新用户状态 * 使用Param注解指定参数名 */ int updateStatus(Param(id) Long id, Param(status) Integer status); // 使用注解定义SQL不需要在XML中配置 Update(UPDATE users SET age #{age} WHERE id #{id}) int updateAge(Param(id) Long id, Param(age) Integer age); // 查 /** * 根据ID查询用户 */ User selectById(Long id); /** * 查询所有用户 */ ListUser selectAll(); /** * 根据用户名查询 */ ListUser selectByUsername(String username); /** * 条件查询 * param condition 查询条件 */ ListUser selectByCondition(MapString, Object condition); /** * 分页查询 * param pageNum 页码 * param pageSize 每页大小 */ ListUser selectByPage(Param(offset) int offset, Param(pageSize) int pageSize); /** * 统计用户数量 */ int count(); /** * 使用注解定义查询 */ Select(SELECT * FROM users WHERE email #{email}) User selectByEmail(String email); /** * 关联查询查询用户及其订单一对多 * 需要在XML中配置resultMap */ User selectUserWithOrders(Long userId); }mybatis工具类javapackage com.example.app; 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 java.io.IOException; import java.io.InputStream; /** * MyBatis 工具类 - 手动管理 SqlSessionFactory */ public class MyBatisUtil { private static SqlSessionFactory sqlSessionFactory; // 静态代码块在类加载时初始化 static { try { // 1. 加载 MyBatis 配置文件 String resource mybatis-config.xml; InputStream inputStream Resources.getResourceAsStream(resource); // 2. 创建 SqlSessionFactory sqlSessionFactory new SqlSessionFactoryBuilder().build(inputStream); System.out.println(MyBatis SqlSessionFactory 初始化成功!); } catch (IOException e) { System.err.println(MyBatis 初始化失败: e.getMessage()); throw new RuntimeException(MyBatis 初始化失败, e); } } /** * 获取 SqlSession 对象 */ public static SqlSession getSqlSession() { return sqlSessionFactory.openSession(); } /** * 获取 SqlSession 对象自动提交事务 */ public static SqlSession getSqlSessionWithAutoCommit() { return sqlSessionFactory.openSession(true); } /** * 关闭 SqlSession */ public static void closeSession(SqlSession session) { if (session ! null) { session.close(); } } /** * 获取 SqlSessionFactory */ public static SqlSessionFactory getSqlSessionFactory() { return sqlSessionFactory; } /** * 获取 Mapper 接口的代理对象 */ public static T T getMapper(ClassT type) { try (SqlSession session getSqlSession()) { return session.getMapper(type); } } }编程式使用示例javapackage com.example.app; import com.example.entity.User; import com.example.mapper.UserMapper; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.InputStream; import java.time.LocalDateTime; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; /** * 不使用 Spring 时MyBatis 编程式使用示例 */