Saltar a contenido

9.A Conectar y crear tablas

¿Quieres aprender SQL a fondo?

Esta unidad explica lo mínimo de SQL para entender los ejemplos. Para estudiarlo con calma (consultas, JOIN, agrupaciones, diseño de tablas, transacciones...) y con ejercicios, tienes la web de SQL y bases de datos.

Qué es una base de datos relacional

Una base de datos relacional guarda la información en tablas: cada tabla tiene columnas (los datos que se guardan) y filas (cada elemento guardado). Las tablas se relacionan entre sí mediante claves, y todo se maneja con un lenguaje común, SQL.

Concepto Qué es Ejemplo
Tabla Un conjunto de datos del mismo tipo libros
Fila (registro) Un elemento guardado el libro «Don Quijote»
Columna (campo) Un dato de cada fila, con un tipo titulo, anio, stock
Clave primaria (primary key) Columna que identifica de forma única cada fila id
Clave foránea (foreign key) Columna que apunta a la clave primaria de otra tabla libros.id_autor → autores.id

En los ejemplos de esta unidad hay tres tablas relacionadas: un autor tiene muchos libros y un libro puede tener muchos préstamos.

autores (id, nombre)  1 ──── N  libros (id, titulo, anio, stock, id_autor)  1 ──── N  prestamos (id, id_libro, socio)

Motores de bases de datos

Motor Cómo funciona Cuándo se usa
SQLite Un archivo (o la memoria); no hay servidor ni instalación Aprender, apps móviles y de escritorio, pruebas
H2, HSQLDB Base de datos embebida en Java (archivo o memoria) Pruebas y programas Java sencillos
MySQL, PostgreSQL Servidor al que se conecta por red con usuario y contraseña Aplicaciones reales con muchos usuarios

Qué motor usan estos ejemplos

Todos los ejemplos usan SQLite, porque no necesita instalar nada. Los ejercicios proponen H2 para Java y Kotlin: el código JDBC es el mismo, solo cambian la dependencia (com.h2database:h2) y la URL (jdbc:h2:./data/tienda). Yo he comprobado los ejemplos con SQLite, no con H2. Las sentencias también varían un poco entre motores: por ejemplo, la columna que se numera sola es AUTOINCREMENT en SQLite y AUTO_INCREMENT en MySQL y H2.

Conectar, crear las tablas e insertar datos

Kotlin usa JDBC (java.sql), la misma API que Java, con el controlador del motor en el classpath (estos ejemplos usan SQLite: org.xerial:sqlite-jdbc). DriverManager.getConnection(url) abre la conexión (jdbc:sqlite:biblioteca.db para un archivo, jdbc:sqlite::memory: para memoria). Se lanzan sentencias con Statement (SQL fijo) o PreparedStatement (con parámetros ?). Todo es Closeable, así que se cierra con la función use. Los errores son una SQLException.

import java.io.File
import java.sql.DriverManager
import java.sql.SQLException

fun main() {
    try {
        DriverManager.getConnection("jdbc:sqlite:biblioteca.db").use { con ->
            println("conectado a la base de datos")
            con.createStatement().use { st ->
                st.execute("CREATE TABLE autores (id INTEGER PRIMARY KEY AUTOINCREMENT, nombre TEXT NOT NULL)")
                st.execute(
                    "CREATE TABLE libros (id INTEGER PRIMARY KEY AUTOINCREMENT, titulo TEXT NOT NULL, " +
                        "anio INTEGER, stock INTEGER NOT NULL, id_autor INTEGER NOT NULL REFERENCES autores(id))"
                )
                st.execute(
                    "CREATE TABLE prestamos (id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        "id_libro INTEGER NOT NULL REFERENCES libros(id), socio TEXT NOT NULL)"
                )
            }
            println("tablas creadas")

            val autores = listOf("Cervantes", "García Márquez")
            con.prepareStatement("INSERT INTO autores (nombre) VALUES (?)").use { ps ->
                for (nombre in autores) {
                    ps.setString(1, nombre)
                    ps.executeUpdate()
                }
            }
            println("autores insertados: ${autores.size}")

            val libros = listOf(
                listOf("Don Quijote", 1605, 3, 1),
                listOf("Novelas ejemplares", 1613, 2, 1),
                listOf("Cien años de soledad", 1967, 5, 2),
                listOf("El coronel no tiene quien le escriba", 1961, 1, 2),
            )
            con.prepareStatement("INSERT INTO libros (titulo, anio, stock, id_autor) VALUES (?, ?, ?, ?)").use { ps ->
                for (libro in libros) {
                    ps.setString(1, libro[0] as String)
                    ps.setInt(2, libro[1] as Int)
                    ps.setInt(3, libro[2] as Int)
                    ps.setInt(4, libro[3] as Int)
                    ps.executeUpdate()
                }
            }
            println("libros insertados: ${libros.size}")

            con.createStatement().use { st ->
                st.executeQuery("SELECT COUNT(*) FROM libros").use { rs ->
                    rs.next()
                    println("libros en la base de datos: ${rs.getInt(1)}")
                }
            }

            try {
                con.createStatement().use { it.execute("INSERT INTO editoriales (nombre) VALUES ('X')") }
            } catch (e: SQLException) {
                println("Error controlado: la sentencia no es válida")
            }
        }
    } catch (e: SQLException) {
        println("Error de base de datos: ${e.message}")
    }
    println("conexión cerrada")

    if (File("biblioteca.db").delete()) {
        println("archivo biblioteca.db borrado")
    }
}

