springboot如何对本地数据库增删改查_Spring Boot整合Mybatis实现对数据库的增删改查...

写在开始：

今天学习了一下springboot，从网上找各种资料，视频，系统的学习了springboot入门，springboot controller,springboot过滤器，监听器，springboot JDBC连接数据库，JPA连接数据库，MyBatis连接数据库等相关知识，通过网上的资料自己动手敲了一遍，慢慢从helloworld到成功实现对数据库的增删改查，但我觉得还是mybatis用的比较多一些，springboot连接mybatis真的是纯注解式开发，个人觉得很简单方便有效。这里主要记录springboot整合mybatis实现对数据库的增删改查。

正文：

首先用maven构建springboot项目：

2.选择构建工具Maven Project、Spring Boot版本1.5.8以及一些工程基本信息，点击“Switch to the full version.”java版本选择1.8，可参考下图所示：

3.点击Generate Project下载项目压缩包，解压之后导入你得开发工具中即可。

运行截图：

4.运行成功之后，首选需要在SpringBoot的启动类里面增加用来扫描Mapper接口的注解，用来扫描Mapper包下面的接口。

5.在application.properties配置文件中添加数据库的支持.

6.pom.xml文件中添加相应的jar包,主要是添加mybatis的相关jar

7.准备工作完成之后就可以写代码了：

实体类代码如下：

package com.joy.entity;

import java.util.Date;

public class UserEntity {

private long userId;

private String userCode;

private String userName;

private String nickName;

private String userPwd;

private Date createDate;

private Date updateDate;

public long getUserId() {

return userId;

}

public void setUserId(long userId) {

this.userId = userId;

}

public String getUserCode() {

return userCode;

}

public void setUserCode(String userCode) {

this.userCode = userCode;

}

public String getUserName() {

return userName;

}

public void setUserName(String userName) {

this.userName = userName;

}

public String getNickName() {

return nickName;

}

public void setNickName(String nickName) {

this.nickName = nickName;

}

public String getUserPwd() {

return userPwd;

}

public void setUserPwd(String userPwd) {

this.userPwd = userPwd;

}

public Date getCreateDate() {

return createDate;

}

public void setCreateDate(Date createDate) {

this.createDate = createDate;

}

public Date getUpdateDate() {

return updateDate;

}

public void setUpdateDate(Date updateDate) {

this.updateDate = updateDate;

}

}

数据库sql命令：SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for user

-- ----------------------------

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

`user_id` int(10) NOT NULL AUTO_INCREMENT,

`user_code` varchar(20) DEFAULT NULL,

`user_name` varchar(20) DEFAULT NULL,

`nick_name` varchar(20) DEFAULT NULL,

`user_pwd` varchar(20) DEFAULT NULL,

`create_date` datetime DEFAULT NULL,

`update_date` datetime DEFAULT NULL,

