springmvc+mybatis的增删改查入门
2024-08-26 23:39:08
先到官网了解mybatis的语法：https://mybatis.org/mybatis-3/zh/sqlmap-xml.html
前端用了thymeleaf和vue.js，效果图和demo地址：https://gitee.com/cainiaoA/mybatis
thymeleaf获取java请求头 ctx='http://localhost:8017/
<script th:inline="javascript"> var ctx = [[@{/}]]; </script>
然后在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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.0.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.tushu</groupId>
<artifactId>tushu</artifactId>
<version>1.0.0</version>
<name>tushu</name>
<description>练习</description> <properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties> <dependencies>
<!--spring-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--<dependency>-->
<!--<groupId>org.springframework.boot</groupId>-->
<!--<artifactId>spring-boot-starter-data-jpa</artifactId>-->
<!--</dependency>-->
<!--mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>5.1.21</scope>
</dependency> <!--引入druid-->
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.8</version>
</dependency> <dependency>
<groupId>org.bgee.log4jdbc-log4j2</groupId>
<artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
<version>1.16</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.13</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.13</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-api</artifactId>
<version>2.0-beta9</version>
</dependency> <dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-validator</artifactId>
<version>6.1.0.Alpha6</version>
</dependency> <!-- thymeleaf -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<!-- elastic search -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-elasticsearch</artifactId>
</dependency>
<!-- 用了 elasticsearch 就要加这么一个，不然要com.sun.jna.Native 错误 -->
<dependency>
<groupId>com.sun.jna</groupId>
<artifactId>jna</artifactId>
<version>3.0.9</version>
</dependency> <!-- thymeleaf legacyhtml5 模式支持 -->
<dependency>
<groupId>net.sourceforge.nekohtml</groupId>
<artifactId>nekohtml</artifactId>
<version>1.9.22</version>
</dependency> <dependency>
<groupId>net.minidev</groupId>
<artifactId>json-smart</artifactId>
<version>2.2.1</version>
</dependency> <!--测试-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency> <!-- junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<!-- commons-lang -->
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>2.6</version>
</dependency>
<!-- shiro -->
<dependency>
<groupId>org.apache.shiro</groupId>
<artifactId>shiro-spring</artifactId>
<version>1.3.2</version>
</dependency>
<!-- hsqldb -->
<dependency>
<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>
</dependency>
</dependencies>
<!--打包成插件-->
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build> </project>
然后编写application.yml的相关配置
server:
port: 8017 spring:
datasource:
# 数据源基本配置
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
type: com.alibaba.druid.pool.DruidDataSource
# 数据源其他配置
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
jackson:
date-format: yyyy-MM-dd HH:mm:ss #如果使用字符串表示，用这行设置格式
timezone: GMT+8
serialization:
write-dates-as-timestamps: false #使用时间戳，使用数值timestamp表示日期 mybatis:
# 搜索指定包别名
typeAliasesPackage: com.tushu.**.domain
# 指定全局配置文件位置
config-location: classpath:mybatis/mybatis-config.xml
# 指定sql映射文件位置
mapper-locations: classpath:mybatis/mapper/*Mapper.xml
mybatis相关配置
<?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> <settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
</configuration>
然后就行相关的ioc和mybatis的数据文件操作
先编写domain，就是数据的model
package com.tushu.book.domain; import java.io.Serializable;
import java.util.HashMap;
import java.util.Map; public class BaseEntity implements Serializable { /** 请求参数 */
private Map<String, Object> params; public Map<String, Object> getParams()
{
if (params == null)
{
params = new HashMap<>();
}
return params;
} public void setParams(Map<String, Object> params)
{
this.params = params;
}
}
package com.tushu.book.domain;
import java.util.Date;
public class books extends BaseEntity {
/**
* 编号
*/
private int id;
/**
* 书名
*/
private String title;
/**
* 价格
*/
private double price;
/**
* 出版日期
*/
private Date publishDate;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public Date getPublishDate() {
return publishDate;
}
public void setPublishDate(Date publishDate) {
this.publishDate = publishDate;
}
@Override
public String toString() {
return "books{" +
"id=" + id +
", title='" + title + '\'' +
", price=" + price +
", publishDate=" + publishDate +
'}';
}
}
然后编写mybatis数据操作，接口文件格式（xxxMapper.java）
package com.tushu.book.mapper;
import com.tushu.book.domain.books;
import java.util.List;
public interface BooksMapper {
public int addBook(books model);
public int updateBook(books model);
public int deleteBook(int id);
public List<books> queryBook(books model);
}
数据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="com.tushu.book.mapper.BooksMapper"><!--xxxMapper所在的包--!> <resultMap type="books" id="booksResult">
<id property="id" column="id"/>
<result property="title" column="title"/>
<result property="price" column="price"/>
<result property="publishDate" column="publishDate"/>
<!--<association property="dept" column="dept_id" javaType="SysDept" resultMap="deptResult" />-->
<!--<collection property="roles" javaType="java.util.List" resultMap="RoleResult" />-->
</resultMap> <!--<resultMap id="deptResult" type="SysDept">-->
<!--<id property="deptId" column="dept_id" />-->
<!--<result property="parentId" column="parent_id" />-->
<!--</resultMap>--> <!--<resultMap id="RoleResult" type="SysRole">-->
<!--<id property="roleId" column="role_id" />-->
<!--</resultMap>--> <select id="queryBook" parameterType="books" resultMap="booksResult">
select * from books where 1=1
<if test="title != null and title != ''">
AND title=#{title}
</if>
<if test="price != null and price >0 ">
AND price = #{price}
</if>
<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
AND date_format(publishDate,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d')
</if>
<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
AND date_format(publishDate,'%y%m%d') <= date_format(#{params.endTime},'%y%m%d')
</if>
<!-- 数据范围过滤 ${params.dataScope} --> </select> <delete id="deleteBook" parameterType="int">
delete from books where id = #{id}
</delete> <update id="updateBook" parameterType="books">
update books
<set>
<if test="title != null and title != ''">title = #{title},</if>
<if test="price != null and price >0 ">price = #{price},</if>
publishDate = sysdate()
</set>
where id = #{id}
</update> <insert id="addBook" parameterType="books" useGeneratedKeys="true" keyProperty="id">
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
SELECT LAST_INSERT_ID()
</selectKey>
insert into books(
<if test="id != null and id > 0">id,</if>
<if test="title != null and title != '' ">title,</if>
<if test="price != null and price > 0 ">price,</if>
publishDate
)values(
<if test="id != null and id > 0">#{id},</if>
<if test="title != null and title != ''">#{title},</if>
<if test="price != null and price >0 ">#{price},</if>
sysdate()
)
</insert> </mapper>
然后是是ico的实现
接口
package com.tushu.book.service;
import com.tushu.book.domain.books;
import java.util.List;
public interface IBooksService {
public int addBook(books model);
public int updateBook(books model);
public int deleteBook(int id);
public List<books> queryBook(books model);
}
实现类（逻辑），添加@Service注解和以接口名称+Impl结尾命名
package com.tushu.book.service.impl; import com.tushu.book.domain.books;
import com.tushu.book.mapper.BooksMapper;
import com.tushu.book.service.IBooksService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service; import java.util.List; @Service
public class IBooksServiceImpl implements IBooksService {
@Autowired
private BooksMapper booksMapper; public int addBook(books model) {
return booksMapper.addBook(model);
} public int updateBook(books model) {
return booksMapper.updateBook(model);
} public int deleteBook(int id) {
return booksMapper.deleteBook(id);
} public List<books> queryBook(books model) {
return booksMapper.queryBook(model);
}
}
然后就是控制器的调用
package com.tushu.book.controller; import com.tushu.book.domain.books;
import com.tushu.book.service.IBooksService;
import com.tushu.book.service.ITestDataService;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*; import java.util.List; @Controller
@RequestMapping("/system/books")
public class bookscontroller {
private String prefix = "system/books";
@Autowired
IBooksService ibooksService;
@Autowired
ITestDataService testDataService; //页面
@RequiresPermissions("system:books:view")
@GetMapping()
public String books() {
return prefix;
}
//查询
@ResponseBody
@PostMapping("/query")
public List<books> querybook(@Validated books model){
List<books> data = ibooksService.queryBook(model);
return data;
}
//添加
@ResponseBody
@PostMapping("/add")
public books addBook(@Validated books model){
int id = ibooksService.addBook(model);
return model;
}
//修改
@ResponseBody
@PostMapping("/updatebook")
public boolean updatebook(@Validated books model){
int row = ibooksService.updateBook(model);
return row >= 0 ? true:false;
}
//删除
@ResponseBody
@GetMapping("/deletebook/{id}")
public boolean deletebook(@PathVariable("id") int id){
int row = ibooksService.deleteBook(id);
return row >= 0 ? true:false;
} @ResponseBody
@GetMapping("/speak/{str}")
public String speak(@PathVariable("str") String str){
return testDataService.speak(str);
} @ResponseBody
@RequestMapping("/getByName")
public String getByName(String name){
return "hi ,welcome to " + name;
}
}
最新文章
- centos mysql开启远程访问
- 游戏的套路你知道吗？ H5 Canvas刮刮乐
- 简单跳转到微信分享，基于libWeiChatSDK 和简单的自定义UIActivityViewController
- [FPGA] 1、开发板使用和引脚连接
- 信心题--FUOJ2226（莫队算法）
- easyui Tooltip 气泡信息提示
- mysql修改用户密码 新增用户
- Java Servlet 工作原理问答
- jsconsole
- recovery编译汉化源码开源地址
- JFreeChart学习
- libeXosip2(3-1) -- eXosip2 INVITE and Call Management
- 学会Func
- How To Size Your Apache Flink® Cluster: A Back-of-the-Envelope Calculation
- JAVA版本微信管家平台—JeeWx 捷微 4.1 微服务版本发布，微信砍价活动闪亮登场！
- TDD：什么是桩（stub）和模拟（mock）？
- spring+springmvc+ibatis整合注解方式实例【转】
- 剑指offer（34）第一个只出现一次的字符
- （转）关于如何学好游戏3D引擎编程的一些经验
- linux常见命令整理