Tuesday, December 19, 2017

Scala access PostgresQL database complete demo

// Connect o PostgresQL

$>
brew info postgres

// Start manually:

pg_ctl -D /usr/local/var/postgres start

// or, write stats to log,
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

// Stop manually:

pg_ctl -D /usr/local/var/postgres stop

// Status

pg_ctl -D /usr/local/var/postgres status


createdb scala_db
createuser scala_user
psql -U scala_user -d scala_db

SQL>

create table emps
(
id integer,
last_name varchar(100),
email varchar(200),
gender varchar(10),
department varchar(100),
start_date date,
salary integer,
job_title varchar(100),
region_id int,
primary key (id)
);

insert into emps values (1,'Kelley','rkelley0@sours.getString("country")ndcloud.com','Female','Computers','10/2/2009',67470,'Structural Engineer',2);
insert into emps values (2,'Armstrong','sarmstrong1@infoseek.co.jp','Male','Home','3/31/2008',71869,'Financial Advisor',2);
insert into emps values (3,'Carr','fcarr2@woothemes.com','Male','Home','7/12/2009',101768,'Recruiting Manager',3);
insert into emps values (4,'Murray','jmurray3@gov.uk','Female','Jewelery','12/25/2014',96897,'Desktop Support Technician',3);
insert into emps values (5,'Ellis','jellis4@sciencedirect.com','Female','Home','9/19/2002',63702,'Software Engineer III',7);


$>

// scala -classpath ~/app/lib/postgresql-42.1.1.jre6.jar
bin/spark-shell -classpath ~/app/lib/postgresql-42.1.1.jre6.jar

// Scala code,
import java.sql.Driver
import java.sql.DriverManager
import java.sql.Connection

val driver = "org.postgresql.Driver"
val url = "jdbc:postgresql://localhost/scala_db?user=scala_user"
Class.forName(driver)
var connection: Connection = null

connection = DriverManager.getConnection(url)
val statement = connection.createStatement()
val resultSet = statement.executeQuery("select * from emps limit 5")
resultSet.next
resultSet.getString("last_name")
resultSet.getString("department")

while ( resultSet.next() ) {
val last_name = resultSet.getString("last_name")
val dept = resultSet.getString("department")
printf("%s: %s \n", dept, last_name)
}

// Prepare SQL statement
val queryStr = "select * from emps where department = ? "
val ps = connection.prepareStatement(queryStr)
ps.setString(1, "Home")
val rs = ps.executeQuery()

while ( rs.next() ) {
val last_name = rs.getString("last_name")
val dept = rs.getString("department")
val startDate = rs.getString("start_date")
printf("%s: %s \t %s \n", dept, last_name, startDate)
}

No comments: