$ winpty mysql --version
mysql  Ver 14.14 Distrib 5.7.18, for Win64 (x86_64)
$ winpty mysql -V
mysql  Ver 14.14 Distrib 5.7.18, for Win64 (x86_64)
$ winpty mysql -u root -p *******************
mysql> exit
# OR
mysql> quit


>mysql --version
mysql  Ver 14.14 Distrib 5.7.18, for Win64 (x86_64)
>mysql -V
mysql  Ver 14.14 Distrib 5.7.18, for Win64 (x86_64)
> mysql -u root -p
mysql> exit
# OR
mysql> quit


# Version
SELECT VERSION();
SELECT @@version;
SHOW VARIABLES LIKE "%version%";
SHOW GLOBAL VARIABLES LIKE '%version%';

# Databases
SHOW DATABASES;

# Change Database
USE `sys`;

# Current Database
SELECT DATABASE();
SELECT DATABASE() FROM DUAL;

# Tables in Current Database
SHOW TABLES;

# Columns
SELECT `COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA`='sys'
AND `TABLE_NAME`='host_summary';
# OR
SELECT `COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA`='sampledb'
AND `TABLE_NAME`='users';

#List of Stored Procedures/Functions Mysql Command Line
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;

###############################################################################################################################################

#MySQL JOIN

DROP DATABASE IF EXISTS JoinExample;

CREATE DATABASE JoinExample;

USE JoinExample;
DROP TABLE IF EXISTS Table1;


CREATE TABLE Table1
(ID INT, Value VARCHAR(10));

INSERT INTO Table1 (ID, Value)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Third'
UNION ALL
SELECT 4, 'Fourth'
UNION ALL
SELECT 5, 'Fifth';

DROP TABLE IF EXISTS Table2;

CREATE TABLE Table2
(ID INT, Value VARCHAR(10));

INSERT INTO Table2 (ID, Value)
SELECT 1, 'I'
UNION ALL
SELECT 2, 'II'
UNION ALL
SELECT 3, 'III'
UNION ALL
SELECT 6, 'VI'
UNION ALL
SELECT 7, 'VII'
UNION ALL
SELECT 8, 'VIII';


SELECT * FROM Table1;
SELECT * FROM Table2;


/* (INNER) JOIN */
SELECT t1.*, t2.* FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID;


/* LEFT (OUTER) JOIN */
SELECT t1.*, t2.* FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID;


/* RIGHT (OUTER) JOIN */
SELECT t1.*, t2.* FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID;


/*
#NO FULL (OUTER) JOIN IN MYSQL
SELECT t1.*, t2.* FROM Table1 t1
FULL JOIN Table2 t2 ON t1.ID = t2.ID;
*/


/* Emulate FULL (OUTER) JOIN --NOTE: NULLS LAST */
SELECT * FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.id = t2.id
UNION
SELECT * FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.id = t2.id;


/* CROSS JOIN --NOTE: Pivot t2.ID (Differs From SQL Server, PostgreSQL) */
SELECT t1.*, t2.* FROM Table1 t1
CROSS JOIN Table2 t2;

###############################################################################################################################################

# MySQL nth Highest

DROP DATABASE IF EXISTS employeedb;

CREATE DATABASE employeedb;

USE employeedb;

DROP TABLE IF EXISTS Employee;

CREATE TABLE Employee
(ID INT, Name NVARCHAR(50), Salary numeric(15, 2));

INSERT INTO Employee
VALUES
(1, 'A', 10000), #4th
(2, 'B', 8000),  #5th
(3, 'C', 8000),
(4, 'D', 6000),  #6th
(5, 'E', 6000),
(6, 'F', 6000),
(7, 'G', 5000),  #7th
(8, 'H', 5000),
(9, 'I', 5000),
(10, 'J', 5000),
(11, 'K', 4000),  #8th
(12, 'L', 4000),
(13, 'M', 3000),  #9th
(14, 'N', 3000),
(15, 'O', 1000),  #10th
(16, 'P', 14000), #2nd
(17, 'Q', 14000),
(18, 'R', 12000), #3rd
(19, 'S', 12000),
(20, 'T', 16000), #1st
(21, 'U', 16000),
(22, 'V', 16000),
(23, 'W', 14000),
(24, 'X', 12000),
(25, 'Y', 12000),
(26, 'Z', 10000);

SELECT * FROM Employee;

# 16000 #1st
# 14000 #2nd
# 12000 #3rd
# 10000 #4th
#  8000 #5th
#  6000 #6th
#  5000 #7th
#  4000 #8th
#  3000 #9th
#  1000 #10th

# nth Highest # 5th Highest # 8000
SELECT Salary FROM
(
SELECT DISTINCT Salary FROM Employee
ORDER BY Salary DESC LIMIT 5
)
A ORDER BY Salary LIMIT 1;
# Alternatively
SELECT *
FROM Employee Emp1
WHERE (5 - 1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary) LIMIT 1;

# 2nd Highest # 14000
SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee);

# Alternatively
SELECT MAX(Salary) from Employee
WHERE Salary <> (select MAX(Salary) from Employee);

###############################################################################################################################################

DROP DATABASE IF EXISTS spdb;
DELIMITER $$
CREATE DATABASE spdb;$$
DELIMITER ;

USE spdb;