PRIMARY KEY (`user_id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of user

-- ----------------------------

INSERT INTO `user` VALUES ('1', '10001', 'user10001', 'no1', '123456', '2017-10-22 15:23:32', '2017-11-07 15:23:35');

INSERT INTO `user` VALUES ('2', '10002', 'user10002', 'no2', '123456', '2017-10-22 15:23:32', '2017-11-07 15:23:35');

接下来就是最重要的编写Mapper接口，我们这里采用通过注解来实现数据库的增删改查功能。package com.joy.dao;

import java.util.List;

import java.util.Map;

import org.apache.ibatis.annotations.*;

import com.joy.entity.UserEntity;

public interface UserMapper {

@Select("select * from user ")

@Results({

@Result(property = "userId", column = "user_id"),

@Result(property = "nickName", column = "nick_name"),

@Result(property = "userCode", column = "user_code"),

@Result(property = "userName", column = "user_name"),

@Result(property = "userPwd", column = "user_pwd"),

@Result(property = "createDate", column = "create_date"),

@Result(property = "updateDate", column = "update_date") })

public List queryList();

@Select("SELECT * FROM USER WHERE user_id = #{userId}")

@Results({

@Result(property = "userId", column = "user_id"),

@Result(property = "nickName", column = "nick_name"),

@Result(property = "userCode", column = "user_code"),

@Result(property = "userName", column = "user_name"),

@Result(property = "userPwd", column = "user_pwd"),

@Result(property = "createDate", column = "create_date"),

@Result(property = "updateDate", column = "update_date") })

UserEntity findById(long userId);

@Insert("INSERT INTO USER(nick_name, user_code) VALUES(#{nickName}, #{userCode})")

int insertParam(@Param("nickName") String nickName, @Param("userCode") String userCode);

@Insert("INSERT INTO USER(nick_name, user_code) VALUES(#{nickName,jdbcType=VARCHAR}, #{userCode,jdbcType=INTEGER})")

int insertByMap(Map map);

@Insert("insert into user(nick_name,user_code,user_name,user_pwd,create_date,update_date) values(#{nickName},#{userCode},#{userName},#{userPwd},#{createDate},#{updateDate})")

public int insertEntity(UserEntity entity);

@Update("UPDATE user SET nick_name=#{nickName} WHERE user_id=#{userId}")

int updateEntity(UserEntity user);

@Delete("DELETE FROM user WHERE user_id =#{userId}")

int delete(Long userId);

@Delete("DELETE FROM user WHERE user_id =#{userId}")

int deleteEntity(UserEntity entity);

}

service层代码：package com.joy.service;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import com.joy.dao.UserMapper;

import com.joy.entity.UserEntity;

@Service

public class UserService {

@Autowired(required = false)

private UserMapper mapper;

public List queryList(){

List userList=mapper.queryList();

return userList;

}

public UserEntity findById(long userId){

System.out.println("userId:"+userId);

return mapper.findById(userId);

}

public int insertEntity() {

UserEntity entity=new UserEntity();

entity.setUserName("lisi");

entity.setUserCode("lisi"+new Date());

entity.setNickName("郭靖");

entity.setUserPwd("123");

entity.setCreateDate(new Date());

entity.setUpdateDate(new Date());

return mapper.insertEntity(entity);

}

public int insertParam() {

return mapper.insertParam("linzhiqiang","lzq");

}

public int insertByMap() {

Map map=new HashMap();

map.put("nickName","zhaotong");

map.put("userCode","zt");

return mapper.insertByMap(map);

}

public int updateEntity() {

UserEntity entity=new UserEntity();

entity.setUserId(1);

entity.setNickName("郭靖");

return mapper.updateEntity(entity);

}

public int deleteEntity() {

UserEntity entity=new UserEntity();

entity.setUserId(11);

return mapper.deleteEntity(entity);

}

}

controller层代码：package com.joy.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.PathVariable;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RestController;

import com.github.pagehelper.PageHelper;

import com.joy.entity.UserEntity;

import com.joy.service.UserService;

@RestController

public class UserController {

@Autowired

private UserService userService;

@RequestMapping("/userlist")

public List queryList(){

PageHelper.startPage(1, 2);

return userService.queryList();

}

@RequestMapping(value="/queryUser/{userId}",method=RequestMethod.GET)

public UserEntity queryUserEntity(@PathVariable long userId){

UserEntity userEntity=userService.findById(userId);

return userEntity;

}

@RequestMapping("/insert")

public int insertEntity() {

return userService.insertEntity();

}

@RequestMapping("/insertParam")

public int insertParam() {

return userService.insertParam();

}

@RequestMapping("/insertByMap")

public int insertByMap() {

return userService.insertByMap();

}

@RequestMapping("/updateEntity")

public int updateEntity() {

return userService.updateEntity();

}

@RequestMapping("/deleteEntity")

public int deleteEntity() {

return userService.deleteEntity();

}

}

8.执行结果：

分页查询结果：

带参数查询结果：

写在最后：通过今天的学习，简单的了解了springboot的实用性，感觉真的很简洁，继续学习springboot,积累点滴。