数据库错误集锦
一、虚拟机数据库部分
1.1 idea
- 先使用这边的datasource工具进行连接
1.2 虚拟机不能通过ip连接虚拟机mysql
- 未解决
1.3 数据库时区设置
1.4 查看当前数据库时间
select now();
1.5 数据库连接依赖
- mybatis,jpa,jdbc任一依赖
- mysql-connector-java是必须的
1.6 spring boot数据库连接测试
- 注意@Test注解引用的包:
import org.junit.jupiter.api.Test;
package com.example.demo;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import javax.sql.DataSource;
import java.sql.SQLException;
@SpringBootTest
class DemoApplicationTests {
@Autowired
DataSource dataSource;
@Test
public void testMysqlConn() throws SQLException {
System.out.println(dataSource.getConnection());
}
}
- 报错:The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
远程数据库服务没有登录,可以通过mysql -uroot -p登入
1.6 注意spring boot版本
- 关于test注解导入
- 2.2 之前是一个版本,2.2之后又是不一样的版本
在Spring Boot 2.2.X以后使用import org.junit.jupiter.api.Test Junit5
在Spring Boot 2.2.x之前使用import org.junit.Test Junit4
1.7 mybatis-plus设置
- 注意一定要加时区配置
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/shopping?serverTimezone=GMT%2B8
username: root
password: ******
#mybatis输出日志
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
1.8 注意datasource的包
import javax.sql.DataSource;
1.9 完整测试
package com.ybx.sunshine;
import com.ybx.sunshine.entity.User;
import com.ybx.sunshine.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import javax.sql.DataSource;
import java.sql.SQLException;
@SpringBootTest
public class Usertest {
@Autowired
UserMapper userMapper;
@Test
public void testUser(){
User user = userMapper.selectById(1);
System.out.println(user.getName());
}
@Autowired
DataSource dataSource;
@Test
public void testMysqlConn() throws SQLException {
System.out.println(dataSource.getConnection());
}
}
1.10 数据库链接配置
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
# url: jdbc:mysql://localhost:3306/shopping?serverTimezone=GMT%2B8
url: jdbc:mysql://localhost:3306/shopping?serverTimezone=GMT%2B8
username: root
password: ******
#mybatis输出日志
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
二、java部分
2.1Chasret
- 得到所有的编码格式
SortedMap<String, Charset> stringCharsetSortedMap = Charset.availableCharsets();
Set<Map.Entry<String, Charset>> entries = stringCharsetSortedMap.entrySet();
for(Map.Entry<String, Charset> key : entries){
System.out.println(key.getKey() + " " + key.getValue());
}
- 得到对应格式的Charset类
三、mysql
3.1 Host ‘192.168.16.104’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’
四、常用配置命令
4.1 查看mysql 安装位置命令
select @@basedir;
4.2 mysql 数据存放命令
select @@datadir;
4.3 mysql的配置文件与数据在同一目录
4.4 重启mysql服务
service mysql restart;
4.5 flush-hosts
- 要在被远程连接的主机上执行
- 比如在192.168.16.104的电脑上连接192.168.16.103,出现:pymysql.err.OperationalError: (1129, “192.168.16.104’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’”),则需要在192.168.16.104的电脑上进行:
- 不能加;
mysqladmin -uroot -p flush-hosts
4.6 设置数据库时区
set global time_zone = "+00:00";
- 第二次重新进入mysql才生效