LSINF1124 Projet de programmation

(1)

LSINF1124 — Projet de programmation

Database Programming with JavaTM

Sébastien Combéfis

University of Louvain (UCLouvain) Louvain School of Engineering (EPL)

(2)

Introduction

A databaseis a collection of records, logically related A Database Management System(DBMS) is software that organizes the storage of data

A database modelis the structure of a database (flat, relational, object, . . .)

A query languageused to make queries on a database (SQL, XQuery, Datalog, . . .)

(3)

Java DB : Apache Derby

“Java DB is Sun’s supported distribution of the open source Apache Derby 100% Java technology database. It is fully transactional, secure, easy-to-use, standards-based ? SQL, JDBC API, and Java EE — yet small, only 2.5 MB.”

A Java RDBMS, that can be embedded in Java programs

(4)

SQL query language

SQL (Structured Query Language) used toquery database

Let’s discover SQL using the Apache Derby’s ijtool

1 $java -jar /Users/combefis/javadb 10.5.3.0/lib/derbyrun.jar ij

2 version ij10.5

3 ij>

To get help about allowed commands, typehelp;

(5)

Database

The first step is to open a connection to the database Can create a new database with create=true attribute

1 ij>connect ’jdbc :derby :MyNewDB ;create=true’ ;

2 ij>show connections ;

3 CONNECTION0∗−jdbc:derby:MyNewDB

4 ∗=connexion en cours

5 ij>disconnect CONNECTION0 ;

6 ij>show connections ;

7 Aucune connexion disponible.

8 ij>exit ;

9 10 $ls

11 MyNewDB derby.log

(6)

Table

A database is composed oftables

A table is created with the CREATE TABLE SQL command

1 ij>connect ’jdbc :derby :MyNewDB’ ;

2 ij>CREATE TABLE students 3 >(

4 > firstname VARCHAR(50),

5 > lastname VARCHAR(50),

6 > sex CHAR(1)

7 >) ;

8 0lignes insérées/mises à jour/supprimées

9

10 ij>DESCRIBE students ;

12 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

(7)

Populate the table

Canadd record in a table with INSERT INTO SQL command

Canget record from a table withSELECT FROM SQL command

1 ij>INSERT INTO students VALUES (’Florence’, ’Turine’, ’F’) ;

2 1ligne insérée/mise à jour/supprimée

3

4 ij>INSERT INTO students VALUES (’Nicolas’, ’Laurent’, ’M’) ;

6

7 ij>SELECT * FROM students ;

8 FIRSTNAME |LASTNAME |SEX

9 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

10 Florence |Turine |F 11 Nicolas |Laurent |M 12 2lignes sélectionnées

(8)

Query the table

Queries done withSELECT FROM SQL command Can use WHERE clause to specify conditions

1 >ijSELECT firstname, lastname FROM students WHERE sex=’F’ ;

2 FIRSTNAME |LASTNAME

3 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

4 Florence |Turine 5 1ligne sélectionnée

6

7 >ijSELECT * FROM students WHERE firstname LIKE ’Thor%’ ;

8 FIRSTNAME |LASTNAME

9 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

(9)

Update a row

Update a row with theUPDATESQL command

1 >ijINSERT INTO students (firstname, sex) VALUES (’Thoralf ’, ’M’) ;

3

6 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

7 Thoralf |NULL |M 8 1ligne sélectionnée

9

10 >ijUPDATE students SET lastname=’G.’ WHERE firstname=’Thoralf ’ ;

12

15 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

16 Thoralf |G. |M 17 1ligne sélectionnée

(10)

Delete a row

Delete a rowwith theDELETE SQL command

1 >ijINSERT INTO students VALUES (’Olivier’, ’Bonaventure’, ’M’) ;

3

4 ij>SELECT * FROM students ;

13 >ijDELETE FROM students WHERE firstname=’olivier’ AND lastname=’bonaventure’ ;

15

16 >ijSELECT COUNT(*) AS nb FROM students ;

17 NB

18 −−−−−−−−−−−

(11)

And now, in Java ! I

Loading the driver

1 try 2 {

3 Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

4 System.out.println("Driver loaded");

5 }

6 catch(ClassNotFoundExceptionexception)

7 {

8 System.err.println("Unable to find the driver : "+exception.getMessage());

9 }

The driver depends on the DB you want to connect to

PostgreSQL :org.postgresql.Driver

(12)

And now, in Java ! II

Connectingto the database and closingthe connection

1 StringdbName="/Users/combefis/MyNewDB"; 2

3 try 4 {

5 Connectionconn=

6 DriverManager.getConnection("jdbc:derby:"+dbName+";create=true");

7

8 // [...]

9

10 conn.close();

11 }

12 catch(SQLExceptionexception)