Salida:

conectado a la base de datos
tablas creadas
autores insertados: 2
libros insertados: 4
libros en la base de datos: 4
Error controlado: la sentencia no es válida
conexión cerrada
archivo biblioteca.db borrado

Qué hace cada paso:

  1. Conecta con la base de datos (el archivo biblioteca.db, que se crea si no existe).
  2. Crea las tres tablas con CREATE TABLE, indicando para cada columna su tipo y sus restricciones.
  3. Inserta datos con INSERT y parámetros (?): los valores no se pegan dentro del texto SQL, se pasan aparte. Esto es lo que se explica en 9.C y es obligatorio cuando el dato viene del usuario.
  4. Cuenta las filas con una consulta.
  5. Provoca un error a propósito (insertar en una tabla que no existe) y lo captura: el programa no se detiene y muestra un mensaje claro.
  6. Cierra la conexión (la función use de la conexión) siempre, haya habido error o no. Solo después se puede borrar el archivo: con la conexión abierta, Windows no permite borrarlo.

Crear tablas: tipos y restricciones

CREATE TABLE libros (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,   -- identifica cada fila; se numera solo
  titulo   TEXT    NOT NULL,                    -- obligatorio
  anio     INTEGER,                             -- puede quedar vacío (NULL)
  stock    INTEGER NOT NULL,
  id_autor INTEGER NOT NULL REFERENCES autores(id)   -- clave foránea
);
Restricción Significa
PRIMARY KEY Identifica la fila; no se repite ni puede ser nula
NOT NULL La columna no puede quedar vacía
UNIQUE No puede haber dos filas con el mismo valor (por ejemplo, un correo)
REFERENCES tabla(columna) Clave foránea: el valor debe existir en la otra tabla
DEFAULT valor Valor que se usa si no se indica ninguno
Tipo en SQLite Para qué Equivalente habitual en otros motores
INTEGER Números enteros INT, BIGINT
TEXT Texto VARCHAR(n), TEXT
REAL Números decimales DOUBLE, FLOAT
BLOB Datos binarios BLOB, BYTEA

Dinero y decimales

Para importes, otros motores ofrecen DECIMAL(10,2), que guarda decimales exactos. Con REAL (coma flotante) pueden aparecer errores de redondeo, como en 0.1 + 0.2. Una alternativa muy usada es guardar el importe en céntimos, como entero.

Errores frecuentes

Error Cómo evitarlo
No cerrar la conexión finally / try-with-resources / use
Pegar el valor dentro del SQL Usar parámetros ?
Olvidar confirmar los cambios (en Python, commit) Confirmar al terminar o usar una transacción (9.C)
Crear una tabla que ya existe Crear las tablas una vez, o usar CREATE TABLE IF NOT EXISTS
Insertar con claves foráneas que no existen Insertar primero la tabla «padre» (los autores) y luego la «hija» (los libros)

Para practicar

Haz los ejercicios de U9.1 · Conexión y creación de tablas. Pasar de uno a otro compara cómo se escribe en cada lenguaje.