实训三笔记 mybatis动态SQL和generator自动生成代码

目录

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> &lt;!&ndash;主键用id&ndash;&gt;-->
        <!--<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:UsersmajiaoDesktopmajiao">
			<!-- 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:UsersmajiaoDesktopmajiao">
			<!-- enableSubPackages:是否让schema作为包的后缀 -->
			<property name="enableSubPackages" value="false" />
		</sqlMapGenerator>
		<!-- targetPackage：mapper接口生成的位置 -->
		<javaClientGenerator type="XMLMAPPER"
							 targetPackage="com.icis.mapper"
							 targetProject="C:UsersmajiaoDesktopmajiao">
			<!-- 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复制到对应目录即可

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/majiao61/p/wuzaiyuan.html