mybatis+maven+父子多模块进行crud以及动态条件查询

使用IDEA创建maven项目，File→New→Project→maven→Next→填写GroupId（例：com.zyl）和ArtifactId(mybatis-demo-parent)→Next，创建完成后，将src目录删除。鼠标右击项目名创建module,就是创建出了子模块。

注意：1.所有类都应该写在包下，如果不写包会出现模块之间依赖了，但是编写代码时使用不了该类的现象

2.添加tomcat后，要将Artifacts中的Output Layout中右边的Artifacts所有内容添加到左边去，不然运行时会出现异常：找不到类和什么异常来着，忘了

3.jstl的<c:if>判断条件是写在${xx == xx}里面的，而不是写成${xx}==xx，当时没注意，一直不知道错哪了

4.@WebServlet("/*")别忘了写/，好多次都是因为/没写报了一些莫名其妙的错误，自己又找不到

创建以下五个子模块，mybatis-demo-util,mybatis-demo-entity,mybatis-demo-dao,mybatis-demo-service,mybatis-demo-web.其中父模块的pom文件内容如下：

<?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.nf</groupId>
    <artifactId>mybatis-firstWork-parent</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>mybatis-demo-util</module>
        <module>mybatis-demo-dao</module>
        <module>mybatis-demo-entity</module>
        <module>mybatis-demo-service</module>
        <module>mybatis-demo-web</module>
    </modules>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
<!--统一使用自定义标签的版本号，方便以后修改，同样的，模块之间的依赖的版本号也使用maven的全局标签<project.version>-->
        <mybatis.version>3.5.2</mybatis.version>
        <mysql.version>5.1.47</mysql.version>
        <junit.version>4.12</junit.version>
        <servlet.version>3.1.0</servlet.version>
        <jstl.version>1.2</jstl.version>
    </properties>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>${mybatis.version}</version>
            </dependency>

            <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>${mysql.version}</version>
            </dependency>
<!--记住，要写scope，junit是test-->
            <!-- https://mvnrepository.com/artifact/junit/junit -->
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>${junit.version}</version>
                <scope>test</scope>
            </dependency>

            <dependency>
                <groupId>javax.servlet</groupId>
                <artifactId>javax.servlet-api</artifactId>
                <version>${servlet.version}</version>
            </dependency>

            <dependency>
                <groupId>javax.servlet</groupId>
                <artifactId>jstl</artifactId>
                <version>${jstl.version}</version>
            </dependency>
        </dependencies>
    </dependencyManagement>
</project>

　　Mysql数据库：

