SpringBoot整合Mybatis使用注解实现多数据源

添加依赖

pom.xml 添加以下依赖

1
2
3
4
5
6
7
8
9
10
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>

配置数据源

注意事项:db后的url注意使用jdbc-url

1
2
3
4
5
6
7
spring.datasource.first.jdbc-url=jdbc:mysql://localhost:3306/mybatis_test
spring.datasource.first.username=root
spring.datasource.first.password=123456

spring.datasource.second.jdbc-url=jdbc:mysql://localhost:3306/mybatis_test1
spring.datasource.second.username=root
spring.datasource.second.password=123456

创建MyBatis Mapper文件

db1 Mapper

1
2
3
4
5
6
@Repository
public interface FirstUserMapper {

@Select("select * from user")
List<User> findUser();
}

db2 Mapper

1
2
3
4
5
@Repository
public interface SecondUserMapper {
@Select("select * from user")
List<User> findUser();
}

创建数据源配置

数据源

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@Configuration
public class MyBatisConfigurationDatasource {

// 配置db1数据源
@Bean("db1")
@ConfigurationProperties(prefix = "spring.datasource.first")
public DataSource dataSource1() {
return DataSourceBuilder.create().build();
}

// db2数据源
@Bean("db2")
@ConfigurationProperties(prefix = "spring.datasource.second")
public DataSource dataSource2() {
return DataSourceBuilder.create().build();
}
}

MyBatis数据源配置

