IT Blog

Majoritatea programelor din ziua de astazi folosesc ca si backend un server de baze de date. Nu putine au fost situatiile cand trebuia ca pentru aplicatiile la care lucrez sa creez cateva clase pentru conexiunile la baze de date, de aceea am considerat ca pentru o dezvoltare mai usoara ar fi nevoie sa imi fac un pachet de clase care sa fie reutilizabil si usor de modificat.
In continuarea acestui articol vor fi prezentate cateva clase care pot fi folosite in orice aplicatie java pentru conectarea la un server de baze de date. Din motive de codare, numele claselor, al metodelor si al obiectelor vor fi in engleza. De asemenea, tin sa precizez ca nu sunt foarte bun pe partea de explicatii, de aceea in cazul in care exista nelamuriri nu existati sa intrebati.

Incep cu o clasa abstracta ce va fi clasa parinte pentru celelalte clase folosite. Aceasta clasa trebuie sa contina declaratii pentru metode specifice JDBC-ului : getConnection, freeConnection, closeStatement si realeaseAllConnection. De asemenea aceasta clasa va contine si un membru de tip Properties si care va contine datele de conectare (url, user, parola, etc…) la serverul de baze de date.

getConnection () – intoarce un java.sql.Connection (fie un obiect deja existent in pool, fie creeaza un obiect nou)

freeConnection() – inchide o conexiune din pool. Aceasta conexiune este transmisa ca si parametru la metoda

releaseAllConnection() - inchide toate conexiunile existente in pool.

closeStatement() – inchide un obiect de tipul java.sql.Statement care este trimis ca parametru

Listing 1. Clasa base.database.pool.AbstractDatabasePool

package base.database.pool;

import java.sql.Connection;
import java.sql.Statement;

import base.properties.ApplicationProperties;

public abstract class AbstractDatabasePool
{
/**
* this is the ApplicationProperties instance.
*/
protected ApplicationProperties props = ApplicationProperties.getInstance();

/**
* Method getConnection.Returns a Connection from the pool,
* or creates a new one,
* @return Connection Connection
*/
public abstract Connection getConnection();

/**
* Method freeConnection.Releases the Connection to the pool.
* @param conn The connection to free.
*/

public abstract void freeConnection(Connection conn);

/**
* Method releaseAllConnections. Closes all available connections.
*/

public abstract void releaseAllConnections();

/**
* Method closeStatement.Closes the specified Statement.
* @param stmt The Statement you want to close.
*/
public abstract void closeStatement(Statement stmt);

}

Dupa cum se poate observa in listingul anterior, exista si un membru props, care va fi folosit pentru incarcarea datelor de conectare la baza de date. Aceste date sunt trecute intr-un fisier .properties care va fi folosit la instantierea clasei ApplicationProperties.
Listing 2. Clasa base.properties.ApplicationProperties

package base.properties;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Properties;
import java.util.StringTokenizer;

import base.exception.BaseException;

