mybatis增删改查例子

合集下载

Mybatis-Plus增删改查

Mybatis-Plus 增删改查Mybatis-plus增删改查插⼊操作⽅法定义测试⽤例测试/*** 插⼊⼀条记录** @param entity 实体对象*/int insert(T entity);package com.pbteach.mp;import erMapper;import er;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import org.springframework.test.context.junit4.SpringRunner;import java.util.List;@RunWith(SpringRunner.class)@SpringBootTestpublic class UserMapperTest {@Autowiredprivate UserMapper userMapper;@Testpublic void testInsert(){User user = new User();user.setAge(20);user.setEmail("test@");user.setName("曹操");user.setUserName("caocao");user.setPassword("123456");int result = erMapper.insert(user); //返回的result 是受影响的⾏数，并不是⾃增后的idSystem.out.println("result = " + result);System.out.println(user.getId()); //⾃增后的id 会回填到对象中}}[main] [erMapper.insert]-[DEBUG] ==> Preparing: INSERT INTO tb_user ( id, user_name, password, name, age, email ) VALUES ( ?, ?, ?, ?, ?, ? )[main] [erMapper.insert]-[DEBUG] ==> Parameters: 1122045867793072130(Long), caocao(String), 123456(String), 曹操(String), 20(Integer), test@(String)[main] [erMapper.insert]-[DEBUG] <== Updates: 1[main] [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@411291e5]result = 11122045867793072130可以看到，数据已经写⼊到了数据库，但是，id的值不正确，我们期望的是数据库⾃增长，实际是MP⽣成了id的值写⼊到了数据库。

fastmybatis案例

fastmybatis案例fastmybatis是一款快速开发MyBatis项目的工具，借助该工具可以大幅度提高项目开发效率。

下面介绍一个fastmybatis的使用案例。

假设现在需要开发一个用户管理系统，需要实现用户的增删改查功能。

首先需要定义一个用户实体类，如下：```javapublic class User {private Long id;private String username;private String password;private Integer age;// 省略getter和setter方法}```然后在数据库中创建一个名为user的表，包含id、username、password、age四个字段。

接着在项目中引入fastmybatis的依赖，并配置数据源和MyBatis的配置文件。

在配置文件中定义一个名为UserMapper的Mapper接口，如下：```javapublic interface UserMapper extends BaseMapper<User, Long>{}```这里使用BaseMapper作为父接口，同时指定实体类和主键类型。

BaseMapper已经封装了常用的增删改查操作，可以直接使用。

接着在Service中定义方法，如下：```java@Servicepublic class UserServiceImpl implements UserService {@Autowiredprivate UserMapper userMapper;@Overridepublic void addUser(User user) {userMapper.insert(user);}@Overridepublic void updateUser(User user) {userMapper.update(user);}@Overridepublic void deleteUser(Long id) {userMapper.deleteById(id);}@Overridepublic User getUserById(Long id) {return userMapper.selectById(id);}@Overridepublic List<User> getAllUsers() {return userMapper.selectAll();}}```可以看到，这里的实现非常简单，直接调用Mapper的方法即可。

springboot整合mybatis实现增删改查小案例

springboot整合mybatis实现增删改查⼩案例⼀、springboot简单hello world1.1 使⽤springboot项⽬前提是必须学会使⽤maven，maven這⾥就不多说了⽹上招⼀⼤堆教程1.2 创建⼀个war包的maven项⽬，创建后在pox.xml添加如下jar1.3 创建⼀个springboot的程序⼊⼝启动类：如下package com.ibigsea.bootdao;import org.mybatis.spring.annotation.MapperScan;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication; importorg.springframework.transaction.annotation.EnableTransactionManagement;@SpringBootApplicationpublic class UserApplication {public static void main(String[] args) {SpringApplication.run(UserApplication.class, args);}}package com.ibigsea.bootdao.controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;@RestController@RequestMapping("users")public class UserController {@RequestMapping("hello")public String hello(){return "Hello World";}}浏览器访问结果如下：⾄此⼀个简单的springboot的应⽤已完成，下⾯整合mybatis实现简单的增删改查案例⼆、springboot整合mybatis案例2.1 ⾸先需要在pox.xml导⼊jar 在hello world案例之上添加如下⼏个jar包</dependency>2.2 在src/main/resources⽬录下创建⼀个application.properties⽂件，内容如下2.3 entity类package com.ibigsea.bootdao.entity;import java.io.Serializable;public class User implements Serializable {private static final long serialVersionUID = 8809101560720973267L; private Integer id;private String userName;private Integer age;public User() {// TODO Auto-generated constructor stub}public User(String userName, Integer age) {super();erName = userName;this.age = age;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getUserName() {public void setUserName(String userName) {erName = userName;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}@Overridepublic String toString() {return "User [id=" + id + ", userName=" + userName + ", age=" + age + "]";}}2.4 mapper接⼝类可以在类加上@Mapper注解也可以在springboot应⽤的程序⼊⼝类@MapperScan(“com.ibigsea.bootdao.mapper”) package com.ibigsea.bootdao.mapper;import java.util.List;import org.apache.ibatis.annotations.Mapper;import er;public interface UserMapper {/*** 新增* @param userint save(User user);/*** 根据Id查询* @param user* @return*/User selectById(Integer id);/*** 根据Id修改* @param user* @return*/int updateById(User user);/*** 删除* @param user* @return*/int deleteById(Integer id);/*** 查询所有* @param user* @return*/List<User> queryAll();/*** 根据username查询*/User findByName(String username); }<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd" ><mapper namespace="erMapper" ><insert id="save" parameterType="er">insert into user(username,age) values(#{userName,jdbcType=VARCHAR},#{age,jdbcType=NUMERIC})</insert><select id="selectById" resultType="er">select * from user where id = #{id,jdbcType=NUMERIC}</select><update id="updateById" parameterType="er"> update user setusername = #{userName,jdbcType=VARCHAR} ,age = #{age,jdbcType=NUMERIC}where id = #{id,jdbcType=NUMERIC}</update><delete id="deleteById">delete from user where id = #{id,jdbcType=NUMERIC}</delete><select id="queryAll" resultType="er">select * from user</select><select id="findByName" resultType="er">select * from user where username=#{userName}</select></mapper>2.6 业务接⼝类与2.4的mapper接⼝类内容⼀致2.7 业务接⼝类的实现package com.ibigsea.bootdao.service.impl;import java.util.List;import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service;import org.springframework.transaction.annotation.Transactional;import er;import erMapper;import erService;@Transactional@Servicepublic class UserServiceImpl implements UserService{@Autowiredprivate UserMapper userMapper;@Overridepublic int save(User user) {int myuser= userMapper.save(user);//测试事务管理//int i =1/0;return myuser;}@Overridepublic User selectById(Integer id) {return userMapper.selectById(id);}@Overridepublic int updateById(User user) {return userMapper.updateById(user);}@Overridepublic int deleteById(Integer id) {return userMapper.deleteById(id);}@Overridepublic List<User> queryAll() {return userMapper.queryAll();}@Overridepublic User findByName(String username) {return userMapper.findByName(username);}}此处配置了事物管理需要在springboot⼊⼝类添加@EnableTransactionManagement 开启事物管理2.8 控制层package com.ibigsea.bootdao.controller;import java.util.List;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.PathVariable;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import er;import erService;@RestController@RequestMapping("users")public class UserController {@RequestMapping("hello")public String hello(){return "Hello World";}@RequestMapping("/add/{name}/{age}")public User insert(@PathVariable("name")String name,@PathVariable Integer age){userService.save(new User(name,age));return userService.findByName(name);}@RequestMapping("/findById/{id}")public User findById(@PathVariable("id") Integer id){return userService.selectById(id);}@RequestMapping("/deleteById/{id}")public void deleteById(@PathVariable("id") Integer id){userService.deleteById(id);}@RequestMapping("/updateById/{id}")public void updateById(@PathVariable("id") Integer id){User user = new User();user.setId(id);user.setAge(100);user.setUserName("xiaoguo");userService.updateById(user);}@RequestMapping("getUserList")public List<User> getUserList(){return userService.queryAll();}}2.9 spring boot 程序⼊⼝启动类package com.ibigsea.bootdao;import org.mybatis.spring.annotation.MapperScan;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication; importorg.springframework.transaction.annotation.EnableTransactionManagement;@SpringBootApplication@EnableTransactionManagement@MapperScan("com.ibigsea.bootdao.mapper")public class UserApplication {public static void main(String[] args) {SpringApplication.run(UserApplication.class, args);}}⾄此springboot整合mybatis整合完成，项⽬的⽬录结构如下：运⾏结果如下：。

MybatisPlus实现简单增删改查功能

MybatisPlus实现简单增删改查功能实现步骤：⼯具：IDEA数据库版本：mysql5.7⼀、环境搭建1.创建springboot项⽬pom.xml2.pom.xml : spring web、lombok mysql<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-devtools</artifactId><scope>runtime</scope><optional>true</optional></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency></dependencies>3.添加mybatisplus依赖<dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.3.1.tmp</version></dependency>application.properties4.application.properties配置⽂件：⽤户名、密码、连接驱动、配置⽇志注意：mysql8 需要增加时区的配置 serverTimezone=GMT%2B8 东⼋区#mysql5 驱动不同ername=rootspring.datasource.password=123456spring.datasource.url=jdbc:mysql://localhost:3306/mybatisplus?useSSL=false&useUnicode=true&characterEncoding=utf-8 spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver#配置⽇志mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl数据库数据#建表DROP TABLE IF EXISTS user;CREATE TABLE user(id BIGINT(20) NOT NULL COMMENT '主键ID',name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',age INT(11) NULL DEFAULT NULL COMMENT '年龄',email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',PRIMARY KEY (id));#插⼊数据DELETE FROM user;INSERT INTO user (id, name, age, email) VALUES(1, 'Jone', 18, 'test1@'),(2, 'Jack', 20, 'test2@'),(3, 'Tom', 28, 'test3@'),(4, 'Sandy', 21, 'test4@'),(5, 'Billie', 24, 'test5@');代码详解1.新建实体类Userlombok注解详解：@Data：⾃动⽣成get\set⽅法@AllArgsConstructor ：有参⽅法@NoArgsConstructor：⽆参⽅法@TableId(value = “id”,type = IdType.AUTO) ：给实体设置⾃增package com.sh.entity;import com.baomidou.mybatisplus.annotation.IdType;import com.baomidou.mybatisplus.annotation.TableId;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import org.springframework.context.annotation.Primary;import java.util.Date;@Data@AllArgsConstructor@NoArgsConstructorpublic class User {@TableId(value = "id",type = IdType.AUTO)private Integer id;private String name;private Integer age;private String email;private Date createTime;private Date updateTime;}2. 新建mapper包在mapper包⾥建UserMappe接⼝r 继承BaseMapper<实体类> package com.sh.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import er;import org.springframework.stereotype.Repository;@Repositorypublic interface UserMapper extends BaseMapper<User> {}3.创建控制层controller：新建UserController类package com.sh.controller;import er;import erMapper;import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.*;import java.util.ArrayList;import java.util.Arrays;import java.util.List;@RestControllerpublic class UserController {@Autowiredprivate UserMapper userMapper;//查询查询所以 null 就是没有条件@GetMapping("/selectUser")public List<User> getAll(){List<User> user = userMapper.selectList(null);return user;}//根据id查询@GetMapping("/selectById")public User selectUserById(){User user = userMapper.selectById(1);return user;}//根据多个id查询 Arrays.asList集合@GetMapping("/selectByIds")public List<User> selectUserByIds(){List<User> user = userMapper.selectBatchIds(Arrays.asList(1,2,3)); return user;}//添加@PostMapping("/insertUser")public Integer insertUsers(User user){Integer result = userMapper.insert(user);return result;}//修改根据id@PutMapping("/updateUser")public Integer updateUsers(User user){Integer result = userMapper.updateById(user);return result;}//删除根据id@DeleteMapping("/deleteUser")public Integer deleteUsers(Integer id){Integer result = userMapper.deleteById(id);return result;}}项⽬结构注意：添加，修改操作需要数据库和实体设置⾃增到此这篇关于MybatisPlus实现简单增删改查的⽂章就介绍到这了,更多相关MybatisPlus增删改查内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！。

springboot+Mybatis批量增删改查（java批量添加、修改带非空判断）

springboot+Mybatis批量增删改查（java批量添加、修改带⾮空判断）1、批量添加--xml代码<insert id="insertBatchList" parameterType="java.util.List">INSERT INTO sw_rs_set_holdstandard (hold_createtime,hold_flag,company_id,hold_type,train_id,hold_level3,hold_level4)values<foreach collection="list" item="item" index="index" separator=","><trim prefix=" (" suffix=")" suffixOverrides="," >now(),1,#{panyIdbs,jdbcType=BIGINT}<if test="item.holdType!=null">,#{item.holdType,jdbcType=BIGINT}</if><if test="item.holdType==null">,0</if>,#{item.trainIdbs,jdbcType=BIGINT}<if test="item.holdLevel3!=null">,#{item.holdLevel3,jdbcType=BIGINT}</if><if test="item.holdLevel3==null">,0</if><if test="item.holdLevel4!=null">,#{item.holdLevel4,jdbcType=BIGINT}</if><if test="item.holdLevel4==null">,0</if></trim></foreach></insert>2、批量添加--调⽤/*** 批量添加*/int insertBatchList(List<SwRsSetHoldstandardEntity> list);3、批量修改--xml代码<update id="updateBatchList" parameterType="java.util.List">UPDATE sw_rs_set_holdstandard<trim prefix="set" suffixOverrides=","><trim prefix="hold_updatetime =case" suffix="end,"><foreach collection="list" item="item">when hold_id = #{item.holdId} then now()</foreach></trim><trim prefix="hold_level3 =case" suffix="end,"><foreach collection="list" item="item" index="index"><if test="item.holdLevel3!=null">when hold_id = #{item.holdId} then #{item.holdLevel3}</if></foreach></trim><trim prefix="hold_level4 =case" suffix="end,"><foreach collection="list" item="item"><if test="item.holdLevel4!=null">when hold_id = #{item.holdId} then #{item.holdLevel4}</if></foreach></trim></trim>where hold_id in<foreach collection="list" index="index" item="item" separator="," open="(" close=")">#{item.holdId}</foreach></update>4、批量修改--调⽤/*** 批量修改*/int updateBatchList(List<SwRsSetHoldstandardEntity> list);5、批量删除--xml<delete id="deleteByPrimaryKey" parameterType="java.util.List">delete from descriptionwhere idin<foreach collection="list" item="id" open="(" separator="," close=")">#{id}</foreach></delete>6、查询xml<select id="getHoldstandardList" parameterType="com.rxjy.modules.ku.entity.SwRsSetHoldstandardEntity" resultMap="BaseResultMap"> SELECTtp.train_level trainLevel,tp.train_id trainIdbs,tp.train_postname trainPostname,-1 companyIdbs,case hs.hold_type when 1 then '资源' when 2 then '客源' else '' end holdTypeName,ifnull(cp.co_name,'集团') coName,hs.*,<include refid="Base_Column_List"></include>from sw_rs_trainingrepository tpLEFT JOIN sw_rs_set_holdstandard hs on hs.train_id=tp.train_id and hs.hold_flag=1<if test="companyId!=null">and pany_id=#{companyId}</if><if test="holdType!=null">and hs.hold_type=#{holdType}</if>LEFT JOIN sw_bs_company cp on pany_id=pany_idwhere tp.train_isenable=1<if test="trainPostname!=null and trainPostname!=''">and tp.train_postname=#{trainPostname}</if>order by tp.train_rank asc</select>。

【Mybatis】mybatis开启Log4j日志、增删改查操作

【Mybatis】mybatis开启Log4j⽇志、增删改查操作Mybatis⽇志（最常⽤的Log4j）# Global logging configurationlog4j.rootLogger=ERROR, stdout# MyBatis logging configuration...需要修改下⾯的⼀⾏.mybatis.example.BlogMapper=TRACE# Console output...log4j.appender.stdout=org.apache.log4j.ConsoleAppenderyout=org.apache.log4j.PatternLayoutyout.ConversionPattern=%5p [%t] - %m%n修改第四⾏：log4j.logger.⾃⼰程序的包名=TRACE例如.blb=TRACE**Mybatis增删改查dao层接⼝public interface UserDao {/*** 查询所有⽤户* @return*/List<User> getUsers();/*** 添加⽤户* @return*/void addUser(User user);/*** 删除⽤户* @return*/void delUserById(int id);void updateUserName(@Param("after") String after, @Param("id")int id);}映射配置⽂件<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd"><mapper namespace="erDao"><select id="getUsers" resultType="er">select * from userinfo</select><insert id="addUser" >INSERT INTO userinfo VALUES(null ,#{username},#{userpwd},#{typeid},#{userstatus})</insert><delete id="delUserById">DELETE FROM userinfo WHERE userid=#{id}</delete><update id="updateUserName">UPDATE userinfo SET username=#{after} WHERE userid=#{id};</update></mapper>使⽤注解的⽅式来映射使⽤注解的⽅式，就不⽤在xml中写<select id="getUsers" resultType="er">select * from userinfo</select>这样的语句了，⾮常⽅便，但是，复杂的SQL语句写在xml⽂件⾥⽅便维护！使⽤注解，⾮常简单public interface UserDao {/*** 查询所有⽤户* @return*/@Select("select * from userinfo")List<User> getUsers();/*** 添加⽤户* @return*/@Insert("INSERT INTO userinfo VALUES(null ,#{username},#{userpwd},#{typeid},#{userstatus})") void addUser(User user);/*** 删除⽤户* @return*/@Delete("DELETE FROM userinfo WHERE userid=#{id}")void delUserById(int id);@Update("UPDATE userinfo SET username=#{after} WHERE userid=#{id};")void updateUserName(@Param("after") String after, @Param("id")int id);}答疑@Update("UPDATE userinfo SET username=#{after} WHERE userid=#{id};")void updateUserName(@Param("after") String after, @Param("id")int id);写这种参数的时候要取别名@Param("别名")，不然获取不到参数报错。

mybatis xml update delete语句

mybatis xml update delete语句MyBatis是一种用于Java语言的持久化框架，通过XML配置文件进行SQL语句的编写。

其中，update和delete语句用于更新和删除数据库中的记录。

以下是MyBatis XML中编写update和delete语句的相关参考内容：1. Update语句：在MyBatis XML文件中，可以通过`<update>`标签来编写update语句。

以下是一个示例：```xml<update id="updateUser" parameterType="User">UPDATE userSET username = #{username}, password = #{password}WHERE id = #{id}</update>```- `<update>`标签用于定义一个update语句。

- `id`属性用于指定语句的唯一标识符。

- `parameterType`属性用于指定传入的参数类型，这里是User 类。

- `UPDATE user`部分指定要更新的表名为`user`，可以根据实际情况进行替换。

- `SET username = #{username}, password = #{password}`部分用于指定要更新的字段和字段值，通过`#{}`来引用参数值。

- `WHERE id = #{id}`部分用于指定要更新的记录的条件，通过`#{}`来引用参数值。

2. Delete语句：在MyBatis XML文件中，可以通过`<delete>`标签来编写delete语句。

以下是一个示例：```xml<delete id="deleteUser" parameterType="int">DELETE FROM userWHERE id = #{id}</delete>```- `<delete>`标签用于定义一个delete语句。

mybatis抽取基类BaseMapper增删改查的实现

mybatis抽取基类BaseMapper增删改查的实现⽬录准备⼯作:1:数据库表2:准备实体类步骤1:编写⼯具类Tools:作⽤:⽤于驼峰和数据库字段的转换步骤2:⾃定义两个注解,分别⽤于类字段的排除和字义主键步骤3:⾃定义动态sql⽣成类BaseSqlProvider<T>步骤4:编写BaseMapper基类接⼝举例:⽬前项⽬当中使⽤mapper.xml⽂件⽅式对数据库进⾏操作,但是每个⾥边都有增/删/改/查,为了⽅便开发,把这些公共的代码提取出来,不⽤当做基类,不⽤每个Mapper⽂件都写了准备⼯作:1:数据库表CREATE TABLE `t_permission` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '权限ID',`type` int(11) NOT NULL COMMENT '权限类型',`name` varchar(255) NOT NULL COMMENT '权限名称',PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 COMMENT='权限表';2:准备实体类public class TPermissionEntity {@PrimaryKey //下⾯步骤2中⾃定义注解private Integer id;//权限IDprivate Integer type;//权限类型private String name;//权限名称//省略了get,set⽅法....}步骤1:编写⼯具类Tools:作⽤:⽤于驼峰和数据库字段的转换因为类的名称⽤的是驼峰命名,所以这⾥需要转换⼀下import java.util.regex.Matcher;import java.util.regex.Pattern;/** 驼峰名称和下划线名称的相互转换*/public class Tool {private static Pattern linePattern = pile("_(\\w)");/** 下划线转驼峰 */public static String lineToHump(String str) {str = str.toLowerCase();Matcher matcher = linePattern.matcher(str);StringBuffer sb = new StringBuffer();while (matcher.find()) {matcher.appendReplacement(sb, matcher.group(1).toUpperCase());}matcher.appendTail(sb);return sb.toString();}private static Pattern humpPattern = pile("[A-Z]");/** 驼峰转下划线,效率⽐上⾯⾼ */public static String humpToLine(String str) {Matcher matcher = humpPattern.matcher(str);StringBuffer sb = new StringBuffer();while (matcher.find()) {matcher.appendReplacement(sb, "_" + matcher.group(0).toLowerCase());}matcher.appendTail(sb);return sb.toString();}}步骤2:⾃定义两个注解,分别⽤于类字段的排除和字义主键@Target({ElementType.FIELD})@Retention(RetentionPolicy.RUNTIME)public @interface Exclude {}@Target({ElementType.FIELD})@Retention(RetentionPolicy.RUNTIME)public @interface PrimaryKey {String value() default "";}步骤3:⾃定义动态sql⽣成类BaseSqlProvider<T>作⽤:根据传⼊的对象动态获取表名和字段名⽣成动态的sql语句,再执⾏@Insert,@Select,@update,@Delete是直接配置SQL语句，⽽@InsertProvider,@UpdateProvider,@SelectProvider,@DeleteProvider则是通过SQL⼯⼚类及对应的⽅法⽣产SQL语句import ng.reflect.Field;import java.util.ArrayList;import java.util.List;import org.apache.ibatis.annotations.Options;import org.apache.ibatis.jdbc.SQL;import mon.utils.Tool;public class BaseSqlProvider<T> {@Optionspublic String add(T bean) {SQL sql = new SQL();Class clazz = bean.getClass();String tableName = clazz.getSimpleName();String realTableName = Tool.humpToLine(tableName).replaceAll("_entity", "").substring(1);sql.INSERT_INTO(realTableName);List<Field> fields = getFields(clazz);for (Field field : fields) {field.setAccessible(true);String column = field.getName();System.out.println("column:" + Tool.humpToLine(column));sql.VALUES(Tool.humpToLine(column), String.format("#{" + column + ",jdbcType=VARCHAR}"));}return sql.toString();}public String delete(T bean) {SQL sql = new SQL();Class clazz = bean.getClass();String tableName = clazz.getSimpleName();String realTableName = Tool.humpToLine(tableName).replaceAll("_entity", "").substring(1); sql.DELETE_FROM(realTableName);List<Field> primaryKeyField = getPrimarkKeyFields(clazz);if (!primaryKeyField.isEmpty()) {for (Field pkField : primaryKeyField) {pkField.setAccessible(true);sql.WHERE(pkField.getName() + "=" + String.format("#{" + pkField.getName() + "}"));}} else {sql.WHERE(" 1= 2");throw new RuntimeException("对象中未包含PrimaryKey属性");}return sql.toString();}private List<Field> getPrimarkKeyFields(Class clazz) {List<Field> primaryKeyField = new ArrayList<>();List<Field> fields = getFields(clazz);for (Field field : fields) {field.setAccessible(true);PrimaryKey key = field.getAnnotation(PrimaryKey.class);if (key != null) {primaryKeyField.add(field);}}return primaryKeyField;}private List<Field> getFields(Class clazz) {List<Field> fieldList = new ArrayList<>();Field[] fields = clazz.getDeclaredFields();for (Field field : fields) {field.setAccessible(true);Exclude key = field.getAnnotation(Exclude.class);if (key == null) {fieldList.add(field);}}return fieldList;}public String get(T bean) {SQL sql = new SQL();Class clazz = bean.getClass();String tableName = clazz.getSimpleName();String realTableName = Tool.humpToLine(tableName).replaceAll("_entity", "").substring(1); sql.SELECT("*").FROM(realTableName);List<Field> primaryKeyField = getPrimarkKeyFields(clazz);if (!primaryKeyField.isEmpty()) {for (Field pkField : primaryKeyField) {pkField.setAccessible(true);sql.WHERE(pkField.getName() + "=" + String.format("#{" + pkField.getName() + "}"));}} else {sql.WHERE(" 1= 2");throw new RuntimeException("对象中未包含PrimaryKey属性");}System.out.println("getSql:"+sql.toString());return sql.toString();}public String update(T bean) {SQL sql = new SQL();Class clazz = bean.getClass();String tableName = clazz.getSimpleName();String realTableName = Tool.humpToLine(tableName).replaceAll("_entity", "").substring(1);sql.UPDATE(realTableName);List<Field> fields = getFields(clazz);for (Field field : fields) {field.setAccessible(true);String column = field.getName();if (column.equals("id")) {continue;}System.out.println(Tool.humpToLine(column));sql.SET(Tool.humpToLine(column) + "=" + String.format("#{" + column + ",jdbcType=VARCHAR}")); }List<Field> primaryKeyField = getPrimarkKeyFields(clazz);if (!primaryKeyField.isEmpty()) {for (Field pkField : primaryKeyField) {pkField.setAccessible(true);sql.WHERE(pkField.getName() + "=" + String.format("#{" + pkField.getName() + "}"));}} else {sql.WHERE(" 1= 2");throw new RuntimeException("对象中未包含PrimaryKey属性");}System.out.println("updateSql:"+sql.toString());return sql.toString();}}步骤4:编写BaseMapper基类接⼝public interface BaseMapper<T> {//新增⼀条数据@InsertProvider(method = "add",type=BaseSqlProvider.class)@Options(useGeneratedKeys=true)public int add(T bean);//根据主键删除⼀条数据@DeleteProvider(method = "delete",type=BaseSqlProvider.class)public int delete(T bean);//根据主键获取⼀条数据@SelectProvider(method = "get",type=BaseSqlProvider.class)public T get(T bean);//修改⼀条数据@UpdateProvider(method = "update",type=BaseSqlProvider.class)public int update(T bean);}说明：@InsertProvider注解中的type指明⾃定义的SQL⼯⼚类，method是⼯⼚类⾥对应的⽅法,⽅法返回的是对⽅的sql语句到这⾥基类以及它的配置就完成了,接下来,可以使⽤了举例:编写⼀个TPermissionMapper接⼝,实现BaseMapper类,并传⼊⼀个泛型参数,此时这个TPermissionMapper接⼝已经具备了,BaseMapper中基本的增/删/改/查功能.同时TPermissionMapper还可以再写⾃⼰独有的⽅法和mapper.xml⽂件对功能进⾏扩展public interface TPermissionMapper extends BaseMapper<TPermissionEntity>{//List<TPermissionEntity> queryByPage();}在controller当中的应⽤:@Controllerpublic class LoginController {@Autowiredprivate TPermissionMapper tPermissionMapper;//新增@ResponseBody@RequestMapping(value = "/add")public Integer add() {TPermissionEntity permissionEntiry = new TPermissionEntity();permissionEntiry.setName("test");permissionEntiry.setType(3);Integer num = tPermissionMapper.add(permissionEntiry);return num;}//修改@ResponseBody@RequestMapping(value = "/update")public Integer update() {TPermissionEntity permissionEntiry = new TPermissionEntity();permissionEntiry.setId(23);permissionEntiry.setName("test");permissionEntiry.setType(3);Integer num = tPermissionMapper.update(permissionEntiry);return num;}//查询@ResponseBody@RequestMapping(value = "/query")public TPermissionEntity query() {TPermissionEntity tPermissionEntity = new TPermissionEntity();tPermissionEntity.setId(23);tPermissionEntity= (TPermissionEntity) tPermissionMapper.get(tPermissionEntity);return tPermissionEntity;}//删除@ResponseBody@RequestMapping(value = "/delete")public Integer delete() {TPermissionEntity permissionEntiry = new TPermissionEntity();permissionEntiry.setId(22);Integer num = tPermissionMapper.delete(permissionEntiry);return num;}}到此这篇关于mybatis抽取基类BaseMapper增删改查的实现的⽂章就介绍到这了,更多相关mybatis BaseMapper增删改查内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

mybatis增删改查例子一、使用MyBatis对表执行CRUD操作——基于XML的实现1、定义sql映射xml文件userMapper.xml文件的内容如下：1<?xml version="1.0" encoding="UTF-8" ?>2<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd">36<mapper namespace="erMapper">71215<select id="getUser" parameterType="int"16resultType="er">17select * from users where id=#{id}18</select>192021<insert id="addUser" parameterType="er">22insert into users(name,age) values(#{name},#{age})23</insert>242526<delete id="deleteUser" parameterType="int">27delete from users where id=#{id}28</delete>293031<update id="updateUser" parameterType="er">32update users set name=#{name},age=#{age} where id=#{id}33</update>343536<select id="getAllUsers" resultType="er">37select * from users38</select>3940</mapper>单元测试类代码如下：1package me.gacl.test;23import java.util.List;4import er;5import me.gacl.util.MyBatisUtil;6import org.apache.ibatis.session.SqlSession;7import org.junit.Test;89public class TestCRUDByXmlMapper {1011@Test12public void testAdd(){13//SqlSession sqlSession = MyBatisUtil.getSqlSession(false);14SqlSession sqlSession = MyBatisUtil.getSqlSession(true);15/**16* 映射sql的标识字符串，17* erMapper是userMapper.xml文件中mapper标签的namespace属性的值，18* addUser是insert标签的id属性值，通过insert标签的id属性值就可以找到要执行的SQL19*/20String statement = "erMapper.addUser";//映射sql的标识字符串21User user = new User();22user.setName("用户孤傲苍狼");23user.setAge(20);24//执行插入操作25int retResult = sqlSession.insert(statement,user);26//手动提交事务27//mit();28//使用SqlSession执行完SQL之后需要关闭SqlSession29sqlSession.close();30System.out.println(retResult);31}3233@Test34public void testUpdate(){35SqlSession sqlSession = MyBatisUtil.getSqlSession(true);36/**37* 映射sql的标识字符串，38* erMapper是userMapper.xml文件中mapper标签的namespace属性的值，39* updateUser是update标签的id属性值，通过update标签的id属性值就可以找到要执行的SQL 40*/41String statement = "erMapper.updateUser";//映射sql的标识字符串42User user = new User();43user.setId(3);44user.setName("孤傲苍狼");45user.setAge(25);46//执行修改操作47int retResult = sqlSession.update(statement,user);48//使用SqlSession执行完SQL之后需要关闭SqlSession49sqlSession.close();50System.out.println(retResult);51}5253@Test54public void testDelete(){55SqlSession sqlSession = MyBatisUtil.getSqlSession(true);56/**57* 映射sql的标识字符串，58* erMapper是userMapper.xml文件中mapper标签的namespace属性的值，59* deleteUser是delete标签的id属性值，通过delete标签的id属性值就可以找到要执行的SQL60*/61String statement = "erMapper.deleteUser";//映射sql的标识字符串62//执行删除操作63int retResult = sqlSession.delete(statement,5);64//使用SqlSession执行完SQL之后需要关闭SqlSession65sqlSession.close();66System.out.println(retResult);67}6869@Test70public void testGetAll(){71SqlSession sqlSession = MyBatisUtil.getSqlSession();72/**73* 映射sql的标识字符串，74* erMapper是userMapper.xml文件中mapper标签的namespace属性的值，75* getAllUsers是select标签的id属性值，通过select标签的id属性值就可以找到要执行的SQL76*/77String statement = "erMapper.getAllUsers";//映射sql的标识字符串78//执行查询操作，将查询结果自动封装成List<User>返回79List<User> lstUsers = sqlSession.selectList(statement);80//使用SqlSession执行完SQL之后需要关闭SqlSession81sqlSession.close();82System.out.println(lstUsers);83}84 }二、使用MyBatis对表执行CRUD操作——基于注解的实现1、定义sql映射的接口UserMapperI接口的代码如下：1package me.gacl.mapping;23import java.util.List;4import er;5import org.apache.ibatis.annotations.Delete;6import org.apache.ibatis.annotations.Insert;7import org.apache.ibatis.annotations.Select;8import org.apache.ibatis.annotations.Update;910/**11* @author gacl12* 定义sql映射的接口，使用注解指明方法要执行的SQL13*/14public interface UserMapperI {1516//使用@Insert注解指明add方法要执行的SQL17@Insert("insert into users(name, age) values(#{name}, #{age})")18public int add(User user);1920//使用@Delete注解指明deleteById方法要执行的SQL21@Delete("delete from users where id=#{id}")22public int deleteById(int id);2324//使用@Update注解指明update方法要执行的SQL25@Update("update users set name=#{name},age=#{age} where id=#{id}")26public int update(User user);2728//使用@Select注解指明getById方法要执行的SQL29@Select("select * from users where id=#{id}")30public User getById(int id);3132//使用@Select注解指明getAll方法要执行的SQL33@Select("select * from users")34public List<User> getAll();35 }需要说明的是，我们不需要针对UserMapperI接口去编写具体的实现类代码，这个具体的实现类由MyBatis帮我们动态构建出来，我们只需要直接拿来使用即可。