Persistence.java

package ej5;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class Persistence {
	static boolean initialized = false;
	
	private static void initialize()
	{
		if (initialized)
			return;
		try {
			Class.forName("org.hsqldb.jdbcDriver");
			initialized = true;
			createTables();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private static Connection getConnection() throws SQLException, ClassNotFoundException {
		return DriverManager
				.getConnection("jdbc:hsqldb:mem:ej5db");
	}
	
	private static void createTables() throws SQLException, ClassNotFoundException {
		Connection conn = getConnection();
		
		Statement s = conn.createStatement();
		s.execute("create table Temas(id int GENERATED BY DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY, tema varchar(50))");
		s.execute("create table Libros(id int GENERATED BY DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY, titulo varchar(50), autor varchar(50), " +
			"precio double, idTema int)");
		s.close();
		conn.close();
		
		añadirTema("Aventuras");
		añadirTema("Ciencia Ficción");
		añadirTema("Novela");
		añadirTema("Histórico");
		añadirTema("Estudio");
	}

	protected static void añadirTema(String tema) throws SQLException, ClassNotFoundException {
		initialize();
		Connection conn = getConnection();
		PreparedStatement st = conn.prepareStatement("insert into Temas (tema) values (?)");
		st.setString(1, tema);
		st.executeUpdate();
		conn.close();
	}

	public void añadirLibro (Libro libro) throws SQLException, ClassNotFoundException {
		initialize();
		Connection connection = getConnection();
		PreparedStatement st = connection.prepareStatement("insert into Libros (titulo, autor, precio, idTema) values (?,?,?,?)");
		
		st.setString(1, libro.getTitulo());
		st.setString(2, libro.getAutor());
		st.setDouble(3, libro.getPrecio());
		st.setInt(4, libro.getIdTema());
		
		if ( st.executeUpdate() != 1)
			throw new SQLException("No se pudo insertar");
		
		st.close();
		connection.close();
	}
	
	public List<Libro> getLibros () throws SQLException, ClassNotFoundException {
		initialize();
		Connection connection = getConnection();
		Statement st = connection.createStatement ();
		
		ResultSet resultset = st.executeQuery("select titulo, autor, precio, idTema from Libros");
		
		ArrayList<Libro> result = new ArrayList<Libro>();
		
		while (resultset.next())
		{
			Libro libro = new Libro();
			libro.setTitulo(resultset.getString(1));
			libro.setAutor(resultset.getString(2));
			libro.setPrecio(resultset.getDouble(3));
			libro.setIdTema(resultset.getInt(4));
			
			result.add(libro);
		}
		
		st.close();
		connection.close();
		
		return result;
	}
	
	public List<Tema> getTemas () throws SQLException, ClassNotFoundException {
		initialize();
		Connection connection = getConnection();
		
		Statement st = connection.createStatement();
		
		ResultSet resultset = st.executeQuery("select id, tema from Temas");
		
		List<Tema> result = new ArrayList<Tema>();
		
		while (resultset.next())
		{
			Tema tema = new Tema();
			tema.setId(resultset.getInt(1));
			tema.setTema(resultset.getString(2));
			
			result.add(tema);
		}
		
		return result;
	}
	
	public Tema getTemaParaLibro (Libro libro) throws SQLException, ClassNotFoundException {
		initialize();
		Connection connection = getConnection();
		PreparedStatement st = connection.prepareStatement ("select id, tema from Temas where id=?");
		
		st.setInt(1, libro.getIdTema());
		
		ResultSet resultset = st.executeQuery();
		
		Tema result = new Tema();
		
		while (resultset.next())
		{
			result.setId(resultset.getInt(1));
			result.setTema(resultset.getString(2));
		}
			
		return result;
	}
}