1. Creación de base de datos, tablas y relaciones
Un buen diseño de base de datos permite mejorar el rendimiento de un sistema, por lo tanto, es fundamental darse el tiempo para diseñar la estructura que debe tener la base de datos.
Un análisis del funcionamiento permitirá controlar y corregir los pequeños problemas que puedan surgir en el sistema. Para realizar el análisis, se puede recurrir a diferentes herramientas externas que darán una visión detallada de cuáles son los patrones de funcionamiento de base de datos y centrarse en aquellos apartados más conflictivos.
Gran parte de las cualidades esperadas en una buena base de datos se basan en su diseño inicial, que constituye los cimientos de cualquier sistema de información actual. El diseño de base de datos debe estar bien definido antes de pasar a otra etapa, dada la dificultad y el coste de realizar cambios en el diseño en etapas posteriores. El diseño de bases de datos se compone de dos fases altamente diferenciadas: diseño lógico y diseño físico.
El diseño lógico consiste en analizar los requerimientos de la empresa y cuáles van a ser los componentes de base de datos, como por ejemplo las tablas y las restricciones. El diseño lógico no tiene en cuenta dónde o cómo van a estar almacenados físicamente los datos.
El uso efectivo de cualquier base de datos se basa en un buen diseño normalizado. Por su parte, la normalización consiste en el proceso de evitar las redundancias de los datos, la actualización, modificación y borrado de datos en múltiples posiciones.
Además, al eliminar la repetición innecesaria de información, se reduce la probabilidad que la base de datos se vuelva inconsistente o contenga errores. Las sentencias básicas para trabajar con base de datos son las siguientes:
- SHOW DATABASES;
Muestra las base de datos existentes en MySQL.
- CREATE DATABASE dbdat;
Crea la base de datos de nombre dbdat.
- USE dbdat;
Activa la base de datos de nombre dbdat.
- DROP DATABASE dbdat;
Elimina la base de datos dbdat.
Una vez creada la base de datos, en ella se crearán las tablas que son los repositorios de datos, para lo cual se deberán definir los tipos de datos que se asignarán en estos repositorios.
Estos tipos de datos pueden ser los siguientes:
- VARCHAR(número): Tiene un número variable de caracteres, el número que se pone es el número máximo de caracteres que puede tener este número va de 1 hasta 255.
- CHAR(número) Tiene un número fijo de caracteres va de 1 hasta 255.
- DATE Tipo fecha (YYYY-MM-DD)-( '1000-01-01' a '9999-12-31').
- DATETIME Tipo fecha y hora (YYYY-MM-DD HH:MM:SS).
- INTEGER (INT) Tipo numérico entero (-2147483648 a 2147483647)
- FLOAT(M, D) Número real de coma flotante M es el número y D los decimales. (-3.402823466E+38 a -1.175494351E-38, 0, y 1.175494351E- 38 a 3.402823466E+38.)
- DOUBLE(M, D) Número real de doble precisión M es el número y D los decimales. (- 1.7976931348623157E+308 a - 2.2250738585072014E-308, 0, y 2.2250738585072014E-308 a 1.7976931348623157E+308)
- BLOB Para grandes textos, siendo la longitud máxima de 65535. Con este tipo las búsquedas de texto son sensibles a las mayúsculas.
- TEXT Para grandes textos, siendo la longitud máxima de 65535. Con este tipo las búsquedas de texto NO son sensibles a las mayúsculas.
1.1 Opciones para las columnas
- NOT NULL El valor no puede ser nulo en el campo.
- AUTO_INCREMENT Automáticamente incrementa el número del registro anterior.
- PRIMARY KEY El PRIMARY KEY es un campo que MySQL usa como índice. Este índice puede hacer cosas como:
- Hallar rápidamente filas que acierten una cláusula WHERE.
- Regresar filas de una tabla desde otras tablas cuando se realizan uniones.
- Esto definitivamente ayudará a agilizar peticiones.
- KEY Crea una llave secundaria. Se puede encontrar mayor información en la siguiente dirección: http://dev.mysql.com/doc/refman/5.0/es/column-types.html
1.2 Tablas
Una tabla es un conjunto de datos sobre un tema concreto para guardar datos de personas, productos, departamentos, cursos entre otros. Si se usa una tabla independiente para cada tema, se evitará la duplicación de datos y la base de datos resultará más eficiente, al mismo tiempo que se reducirá el riesgo de errores en la entrada de datos. En las tablas, los datos están organizados en filas y columnas.
Las sentencias básicas para trabajar con tablas son las siguientes:
- SHOW TABLES;
Muestra las tablas de la base de datos activa.
CREATE TABLE personas (idpersona int NOT NULL AUTO_INCREMENT,nombres varchar(40) NOT NULL,apellidos varchar(40) NOT NULL,sexo char(1) NOT NULL, -- Masculino/Femeninoemail varchar(40) NULL,login varchar(10) NOT NULL,password varchar(10) NOT NULL,telefono varchar(20) NULL,nacimiento datetime NULL, -- fecha en que naciódatoAdicional text NULL -- dato adicional de persona);
- ALTER TABLE personas ADD UNIQUE (login, password);
Altera la tabla personas, para que la unión de login con password no se repita en las siguientes filas.
- DROP TABLE personas; Elimina la tabla personas.
2. Consultas
El lenguaje estructurado de consultas (SQL) proporciona la sentencia SELECT, la cual permite hacer consultas a una base de datos. La siguiente tabla explica cada una de las partes de la sentencia Select.
- SELECT
Palabra clave que indica que la sentencia de SQL que se desea ejecutar es de selección.
Ejemplos:
SELECT * FROM tabla – muestra el contenido de toda la tabla
SELECT col1, col2 FROM tabla -- muestra el contenido de columna 1 y 2
- FROM
Indica la tabla (o tablas) desde la que se desea recuperar los datos. En el caso de que exista más de una tabla se denomina a la consulta "consulta combinada" o "join".
En las consultas combinadas es necesario aplicar una condición de combinación, a través de una cláusula ON. En las consultas combinadas surge el concepto de llave foránea.
Una llave foránea llamada también llave externa es uno o más campos de una tabla que hacen referencia al campo o campos de clave principal de otra tabla, una clave externa indica cómo están relacionadas las tablas.
Los datos en los campos de clave externa y clave principal deben coincidir, aunque los nombres de los campos no sean los mismos. Ejemplos:
SELECT * FROM tabla – muestra el contenido de tabla.
– Muestra el contenido de tabla1 y tabla2.
Si queremos mostrar el contenido de dos tablas.
Siendo estas:
tabla1
campo 1: idtabla1
campo 2:colx
tabla 2
campo 1: idtabla2
campo 2: idtabla1 ( llave foránea)
campo2 coly
SELECT tabla1.colx, tabla2.coly FROM tabla1
INNER JOIN tabla2
ON tabla1.idtabla1= tabla2.idtabla2 (unión realizada por la llave foránea)
- WHERE
Especifica una condición que debe cumplirse para que los datos sean
devueltos por la consulta. Además, admiten los operadores lógicos
AND y OR.
Ejemplos:
– muestra fila de id=5
SELECT * FROM tabla WHERE id=5
– muestra filas cuyas edades estén entre [18, 65]
SELECT * FROM tabla WHERE edad>17 AND edad<66
- GROUP BY
Especifica la agrupación que se da a los datos. Se usa siempre en combinación con funciones agregadas.
Ejemplo:
Tabla alumnos
campo1: idalumno
campo 2: nombre
campo 3: apellido
Tabla notas
campo 1: idnota
campo 2: idalumno
campo 3: tipo nota
– muestra lista de alumnos y promedios
SELECT alumnos.alumno, AVG(notas.nota)
FROM alumnos
INNER JOIN notas
ON alumnos.idalumno= notas.idnota
GROUP BY alumnos
- HAVING
Especifica una condición que debe cumplirse para que los datos sean devueltos por la consulta. Su funcionamiento es similar al de WHERE, pero aplicado al conjunto de resultados devueltos por la consulta. Debe aplicarse siempre junto a GROUP BY y la condición debe estar referida a los campos contenidos en ella.
Ejemplo:
– Muestra lista de alumnos y promedios mayor o igual a 11.
SELECT alumnos.alumno, AVG(notas.nota)
FROM alumnos
INNER JOIN notas
ON alumnos.id= notas.id
GROUP BY alumnos
HAVING AVG(notas.nota)>=11
- ORDER BY
Presenta el resultado ordenado por las columnas indicadas. El orden puede expresarse con ASC (orden ascendente) y DESC (orden descendente). El valor predeterminado es ASC.
Ejemplo:
– Lista de alumnos y promedios mayor o igual a 11 en orden de
mérito.
SELECT alumnos.alumno, AVG(notas.nota)
FROM alumnos
INNER JOIN notas
ON alumnos.id= notas.id
GROUP BY alumnos
HAVING AVG(notas.nota)>=11
ORDER BY 2 DESC
3. Inserción, eliminación y actualización
INSERT
La sentencia INSERT permite insertar valores dentro de una tabla y tiene dos formas importantes a considerar.
Ejemplo:
Insertamos datos en la tabla Alumnos.
Tabla Alumnos
campo 1: nombre
campo 2: dirección
campo 3: teléfono
campo 4: nacimiento
campo 5 : categoría (acepta null)
campo 6: estado ( default A)
Insert Alumnos(Nombre, Direccion, Telefono, Nacimiento) values ('Juan Perez','Av. Arequipa 3025', GetDate())
Si no se especifican columnas es porque se van ingresar datos a todas las
columnas, en correspondencia a la estructura de la tabla.
Insert Alumnos values ('Juan Perez','Av. Arequipa 3025', GetDate(), 'B', 'Activo')
También, es posible insertar datos desde otra tabla, en caso la otra tabla cumpla con la estructura de la tabla destino.
Insert into Alumnos
select * from Alumnos2;
UPDATE
Es posible modificar el contenido de las columnas de una tabla con la sentencia UPDATE.
Ejemplo:
update nombretable set campo1="dato1" where condicion;
Nota:
Si no se colocara la condición (Where ...), entonces todas las filas serían cambiadas a este teléfono.
También, es posible actualizar una tabla desde otras tablas.
Por ejemplo:
update nombretable set campo1= (select campo from tabla2 where id=1) where condicion;
DELETE
Con esta sentencia se puede eliminar una o más filas de una tabla.
delete tabla where condicion;
Ejemplo:
delete alumnos where telefono is null;
También se puede borrar todo:
delete alumnos
También, es posible retirar filas de una tabla, según características de otra
tabla
delete nombretable where campo1 in (select campo from tabla2 where id=1) ;
4. Conectividad con Java
La conectividad de la base de datos de Java (JDBC, Java Database Connectivity) es un marco de programación para los desarrolladores de Java que escriben los programas que tienen acceso a la información guardada en bases de datos.
JDBC, se utiliza comúnmente para conectar un programa del usuario con una base de datos por “detrás de la escena”, sin importar qué software de administración o manejo de base de datos se utilice para controlarlo. En Java existen 4 formas de usar JDBC para conexión con base de datos:
4.1 El puente JDBC-ODBC
Se usará, inicialmente, para facilitar la comunicación con el gestor SQL Server. Para esto se necesita del ODBC (Open Database Connectivity) de Microsoft, a través del cual se creará un DSN (Data Source Name) que permitirá crear una cadena de conexión de información sobre la base de datos.
4.2 Driver de Java parcialmente nativo
Esta forma está integrada por controladores que se comunican con el servidor de base de datos en el protocolo nativo del servidor. Por ejemplo, para el gestor DB2 se necesitaría un driver nativo de DB2 de IBM. Para Informix, se requerirá de un driver nativo de Informix de Unix.
4.3 Driver JDBC-Net de Java puro
En esta forma los drivers están hechos en Java puro; sin embargo, utilizan protocolos estándares, como por ejemplo HTTP con servidor de base de datos. El servidor traduce el protocolo de red. Para el caso de Windows, puede usar ODBC.
4.4 Driver de protocolo de Java puro
En esta última forma, conformada por drivers de java puro, la comunicación es a través de un protocolo específico para la marca de base de datos que se usa. Para mostrar un ejemplo del driver de protocolo de Java puro, se debe crear un proyecto en NetBeans y agregar al nodo Libraries, el driver de MySQL.
Luego, cree una clase Java con el siguiente contenido:
Si se tiene esta salida, entonces ya se tendrá conexión a la base de datos test de MySQL.
5. El paquete java.sql
Es una implementación de la API JDBC (usada para acceder a bases de datos SQL) donde se agrupa una serie de objetos como: Connection, Statement, PreparedStatement, ResulSet, ResultSetMetaData y otros.
5.1 Connection
Permite la conexión a la base de datos. Origina un canal entre una aplicación y la base de datos. Será siempre imprescindible en una aplicación que quiere acceder a una base de datos.}
Ejemplo de su uso:
Connection cn=DriverManager.getConnection("jdbc:mysql://localhost:3306/appweb", "root", "java");
5.2 Statement
Este objeto permitirá ejecutar una sentencia SQL para una base de datos, por ejemplo: select, insert, update y delete.
Ejemplo de su uso:
Sring sql = “SELECT * FROM provincias“;
Connection cn = db.getConnection();
Statement st = cn. createStatement();
ResultSet rs = st. executeQuery(sql);
5.3 PreparedStatement
Este objeto permitirá ejecutar una sentencia SQL para una base de datos usando parámetros de envío; por ejemplo: select, insert, update y delete.
Ejemplo de su uso:
Sring sql = “SELECT * FROM provincias LIMIT ?, ?“;
Connection cn = db.getConnection();
PreparedStatement ps = cn.prepareStatement(sql);
ps.setLong(1, 50);
ps.setLong(2, 50);
ResultSet rs = ps.executeQuery();
5.4 ResulSet
Si el objeto Statement ejecuta una sentencia Select del SQL, entonces, este devuelve un conjunto de resultados. Este conjunto de resultados es asignado y manipulado por un objeto ResulSet.
Ejemplo de su uso:
Sring sql = “SELECT * FROM provincias“;
Connection cn = db.getConnection();
Statement st = cn. createStatement();
ResultSet rs = st. executeQuery(sql);
Tabla de métodos Clase Connection-Resumen de método:
- void: clearWarnings() Limpia todos los warnings reportados por el objeto Connection.
- void close() Libera la base de datos de este objeto Connection y de los recursos consumidos por JDBC.
- void commit() Ejecuta todos los cambios hechos desde el previo commit/rollback y libera a la base de datos de actuales bloqueos por este objeto Connection.
- Statement createStatement() Crea un objeto Statement para enviar una sentencia SQL a la base de datos.
- Statement createStatement(int resultSetType, int resultSetConcurrency) Crea un objeto Statement, el cual generará objetos ResulSet con un tipo y concurrencia dada.
- String getCatalog() Retorna el nombre del actual catálogo o base de datos.
Clase Connection-Resumen de métodos
- DatabaseMetaData getMetaData() Recupera un objeto DatabaseMetaData, el cual contiene metadata de la base de datos con la que este objeto Connection ha establecido la conexión.
- SQLWarning getWarnings() Recupera el primer warning reportado por la ejecución de este objeto Connection.
- boolean isClosed() Retorna true si el objeto Connection fue cerrado.
- boolean isReadOnly() Retorna true si el objeto Connection es de modo read-only.
- void rollback() Deja sin efecto todos los cambios hechos en la actual transacción y libera bloqueos en la base de datos retenidos por este objeto Connection.
- void setCatalog(String catalog) Establece el nombre del catálogo o de la base de datos con la cual trabajará este objeto Connection.
- void setReadOnly(boolean readOnly) Pone esta conexión en modo read-only habilitando al driver para optimizaciones sobre la base de datos.
Clase Statement - Resumen de métodos
- void addBatch(String sql) Adiciona un comando SQL dado a la actual lista de comandos de este objeto Statement.
- void cancel() Cancela este objeto Statement, si tanto el DBMS y el driver soportan abortar una sentencia SQL.
- void clearBatch() Limpia el objeto Statement de la actual lista de comandos SQL.
- void clearWarnings() Limpia todos los warnings reportados por este objeto Statement.
- void close() Libera la base de datos de este objeto Statement y de los recursos consumidos por JDBC.
- boolean execute(String sql) Ejecuta una sentencia SQL y retorna true, si retorna resultados.
- int[] executeBatch() Ejecuta un lote de comandos SQL sobre una base de datos. Si la ejecución fue satisfactoria, retorna un arreglo de actualizaciones incluidas.
- ResultSet executeQuery(String sql) Ejecuta una sentencia SELECT del SQL y retorna un único ResulSet.
- int executeUpdate(String sql) Ejecuta una sentencia SQL como un INSERT, UPDATE o DELETE; o una sentencia SQL que no retorne nada como una sentencia DDL del SQL.
- int getQueryTimeout() Recupera el número de segundos que el driver esperará para que un objeto Statement se ejecute.
- ResultSet getResultSet() Recupera el actual resultado de un objeto ResultSet.
- SQLWarning getWarnings() Recupera la primera warning reportada por la llamada a este objeto Statement.
- void setCursorName(String name) Establece un nombre de cursor SQL, el cual puede ser usado por posteriores métodos del objeto Statement.
- void setQueryTimeout(int seconds) Establece el número de segundos que el driver esperará para que un objeto Statement se ejecute.
Clase ResultSet - Resumen de métodos
- boolean absolute(int row) Mueve el cursor al número de fila determinada en este objeto
- ResultSet. void cancelRowUpdates() Cancela los cambios realizados en la fila actual de este objeto ResultSet.
- void close() Libera base de datos este objeto ResultSet JDBC y recursos inmediatamente en lugar de esperar a que esto suceda cuando se cierra automáticamente.
- void deleteRow() Elimina la fila actual de este objeto ResultSet y de la base de datos subyacente.
- int findColumn(String columnName) Asigna el nombre de la columna de resultados a su índice de la columna de resultados.
- boolean first() Mueve el cursor a la primera fila de este objeto ResultSet.
- boolean getBoolean(int columnIndex) Recupera el valor de la columna designada en la fila actual de este objeto ResultSet como un booleano en el lenguaje de programación Java.
- boolean getBoolean(String columnName) Recupera el valor de la columna designada en la fila actual de este objeto ResultSet como un booleano en el lenguaje de programación Java.
- byte getByte(int columnIndex) Recupera el valor de la columna designada en la fila actual de este objeto ResultSet como un byte en el lenguaje de programación Java.
- byte getByte(String columnName) Recupera el valor de la columna designada en la fila actual de este objeto ResultSet como un byte en el lenguaje de programación Java.
- String getCursorName() Recupera el Nombre del cursor SQL utilizada por Objeto ResultSet.
- Date getDate(int columnIndex) Recupera el valor de la columna designada en la fila actual de este objeto ResultSet como objeto java.sql.Date en el lenguaje de programación Java.
- Date getDate(String columnName) Recupera el valor de la columna designada en la fila actual de este objeto ResultSet como objeto java.sql.Date en el lenguaje de programación Java.
- double getDouble(int columnIndex) Recupera el valor de la columna designada en la fila actual de este objeto ResultSet como un doble en el lenguaje de programación Java.
6. Metadatos
En ocasiones, se necesitan hacer aplicaciones en Java que trabajen contra una base de datos desconocida, de la que no se sabe qué tablas tiene, ni cuáles son sus columnas en cada tabla. Es, por ejemplo, el caso de aplicaciones que permiten visualizar el contenido (o incluso modificar el mismo) de una base de datos cualquiera, de una forma genérica. En este tipo de aplicaciones, a veces también se deja que el usuario escriba sus propias sentencias SELECT que el programa luego ejecuta y debe mostrar los resultados.
Las clases DataBaseMetaData y ResultSetMetaData permiten, respectivamente, analizar la estructura de una base de datos (qué tablas tiene, qué columnas cada tabla, de qué tipos, etc.) o de un ResultSet de una consulta, para averiguar cuántas columnas tiene dicho ResulSet, de qué columnas de base de datos proceden, de qué tipo son, etc.
ResultSetMetaData
Un objeto de esta clase tiene información meta sobre el conjunto de resultados, como por ejemplo: cuántas columnas tiene la consulta, los nombres de las columnas, los tipos de datos que guarda cada columna, cuántas filas, etc. Ejemplo de su uso:
Sring sql = “SELECT * FROM provincias“;
Connection cn = db.getConnection();
Statement st = cn. createStatement();
ResultSet rs = st. executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int ctascols = rsmd.getColumnCount();
Las clases DataBaseMetaData y ResultSetMetaData permiten, respectivamente, analizar la estructura de una base de datos (qué tablas tiene, qué columnas cada tabla, de qué tipos, etc.) o de un ResultSet de una consulta, para averiguar cuántas columnas tiene dicho ResulSet, de qué columnas de base de datos proceden, de qué tipo son, etc.
ResultSetMetaData
Un objeto de esta clase tiene información meta sobre el conjunto de resultados, como por ejemplo: cuántas columnas tiene la consulta, los nombres de las columnas, los tipos de datos que guarda cada columna, cuántas filas, etc. Ejemplo de su uso:
Sring sql = “SELECT * FROM provincias“;
Connection cn = db.getConnection();
Statement st = cn. createStatement();
ResultSet rs = st. executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int ctascols = rsmd.getColumnCount();
7. Retorno y vista de consulta en el browser
Para mostrar una consulta en el browser, se debe considerar lo siguiente:- Los datos se muestran en una tabla HTML, por lo tanto, deben tener los estilos de tablas para que esta sea cebra, es decir, filas pares de un color y pares de otro color.
- El otro aspecto a considerar es la lectura de la base de datos para obtener la información a mostrar al cliente, por lo tanto, se debe considerar lo siguiente:
- El driver a usar, según la base de datos.
- La conexión de Java con el respectivo driver para acceder a la base de datos.
- Tener una instrucción Statement para ejecutar una sentencia SQL.
- Tener una instrucción ResultSet para recoger la información leída de la base de datos.
- Tener una instrucción ResultSetMetaData para que informe de la metadata recogida por la consulta.
- Finalmente, un objeto List que encapsule la información y se lo envíe al cliente.