数据库实践

---

前言

记录自己初次学完数据库后的简单项目实践
环境：centos7、docker、mysql、java

---

一、创建容器数据库

1.容器数据库的创建

拉取镜像并创建数据库容器

docker pull mysql:5.6.36
docker run --name mysql --privileged --network host -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.6.36

2.创建数据库

进入容器内部mysql交互界面内

docker exec -it mysql bash -c "mysql uroot -p"

create database clusterSize1600 character set utf8;

二、连接容器数据库

1.Druid数据库连接池

JDBC中使用DriveManage.getConnection()获取的是与数据库的连接，建立与数据库的连接是一项较耗资源的工作，频繁的进行数据库连接建立操作会产生较大的系统开销。

通过数据库连接池，预先创建好几个数据库连接存放到容器内供使用，每一次使用完连接后并不释放数据库连接，而是放回容器内，这样可以节约数据库连接的创建和释放的时间和系统开销。

DataSource是应用程序与数据库连接的一个抽象的中间层，是一个接口。

使用Druid连接池创建连接池并获取连接

public class DruidDemo {
    public static void main(String[] args) throws Exception {
        //1、导入jar包
        //2、定义配置文件druid.properties并与类文件放在一个目录下
        //3、加载配置文件
        Properties pro = new Properties();
        InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
        pro.load(is);
        //4、获取数据库连接池对象
        DataSource ds = DruidDataSourceFactory.createDataSource(pro);
        System.out.println(ds);
        //5、获取一个数据库连接
        Connection con = ds.getConnection();
        System.out.println(ds);
        //6.放回连接到连接池容器
        con.close();
        System.out.println(ds);
    }
}

druid配置文件 容器使用的是本机3306端口

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///clusterSize1600
username=root
password=123456
initialSize=5
maxActive=10
maxWait=3000

连接池打印结果

init ds----------------------------
{
	CreateTime:"2021-04-07 12:34:50",
	ActiveCount:0,
	PoolingCount:0,
	CreateCount:0,
	DestroyCount:0,
	CloseCount:0,
	ConnectCount:0,
	Connections:[
	]
}
Apr 07, 2021 12:34:50 PM com.alibaba.druid.pool.DruidDataSource info
INFO: {dataSource-1} inited
ds---------------------------------
{
	CreateTime:"2021-04-07 12:34:50",
	ActiveCount:1,
	PoolingCount:4,
	CreateCount:5,
	DestroyCount:0,
	CloseCount:0,
	ConnectCount:1,
	Connections:[
		{ID:2114889273, ConnectTime:"2021-04-07 12:34:50", UseCount:0, LastActiveTime:"2021-04-07 12:34:50"},
		{ID:1025799482, ConnectTime:"2021-04-07 12:34:50", UseCount:0, LastActiveTime:"2021-04-07 12:34:50"},
		{ID:1504109395, ConnectTime:"2021-04-07 12:34:50", UseCount:0, LastActiveTime:"2021-04-07 12:34:50"},
		{ID:2047526627, ConnectTime:"2021-04-07 12:34:50", UseCount:0, LastActiveTime:"2021-04-07 12:34:50"}
	]
}
ds---------------------------------
{
	CreateTime:"2021-04-07 12:34:50",
	ActiveCount:0,
	PoolingCount:5,
	CreateCount:5,
	DestroyCount:0,
	CloseCount:1,
	ConnectCount:1,
	Connections:[
		{ID:2114889273, ConnectTime:"2021-04-07 12:34:50", UseCount:0, LastActiveTime:"2021-04-07 12:34:50"},
		{ID:1025799482, ConnectTime:"2021-04-07 12:34:50", UseCount:0, LastActiveTime:"2021-04-07 12:34:50"},
		{ID:1504109395, ConnectTime:"2021-04-07 12:34:50", UseCount:0, LastActiveTime:"2021-04-07 12:34:50"},
		{ID:2047526627, ConnectTime:"2021-04-07 12:34:50", UseCount:0, LastActiveTime:"2021-04-07 12:34:50"},
		{ID:1908316405, ConnectTime:"2021-04-07 12:34:50", UseCount:1, LastActiveTime:"2021-04-07 12:34:50"}
	]
}

2.JDBCTemplate

优点：
使用JdbcTemplate可节省大量的冗余代码（创建连接，关闭连接，处理异常），因为JdbcTemplate类会自动处理它，只关注业务逻辑即可。
连接池打印结果

JdbcTemplate template = new JdbcTemplate(dataBase);
System.out.println(template.queryForList("show databases;",String.class));

---

三、批量插入数据

1.单条插入

直接使用update方法实现数据插入

for (int j = 0; j < clusterSize; j++) {
    List<Integer> links = linkss.get(j);
    List<Integer> delays = delayss.get(j);
    String sqlInsert = "insert into interval"+i+" values(null,?,?,?,?,?,?,?,?,?,?,?,?)";
    int res = template.update(sqlInsert,links.get(0),links.get(1),links.get(2),links.get(3),delays.get(0),delays.get(1),delays.get(2),delays.get(3),0,0,0,0);
    if(res!=1){
        System.out.println("error"+i+" "+j);
    }
}

起初教程里对于DML语句只提到了update方法，而采用这个方法在数据批量插入的情况下会耗费大量时间。真正耗时的不是操作，而是链接，解析的过程。单条sql的话，会在链接，解析部分耗费大量的时间，因此速度会很慢。一个表需要35秒

2.批量插入1

使用sql的insert into tablename valus(),(),(),…;一次性批量插入

for (int j = 0; j < clusterSize; j++) {
    List<Integer> links = linkss.get(j);
    List<Integer> delays = delayss.get(j);
    String sqlInsert = "insert into interval"+i+" values(null,?,?,?,?,?,?,?,?,?,?,?,?)";
    int res = template.update(sqlInsert,links.get(0),links.get(1),links.get(2),links.get(3),delays.get(0),delays.get(1),delays.get(2),delays.get(3),0,0,0,0);
    if(res!=1){
        System.out.println("error"+i+" "+j);
    }
}

通过使用一次性批量写入表字段的方式，速度达到0.5秒一个表，提高了70倍。

3.批量插入2

使用jdbcTemplate.batchUpdate(String sql, List<Object[]> batchArgs)
表示批量进行插入，插入一个list集合，返回的是影响的行数

 String sqlInsert = "insert into interval"+i+" values(null,?,?,?,?,?,?,?,?,?,?,?,?)";
 List<Object[]> batchArgs = new ArrayList<Object[]>();
 for (int j = 0; j < clusterSize; j++) {
    List<Integer> links = linkss.get(j);
    List<Integer> delays = delayss.get(j);
    batchArgs.add(new Object[] { links.get(0),links.get(1),links.get(2),links.get(3),delays.get(0),delays.get(1),delays.get(2),delays.get(3),0,0,0,0 });
 }
 int res[] = template.batchUpdate(sqlInsert, batchArgs);

注意：连接数据库的url里需要添加rewriteBatchedStatements=true
url=jdbc:mysql:///clusterSize1600?rewriteBatchedStatements=true

---

三、查询数据库表项

---

总结