在Ubuntu下基于Postfix/Dovecot安装自有邮箱服务

博客已经搭建了很多年了,一直想拥有一个自己的邮箱服务器,最近机缘巧合,给另外一个项目搭建了一个,刚好随手,就给自己的域名也搭建一个,拿来用一用装逼。

顺便,把整个流程记录下来。

1、以我的域名 stamhe.com 为例,要部署邮箱服务的IP地址为 149.28.109.196

2、建议购买vultr的主机,好处后面就知道了

主要参考文章为Linode的这篇文章:

https://www.linode.com/docs/email/postfix/email-with-postfix-dovecot-and-mysql/

到达率参考

http://lomu.me/post/SPF-DKIM-DMARC-PTR

一、申请SSL证书

通过Let’s Encrypt申请免费的SSL证书

cd /root
wget https://dl.eff.org/certbot-auto && chmod a+x certbot-auto
# 申请通配符域名
./certbot-auto -d "*.stamhe.com" --manual --preferred-challenges dns-01 certonly 
# 申请单一域名
./certbot-auto -d "mail.stamhe.com" --standalone certonly

按命令行提示进行操作,即可得到如下的证书

root@stamhe:~/certauto# ./certbot-auto certificates
Saving debug log to /var/log/letsencrypt/letsencrypt.log

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Found the following certs:
  Certificate Name: stamhe.com
    Domains: *.stamhe.com
    Expiry Date: 2019-10-22 05:33:50+00:00 (VALID: 89 days)
    Certificate Path: /etc/letsencrypt/live/stamhe.com/fullchain.pem
    Private Key Path: /etc/letsencrypt/live/stamhe.com/privkey.pem

其中,fullchain.pem 为公钥文件, privkey.pem 为私钥文件.

Let’s Encrypt 是免费证书,有效期为 90 天,快到期前,需要执行下面的命令申请延期

./certbot-auto  renew --manual-auth-hook certonly

注意: 如果服务器本身安装得有Web服务器,如Nginx、Apache之类的,在执行延期申请命令前,需要先停止在80、443端口的监听,延期成功后,再启动。

二、配置基本的DNS相关信息

类型名称
Amail149.28.109.196
MX@mail.stamhe.com
TXT@v=spf1 mx:mail.stamhe.com ip4:149.28.109.196 ~all

配置spf主要是为了防止发信被对方放垃圾邮箱的一个手段,后面会有详细介绍。

三、配置基础的MySQL账户信息

假设MySQL的连接信息如下:

localhost
3306
root
123456

创建库、表

create database mailserver;
use mailserver;

CREATE TABLE `virtual_domains` (  
  `id` int(11) NOT NULL auto_increment,  
  `name` varchar(50) NOT NULL,  
  PRIMARY KEY (`id`))  
ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `virtual_users` (  
`id` int(11) NOT NULL auto_increment,  
`domain_id` int(11) NOT NULL,  
`password` varchar(106) NOT NULL,  
`email` varchar(100) NOT NULL,  
PRIMARY KEY (`id`),  
UNIQUE KEY `email` (`email`),  
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE)  
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `virtual_aliases` (  
`id` int(11) NOT NULL auto_increment,  
`domain_id` int(11) NOT NULL,  
`source` varchar(100) NOT NULL,  
`destination` varchar(100) NOT NULL,  
PRIMARY KEY (`id`),  
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE)  
ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入两个测试用的邮箱账户信息

insert into virtual_domains(id,name) values(1,'mail.stamhe.com');     
insert into virtual_domains(id,name) values(2,'stamhe.com');


insert into virtual_users(id,domain_id,password,email) values (1,2,ENCRYPT('123456'),'love@stamhe.com');

insert into virtual_users(id,domain_id,password,email) values (2,2,ENCRYPT('12345678'),'you@stamhe.com');


insert into virtual_aliases(id,domain_id,source,destination) values (1,2,'all@stamhe.com','love@stamhe.com');

insert into virtual_aliases(id,domain_id,source,destination) values (2,2,'all@stamhe.com','you@stamhe.com');

四、Postfix的安装以及相关配置

安装Postfix

apt-get install postfix postfix-mysql postfix-doc mailutils

安装过程中需要选择Postfix的类型,请选择Internet Site,还会需要输入System mail name,这里请输入你要收发邮件的域名地址,我这儿是输入: stamhe.com

Postfix配置项的官方文档

http://www.postfix.org/documentation.html

查看Postfix的版本号

postconf -d | grep mail_version

打开 /etc/postfix/main.cf

1、删除掉下面的所有默认配置

# TLS parameters
smtpd_tls_cert_file=/etc/ssl/certs/ssl-cert-snakeoil.pem
smtpd_tls_key_file=/etc/ssl/private/ssl-cert-snakeoil.key
smtpd_use_tls=yes
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache

2、新增下面的配置

smtpd_tls_cert_file=/etc/letsencrypt/archive/stamhe.com/fullchain1.pem
smtpd_tls_key_file=/etc/letsencrypt/archive/stamhe.com/privkey1.pem
smtpd_use_tls=yes
smtpd_tls_auth_only = yes
smtp_tls_security_level = may
smtpd_tls_security_level = may

3、新增下面的配置

smtpd_sasl_type = dovecot
smtpd_sasl_path = private/auth
smtpd_sasl_auth_enable = yes
message_size_limit = 15728640
broken_sasl_auth_clients = yes
smtpd_sasl_security_options = noanonymous, noplaintext
smtpd_sasl_tls_security_options = noanonymous
smtpd_helo_restrictions =
    permit_mynetworks,
    permit_sasl_authenticated,
    reject_invalid_helo_hostname,
    reject_non_fqdn_helo_hostname

smtpd_recipient_restrictions =
    permit_mynetworks,
    permit_sasl_authenticated,
    reject_non_fqdn_recipient,
    reject_unknown_recipient_domain,
    reject_unlisted_recipient,
    reject_unauth_destination

smtpd_sender_restrictions =
    permit_mynetworks,
    permit_sasl_authenticated,
    reject_non_fqdn_sender,
    reject_unknown_sender_domain

smtpd_relay_restrictions =
    permit_mynetworks,
    permit_sasl_authenticated,
    defer_unauth_destination

4、修改mydestination

mydestination = localhost

注意:mydestination参数中,不能出现在前面 virtual_domains 表中出现的域名,否则创建的账户会无法收到邮件。

5、新增如下配置,告诉Postfix不要使用LDA「Local Delivery Agent」转而使用Dovecot的LMTP完成本地邮件投递

virtual_transport = lmtp:unix:private/dovecot-lmtp

6、新增如下配置,告诉Postfix去MySQL数据库种寻找域名、用户帐号密码及邮件别名等信息

virtual_mailbox_domains = mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias-maps.cf

7、配置连接MySQL相关的信息

# cat /etc/postfix/mysql-virtual-mailbox-domains.cf
user = root
password = 123456
hosts = 127.0.0.1
dbname = mailserver
query = SELECT 1 FROM virtual_domains WHERE name='%s'


# cat /etc/postfix/mysql-virtual-mailbox-maps.cf
user = root
password = 123456
hosts = 127.0.0.1  
dbname = mailserver
query = SELECT 1 FROM virtual_users WHERE email='%s'

# cat /etc/postfix/mysql-virtual-alias-maps.cf
user = root
password = 123456
hosts = 127.0.0.1
dbname = mailserver
query = SELECT destination FROM virtual_aliases WHERE source='%s'

执行下面的命令重启postfix

systemctl restart postfix

测试上面的MySQL连接信息

# postmap -q stamhe.com mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
1

# postmap -q love@stamhe.com mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
1

# postmap -q all@stamhe.com mysql:/etc/postfix/mysql-virtual-alias-maps.cf
love@stamhe.com,you@stamhe.com

8、打开 /etc/postfix/master.cf 文件,去掉里面 submission和smtps所在的两行,并将其注释去掉。

submission inet n       -       y       -       -       smtpd
  -o syslog_name=postfix/submission
  -o smtpd_tls_security_level=encrypt
  -o smtpd_sasl_auth_enable=yes
  -o smtpd_reject_unlisted_recipient=no
  -o smtpd_client_restrictions=$mua_client_restrictions
  -o smtpd_helo_restrictions=$mua_helo_restrictions
  -o smtpd_sender_restrictions=$mua_sender_restrictions
  -o smtpd_recipient_restrictions=
  -o smtpd_relay_restrictions=permit_sasl_authenticated,reject
  -o milter_macro_daemon_name=ORIGINATING
smtps     inet  n       -       y       -       -       smtpd
  -o syslog_name=postfix/smtps
  -o smtpd_tls_wrappermode=yes
  -o smtpd_sasl_auth_enable=yes
  -o smtpd_reject_unlisted_recipient=no
  -o smtpd_client_restrictions=$mua_client_restrictions
  -o smtpd_helo_restrictions=$mua_helo_restrictions
  -o smtpd_sender_restrictions=$mua_sender_restrictions
  -o smtpd_recipient_restrictions=
  -o smtpd_relay_restrictions=permit_sasl_authenticated,reject
  -o milter_macro_daemon_name=ORIGINATING

9、再次重启postfix

10、完整的main.cf配置文件如下

root@stamhe:~# cat /etc/postfix/main.cf
# See /usr/share/postfix/main.cf.dist for a commented, more complete version


# Debian specific:  Specifying a file name will cause the first
# line of that file to be used as the name.  The Debian default
# is /etc/mailname.
#myorigin = /etc/mailname

myhostname = stamhe
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases
mydestination = $myhostname, stamhe, localhost.localdomain, , localhost
relayhost = 
mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
mailbox_size_limit = 0
recipient_delimiter = +
inet_interfaces = all
inet_protocols = all
html_directory = /usr/share/doc/postfix/html

smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)
biff = no

# appending .domain is the MUA's job.
append_dot_mydomain = no

# Uncomment the next line to generate "delayed mail" warnings
#delay_warning_time = 4h

readme_directory = /usr/share/doc/postfix

# TLS parameters
smtpd_tls_cert_file=/etc/letsencrypt/archive/stamhe.com/fullchain1.pem
smtpd_tls_key_file=/etc/letsencrypt/archive/stamhe.com/privkey1.pem
smtpd_use_tls=yes
smtpd_tls_auth_only = yes
smtp_tls_security_level = may
smtpd_tls_security_level = may

# See /usr/share/doc/postfix/TLS_README.gz in the postfix-doc package for
# information on enabling SSL in the smtp client.



smtpd_sasl_type = dovecot
smtpd_sasl_path = private/auth
smtpd_sasl_auth_enable = yes
message_size_limit = 15728640
broken_sasl_auth_clients = yes
smtpd_sasl_security_options = noanonymous, noplaintext
smtpd_sasl_tls_security_options = noanonymous
smtpd_helo_restrictions =
    permit_mynetworks,
    permit_sasl_authenticated,
    reject_invalid_helo_hostname,
    reject_non_fqdn_helo_hostname

smtpd_recipient_restrictions =
    permit_mynetworks,
    permit_sasl_authenticated,
    reject_non_fqdn_recipient,
    reject_unknown_recipient_domain,
    reject_unlisted_recipient,
    reject_unauth_destination

smtpd_sender_restrictions =
    permit_mynetworks,
    permit_sasl_authenticated,
    reject_non_fqdn_sender,
    reject_unknown_sender_domain

smtpd_relay_restrictions =
    permit_mynetworks,
    permit_sasl_authenticated,
    defer_unauth_destination

virtual_transport = lmtp:unix:private/dovecot-lmtp

virtual_mailbox_domains = mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias-maps.cf

# Even more Restrictions and MTA params
disable_vrfy_command = yes
strict_rfc821_envelopes = yes

smtpd_delay_reject = yes
smtpd_helo_required = yes
smtp_always_send_ehlo = yes

smtpd_timeout = 30s
smtp_helo_timeout = 15s
smtp_rcpt_timeout = 15s
smtpd_recipient_limit = 40
minimal_backoff_time = 180s
maximal_backoff_time = 3h

# Reply Rejection Codes
invalid_hostname_reject_code = 550
non_fqdn_reject_code = 550
unknown_address_reject_code = 550
unknown_client_reject_code = 550
unknown_hostname_reject_code = 550
unverified_recipient_reject_code = 550
unverified_sender_reject_code = 550



# rate
smtpd_client_connection_count_limit = 100
smtpd_client_connection_rate_limit = 100
smtpd_client_message_rate_limit = 100
smtpd_client_recipient_rate_limit = 100
smtpd_client_new_tls_session_rate_limit = 100
smtpd_client_auth_rate_limit = 100
root@stamhe:~# 

五、Dovecot的安装及相关配置

Dovecot充当IMAP、POP服务器的角色,同时它也将负责用户登录时用户身份的验证「Dovecot会将真正的验证工作交给MySQL处理」。因为使用SSL,Dovecot将会使用993「IMAP协议」及995「POP协议」与外界通讯,所以需要允许放行993、995、465三个端口。

1、安装Dovecot

apt-get install dovecot-core dovecot-imapd dovecot-pop3d dovecot-lmtpd dovecot-mysql

2、修改 /etc/dovecot/dovecot.conf 配置文件

!include_try /usr/share/dovecot/protocols.d/*.protocol  
protocols = imap pop3 lmtp

3、修改 /etc/dovecot/conf.d/10-mail.conf 配置文件

mail_location = maildir:/var/mail/vhosts/%d/%n
mail_privileged_group = mail

同时执行下面的命令,创建 mail_location需要的工作目录

# mkdir -p /var/mail/vhosts/stamhe.com
# groupadd -g 5000 vmail  
# useradd -g vmail -u 5000 vmail -d /var/mail
# chown -R vmail:vmail /var/mail

4、修改 /etc/dovecot/conf.d/10-auth.conf 配置文件

disable_plaintext_auth = yes
auth_mechanisms = plain login

默认情况下,Dovecot是允许Ubuntu系统用户登录使用的,我们需要将其禁用。找到文件种如下内容并将其注释:

#!include auth-system.conf.ext

开启Dovecot的MySQL支持,取消!include auth-sql.conf.ext的注释符号:

#!include auth-system.conf.ext  
!include auth-sql.conf.ext  
#!include auth-ldap.conf.ext  
#!include auth-passwdfile.conf.ext  
#!include auth-checkpassword.conf.ext  
#!include auth-vpopmail.conf.ext  
#!include auth-static.conf.ext

5、修改 /etc/dovecot/conf.d/auth-sql.conf.ext 配置文件

passdb {  
    driver = sql  
    args = /etc/dovecot/dovecot-sql.conf.ext  
}  

userdb {  
    driver = static  
    args = uid=vmail gid=vmail home=/var/mail/vhosts/%d/%n  
}

6、修改 /etc/dovecot/dovecot-sql.conf.ext 配置文件

driver = mysql
connect = host=127.0.0.1 dbname=mailserver user=root password=123456
default_pass_scheme = CRYPT
password_query = SELECT email as user, password FROM virtual_users WHERE email='%u'

注意: default_pass_scheme代表使用的密码的加密存储算法,我们前面在SQL语句中使用的是encrypt函数,对应的就是CRYPT,当然也可以使用其他算法,自己研究。

7、修改一下目录权限

chown -R vmail:dovecot /etc/dovecot

chmod -R o-rwx /etc/dovecot

8、修改 /etc/dovecot/conf.d/10-master.conf 配置文件

service imap-login {
  inet_listener imap {
    port = 0
  }
  inet_listener imaps {
    port = 993
    ssl = yes
  }
}

service pop3-login {
  inet_listener pop3 {
    port = 0
  }
  inet_listener pop3s {
    port = 995
    ssl = yes
  }
}

service lmtp {  
        unix_listener /var/spool/postfix/private/dovecot-lmtp {  
        mode = 0600  
        user = postfix  
        group = postfix  
  }
}


service auth {  
    unix_listener /var/spool/postfix/private/auth {  
            mode = 0666  
            user = postfix  
            group = postfix  
    }  

    unix_listener auth-userdb {  
            mode = 0600  
            user = vmail  
            #group =  
    }  

    user = dovecot  
}

service auth-worker {  
    user = vmail  
}

9、修改 /etc/dovecot/conf.d/10-ssl.conf 配置文件

ssl = required
ssl_cert = </etc/letsencrypt/archive/stamhe.com/fullchain1.pem
ssl_key = </etc/letsencrypt/archive/stamhe.com/privkey1.pem

注意: 这儿的证书路径最前面,有一个【<】开头,别漏了。。。

10、修改 /etc/dovecot/dovecot.conf 配置文件

postmaster_address = postmaster at stamhe.com

11、重启dovecot服务

systemctl restart dovecot

六、测试接收、发送邮件

建议使用 163、gmail来做测试对象, qq邮箱很多变态规则,在做下面的配置之前,可能你的很多发送都是失败的。。。

调试日志在

/var/log/mail.log

/var/log/mail.log
/var/log/mail.err

七、邮件服务器添加SPF、DKIM、DMARC、PTR提高送达率

主要参考

http://lomu.me/post/SPF-DKIM-DMARC-PTR

1、SPF的配置

我们前面已经做了,在DNS中,新增下面的TXT记录即可

v=spf1 mx:mail.stamhe.com ip4:149.28.109.196 ~all

2、DMARC配置

在DNS中,新增下面的TXT记录

主机名:
_dmarc

记录值:
v=DMARC1;p=reject;rua=support@stamhe.com

3、PTR配置

如果你是使用的vultr,那就简单了,在Server Detail -> Setting下面,修改 Reverse DNS 为【mail.stamhe.com】即可。

如果是其他的主机厂商,问客服

使用如下命令测试结果

dig -x 你的邮箱服务器IP

4、DKIM配置

安装OpenDKIM

apt install opendkim opendkim-tools

A、修改  /etc/opendkim.conf  配置文件

在最后面追加如下配置内容

AutoRestart             Yes
AutoRestartRate         10/1h
UMask                   002
Syslog                  yes
SyslogSuccess           Yes
LogWhy                  Yes

Canonicalization        relaxed/simple

ExternalIgnoreList      refile:/etc/opendkim/TrustedHosts
InternalHosts           refile:/etc/opendkim/TrustedHosts
KeyTable                refile:/etc/opendkim/KeyTable
SigningTable            refile:/etc/opendkim/SigningTable

Mode                    sv
PidFile                 /var/run/opendkim/opendkim.pid
SignatureAlgorithm      rsa-sha256

UserID                  opendkim:opendkim

Socket                  inet:12301@localhost

B、修改 /etc/default/opendkim 配置文件

SOCKET="inet:12301@localhost"

C、修改 /etc/postfix/main.cf 配置文件

milter_protocol = 6
milter_default_action = accept

smtpd_milters = inet:localhost:12301
non_smtpd_milters = inet:localhost:12301

milter_protocol = 6 这个参数,根据下面的命令来

# postconf -d | grep mail_version
当postfix版本为2.6+,milter_protocol=6; 版本为2.3到2.5,milter_protocol=2;

D、创建目录

mkdir -p /etc/opendkim/keys

E、修改 /etc/opendkim/TrustedHosts  配置文件

127.0.0.1
localhost
192.168.0.1/24

 *.stamhe.com

F、创建opendkim所需要的key

# cd /etc/opendkim/keys 
# mkdir stamhe.com
# cd stamhe.com
# opendkim-genkey -s mail –d stamhe.com
# chown opendkim:opendkim mail.private

注意,有一些DNS服务商的TXT值有限制长度,opendkim-genkey默认生成的为2048的key,太长了,对于有长度限制的,可以使用 1024的key

# opendkim-genkey -s mail -d stamhe.com -b 1024

得到如下两个文件

total 16
drwxr-xr-x 2 root     root     4096 Jul 24 08:05 ./
drwxr-xr-x 3 root     root     4096 Jul 24 08:04 ../
-rw------- 1 opendkim opendkim  891 Jul 24 08:05 mail.private
-rw------- 1 root     root      308 Jul 24 08:05 mail.txt

G、修改 /etc/opendkim/KeyTable 配置文件

mail._domainkey.stamhe.com 
 stamhe.com:mail:/etc/opendkim/keys/stamhe.com/mail.private

H、修改 /etc/opendkim/SigningTable 配置文件

*@stamhe.com    mail._domainkey.stamhe.com

H、新增一条DNS记录

主机名:
mail._domainkey

记录值:
v=DKIM1; k=rsa; p=MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCptXy6RtzzWujOEfCAm96Kw/OAzGXGFXzky69T6crWOFl71R7Km8WDbJxLqmz1EfDwq5gVP7QP8lkKBbn/28KLIRlSmkzMaMB+JaZinZ3UJBvKZz6thBePSbAx9AuNU/IwLFlH1NXkK7DBk657EZusD3FgV3rx3sZzH6HcJ9O55wIDAQAB

记录值的来源:

root@stamhe:/etc/opendkim/keys/stamhe.com# cat mail.txt 
mail._domainkey IN      TXT     ( "v=DKIM1; k=rsa; "
          "p=MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCptXy6RtzzWujOEfCAm96Kw/OAzGXGFXzky69T6crWOFl71R7Km8WDbJxLqmz1EfDwq5gVP7QP8lkKBbn/28KLIRlSmkzMaMB+JaZinZ3UJBvKZz6thBePSbAx9AuNU/IwLFlH1NXkK7DBk657EZusD3FgV3rx3sZzH6HcJ9O55wIDAQAB" )  ; ----- DKIM key mail for stamhe.com

I、重启postfix、opendkim服务

systemctl restart postfix
systemctl restart opendkim

测试opendkim的配置结果

opendkim-testkey -d stamhe.com -s mail -vvv

所有这些配置完毕,可以访问 下面的网站,用配置的账户信息,发送一封已经过去,测试一下得分情况。

http://www.mail-tester.com

在Mac、Linux平台下面编译比特币源码

下载托管在Github上面的比特币源码。以当前最新的 v0.18.0代码为例。

git clone https://github.com/bitcoin/bitcoin.git # 克隆最新版的比特币源码到本地。
cd bitcoin # 切换至比特币根目录。
git checkout v0.18.0 # 在当前分支上切换至 tag 为 v0.18.0 的版本,或省略此步骤以编译最新版。
git status # 查看当前状态(这里会显示版本信息),此步可省略。

Mac下面的依赖安装:

brew install automake berkeley-db4 libtool boost@1.64 miniupnpc openssl pkg-config protobuf python qt libevent qrencode

brew 默认安装指定库的最新版本,可以使用命令$ brew search <libname>查看指定库的所有版本。 bitcoin v0.18.0 对应的 boost 库的版本为1.64.0,可以从 bitcoin/depends/packages/boost.mk 中获取当前版本比特币对应的 boost 库的版本。

Ubuntu 16.04.x下面的依赖安装

安装基础编译依赖:

apt-get install build-essential libtool autotools-dev automake pkg-config libssl-dev libevent-dev bsdmainutils python3

安装Boost依赖:

apt-get install libboost-system-dev libboost-filesystem-dev libboost-chrono-dev libboost-program-options-dev libboost-test-dev libboost-thread-dev

apt-get install libboost-all-dev

ubuntu 16.04.* 默认安装 boost 库的版本为 1.58.0,可满足 bitcoin v0.18.0 对 boost 库的需求。

安装DB4.8依赖:

apt-get install software-properties-common
add-apt-repository ppa:bitcoin/bitcoin
apt-get update
apt-get install libdb4.8-dev libdb4.8++-dev

安装upnp依赖miniupnpc依赖:

apt-get install libminiupnpc-dev

安装ZMQ依赖:

apt-get install libzmq3-dev

安装QT GUI依赖支持:

apt-get install libqt5gui5 libqt5core5a libqt5dbus5 qttools5-dev qttools5-dev-tools libprotobuf-dev protobuf-compiler # Qt 5
apt-get install libqt4-dev libprotobuf-dev protobuf-compiler # Qt 4 可选
apt-get install libqrencode-dev

编译构建:

./autogen.sh

./configure # 定制并生成 Makefile,例:关闭钱包功能,使用静态库链接得到移植后不依赖库文件的可执行文件,指定 boost 库路径等。

make -j4  # 使用 Makefile 进行比特币源码的编译,编译完成后会生成 4 至 6 个 ELF 程序,分别为 bitcoind、bitcoin-cli、bitcoin-tx、test_bitcoin,若安装了 Qt 图形库,则会增加 bitcoin-qt、test_bitcoin-qt。

make install # 该项可选,把编译好的比特币程序拷贝到系统默认的可执行程序目录 /usr/local/bin 下。

注:Mac 无法构建 bitcoin v0.18.0 的可执行文件 bitcoin-qt,因为 Mac 不支持 bitcoin v0.18.0 对应的 qt5.5 的构建。

《SpringBoot2从入门到工程实战》第十四篇:gRPC的接入

最近在改造公司的业务过程中,之前一直使用的是HTTP接口,接口性能基本在10-12 ms之间,对于短连接调用来说,性能其实已经算非常高的了,但是实际中发现,业务端的代码,写的实在是让人崩溃,部分业务接口,对于基础接口的调用,超过了20次+,如果每一个接口都是10-12 ms, 光是这些基础接口的调用,都是一件非常消耗时间的事情,没有办法,只好引入RPC框架了。

因为业务端是PHP 7.1,需要达到跨语言调用,所以目前合适的,比较成熟的也就是thrift、grpc两种了,其实dubbo也有一个基于swoole的php实现,但是经过测试,发现官方的框架,实在是太过随意了,怕有大坑,不敢使用。

经过权衡,还是最终选用了gRPC,所以本文还是以gRPC作为实践。关于gRPC与thrift之间的功能、性能差异,可以在网上找一找其他博主的文章。

本章示例工程名称:springboot_worker_grpc

代码地址:https://github.com/stamhe/SpringBoot-Work-Example

目录结构如下:


pom.xml内容:

<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.stamhe</groupId>
  <artifactId>springboot_worker_grpc</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>springboot_worker_grpc</name>
  <url>http://maven.apache.org</url>

  <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
        
        <grpc.version>1.0.0</grpc.version>
        <protobuf.plugin.version>0.5.0</protobuf.plugin.version>
        <protoc.version>3.7.1</protoc.version>
  </properties>
  
  
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

  <dependencies>
    <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-web</artifactId>
    </dependency>
    
    <dependency>  
        <groupId>org.springframework.boot</groupId>  
        <artifactId>spring-boot-starter-thymeleaf</artifactId>  
    </dependency> 
    
    <dependency>
  		<groupId>net.devh</groupId>
  		<artifactId>grpc-spring-boot-starter</artifactId>
  		<version>2.3.0.RELEASE</version>
	</dependency>
	
	
    <dependency>
  		<groupId>com.alibaba</groupId>
  		<artifactId>fastjson</artifactId>
  		<version>1.2.56</version>
	</dependency>
	
	
    <dependency>
    	<groupId>de.codecentric</groupId>
    	<artifactId>spring-boot-admin-starter-client</artifactId>
    	<version>2.1.4</version>
	</dependency>
	
	<dependency>
    	<groupId>org.springframework.boot</groupId>
    	<artifactId>spring-boot-starter-security</artifactId>
	</dependency>
    
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <scope>test</scope>
    </dependency>
  </dependencies>
  
  <build>
  		<extensions>
            <extension>
                <groupId>kr.motd.maven</groupId>
                <artifactId>os-maven-plugin</artifactId>
                <version>1.5.0.Final</version>
            </extension>
        </extensions>
        
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            
            <plugin>
                <groupId>org.xolstice.maven.plugins</groupId>
                <artifactId>protobuf-maven-plugin</artifactId>
                <version>${protobuf.plugin.version}</version>
                <configuration>
                	<protoSourceRoot>${project.basedir}/src/main/proto</protoSourceRoot>
                    <protocArtifact>com.google.protobuf:protoc:${protoc.version}:exe:${os.detected.classifier}</protocArtifact>
                    <pluginId>grpc-java</pluginId>
                    <pluginArtifact>io.grpc:protoc-gen-grpc-java:${grpc.version}:exe:${os.detected.classifier}</pluginArtifact>
                    
                    <outputDirectory>${project.basedir}/src/main/java</outputDirectory>
                    <!-- 
                    巨坑的地方,一定要设置为false,否则上面设置的 src/main/java 目录的代码会全部被删除.
                    https://github.com/xolstice/protobuf-maven-plugin/issues/16
                    -->
                     <clearOutputDirectory>false</clearOutputDirectory>
                </configuration>
                <executions>
                    <execution>
                        <goals>
                            <goal>compile</goal>
                            <goal>compile-custom</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

注意:

1、使用了grpc-spring-boot-starter,免得自己手动集成很多东西

2、proto定义文件放置在 src/main/proto目录, 并在pom.xml添加自动生成命令即执行mvn compile 的时候,自动在 src/main/java目录生成protobuf和stub文件

3、clearOutputDirectory一定要配置为 false, 否则 src/main/java 目录代码会被删除。。。

App.java 内容:

package com.stamhe.springboot;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class App 
{
    public static void main( String[] args )
    {
    	SpringApplication.run(App.class, args);
    }
}

UserModel.java内容:

package com.stamhe.springboot.user.model;

import java.io.Serializable;

public class UserModel implements Serializable {
	private Long user_id;
	private String name;
	private String createTime;
	public Long getUser_id() {
		return user_id;
	}
	public void setUser_id(Long user_id) {
		this.user_id = user_id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getCreateTime() {
		return createTime;
	}
	public void setCreateTime(String createTime) {
		this.createTime = createTime;
	}
	@Override
	public String toString() {
		return "UserModel [user_id=" + user_id + ", name=" + name + ", createTime=" + createTime + "]";
	}
}

UserService.java内容:

package com.stamhe.springboot.user.service;

import com.alibaba.fastjson.JSON;
import com.stamhe.springboot.common.proto.CommonReply;
import com.stamhe.springboot.user.model.UserModel;
import com.stamhe.springboot.user.proto.UserGrpc;
import com.stamhe.springboot.user.proto.UserRequest;

import io.grpc.stub.StreamObserver;
import net.devh.boot.grpc.server.service.GrpcService;

@GrpcService
public class UserService extends UserGrpc.UserImplBase {
	@Override
	public void userInfo(UserRequest request, StreamObserver<CommonReply> responseObserver) {

		UserModel userModel = new UserModel();
		userModel.setUser_id(request.getUserId());
		userModel.setName("Stam He");
		userModel.setCreateTime("2019-04-17 12:00:00");
		
		String json_data = JSON.toJSONString(userModel);
		
		CommonReply reply = CommonReply.newBuilder().setCode(0).setMessage("Success From UserService")
				.setData(json_data).build();
		
        responseObserver.onNext(reply);
        responseObserver.onCompleted();
	}
}

application.properties

grpc.server.port=9090
grpc.server.address=0.0.0.0

server.port=8080

我们自定义了grpc的端口为 9090

com.stamhe.springboot.*.proto 包下面的文件,都是自动生成的 protobuf和stub文件,此处不再列出。

到此,java相关的gRPC部分已经完全开发完成了。

从proto文件生成PHP代码,需要依赖 protoc、grpc_php_plugin这两个工具

安装方式如下:

git clone https://github.com/grpc/grpc.git
cd grpc
git submodule update --init
make grpc_php_plugin -j4

cp bins/opt/grpc_php_plugin   /usr/bin/
cp bins/opt/protobuf/protoc   /usr/bin/

grpc代码库比较大, 比较耗时,如果是在国内,最好开稳定的vpn.

PHP使用gRPC,需要依赖 2 个扩展,一个是grpc扩展,一个是protobuf扩展,编译安装方式如下(假设php安装在/opt/php7目录下):

wget -c "http://pecl.php.net/get/grpc-1.20.0RC3.tgz"
tar xvf grpc-1.20.0RC3.tgz
cd grpc-1.20.0RC3
/opt/php7/bin/phpize
./configure --with-php-config=/opt/php7/bin/php-config && make -j4 && make install

wget -c "http://pecl.php.net/get/protobuf-3.7.1.tgz"
tar xvf protobuf-3.7.1.tgz
cd protobuf-3.7.1
/opt/php7/bin/phpize
./configure --with-php-config=/opt/php7/bin/php-config && make -j4 && make install

然后在/opt/php7/etc/php.ini 中,添加上下面两个配置即可:
extension=grpc.so
extension=protobuf.so

配置好后,下面命令就可以看到两个扩展了
/opt/php7/bin/php -m |grep -E "grpc|protobuf"

使用proto文件,生成php使用的protobuf和stub文件,命令如下:

cd /data/lib
protoc --php_out=/data/lib   --grpc_out=/data/lib   --plugin=protoc-gen-grpc=/usr/bin/grpc_php_plugin   User.proto

则在 /data/lib就有我们需要的protobuf和stub php相关文件了。

为了使用方便,我们引入 composer 的autoload功能来管理所有php文件的加载

composer的安装命令如下:

wget https://dl.laravel-china.org/composer.phar -O /usr/local/bin/composer && chmod a+x /usr/local/bin/composer

composer.json 配置如下:

{
        "name" : "grpc-java/php",
        "require" : {
                "grpc/grpc" : "^v1.3.0",
                "google/protobuf" : "^v3.3.0"
        },
        "autoload" : {
                "classmap" : [
                        "lib/"
                ]
        }
}

放置此 composer.json 文件到 /data 目录, 执行下面的命令更新 autoload 文件列表

composer update

在 /data 目录,新建 main-autoload.php 文件, 内容如下:

<?php
require_once  __DIR__ . '/vendor/autoload.php';

use \Com\Stamhe\Springboot\User\Proto\UserClient;
use \Com\Stamhe\Springboot\User\Proto\UserRequest;

use \Com\Stamhe\Springboot\Common\Proto\CommonReply;

$start_time = microtime(true);

$obj = new UserClient('127.0.0.1:9090', [
    'credentials' => \Grpc\ChannelCredentials::createInsecure(),
    'timeout' => 1000,
]);


$req = new UserRequest();
$req->setUserId(10000);
$rsp = $obj->UserInfo($req)->wait();

$end_time = microtime(true);
printf("start_time = %s end_time = %s 【diff = %s】 ms\n", $start_time, $end_time, ($end_time - $start_time) * 1000.0);

list($rsp_data, $rsp_status) = $rsp;
var_dump($rsp_status);
var_dump($rsp_data->getCode());
var_dump($rsp_data->getMessage());
var_dump($rsp_data->getData());

启动 springboot 项目, 然后去 /data 目录执行 php main-autoload.php , 可得到如下结果:

start_time = 1555487705.4756 end_time = 1555487705.4959 【diff = 20.330905914307】 ms
object(stdClass)#12 (3) {
  ["metadata"]=>
  array(0) {
  }
  ["code"]=>
  int(0)
  ["details"]=>
  string(0) ""
}
int(0)
string(24) "Success From UserService"
string(69) "{"createTime":"2019-04-17 12:00:00","name":"Stam He","user_id":10000}"

说明:

1、rpc调用的请求成功与否,使用$rsp_status->code == 0 来确认

2、业务端的请求是否有问题,使用$rsp_data->getCode() 来确认

gRPC 的错误码说明文档:

https://github.com/grpc/grpc/blob/master/doc/statuscodes.md

完毕。

《SpringBoot2从入门到工程实战》第十三篇:平滑部署

我们都知道,Java服务重启的时候,服务需要kill掉,导致短暂的服务不可用,同时,服务的class加载也要一个启动过程,在服务启动后的开始阶段,因为class没有被jvm加载,导致开始的部分请求响应速度较慢。

平滑部署的目的,是为了在服务重启的时候,可以不损失任何的用户请求、也不降低服务质量。

目前,对于服务级别,实现平滑部署的方式主要有两种方式,一种是以Nginx、PHP-FPM这种为主的,他们是通过master-worker模式工作,重启主要是通过保留旧的worker、瞬时重启master来达到这种目的;还有一种就是目前的各种微服务所使用的,借助第三方的服务发现服务,如Zookeeper、ETCD等,将需要操作的机器或者服务,从服务注册发现平台里面暂时移除即可。

很简单,就不再举具体的示例了。

《SpringBoot2从入门到工程实战》第十二篇:模板引擎-Thymeleaf

SpringBoot官方推荐的模板引擎为Thymeleaf,所以我们在这个系列中只介绍Thymeleaf模板引擎。

本章示例工程名称:springboot_worker_thymeleaf

代码地址:https://github.com/stamhe/SpringBoot-Work-Example

目录结构如下:

pom.xml的内容:

<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.stamhe</groupId>
  <artifactId>springboot_worker_thymeleaf</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>springboot_worker_thymeleaf</name>
  <url>http://maven.apache.org</url>


	<properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
    </properties>
  
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.7.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    
      <dependencies>
        <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-web</artifactId>
        </dependency>
        
        <dependency>
		    <groupId>org.springframework.boot</groupId>
		    <artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>
    
        <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <scope>test</scope>
        </dependency>
    </dependencies>
    <!-- 
    使用Spring Boot微服务搭建框架,在eclipse和Idea下能正常运行,但是在打成jar包部署或者直接使用java -jar命令的时候,
    提示了xxxxxx.jar中没有主清单属性.
    添加 spring-boot-maven-plugin然后再执行mvn install 或者 mvn clean package 即可解决.
    -->
    <build>
      <plugins>
          <plugin>
              <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-maven-plugin</artifactId>
          </plugin>
      </plugins>
     </build>
</project>

App.java的内容:

package com.stamhe.springboot;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class App 
{
    public static void main( String[] args )
    {
    	SpringApplication.run(App.class, args);
    }
}

UserModel.java的内容:

package com.stamhe.springboot.model;

public class UserModel {
	private Long id;
	private String name;
	
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
}

HelloController.java的内容:

package com.stamhe.springboot.controller;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.stamhe.springboot.model.UserModel;

@Controller
@RequestMapping("/hello")
public class HelloController {

	// http://localhost:8080/hello/world.html
	@RequestMapping("/world.html")
	public String worldAction(HttpServletRequest request)
	{
		UserModel u1 = new UserModel();
		u1.setId(1L);
		u1.setName("u1");
		

		UserModel u2 = new UserModel();
		u2.setId(2L);
		u2.setName("u2");
		
		List<UserModel> list = new ArrayList<UserModel>();
		list.add(u1);
		list.add(u2);
		
		request.setAttribute("listUser", list);
		
		// 返回的 world 默认映射到 src/main/resources/templates/world.html
		return "world";
	}
	
	@RequestMapping("/world2.html")
	public ModelAndView world2Action()
	{
		UserModel u1 = new UserModel();
		u1.setId(1L);
		u1.setName("u1");
		
		UserModel u2 = new UserModel();
		u2.setId(2L);
		u2.setName("u2");
		
		List<UserModel> list = new ArrayList<UserModel>();
		list.add(u1);
		list.add(u2);
		
		Map<String, String> map = new HashMap<String, String>();
		map.put("HE", "QUAN");
		map.put("STAM", "HE");
		
		ModelAndView view = new ModelAndView();
        // 设置跳转的视图 默认映射到 src/main/resources/templates/world2.html
        view.setViewName("world2");
        // 设置属性
        view.addObject("listUser", list);
        view.addObject("mapUser", map);
        
        return view;
	}
}

world.html的内容:

<!DOCTYPE HTML>
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta content="text/html;charset=UTF-8"/>
</head>
<body>
<h6>Thymeleaf 模板引擎</h6>
<table border="1" bgcolor="#f0ffff">
    <thead>
    <tr>
        <th>用户ID</th>
        <th>用户名</th>
    </tr>
    </thead>
    <tbody th:each="user : ${listUser}">
    <tr>
        <td th:text="${user.id}"></td>
        <td th:text="${user.name}"></td>
    </tr>
    </tbody>
</table>
</body>
</html>

world2.html的内容:

<!DOCTYPE HTML>
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta content="text/html;charset=UTF-8"/>
</head>
<body>
<h6>Thymeleaf 模板引擎</h6>
<table border="1" bgcolor="#f0ffff">
	<thead>
    <tr>
        <th>用户ID</th>
        <th>用户名</th>
    </tr>
    </thead>
    <tbody th:each="user : ${listUser}">
    <tr>
        <td th:text="${user.id}"></td>
        <td th:text="${user.name}"></td>
    </tr>
    </tbody>
    
    
    <tbody th:each="user : ${mapUser}">
    <tr>
        <td th:text="${user.key}"></td>
        <td th:text="${user.value}"></td>
    </tr>
    </tbody>
</table>
</body>
</html>

application.properties的内容:

# prefix:指定模板所在的目录
spring.thymeleaf.prefix = classpath:/templates/
# check-tempate-location: 检查模板路径是否存在
spring.thymeleaf.check-template-location  = true
# cache: 是否缓存,开发模式下设置为false,避免改了模板还要重启服务器,线上设置为true,可以提高性能。
spring.thymeleaf.cache = false
# 模板文件的后缀
spring.thymeleaf.suffix = .html
spring.thymeleaf.encoding = UTF-8
spring.thymeleaf.content-type = text/html
spring.thymeleaf.mode = HTML5

访问下面的连接就可以看到效果了

http://localhost:8080/hello/world.html

http://localhost:8080/hello/world2.html

《SpringBoot2从入门到工程实战》第十一篇:静态资源访问

在实际开发项目的过程中,我们往往有对logo、css、js、图片等资源有直接访问需求,这类资源的访问,多数是不需要做鉴权的,可以直接访问。

在SpringBoot2中,默认对/**的访问可以直接访问四个目录下的文件:

classpath:/public/
classpath:/resources/
classpath:/static/
classpath:/META-INFO/resouces/

所以多数情况下,我们只需要在src/main/resources目录下面新建这这几个文件夹之一,其实就满足需求了。

本示例在src/main/resources下面新增static目录来做示例。

本章示例工程名称:springboot_worker_static

代码地址:https://github.com/stamhe/SpringBoot-Work-Example

目录结构如下:

pom.xml的内容:

<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.stamhe</groupId>
  <artifactId>springboot_worker_static</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>springboot_worker_static</name>
  <url>http://maven.apache.org</url>


	<properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
    </properties>
  
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.7.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    
      <dependencies>
        <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-web</artifactId>
        </dependency>
    
        <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <scope>test</scope>
        </dependency>
    </dependencies>
    <!-- 
    使用Spring Boot微服务搭建框架,在eclipse和Idea下能正常运行,但是在打成jar包部署或者直接使用java -jar命令的时候,
    提示了xxxxxx.jar中没有主清单属性.
    添加 spring-boot-maven-plugin然后再执行mvn install 或者 mvn clean package 即可解决.
    -->
    <build>
      <plugins>
          <plugin>
              <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-maven-plugin</artifactId>
          </plugin>
      </plugins>
     </build>
</project>

App.java的内容:

package com.stamhe.springboot;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class App 
{
    public static void main( String[] args )
    {
    	SpringApplication.run(App.class, args);
    }
}

在src/main/resources下面新增static目录,防止名称为 4.jpg 的图片,如下图

访问 http://localhost:8080/4.jpg 即可。

《SpringBoot2从入门到工程实战》第十篇:拦截器及过滤器

在SpringMVC中,拦截器及过滤器的使用非常广泛,例如做登录Auth验证、日志记录上报、性能分析等诸多场景,本章主要介绍SpringBoot2下,拦截器及过滤器的简单使用。

本章示例工程名称:springboot_worker_filter

代码地址:https://github.com/stamhe/SpringBoot-Work-Example

目录结构如下:

pom.xml的内容:

<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.stamhe</groupId>
  <artifactId>springboot_worker_filter</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>springboot_worker_filter</name>
  <url>http://maven.apache.org</url>


	<properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
    </properties>
  
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.7.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    
      <dependencies>
        <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-web</artifactId>
        </dependency>
    
        <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <scope>test</scope>
        </dependency>
    </dependencies>
    <!-- 
    使用Spring Boot微服务搭建框架,在eclipse和Idea下能正常运行,但是在打成jar包部署或者直接使用java -jar命令的时候,
    提示了xxxxxx.jar中没有主清单属性.
    添加 spring-boot-maven-plugin然后再执行mvn install 或者 mvn clean package 即可解决.
    -->
    <build>
      <plugins>
          <plugin>
              <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-maven-plugin</artifactId>
          </plugin>
      </plugins>
     </build>
</project>

App.java的内容:

package com.stamhe.springboot;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class App 
{
    public static void main( String[] args )
    {
    	SpringApplication.run(App.class, args);
    }
}

HelloController.java的内容:

package com.stamhe.springboot.controller;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController()
@RequestMapping("/hello")
public class HelloController {
	
	@RequestMapping("/world")
	public String worldAction()
	{
		return "Hello World";
	}
}

WebFilter的内容:

package com.stamhe.springboot.filter;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;

import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class WebFilter {
	
	@Bean
	public FilterRegistrationBean<MyFilter> myfilterRegisterBean()
	{
		FilterRegistrationBean<MyFilter> filter = new FilterRegistrationBean<>();
		filter.setFilter(new MyFilter());
		filter.addUrlPatterns("/*");
		filter.addInitParameter("paramName", "paramValue");
		filter.setName("MyFilter");
		filter.setOrder(1);
		
		return filter;
	}

	public class MyFilter implements Filter
	{
		@Override
		public void doFilter(ServletRequest req, ServletResponse rsp, FilterChain chain)
				throws IOException, ServletException {
			long start = System.currentTimeMillis();
			
			HttpServletRequest hsr = (HttpServletRequest)req;
			
			chain.doFilter(req, rsp);
			
			System.out.println("This is my filter. url = " + hsr.getRequestURI() + " cost = " + (System.currentTimeMillis() - start));
		}
	}
}

访问 http://localhost:8080/hello/world 得到如下内容:

《SpringBoot2从入门到工程实战》第九篇:集成Sharding-Sphere的自定义分库分表及自动读写分离

在实际业务中,针对大规模数据业务的MySQL使用,一般有两种模式,一种是集群模式,使用Proxy来实现,一种是使用主从模式。

本章主要是使用主从模式示例,支持自动读写分离以及自定义模式的分库分表规则(取模、MD5、年月日或者其他规则都行)。

本章示例工程名称:springboot_worker_multi_masterslave_hash

代码地址:https://github.com/stamhe/SpringBoot-Work-Example

目录结构如下:

示例MySQL建表语句:

create database user_201908;
use user_201908;

CREATE TABLE `users_201908_009` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(70) NOT NULL,
  `email` varchar(70) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


create database article_201908;
use article_201908;

CREATE TABLE `t_article_201908_009` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `summary` varchar(1024) NOT NULL DEFAULT '',
  `status` int(11) NOT NULL DEFAULT '0',
  `type` int(11) NOT NULL,
  `user_id` bigint(20) NOT NULL DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `public_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

pom.xml内容:

<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.stamhe</groupId>
  <artifactId>springboot_worker_multi_masterslave_hash</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>springboot_worker_multi_masterslave_hash</name>
  <url>http://maven.apache.org</url>



	<properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
    </properties>
  
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.7.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    
      <dependencies>
        <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-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.15</version>
	    </dependency> 
	    
	    <dependency>
		    <groupId>com.alibaba</groupId>
		    <artifactId>druid-spring-boot-starter</artifactId>
		    <version>1.1.13</version>
	    </dependency>
	    
	    <dependency>
	      <groupId>org.mybatis.spring.boot</groupId>
	      <artifactId>mybatis-spring-boot-starter</artifactId>
	      <version>1.3.3</version>
	    </dependency>
	    
	    
	    <!-- 
	    https://github.com/apache/incubator-shardingsphere/issues/1722
	    需要使用sharding-jdbc 3.1.0.1以上版本,否则limit offset会出现@1722 的bug
	    -->
	    <dependency>
		    <groupId>io.shardingsphere</groupId>
		    <artifactId>sharding-core</artifactId>
		    <version>3.1.0</version>
		</dependency>
	    
	    <dependency>
		    <groupId>io.shardingsphere</groupId>
		    <artifactId>sharding-jdbc-core</artifactId>
		    <version>3.1.0</version>
		</dependency>
    
        <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <scope>test</scope>
        </dependency>
    </dependencies>
    <!-- 
    使用Spring Boot微服务搭建框架,在eclipse和Idea下能正常运行,但是在打成jar包部署或者直接使用java -jar命令的时候,
    提示了xxxxxx.jar中没有主清单属性.
    添加 spring-boot-maven-plugin然后再执行mvn install 或者 mvn clean package 即可解决.
    -->
    <build>
      <plugins>
          <plugin>
              <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-maven-plugin</artifactId>
          </plugin>
      </plugins>
     </build>
</project>

application.properties内容:

spring.datasource.user-master.url = jdbc:mysql://127.0.0.1:3306/user_201908?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.user-master.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.user-master.username = root
spring.datasource.user-master.password = 
spring.datasource.user-master.type = com.alibaba.druid.pool.DruidDataSource

spring.datasource.user-slave01.url = jdbc:mysql://127.0.0.1:3306/user_201908?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.user-slave01.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.user-slave01.username = root
spring.datasource.user-slave01.password = 
spring.datasource.user-slave01.type = com.alibaba.druid.pool.DruidDataSource

spring.datasource.user-slave02.url = jdbc:mysql://127.0.0.1:3306/user_201908?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.user-slave02.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.user-slave02.username = root
spring.datasource.user-slave02.password = 
spring.datasource.user-slave02.type = com.alibaba.druid.pool.DruidDataSource



spring.datasource.article-master.url = jdbc:mysql://localhost:3306/article_201908?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.article-master.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.article-master.username = root
spring.datasource.article-master.password = 
spring.datasource.article-master.type = com.alibaba.druid.pool.DruidDataSource

spring.datasource.article-slave01.url = jdbc:mysql://localhost:3306/article_201908?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.article-slave01.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.article-slave01.username = root
spring.datasource.article-slave01.password = 
spring.datasource.article-slave01.type = com.alibaba.druid.pool.DruidDataSource

spring.datasource.article-slave02.url = jdbc:mysql://localhost:3306/article_201908?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.article-slave02.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.article-slave02.username = root
spring.datasource.article-slave02.password = 
spring.datasource.article-slave02.type = com.alibaba.druid.pool.DruidDataSource



mybatis.type-aliases-package = com.stamhe.springboot.model

App.java的内容:

package com.stamhe.springboot;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;


@SpringBootApplication
@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class, DruidDataSourceAutoConfigure.class})
@EnableTransactionManagement(proxyTargetClass = true)   //开启事务管理功能
@ComponentScan({"com.stamhe.springboot"})
public class App 
{
    public static void main( String[] args )
    {
    	SpringApplication.run(App.class, args);
    }
}

UserModel.java的内容:

package com.stamhe.springboot.model;


/*
create database user_201908;
use user_201908;

CREATE TABLE `users_201908_009` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(70) NOT NULL,
  `email` varchar(70) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 */
