maven-spring-mybatis小型项目开发环境(2)mybatis 集成

    昨天搭建了一个spring的基础开发平台，今天将在spring中集成mybatis。
    既然说到数据库，那就必然要有数据库版本管理软件。我这里使用的是flywaydb，因此脚本中会附上SQL脚本。maven可以集成flyway，但是为了避免pom文件太过复杂，这里使用flywaydb的command line tool。flywaydb官网：
https://flywaydb.org/
    言归正传。
首先，pom中添加连接数据库必要的依赖

<dependency>
	<groupId>commons-dbcp</groupId>
	<artifactId>commons-dbcp</artifactId>
	<version>1.4</version>
	<exclusions>
		<exclusion>
			<artifactId>xercesImpl</artifactId>
			<groupId>xerces</groupId>
		</exclusion>
		<exclusion>
			<artifactId>commons-collections</artifactId>
			<groupId>commons-collections</groupId>
		</exclusion>
	</exclusions>
</dependency>

<dependency>
	<groupId>org.mybatis</groupId>
	<artifactId>mybatis</artifactId>
	<version>3.3.0</version>
</dependency>
<dependency>
	<groupId>org.mybatis</groupId>
	<artifactId>mybatis-spring</artifactId>
	<version>1.1.1</version>
</dependency>

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.13</version>
</dependency>

项目中使用lombok自动生成getset方法，依赖如下：

<dependency>
	<groupId>org.projectlombok</groupId>
	<artifactId>lombok</artifactId>
	<version>1.14.4</version>
</dependency>

rest api所需依赖：

<dependency>
	<groupId>com.fasterxml.jackson.core</groupId>
	<artifactId>jackson-databind</artifactId>
	<version>2.5.0</version>
</dependency>
<dependency>
	<groupId>com.fasterxml.jackson.core</groupId>
	<artifactId>jackson-core</artifactId>
	<version>2.5.0</version>
</dependency>
<dependency>
	<groupId>com.fasterxml.jackson.core</groupId>
	<artifactId>jackson-annotations</artifactId>
	<version>2.5.0</version>
</dependency>

resources文件夹中增加配置文件：jdbc.properties

db.host=localhost
db.name=salesVolume
db.port=3306
db.user=root
db.password=root

JSTL相关依赖

<dependency>
	<groupId>javax.servlet</groupId>
	<artifactId>jstl</artifactId>
	<version>1.2</version>
</dependency>
<dependency>
	<groupId>taglibs</groupId>
	<artifactId>standard</artifactId>
	<version>1.1.2</version>
</dependency>

spring中配置数据源：

<bean id="propertyConfigurer"
	  class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
	<property name="ignoreUnresolvablePlaceholders" value="true"/>
	<property name="locations">
		<list>
			<value>classpath:jdbc.properties</value>
		</list>
	</property>
</bean>

<!--suppress SpringFacetInspection -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
	<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
	<property name="url"
			  value="jdbc:mysql://${db.host}:${db.port}/${db.name}?createDatabaseIfNotExist=true&amp;useUnicode=true&amp;characterEncoding=utf-8&amp;autoReconnect=true"/>
	<property name="username" value="${db.user}"/>
	<property name="password" value="${db.password}"/>
	<property name="maxActive" value="10"/>
	<property name="minIdle" value="2"/>
	<property name="maxWait" value="300"/>
</bean>

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
	<property name="dataSource" ref="dataSource"/>
	<property name="mapperLocations" value="classpath*:sql/*-sql.xml"/>
</bean>

<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
	<constructor-arg index="0" ref="sqlSessionFactory"/>
	<constructor-arg index="1" value="BATCH"/>
</bean>

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
	<property name="dataSource" ref="dataSource"/>
</bean>

别忘了把service和dao放到扫描目录中

<context:component-scan base-package="sales.volume.action"/>
<context:component-scan base-package="sales.volume.service.impl"/>
<context:component-scan base-package="sales.volume.dao.impl"/>

为了同时支持rest api和原有视图配置，需要修改原有视图配置：
老的代码：

<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
	<property name="prefix" value="/"/>
	<property name="suffix" value=".jsp"/>
</bean>

新的代码：

<bean class="org.springframework.web.servlet.view.ContentNegotiatingViewResolver">
	<property name="order" value="1"/>
	<property name="viewResolvers">
		<list>
			<bean class="org.springframework.web.servlet.view.BeanNameViewResolver"/>
			<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
				<property name="prefix" value="/"/>
				<property name="suffix" value=".jsp"/>
			</bean>
		</list>
	</property>
	<property name="defaultViews">
		<list>
			<bean class="org.springframework.web.servlet.view.json.MappingJackson2JsonView"/>
		</list>
	</property>
</bean>

在web.xml中新增一个servlet mapping用于rest api，代码：

