| |
SQL i Java
|
| |
|
| |
El model relacional de bases de
dades: |
| |
|
 |
SQL
és el llenguatge de dades més
universal. Es fonamenta en el model relacional
de descripció de dades, una de les teories sobre emmagatzemament
d'informació amb més seguidors. Els sistemes
relacionals descriuen les dades en forma de
taules bidimensionals organitzades en files i columnes. Les files
són els registres o tuples
i les columnes són els camps o atributs.
Les taules han de tenir una clau
primària, que identifiqui de manera única el registre
a la taula (per exemple, el camp
id de la taula
usuaris a la base
de dades biblioteca).. Cada taula
es pot relacionar amb les altres a través de les claus
externes: una taula conté
informació d'una altra perquè emmagatzema la seva clau
primària. Per exemple, la taula
"llibres" pot contenir un camp
que es digui "lector". En aquest
camp no hi posaré totes les dades
del lector, sinó la clau primària
de la taula d'usuaris de la biblioteca: |
| |
|
| |
| Taula "Llibres": |
| Id |
Títol |
Autor |
Lector |
| 1 |
Java 2
|
Froufe, Agustín |
2 |
| 2 |
Java |
Bishop, Judy |
|
| 3 |
Piensa en Java |
Eckel, Bruce |
3 |
| |
| Taula "Lectors":
|
| Id |
Nom |
Cognom1 |
Cognom2 |
| 1 |
Josep |
Capdevila |
Peris |
| 2 |
Anna |
Solans |
Mesalles |
| 3 |
Marc |
Teulé |
Fitó |
|
| |
|
| |
En aquest cas, el libre "Java 2"
d'"Agustín Froufe"
el té el lector "2"
que és "Anna Solans Mesalles".
Quan es va dissenyar el llenguatge SQL
es buscava un llenguatge que fos entenedor per a usuaris poc experts en
bases de dades. Havia de ser natural,
amb una estructura similar a l'anglès escrit, que fes possible
que l'usuari "preguntés" directament a la base
de dades.
Per exemple, si necessitem saber saber el nom del lector del llibre d'"Agustín
Froufe", SQL ho resol amb
aquesta frase:
| SELECT LECTOR.NOM FROM
LECTORS,LLIBRES WHERE LECTORS.ID=LLIBRES.LECTOR AND LLIBRES.ID=1 |
és a dir: "retorna el camp nom
des de la taula de lectors, tot considerant que:
- el camp lector de la
taula llibres és igual al camp id de la taula lectors i, a més,
- el camp id de la taula de llibres és
l'1"
|
 |
