SpringBoot连接MySQL数据库

作者: SpaceCat | 来源:发表于2021-12-09 23:11 被阅读0次

微架构 springcloud-04. springboot-d
springboot 启动报MySQL服务问题解决过程记录（SC
jpa自动生成实体
SpringBoot点餐随笔-买家类目DAO
从零开始学springboot-整合mybatis-annota
PHP与MySQL
servlet连接mysql数据库和oracle数据库
MySQL 常用命令大全
mysql基本操作
php与mysql

Spring对数据库jdbc操作做了进一步的封装，使用自动注入将数据源相关的信息直接注入到JDBCTemplate中，我们可以直接使用，而无需关注jdbc或者更底层的数据库细节。本例用来演示如何用SpringBoot连接MySQL数据库。

1、环境准备：MySQL下载和安装

这里是在MacOS上试的，直接在MySQL官网下载了一个dmg的安装包，一路next安装的。安装之后为了测试，我们通过命令行登录sql，然后创建一个名为testdb的数据库，在其中创建一个Student表，并插入一条记录：

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database testdb;
Query OK, 1 row affected (0.01 sec)

mysql> use testdb;
Database changed
mysql> create table student(id int primary key not null, name varchar(30), age varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> select * from student;
Empty set (0.02 sec)

mysql> insert into student(id, name, age) values(1, 'Paopao', '5');
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | Paopao | 5    |
+----+--------+------+
1 row in set (0.00 sec)

mysql>

如下是一个查看表结构的命令示例：

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> describe student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table student;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                               |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `age` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

2、新建Maven工程Module

打开IDEA，File->New->Module...，选中Maven：

image.png

然后，Next输入gav：

image.png

选中Module所在的目录：

image.png

最后点击Finish，就建好了。目录结构如下：

image.png

这时候，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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.space.mysql</groupId>
    <artifactId>connnect</artifactId>
    <version>1.0-SNAPSHOT</version>


</project>

3、配置MySQL数据源以及需要的依赖

首先，修改pom，设置需要的依赖connnect/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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.space.mysql</groupId>
    <artifactId>connnect</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.0.RELEASE</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
    </dependencies>

</project>

然后，配置MySQL数据源，在resources目录下新建一个文件application.properties：

spring.datasource.username=root
spring.datasource.password=password
spring.datasource.url=jdbc:mysql://localhost:3306/testdb?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

点开IDEA右手边的Maven Projects窗口，选中当前的Module，右键：

image.png

点击Reimport。
到这里，数据库依赖及其配置已经搞定了。接下来写代码测试。
注意：
application.properties文件中如果添加如下行配置可以指定服务启动的端口：

# 指定服务在9082端口启动
server.port=9080

4、写简单的测试代码

首先，新建一个controller类com.space.mysql.connect.controller.JdbcController：

package com.space.mysql.connect.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

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

/**
 * Created by chengxia on 2021/12/8.
 */
@RestController
public class JdbcController {
    @Autowired
    JdbcTemplate jct;

    @GetMapping("/userlist")
    public List<Map<String, Object>> userList(){
        String sql = "select * from student";
        List<Map<String, Object>> map = jct.queryForList(sql);
        return map;
    }
}

然后，新增一个Module运行的Application类com.space.mysql.connect.main.Application：

package com.space.mysql.connect.main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;

/**
 * Created by chengxia on 2021/12/7.
 */
@SpringBootApplication
@ComponentScan("com.space.mysql.connect.controller")
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

到这里，测试代码已经写完了。目录结构如下：

image.png

打开com.space.mysql.connect.main.Application类，鼠标点击编辑器左侧的绿色三角：

image.png

就可以运行启动该Module。启动后，控制台输出如下：

com.space.mysql.connect.main.Application

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.0.0.RELEASE)

