public interface SqlSettings
server.propertiesSQL
engine, it is
also possible to configure these DataSources with manual entries in your
server.properties file.
When you manually configure a DataSource like this, you do so by maintaining a set of properties with names structured like this:
sql.{dbName}.x.y
where {dbName} is the name of the database configuration you are providing.
Note that this database name is just an arbitrary name for a particular database
configuration; many of the default ones provided with Smart GWT are named after a database
type, in order to make their intended use more immediately obvious, but this is
not by any means a requirement.
For the remainder of this discussion, we will assume we are configuring a database with a name of "MyDatabase".
sql.MyDatabase.database.type
This should be set to one of the supported database types. These are:
| hsqldb | HSQLDB 1.7.x and greater |
| db2 | IBM DB2 8.x and greater |
| db2iSeries | IBM DB2 for iSeries/i5, V5R4 and greater |
| firebirdsql | Firebird 2.5 and greater |
| informix | Informix 11.5 and greater |
| sqlserver | Microsoft SQL Server 2000 and greater |
| mysql | MySQL 3.2.x and greater |
| oracle | Oracle 8.0.5, 8i and greater |
| postgresql | PostgreSQL 7.x and greater |
| generic | A generic SQL92 database, with limitations described in
this article |
sql.MyDatabase.driver
The name of the JDBC driver implementation. This depends upon your database product and
version, and the specific JDBC driver you are using (JDBC drivers can usually be downloaded
from your database vendor's website). Bearing in mind the caveat that this information can
vary by release and JDBC implementation, here are some suggested values for our supported
databases:
| hsqldb | org.hsqldb.jdbcDriver |
| db2 | com.ibm.db2.jcc.DB2DataSource |
| db2iSeries | com.ibm.as400.access.AS400JDBCDriver |
| firebirdsql | org.firebirdsql.jdbc.FBDriver |
| informix | com.informix.jdbc.IfxDriver |
| sqlserver | com.microsoft.jdbc.sqlserver.SQLServerDriver or
com.microsoft.sqlserver.jdbc.SQLServerDriver (Microsoft changed the order of
"jdbc" and "sqlserver" between the 2000 and 2005 editions of the product) |
| mysql | com.mysql.jdbc.jdbc2.optional.MysqlDataSource |
| oracle | oracle.jdbc.driver.OracleDriver |
| postgresql | org.postgresql.Driver |
sql.MyDatabase.driver.serverName
The name or IP address of the database server
sql.MyDatabase.driver.portNumber
The port on which the database server is listening
sql.MyDatabase.driver.user
The user to connect as
sql.MyDatabase.driver.password
The user's password
sql.MyDatabase.driver.databaseName
The database to connect to. A "database" in this context is a named collection of tables
and other database resources that are somehow grouped together by the database product.
The specifics of how this is implemented vary by database. Note that some database
products use the terms "catalog" or "schema" to refer to the same concept, and Oracle -
although it does also have a concept of catalog - uses the term "SID" for this concept.
sql.MyDatabase.interface.type
Indicates how the JDBC connection will be created or looked up; the value of this setting
depends on the capabilities of the particular JDBC driver you are using, and is inherently
connected to the value of sql.MyDatabase.driver. The following settings are
supported:
dataSource - the driver is an instance of javax.sql.DataSource and
should be instantiated by Smart GWT Server
driverManager - the driver is an instance of java.sql.DriverManager
jndi - the driver is an instance of javax.sql.DataSource and should be
looked up using JNDI
sql.MyDatabase.driver.url
For configurations where sql.MyDatabase.interface.type is "driverManager",
this property allows you to manually enter the URL we use to connect to the database. If
this property is not provided, we build the URL from other settings such as
sql.MyDatabase.driver.serverName and
sql.MyDatabase.driver.databaseName.
Other properties
Different JDBC drivers support different properties to support product-specific quirks and
features. You can often specify these properties by embedding them as parameters in the
URL used to connect to the database.
Alternatively, any subproperty you set on the "driver" in server.properties is applied to
the JDBC driver object via Reflection. For example, the MySQL JDBC driver supports a property
"useUnicode", which forces the database to use Unicode character encoding. If
sql.MyDatabase.driver is
com.mysql.jdbc.jdbc2.optional.MysqlDataSource,
setting sql.MyDatabase.driver.useUnicode to true means we'll attempt to call
setUseUnicode(true) on this class. This would have exactly the same effect as
defining the connection URL manually and specifying the parameter useUnicode=true
sql.postgresql.useILike
Starting with version 12.0, Smart GWT Server supports the use of a Postgres-specific
comparison keyword, ILIKE. This keyword natively does a case-insensitive LIKE, so the
Smart GWT driver does not have to do what it normally does to enable this kind of
comparison, which is to convert the filter value to lower case and then generate SQL like:
WHERE LOWER(someField) LIKE 'united%'When ILIKE is in use, Postgres is able to make use of indexes, which it does not do when we use the "lowercase both sides" strategy, so this is a potentially significant performance enhancer, depending on your application:
WHERE someField ILIKE 'United%'