Si heu de treballar amb bases de
dades, és molt convenient que feu una repassada a algun text
o curs de disseny de bases de dades relacionals.
Podeu fer un cop d'ull al curs D50 o al curs D104.
També us pot ser útil algun curs d'SQL
dels que estan disponibles a Internet. Aquests en són bons exemples:
de l'Aula
Click i de la Universitat
de Navarra. |
| |
|
| |
Sentències SQL de supervivència |
| |
|
| |
Donat que l'objectiu d'aquest curs no és
pas l'estudi de les bases de dades us facilitem,
només, algunes consultes SQL que
fan les feines més importants de consulta
i actualització sobre una base de
dades. Veurem després com aplicar-les amb Java. |
| |
|
| |
| Sentències de consulta: |
| Objectiu: |
Sentència: |
| Obtenir tota la informació d'una sola
taula: |
select * from lectors |
| Obtenir informació d'algunes columnes
d'una taula |
select id,nom from lectors |
| Obtenir informació d'una taula
posant alguna condició a un
camp. Retorna els lectors que es
diuen "Angel". |
select * from lectors where nom='Angel' |
| Obtenir informació d'una taula
posant alguna condició no estricta
a un camp. Retorna els lectors que
es diuen "Angel", "Angela",
"Angelines", etc. |
select * from lectors where nom like
'Angel%' |
| Obtenir informació d'una taula
posant alguna condició no estricta
a un camp. Retorna els lectors que
es diuen "Angelina",
"Martina", "Cristina",
etc. |
select * from lectors where nom like
'%ina' |
| Obtenir la suma de registres
d'una taula |
select count(*) from lectors. |
| Obtenir la suma de registres
d'una taula amb condicions |
select count(*) from lectors where
nom='Angel' |
| Obtenir la llista dels noms i el nombre de vegades
que apareixen a la taula de lectors |
select nom,count(*) from lectors
group by nom |
| Obtenir la llista ordenada segons un camp
d'una taula |
select * from lectors order by cognom1 |
| Obté tots els
camps d'una taula i alguns
camps d'una altra. En aquest cas retorna tots els camps
de la taula "llibres"
i, per cada registre, el nom i cognoms
dels lectors. |
Select llibres.*, lectors.id, lectors.cognom1,
lectors.nom from lectors,llibres where lectors.id=llibres.lector |
|
| |
|
| |
| Sentencies d'actualització: |
| Objectiu: |
Sentència: |
| Afegir un camp |
insert into lectors (id,nom,cognom1,cognom2)
values (7,"Miquel","Mesalles","Bernadó") |
| Esborrar tots els registres
d'una taula |
delete from lectors |
| Esborrar condicionalment
registres d'una taula |
delete from lectors where id=5 |
| Esborrar els
llibres el lectors dels quals es diuen 'Angel' |
delete from llibres where lector
in (select id from lectors where nom='Angel') |
| Actualitzar un grup
de registres. Atenció!,
a SQL les consultes
update poden afectar el mateix nombre de registres
com posem a la nostra condició.
Actualitza a 'ANGEL' tots
els registres que tinguin de nom
'Angel' |
update lectors set nom='ANGEL' where
nom='Angel' |
| Actualitza un registre
passant com a condició la
clau primària. |
update lectors set cognom1='Martí'
where id=4 |
| Actualitza més
d'un camp a la vegada |
update lectors set soci='S', carnet='N'
where id=25 |
|
| |
|
| |
Un programa per a consultar, esborrar i actualitzar
registres: |
| |
|
 |
Ara escriurem un programa que ens permetrà experimentar amb diverses
accions sobre una base de dades: afegir
registres, esborrar-ne, actualitzar
informació i llistar-la.
Escriviu, compileu i executeu el següent programa "GestioUsuaris.java":
|
| |
|
| |
import java.sql.*;
import java.io.*;
public class GestioUsuaris {
public static void main (String[] args)
throws IOException {
String urlDades
= "jdbc:odbc:biblioteca";
String usuari =
"";
String clau = "";
Connection connexio=null;
Statement pregunta=null;
ResultSet resposta=null;
int opcio=-1;
BufferedReader entrada=null;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try
{
connexio=DriverManager.getConnection(urlDades,
usuari,
clau);
System.out.println("Usuaris
de la biblioteca");
System.out.println("------------------------\n");
while
(opcio!=0) {
entrada
= new BufferedReader(
new
InputStreamReader(System.in));
System.out.println(
"\n\nElegiu
una tasca: 0 per sortir");
System.out.println("1-
Insertar Usuari\n"+
"2-
Esborrar Usuari\n"+
"3-
Actualitzar Usuari\n"+
"4-
Llistar Usuaris\n\n");
try
{
opcio
= Integer.parseInt(
entrada.readLine());
}
catch (Exception e) {
System.out.println(
"Trieu
un no. entre 0 i 4");
opcio=-1;
}
switch
(opcio) {
case
1: AfegirUsuari afegir =
new AfegirUsuari(connexio);
break;
case
2: EsborrarUsuari esborrar =
new
EsborrarUsuari(connexio);
break;
case
3: ActualitzarUsuari actualitzar =
new ActualitzarUsuari(connexio);
break;
case
4: LlistarUsuaris llista =
new LlistarUsuaris(connexio);
break;
}
}
}
catch (SQLException e) {
System.out.println(e.getMessage());
}
finally {
try
{
connexio.close();
}
catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}
}
class AfegirUsuari {
Connection connexio=null;
public AfegirUsuari (Connection connexio)
throws IOException {
this.connexio=connexio;
inserta();
}
private void inserta() throws IOException
{
String text="";
String nom="";
String cognom1="";
String cognom2="";
BufferedReader entrada
= new BufferedReader(
new
InputStreamReader(System.in));
System.out.println("Nom
de l'usuari, 0 per cancel.lar");
text=(entrada.readLine());
if
(text.equals("0")) return; else nom=text;
System.out.println("Primer
cognom de l'usuari,"+
" 0 per cancel.lar");
text=(entrada.readLine());
if
(text.equals("0")) {
return;
}
else {
cognom1=text;
}
System.out.println("Segon
cognom de l'usuari, "+
"
0 per cancel.lar");
text=(entrada.readLine());
if
(text.equals("0")) {
return;
}
else {
cognom2=text;
}
try
{
PreparedStatement
pregunta =
connexio.prepareStatement("insert
"+
"into usuaris "+
"(nom,cognom1,"+
"cognom2) "+
"values
(?,?,?)");
pregunta.setString(1,nom);
pregunta.setString(2,cognom1);
pregunta.setString(3,cognom2);
pregunta.executeUpdate();
System.out.println("Insertat
usuari "+
nom+
"
"+
cognom1+
" "+
cognom2);
pregunta.close();
}
catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}
class EsborrarUsuari {
Connection connexio;
public EsborrarUsuari (Connection connexio)
throws IOException {
this.connexio=connexio;
esborra();
}
private void esborra () throws IOException
{
PreparedStatement
pregunta = null;
BufferedReader entrada
= new BufferedReader(
new
InputStreamReader(System.in));
System.out.println("Introduiu
el codi numeric de l'usuari");
String usuari=entrada.readLine();
try
{
pregunta=connexio.prepareStatement("delete
"+
"from
usuaris"+
"where
id=?");
pregunta.setString(1,usuari);
pregunta.executeUpdate();
System.out.println("Esborrat
l'usuari "+usuari);
pregunta.close();
}catch(SQLException
e) { }
}
}
class ActualitzarUsuari {
Connection connexio=null;
public ActualitzarUsuari(Connection connexio)
throws IOException {
this.connexio=connexio;
actualitza();
}
private void actualitza() throws IOException
{
BufferedReader entrada
= new BufferedReader(
new
InputStreamReader(System.in));
String text="";
String usuari="";
String nom="";
String cognom1="";
String cognom2="";
System.out.println("Codi
numeric de l'usuari");
text=(entrada.readLine());
if
(text.equals("0")) {
return;
}
else {
usuari=text;
}
System.out.println("Nom
de l'usuari, 0 per cancel.lar");
text=(entrada.readLine());
if
(text.equals("0")) {
return;
}
else {
nom=text;
}
System.out.println("Primer
cognom de l'usuari,"+
"
0 per cancel.lar");
text=(entrada.readLine());
if
(text.equals("0")) {
return;
}
else {
cognom1=text;
}
System.out.println("Segon
cognom de l'usuari,"+
" 0 per cancel.lar");
text=(entrada.readLine());
if
(text.equals("0")) {
return;
}
else {
cognom2=text;
}
try
{
PreparedStatement
pregunta =
connexio.prepareStatement("update
usuaris"+
"
set nom=?,"+
"cognom1=?,"+
"cognom2=?
"+
"where
id=?");
pregunta.setString(1,nom);
pregunta.setString(2,cognom1);
pregunta.setString(3,cognom2);
pregunta.setString(4,usuari);
pregunta.executeUpdate();
System.out.println("Actualitzat
usuari "+
nom+"
"+
cognom1+"
"+
cognom2);
pregunta.close();
}catch(SQLException
e) {
System.out.println(e.getMessage());}
}
}
class LlistarUsuaris {
Connection connexio=null;
public LlistarUsuaris (Connection connexio)
{
this.connexio=connexio;
llista();
}
private void llista() {
Statement pregunta
= null;
ResultSet resposta
= null;
try
{
pregunta
= connexio.createStatement();
resposta
= pregunta.executeQuery("select * from "+
"usuaris
order "+
"by
cognom1");
System.out.println("\n\nLlista
usuaris "+
"de
la biblioteca");
System.out.println("----------------------------\n");
while
(resposta.next()) {
System.out.println(
resposta.getInt("id")+"
"+
resposta.getString("nom")+"
"+
resposta.getString("cognom1")+
" "+
resposta.getString("cognom2"));
}
System.out.println("----------------------------\n");
pregunta.close();
}
catch (SQLException e) {
}
}
}
|
|
| |
|
|
Comentem una mica el funcionament del programa:
- La classe principal
del programa enregistra el drivers
odbc:jdbc i connecta amb la base
de dades. Seguidament entra en un cicle
que presenta cinc opcions de menú. Una d'elles, el zero, provoca
el tancament de la connexió
amb les dades i la sortida del programa. Tot i que, en teoria, al tancar
l'aplicació també es tanca la connexió, molts drivers
no funcionen així i retenen els recursos de la connexió
si no es tanca explícitament,
fins i tot després de sortir del programa. Per tant, quan ja
no necessitem una connexió,
l'hem de tancar amb
connexio.close(). El nostre programa funciona
amb una connexió que s'obre a l'inici i es tanca al final. En
aplicacions grans, amb molta concurrència (com ara una pàgina
web), faríem anar un "pool"
de connexions, una utilitat que mantindria sempre obertes vàries
connexions i en serviria la primera que quedés lliure.
- Un cop connectats amb la base
de dades, triem alguna de les opcions del
menú. Podem donar d'alta usuaris,
esborrar-los de la base de dades,
actualitzar-ne les dades
i llistar-los.
Cadascuna d'aquestes accions la fa una classe
auxiliar. Quan necessitem alguna d'aquestes
classes (EsborrarUsuari,
AfegirUsuari, ActualitzarUsuari,
LlistarUsuaris) la instanciem
tot passant-li com a paràmetre
la connexió
que tenim activa.
- Observeu les diferents estratègies a seguir
segons el tipus de consulta
- Obtenir dades
sense paràmetres:
Statement pregunta
= connexio.createStatement();
ResultSet resposta = pregunta.executeQuery(
"select * from usuaris order by cognom1");
while (resposta.next()) {} |
- Esborrar dades
passant paràmetres:
PreparedStatement
pregunta =
connexio.prepareStatement("delete from usuaris where
id=?");
pregunta.setString(1,usuari);
pregunta.executeUpdate(); |
- Insertar dades
passant paràmetres:
PreparedStatement
pregunta =
connexio.prepareStatement(
"insert into usuaris (nom,cognom1,cognom2)
values (?,?,?)");
pregunta.setString(1,nom);
pregunta.setString(2,cognom1);
pregunta.setString(3,cognom2);
pregunta.executeUpdate(); |
- Actualitzar dades
passant paràmetres:
PreparedStatement
pregunta =
connexio.prepareStatement(
"update usuaris set nom=?,cognom1=?,cognom2=?
where id=?");
pregunta.setString(1,nom);
pregunta.setString(2,cognom1);
pregunta.setString(3,cognom2);
pregunta.setString(4,usuari);
pregunta.executeUpdate(); |
- Observeu que, per a les consultes
amb paràmetres, no utilitzem Statement
sinó PreparedStatement.
Els llocs de la consulta que han de ser substituïts
pel paràmetre els indiquem amb
el símbol '?'.
Seguidament, per assignar el valor al paràmetre
fem pregunta.setString(1,nom).
Això substitueix el primer símbol '?'
del la consulta SQL
pel contingut de la variable
nom. Si hem
de passar un nombre enter
hem d'escriure pregunta.setInt(1,numero).
- Quan la consulta
és d'actualització
resolem Statement
amb pregunta.executeUpdate()
i, quan es tracta d'una consulta
d'obtenció de dades fem pregunta.executeQuery().
- Les preguntes,
siguin Statement o PreparedStatement,
també s'han de tancar explícitament
després d'utilitzar-les, per tal d'alliberar recursos.
|
| |
|
| |
|
| |
|
 |
| |
|
| |
|