2021-12-09 22:59:01.852  INFO 921 --- [           main] c.space.mysql.connect.main.Application   : Starting Application on ChengdeMBP with PID 921 (/Users/chengxia/Developer/Java/projects/Test/connnect/target/classes started by chengxia in /Users/chengxia/Developer/Java/projects/Test)
2021-12-09 22:59:01.857  INFO 921 --- [           main] c.space.mysql.connect.main.Application   : No active profile set, falling back to default profiles: default
2021-12-09 22:59:02.142  INFO 921 --- [           main] ConfigServletWebServerApplicationContext : Refreshing org.springframework.boot.web.servlet.context.AnnotationConfigServletWebServerApplicationContext@214b199c: startup date [Thu Dec 09 22:59:02 CST 2021]; root of context hierarchy
2021-12-09 22:59:04.936  INFO 921 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2021-12-09 22:59:04.978  INFO 921 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2021-12-09 22:59:04.979  INFO 921 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet Engine: Apache Tomcat/8.5.28
2021-12-09 22:59:04.995  INFO 921 --- [ost-startStop-1] o.a.catalina.core.AprLifecycleListener   : The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: [/Users/chengxia/Library/Java/Extensions:/Library/Java/Extensions:/Network/Library/Java/Extensions:/System/Library/Java/Extensions:/usr/lib/java:.]
2021-12-09 22:59:05.156  INFO 921 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2021-12-09 22:59:05.157  INFO 921 --- [ost-startStop-1] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 3021 ms
2021-12-09 22:59:05.366  INFO 921 --- [ost-startStop-1] o.s.b.w.servlet.ServletRegistrationBean  : Servlet dispatcherServlet mapped to [/]
2021-12-09 22:59:05.380  INFO 921 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'characterEncodingFilter' to: [/*]
2021-12-09 22:59:05.386  INFO 921 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'hiddenHttpMethodFilter' to: [/*]
2021-12-09 22:59:05.387  INFO 921 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'httpPutFormContentFilter' to: [/*]
2021-12-09 22:59:05.387  INFO 921 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'requestContextFilter' to: [/*]
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
2021-12-09 22:59:06.502  INFO 921 --- [           main] s.w.s.m.m.a.RequestMappingHandlerAdapter : Looking for @ControllerAdvice: org.springframework.boot.web.servlet.context.AnnotationConfigServletWebServerApplicationContext@214b199c: startup date [Thu Dec 09 22:59:02 CST 2021]; root of context hierarchy
2021-12-09 22:59:06.622  INFO 921 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/userlist],methods=[GET]}" onto public java.util.List<java.util.Map<java.lang.String, java.lang.Object>> com.space.mysql.connect.controller.JdbcController.userList()
2021-12-09 22:59:06.630  INFO 921 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error]}" onto public org.springframework.http.ResponseEntity<java.util.Map<java.lang.String, java.lang.Object>> org.springframework.boot.autoconfigure.web.servlet.error.BasicErrorController.error(javax.servlet.http.HttpServletRequest)
2021-12-09 22:59:06.631  INFO 921 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error],produces=[text/html]}" onto public org.springframework.web.servlet.ModelAndView org.springframework.boot.autoconfigure.web.servlet.error.BasicErrorController.errorHtml(javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse)
2021-12-09 22:59:06.687  INFO 921 --- [           main] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/webjars/**] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2021-12-09 22:59:06.687  INFO 921 --- [           main] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/**] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2021-12-09 22:59:06.793  INFO 921 --- [           main] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/**/favicon.ico] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2021-12-09 22:59:07.123  INFO 921 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2021-12-09 22:59:07.124  INFO 921 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Bean with name 'dataSource' has been autodetected for JMX exposure
2021-12-09 22:59:07.131  INFO 921 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Located MBean 'dataSource': registering with JMX server as MBean [com.zaxxer.hikari:name=dataSource,type=HikariDataSource]
2021-12-09 22:59:07.312  INFO 921 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2021-12-09 22:59:07.316  INFO 921 --- [           main] c.space.mysql.connect.main.Application   : Started Application in 7.102 seconds (JVM running for 8.823)

从上面输出的最后几行，可以看出，这里服务启动在8080端口。在浏览器中访问http://localhost:8080/userlist，就可以看到数据库中查出的信息。如下：

image.png

到这里，测试就完成了。下面给出了除了查询以外，增删改的例子，可以自行进行测试。com.space.mysql.connect.controller.JdbcController：

package com.space.mysql.connect.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

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

/**
 * Created by chengxia on 2021/12/8.
 */
@RestController
public class JdbcController {
    @Autowired
    JdbcTemplate jct;

    @GetMapping("/userlist")
    public List<Map<String, Object>> userList(){
        String sql = "select * from student";
        List<Map<String, Object>> map = jct.queryForList(sql);
        return map;
    }

    @GetMapping("/insertdb")
    public String addUser(){
        String sql1 = "insert into student values(5, 'kobe', 45)";
        String sql2 = "insert into student values(6, 'tim', 44)";
        jct.update(sql1);
        jct.update(sql2);
        return "insert db ok!";
    }

    @GetMapping("/deletedb")
    public String deleteUser(){
        String sql = "delete from student where id in ('5','6')";
        jct.update(sql);
        return "delete users from db ok!";
    }

    @GetMapping("/updatedb")
    public String updateUser(){
        String sql = "update student set age = 5 where id in (5, 6)";
        jct.update(sql);
        return "update users in db set age 5 ok!";
    }
}

微架构 springcloud-04. springboot-d
springboot druid+mysql、使用JAP 这一节讲述springboot 连接Mysql 数据库，...
springboot 启动报MySQL服务问题解决过程记录（SC
执行springboot项目的时候，报错，发现是MySQL连接问题连接数据库输入命令进入数据库，提示未能连接...
jpa自动生成实体
IDEA springboot+jpa 连接mysql数据库 1. 连接数据库 pom.xml中添加配置 appl...
SpringBoot点餐随笔-买家类目DAO
配置数据库使用MySQL，pom.xml引入MySQL连接驱动持久层框架使用SpringBoot Data J...
从零开始学springboot-整合mybatis-annota
前言前章讲了下springboot使用JPA的当时连接mysql，本章我们来重点学习下mybatis连接数据库，...
PHP与MySQL
mysql_connect 建立数据库连接 mysql_close 关闭数据库连接 mysql_select_db...
servlet连接mysql数据库和oracle数据库
连接mysql数据库连接oracle数据库
MySQL 常用命令大全
一、 mysql：连接数据库 mysql 命令用户连接数据库。mysql 命令格式：mysql -h 主机地址-u...
mysql基本操作
连接本地mysql数据库连接远程数据库：
php与mysql
1、连接mysql 2、数据库扩展 mysql扩展进行数据库连接的方法： $link = mysql_connec...