<servlet-mapping>
	<servlet-name>dispatcher</servlet-name>
	<url-pattern>*.action</url-pattern>
</servlet-mapping>

在resource目录中新建子目录sql，新增文件demo-sql.xml，内容如下：

<?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">
<mapper namespace="demoDao">

    <!--  新增记录  -->
    <insert id="add" parameterType="sales.volume.entity.DemoObject" useGeneratedKeys="true" keyProperty="id">
        insert into r_demoObject(name, createTime, updateTime) values(#{name}, now(), now())
    </insert>

    <!--  查询单条记录  -->
    <select id="get" parameterType="int" resultType="sales.volume.entity.DemoObject">
        select * from r_demoObject where id = #{id}
    </select>

    <select id="getAll" resultType="sales.volume.entity.DemoObject">
        select * from r_demoObject
    </select>

    <!--  修改记录  -->
    <update id="update" parameterType="sales.volume.entity.DemoObject">
        update r_demoObject set name = #{name}, updateTime=now() where id=#{id}
    </update>

    <!--  删除记录  -->
    <delete id="delete" parameterType="int">
        delete from r_demoObject where id=#{id}
    </delete>

</mapper>

再附上action代码：

package sales.volume.action;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.servlet.ModelAndView;
import sales.volume.entity.DemoObject;
import sales.volume.entity.StateCode;
import sales.volume.service.DemoService;

import java.util.List;

@RestController
@RequestMapping("/demo")
public class DemoAction {

    @Autowired
    private DemoService demoService;

    @RequestMapping(value = "view/{id}", method = RequestMethod.GET)
    public ModelAndView view(
            @PathVariable(value = "id") int id
    ) {
        ModelAndView mav = new ModelAndView();
        mav.setViewName("viewDemo");
        DemoObject demoObject = demoService.get(id);
        mav.getModel().put("demoObject", demoObject);
        return mav;
    }

    @RequestMapping(value = "viewAll", method = RequestMethod.GET)
    public ModelAndView viewAll(
    ) {
        ModelAndView mav = new ModelAndView();
        mav.setViewName("viewAll");
        List<DemoObject> data = demoService.getAll();
        mav.getModel().put("data", data);
        return mav;
    }

    @RequestMapping(value = "add", method = RequestMethod.POST)
    public ResponseEntity<StateCode> add(
            @RequestParam(value = "name", required = false) String name
    ) {
        DemoObject demoObject = new DemoObject();
        demoObject.setName(name);
        demoService.add(demoObject);
        return new ResponseEntity(new StateCode(StateCode.SUCCESS_CODE, StateCode.SUCCESS_MSG), HttpStatus.OK);
    }

    @RequestMapping(value = "delete", method = RequestMethod.POST)
    public ResponseEntity<StateCode> delete(
            @RequestParam(value = "id", required = true) int id
    ) {
        demoService.delete(id);
        return new ResponseEntity(new StateCode(StateCode.SUCCESS_CODE, StateCode.SUCCESS_MSG), HttpStatus.OK);
    }

    @RequestMapping(value = "update", method = RequestMethod.POST)
    public ResponseEntity<StateCode> update(
            @RequestBody DemoObject[] demoObjects
    ) {
        for (DemoObject demoObject : demoObjects) {
            demoService.update(demoObject);
        }
        return new ResponseEntity(new StateCode(StateCode.SUCCESS_CODE, StateCode.SUCCESS_MSG), HttpStatus.OK);
    }
}

对应的前台页面代码：
demoManager.jsp

<html>
<head>
    <title>demo管理</title>
    <style type="text/css">
        .border {
            border: solid 1px #AAAAAA;
            margin-bottom: 50px;
            display: table;
        }
    </style>
    <script type="text/javascript" src="js/jquery-2.2.0.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $("#addButton").click(function () {
                var data = {};
                data.name = $("#addName").val();
                $.post("demo/add.action", data, function (data, status) {
                    alert(data.msg);
                });
            });

            $("#deleteButton").click(function () {
                var data = {};
                data.id = $("#deleteId").val();
                $.post("demo/delete.action", data, function (data, status) {
                    alert(data.msg);
                });
            });

            $("#viewButton").click(function(){
                var id = $("#viewId").val();
                location.href = ("demo/view/" + id + ".jhtml");
            });

            $("#allViewButton").click(function(){
                location.href = ("demo/viewAll.jhtml");
            });
        });
    </script>
</head>
<body>
<div class="border">
    <div>新增</div>
    <div>
        <input id="addName"/>
        <br>
        <input id="addButton" type="button" value="新增">
    </div>
</div>

<div class="border">
    <div>删除</div>
    <div>
        <input id="deleteId"/>
        <br>
        <input id="deleteButton" type="button" value="删除">
    </div>
</div>