public class UserModel {
	private Long user_id;
    private String name;
    private String email;
    
    
	public Long getUser_id() {
		return user_id;
	}
	public void setUser_id(Long user_id) {
		this.user_id = user_id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	
	@Override
	public String toString() {
		return "UserModel [user_id=" + user_id + ", name=" + name + ", email=" + email + "]";
	}
}

ArticleModel.java的内容:

package com.stamhe.springboot.model;

import java.util.Date;

/*
create database article_201908;
use article_201908;

CREATE TABLE `t_article_201908_009` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `summary` varchar(1024) NOT NULL DEFAULT '',
  `status` int(11) NOT NULL DEFAULT '0',
  `type` int(11) NOT NULL,
  `user_id` bigint(20) NOT NULL DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `public_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 */
public class ArticleModel {
	private Long id;
    private String title;
    private String summary;
    private Integer status;
    private Integer type;
    private Long userId;
    
    private Date createTime;
    private Date publicTime;
    private Date updateTime;
    
    
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getSummary() {
		return summary;
	}
	public void setSummary(String summary) {
		this.summary = summary;
	}
	public Integer getStatus() {
		return status;
	}
	public void setStatus(Integer status) {
		this.status = status;
	}
	public Integer getType() {
		return type;
	}
	public void setType(Integer type) {
		this.type = type;
	}
	public Long getUserId() {
		return userId;
	}
	public void setUserId(Long userId) {
		this.userId = userId;
	}
	public Date getCreateTime() {
		return createTime;
	}
	public void setCreateTime(Date createTime) {
		this.createTime = createTime;
	}
	public Date getPublicTime() {
		return publicTime;
	}
	public void setPublicTime(Date publicTime) {
		this.publicTime = publicTime;
	}
	public Date getUpdateTime() {
		return updateTime;
	}
	public void setUpdateTime(Date updateTime) {
		this.updateTime = updateTime;
	}
	
