mybatis动态SQL和generator自动生成代码
mybatis动态SQL
1. 导入依赖pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.icis</groupId>
<artifactId>mybatis03</artifactId>
<version>1.0-SNAPSHOT</version>
<!--统一定义版本号-->
<properties>
<spring.version>4.3.9.RELEASE</spring.version>
<junit.version>4.13</junit.version>
<mysql.version>5.1.39</mysql.version>
<druid.version>1.0.9</druid.version>
</properties>
<dependencies>
<!--导入Spring的核心依赖包-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<!--导入Spring-aspects-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>${spring.version}</version>
</dependency>
<!--导入junit-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>${junit.version}</version>
</dependency>
<!--Spring整合junit依赖包-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
</dependency>
<!--导入Spring对jdbc操作依赖的包-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<!--Spring对事务的支持-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.version}</version>
</dependency>
<!--数据库驱动依赖包-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<!--数据库连接池依赖包-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
</dependency>
<!-- mybatis依赖 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.0</version>
</dependency>
<!-- MySQL数据库依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.26</version>
</dependency>
<!--日志 start-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!--日志end-->
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.2</version>
<configuration>
<!--配置文件的位置--> <configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
<verbose>true</verbose>
<overwrite>true</overwrite>
</configuration>
<executions>
<execution>
<id>Generate MyBatis Artifacts</id>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.2</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</build>
</project>
2. 新建接口EmpDao.java
package com.icis.dao;
import com.icis.pojo.Empl;
import org.apache.ibatis.annotations.Insert;
import java.util.List;
public interface EmpDao {
Empl getEmpById(Integer empId);
// Integer empId;
// String empName;
// Double empSalary;
// Date empIntime;
// Integer empDeptId;
@Insert("INSERT INTO emp(emp_id, emp_name, emp_salary, emp_intime, emp_dept_id) VALUES (NULL, #{empName}, #{empSalary}, #{empIntime}, #{empDeptId})")
Integer insertEmpByEmp(Empl empl);
List<Empl> getAllEmp(Empl empl);
//批量添加数据
Integer batchInsertEmp(List<Empl> list);
//批量删除
Integer batchDeleteEmpById(List<Integer> list);
}
4. 新建EmpDao.xml
1. 使用where拼接
<select id="getAllEmp" parameterType="com.icis.pojo.Empl" resultType="com.icis.pojo.Empl">
-- 使用<where></where>拼接动态sql
SELECT * FROM emp
<where>
<if test="empName!='' and empName!=null">
AND empName LIKE #{empName}
</if>
<if test="empSex!='' and empSex!=null">
AND empSex = #{empSex}
</if>
</where>
</select>
2. 动态批量删除 delete
<!--动态sql批量删除-->
<!--delete from where id in(1, 2, 3)-->
<delete id="batchDeleteEmpById" parameterType="list">
DELETE FROM emp WHERE emp_id
<foreach collection="list" item="id" separator="," open="IN(" close=") ">
#{id}
</foreach>
</delete>
3. sql片段引入
<!--sql片段-->
<sql id="myselect">
select * from
</sql>
<!--引入sql片段-->
<select id="getEmpById" parameterType="int" resultType="com.icis.pojo.Empl">
<include refid="myselect"></include> emp WHERE emp_id=#{empId}
</select>
4. 动态sql循环遍历list插入
<!--sql片段-->
<sql id="myselect">
select * from
</sql>
<!--引入sql片段-->
<select id="getEmpById" parameterType="int" resultType="com.icis.pojo.Empl">
<include refid="myselect"></include> emp WHERE emp_id=#{empId}
</select>
<?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">
<!--
namespace 相当于java语言中的包 (用以防止sql语句名称冲突)实现sql语句隔离
namespace 一定要和接口名UserDao.java相同
-->
<mapper namespace="com.icis.dao.EmpDao">
<!--id要和函数名相同 resultType="user" 可以使用别名-->
<select id="getAllEmp" parameterType="com.icis.pojo.Empl" resultType="com.icis.pojo.Empl">
-- 使用<where></where>拼接动态sql
SELECT * FROM emp
<where>
<if test="empName!='' and empName!=null">
AND empName LIKE #{empName}
</if>
<if test="empSex!='' and empSex!=null">
AND empSex = #{empSex}
</if>
</where>
</select>
<!--动态sql批量删除-->
<!--delete from where id in(1, 2, 3)-->
<delete id="batchDeleteEmpById" parameterType="list">
DELETE FROM emp WHERE emp_id
<foreach collection="list" item="id" separator="," open="IN(" close=") ">
#{id}
</foreach>
</delete>
<!--sql片段-->
<sql id="myselect">
select * from
</sql>
<!--引入sql片段-->
<select id="getEmpById" parameterType="int" resultType="com.icis.pojo.Empl">
<include refid="myselect"></include> emp WHERE emp_id=#{empId}
</select>
<!--动态sql循环遍历list插入-->
<insert id="batchInsertEmp" parameterType="list">
INSERT into emp VALUES
<foreach collection="list" item="emp" separator=",">
(null, #{emp.empName}, #{emp.empSalary}, #{emp.empIntime}, #{emp.empDeptId}, #{emp.empSex})
</foreach>
</insert>
<!--List<User> getUserByName(@Param("vo") QueryVo vo);-->
<!--<select id="getUserByName" resultType="com.icis.pojo.User">-->
<!--SELECT * FROM user WHERE username LIKE #{vo.username} limit #{vo.idx},#{vo.pageSize}-->
<!--</select>-->
<!--通过resultMap把User字段和数据库字段对应起来-->
<!--<resultMap id="userMap" type="com.icis.pojo.User">-->
<!--<id column="id" property="id"></id> <!–主键用id–>-->
<!--<result column="username" property="username"></result>-->
<!--<result column="birthday" property="birthday"></result>-->
<!--<result column="sex" property="sex"></result>-->
<!--<result column="address" property="address"></result>-->
<!--</resultMap>-->
<!--<select id="getUserByName" resultMap="userMap">-->
<!--SELECT * FROM user WHERE username LIKE #{vo.username} limit #{vo.idx},#{vo.pageSize}-->
<!--</select>-->
</mapper>
使用mybatis_generator自动生成mapper
1. 引入maven依赖
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.2</version>
</dependency>
完整pom.xml
如下
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.icis</groupId>
<artifactId>mybatis03</artifactId>
<version>1.0-SNAPSHOT</version>
<!--统一定义版本号-->
<properties>
<spring.version>4.3.9.RELEASE</spring.version>
<junit.version>4.13</junit.version>
<mysql.version>5.1.39</mysql.version>
<druid.version>1.0.9</druid.version>
</properties>
<dependencies>
<!--导入Spring的核心依赖包-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<!--导入Spring-aspects-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>${spring.version}</version>
</dependency>
<!--导入junit-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>${junit.version}</version>
</dependency>
<!--Spring整合junit依赖包-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
</dependency>
<!--导入Spring对jdbc操作依赖的包-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<!--Spring对事务的支持-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.version}</version>
</dependency>
<!--数据库驱动依赖包-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<!--数据库连接池依赖包-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
</dependency>
<!-- mybatis依赖 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.0</version>
</dependency>
<!-- MySQL数据库依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.26</version>
</dependency>
<!--日志 start-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!--日志end-->
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.2</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.2</version>
<configuration>
<!--配置文件的位置--> <configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
<verbose>true</verbose>
<overwrite>true</overwrite>
</configuration>
<executions>
<execution>
<id>Generate MyBatis Artifacts</id>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.2</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</build>
</project>
2. 新建配置文件generatorConfig.xml
并修改输出目录和目标表
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!--<properties resource="jdbc.properties"></properties>-->
<!--mysql 连接数据库jar 这里选择自己本地位置-->
<context id="testTables" targetRuntime="MyBatis3">
<commentGenerator>
<!-- 是否去除自动生成的注释 true:是 : false:否 -->
<property name="suppressAllComments" value="true" />
</commentGenerator>
<!--数据库连接的信息:驱动类、连接地址、用户名、密码 -->
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/db4" userId="root"
password="root">
</jdbcConnection>
<!-- 默认false,把JDBC DECIMAL 和 NUMERIC 类型解析为 Integer,为 true时把JDBC DECIMAL 和
NUMERIC 类型解析为java.math.BigDecimal -->
<javaTypeResolver>
<property name="forceBigDecimals" value="false" />
</javaTypeResolver>
<!-- targetProject:生成PO类的位置 -->
<javaModelGenerator targetPackage="com.icis.pojo"
targetProject="C:\Users\majiao\Desktop\majiao">
<!-- enableSubPackages:是否让schema作为包的后缀 -->
<property name="enableSubPackages" value="false" />
<!-- 从数据库返回的值被清理前后的空格 -->
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!-- targetProject:mapper映射文件生成的位置
如果maven工程只是单独的一个工程,targetProject="src/main/java"
若果maven工程是分模块的工程,targetProject="所属模块的名称",例如:
targetProject="ecps-manager-mapper",下同-->
<sqlMapGenerator targetPackage="com.icis.mapper"
targetProject="C:\Users\majiao\Desktop\majiao">
<!-- enableSubPackages:是否让schema作为包的后缀 -->
<property name="enableSubPackages" value="false" />
</sqlMapGenerator>
<!-- targetPackage:mapper接口生成的位置 -->
<javaClientGenerator type="XMLMAPPER"
targetPackage="com.icis.mapper"
targetProject="C:\Users\majiao\Desktop\majiao">
<!-- enableSubPackages:是否让schema作为包的后缀 -->
<property name="enableSubPackages" value="false" />
</javaClientGenerator>
<!-- 指定数据库表 -->
<table schema="" tableName="user"></table>
<table schema="" tableName="orders"></table>
</context>
</generatorConfiguration>
3. 新建java文件GeneratorSqlmap.java
编译运行
即可在输出目录看见mapper
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.exception.XMLParserException;
import org.mybatis.generator.internal.DefaultShellCallback;
public class GeneratorSqlmap {
public void generator() throws Exception{
List<String> warnings = new ArrayList<String>();
boolean overwrite = true;
//指定 逆向工程配置文件
File configFile = new File("G:\\Xubuntu_Work_Space\\From_Xubuntu\\codeTest_2019_2_21\\IDEA\\Maven_Pojos\\mybatis03自动生成dao层\\src\\main\\resources\\generatorConfig.xml");
ConfigurationParser cp = new ConfigurationParser(warnings);
Configuration config = cp.parseConfiguration(configFile);
DefaultShellCallback callback = new DefaultShellCallback(overwrite);
MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config,
callback, warnings);
myBatisGenerator.generate(null);
}
public static void main(String[] args) throws Exception {
try {
GeneratorSqlmap generatorSqlmap = new GeneratorSqlmap();
generatorSqlmap.generator();
} catch (Exception e) {
e.printStackTrace();
}
}
}
4. 把生成的实体类和mapper.xml
复制到对应目录即可
最后是测试代码
import com.icis.mapper.OrdersMapper;
import com.icis.mapper.UserMapper;
import com.icis.pojo.Orders;
import com.icis.pojo.OrdersExample;
import com.icis.pojo.User;
import com.icis.pojo.UserExample;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class TestA {
SqlSessionFactory fac = null;
SqlSession sqlSession = null;
UserMapper userMapper;
OrdersMapper ordersMapper;
@Before
public void init() throws IOException {
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
fac = builder.build(Resources.getResourceAsStream("mybatis-config.xml"));
sqlSession = fac.openSession();
userMapper = sqlSession.getMapper(UserMapper.class);
ordersMapper = sqlSession.getMapper(OrdersMapper.class);
}
@Test
public void test2() {
User user = userMapper.selectByPrimaryKey(1);
System.out.println(user);
}
@Test
public void test4() {
UserExample userExample = new UserExample();
UserExample.Criteria criteria = userExample.createCriteria();
int ret = userMapper.countByExample(userExample);
System.out.println(ret);
}
@Test
public void test3() { //模糊查询
UserExample userExample = new UserExample();
UserExample.Criteria criteria = userExample.createCriteria();
criteria.andUsernameLike("%明%");
criteria.andAddressLike("%州%");
List<User> li = userMapper.selectByExample(userExample);
System.out.println(li);
}
@After
public void closeAll() {
sqlSession.commit();
sqlSession.close();
}
// 查询用户订单
@Test
public void test5() {
UserExample userExample = new UserExample();
List<User> users = userMapper.selectByExample(userExample);
System.out.println(users);
for (User user : users) {
OrdersExample ordersExample = new OrdersExample();
OrdersExample.Criteria ordersExampleCriteria = ordersExample.createCriteria();
ordersExampleCriteria.andUserIdEqualTo(user.getId());
List<Orders> orders = ordersMapper.selectByExample(ordersExample);
System.out.println(user.getUsername());
for (Orders order : orders) {
System.out.println(" " + order);
}
}
}
}