参考链接:https://blog.csdn.net/yuanzhengme/article/details/121229360
1.druid环境安装
此文档安装环境:Ubuntu18.04/java-8(PS:java-8以上支持性没有java8好)
官方对于环境描述:
Java 8 (8u92+)
Linux, Mac OS X, or other Unix-like OS (Windows is not supported)
1.1java-8安装
sudo apt-get install openjdk-8-jdk
https://archive.apache.org/dist/druid/
1.2 安装mysql数据库
1.安装mysql-server
sudo apt install mysql-server
2.初始化配置
sudo mysql_secure_installation
#1
VALIDATE PASSWORD PLUGIN can be used to test passwords...
Press y|Y for Yes, any other key for No: N (我的选项)
#2
Please set the password for root here...
New password: (输入密码)
Re-enter new password: (重复输入)
#3
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them...
Remove anonymous users? (Press y|Y for Yes, any other key for No) : N (我的选项)
#4
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network...
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y (我的选项)
#5
By default, MySQL comes with a database named 'test' that
anyone can access...
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : N (我的选项)
#6
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y (我的选项)
3.检查mysql服务状态
systemctl status mysql.service
显示如下结果说明mysql服务是正常的
4.使用默认的用户名和密码登录修改PLUGIN设置
查看系统默认的用户名和密码
sudo cat /etc/mysql/debian.cnf
使用默认用户名密码登录
mysql -u('上图中的用户名') -p('上图中的密码')
修改PLUGIN设置
UPDATE mysql.user SET authentication_string=PASSWORD('root'), PLUGIN='mysql_native_password' WHERE USER='root';
设置完成之后重启服务
sudo service mysql stop
sudo service mysql start
5.使用mysql -uroot -p 输入密码之后密码进入数据库
PS:如果出现这种错误按照如下步骤来
出现场景:这个问题一般出现在刚刚安装完mysql的时候
出现原因:由于使用命令sudo apt-get install mysql安装时,并没有提示输入密码,则密码没有初始化,使用root用户登录自然失败.
具体情况:
执行步骤四4.使用默认的用户名和密码登录即可。
再次使用mysql -uroot -proot的方式即可登录
ps:查看mysql状态和mysql卸载重装过程
#查看mysql状态
sudo service mysql status
#启动mysql服务
sudo service mysql start
#停止mysql服务
sudo service mysql stop
#重启mysql服务
sudo service msyql restart
#权限不够命令加 sudo
完全卸载
sudo rm /var/lib/mysql/ -R
sudo rm /etc/mysql/ -R
sudo apt-get autoremove mysql* --purge
sudo apt-get remove apparmor
安装
sudo apt-get update
sudo apt-get install mysql-server
1.3 Mysql配置数据库远程访问(远程访问情况下需要配置)
在Ubuntu下MySQL缺省是只允许本地访问的,在本机之外使用是连不上的;如果你要其他机器也能够访问的话,需要进行配置.
首先要把MySQL的默认连接端口3306打开,查其看是否开放:
netstat -an | grep 3306
查看的结果如下(这表示未开放)
在配置文件中说明端口号
cd /etc/mysql/mysql.conf.d/
sudo vim mysqld.cnf
再次查看3306端口号
接着编辑mysqld.cnf 的配置文件
cd /etc/mysql/mysql.conf.d/
sudo vim mysqld.cnf
随后重启MySQL让其生效
service mysql restart
然后用根用户进入mysql
sudo mysql -uroot -p
GRANT ALL PRIVILEGES ON *.* to root@'%' IDENTIFIED BY 'root';
%代表所有主机,也可以是具体的ip;
上文中root的host已经是%,意思是所有不同主机都可以连接到此处的mysql来
1.4 druid安装过程
tar -zxvf apache-druid-0.21.0-bin.tar.gz
mv apache-druid-0.21.0 /usr/local/druid
进入apache-druid的安装目录后,我们可以看一下各子目录和文件的功能:
bin/:用于快速入门的脚本。
conf/:单机和集群设置的配置示例。
extensions/:核心Druid扩展。
hadoop-dependencies/:Druid的Hadoop依赖。
lib/:核心Druid的库和依赖。
quickstart/:快速入门的配置文件、样例数据和其他文件。
DISCLAIMER、LICENSE和NOTICE文件。
1.5 druid快速启动
Nano-Quickstart: 1 CPU, 4GB RAM
Micro-Quickstart: 4 CPU, 16GB RAM
Small: 8 CPU, 64GB RAM (~i3.2xlarge)
Medium: 16 CPU, 128GB RAM (~i3.4xlarge)
Large: 32 CPU, 256GB RAM (~i3.8xlarge)
X-Large: 64 CPU, 512GB RAM (~i3.16xlarge)
根据自己运行环境选择相应的启动选项,本文档选用的是start-nano-quickstart。在apache-druid-0.21.0(Druid的安装目录)下,运行./bin/start-nano-quickstart命令。这个命令用于启动Druid。也可以在bin目录下直接运行./start-nano-quickstart,如果权限不够加上sudo即可。
如果是安装在本机中的话,在浏览器中输入:localhost:8888即可访问Druid了
2.druid入门小试
下载mysql和durid的jar包
mysql的jar包https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.25/
druid的jar包https://repo1.maven.org/maven2/com/alibaba/druid/1.1.13/
2.1 通过Druid配置文件使用
1. 导入mysql和DRUID jar 包
2. 拷贝配置文件到src目录
3. 根据配置文件创建Druid连接池对象
4. 从Druid连接池对象获得Connection
配置文件:
# 数据库连接参数
url=jdbc:mysql://(数据库地址):3306/[这里输入数据库文件名路径]
username=[这里输入数据库用户名]
password=[这里输入数据库密码]
driverClassName=com.mysql.jdbc.Driver//驱动
# 连接池的参数
initialSize=10//初始化连接数
maxActive=10//最大最大活动连接数
maxWait=2000//最大等待时间
2.2 使用druid
druid底层是使用的工厂设计模式,去加载配置文件
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
//
public class TestDruid {
public void test01() throws Exception {
//配置文件的方式使用Druid连接池
//1. 创建Properties对象
Properties properties = new Properties();
//2. 将配置文件转换成字节输入流
InputStream is = TestDruid.class.getClassLoader().getResourceAsStream("druid.properties");
//3. 使用properties对象加载is
properties.load(is);
//druid底层是使用的工厂设计模式,去加载配置文件,创建DruidDataSource对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection conn1 = dataSource.getConnection();
Connection conn2 = dataSource.getConnection();
Connection conn3 = dataSource.getConnection();
Connection conn4 = dataSource.getConnection();
Connection conn5 = dataSource.getConnection();
Connection conn6 = dataSource.getConnection();
Connection conn7 = dataSource.getConnection();
Connection conn8 = dataSource.getConnection();
Connection conn9 = dataSource.getConnection();
Connection conn10 = dataSource.getConnection();
System.out.println(conn1);
System.out.println(conn2);
is.close();//关流
}
}
出现下图即为成功
3.sql语句中词法\语法分析
3.1Druid格式化SQL语句
String sql3 =
"select \n" +
" t1.*\n" +
" ,t2.*\n" +
"from\n" +
"(\n" +
" select \n" +
" shop_id 门店id\n" +
" ,shop_name 门店名称\n" +
" from tableA\n" +
" where pt=20201130\n" +
" and shop_level>=8 --注释\n" +
") t1\n" +
"left join\n" +
"(\n" +
" SELECT \n" +
" shop_id\n" +
" ,sum(create_orders) AS 创建订单数\n" +
" ,sum(payment_amount) AS 支付订单金额\n" +
" ,sum(pay_orders) AS 成功订单数\n" +
" ,sum(pay_amount) AS 成功订单金额\n" +
" ,sum(coalesce(ninety_orders,0) -coalesce(ninety_refund_orders,0)) AS XX元订单数\n" +
" ,sum(coalesce(ninety_order_amount,0) - coalesce(ninety_refund_amount,0)) AS XX元订单金额\n" +
" ,sum(zero_orders) AS 0元订单数\n" +
" ,sum(coalesce(pay_orders,0) - coalesce(zero_orders,0)) AS 付费订单数\n" +
" FROM tableB\n" +
" WHERE substr(pt,1,6) = '202011'\n" +
" GROUP BY \n" +
" shop_id\n" +
") t2 on t1.门店id = t2.shop_id\n" +
";";
List<SQLStatement> sqlStatementList = SQLUtils.parseStatements(sql3,JdbcConstants.MYSQL);
System.out.println(sqlStatementList);
System.out.println();
3.2 Druid 解析 insert语句
String sql = "insert into table_test_2 (farendma, hesuandm, hesuanmc, weihguiy, weihjigo, weihriqi, shijchuo) values\n" +
"('99996','HS205301','代码1','S####','101001','20140101',1414673101376), \n" +
"('99996','HS205401','代码2','S####','101001','20140101',1414673101376);";
MySqlStatementParser parser = new MySqlStatementParser(sql);
SQLStatement sqlStatement = parser