Springboot+Mybatis连接各种关系数据库,以及各数据库的安装
数据库下载和安装
对于数据库的下载和安装建议从官网下载并且在官网查看安装文档
MySql 下载和安装
-
进入 Mysql官网
-
点击文档标签
-
点击查看yum方式安装MySql的文档
-
点击全新的安装Mysql
-
点击存储库链接,实际就是下载链接
6.选择要下载的版本,然后点击下载
7.点击下载后会跳转到下载页面。
-
在linux服务器上使用yum下载,这个链接就是上图中 “不,谢谢,开始下载” 的超链接
yum install https://dev.mysql.com/get/mysql80-community-release-el7-10.noarch.rpm
-
继续看文档,执行文档上的步骤
-
安装成功
-
查看配置文件 安装完成后默认配置文件地址是
/etc/my.cnf
官方配置文档地址 -
使用navicate 连数据库
a. 打开防火墙//开放3306端口 firewall-cmd --add-port=3306/tcp --permanent //刷新防火墙 firewall-cmd --reload
b. 设置允许ip访问mysql
//修改为所有ip都能访问root 用户 update user set host = '%' where user = 'root'; //刷新配置 flush privileges;
c. 重新启动Mysql服务
systemctl restart mysqld
11.连接成功
Oracle 下载和安装
由于Oracle安装实在是太繁琐了,就不实操了。
下面是一个其他博主的链接 Linux上的oracle11g安装(提供安装包链接)以及其他问题注解
SqlServer 下载和安装
- 下载SqlServer的yum源文件 ,如何需要下载其他版本的数据库,可以进入
https://packages.microsoft.com/config/rhel
中自行选择。
curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2019.repo
- yum安装SQL Server
yum install -y mssql-server
- 执行SQL Server初始化
/opt/mssql/bin/mssql-conf setup
- 选择版本,
5.同意条款和设置密码
- 使用navicate 连数据库
//开放1433端口
firewall-cmd --add-port=1433/tcp --permanent
//刷新防火墙
firewall-cmd --reload
- 连接成功
Springboot+Mysql+数据库链接
创建SpringBoot项目并且配置MyBatis
-
创建一个父工程和对应的子工程,目录结构如下
-
SpringBootDataBase-mysql:
- pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.20</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
- application.yml
# 应用服务 WEB 访问端口
server:
port: 8081
#下面这些内容是为了让MyBatis映射
#指定Mybatis的Mapper文件
mybatis:
mapper-locations: classpath:mappers/*xml
#指定Mybatis的实体目录
type-aliases-package: com.example.springbootdatabase.mysql.demos.entity
spring:
datasource:
url: jdbc:mysql://192.168.184.128:3306/spring_boot_database?useUnicode=true&useJDBCCompliantTimezoneShift=true&serverTimezone=UTC&utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: Smileday159@
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
druid:
#初始化连接池的连接数量 大小,最小,最大
initial-size: 5
min-idle: 5
max-active: 20
#配置获取连接等待超时的时间
max-wait: 60000
#配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
time-between-eviction-runs-millis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
min-evictable-idle-time-millis: 30000
validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: true
test-on-return: false
# 是否缓存preparedStatement,也就是PSCache 官方建议MySQL下建议关闭 个人建议如果想用SQL防火墙 建议打开
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filter:
stat:
merge-sql: true
slow-sql-millis: 5000
#3.基础监控配置
web-stat-filter:
enabled: true
url-pattern: /*
#设置不统计哪些URL
exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
session-stat-enable: true
session-stat-max-count: 100
stat-view-servlet:
enabled: true
url-pattern: /druid/*
reset-enable: true
#设置监控页面的登录名和密码
login-username: admin
login-password: admin
allow: 127.0.0.1
- SpringBootDataBase-oracle:
- pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc8</artifactId>
<version>12.2.0</version>
</dependency>
<dependency>
<groupId>cn.easyproject</groupId>
<artifactId>orai18n</artifactId>
<version>12.1.0.2.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.20</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
- application.yml
# 应用服务 WEB 访问端口
server:
port: 8082
#下面这些内容是为了让MyBatis映射
#指定Mybatis的Mapper文件
mybatis:
mapper-locations: classpath:mappers/*xml
#指定Mybatis的实体目录
type-aliases-package: com.example.springbootdatabase.oracle.demos.entity
spring:
datasource:
url: jdbc:oracle:thin:@192.168.184.131:1521/orcl
username: spring_boot_database
password: Smileday159@
driver-class-name: oracle.jdbc.driver.OracleDriver
type: com.alibaba.druid.pool.DruidDataSource
druid:
#初始化连接池的连接数量 大小,最小,最大
initial-size: 5
min-idle: 5
max-active: 20
#配置获取连接等待超时的时间
max-wait: 60000
#配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
time-between-eviction-runs-millis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
min-evictable-idle-time-millis: 30000
validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: true
test-on-return: false
# 是否缓存preparedStatement,也就是PSCache 官方建议MySQL下建议关闭 个人建议如果想用SQL防火墙 建议打开
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filter:
stat:
merge-sql: true
slow-sql-millis: 5000
#3.基础监控配置
web-stat-filter:
enabled: true
url-pattern: /*
#设置不统计哪些URL
exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
session-stat-enable: true
session-stat-max-count: 100
stat-view-servlet:
enabled: true
url-pattern: /druid/*
reset-enable: true
#设置监控页面的登录名和密码
login-username: admin
login-password: admin
allow: 127.0.0.1
- SpringBootDataBase-sqlserver:
- pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>sqljdbc4</artifactId>
<version>4.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.20</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
- application.yml
# 应用服务 WEB 访问端口
server:
port: 8083
#下面这些内容是为了让MyBatis映射
#指定Mybatis的Mapper文件
mybatis:
mapper-locations: classpath:mappers/*xml
#指定Mybatis的实体目录
type-aliases-package: com.example.springbootdatabase.sqlserver.demos.entity
spring:
datasource:
url: jdbc:sqlserver://192.168.184.130:1433; DatabaseName=spring_boot_database;useUnicode=true;characterEncoding=utf-8
username: sa
password: Smileday159@
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
type: com.alibaba.druid.pool.DruidDataSource
druid:
#初始化连接池的连接数量 大小,最小,最大
initial-size: 5
min-idle: 5
max-active: 20
#配置获取连接等待超时的时间
max-wait: 60000
#配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
time-between-eviction-runs-millis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
min-evictable-idle-time-millis: 30000
validation-query: SELECT 1
test-while-idle: true
test-on-borrow: true
test-on-return: false
# 是否缓存preparedStatement,也就是PSCache 官方建议MySQL下建议关闭 个人建议如果想用SQL防火墙 建议打开
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filter:
stat:
merge-sql: true
slow-sql-millis: 5000
#3.基础监控配置
web-stat-filter:
enabled: true
url-pattern: /*
#设置不统计哪些URL
exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
session-stat-enable: true
session-stat-max-count: 100
stat-view-servlet:
enabled: true
url-pattern: /druid/*
reset-enable: true
#设置监控页面的登录名和密码
login-username: admin
login-password: admin
allow: 127.0.0.1
- 测试用例
- web
package com.example.springbootdatabase.oracle.demos.web;
import com.example.springbootdatabase.oracle.demos.entity.User;
import com.example.springbootdatabase.oracle.demos.server.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
public class UserController {
@Autowired
private UserService userService;
@RequestMapping("users")
public List<User> query(){
List<User> list = this.userService.list();
return list;
}
}
- entity
package com.example.springbootdatabase.oracle.demos.entity;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName("T_USER")
public class User {
private int userid;
private String username;
}
- service
package com.example.springbootdatabase.oracle.demos.server;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.springbootdatabase.oracle.demos.dao.UserDao;
import com.example.springbootdatabase.oracle.demos.entity.User;
import org.springframework.stereotype.Service;
@Service
public class UserService extends ServiceImpl<UserDao, User> {
}
- dao
package com.example.springbootdatabase.oracle.demos.dao;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.springbootdatabase.oracle.demos.entity.User;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UserDao extends BaseMapper<User> {
}