	@Override
	public String toString() {
		return "ArticleModel [id=" + id + ", title=" + title + ", summary=" + summary + ", status=" + status + ", type="
				+ type + ", userId=" + userId + ", createTime=" + createTime + ", publicTime=" + publicTime
				+ ", updateTime=" + updateTime + "]";
	}
}

以下是mapper的内容,注意,涉及到分库分表时,在mapper中使用的,都是虚拟表名,这个虚拟表名需要在DatabaseMSShardingAlgorithm.java和TableMSShardingAlgorithm.java中使用。

UserMapper.java的内容:

package com.stamhe.springboot.mapper.user;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectKey;
import org.springframework.context.annotation.Configuration;

import com.stamhe.springboot.model.UserModel;


@Configuration
public interface UserMapper 
{
	@Select("select * from t_user_x where user_id=#{user_id}")
	@Results({
		@Result(property="user_id", column="user_id"),
		@Result(property="name", column="name"),
		@Result(property="email", column="email"),
	})
	public UserModel getOne(Long user_id);
	

	@Select("select * from t_user_x")
	@Results({
		@Result(property="user_id", column="user_id"),
		@Result(property="name", column="name"),
		@Result(property="email", column="email"),
	})
	public List<UserModel> getAll();
	

	@Insert("insert into t_user_x (user_id, name, email) "
			+ "values (#{user_id}, #{name}, #{email})")
	@SelectKey(statement="select LAST_INSERT_ID()", keyProperty="user_id", before=false, resultType=long.class)
	public void insert(UserModel userModel);	
}

ArticleMapper.java的内容:

package com.stamhe.springboot.mapper.article;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectKey;
import org.apache.ibatis.annotations.Update;
import org.springframework.context.annotation.Configuration;

import com.stamhe.springboot.model.ArticleModel;

@Configuration
public interface ArticleMapper 
{
	@Select("select * from t_article_x where user_id=#{user_id} and id=#{id}")
	@Results({
		@Result(property="userId", column="user_id"),
		@Result(property="createTime", column="create_time"),
		@Result(property="updateTime", column="update_time"),
		@Result(property="publicTime", column="public_time"),
	})
	public ArticleModel getOne(@Param("user_id")Long user_id, @Param("id")Long id);
	

	@Select("select * from t_article_x where user_id = #{user_id} or user_id != #{user_id}")
	@Results({
		@Result(property="userId", column="user_id"),
		@Result(property="createTime", column="create_time"),
		@Result(property="updateTime", column="update_time"),
		@Result(property="publicTime", column="public_time"),
	})
	public List<ArticleModel> getAll(Long user_id);
	
	// articleModel.getId()
	@Insert("insert into t_article_x (id, title, summary, status, type, user_id, create_time, update_time, public_time) "
			+ "values (#{id}, #{title}, #{summary}, #{status}, #{type}, #{userId}, #{createTime}, #{updateTime}, #{publicTime})")
	@SelectKey(statement="select LAST_INSERT_ID()", keyProperty="id", before=false, resultType=long.class)
	public void insert(ArticleModel articleModel);
	
	// 返回受影响的行数
	@Update("update t_article_x set summary=#{summary} where user_id = #{user_id} and id=#{id}")
	public Integer update(@Param("user_id")Long user_id, @Param("articleModel")ArticleModel articleModel);
	
	// 返回受影响的行数
	@Delete("delete from t_article_x where user_id = #{user_id} and id=#{id}")
	public Integer delete(@Param("user_id")Long user_id, @Param("id")Long id);
}

UserController.java的内容:

package com.stamhe.springboot.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.stamhe.springboot.mapper.user.UserMapper;
import com.stamhe.springboot.model.UserModel;

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

	@Autowired
	private UserMapper userMapper;
	
	// http://localhost:8080/user/add
	@RequestMapping("/add")
	public Long addAction()
	{
		Long user_id = 1L;

		UserModel userModel = new UserModel();
		userModel.setUser_id(user_id);
		userModel.setName("user-测试-201908-001");
		userModel.setEmail("stamhe-201908-001@gmail.com");
		
		userMapper.insert(userModel);
		
		return user_id;
	}
	
	// http://localhost:8080/user/detail/1
	@RequestMapping(value="/detail/{user_id}")
	public UserModel detailAction(@PathVariable("user_id")Long user_id)
	{
		UserModel userModel = userMapper.getOne(user_id);
		return userModel;
	}
}

ArticleController.java的内容:

package com.stamhe.springboot.controller;

import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.stamhe.springboot.mapper.article.ArticleMapper;
import com.stamhe.springboot.model.ArticleModel;

@RestController
@RequestMapping("/article")
public class ArticleController {

	@Autowired
	private ArticleMapper articleMapper;
	
	// http://localhost:8080/article/add
	@RequestMapping("/add")
	public Long addAction()
	{
		Long id = 1L;

		ArticleModel articleModel = new ArticleModel();
		
		articleModel.setId(id);
		articleModel.setTitle("article-测试-201908-001");
		articleModel.setSummary("article-测试-201908-001");
		articleModel.setStatus(1);
		articleModel.setType(1);
		articleModel.setUserId(1L);
		articleModel.setCreateTime(new Date());
		articleModel.setUpdateTime(new Date());
		articleModel.setPublicTime(new Date());
        articleMapper.insert(articleModel);
        
		return id;
	}

	// http://localhost:8080/article/all/1
	@RequestMapping(value = "/all/{user_id}")
	public List<ArticleModel> allAction(@PathVariable("user_id")Long user_id)
	{
		List<ArticleModel> list = articleMapper.getAll(user_id);
		return list;
	}
	
	// http://localhost:8080/article/detail/1/1
	@RequestMapping(value="/detail/{user_id}/{id}")
	public ArticleModel detailAction(@PathVariable("user_id") Long user_id, @PathVariable("id")Long id)
	{
		ArticleModel articleModel = articleMapper.getOne(user_id, id);
		return articleModel;
	}
}

