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