MySQL与SpringBoot整合过程及简单应用

最新推荐文章于 2022-04-18 18:19:53 发布

原创最新推荐文章于 2022-04-18 18:19:53 发布 · 227 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL与SpringBoot

mysql 同时被 2 个专栏收录

6 篇文章

订阅专栏

SpringBoot

2 篇文章

订阅专栏

本文详细介绍了如何在SpringBoot框架中整合MySQL数据库，包括代码实现和接口调用示例，如用户数据的增删改查操作。

整合过程跟我的 MongoDB与SpringBoot整合过程及简单应用
差不多
不多说了
看结果
然后上代码

要注意的点就是update和query方法贼多需要现学下面我的代码只是简单的几种

1.update 增加数据

插入之后数据是这样的
在这里插入图片描述

19	qing		18	123456
29	qingqing	18	123456
12	shuai		18	1234565
14	qing		18	1234565
15	qing		20	1234565
16	qing		20	1234565
17	shi			20	1234565
18	shi			21	1234565

2.query 查询名字为qing的多条数据

http://localhost:10000/user/findUsersByUserName?userName=qing

在这里插入图片描述

2. 查询单条

http://localhost:10000/user/findUserByUserName?userName=qing

在这里插入图片描述

3. 更新数据

http://localhost:10000/user/updateUser?id=19&name=sunqing&age=12

在这里插入图片描述

4.删除数据

http://localhost:10000/user/deleteUserById?id=18

在这里插入图片描述

public class User {
    private String id;
    private String name;
    private String password;
    private int age;
    }

/**
 * 作者:Shishuai
 * 文件名：MysqlController
 * 时间：2019/8/21 19:50
 */

package com.example.demo.controller;


import com.example.demo.bean.User;
import com.example.demo.service.UserService;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Iterator;
import java.util.List;
import java.util.Map;


@RestController
@RequestMapping("/user")
public class MysqlController {

    private static Logger logger = Logger.getLogger(MysqlController.class);

    @Autowired
    private UserService userService;

    @RequestMapping("/insert")
    public String insert(User user){
        int res = userService.insert(user);
        if(res > 0){
            logger.info("恭喜 注册成功");
            return "恭喜 注册成功";
        }else{
            logger.info("注册失败");
            return "注册失败";
        }

    }

    //根据名字查询数据
    @RequestMapping("/findUsersByUserName")
    public List<User> findUsersByUserName(String userName){
        System.out.println("controller中：我要通过"+userName+"找数据 通过Service获取结果");
        List<User> list = userService.findUsersByUserName(userName);
        Iterator<User> iterator = list.iterator();
        while(iterator.hasNext()){
            User user1 = iterator.next();
            System.out.println("查到的结果"+user1);
        }
        return list;
    }

    //    根据用户名查询对象
    @RequestMapping("/findUserByUserName")
    public List<Map<String, Object>> findUserByUserName(String userName){
        System.out.println("controller中：我要通过"+userName+"找数据 通过Service获取结果");
        List<Map<String, Object>> res = userService.findUserByUserName(userName);
        System.out.println("查到的结果"+res);
        Iterator<Map<String, Object>> iterator = res.iterator();
        while(iterator.hasNext()){
            Map<String, Object> map = iterator.next();
            Iterator<Map.Entry<String, Object>> iterator1 = map.entrySet().iterator();
            while(iterator1.hasNext()){
                Map.Entry<String, Object> next = iterator1.next();
                System.out.println(next.getKey() + "  "+ next.getValue());
            }
        }
        return res;
    }

    //更新对象
    @RequestMapping("/updateUser")
    public String updateUser(User user){
        System.out.println("controller中：我要通过"+user+"更新对象");
        int res = userService.updateUser(user);
        if(res > 0){
            return "更新成功";
        }else{
            return "更新失败";
        }
    }

    //删除对象
    @RequestMapping("/deleteUserById")
    public String deleteUserById(String id){
        System.out.println("controller中：我要通过"+id+"删除对象");
        int res = userService.deleteUserById(id);
        if(res > 0){
            return "删除成功";
        }else{
            return "删除失败";
        }
    }

}