DROP TABLE IF EXISTS `customer`;
DELIMITER $$
CREATE TABLE `customer` (
  `customer_id` int unsigned NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(50) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `date_of_birth` date NOT NULL,
  `income` double NOT NULL,
  `credit_limit` decimal(10,2) DEFAULT NULL,
  `create_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`customer_id`)
);$$
DELIMITER ;

SELECT * FROM customer;

USE spdb;

INSERT INTO
`customer`
(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
VALUES
('Bill Gates', 'billgates@microsoft.com', '1955-10-28', 97.9, 9.79);

INSERT INTO
`customer`
(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
VALUES
('Paul Allen', 'paulallen@microsoft.com', '1953-01-21', 20.2, 2.02);

INSERT INTO
`customer`
(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
VALUES
('Larry Page', 'larrypage@abc.xyz', '1973-3-26', 55.2, 5.52);

INSERT INTO
`customer`
(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
VALUES
('Sergey Brin', 'sergeybrin@abc.xyz', '1973-08-21', 55.7, 5.57);

INSERT INTO
`customer`
(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
VALUES
('Tim Cook', 'timcook@apple.com', '1960-11-01', 0.78, 0.07);

INSERT INTO
`customer`
(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
VALUES
('Steve Wozniak', 'stevewozniak@apple.com', '1950-08-11', 0.1, 0.01);

INSERT INTO
`customer`
(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
VALUES
('Jeff Bezos', 'jeffbezos@amazon.com', '1964-01-12', 165.0, 16.50);

INSERT INTO
`customer`
(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
VALUES
('Mark Zuckerberg', 'markzuckerberg@facebook.com', '1984-05-14', 67.1, 6.71);

INSERT INTO
`customer`
(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
VALUES
('Pierre Omidyar', 'pierreomidyar@ebay.com', '1967-06-21', 10.5, 1.05);

INSERT INTO
`customer`
(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
VALUES
('Elon Musk', 'elonmusk@tesla.com', '1971-06-28', 19.8, 1.98);

USE spdb;

SELECT * FROM customer;


USE spdb;

DROP TABLE IF EXISTS `rental`;
DELIMITER $$

CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `customer_id` int unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `status` varchar(50) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE
);$$
DELIMITER ;

SELECT * FROM rental;

USE spdb;
INSERT INTO `rental`
(`rental_date`, `customer_id`, `return_date`, `status`)
VALUES
('2018-01-01', 1, '2018-01-22', 'Shipped'),
('2018-01-02', 1, '2018-01-23', 'Shipped'),
('2018-01-03', 1, '2018-01-24', 'Canceled'),
('2018-01-04', 1, '2018-01-25', 'Resolved'),
('2018-01-05', 1, '2018-01-26', 'Disputed'),
('2018-01-06', 1, '2018-01-27', 'Shipped'),
('2018-01-07', 1, '2018-01-28', 'Shipped'),
('2018-01-08', 1, '2018-01-29', 'Resolved'),
('2018-01-09', 1, '2018-01-30', 'Shipped'),
('2018-01-10', 1, '2018-01-31', 'Shipped'),

('2018-02-01', 2, '2018-02-19', 'Shipped'),
('2018-02-02', 2, '2018-02-20', 'Shipped'),
('2018-02-03', 2, '2018-02-21', 'Canceled'),
('2018-02-04', 2, '2018-02-22', 'Resolved'),
('2018-02-05', 2, '2018-02-23', 'Canceled'),
('2018-02-06', 2, '2018-02-24', 'Resolved'),
('2018-02-07', 2, '2018-02-25', 'Disputed'),
('2018-02-08', 2, '2018-02-26', 'Resolved'),
('2018-02-09', 2, '2018-02-27', 'Resolved'),
('2018-02-10', 2, '2018-02-28', 'Shipped'),

('2018-03-01', 3, '2018-03-22', 'Shipped'),
('2018-03-02', 3, '2018-03-23', 'Shipped'),
('2018-03-03', 3, '2018-03-24', 'Canceled'),
('2018-03-04', 3, '2018-03-25', 'Resolved'),
('2018-03-05', 3, '2018-03-26', 'Disputed'),
('2018-03-06', 3, '2018-03-27', 'Resolved'),
('2018-03-07', 3, '2018-03-28', 'Disputed'),
('2018-03-08', 3, '2018-03-29', 'Resolved'),
('2018-03-09', 3, '2018-03-30', 'Shipped'),
('2018-03-10', 3, '2018-03-31', 'Shipped'),

('2018-04-01', 4, '2018-04-21', 'Shipped'),
('2018-04-02', 4, '2018-04-22', 'Canceled'),
('2018-04-03', 4, '2018-04-23', 'Shipped'),
('2018-04-04', 4, '2018-04-24', 'Resolved'),
('2018-04-05', 4, '2018-04-25', 'Disputed'),
('2018-04-06', 4, '2018-04-26', 'Resolved'),
('2018-04-07', 4, '2018-04-27', 'Disputed'),
('2018-04-08', 4, '2018-04-28', 'Resolved'),
('2018-04-09', 4, '2018-04-29', 'Shipped'),
('2018-04-10', 4, '2018-04-30', 'Shipped'),


('2018-05-01', 5, '2018-05-22', 'Shipped'),
('2018-05-02', 5, '2018-05-23', 'Shipped'),
('2018-05-03', 5, '2018-05-24', 'Shipped'),
('2018-05-04', 5, '2018-05-25', 'Resolved'),
('2018-05-05', 5, '2018-05-26', 'Disputed'),
('2018-05-06', 5, '2018-05-27', 'Canceled'),
('2018-05-07', 5, '2018-05-28', 'Shipped'),
('2018-05-08', 5, '2018-05-29', 'Resolved'),
('2018-05-09', 5, '2018-05-30', 'Shipped'),
('2018-05-10', 5, '2018-05-31', 'Shipped'),

('2018-06-01', 6, '2018-06-21', 'Canceled'),
('2018-06-02', 6, '2018-06-22', 'Shipped'),
('2018-06-03', 6, '2018-06-23', 'Canceled'),
('2018-06-04', 6, '2018-06-24', 'Resolved'),
('2018-06-05', 6, '2018-06-25', 'Disputed'),
('2018-06-06', 6, '2018-06-26', 'Resolved'),
('2018-06-07', 6, '2018-06-27', 'Disputed'),
('2018-06-08', 6, '2018-06-28', 'Resolved'),
('2018-06-09', 6, '2018-06-29', 'Resolved'),
('2018-06-10', 6, '2018-06-30', 'Shipped'),

('2018-07-01', 7, '2018-07-22', 'Shipped'),
('2018-07-02', 7, '2018-07-23', 'Shipped'),
('2018-07-03', 7, '2018-07-24', 'Canceled'),
('2018-07-04', 7, '2018-07-25', 'Resolved'),
('2018-07-05', 7, '2018-07-26', 'Disputed'),
('2018-07-06', 7, '2018-07-27', 'Shipped'),
('2018-07-07', 7, '2018-07-28', 'Shipped'),
('2018-07-08', 7, '2018-07-29', 'Shipped'),
('2018-07-09', 7, '2018-07-30', 'Resolved'),
('2018-07-10', 7, '2018-07-31', 'Shipped'),

('2018-08-01', 8, '2018-08-22', 'Shipped'),
('2018-08-02', 8, '2018-08-23', 'Shipped'),
('2018-08-03', 8, '2018-08-24', 'Disputed'),
('2018-08-04', 8, '2018-08-25', 'Resolved'),
('2018-08-05', 8, '2018-08-26', 'Canceled'),
('2018-08-06', 8, '2018-08-27', 'Shipped'),
('2018-08-07', 8, '2018-08-28', 'Shipped'),
('2018-08-08', 8, '2018-08-29', 'Shipped'),
('2018-08-09', 8, '2018-08-30', 'Resolved'),
('2018-08-10', 8, '2018-08-31', 'Shipped'),

('2018-09-01', 9, '2018-09-21', 'Canceled'),
('2018-09-02', 9, '2018-09-22', 'Resolved'),
('2018-09-03', 9, '2018-09-23', 'Canceled'),
('2018-09-04', 9, '2018-09-24', 'Shipped'),
('2018-09-05', 9, '2018-09-25', 'Disputed'),
('2018-09-06', 9, '2018-09-26', 'Resolved'),
('2018-09-07', 9, '2018-09-27', 'Disputed'),
('2018-09-08', 9, '2018-09-28', 'Resolved'),
('2018-09-09', 9, '2018-09-29', 'Resolved'),
('2018-09-10', 9, '2018-09-30', 'Shipped'),

('2018-10-01', 10, '2018-10-22', 'Shipped'),
('2018-10-02', 10, '2018-10-23', 'Shipped'),
('2018-10-03', 10, '2018-10-24', 'Disputed'),
('2018-10-04', 10, '2018-10-25', 'Resolved'),
('2018-10-05', 10, '2018-10-26', 'Canceled'),
('2018-10-06', 10, '2018-10-27', 'Resolved'),
('2018-10-07', 10, '2018-10-28', 'Shipped'),
('2018-10-08', 10, '2018-10-29', 'Shipped'),
('2018-10-09', 10, '2018-10-30', 'Shipped'),
('2018-10-10', 10, '2018-10-31', 'Shipped');

SELECT * FROM rental;
SELECT count(*) FROM rental;

USE spdb;
SELECT * FROM rental;
SELECT count(*) FROM rental;


USE spdb;
DROP TABLE IF EXISTS `actor`;
DELIMITER $$

CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ;$$
DELIMITER ;

SELECT * FROM `actor`;

USE spdb;
INSERT INTO `actor`
(`first_name`, `last_name`)
VALUES
('PENELOPE', 'GUINESS'),
('NICK', 'WAHLBERG'),
('ED', 'CHASE'),
('JENNIFER', 'DAVIS'),
('JOHNNY', 'LOLLOBRIGIDA'),
('BETTE', 'NICHOLSON'),
('GRACE', 'MOSTEL'),
('MATTHEW', 'JOHANSSON'),
('JOE', 'SWANK'),
('CHRISTIAN', 'GABLE'),
('ZERO', 'CAGE'),
('KARL', 'BERRY'),
('UMA', 'WOOD'),
('VIVIEN', 'BERGEN'),
('CUBA', 'OLIVIER'),
('FRED', 'COSTNER'),
('HELEN', 'VOIGHT'),
('DAN', 'TORN'),
('BOB', 'FAWCETT'),
('LUCILLE', 'TRACY'),
('KIRSTEN', 'PALTROW'),
('ELVIS', 'MARX'),
('SANDRA', 'KILMER'),
('CAMERON', 'STREEP'),
('KEVIN', 'BLOOM'),
('RIP', 'CRAWFORD'),
('JULIA', 'MCQUEEN'),
('WOODY', 'HOFFMAN'),
('ALEC', 'WAYNE'),
('SANDRA', 'PECK'),
('SISSY', 'SOBIESKI'),
('TIM', 'HACKMAN'),
('MILLA', 'PECK'),
('AUDREY', 'OLIVIER'),
('JUDY', 'DEAN'),
('BURT', 'DUKAKIS'),
('VAL', 'BOLGER'),
('TOM', 'MCKELLEN'),
('GOLDIE', 'BRODY'),
('JOHNNY', 'CAGE'),
('JODIE', 'DEGENERES'),
('TOM', 'MIRANDA'),
('KIRK', 'JOVOVICH'),
('NICK', 'STALLONE'),
('REESE', 'KILMER'),
('PARKER', 'GOLDBERG'),
('JULIA', 'BARRYMORE'),
('FRANCES', 'DAY-LEWIS'),
('ANNE', 'CRONYN'),
('NATALIE', 'HOPKINS'),
('GARY', 'PHOENIX'),
('CARMEN', 'HUNT'),
('MENA', 'TEMPLE'),
('PENELOPE', 'PINKETT'),
('FAY', 'KILMER'),
('DAN', 'HARRIS'),
('JUDE', 'CRUISE'),
('CHRISTIAN', 'AKROYD'),
('DUSTIN', 'TAUTOU'),
('HENRY', 'BERRY'),
('CHRISTIAN', 'NEESON'),
('JAYNE', 'NEESON'),
('CAMERON', 'WRAY'),
('RAY', 'JOHANSSON'),
('ANGELA', 'HUDSON'),
('MARY', 'TANDY'),
('JESSICA', 'BAILEY'),
('RIP', 'WINSLET'),
('KENNETH', 'PALTROW'),
('MICHELLE', 'MCCONAUGHEY'),
('ADAM', 'GRANT'),
('SEAN', 'WILLIAMS'),
('GARY', 'PENN'),
('MILLA', 'KEITEL'),
('BURT', 'POSEY'),
('ANGELINA', 'ASTAIRE'),
('CARY', 'MCCONAUGHEY'),
('GROUCHO', 'SINATRA'),
('MAE', 'HOFFMAN'),
('RALPH', 'CRUZ'),
('SCARLETT', 'DAMON'),
('WOODY', 'JOLIE'),
('BEN', 'WILLIS'),
('JAMES', 'PITT'),
('MINNIE', 'ZELLWEGER'),
('GREG', 'CHAPLIN'),
('SPENCER', 'PECK'),
('KENNETH', 'PESCI'),
('CHARLIZE', 'DENCH'),
('SEAN', 'GUINESS'),
('CHRISTOPHER', 'BERRY'),
('KIRSTEN', 'AKROYD'),
('ELLEN', 'PRESLEY'),
('KENNETH', 'TORN'),
('DARYL', 'WAHLBERG'),
('GENE', 'WILLIS'),
('MEG', 'HAWKE'),
('CHRIS', 'BRIDGES'),
('JIM', 'MOSTEL'),
('SPENCER', 'DEPP'),
('SUSAN', 'DAVIS'),
('WALTER', 'TORN'),
('MATTHEW', 'LEIGH'),
('PENELOPE', 'CRONYN'),
('SIDNEY', 'CROWE'),
('GROUCHO', 'DUNST'),
('GINA', 'DEGENERES'),
('WARREN', 'NOLTE'),
('SYLVESTER', 'DERN'),
('SUSAN', 'DAVIS'),
('CAMERON', 'ZELLWEGER'),
('RUSSELL', 'BACALL'),
('MORGAN', 'HOPKINS'),
('MORGAN', 'MCDORMAND'),
('HARRISON', 'BALE'),
('DAN', 'STREEP'),
('RENEE', 'TRACY'),
('CUBA', 'ALLEN'),
('WARREN', 'JACKMAN'),
('PENELOPE', 'MONROE'),
('LIZA', 'BERGMAN'),
('SALMA', 'NOLTE'),
('JULIANNE', 'DENCH'),
('SCARLETT', 'BENING'),
('ALBERT', 'NOLTE'),
('FRANCES', 'TOMEI'),
('KEVIN', 'GARLAND'),
('CATE', 'MCQUEEN'),
('DARYL', 'CRAWFORD'),
('GRETA', 'KEITEL'),
('JANE', 'JACKMAN'),
('ADAM', 'HOPPER'),
('RICHARD', 'PENN'),
('GENE', 'HOPKINS'),
('RITA', 'REYNOLDS'),
('ED', 'MANSFIELD'),
('MORGAN', 'WILLIAMS'),
('LUCILLE', 'DEE'),
('EWAN', 'GOODING'),
('WHOOPI', 'HURT'),
('CATE', 'HARRIS'),
('JADA', 'RYDER'),
('RIVER', 'DEAN'),
('ANGELA', 'WITHERSPOON'),
('KIM', 'ALLEN'),
('ALBERT', 'JOHANSSON'),
('FAY', 'WINSLET'),
('EMILY', 'DEE'),
('RUSSELL', 'TEMPLE'),
('JAYNE', 'NOLTE'),
('GEOFFREY', 'HESTON'),
('BEN', 'HARRIS'),
('MINNIE', 'KILMER'),
('MERYL', 'GIBSON'),
('IAN', 'TANDY'),
('FAY', 'WOOD'),
('GRETA', 'MALDEN'),
('VIVIEN', 'BASINGER'),
('LAURA', 'BRODY'),
('CHRIS', 'DEPP'),
('HARVEY', 'HOPE'),
('OPRAH', 'KILMER'),
('CHRISTOPHER', 'WEST'),
('HUMPHREY', 'WILLIS'),
('AL', 'GARLAND'),
('NICK', 'DEGENERES'),
('LAURENCE', 'BULLOCK'),
('WILL', 'WILSON'),
('KENNETH', 'HOFFMAN'),
('MENA', 'HOPPER'),
('OLYMPIA', 'PFEIFFER'),
('GROUCHO', 'WILLIAMS'),
('ALAN', 'DREYFUSS'),
('MICHAEL', 'BENING'),
('WILLIAM', 'HACKMAN'),
('JON', 'CHASE'),
('GENE', 'MCKELLEN'),
('LISA', 'MONROE'),
('ED', 'GUINESS'),
('JEFF', 'SILVERSTONE'),
('MATTHEW', 'CARREY'),
('DEBBIE', 'AKROYD'),
('RUSSELL', 'CLOSE'),
('HUMPHREY', 'GARLAND'),
('MICHAEL', 'BOLGER'),
('JULIA', 'ZELLWEGER'),
('RENEE', 'BALL'),
('ROCK', 'DUKAKIS'),
('CUBA', 'BIRCH'),
('AUDREY', 'BAILEY'),
('GREGORY', 'GOODING'),
('JOHN', 'SUVARI'),
('BURT', 'TEMPLE'),
('MERYL', 'ALLEN'),
('JAYNE', 'SILVERSTONE'),
('BELA', 'WALKEN'),
('REESE', 'WEST'),
('MARY', 'KEITEL'),
('JULIA', 'FAWCETT'),
('THORA', 'TEMPLE');

SELECT * FROM `actor`;

USE spdb;
SELECT * FROM `actor`;


# PROCEDURE 1
USE spdb;
DROP PROCEDURE IF EXISTS sp;
DELIMITER $$
CREATE PROCEDURE sp
(
 IN cust_id INT,
 OUT shipped INT,
 OUT canceled INT,
 OUT resolved INT,
 OUT disputed INT
)
BEGIN
 -- shipped
 SELECT
  count(*) INTO shipped
 FROM
  rental
 WHERE
  customer_id = cust_id
 AND
  status = 'Shipped';

 -- canceled
 SELECT
  count(*) INTO canceled
 FROM
  rental
 WHERE
  customer_id = cust_id
 AND
  status = 'Canceled';

 -- resolved
 SELECT
  count(*) INTO resolved
 FROM
  rental
 WHERE
  customer_id = cust_id
 AND
  status = 'Resolved';

 -- disputed
 SELECT
  count(*) INTO disputed
 FROM
  rental
 WHERE
  customer_id = cust_id
 AND
  status = 'Disputed';
END;$$
DELIMITER ;

USE spdb;
CALL sp(6, @shipped, @canceled, @resolved, @disputed);
SELECT @shipped, @canceled, @resolved, @disputed;


# PROCEDURE 2
USE spdb;
DROP PROCEDURE IF EXISTS spin;
DELIMITER $$
CREATE PROCEDURE spin
(
 IN n INT
)
BEGIN
 # SELECT * FROM customer LIMIT n;
 SELECT `customer_id`, `customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`, `create_date`, `last_update` FROM customer LIMIT n;
END;$$
DELIMITER ;

USE spdb;
CALL spin(6);


# PROCEDURE 3
USE spdb;
DROP PROCEDURE IF EXISTS spinout;
DELIMITER $$
CREATE PROCEDURE spinout
(
 IN in_customer_id INT, OUT out_count INT
)
BEGIN
 SELECT COUNT(*) INTO out_count  FROM customer WHERE customer_id > in_customer_id;
END; $$
DELIMITER ;

USE spdb;
CALL spinout(4, @out_count);
SELECT @out_count;


# PROCEDURE 4
USE spdb;
DROP PROCEDURE IF EXISTS spinputoutput;
DELIMITER $$
CREATE PROCEDURE spinputoutput
(
 in  p_customer_id int(11),
 inout p_customerLevel  varchar(10) # Note # out p_customerLevel  varchar(10)
)
BEGIN
 DECLARE creditlimit double;
 SELECT
  credit_limit INTO creditlimit
 FROM
  customer
 WHERE
  customer_id = p_customer_id;
 IF creditlimit > 50 THEN
  SET p_customerLevel = 'PLATINUM';
 ELSEIF (creditlimit <= 50 AND creditlimit >= 10) THEN
  SET p_customerLevel = 'GOLD';
 ELSEIF creditlimit < 10 THEN
  SET p_customerLevel = 'SILVER';
 END IF;
END; $$
DELIMITER ;

USE spdb;
CALL spinputoutput(3, @level);
SELECT @level AS level;


# DROP DATABASE IF EXISTS spdb;
# DELIMITER $$
# CREATE DATABASE spdb;$$
# DELIMITER ;


# PROCEDURE 5
USE spdb;
DROP PROCEDURE IF EXISTS multiply;
DELIMITER $$
CREATE PROCEDURE multiply
(
 IN pFac1 INT,
 IN pFac2 INT,
 OUT pProd INT
)
BEGIN
  SET pProd := pFac1 * pFac2;
END;$$
DELIMITER ;

USE spdb;
CALL multiply(5, 5, @Result);
SELECT @Result;


# PROCEDURE 6
USE spdb;
DROP PROCEDURE IF EXISTS concat;
DELIMITER $$
CREATE PROCEDURE concat
(
 IN pStr1 VARCHAR(20),
 IN pStr2 VARCHAR(20),
 OUT pConCat VARCHAR(100)
)
BEGIN
  SET pConCat := CONCAT(pStr1, pStr2);
END;$$
DELIMITER ;

USE spdb;
CALL concat('My', 'SQL', @Result);
SELECT @Result;


# PROCEDURE 7
USE spdb;
DROP PROCEDURE IF EXISTS prepend;
DELIMITER $$
CREATE PROCEDURE prepend
(
 IN inParam VARCHAR(255),
 INOUT inOutParam INT
)
BEGIN
 DECLARE z INT;
 SET z = inOutParam + 1;
 SET inOutParam = z;
 SELECT inParam;
 SELECT CONCAT('zyxw', inParam);
END;$$
DELIMITER ;

USE spdb;
CALL prepend('abcdefg', @inOutParam);

/****************************************************************************************************************************************************

// MySQLConnection.java

import java.io.InputStream;
import java.io.IOException;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

class DataAccessObject {
    private Connection getConnection() throws SQLException {
        Connection conn = null;
        try  {
             InputStream input = new FileInputStream("config.properties");
             Properties connectionProps = new Properties();
             connectionProps.load(input);
             String url = connectionProps.getProperty("url"); //
             String user = connectionProps.getProperty("user"); //
             String password = connectionProps.getProperty("password"); //
             // conn = DriverManager.getConnection(url); //
             conn = DriverManager.getConnection(url, user, password); //
        } catch (IOException e) {
             e.printStackTrace();
        }
        return conn;
    }

    public void testConnection() {
        try (Connection conn = getConnection();) {
            if (conn != null) {
                System.out.println("Connected to MySQL Server successfully.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

public class MySQLConnection {
    public static void main(String args[]) {
        DataAccessObject dao = new DataAccessObject();
        dao.testConnection();
    }
}

// config.properties

url=jdbc:mysql://localhost:3306/spdb?useSSL=false
user=root
password=*******************

// $ javac MySQLConnection.java
// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLConnection

****************************************************************************************************************************************************/

/****************************************************************************************************************************************************

// MySQLCRUD.java

import java.io.InputStream;
import java.io.IOException;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.Properties;
import java.util.List;
import java.util.ArrayList;

class Actor {
    private String firstName;
    private String lastName;

    public Actor() { }

    public Actor(String firstName, String lastName) {
        this.firstName = firstName;
        this.lastName = lastName;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
}

class DataAccessObject {
    public Connection getConnection() throws SQLException {
        Connection conn = null;
        try  {
             InputStream input = new FileInputStream("config.properties");
             Properties connectionProps = new Properties();
             connectionProps.load(input);
             String url = connectionProps.getProperty("url"); //
             String user = connectionProps.getProperty("user"); //
             String password = connectionProps.getProperty("password"); //
             // conn = DriverManager.getConnection(url); //
             conn = DriverManager.getConnection(url, user, password); //
        } catch (IOException e) {
             e.printStackTrace();
        }
        return conn;
    }

    public void testConnection() {
        try (Connection conn = getConnection();) {
            if (conn != null) {
                System.out.println("Connected to MySQL Server successfully.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

class CRUD {
    public int dropTableIfExists() {
        DataAccessObject dao = new DataAccessObject();
        // dao.testConnection();

        StringBuilder dropQuery = new StringBuilder();
        dropQuery.append("DROP TABLE IF EXISTS `spdb`.`actor`;");

        int result = 0;

        try (Connection conn = dao.getConnection();
            Statement stmt = conn.createStatement()) {
                System.out.println("Dropping table in given database...");
                result = stmt.executeUpdate(dropQuery.toString());
                System.out.println("Dropped table in given database.");
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return result;
    }

    public int createTableLike() {
        DataAccessObject dao = new DataAccessObject();
        // dao.testConnection();

        StringBuilder createQuery = new StringBuilder();
        createQuery.append("CREATE TABLE IF NOT EXISTS `spdb`.`actor` LIKE `sakila`.`actor`;");

        int result = 0;

        try (Connection conn = dao.getConnection();
            Statement stmt = conn.createStatement()) {
                System.out.println("Creating table in given database...");
                result = stmt.executeUpdate(createQuery.toString());
                System.out.println("Created table in given database.");
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return result;
    }

    public int insertTable() {
        DataAccessObject dao = new DataAccessObject();
        // dao.testConnection();

        StringBuilder insertQuery = new StringBuilder();
        insertQuery.append("INSERT `spdb`.`actor` SELECT * FROM `sakila`.`actor`;");

        int result = 0;

        try (Connection conn = dao.getConnection();
            Statement stmt = conn.createStatement()) {
                System.out.println("Inserting table in given database...");
                result = stmt.executeUpdate(insertQuery.toString());
                System.out.println("Inserting table in given database.");
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return result;
    }

    public void selectActors(String query) {
        DataAccessObject dao = new DataAccessObject();
        // dao.testConnection();
        try (Connection conn = dao.getConnection();
   	    PreparedStatement pstmt = conn.prepareStatement(query)) {
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                /*
                System.out.println(String.format("%-10d%-20s%-20s%tF",
                        rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                         rs.getTimestamp(4)));
                */
                int actorId = rs.getInt("actor_id");
                String fName = rs.getString("first_name");
                String lName = rs.getString("last_name");
                java.sql.Timestamp lastUpdate = rs.getTimestamp("last_update");
                System.out.println(String.format("%-10d%-20s%-20s%tF",
                        actorId,
                        fName,
                        lName,
                        lastUpdate));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public void selectActorsLike(String query, String text) {
        DataAccessObject dao = new DataAccessObject();
        // dao.testConnection();
        try (Connection conn = dao.getConnection();
   	    PreparedStatement pstmt = conn.prepareStatement(query)) {
            pstmt.setString(1, '%' + text + '%');
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                /*
                System.out.println(String.format("%-10d%-20s%-20s%tF",
                        rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                         rs.getTimestamp(4)));
                */
                int actorId = rs.getInt("actor_id");
                String fName = rs.getString("first_name");
                String lName = rs.getString("last_name");
                java.sql.Timestamp lastUpdate = rs.getTimestamp("last_update");
                System.out.println(String.format("%-10d%-20s%-20s%tF",
                        actorId,
                        fName,
                        lName,
                        lastUpdate));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public long insertActor(Actor actor) {
        DataAccessObject dao = new DataAccessObject();
        // dao.testConnection();

        StringBuilder insertQuery = new StringBuilder();
        insertQuery.append("INSERT INTO actor(first_name, last_name) " + "VALUES(?, ?)");

        long id = 0;

        try (Connection conn = dao.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(insertQuery.toString(),
                Statement.RETURN_GENERATED_KEYS)) {

            pstmt.setString(1, actor.getFirstName());
            pstmt.setString(2, actor.getLastName());

            int affectedRows = pstmt.executeUpdate();
            // check the affected rows
            if (affectedRows > 0) {
                // get the id back
                try (ResultSet rs = pstmt.getGeneratedKeys()) {
                    if (rs.next()) {
                        id = rs.getLong(1);
                    }
                } catch (SQLException ex) {
                    System.out.println(ex.getMessage());
                }
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return id;
    }

    public void insertActors(List<Actor> list) {
        DataAccessObject dao = new DataAccessObject();
        // dao.testConnection();

        StringBuilder insertQuery = new StringBuilder();
        insertQuery.append("INSERT INTO actor(first_name, last_name) " + "VALUES(?, ?)");

        try (Connection conn = dao.getConnection();
                PreparedStatement statement = conn.prepareStatement(insertQuery.toString());) {
            int count = 0;

            for (Actor actor : list) {
                statement.setString(1, actor.getFirstName());
                statement.setString(2, actor.getLastName());

                statement.addBatch();
                count++;
                // execute every 100 rows or less
                if (count % 100 == 0 || count == list.size()) {
                    statement.executeBatch();
                }
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

    public int updateActor(int id, Actor act) {
        DataAccessObject dao = new DataAccessObject();
        // dao.testConnection();

        StringBuilder updateQuery = new StringBuilder();
        updateQuery.append("UPDATE actor " + "SET first_name = ? " + ", last_name = ? " + "WHERE actor_id = ?");

        int affectedrows = 0;

        try (Connection conn = dao.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(updateQuery.toString())) {

            pstmt.setString(1, act.getFirstName());
            pstmt.setString(2, act.getLastName());
            pstmt.setInt(3, id);

            affectedrows = pstmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return affectedrows;
    }

    public int deleteActor(int id) {
        DataAccessObject dao = new DataAccessObject();
        // dao.testConnection();

        StringBuilder deleteQuery = new StringBuilder();
        deleteQuery.append("DELETE FROM actor WHERE actor_id = ?");

        int affectedrows = 0;

        try (Connection conn = dao.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(deleteQuery.toString())) {

            pstmt.setInt(1, id);

            affectedrows = pstmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return affectedrows;
    }
}

public class MySQLCRUD {
    public static void main(String[] args) {
        CRUD crd = new CRUD();
        crd.dropTableIfExists();
        crd.createTableLike();
        crd.insertTable();

        StringBuilder selectQuery = new StringBuilder();
        // selectQuery.append("Select * FROM actor;");
        selectQuery.append("SELECT actor_id, first_name, last_name, last_update FROM actor;");
        System.out.println("Select");
        crd.selectActors(selectQuery.toString());

        System.out.println("Select actor like 'AL'");
        StringBuilder selectLikeQuery = new StringBuilder();
        // selectLikeQuery.append("Select * FROM actor WHERE first_name LIKE (?);");
        selectLikeQuery.append("SELECT actor_id, first_name, last_name, last_update FROM actor WHERE first_name LIKE (?);");
        String text = "AL"; // case-sensitive
        crd.selectActorsLike(selectLikeQuery.toString(), text);

        Actor act = new Actor("Foo", "Bar");
        System.out.println("Insert");
        long id = crd.insertActor(act);
        System.out.printf("Inserted actor %s %s with id %d%n", act.getFirstName(), act.getLastName(), id);
        System.out.println("Select");
        crd.selectActors(selectQuery.toString());

        List<Actor> actorList = new ArrayList<Actor>();
        actorList.add(new Actor("Baz", "Qux"));
        actorList.add(new Actor("Quux", "Corge"));
        System.out.println("Batch Insert");
        crd.insertActors(actorList);
        actorList.forEach(actr -> System.out.printf("Batch Inserted actor %s %s%n", actr.getFirstName(), actr.getLastName()));
        System.out.println("Select");
        crd.selectActors(selectQuery.toString());

        int ident = 202;
        Actor actr = new Actor("Grault", "Garply");
        int rowsAffected = crd.updateActor(ident, actr);
        // System.out.println("Rows affected:" + rowsAffected);
        if (rowsAffected > 0) {
             System.out.println("Update");
            System.out.printf("Updated actor Id: %d with First Name: %s and Last Name: %s%n", ident, actr.getFirstName(), actr.getLastName());
        }
        System.out.println("Select");
        crd.selectActors(selectQuery.toString());

        int identity = 203;
        int recordsAffected = crd.deleteActor(identity);
        // System.out.println("Records affected:" + recordsAffected);
        if (recordsAffected > 0) {
            System.out.println("Delete");
            System.out.printf("Deleted actor Id: %d%n", identity);
        }

        System.out.println("Select");
        crd.selectActors(selectQuery.toString());
    }
}

// config.properties

url=jdbc:mysql://localhost:3306/spdb?useSSL=false
user=root
password=*******************

// $ javac MySQLCRUD.java
// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLCRUD

****************************************************************************************************************************************************/

/****************************************************************************************************************************************************

// MySQLSelect.java

import java.io.InputStream;
import java.io.IOException;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Properties;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
import java.util.Map.Entry;
import java.lang.StringBuilder;

class DataAccessObject {
    private Connection getConnection() throws SQLException {
        Connection conn = null;
        try  {
             InputStream input = new FileInputStream("config.properties");
             Properties connectionProps = new Properties();
             connectionProps.load(input);
             String url = connectionProps.getProperty("url"); //
             String user = connectionProps.getProperty("user"); //
             String password = connectionProps.getProperty("password"); //
             // conn = DriverManager.getConnection(url); //
             conn = DriverManager.getConnection(url, user, password); //
        } catch (IOException e) {
             e.printStackTrace();
        }
        return conn;
    }

    public void testConnection() {
        try (Connection conn = getConnection();) {
            if (conn != null) {
                System.out.println("Connected to MySQL Server successfully.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Map<Integer, Map<String, String>> getData(String query) {
        Map<Integer, Map<String, String>> rows = new HashMap<Integer, Map<String, String>>();
        ResultSet rs;
        try (Connection conn = getConnection();
            Statement stmt = conn.createStatement()) {
            if (conn != null) {
                System.out.println("Connected to MySQL Server successfully.");
            }
            rs = stmt.executeQuery(query);
            int rowCount = 0;
            ResultSetMetaData meta = rs.getMetaData();
            while(rs.next()) {
                int columnCount = meta.getColumnCount();
                Map<String, String> columns = new HashMap<String, String>();
                for (int i = 1; i <= columnCount; i++) {
                    String type = meta.getColumnClassName(i);
                    String key = meta.getColumnName(i);
                    String value = rs.getString(key);
                    columns.put(key, value);
                }
                /*
                for(Entry<String, String> columnsEntry : columns.entrySet()) {
        	    System.out.println(columnsEntry.getKey() + ": " + columnsEntry.getValue());
                }
                System.out.println("Number of Columns(s) = " + columns.size());
                */
                rowCount++;
                rows.put(rowCount, columns);
            }
            /*
            for(Entry<Integer, Map<String, String>> rowsEntry : rows.entrySet()) {
                for(Entry<String, String> columnsEntry : rowsEntry.getValue().entrySet()) {
        	    System.out.println(columnsEntry.getKey() + ": " + columnsEntry.getValue());
                }
            }
            System.out.println("Number of Row(s) = " + rows.size());
            */
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }
}

public class MySQLSelect {
    public static void main(String args[]) {
        DataAccessObject dao = new DataAccessObject();
        // dao.testConnection();
        StringBuilder selectQuery = new StringBuilder();
        // selectQuery.append("Select * FROM customer;");
        selectQuery.append("SELECT customer_id, customer_name, email, date_of_birth, income, credit_limit, create_date, last_update FROM customer;");
        Map<Integer, Map<String, String>> rows = dao.getData(selectQuery.toString());
        if (rows != null) {
            int rowCount = rows.size();
            System.out.println("Number of Row(s) = " + rowCount);
            if (rowCount > 0) {
                for(Entry<Integer, Map<String, String>> rowsEntry : rows.entrySet()) {
                    // int columnsCount = 0;
                    for(Entry<String, String> columnsEntry : rowsEntry.getValue().entrySet()) {
                        System.out.println(columnsEntry.getKey() + ": " + columnsEntry.getValue());
                        /*
                        columnsCount++;
                        System.out.print(columnsEntry.getValue());
                        if (columnsCount < rowsEntry.getValue().entrySet().size()) {
                            System.out.print(" - " );
                        }
                        */
                    }
                    System.out.println();
                }
            }
        }
    }
}

// config.properties

url=jdbc:mysql://localhost:3306/spdb?useSSL=false
user=root
password=*******************

// $ javac MySQLSelect.java
// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLSelect

****************************************************************************************************************************************************/

/****************************************************************************************************************************************************

// MySQLInOut.java

import java.io.InputStream;
import java.io.IOException;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Properties;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
import java.util.Map.Entry;
import java.lang.StringBuilder;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;

class DataAccessObject {
    public Connection getConnection() throws SQLException {
        Connection conn = null;
        try  {
             InputStream input = new FileInputStream("config.properties");
             Properties connectionProps = new Properties();
             connectionProps.load(input);
             String url = connectionProps.getProperty("url"); //
             String user = connectionProps.getProperty("user"); //
             String password = connectionProps.getProperty("password"); //
             // conn = DriverManager.getConnection(url); //
             conn = DriverManager.getConnection(url, user, password); //
        } catch (IOException e) {
             e.printStackTrace();
        }
        return conn;
    }

    public void testConnection() {
        try (Connection conn = getConnection();) {
            System.out.println("Connected to MySQL Server successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

class MySQLProcedure
{
    public <T> CallableStatement setParams(CallableStatement statement, Map<Integer, T> params) throws SQLException {
        for(Entry<Integer, T> paramsEntry : params.entrySet()) {
            int key = paramsEntry.getKey();
            statement.setObject(key, paramsEntry.getValue());
        }
        return statement;
    }

    public <T> Map<Integer, Object> getOutParams(String query, Map<Integer, T> params, int[] resultRowNumbers) {
        Map<Integer, Object> outParams = new HashMap<Integer, Object>();
        ResultSet rs;
        DataAccessObject dao = new DataAccessObject();
        try (Connection conn = dao.getConnection();) {
            CallableStatement statement = conn.prepareCall(query);
            System.out.println("Connected to MySQL Server successfully.");
            statement = setParams(statement, params);
            // int resultCount = statement.executeUpdate();
            boolean hadResults = statement.execute();
            while (hadResults) {
        	rs = statement.getResultSet();
                while (rs.next()) {
                     for(int i = 0; i < resultRowNumbers.length; i++) {
                         outParams.put(i, rs.getObject(resultRowNumbers[i]));
                     }
                }
		hadResults = statement.getMoreResults();
    	    }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return outParams;
    }
}

public class MySQLInOut {
    public static void main(String args[]) {
        MySQLProcedure mysqlProc = new MySQLProcedure();
        StringBuilder query = new StringBuilder();
        query.append("{ CALL prepend(?, ?) }");
        Map<Integer, Object> params = new HashMap<Integer, Object>();
        params.put(1, "abcdefg");
        params.put(2, 0);
        int[] resultRowNumbers = new int[] {1};
        Map<Integer, Object> outParams = mysqlProc.getOutParams(query.toString(), params, resultRowNumbers);
        if(outParams != null) {
            for(Entry<Integer, Object> outParamsEntry : outParams.entrySet()) {
                System.out.println(outParamsEntry.getValue());
            }
        }
    }
}

// config.properties

url=jdbc:mysql://localhost:3306/spdb?useSSL=false
user=root
password=*******************

// $ javac MySQLInOut.java
// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLInOut

****************************************************************************************************************************************************/

/****************************************************************************************************************************************************

// MySQLProcs.java

import java.io.InputStream;
import java.io.IOException;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Properties;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
import java.util.Map.Entry;
import java.lang.StringBuilder;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;

class DataAccessObject {
    public Connection getConnection() throws SQLException {
        Connection conn = null;
        try  {
             InputStream input = new FileInputStream("config.properties");
             Properties connectionProps = new Properties();
             connectionProps.load(input);
             String url = connectionProps.getProperty("url"); //
             String user = connectionProps.getProperty("user"); //
             String password = connectionProps.getProperty("password"); //
             // conn = DriverManager.getConnection(url); //
             conn = DriverManager.getConnection(url, user, password); //
        } catch (IOException e) {
             e.printStackTrace();
        }
        return conn;
    }

    public void testConnection() {
        try (Connection conn = getConnection();) {
            System.out.println("Connected to MySQL Server successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

class MySQLProcedure
{
    public <T> CallableStatement setParams(CallableStatement statement, Map<Integer, T> params) throws SQLException {
        for(Entry<Integer, T> paramsEntry : params.entrySet()) {
            int key = paramsEntry.getKey();
            statement.setObject(key, paramsEntry.getValue());
        }
        return statement;
    }

    public <T> Map<Integer, Object> getOutParams(String query, Map<Integer, T> params, int[] resultRowNumbers) {
        Map<Integer, Object> outParams = new HashMap<Integer, Object>();
        DataAccessObject dao = new DataAccessObject();
        try (Connection conn = dao.getConnection();) {
            CallableStatement statement = conn.prepareCall(query);
            System.out.println("Connected to MySQL Server successfully.");
            statement = setParams(statement, params);
            // int result = statement.executeUpdate();
            boolean hadResults = statement.execute();
            for(int i = 0; i < resultRowNumbers.length; i++) {
                outParams.put(i, statement.getObject(resultRowNumbers[i]));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return outParams;
    }
}

public class MySQLProcs {
    public static void main(String args[]) {
        MySQLProcedure mysqlProc = new MySQLProcedure();
        StringBuilder query = new StringBuilder();
        query.append("{ CALL multiply(?, ?, ?) }");
        Map<Integer, Object> params = new HashMap<Integer, Object>();
        params.put(1, 5);
        params.put(2, 5);
        params.put(3, "@Result");
        int[] resultRowNumbers = new int[] {3};
        Map<Integer, Object> outParams = mysqlProc.getOutParams(query.toString(), params, resultRowNumbers);
        if(outParams != null) {
            for(Entry<Integer, Object> outParamsEntry : outParams.entrySet()) {
                System.out.println(outParamsEntry.getValue());
            }
        }

        query = new StringBuilder();
        query.append("{ CALL concat(?, ?, ?) }");
        params = new HashMap<Integer, Object>();
        params.put(1, "My");
        params.put(2, "SQL");
        params.put(3, "@Result");
        resultRowNumbers = new int[] {3};
        outParams = mysqlProc.getOutParams(query.toString(), params, resultRowNumbers);
        if(outParams != null) {
            for(Entry<Integer, Object> outParamsEntry : outParams.entrySet()) {
                System.out.println(outParamsEntry.getValue());
            }
        }
    }
}

// config.properties

url=jdbc:mysql://localhost:3306/spdb?useSSL=false
user=root
password=*******************

// $ javac MySQLProcs.java
// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLProcs

****************************************************************************************************************************************************/

/****************************************************************************************************************************************************

// MySQLSp.java

import java.io.InputStream;
import java.io.IOException;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Properties;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
import java.util.Map.Entry;
import java.lang.StringBuilder;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;

class DataAccessObject {
    public Connection getConnection() throws SQLException {
        Connection conn = null;
        try  {
             InputStream input = new FileInputStream("config.properties");
             Properties connectionProps = new Properties();
             connectionProps.load(input);
             String url = connectionProps.getProperty("url"); //
             String user = connectionProps.getProperty("user"); //
             String password = connectionProps.getProperty("password"); //
             // conn = DriverManager.getConnection(url); //
             conn = DriverManager.getConnection(url, user, password); //
        } catch (IOException e) {
             e.printStackTrace();
        }
        return conn;
    }

    public void testConnection() {
        try (Connection conn = getConnection();) {
            System.out.println("Connected to MySQL Server successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

class MySQLProcedure
{
    public <T> CallableStatement setParams(CallableStatement statement, Map<Integer, T> params) throws SQLException {
        for(Entry<Integer, T> paramsEntry : params.entrySet()) {
            int key = paramsEntry.getKey();
            statement.setObject(key, paramsEntry.getValue());
        }
        return statement;
    }

    public <T> Map<Integer, Object> getOutParams(String query, Map<Integer, T> params, int[] resultRowNumbers) {
        Map<Integer, Object> outParams = new HashMap<Integer, Object>();
        DataAccessObject dao = new DataAccessObject();
         try (Connection conn = dao.getConnection();) {
            CallableStatement statement = conn.prepareCall(query);
            System.out.println("Connected to MySQL Server successfully.");
            statement = setParams(statement, params);
             // int result = statement.executeUpdate();
            boolean hadResults = statement.execute();
            for(int i = 0; i < resultRowNumbers.length; i++) {
               outParams.put(i, statement.getObject(resultRowNumbers[i]));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return outParams;
    }
}

public class MySQLSp {
    public static void main(String args[]) {
        MySQLProcedure mysqlProc = new MySQLProcedure();
        StringBuilder query = new StringBuilder();
        query.append("{ CALL sp(?, ?, ?, ?, ?) }");
        Map<Integer, Object> params = new HashMap<Integer, Object>();
        params.put(1, 6);
        params.put(2, "@shipped");
        params.put(3, "@canceled");
        params.put(4, "@resolved");
        params.put(5, "@disputed");
        int[] resultRowNumbers = new int[] {2, 3, 4, 5};
        Map<Integer, Object> outParams = mysqlProc.getOutParams(query.toString(), params, resultRowNumbers);
        if(outParams != null) {
            for(Entry<Integer, Object> outParamsEntry : outParams.entrySet()) {
                System.out.println(outParamsEntry.getValue());
            }
        }
    }
}

// config.properties

url=jdbc:mysql://localhost:3306/spdb?useSSL=false
user=root
password=*******************

// $ javac MySQLSp.java
// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLSp

****************************************************************************************************************************************************/

/****************************************************************************************************************************************************

// MySQLSpIn.java

import java.io.InputStream;
import java.io.IOException;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Properties;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
import java.util.Map.Entry;
import java.lang.StringBuilder;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;

class DataAccessObject {
    public Connection getConnection() throws SQLException {
        Connection conn = null;
        try  {
             InputStream input = new FileInputStream("config.properties");
             Properties connectionProps = new Properties();
             connectionProps.load(input);
             String url = connectionProps.getProperty("url"); //
             String user = connectionProps.getProperty("user"); //
             String password = connectionProps.getProperty("password"); //
             // conn = DriverManager.getConnection(url); //
             conn = DriverManager.getConnection(url, user, password); //
        } catch (IOException e) {
             e.printStackTrace();
        }
        return conn;
    }

    public void testConnection() {
        try (Connection conn = getConnection();) {
            System.out.println("Connected to MySQL Server successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

class MySQLProcedure
{
    public <T> CallableStatement setParams(CallableStatement statement, Map<Integer, T> params) throws SQLException {
        for(Entry<Integer, T> paramsEntry : params.entrySet()) {
            int key = paramsEntry.getKey();
            statement.setObject(key, paramsEntry.getValue());
        }
        return statement;
    }

    public <T> Map<Integer, Map<String, String>> getData(String query, Map<Integer, T> params) {
        Map<Integer, Map<String, String>> rows = new HashMap<Integer, Map<String, String>>();
        ResultSet rs;
        DataAccessObject dao = new DataAccessObject();
        try (Connection conn = dao.getConnection();) {
            CallableStatement statement = conn.prepareCall(query);
            System.out.println("Connected to MySQL Server successfully.");
            statement = setParams(statement, params);
	    rs = statement.executeQuery();
            int rowCount = 0;
            ResultSetMetaData meta = rs.getMetaData();
            while(rs.next()) {
                int columnCount = meta.getColumnCount();
                Map<String, String> columns = new HashMap<String, String>();
                for (int i = 1; i <= columnCount; i++) {
                    String type = meta.getColumnClassName(i);
                    String key = meta.getColumnName(i);
                    String value = rs.getString(key);
                    columns.put(key, value);
                }
                /*
                for(Entry<String, String> columnsEntry : columns.entrySet()) {
        	    System.out.println(columnsEntry.getKey() + ": " + columnsEntry.getValue());
                }
                System.out.println("Number of Columns(s) = " + columns.size());
                */
                rowCount++;
                rows.put(rowCount, columns);
            }
            /*
            for(Entry<Integer, Map<String, String>> rowsEntry : rows.entrySet()) {
                for(Entry<String, String> columnsEntry : rowsEntry.getValue().entrySet()) {
        	    System.out.println(columnsEntry.getKey() + ": " + columnsEntry.getValue());
                }
            }
            System.out.println("Number of Row(s) = " + rows.size());
            */
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }
}

public class MySQLSpIn {
    public static void main(String args[]) {
        MySQLProcedure mysqlProc = new MySQLProcedure();;
        StringBuilder query = new StringBuilder();
        query.append("{ CALL spin(?) }");
        Map<Integer, Object> params = new HashMap<Integer, Object>();
        params.put(1, 6);
        Map<Integer, Map<String, String>> rows = mysqlProc.getData(query.toString(), params);
        if (rows != null) {
            int rowCount = rows.size();
            System.out.println("Number of Row(s) = " + rowCount);
            if (rowCount > 0) {
                for(Entry<Integer, Map<String, String>> rowsEntry : rows.entrySet()) {
                    // int columnsCount = 0;
                    for(Entry<String, String> columnsEntry : rowsEntry.getValue().entrySet()) {
                        System.out.println(columnsEntry.getKey() + ": " + columnsEntry.getValue());
                        /*
                        columnsCount++;
                        System.out.print(columnsEntry.getValue());
                        if (columnsCount < rowsEntry.getValue().entrySet().size()) {
                            System.out.print(" - " );
                        }
                        */
                    }
                    System.out.println();
                }
            }
        }
    }
}

// config.properties

url=jdbc:mysql://localhost:3306/spdb?useSSL=false
user=root
password=*******************

// $ javac MySQLSpIn.java
// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLSpIn

****************************************************************************************************************************************************/

/****************************************************************************************************************************************************

// MySQLSpInOut.java

import java.io.InputStream;
import java.io.IOException;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Properties;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
import java.util.Map.Entry;
import java.lang.StringBuilder;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;

class DataAccessObject {
    public Connection getConnection() throws SQLException {
        Connection conn = null;
        try  {
             InputStream input = new FileInputStream("config.properties");
             Properties connectionProps = new Properties();
             connectionProps.load(input);
             String url = connectionProps.getProperty("url"); //
             String user = connectionProps.getProperty("user"); //
             String password = connectionProps.getProperty("password"); //
             // conn = DriverManager.getConnection(url); //
             conn = DriverManager.getConnection(url, user, password); //
        } catch (IOException e) {
             e.printStackTrace();
        }
        return conn;
    }

    public void testConnection() {
        try (Connection conn = getConnection();) {
            System.out.println("Connected to MySQL Server successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

class MySQLProcedure
{
    public <T> CallableStatement setParams(CallableStatement statement, Map<Integer, T> params) throws SQLException {
        for(Entry<Integer, T> paramsEntry : params.entrySet()) {
            int key = paramsEntry.getKey();
            statement.setObject(key, paramsEntry.getValue());
        }
        return statement;
    }

    public <T> Map<Integer, Object> getOutParams(String query, Map<Integer, T> params, int[] resultRowNumbers) {
        Map<Integer, Object> outParams = new HashMap<Integer, Object>();
        DataAccessObject dao = new DataAccessObject();
        try (Connection conn = dao.getConnection();) {
            CallableStatement statement = conn.prepareCall(query);
            System.out.println("Connected to MySQL Server successfully.");
            statement = setParams(statement, params);
            // int result = statement.executeUpdate();
            boolean hadResults = statement.execute();
            for(int i = 0; i < resultRowNumbers.length; i++) {
                outParams.put(i, statement.getObject(resultRowNumbers[i]));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return outParams;
    }
}

public class MySQLSpInOut {
    public static void main(String args[]) {
        MySQLProcedure mysqlProc = new MySQLProcedure();
        StringBuilder query = new StringBuilder();
        query.append("{ CALL spinout(?, ?) }");
        Map<Integer, Object> params = new HashMap<Integer, Object>();
        params.put(1, 4);
        params.put(2, "@out_count");
        int[] resultRowNumbers = new int[] {2};
        Map<Integer, Object> outParams = mysqlProc.getOutParams(query.toString(), params, resultRowNumbers);
        if(outParams != null) {
            for(Entry<Integer, Object> outParamsEntry : outParams.entrySet()) {
                System.out.println(outParamsEntry.getValue());
            }
        }
    }
}

// config.properties

url=jdbc:mysql://localhost:3306/spdb?useSSL=false
user=root
password=*******************

// $ javac MySQLSpInOut.java
// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLSpInOut

****************************************************************************************************************************************************/

/****************************************************************************************************************************************************

// MySQLSpInputOutput.java

import java.io.InputStream;
import java.io.IOException;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Properties;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
import java.util.Map.Entry;
import java.lang.StringBuilder;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;

class DataAccessObject {
    public Connection getConnection() throws SQLException {
        Connection conn = null;
        try  {
             InputStream input = new FileInputStream("config.properties");
             Properties connectionProps = new Properties();
             connectionProps.load(input);
             String url = connectionProps.getProperty("url"); //
             String user = connectionProps.getProperty("user"); //
             String password = connectionProps.getProperty("password"); //
             // conn = DriverManager.getConnection(url); //
             conn = DriverManager.getConnection(url, user, password); //
        } catch (IOException e) {
             e.printStackTrace();
        }
        return conn;
    }

    public void testConnection() {
        try (Connection conn = getConnection();) {
            System.out.println("Connected to MySQL Server successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

class MySQLProcedure
{
    public <T> CallableStatement setParams(CallableStatement statement, Map<Integer, T> params) throws SQLException {
        for(Entry<Integer, T> paramsEntry : params.entrySet()) {
            int key = paramsEntry.getKey();
            statement.setObject(key, paramsEntry.getValue());
        }
        return statement;
    }

    public <T> Map<Integer, Object> getOutParams(String query, Map<Integer, T> params, int[] resultRowNumbers) {
        Map<Integer, Object> outParams = new HashMap<Integer, Object>();
        DataAccessObject dao = new DataAccessObject();
        try (Connection conn = dao.getConnection();) {
            CallableStatement statement = conn.prepareCall(query);
            System.out.println("Connected to MySQL Server successfully.");
            statement = setParams(statement, params);
            // int result = statement.executeUpdate();
            boolean hadResults = statement.execute();
            for(int i = 0; i < resultRowNumbers.length; i++) {
                outParams.put(i, statement.getObject(resultRowNumbers[i]));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return outParams;
    }
}

public class MySQLSpInputOutput {
    public static void main(String args[]) {
        MySQLProcedure mysqlProc = new MySQLProcedure();
        StringBuilder query = new StringBuilder();
        query.append("{ CALL spinputoutput(?, ?) }");
        Map<Integer, Object> params = new HashMap<Integer, Object>();
        params.put(1, 3);
        params.put(2, "@level");
        int[] resultRowNumbers = new int[] {2};
        Map<Integer, Object> outParams = mysqlProc.getOutParams(query.toString(), params, resultRowNumbers);
        if(outParams != null) {
            for(Entry<Integer, Object> outParamsEntry : outParams.entrySet()) {
                System.out.println(outParamsEntry.getValue());
            }
        }

    }
}

// config.properties

url=jdbc:mysql://localhost:3306/spdb?useSSL=false
user=root
password=*******************

// $ javac MySQLSpInputOutput.java
// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLSpInputOutput

****************************************************************************************************************************************************/