Package com.smartfoxserver.db

Class SFSDBManager

java.lang.Object

com.smartfoxserver.db.SFSDBManager

All Implemented Interfaces:

com.smartfoxserver.bitswarm.service.IService, IDBManager

public class SFSDBManager
extends Object

SFSDBManager is the default implementation of the IDBManager interface provided by the SmartFoxServer platform.
It manages the connection to a database using either JDBC native drivers or JDBC-ODBC bridge and providing configurable connection pooling for optimal performance and resource usage.

Each Zone runs its own DbManager which can be configured via the Zone Configurator module in the SFS AdminTool. Additionally a Zone can instantiate multiple DbManagers via server side code. A typical scenario for this is when the application requires to connect to multiple databases.

See Also:

• DBConfig

Field Summary

Fields

Modifier and Type	Field	Description
protected boolean	active
protected final DBConfig	config
protected final org.slf4j.Logger	log
protected final String	name
protected Zone	parentZone

Constructor Summary

Constructors

Constructor	Description
SFSDBManager(DBConfig config)

Method Summary

Modifier and Type	Method	Description
void	destroy(Object o)
Object	executeInsert(String sql, Object[] params)	Executes a SQL INSERT command returning the key of the inserted row
ISFSArray	executeQuery(String sql)	This is a small variation on IDBManager.executeQuery(String, Object[]) where no additional SQL parameter is used.
ISFSArray	executeQuery(String sql, Object[] params)	Perform a SQL query and return a structured object based on SFSArray and SFSObject.
void	executeUpdate(String sql)	Executes a non-query SQL command such as INSERT, UPDATE, DELETE etc...
void	executeUpdate(String sql, Object[] params)	Executes a non-query SQL command such as INSERT, UPDATE, DELETE etc...
int	getActiveConnections()	Get the number of pooled connections currently active
DBConfig	getConfig()	Get the configuration details of the JDBC connection and connection pool
Connection	getConnection()	Get a direct reference to the JDBC connection object.
int	getIdleConnections()	Get the number of pooled connections currently idle
String	getName()
int	getThreadsAwaitingConnections()	Get the number of threads awaiting on a connection
int	getTotalConnections()	Get the total number of connections
void	handleMessage(Object arg0)
void	init(Object o)
boolean	isActive()	True if the Service is active
void	setName(String name)

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Details

parentZone

protected Zone parentZone

active

protected boolean active

config

protected final DBConfig config

name

protected final String name

log

protected final org.slf4j.Logger log

Constructor Details

SFSDBManager

public SFSDBManager(DBConfig config)

Parameters:

config - the configuration settings for the JDBC connection and connection pool

Method Details

init

public void init(Object o)

Specified by:

init in interface com.smartfoxserver.bitswarm.service.IService

destroy

public void destroy(Object o)

Specified by:

destroy in interface com.smartfoxserver.bitswarm.service.IService

getConnection

public Connection getConnection()
                         throws SQLException

Get a direct reference to the JDBC connection object. This way you can access the underlying pooled connection and perform any JDBC API call directly. The connection object must be returned to the connection pool once you are finished using it. This is done by calling the connection's close() method.

An example of a code template would be:

   try
        {
                // An example query ... it could be anything
                sql = "SELECT * FROM table";
                conn = getParentZone().getDBManager().getConnection();
                stmt = conn.prepareStatement(sql);
                
                ResultSet resultSet = stmt.executeQuery();
                
                // More code here...
        }
        
        // Not mandatory
        catch (SQLException)
        {
                // do something about it
        }
        
        // Mandatory! Close connection before leaving this method
        finally
        {
                if (stmt != null)
                        stmt.close();
                
                if (conn != null)
                        conn.close();
        }
 

Returns:

the pooled JDBC connection

Throws:

SQLException

executeQuery

public ISFSArray executeQuery(String sql)
                       throws SQLException

This is a small variation on IDBManager.executeQuery(String, Object[]) where no additional SQL parameter is used. Please see IDBManager.executeQuery(String, Object[])

Parameters:

sql - the SQL code

Returns:

the SFSArray representing the result set

Throws:

SQLException - reports any errors related with the execution of the SQL query

executeQuery

public ISFSArray executeQuery(String sql,
 Object[] params)
                       throws SQLException

Perform a SQL query and return a structured object based on SFSArray and SFSObject. This is a simplified technique that provides the query result(s) in a convenient format, ready to be sent to the client(s).

The SQL code can include placeholders (using a question mark) and an array of parameters that will be used to populate them, just like when using prepared statements via JDBC. Example:

        executeQuery("SELECT * FROM Users WHERE age > ? AND country=?", new Object[] {35, "Sweden"}); 
 

The structure of the returned object is as follows:

SFSArray: represents the result set. It contains all the selected records in form of SFSObject(s)

• Index 0: SFSObject (record)
  • key (field name): value
  • key (field name): value
  • etc...
...
...
...

• Index N: SFSObject (record)
  • key (field name): value
  • key (field name): value
  • etc...

Data types from the database are translated to SFSObject types according to this table:

SQL Type	SFSObject Type
NULL	NULL
BOOLEAN	BOOLEAN
DATE	LONG (Unix timestamp)
FLOAT, DECIMAL, DOUBLE, REAL	DOUBLE
TINYINT, SMALLINT, INTEGER	INTEGER
CHAR, VARCHAR, LONGVARCHAR	UTF_STRING
NCHAR, NVARCHAR, LONGNVARCHAR	UTF_STRING
TIMESTAMP	LONG
BIGINT (up to 2^63)	LONG
LONGVARBINARY, BLOB	BYTE_ARRAY

Parameters:

sql - the SQL code. Placeholders for parameters can be used such as: SELECT * FROM Users WHERE name=?

params - An array of objects that will be used to populate the placeholders in the SQL code

Returns:

the SFSArray representing the result set

Throws:

SQLException - reports any errors related with the execution of the SQL query

executeUpdate

public void executeUpdate(String sql)
                   throws SQLException

Executes a non-query SQL command such as INSERT, UPDATE, DELETE etc...

Parameters:

sql - the SQL code.

Throws:

SQLException - reports any errors related with the execution of the SQL update

executeUpdate

public void executeUpdate(String sql,
 Object[] params)
                   throws SQLException

Executes a non-query SQL command such as INSERT, UPDATE, DELETE etc...

Parameters:

sql - the SQL code. Placeholders for parameters can be used such as: SELECT * FROM Users WHERE name=?

params - An array of objects that will be used to populate the placeholders in the SQL code

Throws:

SQLException - reports any errors related with the execution of the SQL update

executeInsert

public Object executeInsert(String sql,
 Object[] params)
                     throws SQLException

Executes a SQL INSERT command returning the key of the inserted row

Parameters:

sql - the SQL code. Placeholders for parameters can be used such as: INSERT INTO users (name, email) VALUES(?, ?)

params - An array of objects that will be used to populate the placeholders in the SQL code

Throws:

SQLException - reports any errors related with the execution of the SQL update

getTotalConnections

public int getTotalConnections()

Get the total number of connections

Returns:

the total number of pooled connections

getActiveConnections

public int getActiveConnections()

Get the number of pooled connections currently active

Returns:

get the number of pooled connections currently active

getIdleConnections

public int getIdleConnections()

Get the number of pooled connections currently idle

Returns:

get the number of pooled connections currently idle

getThreadsAwaitingConnections

public int getThreadsAwaitingConnections()

Get the number of threads awaiting on a connection

Returns:

the number of threads awaiting on a connection

getName

public String getName()

Specified by:

getName in interface com.smartfoxserver.bitswarm.service.IService

setName

public void setName(String name)

Specified by:

setName in interface com.smartfoxserver.bitswarm.service.IService

handleMessage

public void handleMessage(Object arg0)

Specified by:

handleMessage in interface com.smartfoxserver.bitswarm.service.IService

getConfig

public DBConfig getConfig()

Description copied from interface: IDBManager

Get the configuration details of the JDBC connection and connection pool

Specified by:

getConfig in interface IDBManager

Returns:

the configuration details of the JDBC connection and connection pool

See Also:

• DBConfig

isActive

public boolean isActive()

Description copied from interface: IDBManager

True if the Service is active

Specified by:

isActive in interface IDBManager

Returns:

true if the Service is active