/**
* This class is a singletone that is used to share some general properties
* by all the modules from JBase project. The instance of the class holds the
* properties read from jbase.properties file.
*
*
*/
public class ApplicationProperties extends Properties
{

private static ApplicationProperties instance;
private Logger appLogger = null;

/**
* Constructor for ApplicationProperties.
*/
public ApplicationProperties()
{
super();
}

/**
* Constructor for ApplicationProperties.
* @param arg0
*/
private ApplicationProperties(Properties arg0)
{
super(arg0);
}

/**
* Initialize the ApplicationProperties singletone from a properties
* file.
*
* @param propertiesFileName – the absolute filepath of the properties file
* @throws JBaseException – when the object is already initialized or when
* the properties file could not be accessed or is
* in a wrong format.
*/
public synchronized static void initialize(String propertiesFileName)
throws BaseException
{
if (instance != null)
{
throw new BaseException(“JBaseProperties already initialized !”);
}

System.out.println(“Loading ” + propertiesFileName + “…”);
instance = new ApplicationProperties();

Throwable th = null;

try
{
instance.load(
new BufferedInputStream(
new FileInputStream(propertiesFileName)));
}
catch (FileNotFoundException e)
{
th = e;
}
catch (IOException e)
{
th = e;
}
if (th != null)
{
throw new BaseException(th);
}

if (ApplicationProperties.printMissing == true)
{
JBaseUtil.getMissingProperties(instance);
}
}

/**
* Initialize the ApplicationProperties singletone from a
* Properties object.
*
* @param properties – the Properties object
* @throws BaseException – when the object is already initialized or when
* the properties file could not be accessed or is
* in a wrong format.
*/
public static void initialize(Properties properties) throws BaseException
{
if (instance != null)
{
throw new BaseException(“ApplicationProperties already initialized !”);
}
instance = new ApplicationProperties(properties);

}

/**
* Returns the instance.
* @return ApplicationProperties
*/
public static ApplicationProperties getInstance()
{
return instance;
}

/**
* Returns the db.maxConn property from app.properties
*
* @return int – Returns the dbMaxConn.
*/
public int getDbMaxConn()
{
try
{
return Integer.parseInt(this.getProperty(“db.maxConn”));
}
catch (Exception e)
{
return 0;
}
}

/**
* Returns the db.password property from app.properties
*
* @return String – Returns the dbPassword.
*/
public String getDbPassword()
{
return this.getProperty(“db.password”);
}

/**
* Returns the db.url property from app.properties
*
* @return String – Returns the dbUrl.
*/
public String getDbUrl()
{
return this.getProperty(“db.url”);
}

/**
* Returns the db.user property from app.properties
*
* @return String -Returns the dbUser.
*/
public String getDbUser()
{
return this.getProperty(“db.user”);
}

/**
* Returns the db.class property from app.properties
*
* @return String – Returns the dbClass.
*/
public String getClassName()
{
return this.getProperty(“db.class”);
}

/**
* Returns the db.initConn property from app.properties
*
* @return int – Returns the dbInitConn.
*/
public int getInitConn()
{
try
{
return Integer.parseInt(this.getProperty(“db.initConn”));
}
catch (Exception e)
{
return 0;
}
}

/**
* Returns the db.connTimeout property from app.properties
*
* @return long – Returns the dbConnTimeout.
*/
public long getConnTimeout()
{
try
{
return Long.parseLong(this.getProperty(“db.connTimeout”));
}
catch (Exception e)
{
return 0;
}
}

/**
* Returns the db.expiryTime property from app.properties
*
* @return long – Returns the dbExpiryTime
*/
public long getExpiryTime()
{
try
{
return Long.parseLong(this.getProperty(“db.expiryTime”));
}
catch (Exception e)
{
return 0;
}
}

/**
* Oracle driver has a log . If you set this property, there will be
* created a file with this name in db.logPath that contains oracle log
*
* @return string representing oracle log filename
*/
public String getOracleOptionalLogFileName()
{
return this.getProperty(“db.oracleLogFileName”);
}

/**
* SqlServer driver has a log . If you set this property, there will be
* created a file with this name in db.logPath that contains sqlServer log
*
* @return string representing sqlServer log filename
*/
public String getSqlServerOptionalLogFileName()
{
return this.getProperty(“db.sqlServerLogFileName”);
}
/**
* Postgres driver has a log . If you set this property, there will be
* created a file with this name in db.logPath that contains postgres log
*
* @return string representing postgres log filename
*/
public String getPostgresOptionalLogFileName()
{
return this.getProperty(“db.postgresLogFileName”);
}

/**
* Returns the db.dbName property from app.properties
*
* @return String -Returns the database name used to the application.
*/
public String getDbName()
{
return this.getProperty(“db.dbName”);
}

/**
* Returns the db.serverName property from jbase.properties
*
* @return String -Returns the server name used to the application.
*/
public String getServerName()
{
return this.getProperty(“db.serverName”);
}

/**
* Returns the db.portNumber property from app.properties
*
* @return String -Returns the port number of database.
*/
public String getPortNumber()
{
return this.getProperty(“db.portNumber”);
}

}

Nu voi insista foarte mult pe explicatii privind aceasta clasa, deoarece consider ca numele metodelor folosite sunt suficient de explicite. Trebuie sa precizez doar ca aceasta clasa poate fi folosita si pentru alte zone ale aplicatiei. De exemplu poate contine informatiile necesare pentru trimiterea de emailuri si proprietatile respective pot fi folosite in zone specifice din aplicatie.

Cele doua clase prezentate anterior stau la baza claselor ce vor efectua conexiunile catre baza de date. In continuare vor fi prezentate doar clasele pentru conectarea la Mysql si Oracle. Pentru alte servere de baze de date, puteti lasa un comentariu la acest articol si voi creea si clasa respectiva.

Pentru conectarea la Mysql va fi folosit driverul JDBC pus la dispozitie de catre mysql.com, mai exact clasa MysqlDataSource. Fisierul .jar folosit de mine, este pus la dispozitie in zona Download existenta la finalul acestui articol.

Listing 3. Clasa base.database.pool.MySqlDbPool ce poate fi folosita pentru conectarea la un server mysql

package base.database.pool;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import base.database.DatabaseException;
import base.database.DatabaseRuntimeException;
import base.database.DbLogger;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

