Creació de base de dades PostgreSQL amb usuari associat

Crearem una base de dades anomenada test amb un usuari associat de nom test i contrasenya test.

Establim contrasenya a l'usuari postgres (seguretat):

root@edt:~# sudo passwd postgres
Enter new UNIX password:
Retype new UNIX password:
passwd: contraseña actualizada correctamente

Entrem com a usuari postgres i creem un nou usuari, l'usuari test:

usuari@edt:~$ su postgres
Password:
sh-3.1$ createuser
Ingrese el nombre del rol a agregar: test
¿Será el nuevo rol un superusuario? (s/n) n
CREATE ROLE
sh-3.1$

Creem la base de dades test:

sh-3.1$ createdb test
CREATE DATABASE

Entrem a la consola postgres i posem la password test a l'usuari test:

sh-3.1$ psql test
Bienvenido a psql 8.1.5, la terminal interactiva de PostgreSQL.
Digite:  \copyright para ver los términos de distribución
       \h para ayuda de comandos SQL
       \? para ayuda de comandos psql
       \g o or termine con punto y coma para ejecutar una consulta
       \q para salir
test=# ALTER USER test PASSWORD 'test';

Finalment, hauríem de crear les taules que pertoquessin la base de dades inserint-hi les dades peritnents.

Per fer que l'autenticació de l'usuari sigui obligatòria (i encriptada) hem d'editar el fitxer de configuració de postgreSQL pg_hba.conf:

# "local" is for Unix domain socket connections only
local   all     all                                     md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

Si volem esborrar la base de dades:

sh-3.1$ dropdb test

Si volem esborrar l'usuari:

sh-3.1$ dropuser test

Si tenim un fitxer sql amb la creació de la base de dades, podem recuperar-lo del la següent manera:

sh-3.1$ psql -d una_altra_bbdd_existent -f nom_fitxer.sql

Existeixen algunes aplicacions gràfiques per gestionar les bases de dades Postgres, com per exemple pgadmin i phppgadmin.

JDBC

El primer que hem de fer per poder connectar la nostra aplicació web amb una base de dades PosgreSQL és posar el driver de PostgreSQL al servidor Tomcat. El driver ens el podem descarregar de http://jdbc.postgresql.org/download.html.

Hem de copiar el fitxer postgresql-*.jar al directori common/lib de Tomcat.

Connexió a base de dades

Podeu consultar com fer una connexiótípica a base de dades en aquest document.

PreparedStatement

Si volem evitar atacas web del tipus SQL Injection, hem de reemplaçar sla classe Satement  per una classe més sofisicada: PreparedStatement.

Un objecte de tipus Prepared Statement es crea de la següent manera:

PreparedStatement pstmt = con.prepareStatement(sentència_sql_amb_?_enlloc dels valors);

Per donar-li valors als interrogants farem servir:

pstmt.setTipus(posicio,valor)

Posm un exemple dús amb la sentència sq insert:

String nums[] = {"un","dos","tres"};
PreparedStatement ps = con.prepareStatement("insert into nums values (?,?)");
for(int i=0; i<3;i++){
    ps.setInt(1,i+1);
    ps.setString(2,nums[i])
    ps.executeUpdate();
}

PreparedStatement amb ResultSet

Quan creem un objecte de tipus PreparedStatement, podem afeigr dos paràmetres que ens serviran per treballar amb objectes de tipus ResultSet:

con.prepareStatement(sql_query, tipusResultSet, concurrenciaResultSet)

Valors de tipusResultSet:

• TYPE_FORWARD_ONLY (el cursor només es mou cap endavant),
• TYPE_SCROLL_INSENSITIVE (el cursosr es mou, però no mostra els canvis que s'han realitzat a la bbdd),
• TYPE_SCROLL_SENSITIVE (el cursosr es mou i es mostren els canvis que s'han realitzat a la bbdd)

Valors de concurrenciaResultSet:

• CONCUR_READ_ONLY (el conjunt de dades no es pot actualitzar),
• CONCUR_UPDATABLE (es pot actualitzar)

Si volem simular que tenim la taula de la base de dades posarem:

PreparedStatement ps = con.prepareStatement(sql_query,TYPE_SCROLL_SENSITIVE,CONCUR_UPDATABLE)
ResultSet rs = ps.executeQuery(); 

• Per moure'ns pel ResultSet:

rs.first() //es mou al primer registre
rs.next() //es mou al segúent registre
rs.previous() //es mou al registre anterior
rs.moveToCurrentRow() //es mou al registre on estàvem (després d'inserir)

Per obtenir un valor del ResultSet:

rs.getXXX(num_columna) //obtenim el valor de la columna com a tipus XXX

Per modificar files:

rs.updateXXX(num_col, valor) //modifica el valor de la num_col en el registre que estem, però NO modifica la bbdd
rs.updateRow() //modifica el valor a la bbdd de la fila on estic ara

Per esborrar files:

rs.deleteRow()    //esborra la fila on estic I la de la BBDD

Per inserir files:

rs.moveToInsertRow() //ens "col·loquem" per inserir un nou registre
rs.updateXXX(num_col, valor);
...
rs.insertRow(); : insereix tant al resultset com a la BBDD
rs.MoveToCurrentRow

PooledConnections

Objectiu: Aconseguir no haver d'obrir cada cop una connexió, sinó tenir-ne vàries d'obertes i anar-les oferint als clients que les demanin.

Per tal d'usar pooled Connections hem d'usar JNDI. Per defirnir les dades de la base de dades en el servidor mitjançant JDNI:
1.- Podem posar el recurs tant a server.xml com a META-INF/context.xml
2.- Si ho fem a server.xml, posarem les dades de la bbdd en el context, si ho fem a context.xml, ho posarem dins la marca <Context>:

<Context>
	<Resource name="jdbc/nom_recurs" 
		type="javax.sql.DataSource"
		driverClassName="org.postgresql.Driver"
		url="jdbc:postgresql://localhost/nom_recurs"
		username="usuari_bdd"
		password="contrasenya_bdd"
		maxActive="20"
		maxIdle="30000"
		maxWait="100"
		removeAbandoned="true"
		removeAbandonedTimeout="60"  />
</Context>

Descripció dels atibuts:

• name: nom del recurs
• type: tipus de recurs
• driverClassName: driver
• url: uri de conexió
• username: usuari amb el qual ens connectarem a la bdd
• password: contrasenya de l'usuari
• maxActive: màxim número de connexions actives en un moment donat
• maxIdle: màxim número de connexions que poden quedar a l'espera
• maxWait: màxim número de milisegons a esperar a tenir una connexió (si no s'aconsegueix es llença una excepció)
• removeAbandoned="true"  farà que les connexions idle siguin considerades abandonades i les tancarà per posar a disposició
• removeAbandonedTimeout="60" número de segons que fa que una connexió es consideri abandonada

3.- En el fitxer web.xml hi hem de dir que usarem aquest recurs:

<resource-ref>
	<res-ref-name>jdbc/nom_recurs</res-ref-name>
	<res-type>javax.sql.DataSource</res-type>
	<res-auth>Container</res-auth>
</resource-ref>

Per més informació sobre JNDI: http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html

Finalment, per crear demanar una connexió usarem el següent codi:

ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/nom_recurs");
con = ds.getConnection();