<div class="border">
    <div>查看</div>
    <div>
        <input id="viewId"/>
        <br>
        <input id="viewButton" type="button" value="查看">
        <input id="allViewButton" type="button" value="查看全部">
    </div>
</div>

</body>
</html>

viewAll.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<html>
<head>
    <title>View All</title>
    <style type="text/css">
        #data {
            border: solid 1px #add9c0;
            border-collapse: collapse;
        }

        .tdB {
            border: solid 1px #add9c0;
            padding: 2px 5px;
        }
    </style>
    <script type="text/javascript" src="../js/jquery-2.2.0.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $("#save").click(function () {
                var data = $("#data").find('tr');
                var postData = [];
                for(var i = 1; i < data.length; i++) {
                    var row = {};
                    var tr = data[i];
                    row.id = $(tr).children("td:eq(0)").text();
                    row.name = $(tr).children("td:eq(1)").children("input").val();
                    postData.push(row);
                }

                $.ajax({
                    type: "POST",
                    url: "update.action",
                    contentType: "application/json; charset=utf-8",
                    data: JSON.stringify(postData),
                    dataType:"json",
                    error: function (xhr, state, stateCode) {
                        var data = $.parseJSON(xhr.responseText);
                        mini.MessageBox.alert(data.msg, "提示");
                    },
                    success: function (data, state, xhr) {
                        alert(data.msg);
                    }
                });
            });
        });
    </script>
</head>
<body>
<table id="data" cellspacing="0">
    <tr>
        <td class="tdB">ID</td>
        <td class="tdB">Name</td>
        <td class="tdB">Create Time</td>
        <td class="tdB">Update Time</td>
    </tr>
    <c:forEach var="ite" items="${data}">
        <tr>
            <td class="tdB">${ite.id}</td>
            <td class="tdB"><input value="${ite.name}"></td>
            <td class="tdB">${ite.createTime}</td>
            <td class="tdB">${ite.updateTime}</td>
        </tr>
    </c:forEach>
</table>
<input id="save" type="button" value="保存"/>

</body>
</html>

viewDemo.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Edit Demo ${demoObject.id}</title>
</head>
<body>
<div>
    <p>${demoObject.id}</p>

    <p>${demoObject.name}</p>

    <p>${demoObject.createTime}</p>

    <p>${demoObject.updateTime}</p>
</div>
</body>
</html>

注意：批量修改时，用到了一种比较一种新的请求方式：json post，就是可以将前台JSON对象直接传到后台的一种提交方式。这种请求方式的服务器端代码：

@RequestMapping(value = "update", method = RequestMethod.POST)
public ResponseEntity<StateCode> update(
		@RequestBody DemoObject[] demoObjects
) {
	for (DemoObject demoObject : demoObjects) {
		demoService.update(demoObject);
	}
	return new ResponseEntity(new StateCode(StateCode.SUCCESS_CODE, StateCode.SUCCESS_MSG), HttpStatus.OK);
}

请注意参数，只能有一个参数，用@RequestBody注解修饰，必须是集合或者数组类型的参数。

对应的，前端页面将要传递到后台的JSON对象转换为字符串后，当做POST参数传递到后台，注意一定要设置contentType为 "application/json"。代码：

$.ajax({
	type: "POST",
	url: "update.action",
	contentType: "application/json; charset=utf-8",
	data: JSON.stringify(postData),
	dataType:"json",
	error: function (xhr, state, stateCode) {
		var data = $.parseJSON(xhr.responseText);
		mini.MessageBox.alert(data.msg, "提示");
	},
	success: function (data, state, xhr) {
		alert(data.msg);
	}
});

注意：JSON.stringify(postData)可以将postData对象转换为JSON字符串。chrome支持此function，IE不支持。其它浏览器自行探索。另外，可在页面中引用 json2.js(http://json.bloople.net/)来解决此兼容性问题。

实现效果：
在demoManager页面中的新增栏目下，输入框中输入姓名，能正确添加到数据库。

在删除跟查看栏目下，输入被删除/查看对象的ID，可以删除/浏览对象内容。
点击查看全部，可以看到一个列表，编辑名称，单击保存可以修改到数据库。


tips：mybatis中$和#的区别：
MyBatis/Ibatis中#和$的区别
1. #将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号。如：order by #user_id#，如果传入的值是111,那么解析成sql时的值为order by "111", 如果传入的值是id，则解析成的sql为order by "id".

2. $将传入的数据直接显示生成在sql中。如：order by $user_id$，如果传入的值是111,那么解析成sql时的值为order by user_id,  如果传入的值是id，则解析成的sql为order by id.

3. #方式能够很大程度防止sql注入。

4.$方式无法防止Sql注入。

5.$方式一般用于传入数据库对象，例如传入表名.

6.一般能用#的就别用$.