Bases de DatosBases de Datos
Pedro J. Muñoz MerinoPedro J. Muñoz Merinohttp://www.it.uc3m.es/pedmume/http://www.it.uc3m.es/pedmume/
Definición de Base de Datos
● Definición: Cualquier soporte para el almacenamiento persistente de datos
● Necesidad: Las aplicaciones telemáticas necesitan manipular datos, los cuales deben estar presentes a lo largo del tiempo y no sólo en memoria, actualizarse adecuadamente, añadirse, borrarse, ser buscados, etc.
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas2
adecuadamente, añadirse, borrarse, ser buscados, etc. Ejemplos de aplicaciones con esta necesidad:
― Biblioteca que almacena libros, autores, etc.
― Tienda on-line que almacena productos, compradores, etc.
― Aplicaciones sociales que almacenan personas, relaciones, etc.
― Aplicaciones de realidad aumentada que almacenan puntos de interés, personas, etc.
Tipos de Bases de Datos
● Tipos de Bases de Datos
― Relacionales. Los datos se almacenan en un conjunto de tablas. Dichas tablas pueden establecer relaciones entre sí. Son la mayoría de las bases de datos actuales. Ejemplos de las más conocidas:
� Oracle
MySQL
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas3
� MySQL
� PostgreSQL
� Microsoft Access
― No Relacionales. Los datos se almacenan mediante otro procedimiento que no son tablas. Por ejemplo en ficheros de texto planos
Tablas en BD: Filas, Columnas, Clave Primaria
LOGIN CONTRASEÑA NOMBRE APELLIDOS DIRECCIÓN TELNO
PM PEDRO PEDRO MUÑOZ C/ ARRIBA 91353
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas4
PM PEDRO PEDRO MUÑOZ C/ ARRIBA 91353
JC JUAN JUAN CASTILLO C/ UNIV. 52436
Relaciones entre Tablas
USUARIOS
SUSCRIPCIONES
LOGIN CONTRASEÑA NOMBRE APELLIDOS DIRECCIÓN TELNO
PM PEDRO PEDRO MUÑOZ C/ ARRIBA 91353
JC JUAN JUAN CASTILLO C/ UNIV. 52436
LOGIN_USUARIO ID_ACTIVIDAD
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas5
ACTIVIDADES PABELLONES
JC JUAN JUAN CASTILLO C/ UNIV. 52436
ID NOMBRE NOM_PAB DESCRIP INICIO COSTE PLAZAS
1 FUTBOL A AA 17/02/11 23 12
2 TENIS B BB 19/03/11 54 6
PM 2
NOM_PAB LOCALIZACION
A LEGANES
Acceso a Bases de Datos
● Algunas operaciones posibles en BD son:
― Crear y borrar tablas
― Seleccionar una serie de datos en base a unos filtros
― Insertar nuevos datos
― Borrar datos existentes
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas6
― Actualizar datos
● Para realizar esas operaciones de manera similar con independencia de la implementación específica -> SQL
● A una base de datos se puede acceder
― Conectándose directamente
― A través de un programa -> API como JDBC
SQL: CREATE DATABASE
● Sintaxis
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...
create_specification:[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas7
● Ejemplo― CREATE DATABASE gestor_actividades;
| [DEFAULT] COLLATE [=] collation_name
SQL: DROP DATABASE
● Sintaxis
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas8
● Ejemplo― DROP DATABASE gestor_actividades;
SQL: CREATE TABLE
● Sintaxis
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)[table_options]
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas9
SQL: create_definition
● Sintaxis
create_definition:col_name column_definition| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_col_name,...) [index_type]| {INDEX|KEY} [index_name] [index_type]
(index_col_name,...) [index_type]
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas10
(index_col_name,...) [index_type]| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
[index_name] [index_type] (index_col_name,...) [index_type]
| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_type]
| [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
| CHECK (expr)
SQL: column_definition
● Sintaxis
column_definition:data_type [NOT NULL | NULL] [DEFAULT
default_value][AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY]
KEY][COMMENT 'string'] [reference_definition]
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas11
[COMMENT 'string'] [reference_definition]
reference_definition:REFERENCES tbl_name (index_col_name,...) [MATCH FULL | MATCH PARTIAL | MATCH
SIMPLE] [ON DELETE reference_option][ON UPDATE reference_option]
reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION
SQL: CREATE TABLE
● Ejemplos― CREATE TABLE CLIENTS ( LOGIN VARCHAR(16) NOT NULL, PASSWD
VARCHAR(16) NOT NULL, NAME VARCHAR(16) NOT NULL, SURNAME VARCHAR(16) NOT NULL, ADDRESS VARCHAR(16) NOT NULL, PHONE VARCHAR(16) NOT NULL, PRIMARY KEY (LOGIN));
― CREATE TABLE PAVILLIONS ( PAVILLION VARCHAR(16) NOT NULL, LOCATION VARCHAR(16), PRIMARY KEY (PAVILLION));
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas12
― CREATE TABLE ACTIVITIES ( ID INT UNSIGNED NOT NULL AUTO_INCREMENT, NAME VARCHAR(16), DESCRIPTION VARCHAR(32), START_DATE VARCHAR(16), COST FLOAT, PAVILLION_NAME VARCHAR(16) NOT NULL, TOTAL_PLACES INT UNSIGNED NOT NULL, OCCUPIED_PLACES INT UNSIGNED NOT NULL, PRIMARY KEY (ID), FOREIGN KEY (PAVILLION_NAME) REFERENCES PAVILLIONS(PAVILLION) DELETE ON CASCADE);
― CREATE TABLE SUBSCRIPTIONS ( CLIENT_LOGIN VARCHAR(16) NOT NULL, ACTIVITY_ID INT UNSIGNED NOT NULL, PRIMARY KEY (CLIENT_LOGIN, ACTIVITY_ID), FOREIGN KEY (CLIENT_LOGIN) REFERENCES CLIENTS(LOGIN), FOREIGN KEY (ACTIVITY_ID) REFERENCES ACTIVITIES(ID));
SQL: DROP TABLE
● Sintaxis
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas13
● Ejemplo― DROP TABLE IF EXIST ACTIVITIES CASCADE;
SQL: ALTER TABLEALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ... alter_specification: table_options | ADD [COLUMN] col_name column_definition[FIRST | AFTER col_name ] | ADD [COLUMN] (col_name column_definition,...) | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_type] | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_type] | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_type] | ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (index_col_name,...) [index_type] | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name new_col_name column_definition[FIRST|AFTER col_name] | MODIFY [COLUMN] col_name column_definition[FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas14
● Ejemplo
― ALTER TABLE ACTIVITIES ADD MATERIAL VARCHAR(16);
[FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP {INDEX|KEY} index_name | DROP FOREIGN KEY fk_symbol | DISABLE KEYS | ENABLE KEYS | RENAME [TO] new_tbl_name | ORDER BY col_name [, col_name] ... | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name] | DISCARD TABLESPACE | IMPORT TABLESPACE
SQL: INSERT
● Sintaxis
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ]
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas15
● Ejemplos― INSERT INTO PAVILLIONS VALUES ('Ignacio Pinedo','Getafe');
― INSERT INTO ACTIVITIES (NAME, DESCRIPTION, START_DATE, COST, PAVILLION_NAME, TOTAL_PLACES, OCCUPIED_PLACES) VALUES ('aerobic','advanced aerobic','3 June','40','Alfredo Di Stefano', '50', '48');
― INSERT INTO ACTIVITIES (NAME, DESCRIPTION, COST) VALUES ('dual bike','competition‘, '25’);
SQL: DELETE
● Sintaxis
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition][ORDER BY ...][LIMIT row_count]
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas16
SQL: where_condition y expresiones
● Where_condition es una expresión, sólo se hará la operación para las filas que cumplan que dicha expresión es cierta. Tipos de expresiones simples:
simple_expr: literal | identifier | function_call | simple_expr COLLATE collation_name | param_marker
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas17
simple_expr COLLATE collation_name | param_marker | variable | simple_expr || simple_expr | + simple_expr | - simple_expr | ~ simple_expr | ! simple_expr | BINARY simple_expr | (expr [, expr] ...) | ROW (expr, expr [, expr] ...) | (subquery) | EXISTS (subquery) | {identifier expr} | match_expr | case_expr | interval_expr
SQL: expresiones
bit_expr: bit_expr | bit_expr | bit_expr & bit_expr | bit_expr << bit_expr | bit_expr >> bit_expr | bit_expr + bit_expr | bit_expr - bit_expr | bit_expr * bit_expr | bit_expr / bit_expr | bit_expr DIV bit_expr | bit_expr MOD bit_expr | bit_expr % bit_expr | bit_expr ^ bit_expr | bit_expr + interval_expr | bit_expr - interval_expr | simple_expr
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas18
simple_expr
predicate: bit_expr [NOT] IN (subquery) | bit_expr [NOT] IN (expr [, expr] ...) | bit_expr [NOT] BETWEEN bit_expr AND predicate | bit_expr SOUNDS LIKE bit_expr | bit_expr [NOT] LIKE simple_expr [ESCAPE simple_expr] | bit_expr [NOT] REGEXP bit_expr | bit_expr
SQL: expresiones
expr: expr OR expr | expr || expr | expr XOR expr | expr AND expr | expr && expr | NOT expr | ! expr | boolean_primary IS [NOT] {TRUE | FALSE | UNKNOWN} | boolean_primaryboolean_primary: boolean_primary IS [NOT] NULL | boolean_primary <=> predicate | boolean_primary comparison_operator predicate | boolean_primary comparison_operator {ALL | ANY} (subquery) |
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas19
comparison_operator {ALL | ANY} (subquery) | predicatecomparison_operator: = | >= | > | <= | < | <> | != predicate: bit_expr [NOT] IN (subquery) | bit_expr [NOT] IN (expr [, expr] ...) | bit_expr [NOT] BETWEEN bit_expr AND predicate | bit_expr SOUNDS LIKE bit_expr | bit_expr [NOT] LIKE simple_expr [ESCAPE simple_expr] | bit_expr [NOT] REGEXP bit_expr | bit_expr
SQL: Funciones
● Funciones Escalares― UCASE()
― LCASE()
― MID()
― LEN()
― ROUND()
― NOW()
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas20
― NOW()
― FORMAT()
● Funciones de Cálculo― AVG()
― COUNT()
― FIRST()
― LAST()
― MAX()
― MIN()
― SUM()
SQL: DELETE
● Ejemplos
― DELETE FROM CLIENTS WHERE NAME=‘Pedro’ AND SURNAME=‘Muñoz’;
― DELETE FROM CLIENTS;
― DELETE FROM ACTIVITIES WHERE COST
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas21
BETWEEN 25 AND 50;
SQL: UPDATE
● Sintaxis
● Ejemplos
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas22
● Ejemplos
― UPDATE ACTIVITIES SET Description=‘paddle‘, COST=20WHERE Name=‘paddle’;
― UPDATE ACTIVITIES SET Description=‘paddle‘, COST=20
SQL: SELECT
● Sintaxis
SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas23
select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]
SQL: SELECT
● Ejemplos:
― SELECT * FROM ACTIVITIES, PAVILLIONS WHERE ACTIVITIES.COST <="+price+"AND ACTIVITIES.PAVILLION_NAME = PAVILLIONS.PAVILLION;
― SELECT DISTINCT NAME FROM ACTIVITIES, PAVILLIONS WHERE ACTIVITIES.COST <="+price+"AND ACTIVITIES.PAVILLION_NAME = PAVILLIONS.PAVILLION
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas24
ACTIVITIES.PAVILLION_NAME = PAVILLIONS.PAVILLION ORDER BY ACTIVITIES.NAME;
― SELECT * FROM ACTIVITIES WHERE NAME LIKE '%tennis’;
― SELECT PAVILLION_NAME, SUM(PLACES) FROM ACTIVITIES GROUP BY PAVILLION_NAME HAVING SUM(PLACES)>100;
JDBC: Drivers
● Obtener el driver del vendedor. Fichero JAR o se puede descomprimir. En la documentación vienen detalles como el nombre de la URL (jdbc:tipobd:nombrebd)
● Cargar el driver
― Class.forName(nombre_del_driver)
Propiedad del sistema jdbc.drivers
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas25
― Propiedad del sistema jdbc.drivers
● Obtener una conexión
― DriverManager.getConnection(url_bd)
― DriverManager.getConnection(url_bd, propiedades)
― DriverManager.getConnection(url_bd, usuario, contraseña)
● Establecer tiempo máximo de conexión: DriverManager.setLoginTimeout (segundos)
JDBC: Statements
● Creation of Statements
― createStatement()
― createStatement(p1,p2). TYPE_SCROLL_SENSITIVE, TYPE_SCROLL_INSENSITIVE, TYPE_FORWARD_ONLY
― createStatement(p1, p2, p3)
● Enviar peticiones SQL al servidor a través de un
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas26
● Enviar peticiones SQL al servidor a través de un Statement
― executeUpdate (sentencia_SQL),
― executeQuery (sentencia_SQL)
― executeBatch (), addBatch(sentencia_SQL), clearBatch()
― execute (sentencias_SQL)
JDBC: ResultSets
● Moverse por el ResultSet
― next()
― last(), absolute (int), isFirst(), isAfterLast(), moveToInsertRow(), previous(), afterLast(), relative(int), isBeforeFirst(), getRow(), moveToCurrentRow(), first(), beforeFirst(), isLast()
● Leer datos del ResultSet
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas27
● Leer datos del ResultSet
― getArray(int)
― getBigDecimal(int), getBoolean(int), getByte(int), getDate(int), getDouble(int), getFloat(int), getInt(int), getLong(int), getShort(int), getString(int), getTime(int)
JDBC: Liberación de recursos, excepciones y valores nulos
● Liberación de recursos
― Connection: close()
― Statement: close()
● Excepción de SQL: Casi todos los métodos de JDBC pueden devolver la excepción SQLExceptions. getNextException(), getErrorCode()
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas28
getNextException(), getErrorCode()
● NULL values: Método wasNull()
JDBC: Ejemplo con mysql (I):Query. javapackage activities.model;
import java.sql.*;
public class Query {
Statement stmt;
// Method that creates an Statement object from a Connection object
public Query(Connection con) throws SQLException {
stmt = con.createStatement();
}
// Method to close an Statement object
public void close() throws SQLException {
stmt.close();
}
}
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas29
}
// Method to execute SELECT SQL sentences
public ResultSet doSelect(String query) throws Exception {
ResultSet rs = stmt.executeQuery(query);
return rs;
}
// Method to execute INSERT or DELETE SQL sentences
public int doUpdate(String query) throws SQLException {
return stmt.executeUpdate(query);
}
JDBC: Ejemplo con mysql (II): DBInteraction.javapackage activities.model;
import java.sql.*;
import java.util.ArrayList;
public class DBInteraction {
private static final String dblogin = "";
private static final String dbpasswd = "";
Query q;
Connection con;
try {
System.out.println("Trying to connect...");
con = DriverManager.getConnection (url, "root", "admin2009");
System.out.println("Connected!");
}
catch(SQLException ex) {
System.err.print("SQLException: ");
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas30
public DBInteraction () throws SQLException {
String url="jdbc:mysql://localhost/sporting_manager";
try {
Class.forName("com.mysql.jdbc.Driver");
}
catch (java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
System.err.println(ex.getMessage());
}
q=new Query(con);
}
//Method to close the database Connection
public void close()throws Exception{
q.close();
con.close();
}
JDBC: Ejemplo con mysql (III): DBInteraction.java
public boolean authentication(String login, String pwd)throws Exception{
String list="SELECT * FROM CLIENTS WHERE LOGIN='"+login+"'";
String password=null;
ResultSet rs=q.doSelect(list); //rs will contain the row with login passed as parameter
if (rs.next()){ //Check if the Resultset is empty
password = rs.getString(2);
}
public void delusr(String login) throws Exception{
String delusr="DELETE FROM CLIENTS WHERE LOGIN='"+login+"'";
q.doUpdate(delusr);
}
public ArrayList listactivities(String selection) throwsException{
ArrayList data = new ArrayList();
ResultSet rs=q.doSelect(selection);
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas31
}
if (password == null){
return(false);
}
if(password.equals(pwd)){ // In case the password for this login in the table is the same as the one passed as parameter
return(true);
}
else {
return(false);
}
}
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
String description = rs.getString(3);
String initial = rs.getString(4);
float cost = rs.getFloat(5);
String pavname = rs.getString(6);
int total = rs.getInt(7); int occupied = rs.getInt(8);
data.add(new Activity (id, name, description, initial, cost, pavname, total, occupied));
} return (data);
}
Prerrequisitos de esta temática
● Asignatura de Software de Comunicaciones, http://www.it.uc3m.es/pbasanta/SOFTCOM
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas32
Trabajo propio del alumno asociado a la sesión
● Tutorial de SQL (básico, avanzado, demo, funciones, quiz): http://www.w3schools.com/sql/default.asp
● Tutorial SQL http://www.firstsql.com/tutor6.htm
● Sentencias SQL en MySQL: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax.html
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas33
● Tutorial de JDBC Basics http://download.oracle.com/javase/tutorial/jdbc/basics/index.html
Referencias Extra
● J. L. Weaver, K. Mukhar, and J. Crume, “Beginning J2EE 1.4. From Novice to Professional”, Apress, 2004, Capítulos 6 y 7
● SQL for Web Nerds, http://philip.greenspun.com/sql/
● Tutorial JDBC http://java.sun.com/developer/Books/JDBCTutorial/index.html
Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas34
html