CREATE DATABASE company;
CREATE USER 'generatedata'@'localhost' IDENTIFIED BY 'generatedata';
GRANT ALL PRIVILEGES ON company.* TO generatedata@localhost IDENTIFIED BY 'generatedata';
FLUSH PRIVILEGES;
USE company;
CREATE TABLE employees (
no INT NOT NULL,
dob DATE NOT NULL,
first_name VARCHAR(14) NOT NULL,
last_name VARCHAR(16) NOT NULL,
gender ENUM ('M','F') NOT NULL,
hire_date DATE NOT NULL,
PRIMARY KEY (no)
);
CREATE TABLE roles (
id mediumint(8) unsigned NOT NULL auto_increment,
emp_no mediumint,
role varchar(255) default NULL,
PRIMARY KEY (id)
) AUTO_INCREMENT=1;
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1011,'1985-09-02','Chava','Puckett','F','2008-10-12');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1012,'1971-12-03','Christopher','Tillman','M','2006-11-01');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1013,'1975-07-31','Judith','David','F','10-11-20');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1014,'1957-08-03','Neil','Ford','F','08-09-04');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1015,'1977-01-09','Daryl','Wolfe','M','07-09-14');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1016,'1986-03-08','Maryam','Burt','M','09-09-16');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1017,'1980-08-21','Marny','Alvarez','M','11-01-27');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1018,'1965-04-06','Wanda','Fowler','M','08-02-09');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1019,'1950-02-14','Lillian','Hancock','F','05-11-22');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1020,'1965-11-17','Tatyana','Lucas','M','09-02-16');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1021,'1973-03-13','Rooney','Sears','M','05-09-07');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1022,'1974-11-23','Ezekiel','Harding','M','10-07-02');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1023,'1961-01-26','Willa','Swanson','F','12-10-24');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1024,'1948-01-24','Eden','Mcclure','F','09-02-13');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1025,'1951-10-31','Maris','Serrano','F','11-10-04');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1026,'1972-11-11','Kyle','Jordan','M','12-10-22');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1027,'1953-10-06','Jolie','Burton','M','06-06-11');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1028,'1970-11-22','Alyssa','Black','M','11-11-10');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1029,'1952-05-23','Rahim','Noel','F','10-08-13');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1030,'1979-03-07','Roth','May','M','12-06-04');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1031,'1961-08-07','Mira','Harding','M','08-02-04');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1032,'1957-04-07','Helen','Pacheco','F','07-11-17');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1033,'1960-08-11','Evangeline','Mullen','M','13-01-25');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1034,'1971-08-07','Isadora','Walsh','F','09-07-02');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1035,'1979-02-25','Sybil','Mccarty','F','10-06-15');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1036,'1989-08-23','Emma','Cardenas','M','10-01-16');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1037,'1965-03-18','Seth','Monroe','M','06-10-16');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1038,'1954-03-21','Herrod','Noel','M','10-07-07');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1039,'1963-09-06','Devin','Howard','M','11-12-18');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1040,'1989-05-25','Kaden','Ellis','F','10-12-07');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1041,'1966-02-21','Emery','Walters','M','07-05-07');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1042,'1957-11-15','Tyrone','Gill','F','12-07-24');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1043,'1957-06-20','Uriah','Morse','M','12-04-22');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1044,'1976-11-15','Ross','Bradford','M','08-11-14');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1045,'1964-05-04','Elton','Wilkins','F','10-12-21');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1046,'1948-06-07','Lillith','Estes','M','08-04-12');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1047,'1960-04-04','Hayfa','Burch','F','06-09-25');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1048,'1966-02-26','Erin','Lane','M','05-03-01');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1049,'1985-08-23','Ella','Robinson','F','06-03-11');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1050,'1967-04-19','Wayne','Fischer','M','07-05-24');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1051,'1970-11-07','Channing','Mccoy','M','06-05-27');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1052,'1993-07-07','Rhonda','Kirby','M','06-05-19');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1053,'1978-06-04','Brenda','Hodge','M','06-05-09');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1054,'1959-10-27','Barbara','Dixon','M','12-12-05');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1055,'1949-04-28','Zephr','Lindsey','M','09-02-16');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1056,'1977-08-30','Joan','Campbell','M','12-10-14');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1057,'1957-04-14','Breanna','Leblanc','F','07-12-29');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1058,'1983-01-15','Hanna','Shaffer','M','11-04-12');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1059,'1966-01-15','Felicia','Burt','F','11-11-16');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1060,'1963-10-16','Nevada','Blackburn','M','07-08-10');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1061,'1961-12-26','Germane','Duncan','F','09-05-31');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1062,'1974-03-18','Vladimir','Becker','M','09-12-10');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1063,'1965-03-04','Stephen','Clarke','F','09-06-25');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1064,'1968-10-18','Jackson','Edwards','F','11-03-02');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1065,'1959-05-16','Brent','Dunn','M','08-01-26');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1066,'1971-10-21','Quentin','Puckett','F','08-09-15');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1067,'1950-09-26','Mona','Sosa','M','07-11-27');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1068,'1977-10-01','Nola','Dillard','F','06-10-17');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1069,'1956-08-04','Destiny','Maldonado','M','11-05-07');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1070,'1974-07-03','Levi','Dunn','M','11-12-13');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1071,'1987-09-15','Colleen','Mcpherson','M','05-02-05');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1072,'1952-12-11','Igor','Macias','M','11-10-11');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1073,'1984-07-04','Brooke','Hodge','F','06-06-22');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1074,'1969-08-30','Dillon','Stone','F','06-06-07');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1075,'1975-12-29','Marshall','Acevedo','M','11-12-22');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1076,'1965-03-29','Kylan','Richards','F','10-07-21');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1077,'1991-01-23','Luke','Howard','F','09-07-17');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1078,'1951-01-23','Chelsea','Chan','F','07-03-09');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1079,'1978-02-21','Linus','Hobbs','F','12-04-28');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1080,'1977-01-28','Burke','Ashley','F','08-07-09');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1081,'1990-11-23','Pearl','Dennis','M','10-10-10');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1082,'1981-04-27','Lyle','Myers','F','06-03-02');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1083,'1966-05-04','Kennan','Roman','M','07-07-20');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1084,'1947-12-28','Marcia','Bell','M','05-07-29');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1085,'1987-01-25','Aaron','Parrish','M','12-02-18');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1086,'1960-08-05','Madeline','Elliott','M','08-05-13');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1087,'1951-09-03','Zahir','Stevenson','M','12-06-23');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1088,'1973-01-31','Colette','Berger','F','12-01-22');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1089,'1987-11-09','Molly','Nieves','M','12-04-02');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1090,'1978-10-03','Nicole','Salas','M','07-11-08');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1091,'1955-05-08','Zane','Madden','M','09-07-01');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1092,'1949-03-26','Sydnee','Chen','F','09-11-11');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1093,'1969-02-24','Francesca','Patel','F','08-05-11');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1094,'1949-05-17','Clark','Glenn','F','08-09-25');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1095,'1984-12-07','William','Glover','F','09-12-28');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1096,'1967-10-30','Noble','Wiggins','F','08-04-08');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1097,'1977-10-15','Dai','Weeks','F','10-02-01');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1098,'1955-03-13','Ciara','Chavez','F','11-04-05');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1099,'1977-11-29','Francis','Singleton','M','10-12-07');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1100,'1993-03-25','TaShya','Mack','M','11-01-12');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1101,'1973-08-28','Jameson','Lopez','F','11-12-19');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1102,'1981-08-12','Dora','Hinton','F','07-05-26');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1103,'1948-11-13','Pascale','Ray','F','06-11-27');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1104,'1984-03-15','Abigail','Weiss','F','10-07-09');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1105,'1987-06-10','Fletcher','Underwood','M','13-01-15');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1106,'1947-12-24','Geoffrey','Meyers','M','08-04-15');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1107,'1989-01-09','Mara','Smith','M','05-07-18');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1108,'1963-05-07','Rhoda','Beard','M','10-12-02');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1109,'1964-01-22','Ali','Hanson','M','05-01-26');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1110,'1973-01-25','Vaughan','English','F','11-03-04');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1111,'1961-10-13','Marah','Pollard','M','07-10-28');
INSERT INTO employees (no,dob,first_name,last_name,gender,hire_date) VALUES (1112,'1975-08-18','Tatum','Adams','F','11-03-24');
INSERT INTO roles (emp_no,role) VALUES (1011,'Sr. Developer');
INSERT INTO roles (emp_no,role) VALUES (1012,'Office Manager');
INSERT INTO roles (emp_no,role) VALUES (1013,'Secretary');
INSERT INTO roles (emp_no,role) VALUES (1014,'Engineer');
INSERT INTO roles (emp_no,role) VALUES (1015,'CEO');
INSERT INTO roles (emp_no,role) VALUES (1016,'Office Assistant');
INSERT INTO roles (emp_no,role) VALUES (1017,'Sr. Developer');
INSERT INTO roles (emp_no,role) VALUES (1018,'Developer');
INSERT INTO roles (emp_no,role) VALUES (1019,'Office Manager');
INSERT INTO roles (emp_no,role) VALUES (1020,'Office Assistant');
INSERT INTO roles (emp_no,role) VALUES (1021,'Sr. Manager');
INSERT INTO roles (emp_no,role) VALUES (1022,'Sr. Developer');
INSERT INTO roles (emp_no,role) VALUES (1023,'Manager');
INSERT INTO roles (emp_no,role) VALUES (1024,'Secretary');
INSERT INTO roles (emp_no,role) VALUES (1025,'Office Assistant');
INSERT INTO roles (emp_no,role) VALUES (1026,'Intern');
INSERT INTO roles (emp_no,role) VALUES (1027,'Sr. Developer');
INSERT INTO roles (emp_no,role) VALUES (1028,'CEO');
INSERT INTO roles (emp_no,role) VALUES (1029,'CEO');
INSERT INTO roles (emp_no,role) VALUES (1030,'Secretary');
INSERT INTO roles (emp_no,role) VALUES (1031,'Engineer');
INSERT INTO roles (emp_no,role) VALUES (1032,'Office Manager');
INSERT INTO roles (emp_no,role) VALUES (1033,'Secretary');
INSERT INTO roles (emp_no,role) VALUES (1034,'Secretary');
INSERT INTO roles (emp_no,role) VALUES (1035,'Secretary');
INSERT INTO roles (emp_no,role) VALUES (1036,'Engineer');
INSERT INTO roles (emp_no,role) VALUES (1037,'Intern');
INSERT INTO roles (emp_no,role) VALUES (1038,'Office Assistant');
INSERT INTO roles (emp_no,role) VALUES (1039,'Developer');
INSERT INTO roles (emp_no,role) VALUES (1040,'CEO');
INSERT INTO roles (emp_no,role) VALUES (1041,'Office Manager');
INSERT INTO roles (emp_no,role) VALUES (1042,'Intern');
INSERT INTO roles (emp_no,role) VALUES (1043,'Operations Manager');
INSERT INTO roles (emp_no,role) VALUES (1044,'Software Architect');
INSERT INTO roles (emp_no,role) VALUES (1045,'CEO');
INSERT INTO roles (emp_no,role) VALUES (1046,'Software Architect');
INSERT INTO roles (emp_no,role) VALUES (1047,'Manager');
INSERT INTO roles (emp_no,role) VALUES (1048,'Intern');
INSERT INTO roles (emp_no,role) VALUES (1049,'Operations Manager');
INSERT INTO roles (emp_no,role) VALUES (1050,'Sr. Developer');
INSERT INTO roles (emp_no,role) VALUES (1051,'Software Architect');
INSERT INTO roles (emp_no,role) VALUES (1052,'Software Architect');
INSERT INTO roles (emp_no,role) VALUES (1053,'Sr. Manager');
INSERT INTO roles (emp_no,role) VALUES (1054,'Intern');
INSERT INTO roles (emp_no,role) VALUES (1055,'Secretary');
INSERT INTO roles (emp_no,role) VALUES (1056,'Software Architect');
INSERT INTO roles (emp_no,role) VALUES (1057,'Intern');
INSERT INTO roles (emp_no,role) VALUES (1058,'Engineer');
INSERT INTO roles (emp_no,role) VALUES (1059,'Software Architect');
INSERT INTO roles (emp_no,role) VALUES (1060,'Operations Manager');
INSERT INTO roles (emp_no,role) VALUES (1061,'Sr. Developer');
INSERT INTO roles (emp_no,role) VALUES (1062,'CEO');
INSERT INTO roles (emp_no,role) VALUES (1063,'Engineer');
INSERT INTO roles (emp_no,role) VALUES (1064,'CEO');
INSERT INTO roles (emp_no,role) VALUES (1065,'Sr. Manager');
INSERT INTO roles (emp_no,role) VALUES (1066,'Developer');
INSERT INTO roles (emp_no,role) VALUES (1067,'Office Assistant');
INSERT INTO roles (emp_no,role) VALUES (1068,'Office Manager');
INSERT INTO roles (emp_no,role) VALUES (1069,'Office Manager');
INSERT INTO roles (emp_no,role) VALUES (1070,'Office Manager');
INSERT INTO roles (emp_no,role) VALUES (1071,'Sr. Developer');
INSERT INTO roles (emp_no,role) VALUES (1072,'Sr. Manager');
INSERT INTO roles (emp_no,role) VALUES (1073,'Secretary');
INSERT INTO roles (emp_no,role) VALUES (1074,'Office Assistant');
INSERT INTO roles (emp_no,role) VALUES (1075,'Engineer');
INSERT INTO roles (emp_no,role) VALUES (1076,'Intern');
INSERT INTO roles (emp_no,role) VALUES (1077,'Sr. Developer');
INSERT INTO roles (emp_no,role) VALUES (1078,'Sr. Manager');
INSERT INTO roles (emp_no,role) VALUES (1079,'Secretary');
INSERT INTO roles (emp_no,role) VALUES (1080,'Developer');
INSERT INTO roles (emp_no,role) VALUES (1081,'Operations Manager');
INSERT INTO roles (emp_no,role) VALUES (1082,'Intern');
INSERT INTO roles (emp_no,role) VALUES (1083,'Secretary');
INSERT INTO roles (emp_no,role) VALUES (1084,'Office Manager');
INSERT INTO roles (emp_no,role) VALUES (1085,'Intern');
INSERT INTO roles (emp_no,role) VALUES (1086,'Engineer');
INSERT INTO roles (emp_no,role) VALUES (1087,'Operations Manager');
INSERT INTO roles (emp_no,role) VALUES (1088,'Intern');
INSERT INTO roles (emp_no,role) VALUES (1089,'Sr. Developer');
INSERT INTO roles (emp_no,role) VALUES (1090,'Office Assistant');
INSERT INTO roles (emp_no,role) VALUES (1091,'Developer');
INSERT INTO roles (emp_no,role) VALUES (1092,'Sr. Developer');
INSERT INTO roles (emp_no,role) VALUES (1093,'CEO');
INSERT INTO roles (emp_no,role) VALUES (1094,'Office Assistant');
INSERT INTO roles (emp_no,role) VALUES (1095,'Sr. Developer');
INSERT INTO roles (emp_no,role) VALUES (1096,'Operations Manager');
INSERT INTO roles (emp_no,role) VALUES (1097,'Developer');
INSERT INTO roles (emp_no,role) VALUES (1098,'Intern');
INSERT INTO roles (emp_no,role) VALUES (1099,'Engineer');
INSERT INTO roles (emp_no,role) VALUES (1100,'Intern');
INSERT INTO roles (emp_no,role) VALUES (1101,'Developer');
INSERT INTO roles (emp_no,role) VALUES (1102,'Intern');
INSERT INTO roles (emp_no,role) VALUES (1103,'Operations Manager');
INSERT INTO roles (emp_no,role) VALUES (1104,'Office Assistant');
INSERT INTO roles (emp_no,role) VALUES (1105,'Intern');
INSERT INTO roles (emp_no,role) VALUES (1106,'Developer');
INSERT INTO roles (emp_no,role) VALUES (1107,'Secretary');
INSERT INTO roles (emp_no,role) VALUES (1108,'Sr. Manager');
INSERT INTO roles (emp_no,role) VALUES (1109,'Operations Manager');
INSERT INTO roles (emp_no,role) VALUES (1110,'Software Architect');
Database Connector Examples
This document offers examples of applications which use the Database Connector. For full documentation of the connector, access the Database Connector documentation and Database Connector Reference.
Assumptions
This document presents two usage examples of the database connector. This document assumes you are familiar with the database connector as well as with DataSense; to understand the second example, the reader should also be familiar with the DataMapper transformer and Mule batch processing.
Database Used in the Examples
The examples in this page were written for MySQL. They use one database with two tables.
The table below lists the parameters for the database used by these examples.
-
Database:
company
-
Database user:
generatedata@localhost
-
Database user password:
generatedata
-
MySQL listening port: 3306 (MySQL default)
-
Database tables:
employees
androles
Creating the Database, User, and Table
This example does the following things:
-
Creates a database called
company
-
Creates user
generatedata@localhost
with passwordgeneratedata
-
Grants all privileges on database
company
to usergeneratedata@localhost
-
Creates table
employees
on databasecompany
-
Creates table
roles
on the same database -
Populates both tables with sample data
View the script for copy-paste:
Running the script on MySQL Server
-
Save the MySQL script to a convenient location on your hard drive.
-
Open a terminal and run the following command:
mysql -u root -D mysql -p
-
You are prompted for the MySQL root user’s password. After you type the password, you should see a mysql prompt:
mysql>
-
Run the MySQL script with the following command, where
<script>
is the full path and filename to the script, such as/home/joe/create.sample.db.sql
.source <script>;
-
MySQL creates the user, database and tables specified on the script. To verify the tables, run:
use company; show tables;
-
The
show tables
command produces output similar to the following:+-------------------+ | Tables_in_company | +-------------------+ | employees | | roles | +-------------------+ 2 rows in set (0.00 sec)
-
For information about a table, run
describe <table>
. To see the full contents of a table, run the standard SQL statementselect * from <table>
. -
To exit mysql, type
quit;
.
Example 1
This example simply illustrates how to retrieve data from a database with a database connector using a SELECT
operation.
For simplicity, this example accesses a database directly from an HTTP connector, but this is not a recommended practice. This example is meant to illustrate the concept of a simple SELECT operation, but we do not recommend exposing database functionality directly as an API. |
The MySQL database company
contains a table called employees
, with employee information such as first and last names, birth dates, etc.

In the Mule application, an inbound HTTP connector listens for HTTP GET requests with the form: http://<host>:8081/?lastname=<parameter>
. The HTTP connector passes the value of <parameter>
as one of the message properties to a database connector. The database connector is configured to extract this value and use it for the SQL query listed below.
select first_name from employees where last_name = #[message.inboundProperties['lastname']]
As you can see, the MEL expression in the SQL query references the value of the parameter passed to the HTTP connector. So if the HTTP connector receives http://localhost:8081/?lastname=Smith
, the SQL query is select first_name from employees where last_name = Smith
.
The database connector instructs the database server to run the SQL query, retrieves the result of the query, and passes it to the object-to-JSON message processor which converts the result to JSON. Since the HTTP connector is configured as request-response, the result is returned to the originating HTTP client.
Configuring the Database Connector For This Example
In this example, the database connector retrieves data from a MySQL database that resides on host xubuntu listening on port 3306, the default for MySQL. The table below lists the full configuration for the database connector.
Studio Visual Editor
Parameter | Value | Properties Editor Image |
---|---|---|
Display Name |
|
|
Config Reference |
|
|
Operation |
|
|
Query Type |
|
|
Parameterized SQL Statement |
|
Standalone XML
<db:select config-ref="MySQL_Configuration" doc:name="Database">
<db:parameterized-query><![CDATA[select first_name from employees where last_name = '#[message.inboundProperties['lastname']]></db:parameterized-query>
</db:select>
In this example, the database connector references the MySQL_Configuration
global element to obtain connection parameters. MySQL_Configuration
is configured with the parameters listed below.
Studio Visual Editor
General Tab

Parameter | Value |
---|---|
Name |
|
Host |
Not set (defined in URL) |
Port |
Not set (defined in URL) |
User |
Not set (defined in URL) |
Password |
Not set (defined in URL) |
Database |
Not set (defined in URL) |
Configure via Spring bean |
No (unchecked) |
DataSource Reference |
None |
URL |
|
Enable DataSense |
True (default) |
Advanced Tab
In this tab, all settings are at their default values.

Parameter | Value |
---|---|
Use XA Transactions |
|
Login Timeout |
|
Transaction isolation |
|
Max Pool Size: |
|
Min Pool Size: |
|
Acquire Increment: |
|
Prepared Statement Cache Size: |
|
Max Wait Millis |
Standalone XML
<db:mysql-config name="MySQL_Configuration" url="jdbc:mysql://xubuntu:3306/company?user=generatedata&password=generatedata&generateSimpleParameterMetadata=true" doc:name="MySQL Configuration"/>
The target database company
contains the table employees
, a snippet of which is shown below.
+--------+------------+-------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+-------------+-----------+--------+------------+
| 1010 | 1978-10-07 | Ross | Rodgers | M | 2011-10-07 |
| 1011 | 1985-09-02 | Chava | Puckett | F | 2008-10-12 |
| 1012 | 1971-12-03 | Christopher | Tillman | M | 2006-11-01 |
...
When the HTTP connector receives a request, the flow is activated and the database connector performs the following actions:
-
logs in to the target database
-
instructs the MySQL server to run the user-defined query
-
retrieves the result, then passes it to the next message processor as the message payload
The object-to-JSON transformer converts the message payload into JSON, as shown below.
[{"first_name":"<result>"}]
Finally, the HTTP connector returns the string to the originating client. So for example, an HTTP query originated in a Web browser would return the result in the browser window, as shown below.

The above image shows that the query has returned the correct value for the first_name
column of the row where last_name
matches Tillman
.
To activate the flow in this example, you can use a browser, as shown above, or the curl command-line HTTP client, as shown below.
Using curl will print the resulting JSON to the terminal’s standard output. |
Example 2
In this example, a database connector retrieves data from two tables. A DataMapper transformer maps this data to the CSV format. A file connector writes the resulting CSV file to disk, and a logger component logs processing details to the console.
This example uses batch processing, which means that within this Mule app, messages are divided into records and processed asynchronously. Batch processing, in conjunction with streaming enabled on the database connector, can be useful to avoid system overload when dealing with large volumes of data.

The database connector is inside a poll scope which, every ten minutes, requests the database connector to perform its configured operation on the database. The database connector performs a database query which returns 100 rows. Mule feeds this data into the DataMapper transformer inside the Batch Commit
batch scope. DataMapper transforms the input into a CSV file; then, a file connector writes the resulting CSV file to disk. A logger component logs processed records to the console.
This example uses the same MySQL database as the previous example. If you want to recreate the database on your MySQL server, you can download or copy-paste the MySQL script provided in this page. For details, see the see Database Configuration section above. |
Configuring the Database Connector For This Example
In this example, the database connector retrieves data from a MySQL database that resides on host xubuntu listening on port 3306, the default for MySQL. The table below lists the full configuration for the database connector.
Studio Visual Editor
Parameter | Value | Properties Editor Image |
---|---|---|
Display Name |
|
|
Config Reference |
|
|
Operation |
|
|
Query Type |
|
|
Parameterized SQL Statement |
|
Standalone XML
<db:mysql-config name="MySQL_Config" doc:name="MySQL Config" url="jdbc:mysql://xubuntu:3306/company?user=generatedata&password=generatedata&generateSimpleParameterMetadata=true"/>
In this example, the database connector references MySQL_Configuration
to obtain connection parameters. MySQL_Configuration
was created with the parameters listed below.
Studio Visual Editor
General Tab

Parameter | Value |
---|---|
Name |
|
Host |
Not set (defined in URL) |
Port |
Not set (defined in URL) |
User |
Not set (defined in URL) |
Password |
Not set (defined in URL) |
Database |
Not set (defined in URL) |
Configure via Spring bean |
No (unchecked) |
DataSource Reference |
None |
URL |
|
Enable DataSense |
True (default) |
Advanced Tab
In this tab, all settings are at their default values.

Parameter | Value |
---|---|
Use XA Transactions |
|
Login Timeout |
|
Transaction isolation |
|
Max Pool Size: |
|
Min Pool Size: |
|
Acquire Increment: |
|
Prepared Statement Cache Size: |
|
Max Wait Millis |
Standalone XML
<db:mysql-config name="MySQL_Configuration" url="jdbc:mysql://xubuntu:3306/company?user=generatedata&password=generatedata&generateSimpleParameterMetadata=true" doc:name="MySQL Configuration"/>
Database Query and Results
The target database, called "company," contains two tables, "employees" and "roles." A snippet of each is shown below.
+--------+------------+-------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+-------------+-----------+--------+------------+
| 1010 | 1978-10-07 | Ross | Rodgers | M | 2011-10-07 |
| 1011 | 1985-09-02 | Chava | Puckett | F | 2008-10-12 |
| 1012 | 1971-12-03 | Christopher | Tillman | M | 2006-11-01 |
...
+-----+--------+--------------------+
| id | emp_no | role |
+-----+--------+--------------------+
| 1 | 1011 | Sr. Developer |
| 2 | 1012 | Office Manager |
| 3 | 1013 | Secretary |
...
The database connector has been configured to perform the SQL query shown below.
SELECT no, first_name, last_name, role FROM employees INNER JOIN roles ON employees.no = roles.emp_no;
The query produces 100 rows of results with data from both tables. The first three rows are shown below.
+------+-------------+-----------+--------------------+
| no | first_name | last_name | role |
+------+-------------+-----------+--------------------+
| 1011 | Chava | Puckett | Sr. Developer |
| 1012 | Christopher | Tillman | Office Manager |
| 1013 | Judith | David | Secretary |
...
This resulting data will be transformed to CSV by the DataMapper transformer. The next section explains how to configure the DataMapper transformer while avoiding having to manually map the input/output fields.
Configuring DataMapper with DataSense
In order to configure DataMapper to map the data received from the database query into a CSV file, you can take advantage of DataSense, which allows you to avoid manually configuring the input and output fields. To do so, you must configure the database connector before you configure your DataMapper transformer. This way, the DataMapper transformer will be able to tell the database connector to retrieve the input fields from the database, and will then automatically create the mapping based on those input fields.
To create the batch job in this example, perform these steps:
-
Drag a batch scope from the Palette onto the canvas.
-
Drag a database connector to the Input section of the batch scope.
-
Configure the database connector as necessary to retrieve the desired data from the database. This entails all connection parameters as well as the operation, etc. Test the connection to see that it works. When you leave the database connector configuration (i.e. when you click anywhere outside the database connector on the Studio window) the database connector will automatically retrieve metadata using DataSense.
-
Drag a batch commit scope to the Process Records section of the batch scope.
-
Drag a DataMapper transformer into the batch commit scope.
-
Drag a file connector into the batch commit scope.
-
Finally, drag a logger component in the On Complete section of the batch scope.
At this point, you have built your batch job and are ready to configure the DataMapper transformer. Double-click the DataMapper transformer to open its configuration editor, which should look similar to the image below.

To instruct DataMapper to get the metadata from the database connector, first click Change Type. The DataMapper editor changes to allow you to select which connector to receive data from, as shown in the image below.

Click the drop-down menu next to Connector, then select MySQL_Configuration
. This is the global element referenced by the database connector, which contains the parameters to connect to the MySQL database.

When you select MySQL_Configuration
, DataMapper retrieves input metadata from the database connector. This operation takes a few seconds. When the operation completes, click the drop-down menu next to Operation, then select select
. Next, click the drop-down menu next to Object, then select List<Map>
.

In the output mapping pane, click the drop-down menu next to Type, then select CSV
. Next, click the checkbox next to User Defined.
At this point, the DataMapper view should look like the image below.

In the output mapping pane, click Generate default, then Edit Fields. DataMapper displays the Define the CSV editor, which should look like the image below.

As you can see, DataMapper has used DataSense to gather the relevant metadata from the database, i.e. the column names and data types, and has taken them as the input fields. At this point you can modify, add, or remove fields as you wish. For this example, we can leave the fields as they are.
Click OK to dismiss the Define the CSV window, then click Create mapping at the bottom of the DataMapper view. DataMapper automatically maps the input fields to their corresponding output CSV fields.
The image below shows the finished DataMapper transformer configuration.

If you wish to test this Mule application, you need to perform two additional tasks:
-
Configure the file connector to save the CSV file to your desired location on your local disk.
-
Configure the logger component to output the message displayed below, at log level
WARN
.Total Records exported: #[message.payload.getLoadedRecords()], Failed Records: #[message.payload.getFailedRecords()], Processing time: #[message.payload.getElapsedTimeInMillis()]
When you run the application, DataMapper will output the results to a file connector, which in turn will write them to a file on your local disk. (In this example, the output file is roles.csv
, which will be written to the root folder of the Mule application.)
When you run the application, the database connector will automatically connect to the database and retrieve the data. If you do not stop the application, it will repeat this operation every ten minutes. The output CSV data will be written to the file that you specified in the file connector. Below are the first lines from the resulting CSV file.
"Chava","Puckett","1011","Sr. Developer"
"Christopher","Tillman","1012","Office Manager"
"Judith","David","1013","Secretary"
The console displays the messages output by the logger component.
INFO 2014-02-28 14:11:20,805 [pool-15-thread-1] com.mulesoft.module.batch.engine.DefaultBatchEngine: Created instance 40d3cb27-a0c5-11e3-a3c7-f1f67a172e10 for batch job db-appBatch1
INFO 2014-02-28 14:11:20,808 [pool-15-thread-1] com.mulesoft.module.batch.engine.DefaultBatchEngine: Starting input phase
INFO 2014-02-28 14:11:20,808 [pool-15-thread-1] com.mulesoft.module.batch.engine.DefaultBatchEngine: Input phase completed
INFO 2014-02-28 14:11:20,822 [pool-15-thread-1] com.mulesoft.module.batch.engine.queue.BatchQueueLoader: Starting loading phase for instance 40d3cb27-a0c5-11e3-a3c7-f1f67a172e10 of job db-appBatch1
INFO 2014-02-28 14:11:20,847 [pool-15-thread-1] com.mulesoft.module.batch.engine.queue.BatchQueueLoader: Finished loading phase for instance 40d3cb27-a0c5-11e3-a3c7-f1f67a172e10 of job db-appBatch1. 100 records were loaded
INFO 2014-02-28 14:11:20,851 [pool-15-thread-1] com.mulesoft.module.batch.engine.DefaultBatchEngine: Started execution of instance 40d3cb27-a0c5-11e3-a3c7-f1f67a172e10 of job db-appBatch1
INFO 2014-02-28 14:11:22,007 [batch-job-db-appBatch1-work-manager.01] com.mulesoft.module.batch.DefaultBatchStep: Step Batch_Step finished processing all records for instance 40d3cb27-a0c5-11e3-a3c7-f1f67a172e10 of job db-appBatch1
INFO 2014-02-28 14:11:28,584 [batch-job-db-appBatch1-work-manager.01] org.mule.lifecycle.AbstractLifecycleManager: Initialising: 'connector.file.mule.default.dispatcher.763473616'. Object is: FileMessageDispatcher
INFO 2014-02-28 14:11:28,586 [batch-job-db-appBatch1-work-manager.01] org.mule.lifecycle.AbstractLifecycleManager: Starting: 'connector.file.mule.default.dispatcher.763473616'. Object is: FileMessageDispatcher
INFO 2014-02-28 14:11:28,592 [batch-job-db-appBatch1-work-manager.01] org.mule.transport.file.FileConnector: Writing file to: /Users/pedro/mule.installations/Dolomites-17feb14/workspace/test2/roles.csv
INFO 2014-02-28 14:11:28,691 [[test2].Batch Dispatcher thread] com.mulesoft.module.batch.engine.DefaultBatchEngine: Finished execution for instance 40d3cb27-a0c5-11e3-a3c7-f1f67a172e10 of job db-appBatch1
INFO 2014-02-28 14:11:28,692 [[test2].Batch Dispatcher thread] com.mulesoft.module.batch.engine.DefaultBatchEngine: Starting execution of onComplete phase for instance 40d3cb27-a0c5-11e3-a3c7-f1f67a172e10 of job db-appBatch1
WARN 2014-02-28 14:11:28,702 [[test2].Batch Dispatcher thread] org.mule.api.processor.LoggerMessageProcessor: Total Records exported: 100, Failed Records: 0, Processing time: 7844
INFO 2014-02-28 14:11:28,703 [[test2].Batch Dispatcher thread] com.mulesoft.module.batch.engine.DefaultBatchEngine: Finished execution of onComplete phase for instance 40d3cb27-a0c5-11e3-a3c7-f1f67a172e10 of job db-appBatch1
See Also
-
Learn more about DataSense and how you can use it to retrieve metadata on remote applications.
-
Delve deeper into batch processing to gain a deeper understanding of how Mule handles messages in batch jobs.