Class SFSDBManager

  • All Implemented Interfaces:
    com.smartfoxserver.bitswarm.service.IService, IDBManager

    public class SFSDBManager
    extends java.lang.Object
    SFSDBManager is the default implementation of the IDBManager interface provided by the SFS2X 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 SFS2X 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 DBConfig config  
      protected org.slf4j.Logger log  
      protected java.lang.String name  
      protected Zone parentZone  
    • Method Summary

      All Methods Instance Methods Concrete Methods 
      Modifier and Type Method Description
      void destroy​(java.lang.Object o)
      Destroy service
      java.lang.Object executeInsert​(java.lang.String sql, java.lang.Object[] params)
      Executes a SQL INSERT command returning the key of the inserted row
      ISFSArray executeQuery​(java.lang.String sql)
      This is a small variation on IDBManager.executeQuery(String, Object[]) where no additional SQL parameter is used.
      ISFSArray executeQuery​(java.lang.String sql, java.lang.Object[] params)
      Perform a SQL query and return a structured object based on SFSArray and SFSObject.
      void executeUpdate​(java.lang.String sql)
      Executes a non-query SQL command such as INSERT, UPDATE, DELETE etc...
      void executeUpdate​(java.lang.String sql, java.lang.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
      java.sql.Connection getConnection()
      Get a direct reference to the JDBC connection object.
      int getIdleConnections()
      Get the number of pooled connections currently idle
      java.lang.String getName()
      Get the service name
      void handleMessage​(java.lang.Object arg0)
      Send message to service
      void init​(java.lang.Object o)
      Initialize service
      boolean isActive()
      True if the Service is active
      void setName​(java.lang.String name)
      Set the service name
      • Methods inherited from class java.lang.Object

        clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
    • Field Detail

      • parentZone

        protected Zone parentZone
      • active

        protected boolean active
      • config

        protected final DBConfig config
      • name

        protected final java.lang.String name
      • log

        protected final org.slf4j.Logger log
    • Constructor Detail

      • SFSDBManager

        public SFSDBManager​(DBConfig config)
        Parameters:
        config - the configuration settings for the JDBC connection and connection pool
    • Method Detail

      • init

        public void init​(java.lang.Object o)
        Description copied from interface: com.smartfoxserver.bitswarm.service.IService
        Initialize service
        Specified by:
        init in interface com.smartfoxserver.bitswarm.service.IService
        Parameters:
        o - custom parameters
      • destroy

        public void destroy​(java.lang.Object o)
        Description copied from interface: com.smartfoxserver.bitswarm.service.IService
        Destroy service
        Specified by:
        destroy in interface com.smartfoxserver.bitswarm.service.IService
        Parameters:
        o - custom parameters
      • getConnection

        public java.sql.Connection getConnection()
                                          throws java.sql.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:
        java.sql.SQLException
      • executeQuery

        public ISFSArray executeQuery​(java.lang.String sql,
                                      java.lang.Object[] params)
                               throws java.sql.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 TypeSFSObject Type
        NULLNULL
        BOOLEANBOOLEAN
        DATELONG (Unix timestamp)
        FLOAT, DECIMAL, DOUBLE, REALDOUBLE
        TINYINT, SMALLINT, INTEGERINTEGER
        CHAR, VARCHAR, LONGVARCHARUTF_STRING
        NCHAR, NVARCHAR, LONGNVARCHARUTF_STRING
        TIMESTAMPLONG
        BIGINT (up to 2^63)LONG
        LONGVARBINARY, BLOBBYTE_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:
        java.sql.SQLException - reports any errors related with the execution of the SQL query
      • executeUpdate

        public void executeUpdate​(java.lang.String sql)
                           throws java.sql.SQLException
        Executes a non-query SQL command such as INSERT, UPDATE, DELETE etc...
        Parameters:
        sql - the SQL code.
        Throws:
        java.sql.SQLException - reports any errors related with the execution of the SQL update
      • executeUpdate

        public void executeUpdate​(java.lang.String sql,
                                  java.lang.Object[] params)
                           throws java.sql.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:
        java.sql.SQLException - reports any errors related with the execution of the SQL update
      • executeInsert

        public java.lang.Object executeInsert​(java.lang.String sql,
                                              java.lang.Object[] params)
                                       throws java.sql.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:
        java.sql.SQLException - reports any errors related with the execution of the SQL update
      • 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
      • getName

        public java.lang.String getName()
        Description copied from interface: com.smartfoxserver.bitswarm.service.IService
        Get the service name
        Specified by:
        getName in interface com.smartfoxserver.bitswarm.service.IService
        Returns:
        the service name
      • setName

        public void setName​(java.lang.String name)
        Description copied from interface: com.smartfoxserver.bitswarm.service.IService
        Set the service name
        Specified by:
        setName in interface com.smartfoxserver.bitswarm.service.IService
        Parameters:
        name - the service name
      • handleMessage

        public void handleMessage​(java.lang.Object arg0)
        Description copied from interface: com.smartfoxserver.bitswarm.service.IService
        Send message to service
        Specified by:
        handleMessage in interface com.smartfoxserver.bitswarm.service.IService
        Parameters:
        arg0 - the message
      • 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