dimanche 8 février 2015

Java - SQLite database locked

I'm heaving a problem with SQLite and Java.


I don't know if it's because I'm leaving some open connection (or PreparedStatement/ResultSet) or if it's because I'm handling the Connection in a wrong way but this code is giving me a hard time for the past 2 days.


I've created the above class to create the connection the SQLite DB:



package lp2.database;

import java.sql.*;

public class ConnectionFactory
{
public Connection getConnection()
{
Connection c = null;

try
{
System.out.println("Conectando ao banco de dados...");
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:src/lp2/database/rivieraresort.db");
System.out.println("Conectado com sucesso!");
}
catch(Exception e)
{
e.printStackTrace();
}

return c;
}
}


And the following one to handle the DB itself package lp2.database;



import lp2.objetos.*;

import java.sql.*;
import java.util.Arrays;
import java.util.List;
import java.util.ArrayList;

public class RivieraDB
{
public void adicionarCliente(Cliente cliente) throws Exception
{
Connection c = new ConnectionFactory().getConnection();
String query = "INSERT INTO clientes (nome, cpf, endereco, nascimento, cartao, contrato_id, status_hospedagem) VALUES (?, ?, ?, ?, ?, ?, ?)";
PreparedStatement pstmt = c.prepareStatement(query);

pstmt.setString(1, cliente.getNome());
pstmt.setString(2, cliente.getCpf());
pstmt.setString(3, cliente.getEndereco());
pstmt.setString(4, cliente.getNascimento());
pstmt.setString(5, cliente.getCartao());
pstmt.setInt(6, cliente.getContrato().getID());
pstmt.setBoolean(7, cliente.getEstaHospedado());

pstmt.execute();
pstmt.close();
c.close();
}

public void adicionarContrato(Contrato contrato) throws Exception
{
Connection c = new ConnectionFactory().getConnection();
String query = "INSERT INTO contratos (gastos, multa, data_inicio, tempo) VALUES (?, ?, ?, ?)";
PreparedStatement pstmt = c.prepareStatement(query);

pstmt.setDouble(1, contrato.getGastos());
pstmt.setDouble(2, contrato.getMulta());
pstmt.setString(3, contrato.getInicioContrato());
pstmt.setInt(4, contrato.getDias());

pstmt.execute();
pstmt.close();
c.close();
}

public void adicionarQuarto(Quarto quarto) throws Exception
{
Connection c = new ConnectionFactory().getConnection();
String query = "INSERT INTO quartos (tipo, camas_extras, preco, contrato_id) VALUES (?, ?, ?, ?)";
PreparedStatement pstmt = c.prepareStatement(query);

pstmt.setString(1, quarto.getTipo());
pstmt.setInt(2, quarto.getCamasExtras());
pstmt.setDouble(3, quarto.getPagamento());
pstmt.setInt(4, quarto.getContratoID());

pstmt.execute();
pstmt.close();
c.close();
}

public Quarto buscarQuarto(int contratoID) throws Exception
{
Quarto quartoEncontrado = null;

Connection c = new ConnectionFactory().getConnection();
c = new ConnectionFactory().getConnection();
String query = "SELECT * FROM quartos WHERE contrato_id = ?";
PreparedStatement pstmt = c.prepareStatement(query);

pstmt.setInt(1, contratoID);

ResultSet rs = pstmt.executeQuery();

while(rs.next())
{
String tipo = rs.getString("tipo");
int camasExtras = rs.getInt("camas_extras");
double preco = rs.getDouble("preco");

quartoEncontrado = new Quarto(tipo, camasExtras, preco);
}

rs.close();
pstmt.close();
c.close();
return quartoEncontrado;
}

public Contrato buscarContrato(int contratoID) throws Exception
{
Contrato contratoEncontrado = null;

Connection c = new ConnectionFactory().getConnection();
Quarto quartoAssociado = buscarQuarto(contratoID);
c = new ConnectionFactory().getConnection();
String query = "SELECT * FROM contratos WHERE id = ?";
PreparedStatement pstmt = c.prepareStatement(query);

pstmt.setInt(1, contratoID);

ResultSet rs = pstmt.executeQuery();

while(rs.next())
{
String inicioContrato = rs.getString("data_inicio");
int tempo = rs.getInt("tempo");

contratoEncontrado = new Contrato(quartoAssociado, inicioContrato, tempo);
}

rs.close();
pstmt.close();
c.close();
return contratoEncontrado;
}

public ArrayList<Cliente> getClientes() throws Exception
{
ArrayList<Cliente> clientesEncontrados = new ArrayList<Cliente>();
Cliente clienteEncontrado = null;
Connection c = new ConnectionFactory().getConnection();
String query = "SELECT * FROM clientes";
PreparedStatement pstmt = c.prepareStatement(query);

ResultSet rs = pstmt.executeQuery();

while(rs.next())
{
String nome = rs.getString("nome");
String cpf = rs.getString("cpf");
String nascimento = rs.getString("nascimento");
String endereco = rs.getString("endereco");
String cartao = rs.getString("cartao");
int contratoID = rs.getInt("contrato_id");
boolean statusHospedagem = rs.getBoolean("status_hospedagem");

clienteEncontrado = new Cliente(nome, cpf, nascimento, endereco);
clienteEncontrado.setCartao(cartao);
clienteEncontrado.setContrato(buscarContrato(contratoID));
clienteEncontrado.setEstaHospedado(statusHospedagem);

clientesEncontrados.add(clienteEncontrado);
}

rs.close();
pstmt.close();
c.close();
return clientesEncontrados;
}
}


The "[SQLITE_BUSY] The database file is locked (database is locked)" error only occurs when I try to use the getClientes() method.


Any ideas?


Aucun commentaire:

Enregistrer un commentaire