表dept
dept  | CREATE TABLE `dept` (
  `dept_id` int(11) NOT NULL AUTO_INCREMENT,
  `dept_name` char(20) DEFAULT NULL,
  PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8mb4 |
表employee
| employee | CREATE TABLE `employee` (
  `employee_id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_name` char(30) DEFAULT NULL,
  `employee_gender` binary(1) DEFAULT NULL,
  `employee_salary` decimal(10,2) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 |

　　创建相应的实体类。

dao模块中的resources中写四个文件：

mybatis-config.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="db.properties"></properties>

    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <typeAliases>
        <typeAlias type="com.entity.Employee" alias="employee"></typeAlias>
    </typeAliases>

    <environments default="def">
        <environment id="def">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"></property>
                <property name="url" value="${url}"></property>
                <property name="username" value="${username}"></property>
                <property name="password" value="${password}"></property>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="employeeMapper.xml"></mapper>
        <mapper resource="deptMapper.xml"></mapper>
    </mappers>
</configuration>

　　db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/guoqing?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
#url后面还有一串参数
username=root
password=root
#?和?之后的内容可以不用写

　　employeeMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//OTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--自动映射关掉了，虽然说默认是打开的，但是我这里没用，要的话也可以上官网查找-->
<mapper namespace="com.dao.EmployeeDao">
    <resultMap id="employeeResultMap" type="employee">
        <id property="employeeId" column="employee_id"></id>
        <result property="employeeName" column="employee_name"></result>
        <result column="employee_gender" property="employeeGender"></result>
        <result column="employee_salary" property="employeeSalary"></result>
        <result column="dept_name" property="deptName"></result>
    </resultMap>

    <resultMap id="employeeResultOne" type="employee">
        <result column="dept_name" property="deptName"></result>
        <result column="employee_salary" property="employeeSalary"></result>
        <result property="employeeName" column="employee_name"></result>
        <result column="employee_gender" property="employeeGender"></result>
    </resultMap>

    <select id="queryAll" resultMap="employeeResultMap">
        select employee_id,employee_name,employee_gender,employee_salary,dept_name from employee left outer join dept on employee.dept_id = dept.dept_id
    </select>

    <insert id="insert">
        insert into employee(employee_name,employee_gender,employee_salary,dept_id) values(#{arg0},#{arg1},#{arg2},#{arg3})
    </insert>

    <update id="update">
        update employee set employee_name=#{arg0},employee_gender=#{arg1},employee_salary=#{arg2},dept_id=#{arg3} where employee_id=#{arg4}
    </update>

    <delete id="delete">
        delete from employee where employee_id=#{arg0}
    </delete>

    <select id="queryOne" resultMap="employeeResultOne">
        select employee_name,employee_gender,employee_salary,dept_name from employee left outer join dept on employee.dept_id = dept.dept_id where employee_id = #{arg0}
    </select>

    <select id="selectList" resultMap="employeeResultMap">
        select employee_id,employee_name,employee_gender,employee_salary,dept_name from employee inner join dept on employee.dept_id = dept.dept_id where employee.dept_id=#{arg0}
    </select>
</mapper>

　　deptMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//OTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--自动映射关掉了-->
<mapper namespace="com.dao.DeptDao">
    <resultMap id="DeptResultMap" type="com.entity.Dept">
        <id property="deptId" column="dept_id"></id>
        <result property="deptName" column="dept_name"></result>
    </resultMap>

    <select id="queryAll" resultMap="DeptResultMap">
        select dept_id,dept_name from dept
    </select>
</mapper>

　　com.dao包下：

package com.dao;

import com.entity.Employee;

import java.math.BigDecimal;
import java.util.List;

public interface EmployeeDao {

    List<Employee> queryAll();
    int insert(String name, int gender, BigDecimal salary, Integer deptId);
    int update(String name, int gender, BigDecimal salary, Integer deptId, Integer id);
    int delete(Integer id);
    Employee queryOne(Integer employeeId);
    List<Employee> selectList(Integer employeeId);
}

　　

package com.dao;

import com.entity.Employee;
import com.util.MapperFactory;
import org.apache.ibatis.annotations.Mapper;

import java.math.BigDecimal;
import java.util.List;

public class EmployeeDaoImpl implements EmployeeDao{
    @Override
    public List<Employee> queryAll() {
        EmployeeDao employeeDao = MapperFactory.generateMapper(EmployeeDao.class);
        List<Employee> employees = employeeDao.queryAll();
        return employees;
    }

    @Override
    public int insert(String name, int gender, BigDecimal salary, Integer deptId) {
        EmployeeDao employeeDao = MapperFactory.generateMapper(EmployeeDao.class);
        return employeeDao.insert(name, gender, salary, deptId);
    }

    @Override
    public int update(String name, int gender, BigDecimal salary, Integer deptId, Integer id) {
        EmployeeDao employeeDao = MapperFactory.generateMapper(EmployeeDao.class);
        return employeeDao.update(name, gender, salary, deptId, id);
    }

    @Override
    public int delete(Integer id) {
        EmployeeDao employeeDao = MapperFactory.generateMapper(EmployeeDao.class);
        return employeeDao.delete(id);
    }

    @Override
    public Employee queryOne(Integer employeeId) {
        EmployeeDao employeeDao = MapperFactory.generateMapper(EmployeeDao.class);
        Employee employee = employeeDao.queryOne(employeeId);
        return employee;
    }

    @Override
    public List<Employee> selectList(Integer deptId) {
        EmployeeDao employeeDao = MapperFactory.generateMapper(EmployeeDao.class);
        List<Employee> employees = employeeDao.selectList(deptId);
        return employees;
    }

}

　　mybatis-demo-util中使用了代理，让SqlSession在使用后自动关闭

package com.util;

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;

public class SqlSessionFactoryUtil {

    private final static String RESOURCE = "mybatis-config.xml";
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            InputStream inputStream = Resources.getResourceAsStream(RESOURCE);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            throw new DaoException("get SqlSessionFactory failed", e);
        }
        }

    public static SqlSessionFactory getSqlSessionFactory() {
        return sqlSessionFactory;
    }

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession(true);// 这里的true是让sql语句执行后自动提交，如果不写的话，后面的java代码执行sql语句都需要多写一句提交代码
    }
}

　　

package com.util;

import org.apache.ibatis.session.SqlSession;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;

public class MapperInterceptor implements InvocationHandler {

    private SqlSession sqlSession;
    private Object target;

    public MapperInterceptor(SqlSession sqlSession, Object target) {
        this.sqlSession = sqlSession;
        this.target = target;
    }

    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        try {
            return method.invoke(target, args);
        } finally {
            sqlSession.close();
        }
    }
}

　　

package com.util;

import org.apache.ibatis.session.SqlSession;

public class MapperFactory {

    public static <T> T generateMapper(Class<? extends T> clz) {
        SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSession();
        return sqlSession.getMapper(clz);
    }
}

　　mybatis-demo-web的pom文件：

<?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">
    <parent>
        <artifactId>mybatis-firstWork-parent</artifactId>
        <groupId>com.nf</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>mybatis-web</artifactId>

    <packaging>war</packaging>

    <dependencies>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
        </dependency>

        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
        </dependency>

        <dependency>
            <groupId>com.nf</groupId>
            <artifactId>mybatis-service</artifactId>
            <version>${project.version}</version>
        </dependency>
    </dependencies>