下面是Sharding-Sphere使用的关键模块:

下面主要是根据 虚拟表名 shardingValue.getLogicTableName() 及 分库分表字段 shardingValue.getValue() 来计算当次请求需要使用的datasource,在 MSDSConfig.java中定义,并返回这个datasource。

DatabaseMSShardingAlgorithm.java的内容:

package com.stamhe.springboot.algorithm;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

/*
 * 
 * 分库分表: hash、取模、按时间
 * https://blog.csdn.net/myshy025tiankong/article/details/83063887
 * 
 * https://www.cnblogs.com/mr-yang-localhost/p/8313360.html
 * StandardShardingStrategy 标准分片策略。提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键,
 * 提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法
 * PreciseShardingAlgorithm 用于处理=和IN的分片
 * RangeShardingAlgorithm 用于处理BETWEEN AND分片,如果不配置RangeShardingAlgorithm,SQL中的BETWEEN AND将按照全库路由处理
 * 
 * 
 * ComplexShardingStrategy 复合分片策略。提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。
 * ComplexShardingStrategy支持多分片键,由于多分片键之间的关系复杂,因此Sharding-JDBC并未做过多的封装,
 * 而是直接将分片键值组合以及分片操作符交于算法接口,完全由应用开发者实现,提供最大的灵活度。
 * 
 * 
 * InlineShardingStrategy Inline表达式分片策略。使用Groovy的Inline表达式,提供对SQL语句中的=和IN的分片操作支持。
 * InlineShardingStrategy只支持单分片键,对于简单的分片算法,可以通过简单的配置使用,从而避免繁琐的Java代码开发,
 * 如: tuser${user_id % 8} 表示t_user表按照user_id按8取模分成8个表,表名称为t_user_0到t_user_7。
 * 
 * 
 * HintShardingStrategy 通过Hint而非SQL解析的方式分片的策略。
 * 
 * 
 * NoneShardingStrategy 不分片的策略。
 */

public class DatabaseMSShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

	@Override
	public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
		String logic_table = shardingValue.getLogicTableName();
//		System.out.println("logic_table = " + logic_table);
		
		String ds_name = "ds_hash_";
		String subfix  = "";
		
		switch(logic_table)
		{
			case "t_user_x":
				return "ds_hash_user";
			case "t_article_x":
				return "ds_hash_article";
			default:
				break;
		}
		
		try {
			Date date = (Date) new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(shardingValue.getValue().toString());

			String year	= String.format("%tY", date);
			String mon	= String.format("%tm", date);
			subfix		= year + mon;
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// ds_name 格式【ds_db_201810】
		ds_name = ds_name + subfix;
//		System.out.println("ds_name = " + ds_name + " new");
		return ds_name;
	}

}

下面主要是根据 虚拟表名 shardingValue.getLogicTableName() 及 分库分表字段 shardingValue.getValue() 来计算当次请求需要使用的真实表名,并返回这个真实的表名。

TableMSShardingAlgorithm.java的内容:

package com.stamhe.springboot.algorithm;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;


/*
 * 
 * 分库分表: hash、取模、按时间
 * https://blog.csdn.net/myshy025tiankong/article/details/83063887
 * 
 * https://www.cnblogs.com/mr-yang-localhost/p/8313360.html
 * StandardShardingStrategy 标准分片策略。提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键,
 * 提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法
 * PreciseShardingAlgorithm 用于处理=和IN的分片
 * RangeShardingAlgorithm 用于处理BETWEEN AND分片,如果不配置RangeShardingAlgorithm,SQL中的BETWEEN AND将按照全库路由处理
 * 
 * 
 * ComplexShardingStrategy 复合分片策略。提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。
 * ComplexShardingStrategy支持多分片键,由于多分片键之间的关系复杂,因此Sharding-JDBC并未做过多的封装,
 * 而是直接将分片键值组合以及分片操作符交于算法接口,完全由应用开发者实现,提供最大的灵活度。
 * 
 * 
 * InlineShardingStrategy Inline表达式分片策略。使用Groovy的Inline表达式,提供对SQL语句中的=和IN的分片操作支持。
 * InlineShardingStrategy只支持单分片键,对于简单的分片算法,可以通过简单的配置使用,从而避免繁琐的Java代码开发,
 * 如: tuser${user_id % 8} 表示t_user表按照user_id按8取模分成8个表,表名称为t_user_0到t_user_7。
 * 
 * 
 * HintShardingStrategy 通过Hint而非SQL解析的方式分片的策略。
 * 
 * 
 * NoneShardingStrategy 不分片的策略。
 */
public class TableMSShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

	@Override
	public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
		String logic_table = shardingValue.getLogicTableName();
		
		String tb_name = "";
		
		switch(logic_table) {
			case "t_user_x":
				tb_name = "users_201908_009";
				return tb_name;
			case "t_article_x":
				tb_name = "t_article_201908_009";
				return tb_name;
			default:
				tb_name = "xxx_";
		}
		
		try {
			Date date = (Date) new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(shardingValue.getValue().toString());

			String year = String.format("%tY", date);
			String mon = String.format("%tm", date);
			String dat = String.format("%td", date);
			// 表名【gps_20181011】
			tb_name = tb_name + year + mon + dat;
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

//		System.out.println("tb_name = " + tb_name);
		return tb_name;
	}

}

MSDSConfig.java的内容:

package com.stamhe.springboot.dsconfig;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.stamhe.springboot.algorithm.DatabaseMSShardingAlgorithm;
import com.stamhe.springboot.algorithm.TableMSShardingAlgorithm;

import io.shardingsphere.api.algorithm.masterslave.RoundRobinMasterSlaveLoadBalanceAlgorithm;
import io.shardingsphere.api.config.rule.MasterSlaveRuleConfiguration;
import io.shardingsphere.api.config.rule.ShardingRuleConfiguration;
import io.shardingsphere.api.config.rule.TableRuleConfiguration;
import io.shardingsphere.api.config.strategy.StandardShardingStrategyConfiguration;
import io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;

/*
 * 由于我们在SpringBootApplication中禁掉了自动数据源配置,需要在这儿手动创建 application.properteis 中的数据源
 * 
 * 分库分表: hash、取模、按时间
 * https://blog.csdn.net/myshy025tiankong/article/details/83063887
 * 
 * 主从读写分离
 */
@Configuration
@MapperScan(basePackages = {"com.stamhe.springboot.mapper"}, sqlSessionFactoryRef = "sqlSessionFactoryUserMSHash")
public class MSDSConfig {
    List<MasterSlaveRuleConfiguration> getMasterSlaveRuleConfigurations() {
  		MasterSlaveRuleConfiguration msUserRuleConfig = new MasterSlaveRuleConfiguration("ds_hash_user", "ds_user_master", 
  				Arrays.asList("ds_user_slave01", "ds_user_slave02"), new RoundRobinMasterSlaveLoadBalanceAlgorithm());

  		MasterSlaveRuleConfiguration msArticleRuleConfig = new MasterSlaveRuleConfiguration("ds_hash_article", "ds_article_master", 
  				Arrays.asList("ds_article_slave01", "ds_article_slave02"), new RoundRobinMasterSlaveLoadBalanceAlgorithm());

  		List<MasterSlaveRuleConfiguration> list = new ArrayList<>();
  		list.add(msUserRuleConfig);
  		list.add(msArticleRuleConfig);
  		return list;
    }
    
    
    Map<String, DataSource> createDataSourceMap() {
        final Map<String, DataSource> result = new HashMap<>();
        
        result.put("ds_user_master", DBUserDataSourceMaster());
        result.put("ds_user_slave01", DBUserDataSourceSlave01());
        result.put("ds_user_slave02", DBUserDataSourceSlave02());
        
        result.put("ds_article_master", DBArticleDataSourceMaster());
        result.put("ds_article_slave01", DBArticleDataSourceSlave01());
        result.put("ds_article_slave02", DBArticleDataSourceSlave02());
        
        return result;
    }
    

  	@Bean(name="shardingDataSourceUserMSHash")
  	@Primary
  	public DataSource shardingDataSource() throws SQLException {
  		ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        
  		//用户表配置,可以添加多个配置
  		shardingRuleConfig.getTableRuleConfigs().add(getUserTableRuleConfiguration());
  		shardingRuleConfig.getTableRuleConfigs().add(getArticleTableRuleConfiguration());
  		
//  		shardingRuleConfig.getBindingTableGroups().add("gps_table, gps_20190227_x, gps_20190227_y");
		shardingRuleConfig.getBindingTableGroups().add("t_user_x, t_article_x");
        
  		//设置数据库策略,传入的是user_id
  		shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new DatabaseMSShardingAlgorithm()));
  		//设置数据表策略,传入的是user_id
  		shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new TableMSShardingAlgorithm()));

  		shardingRuleConfig.setMasterSlaveRuleConfigs(getMasterSlaveRuleConfigurations());
  		
  		Properties props = new Properties();
  		// 是否显示sql 和 DataSource
  		props.setProperty("sql.show", "true");
  		
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, new ConcurrentHashMap<String, Object>(), props);
  	}
  	
  	
  	/**
     * 需要手动配置事务管理器
     *
     * @param shardingDataSource
     * @return
     */
    @Bean(name="transactitonManagerUserMSHash")
    public DataSourceTransactionManager transactitonManager(DataSource shardingDataSourceMSHash) {
        return new DataSourceTransactionManager(shardingDataSourceMSHash);
    }

    @Bean(name="sqlSessionFactoryUserMSHash")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("shardingDataSourceUserMSHash") DataSource shardingDataSourceMSHash) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(shardingDataSourceMSHash);
        return bean.getObject();
    }

    @Bean(name="testSqlSessionTemplateUserMSHash")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("sqlSessionFactoryUserMSHash") SqlSessionFactory sqlSessionFactoryObjGpsMSHash) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryObjGpsMSHash);
    }
    

    /**
     * 设置Druid参数
     * @param dataSource
     * @return
     * @throws SQLException 
     */
    public DruidDataSource setDataSource(DruidDataSource druidDataSource)
    {
        // 初始化大小,最小,最大
        druidDataSource.setMaxActive(10);
        druidDataSource.setInitialSize(4);
        druidDataSource.setMinIdle(3);
        
        // 配置获取连接等待超时的时间 单位: ms
        druidDataSource.setMaxWait(100);
        
        // 打开后,增强timeBetweenEvictionRunsMillis的周期性连接检查,minIdle内的空闲连接,每次检查强制验证连接有效性. 参考:https://github.com/alibaba/druid/wiki/KeepAlive_cn
        druidDataSource.setKeepAlive(true);
        // 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位: ms
        druidDataSource.setTimeBetweenEvictionRunsMillis(30000);
        // 配置一个连接在池中最小生存的时间,单位: ms
        druidDataSource.setMinEvictableIdleTimeMillis(300000);

        // 配置一个连接在池中最小生存的时间,单位: ms
        druidDataSource.setMinEvictableIdleTimeMillis(300000);
        
        // 用来检测连接是否有效的sql,要求是一个查询语句。 如果validationQuery为null,testOnBorrow、testOnReturn、 testWhileIdle都不会其作用。
        druidDataSource.setValidationQuery("select 'x'");
        
        // 建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于 timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
        druidDataSource.setTestWhileIdle(true);
        // 申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。
        druidDataSource.setTestOnBorrow(false);
        // 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能
        druidDataSource.setTestOnReturn(false);
        
        /* 打开PSCache,并且指定每个连接上PSCache的大小。
        如果用Oracle,则把poolPreparedStatements配置为true,
        mysql可以配置为false。分库分表较多的数据库,建议配置为false */
        druidDataSource.setPoolPreparedStatements(false);
        // 指定每个连接上PSCache的大小
        druidDataSource.setMaxOpenPreparedStatements(20);
        // 合并多个DruidDataSource的监控数据
        //druidDataSource.setUseGlobalDataSourceStat(true);
        
        // 自动提交设置为 true
        druidDataSource.setDefaultAutoCommit(true);
        
        /*
         * 连接泄露检查,打开removeAbandoned功能 , 连接从连接池借出后,长时间不归还,将触发强制回连接。回收周期随timeBetweenEvictionRunsMillis进行.
         * 如果连接为从连接池借出状态,并且未执行任何sql,并且从借出时间起已超过removeAbandonedTimeout时间,则强制归还连接到连接池中。
         */
        druidDataSource.setRemoveAbandoned(true);
        // 单位: s
        druidDataSource.setRemoveAbandonedTimeout(5);
        // 关闭abanded连接时输出错误日志,这样出现连接泄露时可以通过错误日志定位忘记关闭连接的位置
        druidDataSource.setLogAbandoned(true);
        
        //配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
        /*
        属性类型是字符串,通过别名的方式配置扩展插件, 
		常用的插件有: 
		监控统计用的filter:stat  
		日志用的filter:log4j 
		防御sql注入的filter:wall
         */
        try {
        	druidDataSource.setFilters("stat,wall,slf4j");
        }catch(Exception e) {
        	e.printStackTrace();
        }
        // 配置监控统计日志的输出间隔,每次输出所有统计数据会重置,酌情开启. 单位: ms
        druidDataSource.setTimeBetweenLogStatsMillis(10000);
            
    	return druidDataSource;
    }
    

  	@Bean
  	public TableRuleConfiguration getUserTableRuleConfiguration() {
  		TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration();
   
  		tableRuleConfig.setLogicTable("t_user_x");
  		// 设置使用sharding-jdbc产生id的列名,如果不用sharding-jdbc产生,则不要设置任何值,否则insert报错
//		tableRuleConfig.setKeyGeneratorColumnName("id");
  		return tableRuleConfig;
  	}

  	@Bean
  	public TableRuleConfiguration getArticleTableRuleConfiguration() {
  		TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration();
   
  		tableRuleConfig.setLogicTable("t_article_x");
  		// 设置使用sharding-jdbc产生id的列名,如果不用sharding-jdbc产生,则不要设置任何值,否则insert报错
//		tableRuleConfig.setKeyGeneratorColumnName("id");
  		return tableRuleConfig;
  	}

	@Bean(name = "db_user_master")
    @ConfigurationProperties(prefix="spring.datasource.user-master")
    public DataSource DBUserDataSourceMaster() {
    	DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
    	return setDataSource(dataSource);
    }
    
    @Bean(name = "db_user_slave01")
    @ConfigurationProperties(prefix="spring.datasource.user-slave01")
    public DataSource DBUserDataSourceSlave01() {
    	DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
    	return setDataSource(dataSource);
    }
    
    @Bean(name = "db_user_slave02")
    @ConfigurationProperties(prefix="spring.datasource.user-slave02")
    public DataSource DBUserDataSourceSlave02() {
    	DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
    	return setDataSource(dataSource);
    }
    

	@Bean(name = "db_article_master")
    @ConfigurationProperties(prefix="spring.datasource.article-master")
    public DataSource DBArticleDataSourceMaster() {
    	DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
    	return setDataSource(dataSource);
    }
    
    @Bean(name = "db_article_slave01")
    @ConfigurationProperties(prefix="spring.datasource.article-slave01")
    public DataSource DBArticleDataSourceSlave01() {
    	DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
    	return setDataSource(dataSource);
    }
    
    @Bean(name = "db_article_slave02")
    @ConfigurationProperties(prefix="spring.datasource.article-slave02")
    public DataSource DBArticleDataSourceSlave02() {
    	DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
    	return setDataSource(dataSource);
    }
}

