Configure database properties

Database configuration

All database configuration properties are specified in config/database.properties file.

By default, Scooter assumes three database environments and sets up three database connection settings:

  • {app_name}_development for development
  • {app_name}_test for testing
  • {app_name}_production for production
Use default.database.connection.name property to indicate which database environment to run your application. The default is {app_name}_development.

The following is an example of blog_development database configuration using MySQL. It assumes that database url is localhost/blog_development, username is root and password is empty. 

database.connection.blog_development=\
    driver=com.mysql.jdbc.Driver,\
    url=jdbc:mysql://localhost/blog_development:3306?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull,\
    username=root,\
    password=

WARNING: Please verify that the url property in the database connection entry as above points to the database instance name in your database. You should modify values of url, username and password properties according to your own database setup.

Some MySQL specific properties are added in the url for convenience. You can find all MySQL connection properties from MySQL website here.

You can add more databases by just adding a new entry of database.connection.{AnotherDB}. For example, if your QA team uses their own database, just add the following database connection:

database.connection.blog_qa=\
    driver=com.mysql.jdbc.Driver,\
    url=jdbc:mysql://localhost/blog_qa_mysql:3306?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull,\
    username=onlyqaknows,\
    password=qasecret

And set value of default.database.connection.name property as follows for QA testing:

default.database.connection.name=blog_qa

There are more properties you can configure for database access. View config/database.properties file for details.

More connection configuration properties

File database.properties is a place where you can set database connection properties. If you accept the default value of a property, you do not need to configure it.

The following is a list of properties in this file.

default.transaction.type

The default value is JDBC which means SDE uses database connection to control transaction.

Two other transaction types are: JTA and CMT.

  1. JDBC (default): using connection
  2. JTA: using UserTransaction
  3. CMT: using Container's transaction manager
default.database.connection.name

The default value is {app_name}_development where {app_name} is populated by the creation command.

You can specify as many database connection names as you want. The default value here is used by the framework to make a database connection when you do not supply a specific database connection property.

database.connection.XXX

You need to replace XXX by the specific database connection you define.

Here is a list of names and values for this property.

NameDescriptionFormat and Examples
driver Driver class name com.mysql.jdbc.Driver
url Database connection url jdbc:mysql://localhost/jpetstore
beforeConnection The method to execute before creating a connection. Format: {full class name}.{method name}
Example: com.example.Util.beforeConnection
afterConnection The method to execute after creating a connection. Format: {full class name}.{method name}
Example: com.example.Util.afterConnection
This method is useful when you need to set role immediatelt after a connection is created.
username database login username sccot
password database login password tiger
schema database schema Optional, default value is different for each database type.
  • H2: "PUBLIC" if db url doesn't contain "SCHEMA" property
  • HsqlDB: empty or derived from the last segment in db url
  • Oracle: value of username property
  • PostgreSQL: "public"
use_login_as_schema Use login username as schema name Default is false.
use_login_for_connection Use login username and password for connection Default is false
max_pool_size. maximum number of connections a pool will maintain at any given time. Default is 5.
Special: Zero means connection pool is not turned on.
min_pool_size minimum number of connections a pool will maintain at any given time. Default is 3.
acquire_increment number of connections at a time framework will try to acquire when the pool is exhausted. Default is 3.
initial_pool_size number of connections a pool will try to acquire upon startup. Should be between min_pool_size and max_pool_size. Default is 3.
max_idle_time seconds a connection can remain pooled but unused before being discarded. Zero means idle connections never expire. Default is 0.
timeout The maximum time in seconds that this data source will wait while attempting to connect to a database. Default is 0.
readonly Specify whether the connection is readonly Default is false.
vendor Database vendor name Optional, MYSQL, ORACLE, POSTGRESQL, H2, HSQLDB
adapterClassName custom database adapter class name Default is empty.
transactionIsolationLevel transaction isolation level -1: default, no specified level, use DB default setting
0: TRANSACTION_NONE
1: TRANSACTION_READ_UNCOMMITTED
2: TRANSACTION_READ_COMMITTED
4: TRANSACTION_REPEATABLE_READ
8: TRANSACTION_SERIALIZABLE

reference.data.XXX

Reference data are usually definitions in your database, such as status codes, categories, etc.. Reference data are relative static and stable. They do not change very often. Therefore you can improve performance of your application by caching them.

You can specify as many reference data as you want by just using a unique name for the data through the replacement of the XXX token.

Here is a list of names and values for this property.

NameDescriptionFormat and Examples
sql The sql query for loading the reference select st_cd, name from states
key the key to the value Example: st_cd which may have contents like CA, DC, NY, etc.
value the value for the key Example: name which may include California, District of Columbia, New York
class the model class of the reference data Example: State
Please notice that you use either sql or class, not both.
period the interval in milliseconds for reloading the reference data Example: 1000. Its default value is 0 which means load once and never refresh.

Automatic column values

The values of the following columns are set to current java.sql.Timestamp.

autoaudit.create.timestamp.fields

Specifies which columns are used for automatically setting up values when the record is created. Current columns are: created_at, created_dt, created_on, entry_dt

autoaudit.update.timestamp.fields

Specifies which columns are used for automatically setting up values when the record is updated. Current columns are: updated_at, updated_dt, updated_on, update_dt

More properties

Global table naming conventions

NameDescriptionFormat and Examples
global.table.naming.prefix global table name prefix Example: "CRM_"
global.table.naming.suffix global table name suffix Example: "_CRM"
use.plural.table.name If true, use plural noun for table name Default is true

Additional SQL data type mapping

NameDescriptionFormat and Examples
additional_sql_data_type_mapping additional sql data type mapping Format: {sql data type name}:{sql data type}:{java class name}
Examples: VARCHAR:12:java.lang.String, DATE:91:java.sql.TimeStamp