· 7 years ago · Oct 07, 2018, 05:28 PM
1$ winpty mysql --version
2mysql Ver 14.14 Distrib 5.7.18, for Win64 (x86_64)
3$ winpty mysql -V
4mysql Ver 14.14 Distrib 5.7.18, for Win64 (x86_64)
5$ winpty mysql -u root -p *******************
6mysql> exit
7# OR
8mysql> quit
9
10
11>mysql --version
12mysql Ver 14.14 Distrib 5.7.18, for Win64 (x86_64)
13>mysql -V
14mysql Ver 14.14 Distrib 5.7.18, for Win64 (x86_64)
15> mysql -u root -p
16mysql> exit
17# OR
18mysql> quit
19
20
21# Version
22SELECT VERSION();
23SELECT @@version;
24SHOW VARIABLES LIKE "%version%";
25SHOW GLOBAL VARIABLES LIKE '%version%';
26
27# Databases
28SHOW DATABASES;
29
30# Change Database
31USE `sys`;
32
33# Current Database
34SELECT DATABASE();
35SELECT DATABASE() FROM DUAL;
36
37# Tables in Current Database
38SHOW TABLES;
39
40# Columns
41SELECT `COLUMN_NAME`
42FROM `INFORMATION_SCHEMA`.`COLUMNS`
43WHERE `TABLE_SCHEMA`='sys'
44AND `TABLE_NAME`='host_summary';
45# OR
46SELECT `COLUMN_NAME`
47FROM `INFORMATION_SCHEMA`.`COLUMNS`
48WHERE `TABLE_SCHEMA`='sampledb'
49AND `TABLE_NAME`='users';
50
51#List of Stored Procedures/Functions Mysql Command Line
52SHOW PROCEDURE STATUS;
53SHOW FUNCTION STATUS;
54
55###############################################################################################################################################
56
57#MySQL JOIN
58
59DROP DATABASE IF EXISTS JoinExample;
60
61CREATE DATABASE JoinExample;
62
63USE JoinExample;
64DROP TABLE IF EXISTS Table1;
65
66
67CREATE TABLE Table1
68(ID INT, Value VARCHAR(10));
69
70INSERT INTO Table1 (ID, Value)
71SELECT 1, 'First'
72UNION ALL
73SELECT 2, 'Second'
74UNION ALL
75SELECT 3, 'Third'
76UNION ALL
77SELECT 4, 'Fourth'
78UNION ALL
79SELECT 5, 'Fifth';
80
81DROP TABLE IF EXISTS Table2;
82
83CREATE TABLE Table2
84(ID INT, Value VARCHAR(10));
85
86INSERT INTO Table2 (ID, Value)
87SELECT 1, 'I'
88UNION ALL
89SELECT 2, 'II'
90UNION ALL
91SELECT 3, 'III'
92UNION ALL
93SELECT 6, 'VI'
94UNION ALL
95SELECT 7, 'VII'
96UNION ALL
97SELECT 8, 'VIII';
98
99
100SELECT * FROM Table1;
101SELECT * FROM Table2;
102
103
104/* (INNER) JOIN */
105SELECT t1.*, t2.* FROM Table1 t1
106INNER JOIN Table2 t2 ON t1.ID = t2.ID;
107
108
109/* LEFT (OUTER) JOIN */
110SELECT t1.*, t2.* FROM Table1 t1
111LEFT JOIN Table2 t2 ON t1.ID = t2.ID;
112
113
114/* RIGHT (OUTER) JOIN */
115SELECT t1.*, t2.* FROM Table1 t1
116RIGHT JOIN Table2 t2 ON t1.ID = t2.ID;
117
118
119/*
120#NO FULL (OUTER) JOIN IN MYSQL
121SELECT t1.*, t2.* FROM Table1 t1
122FULL JOIN Table2 t2 ON t1.ID = t2.ID;
123*/
124
125
126/* Emulate FULL (OUTER) JOIN --NOTE: NULLS LAST */
127SELECT * FROM Table1 t1
128LEFT JOIN Table2 t2 ON t1.id = t2.id
129UNION
130SELECT * FROM Table1 t1
131RIGHT JOIN Table2 t2 ON t1.id = t2.id;
132
133
134/* CROSS JOIN --NOTE: Pivot t2.ID (Differs From SQL Server, PostgreSQL) */
135SELECT t1.*, t2.* FROM Table1 t1
136CROSS JOIN Table2 t2;
137
138###############################################################################################################################################
139
140# MySQL nth Highest
141
142DROP DATABASE IF EXISTS employeedb;
143
144CREATE DATABASE employeedb;
145
146USE employeedb;
147
148DROP TABLE IF EXISTS Employee;
149
150CREATE TABLE Employee
151(ID INT, Name NVARCHAR(50), Salary numeric(15, 2));
152
153INSERT INTO Employee
154VALUES
155(1, 'A', 10000), #4th
156(2, 'B', 8000), #5th
157(3, 'C', 8000),
158(4, 'D', 6000), #6th
159(5, 'E', 6000),
160(6, 'F', 6000),
161(7, 'G', 5000), #7th
162(8, 'H', 5000),
163(9, 'I', 5000),
164(10, 'J', 5000),
165(11, 'K', 4000), #8th
166(12, 'L', 4000),
167(13, 'M', 3000), #9th
168(14, 'N', 3000),
169(15, 'O', 1000), #10th
170(16, 'P', 14000), #2nd
171(17, 'Q', 14000),
172(18, 'R', 12000), #3rd
173(19, 'S', 12000),
174(20, 'T', 16000), #1st
175(21, 'U', 16000),
176(22, 'V', 16000),
177(23, 'W', 14000),
178(24, 'X', 12000),
179(25, 'Y', 12000),
180(26, 'Z', 10000);
181
182SELECT * FROM Employee;
183
184# 16000 #1st
185# 14000 #2nd
186# 12000 #3rd
187# 10000 #4th
188# 8000 #5th
189# 6000 #6th
190# 5000 #7th
191# 4000 #8th
192# 3000 #9th
193# 1000 #10th
194
195# nth Highest # 5th Highest # 8000
196SELECT Salary FROM
197(
198SELECT DISTINCT Salary FROM Employee
199ORDER BY Salary DESC LIMIT 5
200)
201A ORDER BY Salary LIMIT 1;
202# Alternatively
203SELECT *
204FROM Employee Emp1
205WHERE (5 - 1) = (
206SELECT COUNT(DISTINCT(Emp2.Salary))
207FROM Employee Emp2
208WHERE Emp2.Salary > Emp1.Salary) LIMIT 1;
209
210# 2nd Highest # 14000
211SELECT MAX(Salary) FROM Employee
212WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee);
213
214# Alternatively
215SELECT MAX(Salary) from Employee
216WHERE Salary <> (select MAX(Salary) from Employee);
217
218###############################################################################################################################################
219
220DROP DATABASE IF EXISTS spdb;
221DELIMITER $$
222CREATE DATABASE spdb;$$
223DELIMITER ;
224
225USE spdb;
226
227DROP TABLE IF EXISTS `customer`;
228DELIMITER $$
229CREATE TABLE `customer` (
230 `customer_id` int unsigned NOT NULL AUTO_INCREMENT,
231 `customer_name` varchar(50) NOT NULL,
232 `email` varchar(50) DEFAULT NULL,
233 `date_of_birth` date NOT NULL,
234 `income` double NOT NULL,
235 `credit_limit` decimal(10,2) DEFAULT NULL,
236 `create_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
237 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
238 PRIMARY KEY (`customer_id`)
239);$$
240DELIMITER ;
241
242SELECT * FROM customer;
243
244USE spdb;
245
246INSERT INTO
247`customer`
248(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
249VALUES
250('Bill Gates', 'billgates@microsoft.com', '1955-10-28', 97.9, 9.79);
251
252INSERT INTO
253`customer`
254(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
255VALUES
256('Paul Allen', 'paulallen@microsoft.com', '1953-01-21', 20.2, 2.02);
257
258INSERT INTO
259`customer`
260(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
261VALUES
262('Larry Page', 'larrypage@abc.xyz', '1973-3-26', 55.2, 5.52);
263
264INSERT INTO
265`customer`
266(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
267VALUES
268('Sergey Brin', 'sergeybrin@abc.xyz', '1973-08-21', 55.7, 5.57);
269
270INSERT INTO
271`customer`
272(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
273VALUES
274('Tim Cook', 'timcook@apple.com', '1960-11-01', 0.78, 0.07);
275
276INSERT INTO
277`customer`
278(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
279VALUES
280('Steve Wozniak', 'stevewozniak@apple.com', '1950-08-11', 0.1, 0.01);
281
282INSERT INTO
283`customer`
284(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
285VALUES
286('Jeff Bezos', 'jeffbezos@amazon.com', '1964-01-12', 165.0, 16.50);
287
288INSERT INTO
289`customer`
290(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
291VALUES
292('Mark Zuckerberg', 'markzuckerberg@facebook.com', '1984-05-14', 67.1, 6.71);
293
294INSERT INTO
295`customer`
296(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
297VALUES
298('Pierre Omidyar', 'pierreomidyar@ebay.com', '1967-06-21', 10.5, 1.05);
299
300INSERT INTO
301`customer`
302(`customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`)
303VALUES
304('Elon Musk', 'elonmusk@tesla.com', '1971-06-28', 19.8, 1.98);
305
306USE spdb;
307
308SELECT * FROM customer;
309
310
311USE spdb;
312
313DROP TABLE IF EXISTS `rental`;
314DELIMITER $$
315
316CREATE TABLE `rental` (
317 `rental_id` int(11) NOT NULL AUTO_INCREMENT,
318 `rental_date` datetime NOT NULL,
319 `customer_id` int unsigned NOT NULL,
320 `return_date` datetime DEFAULT NULL,
321 `status` varchar(50) NOT NULL,
322 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
323 PRIMARY KEY (`rental_id`),
324 CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE
325);$$
326DELIMITER ;
327
328SELECT * FROM rental;
329
330USE spdb;
331INSERT INTO `rental`
332(`rental_date`, `customer_id`, `return_date`, `status`)
333VALUES
334('2018-01-01', 1, '2018-01-22', 'Shipped'),
335('2018-01-02', 1, '2018-01-23', 'Shipped'),
336('2018-01-03', 1, '2018-01-24', 'Canceled'),
337('2018-01-04', 1, '2018-01-25', 'Resolved'),
338('2018-01-05', 1, '2018-01-26', 'Disputed'),
339('2018-01-06', 1, '2018-01-27', 'Shipped'),
340('2018-01-07', 1, '2018-01-28', 'Shipped'),
341('2018-01-08', 1, '2018-01-29', 'Resolved'),
342('2018-01-09', 1, '2018-01-30', 'Shipped'),
343('2018-01-10', 1, '2018-01-31', 'Shipped'),
344
345('2018-02-01', 2, '2018-02-19', 'Shipped'),
346('2018-02-02', 2, '2018-02-20', 'Shipped'),
347('2018-02-03', 2, '2018-02-21', 'Canceled'),
348('2018-02-04', 2, '2018-02-22', 'Resolved'),
349('2018-02-05', 2, '2018-02-23', 'Canceled'),
350('2018-02-06', 2, '2018-02-24', 'Resolved'),
351('2018-02-07', 2, '2018-02-25', 'Disputed'),
352('2018-02-08', 2, '2018-02-26', 'Resolved'),
353('2018-02-09', 2, '2018-02-27', 'Resolved'),
354('2018-02-10', 2, '2018-02-28', 'Shipped'),
355
356('2018-03-01', 3, '2018-03-22', 'Shipped'),
357('2018-03-02', 3, '2018-03-23', 'Shipped'),
358('2018-03-03', 3, '2018-03-24', 'Canceled'),
359('2018-03-04', 3, '2018-03-25', 'Resolved'),
360('2018-03-05', 3, '2018-03-26', 'Disputed'),
361('2018-03-06', 3, '2018-03-27', 'Resolved'),
362('2018-03-07', 3, '2018-03-28', 'Disputed'),
363('2018-03-08', 3, '2018-03-29', 'Resolved'),
364('2018-03-09', 3, '2018-03-30', 'Shipped'),
365('2018-03-10', 3, '2018-03-31', 'Shipped'),
366
367('2018-04-01', 4, '2018-04-21', 'Shipped'),
368('2018-04-02', 4, '2018-04-22', 'Canceled'),
369('2018-04-03', 4, '2018-04-23', 'Shipped'),
370('2018-04-04', 4, '2018-04-24', 'Resolved'),
371('2018-04-05', 4, '2018-04-25', 'Disputed'),
372('2018-04-06', 4, '2018-04-26', 'Resolved'),
373('2018-04-07', 4, '2018-04-27', 'Disputed'),
374('2018-04-08', 4, '2018-04-28', 'Resolved'),
375('2018-04-09', 4, '2018-04-29', 'Shipped'),
376('2018-04-10', 4, '2018-04-30', 'Shipped'),
377
378
379('2018-05-01', 5, '2018-05-22', 'Shipped'),
380('2018-05-02', 5, '2018-05-23', 'Shipped'),
381('2018-05-03', 5, '2018-05-24', 'Shipped'),
382('2018-05-04', 5, '2018-05-25', 'Resolved'),
383('2018-05-05', 5, '2018-05-26', 'Disputed'),
384('2018-05-06', 5, '2018-05-27', 'Canceled'),
385('2018-05-07', 5, '2018-05-28', 'Shipped'),
386('2018-05-08', 5, '2018-05-29', 'Resolved'),
387('2018-05-09', 5, '2018-05-30', 'Shipped'),
388('2018-05-10', 5, '2018-05-31', 'Shipped'),
389
390('2018-06-01', 6, '2018-06-21', 'Canceled'),
391('2018-06-02', 6, '2018-06-22', 'Shipped'),
392('2018-06-03', 6, '2018-06-23', 'Canceled'),
393('2018-06-04', 6, '2018-06-24', 'Resolved'),
394('2018-06-05', 6, '2018-06-25', 'Disputed'),
395('2018-06-06', 6, '2018-06-26', 'Resolved'),
396('2018-06-07', 6, '2018-06-27', 'Disputed'),
397('2018-06-08', 6, '2018-06-28', 'Resolved'),
398('2018-06-09', 6, '2018-06-29', 'Resolved'),
399('2018-06-10', 6, '2018-06-30', 'Shipped'),
400
401('2018-07-01', 7, '2018-07-22', 'Shipped'),
402('2018-07-02', 7, '2018-07-23', 'Shipped'),
403('2018-07-03', 7, '2018-07-24', 'Canceled'),
404('2018-07-04', 7, '2018-07-25', 'Resolved'),
405('2018-07-05', 7, '2018-07-26', 'Disputed'),
406('2018-07-06', 7, '2018-07-27', 'Shipped'),
407('2018-07-07', 7, '2018-07-28', 'Shipped'),
408('2018-07-08', 7, '2018-07-29', 'Shipped'),
409('2018-07-09', 7, '2018-07-30', 'Resolved'),
410('2018-07-10', 7, '2018-07-31', 'Shipped'),
411
412('2018-08-01', 8, '2018-08-22', 'Shipped'),
413('2018-08-02', 8, '2018-08-23', 'Shipped'),
414('2018-08-03', 8, '2018-08-24', 'Disputed'),
415('2018-08-04', 8, '2018-08-25', 'Resolved'),
416('2018-08-05', 8, '2018-08-26', 'Canceled'),
417('2018-08-06', 8, '2018-08-27', 'Shipped'),
418('2018-08-07', 8, '2018-08-28', 'Shipped'),
419('2018-08-08', 8, '2018-08-29', 'Shipped'),
420('2018-08-09', 8, '2018-08-30', 'Resolved'),
421('2018-08-10', 8, '2018-08-31', 'Shipped'),
422
423('2018-09-01', 9, '2018-09-21', 'Canceled'),
424('2018-09-02', 9, '2018-09-22', 'Resolved'),
425('2018-09-03', 9, '2018-09-23', 'Canceled'),
426('2018-09-04', 9, '2018-09-24', 'Shipped'),
427('2018-09-05', 9, '2018-09-25', 'Disputed'),
428('2018-09-06', 9, '2018-09-26', 'Resolved'),
429('2018-09-07', 9, '2018-09-27', 'Disputed'),
430('2018-09-08', 9, '2018-09-28', 'Resolved'),
431('2018-09-09', 9, '2018-09-29', 'Resolved'),
432('2018-09-10', 9, '2018-09-30', 'Shipped'),
433
434('2018-10-01', 10, '2018-10-22', 'Shipped'),
435('2018-10-02', 10, '2018-10-23', 'Shipped'),
436('2018-10-03', 10, '2018-10-24', 'Disputed'),
437('2018-10-04', 10, '2018-10-25', 'Resolved'),
438('2018-10-05', 10, '2018-10-26', 'Canceled'),
439('2018-10-06', 10, '2018-10-27', 'Resolved'),
440('2018-10-07', 10, '2018-10-28', 'Shipped'),
441('2018-10-08', 10, '2018-10-29', 'Shipped'),
442('2018-10-09', 10, '2018-10-30', 'Shipped'),
443('2018-10-10', 10, '2018-10-31', 'Shipped');
444
445SELECT * FROM rental;
446SELECT count(*) FROM rental;
447
448USE spdb;
449SELECT * FROM rental;
450SELECT count(*) FROM rental;
451
452
453USE spdb;
454DROP TABLE IF EXISTS `actor`;
455DELIMITER $$
456
457CREATE TABLE `actor` (
458 `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
459 `first_name` varchar(45) NOT NULL,
460 `last_name` varchar(45) NOT NULL,
461 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
462 PRIMARY KEY (`actor_id`),
463 KEY `idx_actor_last_name` (`last_name`)
464) ;$$
465DELIMITER ;
466
467SELECT * FROM `actor`;
468
469USE spdb;
470INSERT INTO `actor`
471(`first_name`, `last_name`)
472VALUES
473('PENELOPE', 'GUINESS'),
474('NICK', 'WAHLBERG'),
475('ED', 'CHASE'),
476('JENNIFER', 'DAVIS'),
477('JOHNNY', 'LOLLOBRIGIDA'),
478('BETTE', 'NICHOLSON'),
479('GRACE', 'MOSTEL'),
480('MATTHEW', 'JOHANSSON'),
481('JOE', 'SWANK'),
482('CHRISTIAN', 'GABLE'),
483('ZERO', 'CAGE'),
484('KARL', 'BERRY'),
485('UMA', 'WOOD'),
486('VIVIEN', 'BERGEN'),
487('CUBA', 'OLIVIER'),
488('FRED', 'COSTNER'),
489('HELEN', 'VOIGHT'),
490('DAN', 'TORN'),
491('BOB', 'FAWCETT'),
492('LUCILLE', 'TRACY'),
493('KIRSTEN', 'PALTROW'),
494('ELVIS', 'MARX'),
495('SANDRA', 'KILMER'),
496('CAMERON', 'STREEP'),
497('KEVIN', 'BLOOM'),
498('RIP', 'CRAWFORD'),
499('JULIA', 'MCQUEEN'),
500('WOODY', 'HOFFMAN'),
501('ALEC', 'WAYNE'),
502('SANDRA', 'PECK'),
503('SISSY', 'SOBIESKI'),
504('TIM', 'HACKMAN'),
505('MILLA', 'PECK'),
506('AUDREY', 'OLIVIER'),
507('JUDY', 'DEAN'),
508('BURT', 'DUKAKIS'),
509('VAL', 'BOLGER'),
510('TOM', 'MCKELLEN'),
511('GOLDIE', 'BRODY'),
512('JOHNNY', 'CAGE'),
513('JODIE', 'DEGENERES'),
514('TOM', 'MIRANDA'),
515('KIRK', 'JOVOVICH'),
516('NICK', 'STALLONE'),
517('REESE', 'KILMER'),
518('PARKER', 'GOLDBERG'),
519('JULIA', 'BARRYMORE'),
520('FRANCES', 'DAY-LEWIS'),
521('ANNE', 'CRONYN'),
522('NATALIE', 'HOPKINS'),
523('GARY', 'PHOENIX'),
524('CARMEN', 'HUNT'),
525('MENA', 'TEMPLE'),
526('PENELOPE', 'PINKETT'),
527('FAY', 'KILMER'),
528('DAN', 'HARRIS'),
529('JUDE', 'CRUISE'),
530('CHRISTIAN', 'AKROYD'),
531('DUSTIN', 'TAUTOU'),
532('HENRY', 'BERRY'),
533('CHRISTIAN', 'NEESON'),
534('JAYNE', 'NEESON'),
535('CAMERON', 'WRAY'),
536('RAY', 'JOHANSSON'),
537('ANGELA', 'HUDSON'),
538('MARY', 'TANDY'),
539('JESSICA', 'BAILEY'),
540('RIP', 'WINSLET'),
541('KENNETH', 'PALTROW'),
542('MICHELLE', 'MCCONAUGHEY'),
543('ADAM', 'GRANT'),
544('SEAN', 'WILLIAMS'),
545('GARY', 'PENN'),
546('MILLA', 'KEITEL'),
547('BURT', 'POSEY'),
548('ANGELINA', 'ASTAIRE'),
549('CARY', 'MCCONAUGHEY'),
550('GROUCHO', 'SINATRA'),
551('MAE', 'HOFFMAN'),
552('RALPH', 'CRUZ'),
553('SCARLETT', 'DAMON'),
554('WOODY', 'JOLIE'),
555('BEN', 'WILLIS'),
556('JAMES', 'PITT'),
557('MINNIE', 'ZELLWEGER'),
558('GREG', 'CHAPLIN'),
559('SPENCER', 'PECK'),
560('KENNETH', 'PESCI'),
561('CHARLIZE', 'DENCH'),
562('SEAN', 'GUINESS'),
563('CHRISTOPHER', 'BERRY'),
564('KIRSTEN', 'AKROYD'),
565('ELLEN', 'PRESLEY'),
566('KENNETH', 'TORN'),
567('DARYL', 'WAHLBERG'),
568('GENE', 'WILLIS'),
569('MEG', 'HAWKE'),
570('CHRIS', 'BRIDGES'),
571('JIM', 'MOSTEL'),
572('SPENCER', 'DEPP'),
573('SUSAN', 'DAVIS'),
574('WALTER', 'TORN'),
575('MATTHEW', 'LEIGH'),
576('PENELOPE', 'CRONYN'),
577('SIDNEY', 'CROWE'),
578('GROUCHO', 'DUNST'),
579('GINA', 'DEGENERES'),
580('WARREN', 'NOLTE'),
581('SYLVESTER', 'DERN'),
582('SUSAN', 'DAVIS'),
583('CAMERON', 'ZELLWEGER'),
584('RUSSELL', 'BACALL'),
585('MORGAN', 'HOPKINS'),
586('MORGAN', 'MCDORMAND'),
587('HARRISON', 'BALE'),
588('DAN', 'STREEP'),
589('RENEE', 'TRACY'),
590('CUBA', 'ALLEN'),
591('WARREN', 'JACKMAN'),
592('PENELOPE', 'MONROE'),
593('LIZA', 'BERGMAN'),
594('SALMA', 'NOLTE'),
595('JULIANNE', 'DENCH'),
596('SCARLETT', 'BENING'),
597('ALBERT', 'NOLTE'),
598('FRANCES', 'TOMEI'),
599('KEVIN', 'GARLAND'),
600('CATE', 'MCQUEEN'),
601('DARYL', 'CRAWFORD'),
602('GRETA', 'KEITEL'),
603('JANE', 'JACKMAN'),
604('ADAM', 'HOPPER'),
605('RICHARD', 'PENN'),
606('GENE', 'HOPKINS'),
607('RITA', 'REYNOLDS'),
608('ED', 'MANSFIELD'),
609('MORGAN', 'WILLIAMS'),
610('LUCILLE', 'DEE'),
611('EWAN', 'GOODING'),
612('WHOOPI', 'HURT'),
613('CATE', 'HARRIS'),
614('JADA', 'RYDER'),
615('RIVER', 'DEAN'),
616('ANGELA', 'WITHERSPOON'),
617('KIM', 'ALLEN'),
618('ALBERT', 'JOHANSSON'),
619('FAY', 'WINSLET'),
620('EMILY', 'DEE'),
621('RUSSELL', 'TEMPLE'),
622('JAYNE', 'NOLTE'),
623('GEOFFREY', 'HESTON'),
624('BEN', 'HARRIS'),
625('MINNIE', 'KILMER'),
626('MERYL', 'GIBSON'),
627('IAN', 'TANDY'),
628('FAY', 'WOOD'),
629('GRETA', 'MALDEN'),
630('VIVIEN', 'BASINGER'),
631('LAURA', 'BRODY'),
632('CHRIS', 'DEPP'),
633('HARVEY', 'HOPE'),
634('OPRAH', 'KILMER'),
635('CHRISTOPHER', 'WEST'),
636('HUMPHREY', 'WILLIS'),
637('AL', 'GARLAND'),
638('NICK', 'DEGENERES'),
639('LAURENCE', 'BULLOCK'),
640('WILL', 'WILSON'),
641('KENNETH', 'HOFFMAN'),
642('MENA', 'HOPPER'),
643('OLYMPIA', 'PFEIFFER'),
644('GROUCHO', 'WILLIAMS'),
645('ALAN', 'DREYFUSS'),
646('MICHAEL', 'BENING'),
647('WILLIAM', 'HACKMAN'),
648('JON', 'CHASE'),
649('GENE', 'MCKELLEN'),
650('LISA', 'MONROE'),
651('ED', 'GUINESS'),
652('JEFF', 'SILVERSTONE'),
653('MATTHEW', 'CARREY'),
654('DEBBIE', 'AKROYD'),
655('RUSSELL', 'CLOSE'),
656('HUMPHREY', 'GARLAND'),
657('MICHAEL', 'BOLGER'),
658('JULIA', 'ZELLWEGER'),
659('RENEE', 'BALL'),
660('ROCK', 'DUKAKIS'),
661('CUBA', 'BIRCH'),
662('AUDREY', 'BAILEY'),
663('GREGORY', 'GOODING'),
664('JOHN', 'SUVARI'),
665('BURT', 'TEMPLE'),
666('MERYL', 'ALLEN'),
667('JAYNE', 'SILVERSTONE'),
668('BELA', 'WALKEN'),
669('REESE', 'WEST'),
670('MARY', 'KEITEL'),
671('JULIA', 'FAWCETT'),
672('THORA', 'TEMPLE');
673
674SELECT * FROM `actor`;
675
676USE spdb;
677SELECT * FROM `actor`;
678
679
680# PROCEDURE 1
681USE spdb;
682DROP PROCEDURE IF EXISTS sp;
683DELIMITER $$
684CREATE PROCEDURE sp
685(
686 IN cust_id INT,
687 OUT shipped INT,
688 OUT canceled INT,
689 OUT resolved INT,
690 OUT disputed INT
691)
692BEGIN
693 -- shipped
694 SELECT
695 count(*) INTO shipped
696 FROM
697 rental
698 WHERE
699 customer_id = cust_id
700 AND
701 status = 'Shipped';
702
703 -- canceled
704 SELECT
705 count(*) INTO canceled
706 FROM
707 rental
708 WHERE
709 customer_id = cust_id
710 AND
711 status = 'Canceled';
712
713 -- resolved
714 SELECT
715 count(*) INTO resolved
716 FROM
717 rental
718 WHERE
719 customer_id = cust_id
720 AND
721 status = 'Resolved';
722
723 -- disputed
724 SELECT
725 count(*) INTO disputed
726 FROM
727 rental
728 WHERE
729 customer_id = cust_id
730 AND
731 status = 'Disputed';
732END;$$
733DELIMITER ;
734
735USE spdb;
736CALL sp(6, @shipped, @canceled, @resolved, @disputed);
737SELECT @shipped, @canceled, @resolved, @disputed;
738
739
740# PROCEDURE 2
741USE spdb;
742DROP PROCEDURE IF EXISTS spin;
743DELIMITER $$
744CREATE PROCEDURE spin
745(
746 IN n INT
747)
748BEGIN
749 # SELECT * FROM customer LIMIT n;
750 SELECT `customer_id`, `customer_name`, `email`, `date_of_birth`, `income`, `credit_limit`, `create_date`, `last_update` FROM customer LIMIT n;
751END;$$
752DELIMITER ;
753
754USE spdb;
755CALL spin(6);
756
757
758# PROCEDURE 3
759USE spdb;
760DROP PROCEDURE IF EXISTS spinout;
761DELIMITER $$
762CREATE PROCEDURE spinout
763(
764 IN in_customer_id INT, OUT out_count INT
765)
766BEGIN
767 SELECT COUNT(*) INTO out_count FROM customer WHERE customer_id > in_customer_id;
768END; $$
769DELIMITER ;
770
771USE spdb;
772CALL spinout(4, @out_count);
773SELECT @out_count;
774
775
776# PROCEDURE 4
777USE spdb;
778DROP PROCEDURE IF EXISTS spinputoutput;
779DELIMITER $$
780CREATE PROCEDURE spinputoutput
781(
782 in p_customer_id int(11),
783 inout p_customerLevel varchar(10) # Note # out p_customerLevel varchar(10)
784)
785BEGIN
786 DECLARE creditlimit double;
787 SELECT
788 credit_limit INTO creditlimit
789 FROM
790 customer
791 WHERE
792 customer_id = p_customer_id;
793 IF creditlimit > 50 THEN
794 SET p_customerLevel = 'PLATINUM';
795 ELSEIF (creditlimit <= 50 AND creditlimit >= 10) THEN
796 SET p_customerLevel = 'GOLD';
797 ELSEIF creditlimit < 10 THEN
798 SET p_customerLevel = 'SILVER';
799 END IF;
800END; $$
801DELIMITER ;
802
803USE spdb;
804CALL spinputoutput(3, @level);
805SELECT @level AS level;
806
807
808# DROP DATABASE IF EXISTS spdb;
809# DELIMITER $$
810# CREATE DATABASE spdb;$$
811# DELIMITER ;
812
813
814# PROCEDURE 5
815USE spdb;
816DROP PROCEDURE IF EXISTS multiply;
817DELIMITER $$
818CREATE PROCEDURE multiply
819(
820 IN pFac1 INT,
821 IN pFac2 INT,
822 OUT pProd INT
823)
824BEGIN
825 SET pProd := pFac1 * pFac2;
826END;$$
827DELIMITER ;
828
829USE spdb;
830CALL multiply(5, 5, @Result);
831SELECT @Result;
832
833
834# PROCEDURE 6
835USE spdb;
836DROP PROCEDURE IF EXISTS concat;
837DELIMITER $$
838CREATE PROCEDURE concat
839(
840 IN pStr1 VARCHAR(20),
841 IN pStr2 VARCHAR(20),
842 OUT pConCat VARCHAR(100)
843)
844BEGIN
845 SET pConCat := CONCAT(pStr1, pStr2);
846END;$$
847DELIMITER ;
848
849USE spdb;
850CALL concat('My', 'SQL', @Result);
851SELECT @Result;
852
853
854# PROCEDURE 7
855USE spdb;
856DROP PROCEDURE IF EXISTS prepend;
857DELIMITER $$
858CREATE PROCEDURE prepend
859(
860 IN inParam VARCHAR(255),
861 INOUT inOutParam INT
862)
863BEGIN
864 DECLARE z INT;
865 SET z = inOutParam + 1;
866 SET inOutParam = z;
867 SELECT inParam;
868 SELECT CONCAT('zyxw', inParam);
869END;$$
870DELIMITER ;
871
872USE spdb;
873CALL prepend('abcdefg', @inOutParam);
874
875/****************************************************************************************************************************************************
876
877// MySQLConnection.java
878
879import java.io.InputStream;
880import java.io.IOException;
881import java.io.FileInputStream;
882import java.sql.Connection;
883import java.sql.DriverManager;
884import java.sql.SQLException;
885import java.util.Properties;
886
887class DataAccessObject {
888 private Connection getConnection() throws SQLException {
889 Connection conn = null;
890 try {
891 InputStream input = new FileInputStream("config.properties");
892 Properties connectionProps = new Properties();
893 connectionProps.load(input);
894 String url = connectionProps.getProperty("url"); //
895 String user = connectionProps.getProperty("user"); //
896 String password = connectionProps.getProperty("password"); //
897 // conn = DriverManager.getConnection(url); //
898 conn = DriverManager.getConnection(url, user, password); //
899 } catch (IOException e) {
900 e.printStackTrace();
901 }
902 return conn;
903 }
904
905 public void testConnection() {
906 try (Connection conn = getConnection();) {
907 if (conn != null) {
908 System.out.println("Connected to MySQL Server successfully.");
909 }
910 } catch (SQLException e) {
911 e.printStackTrace();
912 }
913 }
914}
915
916public class MySQLConnection {
917 public static void main(String args[]) {
918 DataAccessObject dao = new DataAccessObject();
919 dao.testConnection();
920 }
921}
922
923// config.properties
924
925url=jdbc:mysql://localhost:3306/spdb?useSSL=false
926user=root
927password=*******************
928
929// $ javac MySQLConnection.java
930// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLConnection
931
932****************************************************************************************************************************************************/
933
934/****************************************************************************************************************************************************
935
936// MySQLCRUD.java
937
938import java.io.InputStream;
939import java.io.IOException;
940import java.io.FileInputStream;
941import java.sql.Connection;
942import java.sql.DriverManager;
943import java.sql.SQLException;
944import java.sql.ResultSet;
945import java.sql.Statement;
946import java.sql.PreparedStatement;
947import java.util.Properties;
948import java.util.List;
949import java.util.ArrayList;
950
951class Actor {
952 private String firstName;
953 private String lastName;
954
955 public Actor() { }
956
957 public Actor(String firstName, String lastName) {
958 this.firstName = firstName;
959 this.lastName = lastName;
960 }
961
962 public String getFirstName() {
963 return firstName;
964 }
965
966 public void setFirstName(String firstName) {
967 this.firstName = firstName;
968 }
969
970 public String getLastName() {
971 return lastName;
972 }
973
974 public void setLastName(String lastName) {
975 this.lastName = lastName;
976 }
977}
978
979class DataAccessObject {
980 public Connection getConnection() throws SQLException {
981 Connection conn = null;
982 try {
983 InputStream input = new FileInputStream("config.properties");
984 Properties connectionProps = new Properties();
985 connectionProps.load(input);
986 String url = connectionProps.getProperty("url"); //
987 String user = connectionProps.getProperty("user"); //
988 String password = connectionProps.getProperty("password"); //
989 // conn = DriverManager.getConnection(url); //
990 conn = DriverManager.getConnection(url, user, password); //
991 } catch (IOException e) {
992 e.printStackTrace();
993 }
994 return conn;
995 }
996
997 public void testConnection() {
998 try (Connection conn = getConnection();) {
999 if (conn != null) {
1000 System.out.println("Connected to MySQL Server successfully.");
1001 }
1002 } catch (SQLException e) {
1003 e.printStackTrace();
1004 }
1005 }
1006}
1007
1008class CRUD {
1009 public int dropTableIfExists() {
1010 DataAccessObject dao = new DataAccessObject();
1011 // dao.testConnection();
1012
1013 StringBuilder dropQuery = new StringBuilder();
1014 dropQuery.append("DROP TABLE IF EXISTS `spdb`.`actor`;");
1015
1016 int result = 0;
1017
1018 try (Connection conn = dao.getConnection();
1019 Statement stmt = conn.createStatement()) {
1020 System.out.println("Dropping table in given database...");
1021 result = stmt.executeUpdate(dropQuery.toString());
1022 System.out.println("Dropped table in given database.");
1023 } catch (SQLException ex) {
1024 System.out.println(ex.getMessage());
1025 }
1026 return result;
1027 }
1028
1029 public int createTableLike() {
1030 DataAccessObject dao = new DataAccessObject();
1031 // dao.testConnection();
1032
1033 StringBuilder createQuery = new StringBuilder();
1034 createQuery.append("CREATE TABLE IF NOT EXISTS `spdb`.`actor` LIKE `sakila`.`actor`;");
1035
1036 int result = 0;
1037
1038 try (Connection conn = dao.getConnection();
1039 Statement stmt = conn.createStatement()) {
1040 System.out.println("Creating table in given database...");
1041 result = stmt.executeUpdate(createQuery.toString());
1042 System.out.println("Created table in given database.");
1043 } catch (SQLException ex) {
1044 System.out.println(ex.getMessage());
1045 }
1046 return result;
1047 }
1048
1049 public int insertTable() {
1050 DataAccessObject dao = new DataAccessObject();
1051 // dao.testConnection();
1052
1053 StringBuilder insertQuery = new StringBuilder();
1054 insertQuery.append("INSERT `spdb`.`actor` SELECT * FROM `sakila`.`actor`;");
1055
1056 int result = 0;
1057
1058 try (Connection conn = dao.getConnection();
1059 Statement stmt = conn.createStatement()) {
1060 System.out.println("Inserting table in given database...");
1061 result = stmt.executeUpdate(insertQuery.toString());
1062 System.out.println("Inserting table in given database.");
1063 } catch (SQLException ex) {
1064 System.out.println(ex.getMessage());
1065 }
1066 return result;
1067 }
1068
1069 public void selectActors(String query) {
1070 DataAccessObject dao = new DataAccessObject();
1071 // dao.testConnection();
1072 try (Connection conn = dao.getConnection();
1073 PreparedStatement pstmt = conn.prepareStatement(query)) {
1074 ResultSet rs = pstmt.executeQuery();
1075 while (rs.next()) {
1076 /*
1077 System.out.println(String.format("%-10d%-20s%-20s%tF",
1078 rs.getInt(1),
1079 rs.getString(2),
1080 rs.getString(3),
1081 rs.getTimestamp(4)));
1082 */
1083 int actorId = rs.getInt("actor_id");
1084 String fName = rs.getString("first_name");
1085 String lName = rs.getString("last_name");
1086 java.sql.Timestamp lastUpdate = rs.getTimestamp("last_update");
1087 System.out.println(String.format("%-10d%-20s%-20s%tF",
1088 actorId,
1089 fName,
1090 lName,
1091 lastUpdate));
1092 }
1093 } catch (SQLException e) {
1094 System.out.println(e.getMessage());
1095 }
1096 }
1097
1098 public void selectActorsLike(String query, String text) {
1099 DataAccessObject dao = new DataAccessObject();
1100 // dao.testConnection();
1101 try (Connection conn = dao.getConnection();
1102 PreparedStatement pstmt = conn.prepareStatement(query)) {
1103 pstmt.setString(1, '%' + text + '%');
1104 ResultSet rs = pstmt.executeQuery();
1105 while (rs.next()) {
1106 /*
1107 System.out.println(String.format("%-10d%-20s%-20s%tF",
1108 rs.getInt(1),
1109 rs.getString(2),
1110 rs.getString(3),
1111 rs.getTimestamp(4)));
1112 */
1113 int actorId = rs.getInt("actor_id");
1114 String fName = rs.getString("first_name");
1115 String lName = rs.getString("last_name");
1116 java.sql.Timestamp lastUpdate = rs.getTimestamp("last_update");
1117 System.out.println(String.format("%-10d%-20s%-20s%tF",
1118 actorId,
1119 fName,
1120 lName,
1121 lastUpdate));
1122 }
1123 } catch (SQLException e) {
1124 System.out.println(e.getMessage());
1125 }
1126 }
1127
1128 public long insertActor(Actor actor) {
1129 DataAccessObject dao = new DataAccessObject();
1130 // dao.testConnection();
1131
1132 StringBuilder insertQuery = new StringBuilder();
1133 insertQuery.append("INSERT INTO actor(first_name, last_name) " + "VALUES(?, ?)");
1134
1135 long id = 0;
1136
1137 try (Connection conn = dao.getConnection();
1138 PreparedStatement pstmt = conn.prepareStatement(insertQuery.toString(),
1139 Statement.RETURN_GENERATED_KEYS)) {
1140
1141 pstmt.setString(1, actor.getFirstName());
1142 pstmt.setString(2, actor.getLastName());
1143
1144 int affectedRows = pstmt.executeUpdate();
1145 // check the affected rows
1146 if (affectedRows > 0) {
1147 // get the id back
1148 try (ResultSet rs = pstmt.getGeneratedKeys()) {
1149 if (rs.next()) {
1150 id = rs.getLong(1);
1151 }
1152 } catch (SQLException ex) {
1153 System.out.println(ex.getMessage());
1154 }
1155 }
1156 } catch (SQLException ex) {
1157 System.out.println(ex.getMessage());
1158 }
1159 return id;
1160 }
1161
1162 public void insertActors(List<Actor> list) {
1163 DataAccessObject dao = new DataAccessObject();
1164 // dao.testConnection();
1165
1166 StringBuilder insertQuery = new StringBuilder();
1167 insertQuery.append("INSERT INTO actor(first_name, last_name) " + "VALUES(?, ?)");
1168
1169 try (Connection conn = dao.getConnection();
1170 PreparedStatement statement = conn.prepareStatement(insertQuery.toString());) {
1171 int count = 0;
1172
1173 for (Actor actor : list) {
1174 statement.setString(1, actor.getFirstName());
1175 statement.setString(2, actor.getLastName());
1176
1177 statement.addBatch();
1178 count++;
1179 // execute every 100 rows or less
1180 if (count % 100 == 0 || count == list.size()) {
1181 statement.executeBatch();
1182 }
1183 }
1184 } catch (SQLException ex) {
1185 System.out.println(ex.getMessage());
1186 }
1187 }
1188
1189 public int updateActor(int id, Actor act) {
1190 DataAccessObject dao = new DataAccessObject();
1191 // dao.testConnection();
1192
1193 StringBuilder updateQuery = new StringBuilder();
1194 updateQuery.append("UPDATE actor " + "SET first_name = ? " + ", last_name = ? " + "WHERE actor_id = ?");
1195
1196 int affectedrows = 0;
1197
1198 try (Connection conn = dao.getConnection();
1199 PreparedStatement pstmt = conn.prepareStatement(updateQuery.toString())) {
1200
1201 pstmt.setString(1, act.getFirstName());
1202 pstmt.setString(2, act.getLastName());
1203 pstmt.setInt(3, id);
1204
1205 affectedrows = pstmt.executeUpdate();
1206
1207 } catch (SQLException ex) {
1208 System.out.println(ex.getMessage());
1209 }
1210 return affectedrows;
1211 }
1212
1213 public int deleteActor(int id) {
1214 DataAccessObject dao = new DataAccessObject();
1215 // dao.testConnection();
1216
1217 StringBuilder deleteQuery = new StringBuilder();
1218 deleteQuery.append("DELETE FROM actor WHERE actor_id = ?");
1219
1220 int affectedrows = 0;
1221
1222 try (Connection conn = dao.getConnection();
1223 PreparedStatement pstmt = conn.prepareStatement(deleteQuery.toString())) {
1224
1225 pstmt.setInt(1, id);
1226
1227 affectedrows = pstmt.executeUpdate();
1228
1229 } catch (SQLException ex) {
1230 System.out.println(ex.getMessage());
1231 }
1232 return affectedrows;
1233 }
1234}
1235
1236public class MySQLCRUD {
1237 public static void main(String[] args) {
1238 CRUD crd = new CRUD();
1239 crd.dropTableIfExists();
1240 crd.createTableLike();
1241 crd.insertTable();
1242
1243 StringBuilder selectQuery = new StringBuilder();
1244 // selectQuery.append("Select * FROM actor;");
1245 selectQuery.append("SELECT actor_id, first_name, last_name, last_update FROM actor;");
1246 System.out.println("Select");
1247 crd.selectActors(selectQuery.toString());
1248
1249 System.out.println("Select actor like 'AL'");
1250 StringBuilder selectLikeQuery = new StringBuilder();
1251 // selectLikeQuery.append("Select * FROM actor WHERE first_name LIKE (?);");
1252 selectLikeQuery.append("SELECT actor_id, first_name, last_name, last_update FROM actor WHERE first_name LIKE (?);");
1253 String text = "AL"; // case-sensitive
1254 crd.selectActorsLike(selectLikeQuery.toString(), text);
1255
1256 Actor act = new Actor("Foo", "Bar");
1257 System.out.println("Insert");
1258 long id = crd.insertActor(act);
1259 System.out.printf("Inserted actor %s %s with id %d%n", act.getFirstName(), act.getLastName(), id);
1260 System.out.println("Select");
1261 crd.selectActors(selectQuery.toString());
1262
1263 List<Actor> actorList = new ArrayList<Actor>();
1264 actorList.add(new Actor("Baz", "Qux"));
1265 actorList.add(new Actor("Quux", "Corge"));
1266 System.out.println("Batch Insert");
1267 crd.insertActors(actorList);
1268 actorList.forEach(actr -> System.out.printf("Batch Inserted actor %s %s%n", actr.getFirstName(), actr.getLastName()));
1269 System.out.println("Select");
1270 crd.selectActors(selectQuery.toString());
1271
1272 int ident = 202;
1273 Actor actr = new Actor("Grault", "Garply");
1274 int rowsAffected = crd.updateActor(ident, actr);
1275 // System.out.println("Rows affected:" + rowsAffected);
1276 if (rowsAffected > 0) {
1277 System.out.println("Update");
1278 System.out.printf("Updated actor Id: %d with First Name: %s and Last Name: %s%n", ident, actr.getFirstName(), actr.getLastName());
1279 }
1280 System.out.println("Select");
1281 crd.selectActors(selectQuery.toString());
1282
1283 int identity = 203;
1284 int recordsAffected = crd.deleteActor(identity);
1285 // System.out.println("Records affected:" + recordsAffected);
1286 if (recordsAffected > 0) {
1287 System.out.println("Delete");
1288 System.out.printf("Deleted actor Id: %d%n", identity);
1289 }
1290
1291 System.out.println("Select");
1292 crd.selectActors(selectQuery.toString());
1293 }
1294}
1295
1296// config.properties
1297
1298url=jdbc:mysql://localhost:3306/spdb?useSSL=false
1299user=root
1300password=*******************
1301
1302// $ javac MySQLCRUD.java
1303// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLCRUD
1304
1305****************************************************************************************************************************************************/
1306
1307/****************************************************************************************************************************************************
1308
1309// MySQLSelect.java
1310
1311import java.io.InputStream;
1312import java.io.IOException;
1313import java.io.FileInputStream;
1314import java.sql.Connection;
1315import java.sql.DriverManager;
1316import java.sql.SQLException;
1317import java.sql.Statement;
1318import java.sql.ResultSet;
1319import java.sql.ResultSetMetaData;
1320import java.util.Properties;
1321import java.util.List;
1322import java.util.ArrayList;
1323import java.util.Map;
1324import java.util.HashMap;
1325import java.util.Map.Entry;
1326import java.lang.StringBuilder;
1327
1328class DataAccessObject {
1329 private Connection getConnection() throws SQLException {
1330 Connection conn = null;
1331 try {
1332 InputStream input = new FileInputStream("config.properties");
1333 Properties connectionProps = new Properties();
1334 connectionProps.load(input);
1335 String url = connectionProps.getProperty("url"); //
1336 String user = connectionProps.getProperty("user"); //
1337 String password = connectionProps.getProperty("password"); //
1338 // conn = DriverManager.getConnection(url); //
1339 conn = DriverManager.getConnection(url, user, password); //
1340 } catch (IOException e) {
1341 e.printStackTrace();
1342 }
1343 return conn;
1344 }
1345
1346 public void testConnection() {
1347 try (Connection conn = getConnection();) {
1348 if (conn != null) {
1349 System.out.println("Connected to MySQL Server successfully.");
1350 }
1351 } catch (SQLException e) {
1352 e.printStackTrace();
1353 }
1354 }
1355
1356 public Map<Integer, Map<String, String>> getData(String query) {
1357 Map<Integer, Map<String, String>> rows = new HashMap<Integer, Map<String, String>>();
1358 ResultSet rs;
1359 try (Connection conn = getConnection();
1360 Statement stmt = conn.createStatement()) {
1361 if (conn != null) {
1362 System.out.println("Connected to MySQL Server successfully.");
1363 }
1364 rs = stmt.executeQuery(query);
1365 int rowCount = 0;
1366 ResultSetMetaData meta = rs.getMetaData();
1367 while(rs.next()) {
1368 int columnCount = meta.getColumnCount();
1369 Map<String, String> columns = new HashMap<String, String>();
1370 for (int i = 1; i <= columnCount; i++) {
1371 String type = meta.getColumnClassName(i);
1372 String key = meta.getColumnName(i);
1373 String value = rs.getString(key);
1374 columns.put(key, value);
1375 }
1376 /*
1377 for(Entry<String, String> columnsEntry : columns.entrySet()) {
1378 System.out.println(columnsEntry.getKey() + ": " + columnsEntry.getValue());
1379 }
1380 System.out.println("Number of Columns(s) = " + columns.size());
1381 */
1382 rowCount++;
1383 rows.put(rowCount, columns);
1384 }
1385 /*
1386 for(Entry<Integer, Map<String, String>> rowsEntry : rows.entrySet()) {
1387 for(Entry<String, String> columnsEntry : rowsEntry.getValue().entrySet()) {
1388 System.out.println(columnsEntry.getKey() + ": " + columnsEntry.getValue());
1389 }
1390 }
1391 System.out.println("Number of Row(s) = " + rows.size());
1392 */
1393 } catch (SQLException e) {
1394 e.printStackTrace();
1395 }
1396 return rows;
1397 }
1398}
1399
1400public class MySQLSelect {
1401 public static void main(String args[]) {
1402 DataAccessObject dao = new DataAccessObject();
1403 // dao.testConnection();
1404 StringBuilder selectQuery = new StringBuilder();
1405 // selectQuery.append("Select * FROM customer;");
1406 selectQuery.append("SELECT customer_id, customer_name, email, date_of_birth, income, credit_limit, create_date, last_update FROM customer;");
1407 Map<Integer, Map<String, String>> rows = dao.getData(selectQuery.toString());
1408 if (rows != null) {
1409 int rowCount = rows.size();
1410 System.out.println("Number of Row(s) = " + rowCount);
1411 if (rowCount > 0) {
1412 for(Entry<Integer, Map<String, String>> rowsEntry : rows.entrySet()) {
1413 // int columnsCount = 0;
1414 for(Entry<String, String> columnsEntry : rowsEntry.getValue().entrySet()) {
1415 System.out.println(columnsEntry.getKey() + ": " + columnsEntry.getValue());
1416 /*
1417 columnsCount++;
1418 System.out.print(columnsEntry.getValue());
1419 if (columnsCount < rowsEntry.getValue().entrySet().size()) {
1420 System.out.print(" - " );
1421 }
1422 */
1423 }
1424 System.out.println();
1425 }
1426 }
1427 }
1428 }
1429}
1430
1431// config.properties
1432
1433url=jdbc:mysql://localhost:3306/spdb?useSSL=false
1434user=root
1435password=*******************
1436
1437// $ javac MySQLSelect.java
1438// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLSelect
1439
1440****************************************************************************************************************************************************/
1441
1442/****************************************************************************************************************************************************
1443
1444// MySQLInOut.java
1445
1446import java.io.InputStream;
1447import java.io.IOException;
1448import java.io.FileInputStream;
1449import java.sql.Connection;
1450import java.sql.DriverManager;
1451import java.sql.SQLException;
1452import java.sql.Statement;
1453import java.sql.CallableStatement;
1454import java.sql.ResultSet;
1455import java.sql.ResultSetMetaData;
1456import java.util.Properties;
1457import java.util.List;
1458import java.util.ArrayList;
1459import java.util.Map;
1460import java.util.HashMap;
1461import java.util.Map.Entry;
1462import java.lang.StringBuilder;
1463import java.sql.Date;
1464import java.sql.Time;
1465import java.sql.Timestamp;
1466import java.sql.Types;
1467
1468class DataAccessObject {
1469 public Connection getConnection() throws SQLException {
1470 Connection conn = null;
1471 try {
1472 InputStream input = new FileInputStream("config.properties");
1473 Properties connectionProps = new Properties();
1474 connectionProps.load(input);
1475 String url = connectionProps.getProperty("url"); //
1476 String user = connectionProps.getProperty("user"); //
1477 String password = connectionProps.getProperty("password"); //
1478 // conn = DriverManager.getConnection(url); //
1479 conn = DriverManager.getConnection(url, user, password); //
1480 } catch (IOException e) {
1481 e.printStackTrace();
1482 }
1483 return conn;
1484 }
1485
1486 public void testConnection() {
1487 try (Connection conn = getConnection();) {
1488 System.out.println("Connected to MySQL Server successfully.");
1489 } catch (SQLException e) {
1490 e.printStackTrace();
1491 }
1492 }
1493}
1494
1495class MySQLProcedure
1496{
1497 public <T> CallableStatement setParams(CallableStatement statement, Map<Integer, T> params) throws SQLException {
1498 for(Entry<Integer, T> paramsEntry : params.entrySet()) {
1499 int key = paramsEntry.getKey();
1500 statement.setObject(key, paramsEntry.getValue());
1501 }
1502 return statement;
1503 }
1504
1505 public <T> Map<Integer, Object> getOutParams(String query, Map<Integer, T> params, int[] resultRowNumbers) {
1506 Map<Integer, Object> outParams = new HashMap<Integer, Object>();
1507 ResultSet rs;
1508 DataAccessObject dao = new DataAccessObject();
1509 try (Connection conn = dao.getConnection();) {
1510 CallableStatement statement = conn.prepareCall(query);
1511 System.out.println("Connected to MySQL Server successfully.");
1512 statement = setParams(statement, params);
1513 // int resultCount = statement.executeUpdate();
1514 boolean hadResults = statement.execute();
1515 while (hadResults) {
1516 rs = statement.getResultSet();
1517 while (rs.next()) {
1518 for(int i = 0; i < resultRowNumbers.length; i++) {
1519 outParams.put(i, rs.getObject(resultRowNumbers[i]));
1520 }
1521 }
1522 hadResults = statement.getMoreResults();
1523 }
1524 } catch (SQLException e) {
1525 e.printStackTrace();
1526 }
1527 return outParams;
1528 }
1529}
1530
1531public class MySQLInOut {
1532 public static void main(String args[]) {
1533 MySQLProcedure mysqlProc = new MySQLProcedure();
1534 StringBuilder query = new StringBuilder();
1535 query.append("{ CALL prepend(?, ?) }");
1536 Map<Integer, Object> params = new HashMap<Integer, Object>();
1537 params.put(1, "abcdefg");
1538 params.put(2, 0);
1539 int[] resultRowNumbers = new int[] {1};
1540 Map<Integer, Object> outParams = mysqlProc.getOutParams(query.toString(), params, resultRowNumbers);
1541 if(outParams != null) {
1542 for(Entry<Integer, Object> outParamsEntry : outParams.entrySet()) {
1543 System.out.println(outParamsEntry.getValue());
1544 }
1545 }
1546 }
1547}
1548
1549// config.properties
1550
1551url=jdbc:mysql://localhost:3306/spdb?useSSL=false
1552user=root
1553password=*******************
1554
1555// $ javac MySQLInOut.java
1556// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLInOut
1557
1558****************************************************************************************************************************************************/
1559
1560/****************************************************************************************************************************************************
1561
1562// MySQLProcs.java
1563
1564import java.io.InputStream;
1565import java.io.IOException;
1566import java.io.FileInputStream;
1567import java.sql.Connection;
1568import java.sql.DriverManager;
1569import java.sql.SQLException;
1570import java.sql.Statement;
1571import java.sql.CallableStatement;
1572import java.sql.ResultSet;
1573import java.sql.ResultSetMetaData;
1574import java.util.Properties;
1575import java.util.List;
1576import java.util.ArrayList;
1577import java.util.Map;
1578import java.util.HashMap;
1579import java.util.Map.Entry;
1580import java.lang.StringBuilder;
1581import java.sql.Date;
1582import java.sql.Time;
1583import java.sql.Timestamp;
1584import java.sql.Types;
1585
1586class DataAccessObject {
1587 public Connection getConnection() throws SQLException {
1588 Connection conn = null;
1589 try {
1590 InputStream input = new FileInputStream("config.properties");
1591 Properties connectionProps = new Properties();
1592 connectionProps.load(input);
1593 String url = connectionProps.getProperty("url"); //
1594 String user = connectionProps.getProperty("user"); //
1595 String password = connectionProps.getProperty("password"); //
1596 // conn = DriverManager.getConnection(url); //
1597 conn = DriverManager.getConnection(url, user, password); //
1598 } catch (IOException e) {
1599 e.printStackTrace();
1600 }
1601 return conn;
1602 }
1603
1604 public void testConnection() {
1605 try (Connection conn = getConnection();) {
1606 System.out.println("Connected to MySQL Server successfully.");
1607 } catch (SQLException e) {
1608 e.printStackTrace();
1609 }
1610 }
1611}
1612
1613class MySQLProcedure
1614{
1615 public <T> CallableStatement setParams(CallableStatement statement, Map<Integer, T> params) throws SQLException {
1616 for(Entry<Integer, T> paramsEntry : params.entrySet()) {
1617 int key = paramsEntry.getKey();
1618 statement.setObject(key, paramsEntry.getValue());
1619 }
1620 return statement;
1621 }
1622
1623 public <T> Map<Integer, Object> getOutParams(String query, Map<Integer, T> params, int[] resultRowNumbers) {
1624 Map<Integer, Object> outParams = new HashMap<Integer, Object>();
1625 DataAccessObject dao = new DataAccessObject();
1626 try (Connection conn = dao.getConnection();) {
1627 CallableStatement statement = conn.prepareCall(query);
1628 System.out.println("Connected to MySQL Server successfully.");
1629 statement = setParams(statement, params);
1630 // int result = statement.executeUpdate();
1631 boolean hadResults = statement.execute();
1632 for(int i = 0; i < resultRowNumbers.length; i++) {
1633 outParams.put(i, statement.getObject(resultRowNumbers[i]));
1634 }
1635 } catch (SQLException e) {
1636 e.printStackTrace();
1637 }
1638 return outParams;
1639 }
1640}
1641
1642public class MySQLProcs {
1643 public static void main(String args[]) {
1644 MySQLProcedure mysqlProc = new MySQLProcedure();
1645 StringBuilder query = new StringBuilder();
1646 query.append("{ CALL multiply(?, ?, ?) }");
1647 Map<Integer, Object> params = new HashMap<Integer, Object>();
1648 params.put(1, 5);
1649 params.put(2, 5);
1650 params.put(3, "@Result");
1651 int[] resultRowNumbers = new int[] {3};
1652 Map<Integer, Object> outParams = mysqlProc.getOutParams(query.toString(), params, resultRowNumbers);
1653 if(outParams != null) {
1654 for(Entry<Integer, Object> outParamsEntry : outParams.entrySet()) {
1655 System.out.println(outParamsEntry.getValue());
1656 }
1657 }
1658
1659 query = new StringBuilder();
1660 query.append("{ CALL concat(?, ?, ?) }");
1661 params = new HashMap<Integer, Object>();
1662 params.put(1, "My");
1663 params.put(2, "SQL");
1664 params.put(3, "@Result");
1665 resultRowNumbers = new int[] {3};
1666 outParams = mysqlProc.getOutParams(query.toString(), params, resultRowNumbers);
1667 if(outParams != null) {
1668 for(Entry<Integer, Object> outParamsEntry : outParams.entrySet()) {
1669 System.out.println(outParamsEntry.getValue());
1670 }
1671 }
1672 }
1673}
1674
1675// config.properties
1676
1677url=jdbc:mysql://localhost:3306/spdb?useSSL=false
1678user=root
1679password=*******************
1680
1681// $ javac MySQLProcs.java
1682// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLProcs
1683
1684****************************************************************************************************************************************************/
1685
1686/****************************************************************************************************************************************************
1687
1688// MySQLSp.java
1689
1690import java.io.InputStream;
1691import java.io.IOException;
1692import java.io.FileInputStream;
1693import java.sql.Connection;
1694import java.sql.DriverManager;
1695import java.sql.SQLException;
1696import java.sql.Statement;
1697import java.sql.CallableStatement;
1698import java.sql.ResultSet;
1699import java.sql.ResultSetMetaData;
1700import java.util.Properties;
1701import java.util.List;
1702import java.util.ArrayList;
1703import java.util.Map;
1704import java.util.HashMap;
1705import java.util.Map.Entry;
1706import java.lang.StringBuilder;
1707import java.sql.Date;
1708import java.sql.Time;
1709import java.sql.Timestamp;
1710import java.sql.Types;
1711
1712class DataAccessObject {
1713 public Connection getConnection() throws SQLException {
1714 Connection conn = null;
1715 try {
1716 InputStream input = new FileInputStream("config.properties");
1717 Properties connectionProps = new Properties();
1718 connectionProps.load(input);
1719 String url = connectionProps.getProperty("url"); //
1720 String user = connectionProps.getProperty("user"); //
1721 String password = connectionProps.getProperty("password"); //
1722 // conn = DriverManager.getConnection(url); //
1723 conn = DriverManager.getConnection(url, user, password); //
1724 } catch (IOException e) {
1725 e.printStackTrace();
1726 }
1727 return conn;
1728 }
1729
1730 public void testConnection() {
1731 try (Connection conn = getConnection();) {
1732 System.out.println("Connected to MySQL Server successfully.");
1733 } catch (SQLException e) {
1734 e.printStackTrace();
1735 }
1736 }
1737}
1738
1739class MySQLProcedure
1740{
1741 public <T> CallableStatement setParams(CallableStatement statement, Map<Integer, T> params) throws SQLException {
1742 for(Entry<Integer, T> paramsEntry : params.entrySet()) {
1743 int key = paramsEntry.getKey();
1744 statement.setObject(key, paramsEntry.getValue());
1745 }
1746 return statement;
1747 }
1748
1749 public <T> Map<Integer, Object> getOutParams(String query, Map<Integer, T> params, int[] resultRowNumbers) {
1750 Map<Integer, Object> outParams = new HashMap<Integer, Object>();
1751 DataAccessObject dao = new DataAccessObject();
1752 try (Connection conn = dao.getConnection();) {
1753 CallableStatement statement = conn.prepareCall(query);
1754 System.out.println("Connected to MySQL Server successfully.");
1755 statement = setParams(statement, params);
1756 // int result = statement.executeUpdate();
1757 boolean hadResults = statement.execute();
1758 for(int i = 0; i < resultRowNumbers.length; i++) {
1759 outParams.put(i, statement.getObject(resultRowNumbers[i]));
1760 }
1761 } catch (SQLException e) {
1762 e.printStackTrace();
1763 }
1764 return outParams;
1765 }
1766}
1767
1768public class MySQLSp {
1769 public static void main(String args[]) {
1770 MySQLProcedure mysqlProc = new MySQLProcedure();
1771 StringBuilder query = new StringBuilder();
1772 query.append("{ CALL sp(?, ?, ?, ?, ?) }");
1773 Map<Integer, Object> params = new HashMap<Integer, Object>();
1774 params.put(1, 6);
1775 params.put(2, "@shipped");
1776 params.put(3, "@canceled");
1777 params.put(4, "@resolved");
1778 params.put(5, "@disputed");
1779 int[] resultRowNumbers = new int[] {2, 3, 4, 5};
1780 Map<Integer, Object> outParams = mysqlProc.getOutParams(query.toString(), params, resultRowNumbers);
1781 if(outParams != null) {
1782 for(Entry<Integer, Object> outParamsEntry : outParams.entrySet()) {
1783 System.out.println(outParamsEntry.getValue());
1784 }
1785 }
1786 }
1787}
1788
1789// config.properties
1790
1791url=jdbc:mysql://localhost:3306/spdb?useSSL=false
1792user=root
1793password=*******************
1794
1795// $ javac MySQLSp.java
1796// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLSp
1797
1798****************************************************************************************************************************************************/
1799
1800/****************************************************************************************************************************************************
1801
1802// MySQLSpIn.java
1803
1804import java.io.InputStream;
1805import java.io.IOException;
1806import java.io.FileInputStream;
1807import java.sql.Connection;
1808import java.sql.DriverManager;
1809import java.sql.SQLException;
1810import java.sql.CallableStatement;
1811import java.sql.ResultSet;
1812import java.sql.ResultSetMetaData;
1813import java.util.Properties;
1814import java.util.List;
1815import java.util.ArrayList;
1816import java.util.Map;
1817import java.util.HashMap;
1818import java.util.Map.Entry;
1819import java.lang.StringBuilder;
1820import java.sql.Date;
1821import java.sql.Time;
1822import java.sql.Timestamp;
1823
1824class DataAccessObject {
1825 public Connection getConnection() throws SQLException {
1826 Connection conn = null;
1827 try {
1828 InputStream input = new FileInputStream("config.properties");
1829 Properties connectionProps = new Properties();
1830 connectionProps.load(input);
1831 String url = connectionProps.getProperty("url"); //
1832 String user = connectionProps.getProperty("user"); //
1833 String password = connectionProps.getProperty("password"); //
1834 // conn = DriverManager.getConnection(url); //
1835 conn = DriverManager.getConnection(url, user, password); //
1836 } catch (IOException e) {
1837 e.printStackTrace();
1838 }
1839 return conn;
1840 }
1841
1842 public void testConnection() {
1843 try (Connection conn = getConnection();) {
1844 System.out.println("Connected to MySQL Server successfully.");
1845 } catch (SQLException e) {
1846 e.printStackTrace();
1847 }
1848 }
1849}
1850
1851class MySQLProcedure
1852{
1853 public <T> CallableStatement setParams(CallableStatement statement, Map<Integer, T> params) throws SQLException {
1854 for(Entry<Integer, T> paramsEntry : params.entrySet()) {
1855 int key = paramsEntry.getKey();
1856 statement.setObject(key, paramsEntry.getValue());
1857 }
1858 return statement;
1859 }
1860
1861 public <T> Map<Integer, Map<String, String>> getData(String query, Map<Integer, T> params) {
1862 Map<Integer, Map<String, String>> rows = new HashMap<Integer, Map<String, String>>();
1863 ResultSet rs;
1864 DataAccessObject dao = new DataAccessObject();
1865 try (Connection conn = dao.getConnection();) {
1866 CallableStatement statement = conn.prepareCall(query);
1867 System.out.println("Connected to MySQL Server successfully.");
1868 statement = setParams(statement, params);
1869 rs = statement.executeQuery();
1870 int rowCount = 0;
1871 ResultSetMetaData meta = rs.getMetaData();
1872 while(rs.next()) {
1873 int columnCount = meta.getColumnCount();
1874 Map<String, String> columns = new HashMap<String, String>();
1875 for (int i = 1; i <= columnCount; i++) {
1876 String type = meta.getColumnClassName(i);
1877 String key = meta.getColumnName(i);
1878 String value = rs.getString(key);
1879 columns.put(key, value);
1880 }
1881 /*
1882 for(Entry<String, String> columnsEntry : columns.entrySet()) {
1883 System.out.println(columnsEntry.getKey() + ": " + columnsEntry.getValue());
1884 }
1885 System.out.println("Number of Columns(s) = " + columns.size());
1886 */
1887 rowCount++;
1888 rows.put(rowCount, columns);
1889 }
1890 /*
1891 for(Entry<Integer, Map<String, String>> rowsEntry : rows.entrySet()) {
1892 for(Entry<String, String> columnsEntry : rowsEntry.getValue().entrySet()) {
1893 System.out.println(columnsEntry.getKey() + ": " + columnsEntry.getValue());
1894 }
1895 }
1896 System.out.println("Number of Row(s) = " + rows.size());
1897 */
1898 } catch (SQLException e) {
1899 e.printStackTrace();
1900 }
1901 return rows;
1902 }
1903}
1904
1905public class MySQLSpIn {
1906 public static void main(String args[]) {
1907 MySQLProcedure mysqlProc = new MySQLProcedure();;
1908 StringBuilder query = new StringBuilder();
1909 query.append("{ CALL spin(?) }");
1910 Map<Integer, Object> params = new HashMap<Integer, Object>();
1911 params.put(1, 6);
1912 Map<Integer, Map<String, String>> rows = mysqlProc.getData(query.toString(), params);
1913 if (rows != null) {
1914 int rowCount = rows.size();
1915 System.out.println("Number of Row(s) = " + rowCount);
1916 if (rowCount > 0) {
1917 for(Entry<Integer, Map<String, String>> rowsEntry : rows.entrySet()) {
1918 // int columnsCount = 0;
1919 for(Entry<String, String> columnsEntry : rowsEntry.getValue().entrySet()) {
1920 System.out.println(columnsEntry.getKey() + ": " + columnsEntry.getValue());
1921 /*
1922 columnsCount++;
1923 System.out.print(columnsEntry.getValue());
1924 if (columnsCount < rowsEntry.getValue().entrySet().size()) {
1925 System.out.print(" - " );
1926 }
1927 */
1928 }
1929 System.out.println();
1930 }
1931 }
1932 }
1933 }
1934}
1935
1936// config.properties
1937
1938url=jdbc:mysql://localhost:3306/spdb?useSSL=false
1939user=root
1940password=*******************
1941
1942// $ javac MySQLSpIn.java
1943// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLSpIn
1944
1945****************************************************************************************************************************************************/
1946
1947/****************************************************************************************************************************************************
1948
1949// MySQLSpInOut.java
1950
1951import java.io.InputStream;
1952import java.io.IOException;
1953import java.io.FileInputStream;
1954import java.sql.Connection;
1955import java.sql.DriverManager;
1956import java.sql.SQLException;
1957import java.sql.Statement;
1958import java.sql.CallableStatement;
1959import java.sql.ResultSet;
1960import java.sql.ResultSetMetaData;
1961import java.util.Properties;
1962import java.util.List;
1963import java.util.ArrayList;
1964import java.util.Map;
1965import java.util.HashMap;
1966import java.util.Map.Entry;
1967import java.lang.StringBuilder;
1968import java.sql.Date;
1969import java.sql.Time;
1970import java.sql.Timestamp;
1971import java.sql.Types;
1972
1973class DataAccessObject {
1974 public Connection getConnection() throws SQLException {
1975 Connection conn = null;
1976 try {
1977 InputStream input = new FileInputStream("config.properties");
1978 Properties connectionProps = new Properties();
1979 connectionProps.load(input);
1980 String url = connectionProps.getProperty("url"); //
1981 String user = connectionProps.getProperty("user"); //
1982 String password = connectionProps.getProperty("password"); //
1983 // conn = DriverManager.getConnection(url); //
1984 conn = DriverManager.getConnection(url, user, password); //
1985 } catch (IOException e) {
1986 e.printStackTrace();
1987 }
1988 return conn;
1989 }
1990
1991 public void testConnection() {
1992 try (Connection conn = getConnection();) {
1993 System.out.println("Connected to MySQL Server successfully.");
1994 } catch (SQLException e) {
1995 e.printStackTrace();
1996 }
1997 }
1998}
1999
2000class MySQLProcedure
2001{
2002 public <T> CallableStatement setParams(CallableStatement statement, Map<Integer, T> params) throws SQLException {
2003 for(Entry<Integer, T> paramsEntry : params.entrySet()) {
2004 int key = paramsEntry.getKey();
2005 statement.setObject(key, paramsEntry.getValue());
2006 }
2007 return statement;
2008 }
2009
2010 public <T> Map<Integer, Object> getOutParams(String query, Map<Integer, T> params, int[] resultRowNumbers) {
2011 Map<Integer, Object> outParams = new HashMap<Integer, Object>();
2012 DataAccessObject dao = new DataAccessObject();
2013 try (Connection conn = dao.getConnection();) {
2014 CallableStatement statement = conn.prepareCall(query);
2015 System.out.println("Connected to MySQL Server successfully.");
2016 statement = setParams(statement, params);
2017 // int result = statement.executeUpdate();
2018 boolean hadResults = statement.execute();
2019 for(int i = 0; i < resultRowNumbers.length; i++) {
2020 outParams.put(i, statement.getObject(resultRowNumbers[i]));
2021 }
2022 } catch (SQLException e) {
2023 e.printStackTrace();
2024 }
2025 return outParams;
2026 }
2027}
2028
2029public class MySQLSpInOut {
2030 public static void main(String args[]) {
2031 MySQLProcedure mysqlProc = new MySQLProcedure();
2032 StringBuilder query = new StringBuilder();
2033 query.append("{ CALL spinout(?, ?) }");
2034 Map<Integer, Object> params = new HashMap<Integer, Object>();
2035 params.put(1, 4);
2036 params.put(2, "@out_count");
2037 int[] resultRowNumbers = new int[] {2};
2038 Map<Integer, Object> outParams = mysqlProc.getOutParams(query.toString(), params, resultRowNumbers);
2039 if(outParams != null) {
2040 for(Entry<Integer, Object> outParamsEntry : outParams.entrySet()) {
2041 System.out.println(outParamsEntry.getValue());
2042 }
2043 }
2044 }
2045}
2046
2047// config.properties
2048
2049url=jdbc:mysql://localhost:3306/spdb?useSSL=false
2050user=root
2051password=*******************
2052
2053// $ javac MySQLSpInOut.java
2054// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLSpInOut
2055
2056****************************************************************************************************************************************************/
2057
2058/****************************************************************************************************************************************************
2059
2060// MySQLSpInputOutput.java
2061
2062import java.io.InputStream;
2063import java.io.IOException;
2064import java.io.FileInputStream;
2065import java.sql.Connection;
2066import java.sql.DriverManager;
2067import java.sql.SQLException;
2068import java.sql.Statement;
2069import java.sql.CallableStatement;
2070import java.sql.ResultSet;
2071import java.sql.ResultSetMetaData;
2072import java.util.Properties;
2073import java.util.List;
2074import java.util.ArrayList;
2075import java.util.Map;
2076import java.util.HashMap;
2077import java.util.Map.Entry;
2078import java.lang.StringBuilder;
2079import java.sql.Date;
2080import java.sql.Time;
2081import java.sql.Timestamp;
2082import java.sql.Types;
2083
2084class DataAccessObject {
2085 public Connection getConnection() throws SQLException {
2086 Connection conn = null;
2087 try {
2088 InputStream input = new FileInputStream("config.properties");
2089 Properties connectionProps = new Properties();
2090 connectionProps.load(input);
2091 String url = connectionProps.getProperty("url"); //
2092 String user = connectionProps.getProperty("user"); //
2093 String password = connectionProps.getProperty("password"); //
2094 // conn = DriverManager.getConnection(url); //
2095 conn = DriverManager.getConnection(url, user, password); //
2096 } catch (IOException e) {
2097 e.printStackTrace();
2098 }
2099 return conn;
2100 }
2101
2102 public void testConnection() {
2103 try (Connection conn = getConnection();) {
2104 System.out.println("Connected to MySQL Server successfully.");
2105 } catch (SQLException e) {
2106 e.printStackTrace();
2107 }
2108 }
2109}
2110
2111class MySQLProcedure
2112{
2113 public <T> CallableStatement setParams(CallableStatement statement, Map<Integer, T> params) throws SQLException {
2114 for(Entry<Integer, T> paramsEntry : params.entrySet()) {
2115 int key = paramsEntry.getKey();
2116 statement.setObject(key, paramsEntry.getValue());
2117 }
2118 return statement;
2119 }
2120
2121 public <T> Map<Integer, Object> getOutParams(String query, Map<Integer, T> params, int[] resultRowNumbers) {
2122 Map<Integer, Object> outParams = new HashMap<Integer, Object>();
2123 DataAccessObject dao = new DataAccessObject();
2124 try (Connection conn = dao.getConnection();) {
2125 CallableStatement statement = conn.prepareCall(query);
2126 System.out.println("Connected to MySQL Server successfully.");
2127 statement = setParams(statement, params);
2128 // int result = statement.executeUpdate();
2129 boolean hadResults = statement.execute();
2130 for(int i = 0; i < resultRowNumbers.length; i++) {
2131 outParams.put(i, statement.getObject(resultRowNumbers[i]));
2132 }
2133 } catch (SQLException e) {
2134 e.printStackTrace();
2135 }
2136 return outParams;
2137 }
2138}
2139
2140public class MySQLSpInputOutput {
2141 public static void main(String args[]) {
2142 MySQLProcedure mysqlProc = new MySQLProcedure();
2143 StringBuilder query = new StringBuilder();
2144 query.append("{ CALL spinputoutput(?, ?) }");
2145 Map<Integer, Object> params = new HashMap<Integer, Object>();
2146 params.put(1, 3);
2147 params.put(2, "@level");
2148 int[] resultRowNumbers = new int[] {2};
2149 Map<Integer, Object> outParams = mysqlProc.getOutParams(query.toString(), params, resultRowNumbers);
2150 if(outParams != null) {
2151 for(Entry<Integer, Object> outParamsEntry : outParams.entrySet()) {
2152 System.out.println(outParamsEntry.getValue());
2153 }
2154 }
2155
2156 }
2157}
2158
2159// config.properties
2160
2161url=jdbc:mysql://localhost:3306/spdb?useSSL=false
2162user=root
2163password=*******************
2164
2165// $ javac MySQLSpInputOutput.java
2166// $ java -cp "mysql-connector-java-8.0.12.jar;.;" MySQLSpInputOutput
2167
2168****************************************************************************************************************************************************/