/**
 * 作者:Shishuai
 * 文件名：UserDaoImpl
 * 时间：2019/8/21 19:52
 */

package com.example.demo.dao.Impl;

import com.example.demo.bean.User;
import com.example.demo.dao.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

@Component
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate template;
    @Override
    public int insert(User user) {
        System.out.println("插入"+user);
        String sql = "insert into user (id, name, age, password) VALUES (?,?,?,?)";
        Object[] object =  new Object[]{user.getId(), user.getName(), user.getAge(), user.getPassword()};
        int res = template.update(sql, object);
        return res;
    }

    @Override
    public List<User> findUsersByUserName(String userName) {
        System.out.println("在Dao实现类中：我要通过"+userName+"找多条数据");
        String sql = "select * from user where name = ?";
//        List<Map<String, Object>> list = template.queryForList(sql, userName);

        List list = template.query(sql, new BeanPropertyRowMapper(User.class), userName);
        return list;
    }

    @Override
    public List<Map<String, Object>> findUserByUserName(String userName) {

        System.out.println("在Dao实现类中：我要通过"+userName+"找一条数据");
        String sql = "select * from user where name = ?";
        Object[] objects = new Object[]{userName};
        List<Map<String, Object>> res = template.queryForList(sql, objects);
        return res;
    }

    @Override
    public int updateUser(User user) {

        System.out.println("在Dao实现类中：我要通过"+user+"更新一条数据");
        String sql = "update user set name = ?, age = ? where id = ?";
        Object[] objects = new Object[]{user.getName(), user.getAge(), user.getId()};
        int res = template.update(sql, objects);
        return res;

    }

    @Override
    public int deleteUserById(String id) {
        System.out.println("在Dao实现类中：我要通过"+id+"删除一条数据");
        String sql = "delete from user where id = ?";
        int res = template.update(sql, id);
        return res;
    }
}

package com.example.demo.dao;

import com.example.demo.bean.User;

import java.util.List;
import java.util.Map;

public interface UserDao {
    public int insert(User user);

    //根据名字查询数据
    public List<User> findUsersByUserName(String userName);

    //    根据用户名查询对象
    public List<Map<String, Object>> findUserByUserName(String userName);

    //更新对象
    public int updateUser(User user);

    //删除对象
    public int deleteUserById(String id);
}

/**
 * 作者:Shishuai
 * 文件名：UserServiceImpl
 * 时间：2019/8/21 19:52
 */

package com.example.demo.service.Impl;

import com.example.demo.bean.User;
import com.example.demo.dao.UserDao;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;


@Service
public class UserServiceImpl implements UserService{

    @Autowired
    private UserDao userDao;

    @Override
    public int insert(User user) {

        return userDao.insert(user);
    }

    @Override
    public List<User> findUsersByUserName(String userName) {
        return userDao.findUsersByUserName(userName);
    }

    @Override
    public List<Map<String, Object>> findUserByUserName(String userName) {
        return userDao.findUserByUserName(userName);
    }

    @Override
    public int updateUser(User user) {
        int res = userDao.updateUser(user);
        return res;
    }

    @Override
    public int deleteUserById(String id) {
        int res = userDao.deleteUserById(id);
        return res;
    }
}

package com.example.demo.service;

import com.example.demo.bean.User;

import java.util.List;
import java.util.Map;

public interface UserService {
    public int insert(User user);

    //根据名字查询数据
    public List<User> findUsersByUserName(String userName);

    //    根据用户名查询对象
    public List<Map<String, Object>> findUserByUserName(String userName);

    //更新对象
    public int updateUser(User user);

    //删除对象
    public int deleteUserById(String id);
}

application.properties全局配置文件

######数据库链接配置########
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/db1?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

#设置servlet的端口号
server.port=10000

log4j

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

依赖

<?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.1.7.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.example</groupId>
	<artifactId>demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>demo</name>
	<description>Demo project for Spring Boot</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>


        <!-- log4j -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j</artifactId>
            <version>1.3.8.RELEASE</version>
        </dependency>
    </dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>