All Connectors

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 and roles

Tables

Table 1. Table employees. Primary key: no (employee number).
Column Type

no

Integer

dob

Date

first_name

Varchar

last_name

Varchar

gender

Enum(F, M)

hire_date

Date
Table 2. Table roles. Primary key: id (role ID).
Column Type

id

Integer

emp_no

Integer

role

Varchar

Creating the Database, User, and Table

This example does the following things:

  • Creates a database called company

  • Creates user generatedata@localhost with password generatedata

  • Grants all privileges on database company to user generatedata@localhost

  • Creates table employees on database company

  • Creates table roles on the same database

  • Populates both tables with sample data

View the script for copy-paste:

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');

Running the script on MySQL Server

  1. Save the MySQL script to a convenient location on your hard drive.

  2. Open a terminal and run the following command:

    mysql -u root -D mysql -p

  3. You are prompted for the MySQL root user’s password. After you type the password, you should see a mysql prompt:

    mysql>

  4. 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>;

  5. MySQL creates the user, database and tables specified on the script. To verify the tables, run:

    use company;
show tables;

  6. The show tables command produces output similar to the following:

    +-------------------+
| Tables_in_company |
+-------------------+
| employees         |
| roles             |
+-------------------+
2 rows in set (0.00 sec)

  7. For information about a table, run describe <table>. To see the full contents of a table, run the standard SQL statement select * from <table>.

  8. 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.

DBtestFlow

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

Database

db ex 1

Config Reference

MySQL_Configuration

Operation

Select

Query Type

Parameterized

Parameterized SQL Statement

select first_name from employees where last_name = #[message.inboundProperties['lastname']]

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

global_db_connector_example
Parameter Value

Name

MySQL_Configuration

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

 
jdbc:mysql://xubuntu:3306/ company?user=generatedata&password=
generatedata

Enable DataSense

True (default)

Advanced Tab

In this tab, all settings are at their default values.

global_db_conn_Advanced
Parameter Value

Use XA Transactions

Login Timeout

Transaction isolation

NONE

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&amp;password=generatedata&amp;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.

ex.1.v2.browser_results

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.

curl http://<host>:8081/?lastname=<parameter>

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.

ex.2-batchjob.flow

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

Database

example_2_db_gentab

Config Reference

MySQL_Configuration

Operation

Select

Query Type

Parameterized

Parameterized SQL Statement

SELECT no, first_name, last_name, role FROM employees INNER JOIN roles ON employees.no = roles.emp_no;

Standalone XML

<db:mysql-config name="MySQL_Config"     doc:name="MySQL Config" url="jdbc:mysql://xubuntu:3306/company?user=generatedata&amp;password=generatedata&amp;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

global_db_connector_example
Parameter Value

Name

MySQL_Configuration

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

 
jdbc:mysql://xubuntu:3306/company?user=generatedata&password=generatedata&generateSimpleParameterMetadata=true

Enable DataSense

True (default)

Advanced Tab

In this tab, all settings are at their default values.

global_db_conn_Advanced
Parameter Value

Use XA Transactions

Login Timeout

Transaction isolation

NONE

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&amp;password=generatedata&amp;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.

Table "employees"
+--------+------------+-------------+-----------+--------+------------+
| 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 |
...
Table "roles"
+-----+--------+--------------------+
| 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:

  1. Drag a batch scope from the Palette onto the canvas.

  2. Drag a database connector to the Input section of the batch scope.

  3. 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.

  4. Drag a batch commit scope to the Process Records section of the batch scope.

  5. Drag a DataMapper transformer into the batch commit scope.

  6. Drag a file connector into the batch commit scope.

  7. 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.

ex2.datamapper.raw

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.

ex2.datamapper.conn.select

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.

ex2.datamapper.conn.select2

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>.

ex2.datamapper.all.selected.ops

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.

ex2.datamapper.CSV.selected

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.

ex2.Define.the.CSV

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.

ex2.finished.datamapper

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.