db1数据源

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
@Configuration
// 配置扫描mapper路径
@MapperScan(value = "com.example.springbootmybatis.first", sqlSessionFactoryRef = "sqlSessionFactoryDb1")
public class Db1Config {

@Autowired
@Qualifier("db1")
private DataSource dataSource;

// 创建SqlSessionFactory
@Bean
public SqlSessionFactory sqlSessionFactoryDb1() {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
try {
return sqlSessionFactoryBean.getObject();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}

// 创建SqlSessionTemplate
@Bean
public SqlSessionTemplate sqlSessionTemplate1() {
return new SqlSessionTemplate(sqlSessionFactoryDb1());
}
}

db2数据源

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
@Configuration
// 配置扫描mapper路径
@MapperScan(value = "com.example.springbootmybatis.first", sqlSessionFactoryRef = "sqlSessionFactoryDb1")
public class Db1Config {

@Autowired
@Qualifier("db1")
private DataSource dataSource;

// 创建SqlSessionFactory
@Bean
public SqlSessionFactory sqlSessionFactoryDb1() {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
try {
return sqlSessionFactoryBean.getObject();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}

// 创建SqlSessionTemplate
@Bean
public SqlSessionTemplate sqlSessionTemplate1() {
return new SqlSessionTemplate(sqlSessionFactoryDb1());
}
}

测试

测试接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@RestController
public class TestController {
// 注入数据源1Mapper
@Autowired
FirstUserMapper firstUserMapper;

// 注入数据源2Mapper
@Autowired
SecondUserMapper secondUserMapper;

@GetMapping("getUser1")
public String findUser1() {
List<User> user1 = firstUserMapper.findUser();
List<User> user2 = secondUserMapper.findUser();
System.out.println(user1);
System.out.println(user2);
Map<String, List<User>> map = new HashMap<>();
map.put("first", user1);
map.put("second", user2);
return JSON.toJSONString(map);
}
}

查看结果

浏览器访问localhost:8080/getUser1 看到以下两个数据源数据,可证明数据源有效:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
{
"first": [
{
"id": 1,
"name": "name"
},
{
"id": 2,
"name": "test"
},
{
"id": 3,
"name": "213"
}
],
"second": [
{
"id": 1,
"name": "db2"
}
]
}

其它问题

打印日志

applicatio.properties添加以下配置即可在控制台查看sql执行日志

1
2
3
# 项目日志目录的日志级别
logging.level.sql=debug
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

双数据源打印日志

MyBatis配置双数据源后会导致日志不打印,修改配置日志level可解决

1
2
logging.level.com.example.springbootmybatis=debug
# 配置到具体目录

MySQL,PostgreSQL 按 年、季、月、周、天 统计

MySQL

按日

1
SELECT COUNT(*),DATE(CreateTime) FROM t_voipchannelrecord WHERE YEAR(CreateTime)='2016' GROUP BY DAY(CreateTime)

按周

1
2
3
4
5
6
7
8
9
-- 
SELECT COUNT(*),WEEK(CreateTime) FROM t_voipchannelrecord WHERE MONTH(CreateTime) = '8' GROUP BY WEEK(CreateTime)

-- 周一到周五每天的统计结果
SELECT COUNT(*),DAYNAME(CreateTime) FROM t_voipchannelrecord WHERE YEAR(CreateTime) = '2016' GROUP BY DAYNAME(CreateTime)

-- 统计本周数据

SELECT COUNT(*) FROM t_voipchannelrecord WHERE MONTH(CreateTime) = MONTH(CURDATE()) AND WEEK(CreateTime) = WEEK(CURDATE())

按月统计

1
SELECT COUNT(*),MONTH(CreateTime) FROM t_voipchannelrecord WHERE YEAR(CreateTime) = '2016' GROUP BY MONTH(CreateTime) 

按季统计

1
SELECT COUNT(*),QUARTER(CreateTime) FROM t_voipchannelrecord WHERE YEAR(CreateTime) = '2016' GROUP BY QUARTER(CreateTime) 

按年统计

1
SELECT COUNT(*),YEAR(CreateTime) FROM t_voipchannelrecord  GROUP BY YEAR(CreateTime)

PostgreSQL

按日统计

1
2
3
to_char( time, 'yyyy-MM-dd' ) AS time

GROUP BY to_char(time, 'yyyy-MM-dd' )

按月统计

1
2
3
to_char(time, 'yyyy-MM' ) AS time

GROUP BY to_char(time, 'yyyy-MM' )

按年统计

1
2
3
to_char( time,'yyyy' ) AS time

GROUP BY to_char( time,'yyyy' )

按小时统计

1
2
3
to_char(time, 'yyyy-MM-dd HH' ) AS time

GROUP BY to_char( time, 'yyyy-MM-dd HH' )

按分钟统计

1
2
3
to_char( time, 'yyyy-MM-dd HH:mm' ) AS time

GROUP BY to_char( time, 'yyyy-MM-dd HH:mm' )

按周统计

按周统计最简单法

对时间row_date字段做处理,变成对应日期周一时间,然后按这个周一的时间去统计。减1的操作表示为对应日期的星期一,减1,2,3,4,5,6,7分别是对应日期的周一,周二,周三,周四,周五、周六、周日。

to_char( time-(extract (dow from time) - 1 ||’day’)::interval,’yyyy-MM-dd’) row_date

然后按上面的语句分组统计即可实现按周统计,下面对应分组函数

GROUP BY to_char(time-(extract (dow from time) - 1 ||’day’)::interval,’yyyy-MM-dd’)

DES可逆加密算法

对敏感信息进行加密

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
package com;

import javax.crypto.Cipher;
import java.security.Key;
import java.util.HashMap;
import java.util.Map;

/**
* 加密
*
* @author
* @since 2021-08-30 14:14
**/
public class DesPlus {

private static final String strDefaultKey = "dhyPksiF";

private final Map<String, Cipher> encryptMap = new HashMap<String, Cipher>();

private final Map<String, Cipher> decryptMap = new HashMap<String, Cipher>();

private static final ThreadLocal<DesPlus> localDesPlus = new ThreadLocal<DesPlus>() {

@Override
protected DesPlus initialValue() {
return new DesPlus();
}
};

private DesPlus() {
}

public static DesPlus getInstance() {

return localDesPlus.get();
}

public String encrypt(String str) {

return encrypt(str, strDefaultKey);
}

public String encrypt(String str, String key) {

if (str == null) {
return null;
}
if (key == null || key.length() < 8) {
return null;
}
Cipher encryptCipher = encryptMap.get(key);
if (encryptCipher == null) {
try {
Key k = getKey(key);
encryptCipher = Cipher.getInstance("DES");
encryptCipher.init(Cipher.ENCRYPT_MODE, k);

encryptMap.put(key, encryptCipher);
} catch (Exception e) {
throw new RuntimeException(e.getMessage(), e);
}
}
try {
byte[] bs = str.getBytes("utf-8");
bs = encryptCipher.doFinal(bs);
return byteArrayToHexString(bs);
} catch (Exception e) {
return null;
}
}

public String decrypt(String str) {
return decrypt(str, strDefaultKey);
}


public String decrypt(String str, String key) {
if (str == null) {
return null;
}
if (key == null || key.length() < 8) {
return null;
}
Cipher decryptCipher = decryptMap.get(key);
if (decryptCipher == null) {
try {
Key k = getKey(key);
decryptCipher = Cipher.getInstance("DES");
decryptCipher.init(Cipher.DECRYPT_MODE, k);

decryptMap.put(key, decryptCipher);
} catch (Exception e) {
throw new RuntimeException(e.getMessage(), e);
}
}
try {
byte[] bs = hexStr2ByteArr(str);
bs = decryptCipher.doFinal(bs);
return new String(bs, "utf-8");
} catch (Exception e) {
return str;
}
}


private Key getKey(String key) {
byte[] arrBTmp = key.getBytes();
// 创建一个空的8位字节数组(默认值为0)
byte[] arrB = new byte[8];
// 将原始字节数组转换为8位
for (int i = 0; i < arrBTmp.length && i < arrB.length; i++) {
arrB[i] = arrBTmp[i];
}
// 生成密钥
Key k = new javax.crypto.spec.SecretKeySpec(arrB, "DES");
return k;
}

public static String byteArrayToHexString(byte b[]) {
StringBuffer resultSb = new StringBuffer();
for (int i = 0; i < b.length; i++)
resultSb.append(byteToHexString(b[i]));

return resultSb.toString();
}

private static String byteToHexString(byte b) {
int n = b;
if (n < 0)
n += 256;
int d1 = n / 16;
int d2 = n % 16;
return hexDigits[d1] + hexDigits[d2];
}

public static byte[] hexStr2ByteArr(String strIn) {

byte[] arrB = strIn.getBytes();

int iLen = arrB.length;
// 两个字符表示一个字节,所以字节数组长度是字符串长度除以2
byte[] arrOut = new byte[iLen / 2];

for (int i = 0; i < iLen; i = i + 2) {

String strTmp = new String(arrB, i, 2);

arrOut[i / 2] = (byte) Integer.parseInt(strTmp, 16);

}

return arrOut;

}

public static void main(String[] args) {
DesPlus desPlus = new DesPlus();

String encrypt = desPlus.encrypt("1500213000000");
System.out.println(encrypt);
String decrypt = desPlus.decrypt(encrypt);
System.out.println(decrypt);
}
}

Mac InetAddress.getLocalHost().getHostAddress(); 很慢

解决方案

