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