2.2.2 Configuring Viewpoint to communicate with your DBMS
Viewpoint uses a database and relies on Hibernate, an Object/Relational Mapping (ORM) tool for Java programs, to communicate with the database. For Viewpoint to recognize and work with your database management system (DBMS), you must configure the hibernate.cfg.xml settings file.
Hibernate relies on the JDBC specification. Viewpoint works with the following database management systems: MySQL and Oracle.
To configure Viewpoint to communicate with your DBMS
- Learn how to do the following steps by consulting the vendor documentation:
- Verify your DBMS is running and that it accepts remote connections.
- Create a database in your DBMS for use with Viewpoint.
- Use the SQL DDL files in the sql folder of your unzipped Viewpoint distribution to set up the tables, foreign key constraints, and so forth that Viewpoint is expecting. If using an unsupported JDBC driver, tailor one of the schema files to fit any DBMS specific syntax you have
- Open hibernate.cgf.xml located in the Viewpoint home directory.
- Set the hibernate.dialect property to the appropriate class for your DBMS as listed in the Hibernate Dialects tables that follow.
- Set the hibernate.connection.driver class to the appropriate class for your DBMS as listed in the Hibernate Dialects tables that follow.
- Modify the hibernate.connection.url to point to the database location.
As each DBMS database driver has slightly different connection URL syntax, you may need to consult the documentation for the JDBC driver for your DBMS. However, they often have the following form:
jdbc:<DBMS vendor>://<server>[:<port>]/<database>
To help you get started quickly, some sample hibernate.connection.urls are listed in the Hibernate Dialects tables that follow.
- Specify a username and password in the respective property elements.
- If using MySQL or Oracle, download the JDBC drivers separately and place them where Viewpoint can find them.
- Download the MySQL driver. The driver will come in a tar.gz or a zip file. The jar file will be inside this tar.gz or zip file and will likely be named something like mysql-connector-java-x.x.x-bin.jar.
- Download the Oracle driver from the Oracle web site. Get the appropriate driver jar file for your Oracle database version. We recommend the drivers in the "JDBC Thin for All Platforms" section of that page. Also note you will need to get an Oracle driver compatible with JDK 1.4 and 1.5.
- Extract the contents of the database driver file and place the jar file in the tomcat/webapps/moab/WEB-INF/lib directory before you start Viewpoint.
| SQL Server Hibernate Dialects |
| Dialect |
org.hibernate.dialect.SQLServerDialect |
| connection.driver_class |
net.sourceforge.jtds.jdbc.Driver |
| Sample Connection URL |
jdbc:jtds:sqlserver://localhost:41433/my_database |
| MySQL Hibernate Dialects |
| Dialect |
org.hibernate.dialect.MySQLInnoDBDialect |
| connection.driver_class |
com.mysql.jdbc.Driver |
| Sample Connection URL |
jdbc:mysql://localhost/my_database |
| PostgreSQL |
| Dialect |
org.hibernate.dialect.PostgreSQLDialect |
| connection.driver_class |
org.postgresql.Driver |
| Sample Connection URL |
jdbc:postgresql://localhost/my_database |
| Oracle Hibernate Dialects |
| Dialect |
com.cri.poller.hibernate.Oracle10gDialect |
| connection.driver_class |
org.postgresql.Driver |
| Sample Connection URL |
jdbc:oracle:thin:moab/moab@localhost:1521/XE |
The following is an example of a MySQL configuration in the hibernate.cfg.xml file:
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost/my_database</property>
<property name="hibernate.connection.username">moab</property>
<property name="hibernate.connection.password">p@ssw0rd</property>
- To enable schema namespaces, which is a way of logically grouping table within a single database into separate containers, configure the optional hibernate.default.schema property by performing the following steps:
- Open the hibernate.cfg.xml file located in the Viewpoint home directory and add the following line:
<property name="hibernate.default_schema">myschema</property>
- Open the unzipped Viewpoint distribution directory and navigate to the sql directory.
- Modify the SQL DDL files that set up the tables, foreign key constraints, and so forth that Viewpoint is expecting . To do so, prepend "<schema name>." to the table name for every line in the SQL DDL file that references a table name.
An original file might look like the following example:
drop table MoabNode_VM;
create table MoabNode_VM (MoabNode_nodeID varchar(255) not null, vms_id varchar(255) not null, ...
alter table MoabNode_VM add constraint FK_MoabNodeVM_VM_ID foreign key ...
The amended lines should appear as follows:
drop table myschema.MoabNode_VM;
create table myschema.MoabNode_VM (MoabNode_nodeID varchar(255) not null, vms_id varchar(255) not null, ...
alter table myschema.MoabNode_VM add constraint FK_MoabNodeVM_VM_ID foreign key ...
Additional Connection Parameters
In addition to the JDBC connection parameters you will notice that the sample hibernate.cfg.xml file has many other properties. You may also notice some mapping elements, like
<mapping class="com.cri.cart.server.ShoppingCart" />
Refrain from modifying properties or mappings without talking to support first as modifications may cause problems with the communication between Viewpoint and the database. For more information on what these configuration options do, please refer to the Hibernate documentation.
Mapping elements enable Viewpoint to manage database tables for certain features. In particular, Viewpoint will be able to manage tables for the Java class referenced by the class attribute. It is recommended that you not modify these mappings or Viewpoint may not be able communicate properly with the database.
Below is an explanation of the other properties in the hibernate.cfg.xml file.
| hibernate.transaction.auto_close_session |
| Suggested value |
true (Strongly recommended) |
| Description |
If enabled, the persistence session is automatically closed after the completion phase of its database transactions. The alternative is to have the application close these sessions manually. In Viewpoint this should be set to true. |
| hibernate.connection.autocommit |
| Suggested value |
false (Strongly recommended) |
| Description |
When true, this parameter causes autocommit to be enabled for the JDBC connections in the connection pool. It is strongly recommended that this be false. Autocommit mode allows each individual SQL statement to be executed in its own transaction. Starting these extra transactions causes lots of overhead and destroys atomicity and isolation guarantees for operations that should be grouped together in a transaction. |
| hibernate.current_session_context_class |
| Suggested value |
thread (Strongly recommended) |
| Description |
How the hibernate session should be bound. Although hibernate supports "thread", "jta", and "managed" as legal values for this parameter, in Viewpoint this should be set to "thread". "thread" causes sessions to be bound to threads. "jta" binds sessions to JTA transactions and "managed" causes the responsibility for managing session scope, start, and end to the application. |
| hibernate.show_sql |
| Suggested value |
false |
| Description |
Causes all SQL statements to be printed to the console.
A better option is to cause SQL to be sent to the log instead of the console. To do this set the log category org.hibernate.SQL to debug in the ViewpointConfig.groovy file.
|
| hibernate.format_sql |
| Suggested value |
true |
| Description |
Causes the SQL printed to the log and the console to be pretty printed |
| connection.provider_class |
| Suggested value |
org.hibernate.connection.C3P0ConnectionProvider |
| Description |
Configures the JDBC connection pool which makes communication with the database more efficient. Viewpoint supports only c3p0, or no connection pool. To disable connection pooling. the connection.provider_class property can be commented out. |
| hibernate.c3p0.min_size |
| Suggested value |
5 |
| Description |
Configures the JDBC connection pool which makes communication with the database more efficient. Viewpoint supports only c3p0, or no connection pool. To disable connection pooling. the connection.provider_class property can be commented out.
|
| hibernate.c3p0.max_size |
| Suggested value |
20 |
| Description |
Configures the JDBC connection pool which makes communication with the database more efficient. Viewpoint supports only c3p0, or no connection pool. To disable connection pooling. the connection.provider_class property can be commented out. |
| hibernate.c3p0.timeout |
| Suggested value |
25000 |
| Description |
The minimum number of JDBC connections that c3p0 keeps ready at all times. This property is supported with only the c3p0 connection provider. |
| hibernate.c3p0.max_statements |
| Suggested value |
0 |
| Description |
The number of prepared statements that c3p0 will cache. Although prepared statement caching improves performance it has been linked to sporadic race conditions due to bugs in c3p0. For that reason it is recommended to set this to 0 to disable prepared statement caching. This property is supported with only the c3p0 connection provider. |
| hibernate.c3p0.idle_test_period |
| Suggested value |
3000 |
| Description |
The idle time in seconds before a connection is automatically validated. This causes the connection to be kept alive even when idle so it doesn't get closed by a firewall. This property is supported with only the c3p0 connection provider. |
Out-of-the-Box hibernate.cfg.xml Settings
The following represents the default hibernate.cfg.xml settings in Viewpoint:
<hibernate-configuration>
<session-factory>
<!-- General properties -->
<property name="hibernate.transaction.auto_close_session">true</property>
<property name="hibernate.connection.autocommit">false</property>
<property name="hibernate.current_session_context_class">thread</property>
<!-- Setting the schema namespace (Optional) -->
<!--
<property name="hibernate.default_schema">dbo</property>
-->
<!-- MySQL connections -->
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost/my_database</property>
<property name="hibernate.connection.username">moab</property>
<property name="hibernate.connection.password">p@ssw0rd</property>
<!-- Postgres connections -->
<!--
<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property name="hibernate.connection.url">jdbc:postgresql://localhost/my_database</property>
<property name="hibernate.connection.username">Moab</property>
<property name="hibernate.connection.password">p@ssw0rd</property>
-->
<!-- H2 connections -->
<!--
<property name="hibernate.connection.driver_class">org.h2.Driver</property>
<property name="hibernate.dialect">org.hibernate.dialect.H2Dialect</property>
<property name="hibernate.connection.url">jdbc:h2:tcp://localhost/~/my_database</property>
<property name="hibernate.connection.username">sa</property>
<property name="hibernate.connection.password">p@ssw0rd</property>
-->
<!-- Sql Server connections -->
<!--
<property name="hibernate.connection.driver_class">net.sourceforge.jtds.jdbc.Driver</property>
<property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>
<property name="hibernate.connection.url">jdbc:jtds:sqlserver://localhost:41433/my_database;tds=8.0</property>
<property name="hibernate.connection.username">Moab</property>
<property name="hibernate.connection.password">p@ssw0rd</property>
-->
<!-- Use the C3P0 connection pool provider -->
<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.timeout">40</property>
<property name="hibernate.c3p0.max_statements">0</property>
<property name="hibernate.c3p0.idle_test_period">3000</property>
<!-- Show SQL on stdout -->
<property name="hibernate.show_sql">false</property>
<property name="hibernate.format_sql">false</property>
<!-- Shopping Cart classes -->
<mapping class="com.cri.cart.server.ShoppingCart"/>
<mapping class="com.cri.cart.server.ShoppingCartLineItem"/>
<mapping class="com.cri.cart.server.ShoppingCartItem"/>
<!-- Archive classes -->
<mapping class="com.cri.archival.server.model.Archive"/>
<mapping class="com.cri.archival.server.model.ArchiveObject"/>
<!-- VPC user classes -->
<mapping class="com.cri.provisioning.environmentmgt.server.model.VpcUser"/>
<mapping class="com.cri.component.server.form.persistmodel.FormStatePersistModel"/>
<mapping class="com.cri.security.server.SecurityUser"/>
</session-factory>
</hibernate-configuration>
Related topics