/**
*
*/
public class MySqlDbPool extends AbstractDatabasePool
{
/**
* The DataSource object used to retrieve the connections
* to database.
*/
private MysqlDataSource ds = null;

/**
* Constructs a new MySqlDbPool object.
*
* @throws DatabaseException if fails
*/
public MySqlDbPool() throws DatabaseException
{
this.ds = new MysqlDataSource();
this.ds.setUrl(props.getDbUrl());
}

/**
*
*/
public Connection getConnection()
{
try
{
return this.ds.getConnection(props.getDbUser(),
props.getDbPassword());
}

catch (SQLException e)
{
// Do what you want

throw new DatabaseRuntimeException(e);
}
};

/**
* @see AbstractDatabasePool#releaseAllConnections()
*/
public void releaseAllConnections()
{
// Do what you want
};

/**
* @see AbstractDatabasePool#closeStatement(Statement)
*/
public void closeStatement(Statement stmt)
{
if (stmt == null)
{
return;
}

try
{
stmt.close();
}

catch (SQLException e)
{
// Do what you want
}
};

/**
* @see AbstractDatabasePool#freeConnection
*/
public void freeConnection(Connection conn)
{
if (conn == null)
{
return;
}

try
{
conn.close();
}

catch (SQLException e)
{
// Do what you want
}
}

}

Clasa care se ocupa de conectarea la Oracle, poate parea un picut mai complicata, deoarece sistemul Oracle are posibilitatea de a pune un log separat, fisier de log ce este definit in fisierul de proprietati folosit la inceput.

Listing 4. Clasa base.database.pool.OraDbPool ce poate fi folosita pentru conectarea la un server oracle

package base.database.pool;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.Hashtable;
import java.util.Iterator;

import oracle.jdbc.pool.OracleConnectionCacheImpl;
import oracle.jdbc.pool.OracleConnectionPoolDataSource;
import base.database.DatabaseException;
import base.database.DatabaseRuntimeException;

