[sip-comm-dev] Developer documentation for Database Service


#1

Hi all

I have prepared dev documentation for the database service developed by me
during GSoC 2009.Kindly have a look and let me know if its ok.

Regards

Ajay

DatabaseService Developer Documentation

Description

The database service provides access to a relational database embedded with
SIP Communicator, automatically handling database specific configuration
like establishing and closing database connections, preventing duplicate
connections etc.

The main design goal is to provide a simple and DBMS independent interface
that allows a developer to use the database through simple SQL without
having to worry about the database URLs, connection settings,passwords etc.
Using it

···

*
*

*Obtaining a connection to the database*

To use the database,you first need to create a *DatabaseConnection* object
using the *DatabaseService*'s *createConnection(String DBName, boolean
createDB)* function.

Please note that the default implementation does not allow two concurrent
connections to the same database.

*
*

*Executing SQL statements*

To execute SQL statements use the following functions on a *
DatabaseConnection* object :

*int executeUpdate(String sql) *: Execute a SQL DML or DDL statement

*ResultSet executeQuery(String sql) *: Execute a SQL query

Eg: *dbCon.executeUpdate(“CREATE TABLE EMPLOYEES(SALARY DECIMAL(10,2) ,*

*ID INTEGER” );*

*ResultSet rs=dbCon.executeUpdate(“SELECT * FROM EMPLOYEES” );*

*Using PreCompiled SQL statements*

Precompiled SQL statements are often needed for performance or
security(preventing SQL injection).

To precompile a SQL statement use *PrecompiledStatement
prepareStatement(String sql).*

Use the *PrecompiledStatement*'s *setParameter(int parameterIndex, Object x,
int targetSqlType) *function or its overloaded variants to set the variable
parameters of the *PrecompiledStatement *and use *ResultSet executeQuery()*or
*int executeUpdate() *to execute the precompiled statement.

Eg :

*PrecompiledStatement pstmt = dbCon.precompileStatement("UPDATE EMPLOYEES*

*SET SALARY = ? WHERE ID = ?");*

*pstmt.setParameter(1, 153833.00, java.sql.Types.DECIMAL);*

*pstmt.setParameter(2, 110592, java.sql.Types.INTEGER);*

*psmt.executeUpdate();*

*Transaction Support*

To execute a group of statements as a single transaction, start a
transaction using *startTransaction(),*

commit a sucessful transaction using *commitTransaction()* or roll back an
unsucessful transaction using *rollBackTransaction() *functions on
a*DatabaseConnection
*object.

*Getting Table Information*

Use the following functions to get information about the tables in the
database:

*boolean isTableExisting(String tableName)*

*List<String> getExistingTableNames()*

*String[] getColumnNames(String tableName)*

*Terminating a Database Connection*

Use the *DatabaseConnection*'s close() function to close a connection to the
database.

Note that the default implementation automatically closes all open
connections to the database before terminating the service.