登录
当前位置： 首页 > 工具软件 > spring data mybatis mini > 使用案例 >

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

通奕
2023-12-01
昨天搭建了一个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&useUnicode=true&characterEncoding=utf-8&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页面中的新增栏目下，输入框中输入姓名，能正确添加到数据库。
[img]http://dl2.iteye.com/upload/attachment/0114/9684/b8bc064a-4007-35c8-a342-ec76f7dbbdc7.png[/img]
在删除跟查看栏目下，输入被删除/查看对象的ID，可以删除/浏览对象内容。
点击查看全部，可以看到一个列表，编辑名称，单击保存可以修改到数据库。
[img]http://dl2.iteye.com/upload/attachment/0114/9686/9857119b-9fd2-3569-9e3c-740ec1af3ef3.png[/img]

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.一般能用#的就别用$.
 类似资料：

相关阅读

微信开发准备第二步 springmvc mybatis项目结构搭建python集成开发环境配置(pycharm)使用Maven搭建Hadoop开发环境MyBaits 入门之MyBatis环境搭建(第一篇)IDEA 创建一个Mybatis Maven项目的方法步骤(图文)

相关文章

什么是IDE（集成开发环境）？MyBatis 类型模块Erlang环境配置(开发环境)Spring Boot整合MyBatisC#开发环境

相关问答

Spring Boot和Mybatis项目中多个数据源时的NoUniqueBeanDefinitionException@Transactional Spring MyBatis不工作用Eclipse开发一个Android Maven项目需要Javers和MyBatis集成帮助vue3 - vite项目import.meta.env能获取非VITE开发的环境变量？

相关文档

Laravel 开发环境部署 6.xMyBatis 入门教程Mybatis 实战教程Laravel 项目开发规范lemp PHP 集成运行环境
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.