介绍
本文介绍在Spring Boot基础下配置mybatis编写数据访问的示例。
一、maven配置
新建springboot项目时,勾选web,mybatis,mysql会自动加入jar就不一一介绍了。
需要注意的是:如果引入的mysql-connector-java是8.0以后的版本,需要注意一下时间。再配置文件添加这个时间的配置,不配置添加时间格式数据会报错
<!-- 引入阿里数据源 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.6</version>
</dependency>
异常
java.sql.SQLException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.13.jar:8.0.13]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.13.jar:8.0.13]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[mysql-connector-java-8.0.13.jar:8.0.13]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[mysql-connector-java-8.0.13.jar:8.0.13]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73) ~[mysql-connector-java-8.0.13.jar:8.0.13]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76) ~[mysql-connector-java-8.0.13.jar:8.0.13]
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835) ~[mysql-connector-java-8.0.13.jar:8.0.13]
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455) ~[mysql-connector-java-8.0.13.jar:8.0.13]
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240) ~[mysql-connector-java-8.0.13.jar:8.0.13]
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207) ~[mysql-connector-java-8.0.13.jar:8.0.13]
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1570) ~[druid-1.1.12.jar:1.1.12]
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1636) ~[druid-1.1.12.jar:1.1.12].....
二、application.properties
spring.datasource.url = jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
spring.datasource.username =root
spring.datasource.password =123456
#使用阿里巴巴druid数据源,默认使用自带的
spring.datasource.type =com.alibaba.druid.pool.DruidDataSource
#开启控制台打印sql
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
spring.jackson.date-format=yyyy-MM-dd HH:mm:ss
spring.jackson.time-zone=Asia/Shanghai
三、全注解代码
先上个图片
启动类:
package com.guo;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.guo.mapper")
public class MybatisApplication {
public static void main(String[] args) {
SpringApplication.run(MybatisApplication.class, args);
}
}
控制类:
package com.guo.controller;
import com.guo.domain.JsonData;
import com.guo.domain.User;
import com.guo.mapper.UserMapper;
import com.guo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.Date;
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@Autowired
private UserMapper userMapper;
/**
* 功能描述: user 保存接口
* @return
*/
@GetMapping("add")
public Object add(){
User user = new User();
user.setAge(11);
user.setCreateTime(new Date());
user.setName("mybatis");
user.setPhone("010-666666");
int id = userService.add(user);
return JsonData.buildSuccess(id);
}
@GetMapping("findAll")
public Object findAll(){
return JsonData.buildSuccess(userMapper.getAll());
}
@GetMapping("findById")
public Object findById(long id){
return JsonData.buildSuccess(userMapper.findById(id));
}
@GetMapping("del_by_id")
public Object delById(long id){
userMapper.delete(id);
return JsonData.buildSuccess();
}
@GetMapping("update")
public Object update(String name,int id){
User user = new User();
user.setName(name);
user.setId(id);
userMapper.update(user);
return JsonData.buildSuccess();
}
//测试事务
@GetMapping("add_account")
public Object addAccount(){
int id = userService.addAccount();
return JsonData.buildSuccess(id);
}
}
实体类:
package com.guo.domain;
import java.util.Date;
public class User {
private int id;
private String name;
private String phone;
private int age;
private Date createTime;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
}
JsonData.java
package com.guo.domain;
import java.io.Serializable;
public class JsonData implements Serializable {
private static final long serialVersionUID = 1L;
private Integer code; // 状态码 0 表示成功,1表示处理中,-1表示失败
private Object data; // 数据
private String msg;// 描述
public JsonData() {
}
public JsonData(Integer code, Object data, String msg) {
this.code = code;
this.data = data;
this.msg = msg;
}
// 成功,传入数据
public static JsonData buildSuccess() {
return new JsonData(0, null, null);
}
// 成功,传入数据
public static JsonData buildSuccess(Object data) {
return new JsonData(0, data, null);
}
// 失败,传入描述信息
public static JsonData buildError(String msg) {
return new JsonData(-1, null, msg);
}
// 失败,传入描述信息,状态码
public static JsonData buildError(String msg, Integer code) {
return new JsonData(code, null, msg);
}
// 成功,传入数据,及描述信息
public static JsonData buildSuccess(Object data, String msg) {
return new JsonData(0, data, msg);
}
// 成功,传入数据,及状态码
public static JsonData buildSuccess(Object data, int code) {
return new JsonData(code, data, null);
}
public Integer getCode() {
return code;
}
public void setCode(Integer code) {
this.code = code;
}
public Object getData() {
return data;
}
public void setData(Object data) {
this.data = data;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
@Override
public String toString() {
return "JsonData [code=" + code + ", data=" + data + ", msg=" + msg+ "]";
}
}
Mapper类
package com.guo.mapper;
import com.guo.domain.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
public interface UserMapper {
//推荐使用#{}取值,不要用${},因为存在注入的风险
@Insert("INSERT INTO user(name,phone,create_time,age) VALUES(#{name}, #{phone}, #{createTime},#{age})")
@Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id") // keyProperty java对象的属性;keyColumn表示数据库的字段
int insert(User user);
@Select("SELECT * FROM user")
@Results({
@Result(column = "create_time",property = "createTime") // javaType = java.util.Date.class
})
List<User> getAll();
@Select("SELECT * FROM user WHERE id = #{id}")
@Results({
@Result(column = "create_time",property = "createTime")
})
User findById(Long id);
@Update("UPDATE user SET name=#{name} WHERE id =#{id}")
void update(User user);
@Delete("DELETE FROM user WHERE id =#{userId}")
void delete(Long userId);
}
服务类
package com.guo.service;
import com.guo.domain.User;
public interface UserService {
public int add(User user);
public int addAccount();
}
服务实现类
package com.guo.service.impl;
import com.guo.domain.User;
import com.guo.mapper.UserMapper;
import com.guo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public int add(User user) {
userMapper.insert(user);
int id = user.getId();
return id;
}
@Override
@Transactional(propagation=Propagation.REQUIRED)
public int addAccount() {
User user = new User();
user.setAge(88);
user.setCreateTime(new Date());
user.setName("测试事务啦加入事务");
user.setPhone("10010101010");
userMapper.insert(user);
int i = 19/0;
return 0;
}
}
jsonUtils类
package com.guo.utils;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.util.StringUtils;
import java.io.IOException;
public class JsonUtils {
private static ObjectMapper objectMapper = new ObjectMapper();
//对象转字符串
public static <T> String obj2String(T obj){
if (obj == null){
return null;
}
try {
return obj instanceof String ? (String) obj : objectMapper.writeValueAsString(obj);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
//字符串转对象
public static <T> T string2Obj(String str,Class<T> clazz){
if (StringUtils.isEmpty(str) || clazz == null){
return null;
}
try {
return clazz.equals(String.class)? (T) str :objectMapper.readValue(str,clazz);
} catch (IOException e) {
e.printStackTrace();
return null;
}
}
}
由于代码太多,测试结果就不一一展示了。这是全注解的方式进行mybatis,但是有时候sql过于复杂用这种方式,采用xml方式来实现mapper
三、xml 的方式
修改UserMapper.java,简单做了一个查询的,其他可以自行添加测试。
package com.guo.mapper;
import com.guo.domain.User;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface UserMapper {
List<User> getAll();
}
UserController.java
package com.guo.controller;
import com.guo.domain.JsonData;
import com.guo.domain.User;
import com.guo.mapper.UserMapper;
import com.guo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.Date;
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@Autowired
private UserMapper userMapper;
@GetMapping("findAll")
public Object findAll(){
return JsonData.buildSuccess(userMapper.getAll());
}
}
application.properties添加xml位置
mybatis.mapper-locations=classpath:mapper/*.xml
结果: