在演示中，我们将使用MySQL和可以找到的示例SQL模式(HR App)这里:
该模式包含7个表，它们之间具有不同的关联关系。
1.建立数据库
如果没有安装MySQL，可以从这里.
如果你喜欢用码头工人作为一种快速选择，您可以使用以下命令来设置和启动MySQL：
#This will pull the official mysql docker image
docker pull mysql
下载完成后，您可以像这样启动MySQL容器：
# This is will start a mysql instance on port 3306 (no password will be required)
docker run -p 3306:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d mysql --default-authentication-plugin=mysql_native_password
此时，您应该可以启动并运行MySQL。
工作和连接到MySQL的最简单方法是安装MySQL工作台。默认情况下，IDE附带一个预定义的连接到本地主机：3306(其中3306是MySQL操作的默认端口)。
下一步是创建人力资源架构。这，这个，那，那个DDL用于创建表的脚本如下所示。
在Purporse中，我没有在表的定义中包含任何约束(比如FKS)。这些约束可以在生成和执行实际插入后添加。
DROP SCHEMA IF EXISTS hr;
CREATE SCHEMA hr COLLATE = utf8_general_ci;
USE hr;
CREATE TABLE regions (
region_id INT (11) UNSIGNED NOT NULL,
region_name VARCHAR(25),
PRIMARY KEY (region_id)
);
CREATE TABLE countries (
country_id CHAR(2) NOT NULL,
country_name VARCHAR(40),
region_id INT (11) UNSIGNED NOT NULL,
PRIMARY KEY (country_id)
);
CREATE TABLE locations (
location_id INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,
street_address VARCHAR(40),
postal_code VARCHAR(12),
city VARCHAR(30) NOT NULL,
state_province VARCHAR(25),
country_id CHAR(2) NOT NULL,
PRIMARY KEY (location_id)
);
CREATE TABLE departments (
department_id INT (11) UNSIGNED NOT NULL,
department_name VARCHAR(30) NOT NULL,
manager_id INT (11) UNSIGNED,
location_id INT (11) UNSIGNED,
PRIMARY KEY (department_id)
);
CREATE TABLE jobs (
job_id VARCHAR(10) NOT NULL,
job_title VARCHAR(35) NOT NULL,
min_salary DECIMAL(8, 0) UNSIGNED,
max_salary DECIMAL(8, 0) UNSIGNED,
PRIMARY KEY (job_id)
);
CREATE TABLE employees (
employee_id INT (11) UNSIGNED NOT NULL,
first_name VARCHAR(20),
last_name VARCHAR(25) NOT NULL,
email VARCHAR(25) NOT NULL,
phone_number VARCHAR(20),
hire_date DATE NOT NULL,
job_id VARCHAR(10) NOT NULL,
salary DECIMAL(8, 2) NOT NULL,
commission_pct DECIMAL(2, 2),
manager_id INT (11) UNSIGNED,
department_id INT (11) UNSIGNED,
PRIMARY KEY (employee_id)
);
CREATE TABLE job_history (
employee_id INT (11) UNSIGNED NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
job_id VARCHAR(10) NOT NULL,
department_id INT (11) UNSIGNED NOT NULL
);
ALTER TABLE job_history ADD UNIQUE INDEX (
employee_id,
start_date
);
如果一切顺利，您应该看到一条确认消息：
2.使用MockNeat为架构生成SQL插入
可以找到MockNeat的安装页面。这里.
库作为Maven存储库发布在JCenter()，因此您可以轻松地将其包含在您的Gradle构建中：
repositories {
// something else
jcenter()
}
dependencies {
// something else
compile 'net.andreinc.mockneat:mockneat:0.2.3'
}
或者如果您喜欢maven：
<repositories>
<repository>
<id>jcenter</id>
<url>https://jcenter.bintray.com/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>net.andreinc.mockneat</groupId>
<artifactId>mockneat</artifactId>
<version>0.2.3</version>
</dependency>
</dependencies>
我的建议是始终检查官方GitHub页面或者JCenter()页面获取最新版本。
我们可以使用sqlInserts()方法。
第一步是创建一个MockNeat实例(或重用预定义的实例之一)：
MockNeat m = MockNeat.threadLocal();
之后，我们可以开始为第一个表生成实际的SQL插入：“区域".
// ----- Regions -----
String[] regionNames = new String[] {"Europe", "Americas", "Asia", "Middle East and Africa" };
SQLTable regions = m.sqlInserts()
.tableName("regions")
.column("region_id", m.intSeq().start(1)) // A sequence 1,2,3...
// At each step we iterate for the values in the regionNames[] array
.column("region_name", m.seq(regionNames), TEXT_BACKSLASH)
.table(regionNames.length) // The number of rows
.val();
重写SQLTable toString()方法以生成与表关联的所有相应SQL插入。所以如果我们打印“区域“对象，我们将获得如下内容：
System.out.println(regions);
/**
Output:
INSERT INTO regions (region_id, region_name) VALUES (1, 'Europe');
INSERT INTO regions (region_id, region_name) VALUES (2, 'Americas');
INSERT INTO regions (region_id, region_name) VALUES (3, 'Asia');
INSERT INTO regions (region_id, region_name) VALUES (4, 'Middle East and Africa');
**/
下一步是为“各国“桌子。MockNeat已经定义了两个字典，其中包含241个国家的名称和iso代码(DictType.COUNTRY_NAME和DictType.COUNTRY_ISO_CODE_2)我们可以做的是使用SEQ()方法。
int numCountries = 241;
SQLTable countries = m.sqlInserts()
.tableName("countries")
.column("country_id", m.seq(COUNTRY_ISO_CODE_2), TEXT_BACKSLASH)
.column("country_name", m.seq(COUNTRY_NAME), TEXT_BACKSLASH)
.column("region_id", regions.fromColumn("region_id"))
.table(numCountries)
.val();
对于“Region_id”，我们将使用From Column()方法从Regions表中随机获取数据。当我们想要生成关系数据和执行外键约束时，这种方法特别有用。
如果要打印与SQLTable关联的行，我们将获得如下内容：
System.out.println(countries);
/**
Output
NSERT INTO countries (country_id, country_name, region_id) VALUES ('AF', 'Afghanistan', 2);
INSERT INTO countries (country_id, country_name, region_id) VALUES ('AL', 'Albania', 1);
INSERT INTO countries (country_id, country_name, region_id) VALUES ('DZ', 'Algeria', 1);
INSERT INTO countries (country_id, country_name, region_id) VALUES ('AS', 'American Samoa', 4);
INSERT INTO countries (country_id, country_name, region_id) VALUES ('AD', 'Andorra', 3);
INSERT INTO countries (country_id, country_name, region_id) VALUES ('AO', 'Angola', 2);
INSERT INTO countries (country_id, country_name, region_id) VALUES ('AI', 'Anguilla', 3);
....and so on
**/
为“地点“表将涉及编写我们自己的自定义MockUnit(任意数据生成器)。
我们将需要“邮政编码”和“Street_Address”列的任意数据生成器。
MockUnitString streetAddressGen =
m.fmt("#{num} #{noun} #{end}")
.param("num", m.ints().range(10, 2000))
.param("noun", m.words().nouns().format(CAPITALIZED))
.param("end", m.from(new String[]{"Ave", "St", "Blvd", "Rd"}));
所以使用FMT()方法，我们将能够生成具有以下格式的街道地址：
System.out.println(streetAddressGen.val());
System.out.println(streetAddressGen.val());
System.out.println(streetAddressGen.val());
/**
Output:
1009 Maroons Blvd
286 Berberine Ave
1454 Eatables Ave
**/
使用类似的方法，我们将编写自己的MockUnit来为“邮政编码”列生成数据。
MockUnitString postalCodeGen =
m.fmt("#{word1} #{word2}")
.param("word1", m.strings().size(3).format(UPPER_CASE))
.param("word2", m.strings().size(3).format(UPPER_CASE));
邮政编码的格式是：
System.out.println(postalCodeGen.val());
System.out.println(postalCodeGen.val());
System.out.println(postalCodeGen.val());
/**
Output:
ENO OYW
OTQ NKZ
BCE TB8
**/
现在，我们可以重用我们的生成器来实际为“位置”表生成数据：
int numLocations = 100;
SQLTable locations =
m.sqlInserts()
.tableName("locations")
.column("location_id", m.intSeq().start(1000).increment(100))
.column("street_address", streetAddressGen, TEXT_BACKSLASH)
.column("postal_code", postalCodeGen, TEXT_BACKSLASH)
.column("city", m.cities().us(), TEXT_BACKSLASH)
.column("state_province", m.cities().capitals(), TEXT_BACKSLASH)
.column("country_id", countries.fromColumn("country_id"), TEXT_BACKSLASH)
.table(numLocations)
.val();
如果要打印“Locations”对象，SQL插入将如下所示：
System.out.println(locations);
/**
Output:
INSERT INTO locations (location_id, street_address, postal_code, city, state_province, country_id) VALUES (1000, '15 Olms St', '9Z3 DOA', 'Weaver', 'Ulaanbaatar', 'MS');
INSERT INTO locations (location_id, street_address, postal_code, city, state_province, country_id) VALUES (1100, '205 Dah Rd', 'M4D D7X', 'Land of Pines', 'Baghdad', 'CR');
INSERT INTO locations (location_id, street_address, postal_code, city, state_province, country_id) VALUES (1200, '848 Mirliton Blvd', 'JSA ARO', 'Millersburg', 'Prague', 'MT');
INSERT INTO locations (location_id, street_address, postal_code, city, state_province, country_id) VALUES (1300, '1585 Supplementer Rd', '97R RF7', 'Sparta', 'São Tomé', 'PY');
... and so on
**/
下一步是开始生成“各部门“信息。这将是相当直截了当的。唯一的先决条件是已经生成了一个经理ID列表。今后我们将此列表用于其他表。
// Manager ids
// Generating a list of 20 manager ids in the range [1..1000).
List<Integer> managerIds = m.ints()
.range(1, 1000)
.list(() -> new ArrayList<>(), 20)
.val();
// We will have 10 departments
int depNum = 10;
SQLTable departments = m.sqlInserts()
.tableName("departments")
.column("department_id", m.intSeq().start(0).increment(10))
.column("department_name",m.seq(DEPARTMENTS), TEXT_BACKSLASH)
.column("manager_id", m.from(managerIds))
.column("location_id", locations.fromColumn("location_id"))
.table(depNum)
.val();
在部门生成之后，我们可以开始生成实际的“就业":
// ----- JOBS -----
String[] jobNames = new String[] {"Analyst",
"Consultant",
"Senior Consultant",
"Manager",
"Software Architect",
"Senior Manager",
"Director"};
String[] jobIds = new String[] { "A",
"C",
"SC",
"M",
"SA",
"SM",
"D" };
int numJobs = jobNames.length;
int minSalary = 2000;
int maxSalary = 5000;
SQLTable jobs = m.sqlInserts()
.tableName("jobs")
.column("job_id", m.seq(jobIds), TEXT_BACKSLASH)
.column("job_title", m.seq(jobNames), TEXT_BACKSLASH)
.column("min_salary", minSalary + "")
.column("max_salary", maxSalary + "")
.table(numJobs)
.val();
System.out.println(jobs);
最后一个也是最复杂的表是“员工“在这里，我们需要编写额外的编码逻辑，以确保每个员工都有一个与其相关的ManagerId。
// ----- EMPLOYEES -----
int numEmployes = 1000;
SQLTable employees = m.sqlInserts()
.tableName("employees")
.column("employee_id", m.intSeq())
.column("first_name", m.names().first(), TEXT_BACKSLASH)
.column("last_name", m.names().last(), TEXT_BACKSLASH)
.column("email", m.emails().domain("corp.com"), TEXT_BACKSLASH)
.column("phone_number", m.regex("\\+30 [0-9]{9}"), TEXT_BACKSLASH)
.column("hire_date",
m.localDates()
.past(LocalDate.of(2000, 1, 1))
.display(BASIC_ISO_DATE), TEXT_BACKSLASH)
.column("job_id", jobs.fromColumn("job_id"), TEXT_BACKSLASH)
.column("salary", m.ints().range(minSalary, maxSalary))
.column("commission_pct", "NULL")
.column("manager_id", "NULL" /* TO UPDATE LATER */)
.column("department_id", departments.fromColumn("department_id"))
.table(numEmployes)
.val();
// Updating employees with their manager ID
// - One person has no manager ID - it should be a director level person
// - Manager id and employee_id should be different
// We update all people with a manager_id
employees.updateAll((i, insert) -> {
Integer employeeId = parseInt(insert.getValue("employee_id"));
Integer managerId = Integer.MIN_VALUE;
while(employeeId == (managerId = m.from(managerIds).val()));
insert.setValue("manager_id", managerId + "");
});
// One of the directors doesn't have a manager id as he is the CO
employees.selectFirstWhere(sqlInsert -> sqlInsert.getValue("job_id").equals("D"))
.get()
.setValue("manager_id", "NULL");
System.out.println(employees);
3.把一切都放在一起
如果我们将所有内容组合在一起，则生成的脚本如下所示：
import net.andreinc.mockneat.MockNeat;
import net.andreinc.mockneat.abstraction.MockUnitString;
import net.andreinc.mockneat.unit.text.sql.SQLTable;
import org.junit.Test;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import static java.lang.Integer.parseInt;
import static java.time.format.DateTimeFormatter.BASIC_ISO_DATE;
import static net.andreinc.mockneat.types.enums.DictType.COUNTRY_ISO_CODE_2;
import static net.andreinc.mockneat.types.enums.DictType.COUNTRY_NAME;
import static net.andreinc.mockneat.types.enums.DictType.DEPARTMENTS;
import static net.andreinc.mockneat.types.enums.StringFormatType.CAPITALIZED;
import static net.andreinc.mockneat.types.enums.StringFormatType.UPPER_CASE;
import static net.andreinc.mockneat.unit.text.sql.escapers.MySQL.TEXT_BACKSLASH;
public class SQLInsertsTest {
public static void main(String[] args) {
MockNeat m = MockNeat.threadLocal();
// ----- Regions -----
String[] regionNames = new String[] {"Europe",
"Americas",
"Asia",
"Middle East and Africa" };
SQLTable regions = m.sqlInserts()
.tableName("regions")
.column("region_id",
m.intSeq().start(1))
.column("region_name",
m.seq(regionNames), TEXT_BACKSLASH)
.table(regionNames.length)
.val();
System.out.println(regions);
// ----- Countries -----
int numCountries = 241;
SQLTable countries = m.sqlInserts()
.tableName("countries")
.column("country_id", m.seq(COUNTRY_ISO_CODE_2), TEXT_BACKSLASH)
.column("country_name", m.seq(COUNTRY_NAME), TEXT_BACKSLASH)
.column("region_id", regions.fromColumn("region_id"))
.table(numCountries)
.val();
System.out.println(countries);
// ----- Locations -----
int numLocations = 100;
MockUnitString streetAddressGen = m.fmt("#{num} #{noun} #{end}")
.param("num",
m.ints().range(10, 2000))
.param("noun",
m.words().nouns().format(CAPITALIZED))
.param("end",
m.from(new String[]{"Ave", "St", "Blvd", "Rd"}));
MockUnitString postalCodeGen = m.fmt("#{word1} #{word2}")
.param("word1",
m.strings().size(3).format(UPPER_CASE))
.param("word2",
m.strings().size(3).format(UPPER_CASE));
SQLTable locations = m.sqlInserts()
.tableName("locations")
.column("location_id",
m.intSeq().start(1000).increment(100))
.column("street_address",
streetAddressGen, TEXT_BACKSLASH)
.column("postal_code",
postalCodeGen, TEXT_BACKSLASH)
.column("city",
m.cities().us(), TEXT_BACKSLASH)
.column("state_province",
m.cities().capitals(), TEXT_BACKSLASH)
.column("country_id",
countries.fromColumn("country_id"), TEXT_BACKSLASH)
.table(numLocations)
.val();
System.out.println(locations);
// ----- Departments -----
// Manager ids
List<Integer> managerIds = m.ints()
.range(1, 1000)
.list(() -> new ArrayList<>(), 20)
.val();
int depNum = 10;
SQLTable departments = m.sqlInserts()
.tableName("departments")
.column("department_id",
m.intSeq().start(0).increment(10))
.column("department_name",
m.seq(DEPARTMENTS), TEXT_BACKSLASH)
.column("manager_id",
m.from(managerIds))
.column("location_id",
locations.fromColumn("location_id"))
.table(depNum)
.val();
System.out.println(departments);
// ----- JOBS -----
String[] jobNames = new String[] {"Analyst",
"Consultant",
"Senior Consultant",
"Manager",
"Software Architect",
"Senior Manager",
"Director"};
String[] jobIds = new String[] { "A",
"C",
"SC",
"M",
"SA",
"SM",
"D" };
int numJobs = jobNames.length;
int minSalary = 2000;
int maxSalary = 5000;
SQLTable jobs = m.sqlInserts()
.tableName("jobs")
.column("job_id",
m.seq(jobIds), TEXT_BACKSLASH)
.column("job_title",
m.seq(jobNames), TEXT_BACKSLASH)
.column("min_salary",
minSalary + "")
.column("max_salary",
maxSalary + "")
.table(numJobs)
.val();
System.out.println(jobs);
// ----- EMPLOYEES -----
int numEmployes = 1000;
SQLTable employees = m.sqlInserts()
.tableName("employees")
.column("employee_id",
m.intSeq())
.column("first_name",
m.names().first(), TEXT_BACKSLASH)
.column("last_name",
m.names().last(), TEXT_BACKSLASH)
.column("email",
m.emails().domain("corp.com"), TEXT_BACKSLASH)
.column("phone_number",
m.regex("\\+30 [0-9]{9}"), TEXT_BACKSLASH)
.column("hire_date",
m.localDates()
.past(LocalDate.of(2000, 1, 1))
.display(BASIC_ISO_DATE), TEXT_BACKSLASH)
.column("job_id",
jobs.fromColumn("job_id"), TEXT_BACKSLASH)
.column("salary",
m.ints().range(minSalary, maxSalary))
.column("commission_pct",
"NULL")
.column("manager_id",
"NULL" /* TO UPDATE LATER */)
.column("department_id",
departments.fromColumn("department_id"))
.table(numEmployes)
.val();
// Updating employees with their manager ID
// - One person has no manager ID - it should be a director level person
// - Manager id and employee_id should be different
// We update all people with a manager_id
employees.updateAll((i, insert) -> {
Integer employeeId = parseInt(insert.getValue("employee_id"));
Integer managerId = Integer.MIN_VALUE;
while(employeeId == (managerId = m.from(managerIds).val()));
insert.setValue("manager_id", managerId + "");
});
// One of the directors doesn't have a manager id as he is the CO
employees.selectFirstWhere(sqlInsert -> sqlInsert.getValue("job_id").equals("D"))
.get()
.setValue("manager_id", "NULL");
System.out.println(employees);
}
}
谢谢你的阅读！让我知道你的想法，写在评论部分！
703
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