示例完。

《SpringBoot2从入门到工程实战》第七篇:集成Druid连接池

我们都知道,java的高性能,根本原因其实不是因为Java的jvm或者静态编译,而是因为Java的长链接,而长链接的性能的提升,主要是依赖连接池,所以针对MySQL这种数据库,在实际使用时,使用自动管理的MySQL连接池就可以大幅提升业务代码的响应速度,本章我们主要介绍针对MySQL的连接池Druid连接池的使用。

本章示例工程名称:springboot_worker_druid

代码地址:https://github.com/stamhe/SpringBoot-Work-Example

目录结构如下:



示例MySQL建表语句:

create database article_201908;
use article_201908;

CREATE TABLE `t_article_201908_007` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `summary` varchar(1024) NOT NULL DEFAULT '',
  `status` int(11) NOT NULL DEFAULT '0',
  `type` int(11) NOT NULL,
  `user_id` bigint(20) NOT NULL DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `public_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


create database user_201908;
use user_201908;

CREATE TABLE `users_201908_007` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(70) NOT NULL,
  `email` varchar(70) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

pom.xml内容:

<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.stamhe</groupId>
  <artifactId>springboot_worker_druid</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>springboot_worker_druid</name>
  <url>http://maven.apache.org</url>


	<properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
    </properties>
  
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.7.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    
      <dependencies>
        <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-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.15</version>
	    </dependency> 
	    
	    <dependency>
		    <groupId>com.alibaba</groupId>
		    <artifactId>druid-spring-boot-starter</artifactId>
		    <version>1.1.13</version>
	    </dependency>
	    
	    <dependency>
	      <groupId>org.mybatis.spring.boot</groupId>
	      <artifactId>mybatis-spring-boot-starter</artifactId>
	      <version>1.3.3</version>
	    </dependency>
    
        <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <scope>test</scope>
        </dependency>
    </dependencies>
    <!-- 
    使用Spring Boot微服务搭建框架,在eclipse和Idea下能正常运行,但是在打成jar包部署或者直接使用java -jar命令的时候,
    提示了xxxxxx.jar中没有主清单属性.
    添加 spring-boot-maven-plugin然后再执行mvn install 或者 mvn clean package 即可解决.
    -->
    <build>
      <plugins>
          <plugin>
              <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-maven-plugin</artifactId>
          </plugin>
      </plugins>
     </build>
</project>

App.java的内容:

package com.stamhe.springboot;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;

/*
首先要将SpringBoot自带的DataSourceAutoConfiguration禁掉,因为它会读取application.properties文件的spring.datasource.*属性并自动配置单数据源。
在@SpringBootApplication注解中添加exclude属性即可
*/
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@ComponentScan({"com.stamhe.springboot"})
@MapperScan("com.stamhe.springboot.mapper")
public class App 
{
    public static void main( String[] args )
    {
    	SpringApplication.run(App.class, args);
    }
}

UserModel.java的内容:

package com.stamhe.springboot.model;


/*

create database user_201908;
use user_201908;

CREATE TABLE `users_201908_007` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(70) NOT NULL,
  `email` varchar(70) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 */
public class UserModel {
	private Long id;
    private String name;
    private String email;
    
    
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	
	@Override
	public String toString() {
		return "UserModel [id=" + id + ", name=" + name + ", email=" + email + "]";
	}
}

ArticleModel.java的内容:

package com.stamhe.springboot.model;

import java.util.Date;

/*
create database article_201908;
use article_201908;

CREATE TABLE `t_article_201908_007` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `summary` varchar(1024) NOT NULL DEFAULT '',
  `status` int(11) NOT NULL DEFAULT '0',
  `type` int(11) NOT NULL,
  `user_id` bigint(20) NOT NULL DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `public_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 */
public class ArticleModel {
	private Long id;
    private String title;
    private String summary;
    private Integer status;
    private Integer type;
    private Long userId;
    
    private Date createTime;
    private Date publicTime;
    private Date updateTime;
    
    
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getSummary() {
		return summary;
	}
	public void setSummary(String summary) {
		this.summary = summary;
	}
	public Integer getStatus() {
		return status;
	}
	public void setStatus(Integer status) {
		this.status = status;
	}
	public Integer getType() {
		return type;
	}
	public void setType(Integer type) {
		this.type = type;
	}
	public Long getUserId() {
		return userId;
	}
	public void setUserId(Long userId) {
		this.userId = userId;
	}
	public Date getCreateTime() {
		return createTime;
	}
	public void setCreateTime(Date createTime) {
		this.createTime = createTime;
	}
	public Date getPublicTime() {
		return publicTime;
	}
	public void setPublicTime(Date publicTime) {
		this.publicTime = publicTime;
	}
	public Date getUpdateTime() {
		return updateTime;
	}
	public void setUpdateTime(Date updateTime) {
		this.updateTime = updateTime;
	}
	
	@Override
	public String toString() {
		return "ArticleModel [id=" + id + ", title=" + title + ", summary=" + summary + ", status=" + status + ", type="
				+ type + ", userId=" + userId + ", createTime=" + createTime + ", publicTime=" + publicTime
				+ ", updateTime=" + updateTime + "]";
	}
}

UserMapper.java内容:

package com.stamhe.springboot.mapper.user;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectKey;
import org.springframework.context.annotation.Configuration;

import com.stamhe.springboot.model.UserModel;


@Configuration
public interface UserMapper 
{
	@Select("select * from users_201908_007 where id=#{id}")
	@Results({
		@Result(property="id", column="id"),
		@Result(property="name", column="name"),
		@Result(property="email", column="email"),
	})
	public UserModel getOne(Long id);
	

	@Select("select * from users_201908_007")
	@Results({
		@Result(property="id", column="id"),
		@Result(property="name", column="name"),
		@Result(property="email", column="email"),
	})
	public List<UserModel> getAll();
	

	// userModel.getId()
	@Insert("insert into users_201908_007 (name, email) "
			+ "values (#{name}, #{email})")
	@SelectKey(statement="select LAST_INSERT_ID()", keyProperty="id", before=false, resultType=long.class)
	public void insert(UserModel userModel);	
}

ArticleMapper.java内容:

package com.stamhe.springboot.mapper.article;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectKey;
import org.apache.ibatis.annotations.Update;
import org.springframework.context.annotation.Configuration;

import com.stamhe.springboot.model.ArticleModel;

@Configuration
public interface ArticleMapper 
{
	@Select("select * from t_article_201908_007 where id=#{id}")
	@Results({
		@Result(property="userId", column="user_id"),
		@Result(property="createTime", column="create_time"),
		@Result(property="updateTime", column="update_time"),
		@Result(property="publicTime", column="public_time"),
	})
	public ArticleModel getOne(Long id);
	

	@Select("select * from t_article_201908_007")
	@Results({
		@Result(property="userId", column="user_id"),
		@Result(property="createTime", column="create_time"),
		@Result(property="updateTime", column="update_time"),
		@Result(property="publicTime", column="public_time"),
	})
	public List<ArticleModel> getAll();
	
	// articleModel.getId()
	@Insert("insert into t_article_201908_007 (title, summary, status, type, user_id, create_time, update_time, public_time) "
			+ "values (#{title}, #{summary}, #{status}, #{type}, #{userId}, #{createTime}, #{updateTime}, #{publicTime})")
	@SelectKey(statement="select LAST_INSERT_ID()", keyProperty="id", before=false, resultType=long.class)
	public void insert(ArticleModel articleModel);
	
	// 返回受影响的行数
	@Update("update t_article_201908_007 set summary=#{summary} where id=#{id}")
	public Integer update(ArticleModel articleModel);
	
	// 返回受影响的行数
	@Delete("delete from t_article_201908_007 where id=#{id}")
	public Integer delete(@Param("id")Long id);
}

UserDSConfig.java内容:

package com.stamhe.springboot.dsconfig;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;

/*
 * 由于我们在SpringBootApplication中禁掉了自动数据源配置,需要在这儿手动创建 application.properteis 中的数据源
 */
