/**/
package db_interface;
//package DataBase;
import business_layer.Result;
import business_layer.ResultMessage;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.Date;
import java.util.Calendar;
import java.io.*;
import java.io.FileWriter;
import java.util.ArrayList;
/**
*
* @author Alberto Mateos
*/
/****************************************************************************/
//This is what I think you said I was suppose to do. But I think I don´t know
// how can I return values if I don´t know how the database is implemented
public class DataBase {
public static Connection conn;
public static String DBName = "jdbc:sqlite:database.db";
public static String sFichero = "logfile_db.txt";
public static void setDBname(String dbname) {
try {
Class.forName("org.sqlite.JDBC");
} catch (ClassNotFoundException e) {
Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, e);
}
DataBase.DBName = "jdbc:sqlite:" + dbname;
}
private static void writeToLog(String txt)
{
//_______LOG FILE______@___
Date rightnow = Calendar.getInstance().getTime();
File f = new File(sFichero);
FileWriter fw;
try {
fw = new FileWriter(f, true);
fw.write(rightnow + txt+"\n");
fw.close();
//_____________________@___
} catch (IOException ex) {
Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
}
}
public static void Connect() { //conecta con la base de datos
try {
Class.forName("org.sqlite.JDBC");
} catch (ClassNotFoundException ex) {
Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
}
try {
conn = DriverManager.getConnection(DBName);
} catch (SQLException ex) {
Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
writeToLog("======> DATA BASE CONNECTION______(|) - ERROR: problem with setting the connection");
}
writeToLog("======> DATA BASE CONNECTION______(|)");
}
public static void Disconnect() { //se desconecta de la base de datos
try {
conn.close();
writeToLog("======> DATA BASE DISCONNECTION___(-)");
} catch (Exception ex) {
Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
writeToLog("======> DATA BASE DISCONNECTION___(-) - ERROR: problem with closing the connection");
}
}
//INSERT - DELETE - UPDATE - CREATE - DROP - ROLLBACK - COMMIT QUERIES
public static Result executeStatement(String querySQL){
Result res = new Result();
try {
Connect();
Statement stat = conn.createStatement();
stat.executeUpdate(querySQL);
String txtquery="======> " + querySQL +" :: run successfully";
writeToLog( txtquery);
stat.close();
Disconnect();
res.setResMessage(ResultMessage.OK);
} catch (Exception ex) {
Logger.getLogger(DataBase.class.getName()).log(Level.WARNING, null, ex);
res.addResult(ex.getMessage());
res.setResMessage(ResultMessage.DB_EX);
String txtquery="======> " + querySQL +"\n"+" ERROR: query discarded by the data base - "+ ex.getMessage();
writeToLog( txtquery);
}
return res;
}
//SELECT QUERY
public static Result executeQuery(String querySQL) {
Result res = new Result();
String row="";
try {
Connect();
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery(querySQL);
//the table names
if(rs.next()) {
row="";
int cols = rs.getMetaData().getColumnCount();
for(int i=1;i<cols;i++)
row+= (rs.getMetaData().getColumnName(i) + ",");
row+=rs.getMetaData().getColumnName(cols);
// row+=rs.getString(cols);
res.addResult(row);
}
//the actual data
rs = stat.executeQuery(querySQL);
while (rs.next()) {
row="";
int numCols = rs.getMetaData().getColumnCount();
//join row content
for (int i=1; i < numCols; i++) {
row+= (rs.getString(i) + ",");
}
row+=rs.getString(numCols);
res.addResult(row);
}
String txtquery="======> " + querySQL +" :: run successfully";
writeToLog( txtquery);
stat.close();
Disconnect();
res.setResMessage(ResultMessage.OK);
} catch (Exception ex) {
Logger.getLogger(DataBase.class.getName()).log(Level.WARNING, null, ex);
res.addResult(ex.getMessage());
res.setResMessage(ResultMessage.DB_EX);
String txtquery="======> " + querySQL +"\n"+" ERROR: query discarded by the data base - "+ ex.getMessage();
writeToLog( txtquery);
}
return res;
}
//for viewing
public static ArrayList<String> getTablesNames() {
ArrayList<String> result = new ArrayList<String>();
ResultSet res = null;
try {
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection(DBName);
Statement stat = conn.createStatement();
res = stat.executeQuery("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name");
while(res.next())
result.add(res.getString(1));
stat.close();
conn.close();
} catch (Exception ex) {
ex.printStackTrace();
}
return result;
}
public static core.DataViewObj getTableData(String tableName) {
core.DataViewObj result = new core.DataViewObj();
try {
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection(DBName);
Statement stat = conn.createStatement();
ResultSet res = stat.executeQuery("SELECT * FROM "+tableName);
boolean titleSet = false;
while(res.next()) {
int cols = res.getMetaData().getColumnCount();
int dataSize = result.data.size();
result.data.add(new ArrayList<String>());
for(int i=1;i<=cols;i++) {
if(!titleSet) result.title.add(res.getMetaData().getColumnName(i));
result.data.get(dataSize).add(res.getString(i));
}
titleSet = true;
}
stat.close();
conn.close();
} catch (Exception ex) {
ex.printStackTrace();
}
return result;
}
}
// //SELECT QUERY
// public static Result executeQuery(String querySQL) {
// try {
// Connect();
// Statement stat = conn.createStatement();
// ResultSet rs = stat.executeQuery(querySQL);
// while (rs.next()) {
// int numCols = rs.getMetaData().getColumnCount();
// //print row content
// for (int i = 1; i <= numCols; i++) {
// //print each column content
// System.out.print(rs.getString(i) + " ");
// }
// //print new line
// System.out.println();
// }
// stat.close();
// Disconnect();
// return
// } catch (java.sql.SQLException ex) {
// System.out.println(ex.getMessage());
// }
//
/*
/****************************************************************************/
//This first try is based on a already created data base, called "DBejemplo"
//I dont really know if this is better than the other way (implemented below)
//*****************************************************************************
/*public class DataBase {
public static void main(String[] args) {
try {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite://DBejemplo");
Statement stat = conn.createStatement();
stat.execute("DELETE FROM Personas");
PreparedStatement prep = conn.prepareStatement("INSERT INTO Personas (Apellido,IDpersona,Nombre) VALUES (?, ?, ?);");
prep.setString(1, "de los Palotes");
prep.setInt(2,1);
prep.setString(3,"Deitel");
prep.addBatch();
prep.setString(1, "Garcia");
prep.setInt(2,2);
prep.setString(3,"Ceballos");
prep.addBatch();
prep.setString(1, "Perez");
prep.setInt(2,3);
prep.setString(3,"Joyanes Aguilar");
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
ResultSet rs = stat.executeQuery("select * from Personas;");
while (rs.next()) {
System.out.println("ID_PERSONA...: " + rs.getString("IDpersona"));
System.out.println("NOMBRE.....: " + rs.getString("Nombre"));
System.out.println("APELLIDO.....: " + rs.getString("Apellido"));
System.out.println("-----------------------------------");
}
rs.close();
stat.close();
conn.close();
} catch (SQLException ex) {
System.out.println(ex.getMessage());
} catch (ClassNotFoundException ex) {
System.out.println(ex.getMessage());
}
}
}
//*****************************************************************************
Other useless try
//*****************************************************************************
=====================================================================================================
Class.forName(DataBase.classForName).newInstance();
connection = DriverManager.getConnection(DataBase.url,DataBase.login,DataBase.password);
} catch (InstantiationException ex) {
Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
} catch (IllegalAccessException ex) {
Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
}catch(SQLException ex){
System.out.println(ex.getMessage());
}catch(ClassNotFoundException ex){
System.out.println(ex.getMessage());
}
}
public static void Disconnect(){ //se desconecta de la base de datos
try {
connection.close();
} catch (SQLException ex) {
Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
}
}
public static void ExecuteUpdateQuery(String querySQL){
try {
DataBase.Connect();
java.sql.Statement statement = DataBase.connection.createStatement();
statement.executeUpdate(querySQL);
statement.close();
DataBase.Disconnect();
} catch (java.sql.SQLException ex) {
java.util.logging.Logger.getLogger("SQL").log(java.util.logging.Level.SEVERE, null, ex);
System.out.println(ex.getMessage());
}
}
public static void ExecuteQuery(String querySQL){
try {
DataBase.Connect();
java.sql.Statement statement = DataBase.connection.createStatement();
statement.execute(querySQL);
statement.close();
DataBase.Disconnect();
} catch (java.sql.SQLException ex) {
java.util.logging.Logger.getLogger("SQL").log(java.util.logging.Level.SEVERE, null, ex);
System.out.println(ex.getMessage());
}
}
}
* */