SSM练手-增删改查-1-搭建

　　学完了SSM三大框架后，需要一个简单的项目来练练手。接下来我就会带大家详细的做一个应用Spring+SpringMVC+Mybatis三大框架的练手项目。

本项目采用的IDE工具是Intellij IDEA, 使用MAVEN来做jar包的管理工具。如有不会，自行了解。

 

一、创建MAVEN工程，引入项目依赖的jar包，编写SSM需要的配置文件。

　　首先配置好文件，运行处hello world。详细介绍看博文：http://blog.csdn.net/noaman_wgs/article/details/53893948。

　　配置后的项目结构如下：

　　

　　启动项目后，运行项目，页面上显示Hello success...表示成功。

二、数据库建表

SQL语句为：

DROP TABLE IF EXISTS `tbl_emp`;
CREATE TABLE `tbl_emp`(
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `gender` VARCHAR(64) NOT NULL DEFAULT '',
  `name` VARCHAR(64) NOT NULL DEFAULT '',
  `eamil` VARCHAR(128) NOT NULL DEFAULT '',
  `department_id` VARCHAR(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tbl_dept`;
CREATE TABLE `tbl_emp`(
  `dept_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `dept_name` VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

三、使用Mybatis逆向工程Mybatis Generator 生成对应的bean 和 mapper:

1 pom.xml中配置：

  <!--mybatis 逆向工程插件-->
      <plugin>
        <groupId>org.mybatis.generator</groupId>
        <artifactId>mybatis-generator-maven-plugin</artifactId>
        <version>1.3.2</version>
        <configuration>
          <verbose>true</verbose>
          <overwrite>true</overwrite>
        </configuration>
      </plugin>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <configuration>
          <source>1.7</source>
          <target>1.7</target>
        </configuration>
      </plugin>

2 resources下新建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>

    <!--
        出现错误：Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
        解决办法：将本地的MAVEN仓库中的mysql驱动引入进来
    -->
    <classPathEntry location="C:\Users\Administrator\.m2\repository\mysql\mysql-connector-java\5.1.6\mysql-connector-java-5.1.6.jar"/>

    <context id="mysqlgenerator" targetRuntime="MyBatis3">
        <!--不生成注释-->
        <commentGenerator>
            <property name="suppressAllComments" value="true" />
        </commentGenerator>
        <!-- 配置数据库连接 -->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/ssm_demo"
                        userId="root"
                        password="920614" />

        <!-- 指定javaBean生成的位置 -->
        <javaModelGenerator targetPackage="com.wgs.domain" targetProject="src/main/java" >
            <!-- 在targetPackage的基础上，根据数据库的schema再生成一层package，最终生成的类放在这个package下，默认为false -->
            <property name="enableSubPackages" value="true" />
            <!-- 设置是否在getter方法中，对String类型字段调用trim()方法 -->
            <property name="trimStrings" value="true" />
        </javaModelGenerator>

        <!--指定sql映射文件生成的位置 -->
        <sqlMapGenerator targetPackage="com.wgs.dao" targetProject="src/main/resources" >
            <property name="enableSubPackages" value="true" />
        </sqlMapGenerator>
        <!-- 指定dao接口生成的位置，mapper接口 -->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.wgs.dao" targetProject="src/main/java" >
            <property name="enableSubPackages" value="true" />
        </javaClientGenerator>

        <!-- table表生成对应的DoaminObject -->
        <table tableName="tbl_emp" domainObjectName="Employee"></table>
        <table tableName="tbl_dept" domainObjectName="Department"></table>

    </context>

</generatorConfiguration>

3 使用mvn命令来运行：mybatis-generator:generate -e

 具体操作如下：

点击运行没有错误后，表示成功。

生成的主要方法有：

//根据查询条件统计
int countByExample(EmployeeExample example);
//根据查询条件删除
int deleteByExample(EmployeeExample example);
//根据主键删除
int deleteByPrimaryKey(Integer empId);
//插入
int insert(Employee record);
//选择性插入
int insertSelective(Employee record);
//根据查询条件查询，得到list
List<Employee> selectByExample(EmployeeExample example);
//根据主键查询，得到一个对象
Employee selectByPrimaryKey(Integer empId);
//选择性更新
int updateByExampleSelective(@Param("record") Employee record, @Param("example") EmployeeExample example);
//更新
int updateByExample(@Param("record") Employee record, @Param("example") EmployeeExample example);
//根据主键选择性更新
int updateByPrimaryKeySelective(Employee record);
//根据主键更新
int updateByPrimaryKey(Employee record);

 

四、联合查询

Employee表中的d_id 与 Department 表中的 dept_id 相关联。

代码中如何实现联合查询呢？

1. 首先Employee实体类中需要加入Department信息：

 private Department department;

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

2. EmployeeMapper.java

   　　加入带有部门查询信息的方法：

//带有部门信息的查询
    List<Employee> selectByExampleWithDept(EmployeeExample example);
    //带有部门信息的查询，得到一个对象
    Employee selectByPrimaryKeyWithDept(Integer empId);

3. EmployeeMapper.xml:

　　带有部门的返回信息：　

<resultMap id="WithDeptBaseResultMap" type="com.wgs.domain.Employee">
     <id column="emp_id" property="empId" jdbcType="INTEGER"/>
    <result column="emp_name" property="empName" jdbcType="VARCHAR"/>
    <result column="gender" property="gender" jdbcType="CHAR"/>
    <result column="email" property="email" jdbcType="VARCHAR"/>
    <result column="d_id" property="dId" jdbcType="VARCHAR"/>
    <!-- 指定联合查询出的部门字段的封装 -->
    <association property="department" jdbcType="com.wgs.domain.Department">
        <id column="dept_id" property="deptId"/>
        <result column="dept_name" property="deptName"/>
    </association>
  </resultMap>

　　查询列：

<sql id="WithDept_Column_List" >
    e.emp_id, e.emp_name, e.gender, e.eamil, e.d_id, d.dept_id, d.dept_name
  </sql>

两个联合查询的具体实现：

 <select id="selectByExampleWithDept" resultMap="WithDeptBaseResultMap">
    select
    <if test="distinct" >
      distinct
    </if>
    <include refid="WithDept_Column_List" />
    from tbl_emp e
    left join tbl_dept d on e.`d_id`=d.`dept_id`
    <if test="_parameter != null" >
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null" >
      order by ${orderByClause}
    </if>
  </select>

  <select id="selectByPrimaryKeyWithDept" resultMap="WithDeptBaseResultMap">
    select
    <if test="distinct" >
      distinct
    </if>
    <include refid="WithDept_Column_List" />
    from tbl_emp e
    left join tbl_dept d on e.`d_id`=d.`dept_id`
    where emp_id = #{empId, jdbcType=Integer}
  </select>

五、测试

新建test包：

 

package com.wgs.test;

import com.wgs.domain.Department;
import com.wgs.domain.DepartmentExample;
import com.wgs.domain.Employee;
import com.wgs.mapper.DepartmentMapper;
import com.wgs.mapper.EmployeeMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.UUID;

/**
 * Created by GenshenWang.nomico on 2017/6/8.
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:applicationContext.xml"})
public class MapperTest {

    @Autowired
    DepartmentMapper departmentMapper;
    @Autowired
    EmployeeMapper employeeMapper;
    @Autowired
    SqlSession sqlSession;

    @Test
    public void testCRUD(){
        //测试插入部门信息
       /* departmentMapper.insertSelective(new Department(null, "开发部"));
        departmentMapper.insertSelective(new Department(null, "运营部"));
        departmentMapper.insertSelective(new Department(null, "人力资源部"));
        departmentMapper.insertSelective(new Department(null, "后勤部"));
        departmentMapper.insertSelective(new Department(null, "侯aa部"));
        departmentMapper.insertSelective(new Department(null, "侯a222a部"));*/
        //测试删除
       /* departmentMapper.deleteByPrimaryKey(22);
        DepartmentExample departmentExample = new DepartmentExample();
        departmentExample.createCriteria().andDeptNameEqualTo("侯a222a部");
        departmentMapper.deleteByExample(departmentExample);*/

       //测试插入人员信息
        /*Employee employee = new Employee(null, "Hebe", "男", "Hebe@qq.com", 1);
        employeeMapper.insertSelective(employee);*/
        //批量插入
        EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
        for (int i = 0; i < 1000; i++){
            String uid = UUID.randomUUID().toString().substring(0, 5);
            employeeMapper.insertSelective(new Employee(null, uid, "M", uid + "@qq.com", 1));
        }


    }
}

 

 这里值得注意的地方是批量插入，批量插入的时候需要获取sqlsession:

    <!--
        2. 配置和MyBatis的整合
    -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!-- 指定mybatis全局配置文件的位置 -->
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <!-- 配置一个可以执行批量的sqlSession -->
    <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory"></constructor-arg>
        <constructor-arg name="executorType" value="BATCH"></constructor-arg>
    </bean>