发表于： 2021-11-07 23:29:34
2 886
今天完成的事：设计数库库结构
创建新的项目
配置pom.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<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>org.example</groupId>
<artifactId>task</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>17</maven.compiler.source>
<maven.compiler.target>17</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>5.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>2.0.6</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.30</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-simple</artifactId>
<version>2.0.0-alpha0</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>5.8.1</version>
<scope>compile</scope>
</dependency>
</dependencies>
</project>
用mvaen自动加载依赖包，让好写数据库连接配置
<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration >
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
<!-- 和Spring整合后envirnmentss 配置将被移除-->
<environments default="mysql">
<environment id="mysql">
<!-- 使用jdbc事务管理-->
<transactionManager type="JDBC"/>
<!-- 数据库连接池-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://服务器ip:3306/task?useServerPrepStmts=true"/>
<property name="username" value="root"/>
<property name="password" value="8888888"/>
</dataSource>
</environment>
</environments>
<!-- 写完SQL映射文件后为了能让mybatis资源文件加载类解析Mapper文件-->
<mappers>
<mapper resource="UserMapper.xml"/>
</mappers>
</configuration>
创建实体类user.java
package com.jnshu.task.app;
import java.util.Date;
public class User {
public String getAddress() {return address;}
public void setAddress(String address) {this.address = address;}
private String address;
public int getId() {return id;}
public void setId(int id) {this.id = id;}
private int id;
public String getUsername() {return username;}
public void setUsername(String username) {this.username = username;}
private String username;
public String getSex() {return sex;}
public void setSex(String sex) {this.sex = sex;}
private String sex;
public String getCourse() {return course;}
public void setCourse(String course) {this.course = course;}
private String course;
public String getEducation() {return education;}
public void setEducation(String education) {this.education = education;}
private String education;
public String getPhone() {return phone;}
public void setPhone(String phone) {this.phone = phone;}
private String phone;
public String getTerget() {return terget;}
public void setTerget(String terget) {this.terget = terget;}
private String terget;
public String getSchool() {return school;}
public void setSchool(String school) {this.school = school;}
private String school;
public Date getBirthday() {return birthday;}
public void setBirthday(Date birthday) {this.birthday = birthday;}
private Date birthday;
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
", course'" + course + '\'' +
", phone='" + phone + '\'' +
", education='" + education + '\'' +
", school='" + school + '\'' +
", terget='" + terget + '\'' +
'}';}}
写数据库映射文件User.mapper
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jnshu.task.mapper.UserMapper">
<!-- 根据id获取用户信息 -->
<select id="findUserById" parameterType="int" resultType="com.jnshu.task.app.User">
select * from user where id = #{id}
</select>
<!-- 自定义条件查询用户列表 -->
<select id="findUserByUsername" parameterType="java.lang.String"
resultType="com.jnshu.task.app.User">
select * from user where username like '%${value}%'
</select>
<!-- 添加用户 -->
<insert id="insertUser" parameterType="com.jnshu.task.app.User">
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
select LAST_INSERT_ID()
</selectKey>
insert into user(username,sex,address,birthday,course,phone,education,school,terget)
values(#{username},#{sex},#{address},#{birthday},#{course},#{phone},#{education},#{school},#{terget})
</insert>
<insert id="insertBatch" parameterType="com.jnshu.task.app.User">
insert into user (username,sex,address,birthday,course,phone,education,school,terget) values
<foreach collection="list" item="user" separator="," close=";">
(#{user.username},#{user.sex},#{user.address},#{user.birthday},#{user.course},#{user.phone},#{user.education},#{user.school},#{user.terget})
</foreach>
</insert>
</mapper>
编写测试类
package com.jnshu.task.app;
import com.jnshu.task.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.TestInstance;
import org.mybatis.spring.SqlSessionTemplate;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@TestInstance(TestInstance.Lifecycle.PER_CLASS)
public class MybatisTest {
private SqlSession sqlSession;
private UserMapper dao;
private InputStream is;
@BeforeAll
public void init() throws IOException{
//1)读取配置文件
is = Resources.getResourceAsStream("SqlMapConfig.xml");
//2)创建SqlSessionFactory
SqlSessionFactoryBuilder sqlBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlFactory = sqlBuilder.build(is);
//3)使用工厂创建SqlSession对象 openSession(falshe) 为手动提交事务
sqlSession = sqlFactory.openSession(false);
//4)使用SqlSession创建Dao接口的代理对象
dao = sqlSession.getMapper(UserMapper.class);
}
@Test
public void testBatchInsert() {
List<User> users = new ArrayList<User>();
int userCount = 10000;
User user = new User();
for (int i = 0; i != userCount; i++) {
user.setUsername("聂小倩");
user.setBirthday(new Date());
user.setSex("女");
user.setAddress("长沙");
user.setCourse("UI");
user.setEducation("本科");
user.setPhone("1889220708");
user.setSchool("深圳大学");
user.setTerget("不工作就没有放吃");
Date date = new Date();
date.setTime(108L);
user.setBirthday(date);
users.add(user);
}
//按批次插入
for (int j=0;j<100;j++) {
dao.insertBatch(users);
}
sqlSession.clearCache();
//这里需要提交事务
sqlSession.commit()
}
@AfterAll
public void close() throws IOException{
//6)释放资源
sqlSession.close();
is.close();
}
}
向数据库插入了1个亿的数据了；
在写入数据中遇到很多问题，首先是按照自己的想法直接100w100w的往数据库丢，想法有好了。任务不是很好完成，经自己测试最多列表插入一次最多插入30w数据，多了就内存不够了。为了完成2个亿的数据目标，然后在网上查了资料，看到批次插入，这个想法跟自己不谋而合，立马回来测试。
测试结果如下
不按批次所用时间 按批次所用时间
- 1w 3.56s 3.56s
- 2w 7.779s 5.393s
- 5w 13.426s 9.594s
- 10w 39s 15.559s
- 20w 1m37s 26.868s
- 30w 4m32s
50w 内存不够 58s
- 100w 1m44s
- 1000w 22m
今天遇到的问题：在向服务器插入数据的时候，也测试了JDBC连接不用mybatis插入数据是会快一点，但是考虑到以后表改动以后不好维护，所以用mybatis写的代码，在插入过程中前面并没有这么快，是看到一个博客上的资料发现的，后面更改的，利用mavicat创建Mysql的表默认为InnoDB引擎。为了插入数据快改为MyISAM引擎
在这个过程中因为内存不够的问题改了IDEA中JVM的堆内存，给服务器（CentOs8）加了swap分区
今天的总结：所有的问题都是在实践中发现并解决的，了解了JAVA的事务机制，JVM内存，还有就是各种数据库引擎对于读写，与安全性
明天的任务：总结任务1
返回列表
评论