  1. 就是把本机的hostname 添加到 hosts 中,例如:
  2. 0.0.1 mac-mini.local
    ::1 mac-mini.local
  3. 执行 scutil –set HostName “localhost”

水平滚动页面与切换页面冲突,关闭手势切换页面

chrome

关闭

1
defaults write com.google.Chrome AppleEnableSwipeNavigateWithScrolls -bool FALSE

开启

1
defaults write com.google.Chrome AppleEnableSwipeNavigateWithScrolls -bool TRUE

edge

关闭

1
defaults write com.microsoft.edgemac AppleEnableSwipeNavigateWithScrolls -bool false

开启

1
defaults write com.microsoft.edgemac AppleEnableSwipeNavigateWithScrolls -bool true

GIT .gitignore修改后不生效解决办法

  1. 清除本地暂存GIT数据
    git rm -rf –cached .

  2. 重新添加文件到暂存区
    git add .

  3. 重新提交文件
    git commit -m “update”

  4. push到分支
    git push origin master

Welcome to Hexo! This is your very first post. Check documentation for more info. If you get any problems when using Hexo, you can find the answer in troubleshooting or you can ask me on GitHub.

Quick Start

Create a new post

1
$ hexo new "My New Post"

More info: Writing

Run server

1
$ hexo server

More info: Server

Generate static files

1
$ hexo generate

More info: Generating

Deploy to remote sites

1
$ hexo deploy

More info: Deployment

1、引入zxing的依赖

1
2
3
4
5
<dependency>
<groupId>com.google.zxing</groupId>
<artifactId>javase</artifactId>
<version>3.4.0</version>
</dependency>

2、二维码工具类的实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
package com.psk.hms.base.util;
import com.google.zxing.*;
import com.google.zxing.client.j2se.BufferedImageLuminanceSource;
import com.google.zxing.common.BitMatrix;
import com.google.zxing.common.HybridBinarizer;
import com.google.zxing.qrcode.decoder.ErrorCorrectionLevel;
import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.geom.RoundRectangle2D;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Hashtable;
/**
* 二维码生成及读取工具类
*
* @author chenj.
* @version 1.0
* @since 2019/3/14 18:46
*/
public class QRCodeUtil {
private static final String CHARSET = "UTF-8";
private static final String FORMAT_NAME = "JPG";
// 二维码尺寸
private static final int QRCODE_SIZE = 200;
// LOGO宽度
private static final int WIDTH = 60;
// LOGO高度
private static final int HEIGHT = 60;
/**
* 创建二维码图片
*
* @param content 二维码内容
* @param logoImgPath Logo
* @param needCompress 是否压缩Logo
* @return 返回二维码图片
* @throws WriterException
* @throws IOException BufferedImage
*/
private static BufferedImage createImage(String content, String logoImgPath, boolean needCompress) throws WriterException, IOException {
Hashtable<EncodeHintType, Object> hints = new Hashtable<EncodeHintType, Object>();
hints.put(EncodeHintType.ERROR_CORRECTION, ErrorCorrectionLevel.H);
hints.put(EncodeHintType.CHARACTER_SET, CHARSET);
hints.put(EncodeHintType.MARGIN, 1);
BitMatrix bitMatrix = new MultiFormatWriter().encode(content, BarcodeFormat.QR_CODE, QRCODE_SIZE, QRCODE_SIZE, hints);
int width = bitMatrix.getWidth();
int height = bitMatrix.getHeight();
BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
for (int x = 0; x < width; x++) {
for (int y = 0; y < height; y++) {
image.setRGB(x, y, bitMatrix.get(x, y) ? 0xFF000000 : 0xFFFFFFFF);
}
}
if (logoImgPath == null || "".equals(logoImgPath)) {
return image;
}
// 插入图片
QRCodeUtil.insertImage(image, logoImgPath, needCompress);
return image;
}
/**
* 添加Logo
*
* @param source 二维码图片
* @param logoImgPath Logo
* @param needCompress 是否压缩Logo
* @throws IOException void
*/
private static void insertImage(BufferedImage source, String logoImgPath, boolean needCompress) throws IOException {
File file = new File(logoImgPath);
if (!file.exists()) {
return;
}
Image src = ImageIO.read(new File(logoImgPath));
int width = src.getWidth(null);
int height = src.getHeight(null);
if (needCompress) { // 压缩LOGO
if (width > WIDTH) {
width = WIDTH;
}
if (height > HEIGHT) {
height = HEIGHT;
}
Image image = src.getScaledInstance(width, height, Image.SCALE_SMOOTH);
BufferedImage tag = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
Graphics g = tag.getGraphics();
g.drawImage(image, 0, 0, null); // 绘制缩小后的图
g.dispose();
src = image;
}
// 插入LOGO
Graphics2D graph = source.createGraphics();
int x = (QRCODE_SIZE - width) / 2;
int y = (QRCODE_SIZE - height) / 2;
graph.drawImage(src, x, y, width, height, null);
Shape shape = new RoundRectangle2D.Float(x, y, width, width, 6, 6);
graph.setStroke(new BasicStroke(3f));
graph.draw(shape);
graph.dispose();
}
/**
* 生成带Logo的二维码
*
* @param content 二维码内容
* @param logoImgPath Logo
* @param destPath 二维码输出路径
* @param needCompress 是否压缩Logo
* @throws Exception void
*/
public static void encode(String content, String logoImgPath, String destPath, boolean needCompress) throws Exception {
BufferedImage image = QRCodeUtil.createImage(content, logoImgPath, needCompress);
mkdirs(destPath);
ImageIO.write(image, FORMAT_NAME, new File(destPath));
}
/**
* 生成不带Logo的二维码
*
* @param content 二维码内容
* @param destPath 二维码输出路径
*/
public static void encode(String content, String destPath) throws Exception {
QRCodeUtil.encode(content, null, destPath, false);
}
/**
* 生成带Logo的二维码,并输出到指定的输出流
*
* @param content 二维码内容
* @param logoImgPath Logo
* @param output 输出流
* @param needCompress 是否压缩Logo
*/
public static void encode(String content, String logoImgPath, OutputStream output, boolean needCompress) throws Exception {
BufferedImage image = QRCodeUtil.createImage(content, logoImgPath, needCompress);
ImageIO.write(image, FORMAT_NAME, output);
}
/**
* 生成不带Logo的二维码,并输出到指定的输出流
*
* @param content 二维码内容
* @param output 输出流
* @throws Exception void
*/
public static void encode(String content, OutputStream output) throws Exception {
QRCodeUtil.encode(content, null, output, false);
}
/**
* 二维码解析
*
* @param file 二维码
* @return 返回解析得到的二维码内容
* @throws Exception String
*/
public static String decode(File file) throws Exception {
BufferedImage image;
image = ImageIO.read(file);
if (image == null) {
return null;
}
BufferedImageLuminanceSource source = new BufferedImageLuminanceSource(image);
BinaryBitmap bitmap = new BinaryBitmap(new HybridBinarizer(source));
Result result;
Hashtable<DecodeHintType, Object> hints = new Hashtable<DecodeHintType, Object>();
hints.put(DecodeHintType.CHARACTER_SET, CHARSET);
result = new MultiFormatReader().decode(bitmap, hints);
String resultStr = result.getText();
return resultStr;
}
/**
* 二维码解析
*
* @param path 二维码存储位置
* @return 返回解析得到的二维码内容
* @throws Exception String
*/
public static String decode(String path) throws Exception {
return QRCodeUtil.decode(new File(path));
}
/**
* 判断路径是否存在,如果不存在则创建
*
* @param dir 目录
*/
public static void mkdirs(String dir) {
if (dir != null && !"".equals(dir)) {
File file = new File(dir);
if (!file.isDirectory()) {
file.mkdirs();
}
}
}
}

3、测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
生成不带logo的二维码
public static void main(String[] args) throws Exception {
String dir = "d:/qrcode.jpg";
String content = "www.baidu.com";
File file = new File(dir);
QRCodeUtil.encode(content, new FileOutputStream(file));
System.out.println(QRCodeUtil.decode(file));
}
生成带logo的二维码
public static void main(String[] args) throws Exception {
String dir = "d:/qrcode.jpg";
String content = "www.baidu.com";
String logoImgPath = "d:/logo.png";
File file = new File(dir);
QRCodeUtil.encode(content, logoImgPath, new FileOutputStream(file), true);
System.out.println(QRCodeUtil.decode(file));
}

阻塞队列

方法\处理方式 抛出异常 返回特殊值 一直阻塞 超时退出
插入方法 add(e) offer(e) put(e) offer(e,time,unit)
移除方法 remove(e) poll() take() poll(time,unit)
检查方法 element(e) peek() 不可用 不可用

远程拉取项目

1
git clone xxxx.git

查看分支

1
git branch -a

切换分支

1
git checkout -b dev origin/dev

从指定分支更新项目

1
git pull origin dev

提交分支

1
git push origin dev

连接分支

1
git push -set upstream 

查看当前 远程url

1
git remote -v

git 帮助文档

git帮助文档

0%