/*
* GeoPMSDB.java
*
* Created on 22. Juli 2007, 12:49
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/
package geopms;
import java.sql.*;
import java.sql.PreparedStatement;
import java.io.*;
import java.util.logging.*;
/**
*
* @author lazarus
*/
public class GeoPMSDB
{
private static Logger logger = Logger.getLogger("com.geores.geopms");
// Globale Variablen
private Connection con = null;
//private ResultSet rs = null;
private String DriverName = "com.mysql.jdbc.Driver";
private String DBurl = "jdbc:mysql://";
private String db_name = "";
private String db_password = "";
// Meldungen
public static final String error_db_contact = "Database connection error, please check properties";
public static final String error_query = "Database query error, please check properties";
public static final String error_tbl_query = "Database change query error, plase check properties";
public static String msg = "Database connected!";
//------------------------------------------------------------------------
// Konstruktor mit DB-Frame
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
public GeoPMSDB()
{
}
//------------------------------------------------------------------------
// setDriverName
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
public void setDriverName(String dn)
{
DriverName = dn;
}
//------------------------------------------------------------------------
// setDBUrl
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
public void setDBUrl(String db)
{
DBurl = db;
}
//------------------------------------------------------------------------
// setDBUser
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
public void setDBParam(String user,String password)
{
db_name = user;
db_password = password;
}
//------------------------------------------------------------------------
// getDBUser
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
public String getDBUser()
{
return db_name;
}
//------------------------------------------------------------------------
// getDBPassword
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
public String getDBPassword()
{
return db_password;
}
//------------------------------------------------------------------------
// getDBPasswort
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
public String getDBMSG()
{
return msg;
}
//------------------------------------------------------------------------
// getDriverName
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
public String getDriverName()
{
return DriverName;
}
//------------------------------------------------------------------------
// String getDBUrl
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
public String getDBUrl()
{
return DBurl;
}
//------------------------------------------------------------------------
// getFieldRows
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
public String getFieldRow(String field ,String where,String tbl)
{
ResultSet rs = null;
String sql = "";
String result = "";
try
{
Class.forName(DriverName);
con = DriverManager.getConnection(DBurl,db_name,db_password);
Statement state = con.createStatement();
if(where.equals(""))
sql = "SELECT "+field+" FROM "+tbl;
else
sql = "SELECT "+field+" FROM "+tbl+" WHERE "+where;
rs = state.executeQuery(sql);
rs.next();
if(rs.getRow() > 0)
{
result = rs.getString(1);
}
else
{
result = null;
}
state.clearWarnings();
con.close();
}
catch(SQLException e)
{
logger.severe("SQL error: "+e.getMessage());
msg = error_tbl_query;
return null;
}
catch(Exception e)
{
logger.severe("System error: "+e.getMessage());
msg = error_db_contact;
return null;
}
return result;
}
//------------------------------------------------------------------------
// getFieldRows
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
public ResultSet getAllRows(String where,String tbl)
{
ResultSet rs = null;
String sql = "";
try
{
Class.forName(DriverName);
con = DriverManager.getConnection(DBurl,db_name,db_password);
Statement state = con.createStatement();
if(where.equals(""))
sql = "SELECT * FROM "+tbl;
else
sql = "SELECT * FROM "+tbl+" WHERE "+where;
rs = state.executeQuery(sql);
rs.next();
}
catch(SQLException e)
{
logger.severe("SQL error: "+e.getMessage());
msg = error_tbl_query;
}
catch(Exception e)
{
logger.severe("System error: "+e.getMessage());
msg = error_db_contact;
return null;
}
return rs;
}
//------------------------------------------------------------------------
// getFieldRowsDistinct
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
public ResultSet getFieldRowsDistinct(String field ,String where,String tbl)
{
ResultSet rs = null;
String sql = "";
try
{
Class.forName(DriverName);
con = DriverManager.getConnection(DBurl,db_name,db_password);
Statement state = con.createStatement();
if(where.equals(""))
sql = "SELECT DISTINCT "+field+" FROM "+tbl;
else
sql = "SELECT DISTINCT "+field+" FROM "+tbl+" WHERE "+where;
rs = state.executeQuery(sql);
state.clearWarnings();
con.close();
}
catch(SQLException e)
{
logger.severe("SQL error: "+e.getMessage());
msg = error_tbl_query;
}
catch(Exception e)
{
logger.severe("System error: "+e.getMessage());
msg = error_db_contact;
return null;
}
return rs;
}
//------------------------------------------------------------------------
// setQuery
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
public boolean setQuery(String spalte, String values, String tbl)
{
int res = 0;
String sql = "";
try
{
Class.forName(DriverName);
con = DriverManager.getConnection(DBurl,db_name,db_password);
Statement state = con.createStatement();
sql = "INSERT INTO "+tbl+" ("+spalte+") VALUES ("+values+")";
res = state.executeUpdate(sql);
state.clearWarnings();
con.close();
}
catch(SQLException e)
{
logger.severe("SQL error: "+e.getMessage());
msg = error_tbl_query;
}
catch(Exception e)
{
logger.severe("System error: "+e.getMessage());
msg = error_db_contact;
return false;
}
return true;
}
//------------------------------------------------------------------------
// delQuery
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
public boolean delQuery(String where, String tbl)
{
int res = 0;
String sql = "";
try
{
Class.forName(DriverName);
con = DriverManager.getConnection(DBurl,db_name,db_password);
Statement state = con.createStatement();
sql = "DELETE FROM "+tbl+" WHERE "+where;
res = state.executeUpdate(sql);
state.clearWarnings();
con.close();
}
catch(SQLException e)
{
logger.severe("SQL error: "+e.getMessage());
msg = error_tbl_query;
}
catch(Exception e)
{
logger.severe("System error: "+e.getMessage());
msg = error_db_contact;
return false;
}
return true;
}
//------------------------------------------------------------------------
// changeQuery
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
public boolean changeQuery(String set, String where, String tbl)
{
int rs = 0;
String sql = "";
try
{
Class.forName(DriverName);
con = DriverManager.getConnection(DBurl,db_name,db_password);
Statement state = con.createStatement();
sql = "UPDATE "+tbl+" SET "+set+" WHERE "+where;
rs = state.executeUpdate(sql);
state.clearWarnings();
con.close();
}
catch(SQLException e)
{
logger.severe("SQL error: "+e.getMessage());
msg = error_tbl_query;
}
catch(Exception e)
{
logger.severe("System error: "+e.getMessage());
msg = error_db_contact;
return false;
}
return true;
}
//------------------------------------------------------------------------
// writeBinData
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
// public boolean writeBinData(String set, String where, String tbl, FileInputStream fis)
public boolean writeBinData(String set, String where, String tbl, byte[] data)
{
try
{
Class.forName(DriverName);
con = DriverManager.getConnection(DBurl,db_name,db_password);
String sql = "UPDATE "+tbl+" SET "+set+" = ? WHERE "+where;
PreparedStatement state = con.prepareStatement(sql);
// state.setBinaryStream(1, fis, fis.available());
state.setBytes(1,data);
state.executeUpdate();
state.clearWarnings();
con.close();
}
catch(SQLException e)
{
logger.severe("SQL error: "+e.getMessage());
msg = error_tbl_query;
return false;
}
catch(Exception e)
{
logger.severe("System error: "+e.getMessage());
msg = error_db_contact;
return false;
}
return true;
}
public boolean closeCon()
{
try
{
if(!con.isClosed())
{
con.close();
}
}
catch(SQLException e)
{
logger.severe("SQL error: "+e.getMessage());
msg = error_tbl_query;
return false;
}
catch(Exception e)
{
logger.severe("System error: "+e.getMessage());
msg = error_db_contact;
return false;
}
return true;
}
//------------------------------------------------------------------------
// getFieldRows
// Version 1.00 Stand: 22.07.2007 15:30 Marcus Landschulze
//------------------------------------------------------------------------
public int existTable(String tbl)
{
ResultSet rs = null;
String sql = "";
int result = 1;
try
{
Class.forName(DriverName);
con = DriverManager.getConnection(DBurl,db_name,db_password);
Statement state = con.createStatement();
sql = "SELECT id FROM "+tbl;
rs = state.executeQuery(sql);
state.clearWarnings();
con.close();
}
catch(SQLException e)
{
logger.severe("SQL error: "+e.getMessage());
msg = error_tbl_query;
return 0;
}
catch(Exception e)
{
logger.severe("System error: "+e.getMessage());
msg = error_db_contact;
return 0;
}
return result;
}
}