13 {

14 System.err.println("SQL error : "+exception.getMessage());

(13)

And now, in Java ! III

Querying the database and retrieving results

1 Statements=conn.createStatement();

2

3 ResultSetres=s.executeQuery("SELECT∗FROM students");

4 while(res.next()) 5 {

6 System.out.printf("%s %s %s\n",res.getString(1),

7 res.getString(2),res.getString("sex"));

8 }

9 res.close(); 10

11 s.close();

Can get values of a row with methods from ResultSet: by

column number (first = 1) or by column name Don’t forget res.close() to free resource

(14)

And now, in Java ! IV

Shutting downthe database

1 booleanSQLexc=false;

2 try 3 {

4 DriverManager.getConnection("jdbc:derby:;shutdown=true");

5 }

6 catch(SQLExceptionexception)

7 {

8 SQLexc=exception.getSQLState().equals("XJ015");

9 }

10

11 if(SQLexc)

12 {

13 System.out.println("Database shut down normally");

14 }

15 else 16 {

17 System.out.println("Database did not shut down normally");

(15)

Updating the database

With a Statement, use

executeQuerytogeta result (returns aResultSet)

executeUpdateto perform anupdate (returns aint)

1 Statements=conn.createStatement();

2

3 intr=s.executeUpdate("DELETE FROM students"); 4 System.out.printf("%d row(s) deleted\n",r);

5

(16)

Prepared statements and batch processing

UsePreparedStatement to build a not complete statement

that need to be filled

Can prepared queries that will be executed in abatch

1 PreparedStatementpstmt=

2 conn.prepareStatement("INSERT INTO students VALUES (?, ?, ?)");

3

4 for(inti= 0;i<firstnames.length;i++)

5 { 6 pstmt.setString(1,firstnames[i]); 7 pstmt.setString(2,lastnames[i]); 8 pstmt.setString(3,sexs[i]); 9 pstmt.addBatch(); 10 } 11 12 int[]rs=pstmt.executeBatch();

(17)

SQLData

or how to define a new type

(18)

Primary key

Rowsindexed for better search performance Should definedkeysand indexes

Primary key should uniquely identify a row

1 >ijCREATE TABLE students

2 >(

3 > id_student INTEGER NOT NULL

4 > PRIMARY KEY GENERATED ALWAYS AS IDENTITY

5 > (START WITH 1, INCREMENT BY 1),

6 > firstname VARCHAR(50),

7 > lastname VARCHAR(50),

8 > sex CHAR(1) NOT NULL

9 >) ;

(19)

Establishing relations between tables

We have a table describing students

We want to add, for each student, the other students with which they are in a love relationship

One student may have several active partners !

id_students firstname lastname sex partners

1 Florence Turine F P.

2 Nicolas Laurent M Pénéloppe

3 Thoralf G. M

4 P. NULL M Florence

5 Péneloppe X F Nicolas, Nicole, Thoralf, Florence

6 Nicole Y F Pénéloppe

(20)

Establishing relations between tables

We have a table describing students

We want to add, for each student, the other students with which they are in a love relationship

One student may have several active partners !

id_students firstname lastname sex partners

1 Florence Turine F 4 2 Nicolas Laurent M 6 3 Thoralf G. M 4 P. NULL M 1 5 Péneloppe X F 2, 6, 3, 1 6 Nicole Y F 5

(21)

Foreign key I

1 >ijCREATE TABLE relations

2 >(

3 > p1 INTEGER NOT NULL,

4 > p2 INTEGER NOT NULL,

5 > PRIMARY KEY (p1, p2),

6 > FOREIGN KEY (p1) REFERENCES students(id_student),

7 > FOREIGN KEY (p2) REFERENCES students(id_student)

8 >) ;

9 0lignes insérées/mises à jour/supprimées

PRIMARY KEY constraint defines the table’sprimary key

FOREIGN KEY constraint states that a key in that table refers to a key in another one

(22)

Foreign key II

The constraints are checked when the tables are updated

1 >ijINSERT INTO relations VALUES (42, 69) ;

2 ERREUR23503 :INSERT sur la table’RELATIONS’a entrainé la violation de la

3 contrainte de clé externe’SQL100228172518851’pour la clé(42).

4 L’instruction a été annulée.

(23)

Foreign key III

(24)

Foreign key IV

Here are the two tables

id_students firstname lastname sex

1 Florence Turine F 2 Nicolas Laurent M 3 Thoralf G. M 4 P. NULL M 5 Péneloppe X F 6 Nicole Y F p1 p2 1 4 2 6 4 1 5 2 5 6 5 3 5 1 6 5

(25)

Making joins

Making queries on several tables, joining on some columns

1 >ijSELECT S1.firstname, S2.firstname FROM relations, students S1, students S2

2 WHERE p1=S1.id_student AND p2=S2.id_student ;

(26)

Resources and References I

JDBC : Practical Guide for JavaTM Programmers (2001)

Gregory Speegle Morgan Kaufmann 978-1558607361

JavaTM Database Best Practices (2003) George Reese

O’Reilly 978-0596005221

(27)

Resources and References II

Sun – JavaTM tutorial

The Sun tutorial on JDBC

— http://java.sun.com/docs/books/tutorial/jdbc/ Apache – Derby tutorial

How to use derby : install it, use theijtool and query databases, embedded and network derby