/**
*
*
*/
public class OraDbPool extends AbstractDatabasePool
{
private class InUseConnection
{
private InUseConnection(Date aDate, StackTraceElement[] aStack)
{
this.getDate = aDate;
this.stack = aStack;
}
private Date getDate = null;
private StackTraceElement[] stack = null;

private Hashtable usedConnections = null;

/** flag that is set true if pool was closed */
private boolean isClosed = false;

/**
* The Oracle pool data source.
*/
private OracleConnectionPoolDataSource ocpds = null;

/**
* The Oracle cache implementation.
*/
private OracleConnectionCacheImpl occ = null;

/**
* @see java.lang.Object#Object()
*/
public OraDbPool() throws DatabaseException
{
super();
initialize();
}

/**
* Here we’ll do all the initializations for this connection pool.
* First, a oracle data source is defined, then a cache for this source
* is created, so connections can be managed automatically.
*
*/
private void initialize() throws DatabaseException
{
if (props == null)
{
// do what you want
throw new DatabaseException(“Properties are not loaded !”);
}

String oracleLog = props.getOracleOptionalLogFileName();
String logDir = props.getDbLogPath();

// create pool
try
{
ocpds = new OracleConnectionPoolDataSource();
if (oracleLog != null && logDir != null)
{
ocpds.setLogWriter(
new PrintWriter(
new FileOutputStream(new File(logDir, oracleLog))));
}
}
catch (FileNotFoundException e)
{
// do what you want
}
catch (SQLException e)
{
// do what you want
throw new DatabaseException(e);
}

ocpds.setURL(props.getDbUrl());
ocpds.setUser(props.getDbUser());
ocpds.setPassword(props.getDbPassword());
int maxconn = props.getDbMaxConn();

try
{
occ = new OracleConnectionCacheImpl(ocpds);
if (maxconn > 0)
{
occ.setMaxLimit(maxconn);
}
occ.setCacheScheme(OracleConnectionCacheImpl.DYNAMIC_SCHEME);
}
catch (SQLException e)
{
// do what you want
throw new DatabaseException(e);
}
}

/**
* @see AbstractDatabasePool#getConnection()
*/

public Connection getConnection()
{
if (this.isClosed)
{
// // do what you want
return null;
}

int activeConnections = occ.getActiveSize();
if (activeConnections > 20)
{
// do what you want
}

try
{
Connection conn = occ.getConnection();
if (debugMode)
{
StackTraceElement[] stack = null;
try
{
throw new RuntimeException();
}
catch (RuntimeException ex)
{
stack = ex.getStackTrace();
usedConnections.put(
conn,
new InUseConnection(new Date(), stack));
}
}

return conn;
}
catch (SQLException e)
{
// do what you want
throw new DatabaseRuntimeException(e);
}
}

/**
* @see AbstractDatabasePool#releaseAllConnections()
*/

public void releaseAllConnections()
{
if (this.isClosed)
{
// do what you want
return;
}

if (occ == null)
{
// do what you want
return;
}

try
{
occ.close();
this.isClosed = true;
}
catch (SQLException e)
{
// do what you want
}
}

/**
* @see AbstractDatabasePool#closeStatement(Statement)
*/
public void closeStatement(Statement stmt)
{

if (stmt == null)
{
// do what you want
return;
}

try
{
stmt.close();
}
catch (SQLException e)
{
// do what you want
}
}

/**
* @see AbstractDatabasePool#freeConnection(Connection)
*/
public void freeConnection(Connection conn)
{
if (this.isClosed)
{
// do what you want
}

if (conn == null)
{
// do what you want
return;
}

try
{
conn.rollback();
conn.setAutoCommit(true);
conn.close();
}
catch (SQLException e)
{
// do what you want
}

}

}

In continuare, prezint pe scurt, felul in care pot fi folosite aceste clase. In exemplu meu, aplicatia este web-base si initializarile se fac in metoda contextInitialized(ServletContextEvent sc) definita intr-o clasa ce extinde javax.servlet.ServletContextEvent

Listing 5. Metoda dbInitialize ce realizeaza initierea conexiunii catre baza de date

public static void dbInitialize () throws DatabaseException
{
if (dbInstance != null)
{
dbInstance .releaseAllConnections();
dbInstance = null;
}

ApplicationProperties dbProps = ApplicationProperties.getInstance();
if (dbProps == null)
{
throw new DatabaseException(“The <> ”
+ “was not initialized !”);
}
String className = dbProps.getClassName();

try
{
Class classInstance = Class.forName(className);
dbInstance = (AbstractDatabasePool) classInstance.newInstance();
}
catch (Exception e)
{
throw new DatabaseException(
“Class not found ->>> ” + className,
e);
}

}

Obiectul dbInstance este definit de tipul AbstractDatabasePool. Dupa cum se poate observa din codul anterior, la initializare se incarca doar clasa ce este definita in app.properties la intrarea db.class.
De exemplu pentru conectare la MySQL aceasta linie este:
db.class=base.database.pool.MySqlDbPool
Recomandarea mea este ca aceasta metoda sa fie definita staticintr-o clasa separata, gen DbFactory. Puteti gasi aceata clasa, DbFactory, in codul sursa atasat la aceste articol.

Listing 6. Metoda contextInitialized(ServletContextEvent sc) ce este folosita pentru initializa. In codul de aici este prezentata doar sectiunea legata de bazele de date

public void contextInitialized(ServletContextEvent sc)
{
try
{

ApplicationProperties.initialize(APPLICATION_PROPERTIES);
DBFactory.dbInitialize();

}
catch (Throwable th)
{
th.printStackTrace();
}

In acesta metoda se incarca fisierul de proprietati, definit intr-o variabila statica APPLICATION_PROPERTIES. Recomand ca acest fisier sa fie definit in functie de context-ul in care ruleaza aplicatia (sc.getServletContext().getRealPath(“”) ).

Listing 7. O metoda prin care se preia din baza de date un user, pe baza unui id. Fraza SQL folosita pentru aceata metoda este definita intr-o variabila statica, SQL_GET_USER_BY_ID.

public static User getUserById(long id)
throws UserException
{
Connection conn = null;
PreparedStatement stmt = null;

try
{
conn = PortalApp.dbPool.getConnection();
stmt = conn.prepareStatement(SQL_GET_USER_BY_ID);
stmt.setLong(1, id);

ResultSet rset = stmt.executeQuery();

if (rset.next() == false)
{
return null;
}

return buildUserObjectFromRS(rset);
}

catch (SQLException e)
{
throw new UserException(“Failed to retrieve group ”
+ id + ” from database”, e);
}

finally
{
PortalApp.dbPool.closeStatement(stmt);
}
}

Ce face aceata metoda?
Preia conexiunea initializata in aplicatie (PortalApp este clasa care contine mai multe variabile de tip static pentru a putea fi folosite peste tot in aplicatie. Aceasta aplicatie are si o metoda initialize care este apelata in metoda contextListener si primeste mai multi parametri, printre care si cele legate de baza de date. Ramane la latitudinea programatorului cum creeaza aceasta clasa, de aceea nu face obiectul in aceasta prezentare.
Dupa ce este preluata conexiunea respectiva, se executa fraza sql necesara, se preia rezultatul executiei si se contruieste obiectul ce trebuie intors. Continutul din metoda buildUserObjectFromRS depinde de membri clasei User precum si de legaturile acestora cu datele din baza de date, de aceea nu este prezentat in acest articol.

Download

1. Codul sursa

2. Fisierele .jar ce contin drivere pentru conectarile la bazele de date

Conector pentru Mysql – mysql-connector-java-3.0.11-stable-bin.jar
Conector pentru Oracle – classes12.jar
Conector pentru PostgreSql – postgresql-8.0-311.jdbc3.jar
Conector pentru ODBC – ojdbc14.jar

Reamintesc faptul ca daca sunteti interesati si de conexiuni catre alte servere de baze de date, puteti sa lasati un comentariu la acest articol