<!--打包成war包-->
    <build>
        <finalName>big</finalName>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-war-plugin</artifactId>
                <version>3.2.3</version>
                <configuration>
                    <warSourceDirectory>web</warSourceDirectory>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

　　File→Project Settings→Modules→mybatis-demo-web→+→web→OK变成web项目

添加tomcat，再写相应的Servlet和jsp

最后，关于动态查询：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//OTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.EmployeeDao">
    <resultMap id="employeeResultMap" type="employee">
        <id property="employeeId" column="employee_id"></id>
        <result column="employee_name" property="employeeName"></result>
        <result property="employeeGender" column="employee_gender"></result>
        <result column="employee_salary" property="employeeSalary"></result>
        <result property="deptName" column="dept_name"></result>
    </resultMap>

    <select id="queryAll" resultMap="employeeResultMap">
        select employee_id,employee_name,employee_gender,employee_salary,dept_name from employee e left outer join dept d on e.dept_id = d.dept_id where 1 = 1 ${arg0}
    </select>
</mapper>

　　其中要注意，where 1=1后面的参数一定要写$而不是#，写#的话是转成?的，若是没用条件那么语句就是错误的，而$是完全代替，就不会有这个错误了

package com.dao;

import com.entity.Employee;

import java.util.List;

public interface EmployeeDao {

    List<Employee> queryAll(String sql);
}

　　

package com.dao;

import com.entity.Employee;
import com.util.MapperFactory;

import java.util.List;

public class EmployeeDaoImpl implements EmployeeDao {
    @Override
    public List<Employee> queryAll(String sql) {
        EmployeeDao employeeDao = MapperFactory.generateMapper(EmployeeDao.class);
        List<Employee> employees = employeeDao.queryAll(sql);
        return employees;
    }
}

　　jsp中的<Script>是重点：

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.min.js"></script>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>员工</title>
</head>
<body>
<label>
    <span>性别</span>
    <select id="selGender" class="select">
        <option value="">请选择</option>
        <option value="1">男</option>
        <option value="0">女</option>
    </select>
</label>

<label>
    <span>工资</span>
    <select id="selSalary" class="select">
        <option value="">请选择</option>
        <option value="< 1000">1000以下</option>
        <option value="between 1000 and 3000">1000-3000</option>
        <option value="> 3000">3000以上</option>
    </select>
</label>

<label>
    <span>排序</span>
    <select id="selOrder" class="select">
        <option value="">请选择</option>
        <option value=" order by salary desc">工资降序</option>
        <option value=" order by salary asc">工资升序</option>
    </select>
</label>
<table>
    <tr>
        <th>编号</th>
        <th>姓名</th>
        <th>性别</th>
        <th>工资</th>
        <th>部门</th>
    </tr>

<c:forEach items="${employees}" var="employee">
    <tr>
        <td>${employee.employeeId}</td>
        <td>${employee.employeeName}</td>
        <td>${employee.employeeGender}</td>
        <td>${employee.employeeSalary}</td>
        <td>${employee.deptName}</td>
    </tr>
</c:forEach>
</table>

<script>
    $(".select").on("change", function () {
        $.ajax({
            method: "post",
            url: "${pageContext.request.contextPath}/con",
            data: {
                gender: $("#selGender").val(),
                salary: $("#selSalary").val(),
                order: $("#selOrder").val()
            }
        }).done(function (result) {
            $("table").remove();
            $("body").append(result);
        })
    })
</script>
</body>
</html>

　　转到Servlet

package com.web.servlet;

import com.entity.Employee;
import com.service.EmployeeServiceImpl;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

@WebServlet("/con")
public class ConditionServlet extends HttpServlet {

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String gender = req.getParameter("gender");
        String salary = req.getParameter("salary");
        String order = req.getParameter("order");
        StringBuilder sql = new StringBuilder();
        if (gender != "") {
            sql.append(" and employee_gender = " + gender);
        }
        if (salary != "") {
            sql.append(" and employee_salary " + salary);
        }
        if (order != "") {
            sql.append(order);
        }
        List<Employee> employees = new EmployeeServiceImpl().queryAll(sql.toString());
        req.setAttribute("employees", employees);
        req.getRequestDispatcher("WEB-INF/employee/table.jsp").forward(req, resp);
    }
}

　　这样子就做到了动态修改sql语句，完成了动态条件查询

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<table>
    <tr>
        <th>编号</th>
        <th>姓名</th>
        <th>性别</th>
        <th>工资</th>
        <th>部门</th>
    </tr>

    <c:forEach items="${employees}" var="employee">
        <tr>
            <td>${employee.employeeId}</td>
            <td>${employee.employeeName}</td>
            <td>${employee.employeeGender}</td>
            <td>${employee.employeeSalary}</td>
            <td>${employee.deptName}</td>
        </tr>
    </c:forEach>
</table>