@Configuration
@MapperScan(basePackages = {"com.stamhe.springboot.mapper.user"}, sqlSessionFactoryRef = "sqlSessionFactoryObj01")
public class UserDSConfig {
    @Bean(name = "ds_db01")
    @Primary
    @ConfigurationProperties(prefix="spring.datasource.user")
    public DataSource DB01DataSource() {
    	DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
    	return setDataSource(dataSource);
    }

    @Bean(name="sqlSessionFactoryObj01")
    @Primary
    public SqlSessionFactory sqlSessionFactory1(@Qualifier("ds_db01") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);

        return factoryBean.getObject();
    }

    @Bean(name="sqlSessionTemplateObj01")
    public SqlSessionTemplate sqlSessionTemplate1(@Qualifier("sqlSessionFactoryObj01") SqlSessionFactory sqlSessionFactory) throws Exception {
        SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory); // 使用上面配置的Factory
        return template;
    }
    
    /**
     * 设置Druid参数
     * @param dataSource
     * @return
     * @throws SQLException 
     */
    public DruidDataSource setDataSource(DruidDataSource druidDataSource)
    {
        // 初始化大小,最小,最大
        druidDataSource.setMaxActive(10);
        druidDataSource.setInitialSize(3);
        druidDataSource.setMinIdle(3);
        
        // 配置获取连接等待超时的时间 单位: ms
        druidDataSource.setMaxWait(100);
        
        // 打开后,增强timeBetweenEvictionRunsMillis的周期性连接检查,minIdle内的空闲连接,每次检查强制验证连接有效性. 参考:https://github.com/alibaba/druid/wiki/KeepAlive_cn
        druidDataSource.setKeepAlive(true);
        // 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位: ms
        druidDataSource.setTimeBetweenEvictionRunsMillis(30000);
        // 配置一个连接在池中最小生存的时间,单位: ms
        druidDataSource.setMinEvictableIdleTimeMillis(300000);

        // 配置一个连接在池中最小生存的时间,单位: ms
        druidDataSource.setMinEvictableIdleTimeMillis(300000);
        
        // 用来检测连接是否有效的sql,要求是一个查询语句。 如果validationQuery为null,testOnBorrow、testOnReturn、 testWhileIdle都不会其作用。
        druidDataSource.setValidationQuery("select 'x'");
        
        // 建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于 timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
        druidDataSource.setTestWhileIdle(true);
        // 申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。
        druidDataSource.setTestOnBorrow(false);
        // 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能
        druidDataSource.setTestOnReturn(false);
        
        /* 打开PSCache,并且指定每个连接上PSCache的大小。
        如果用Oracle,则把poolPreparedStatements配置为true,
        mysql可以配置为false。分库分表较多的数据库,建议配置为false */
        druidDataSource.setPoolPreparedStatements(false);
        // 指定每个连接上PSCache的大小
        druidDataSource.setMaxOpenPreparedStatements(20);
        // 合并多个DruidDataSource的监控数据
        //druidDataSource.setUseGlobalDataSourceStat(true);
        
        // 自动提交设置为 true
        druidDataSource.setDefaultAutoCommit(true);
        
        /*
         * 连接泄露检查,打开removeAbandoned功能 , 连接从连接池借出后,长时间不归还,将触发强制回连接。回收周期随timeBetweenEvictionRunsMillis进行.
         * 如果连接为从连接池借出状态,并且未执行任何sql,并且从借出时间起已超过removeAbandonedTimeout时间,则强制归还连接到连接池中。
         */
        druidDataSource.setRemoveAbandoned(true);
        // 单位: s
        druidDataSource.setRemoveAbandonedTimeout(5);
        // 关闭abanded连接时输出错误日志,这样出现连接泄露时可以通过错误日志定位忘记关闭连接的位置
        druidDataSource.setLogAbandoned(true);
        
        //配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
        /*
        属性类型是字符串,通过别名的方式配置扩展插件, 
		常用的插件有: 
		监控统计用的filter:stat  
		日志用的filter:log4j 
		防御sql注入的filter:wall
         */
        try {
        	druidDataSource.setFilters("stat,wall,slf4j");
        }catch(Exception e) {
        	e.printStackTrace();
        }
        // 配置监控统计日志的输出间隔,每次输出所有统计数据会重置,酌情开启. 单位: ms
        druidDataSource.setTimeBetweenLogStatsMillis(10000);
            
    	return druidDataSource;
    }
}

ArticleDSConfig.java内容:

package com.stamhe.springboot.dsconfig;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;

/*
 * 由于我们在SpringBootApplication中禁掉了自动数据源配置,需要在这儿手动创建 application.properteis 中的数据源
 */
@Configuration
@MapperScan(basePackages = {"com.stamhe.springboot.mapper.article"}, sqlSessionFactoryRef = "sqlSessionFactoryObj02")
public class ArticleDSConfig {
    @Bean(name="ds_db02")
    @ConfigurationProperties(prefix="spring.datasource.article")
    public DataSource DB02DataSource() {
    	DruidDataSource dataSource =  DruidDataSourceBuilder.create().build();
    	return setDataSource(dataSource);
    }

    @Bean(name="sqlSessionFactoryObj02")
    @Primary
    public SqlSessionFactory sqlSessionFactory1(@Qualifier("ds_db02") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);

        return factoryBean.getObject();
    }

    @Bean(name="sqlSessionTemplateObj02")
    public SqlSessionTemplate sqlSessionTemplate1(@Qualifier("sqlSessionFactoryObj02") SqlSessionFactory sqlSessionFactory) throws Exception {
        SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory); // 使用上面配置的Factory
        return template;
    }
    
    /**
     * 设置Druid参数
     * @param dataSource
     * @return
     * @throws SQLException 
     */
    public DruidDataSource setDataSource(DruidDataSource druidDataSource)
    {
        // 初始化大小,最小,最大
        druidDataSource.setMaxActive(10);
        druidDataSource.setInitialSize(3);
        druidDataSource.setMinIdle(3);
        
        // 配置获取连接等待超时的时间 单位: ms
        druidDataSource.setMaxWait(100);
        
        // 打开后,增强timeBetweenEvictionRunsMillis的周期性连接检查,minIdle内的空闲连接,每次检查强制验证连接有效性. 参考:https://github.com/alibaba/druid/wiki/KeepAlive_cn
        druidDataSource.setKeepAlive(true);
        // 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位: ms
        druidDataSource.setTimeBetweenEvictionRunsMillis(30000);
        // 配置一个连接在池中最小生存的时间,单位: ms
        druidDataSource.setMinEvictableIdleTimeMillis(300000);

        // 配置一个连接在池中最小生存的时间,单位: ms
        druidDataSource.setMinEvictableIdleTimeMillis(300000);
        
        // 用来检测连接是否有效的sql,要求是一个查询语句。 如果validationQuery为null,testOnBorrow、testOnReturn、 testWhileIdle都不会其作用。
        druidDataSource.setValidationQuery("select 'x'");
        
        // 建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于 timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
        druidDataSource.setTestWhileIdle(true);
        // 申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。
        druidDataSource.setTestOnBorrow(false);
        // 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能
        druidDataSource.setTestOnReturn(false);
        
        /* 打开PSCache,并且指定每个连接上PSCache的大小。
        如果用Oracle,则把poolPreparedStatements配置为true,
        mysql可以配置为false。分库分表较多的数据库,建议配置为false */
        druidDataSource.setPoolPreparedStatements(false);
        // 指定每个连接上PSCache的大小
        druidDataSource.setMaxOpenPreparedStatements(20);
        // 合并多个DruidDataSource的监控数据
        //druidDataSource.setUseGlobalDataSourceStat(true);
        
        // 自动提交设置为 true
        druidDataSource.setDefaultAutoCommit(true);
        
        /*
         * 连接泄露检查,打开removeAbandoned功能 , 连接从连接池借出后,长时间不归还,将触发强制回连接。回收周期随timeBetweenEvictionRunsMillis进行.
         * 如果连接为从连接池借出状态,并且未执行任何sql,并且从借出时间起已超过removeAbandonedTimeout时间,则强制归还连接到连接池中。
         */
        druidDataSource.setRemoveAbandoned(true);
        // 单位: s
        druidDataSource.setRemoveAbandonedTimeout(5);
        // 关闭abanded连接时输出错误日志,这样出现连接泄露时可以通过错误日志定位忘记关闭连接的位置
        druidDataSource.setLogAbandoned(true);
        
        //配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
        /*
        属性类型是字符串,通过别名的方式配置扩展插件, 
		常用的插件有: 
		监控统计用的filter:stat  
		日志用的filter:log4j 
		防御sql注入的filter:wall
         */
        try {
        	druidDataSource.setFilters("stat,wall,slf4j");
        }catch(Exception e) {
        	e.printStackTrace();
        }
        // 配置监控统计日志的输出间隔,每次输出所有统计数据会重置,酌情开启. 单位: ms
        druidDataSource.setTimeBetweenLogStatsMillis(10000);
            
    	return druidDataSource;
    }
}

UserController.java内容:

package com.stamhe.springboot.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.stamhe.springboot.mapper.user.UserMapper;
import com.stamhe.springboot.model.UserModel;

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

	@Autowired
	private UserMapper userMapper;
	
	// http://localhost:8080/user/add
	@RequestMapping("/add")
	public Long addAction()
	{
		Long id = 0L;

		UserModel userModel = new UserModel();
		userModel.setName("user-测试-201908-006");
		userModel.setEmail("stamhe-201908-006@gmail.com");
		
		userMapper.insert(userModel);
		
		id = userModel.getId();
		
		return id;
	}
	
	// http://localhost:8080/user/detail/1
	@RequestMapping(value="/detail/{id}")
	public UserModel detailAction(@PathVariable("id")Long id)
	{
		UserModel userModel = userMapper.getOne(id);
		return userModel;
	}
}

ArticleController.java内容:

package com.stamhe.springboot.controller;

import java.util.Date;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.stamhe.springboot.mapper.article.ArticleMapper;
import com.stamhe.springboot.model.ArticleModel;

@RestController
@RequestMapping("/article")
public class ArticleController {

	@Autowired
	private ArticleMapper articleMapper;
	
	// http://localhost:8080/article/add
	@RequestMapping("/add")
	public Long addAction()
	{
		Long id = 0L;

		ArticleModel articleModel = new ArticleModel();

		articleModel.setTitle("article-测试-201908-002");
		articleModel.setSummary("article-测试-201908-002");
		articleModel.setStatus(1);
		articleModel.setType(1);
		articleModel.setUserId(1L);
		articleModel.setCreateTime(new Date());
		articleModel.setUpdateTime(new Date());
		articleModel.setPublicTime(new Date());
        articleMapper.insert(articleModel);
        id = articleModel.getId();
        
		return id;
	}
	
	// http://localhost:8080/article/detail/1
	@RequestMapping(value="/detail/{id}")
	public ArticleModel detailAction(@PathVariable("id")Long id)
	{
		ArticleModel articleModel = articleMapper.getOne(id);
		return articleModel;
	}
}

application.properties内容:

spring.datasource.user.url = jdbc:mysql://127.0.0.1:3306/user_201908?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.user.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.user.username = root
spring.datasource.user.password = 
spring.datasource.user.type = com.alibaba.druid.pool.DruidDataSource


spring.datasource.article.url = jdbc:mysql://localhost:3306/article_201908?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.article.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.article.username = root
spring.datasource.article.password = 
spring.datasource.article.type = com.alibaba.druid.pool.DruidDataSource


mybatis.type-aliases-package = com.stamhe.springboot.model

下面为示例实例运行起来后,数据库的连接情况:

可见每个库有3个连接长期存在,原因为我们在DSConfig的setDataSource方法中,有如下代码:

        druidDataSource.setMaxActive(10);
        druidDataSource.setInitialSize(3);
        druidDataSource.setMinIdle(3);

这三个参数,是用来初始化Druid的最大活跃连接数、初始连接数、最小空闲连接数这三个参数的。