LDSTechForumProjects

Community Database

Community Development Best Practices edit

Documents the practices for Database Development for each database that is in use.

Definition

Database Development includes creating the database structure, configuring the application to communicate with the database, and using tools to persist and retrieve information from the database.

Databases

At the time this is written, and into the foreseeable future, it has been determined that Oracle is the database that all production applications will use. However, this does not mean that a developer has to use Oracle on their local machine. Hibernate, a java Object Relational Mapping API, provides a transparent way of using different databases without ever having to write a SQL script, thus enabling a community developer to use a different database on their local machine. If, however, the language that you are developing in does not provide an API to communicate with multiple databases then it is strongly recommended that you develop connecting to an Oracle instance. That way the source code will be compatible with the Church's current production environment.

Oracle XE

The Oracle XE (Express Edition) is available for free and can be downloaded from the Oracle XE website.

Oracle likes to run the Oracle XML DB Http Server on port 8080 which is the default port when developing with Tomcat on Eclipse. If port 8080 is already in use then there are a few things that you can do to avoid this conflict.

Installing Oracle XE on Windows

  • Run the downloaded installer
  • Go through the installation wizard. You will be prompted for a system password. We will be referring to this password later so choose a password that you can remember.
  • Once you have installed Oracle XE, you will need to change the port that XE uses for its web management console. Open SQLPlus by going to Start/Oracle Database 10g Express Edition/Run SQL Command Line. Enter:
connect system

Then enter the system password you chose when you installed Oracle. Now enter:

exec dbms_xdb.sethttpport('4080');

If you want to access it remotely, add this too:

exec dbms_xdb.setlistenerlocalaccess(false);
  • By default Oracle XE adds itself to your Windows services so that it starts up automatically. To change this so that you can start up Oracle XE manually, go to Start/Control Panel. Then go to Administrative Tools. Then go to Services. Then find “OracleServiceXE” and “OracleXETNSListener” and change their Startup Type to Manual.

Installing Oracle XE on Debian/Ubuntu

  • As root create the file /etc/apt/sources.list.d/oracle.list with the following contents:
deb http://oss.oracle.com/debian unstable main non-free
  • From a shell run:
sudo apt-get update
sudo apt-get install oracle-xe-universal
sudo /etc/init.d/oracle-xe configure
  • As part of the configure step, you will be asked for a system password. This password will be used later so choose a password you can remember. You will also be prompted for a port on which the Oracle XE Web Management console should run. Use port 4080.

Creating an Oracle XE Database User

To create a database user for the training, go to http://127.0.0.1:4080/apex. Login as system and use the password you chose when installing/configuring Oracle XE. Click on the big Administration icon, then Database Users, then Create > button (on far right).

Make the user a DBA and click on the “Check All” in the bottom right box containing DB privileges. Then create the user. At this point, your database is ready.

Change the port on the Tomcat Server in Eclipse

  • In the Servers view in Eclipse double click on the server name.
  • In the Tomcat details window change the HTTP/1.1 port from 8080 to another port that is not is use (like port 8088).
  • Save the configuration file.
  • Now make sure you always access the server with the right url (http://localhost:8088/appName/ for example).

Use a different application to administer the database

There are free applications out there that allow you to administer the database (SqlDeveloper, SQuirrel SQL Client, etc.). You could use that and set the port to 0.

  • Go to the sql prompt (run sqlplus). For me I selected Programs->Oracle 10g...->Run SQL Command Prompt
  • Then type 'connect system' and enter your password.
  • Then enter the command 'EXEC DBMS_XDB.SETHTTPPORT(0);'

PostgreSQL

Posgres is also a popular open source database. It is available for free and can be downloaded from the PostgreSQL website.

Postgres comes with a database admin tool (PGAdmin) as well, but it doesn't use a conflicting port similar to Oracle. On a Windows box you can access this through the PostgreSQL menu.

Why PostgreSQL?

Here are a few reasons for using Postgres:

  1. Works on a Mac without virtualization! Whereas Oracle does not have an installer for Mac and must be run in a virtual environment.
  2. Smaller footprint. PostgreSQL install folder is 140MB. OracleXE install folder is 1.71 GB. (Sizes taken from a Windows box.)
  3. Less virtual memory usage. PostgreSQL when idle consumes 24MB. OracleXE when idle consumes 62MB. (Sizes taken from a Windows box.)
  4. Works with standard Java Persistence API sequencing and query formatting.

MySQL

MySQL is a popular open source database. It is available for free or for purchase with support. phpMyAdmin is a popular database admin tool for MySQL. Most of the LDSTech projects using PHP use MySQL. Java apps typically do not.

Why MySQL?

Here are a few reasons for using MySQL:

  1. Can be installed natively on Mac, Windows, and Linux
  2. Small disk and memory footprint as compared to Oracle
  3. Works with standard Java Persistence API sequencing and query formatting
  4. Very fast as compared to Oracle

Creating User Schema / Database

Once the database server has been installed it needs to be configured to allow the application to connect to it.

OracleXE

Connect as the SYSTEM user on the database (hopefully you remember the password).

The goal is to add a user schema name and can be done by

  • running this command after replacing the '<>' values.
CREATE USER <MyUserName> IDENTIFIED BY <MyPassword> ;
  • if in a Database Application like SQL Developer you can just right click on Other Users and select Create User.

PostgreSQL

  • Open PgAdmin III. Log in to your local database server.
  • Right click on 'Databases'.
  • Select 'New Database'.
  • The name is the only real important parameter.

Note: If you create a new login role to use for your application then make sure that it is the the owner for all objects created.

Technologies

JPA

The Java Persistence API (JPA) simplifies the programming model for entity persistence and adds capabilities that were not in EJB 2.1. JPA abstracts the persistence and query services provided by projects like Hibernate, EclipseLink, etc. More information is available here.

Hibernate

Hibernate is a powerful, high performance object/relational persistence and query service. Hibernate lets you develop persistent classes following object-oriented idiom - including association, inheritance, polymorphism, composition, and collections. Hibernate allows you to express queries in its own portable SQL extension (HQL), as well as in native SQL, or with an object-oriented Criteria and Example API. More information is available on the Hibernate website.

Service Proxy

Service Proxy is a custom API provided by the an LDSTech Web Application Platform and is meant to aide developers by removing many of the duplicate service layer operations through the use of annotations. Service Proxy attempts to provide an implementation for all annotated methods of a service interface that do not have an explicit implementation already provided. More information is available on the Service Proxy documentation site.

Application

Detailed documentation of what technologies are used and how they are used within a technology stack.

Java Web Technology Stack

The LDS Java Stack uses :

  • JPA with Hibernate
  • Service Proxy

An example of a project that uses the Java Web Technology Stack is Local Unit Calendar (LUC). The source code of LUC will be referenced in the next few sections. The base project is accessible through the SVN repository at https://dev.lds.org/svn/calendar . Anyone with an LDS Account will be able to view the source code by browsing to the base project. If you need access to check out the project then special access will need to be granted to you.

Database Configuration

  • Make a database selection from the list above.
  • Deprecated - Use the Stack Database Versioning Tool to manage all of the sql commands required to create the database structure and import default data.
  • Optional - Some projects have SQL scripts to create the database structure for you. Whether or not that exists an option may be to have hibernate auto update or create the table structures for you. This can be done by setting the hbm2ddl's auto setting in the applicationContext.xml. For more documentation on the property setting view this helpful blog.
    <stack-db:hibernate persistence-unit-name="org.lds.lua.calendar.calendar"
                        show-sql="true">
        <stack-db:hibernate-properties>
            <!--<entry key="hibernate.hbm2ddl.auto" value="validate"/>-->
            <entry key="hibernate.hbm2ddl.auto" value="create"/>     <!-- UNCOMMENT TO HAVE HIBERNATE AUTOMATICALLY GENERATE YOUR LOCAL SCHEMA -->
            <!--<entry key="hibernate.hbm2ddl.auto" value="create-drop"/>-->
            <!--<entry key="hibernate.hbm2ddl.auto" value="update"/>-->
        </stack-db:hibernate-properties>
    </stack-db:hibernate>
  • To configure your database properties for Oracle XE, add the following to context.xml in your <tomcat_install>/conf directory:
<!-- OracleXE -->
<Context>
     .
     .
    <Environment name="datasourceUrl"
                 value="jdbc:oracle:thin:@localhost:1521:XE"
                 type="java.lang.String" override="false"/>
    <Environment name="datasourceUser"
                 value="luc"
                 type="java.lang.String" override="false"/>
    <Environment name="datasourcePassword"
                 value="myDBPass"
                 type="java.lang.String" override="false"/>
    <Environment name="datasourceDriver"
                 value="oracle.jdbc.OracleDriver"
                 type="java.lang.String" override="false"/>
    <Environment name="datasourcePoolSize"
                 value="5"
                 type="java.lang.String" override="false"/>
     .
     .
</Context>
  • To configure your database properties for PostgreSQL, add the following to context.xml in your <tomcat_install>/conf directory:
<!-- PostgreSQL -->
<Context>
     .
     .
    <Environment name="datasourceUrl"
                 value="jdbc:postgresql:comm_luc://localhost:5432"
                 type="java.lang.String" override="false"/>
    <Environment name="datasourceUser"
                 value="luc"
                 type="java.lang.String" override="false"/>
    <Environment name="datasourcePassword"
                 value="myDBPass"
                 type="java.lang.String" override="false"/>
    <Environment name="datasourceDriver"
                 value="org.postgresql.Driver"
                 type="java.lang.String" override="false"/>
    <Environment name="datasourcePoolSize"
                 value="5"
                 type="java.lang.String" override="false"/>
     .
     .
</Context>
  • Deprecated - Java Stack version 3.0-rc2 will now auto detect postgresql for you if you have a postgresql driver in your classpath and no oracle driver.
  • Your database dialect will be set correctly for Oracle with no configuration changes. To set the correct dialect for PostgreSQL, add the dialect attribute to the <stack-db:hibernate/> element in applicationContext.xml:
    <stack-db:hibernate persistence-unit-name="org.lds.lua.calendar.calendar"
                        show-sql="true" dialect="org.hibernate.dialect.PostgreSQLDialect">     <!-- ADD DIALECT ATTRIBUTE HERE -->
        <stack-db:hibernate-properties>
            <!--<entry key="hibernate.hbm2ddl.auto" value="validate"/>-->
            <!--<entry key="hibernate.hbm2ddl.auto" value="create"/>-->
            <!--<entry key="hibernate.hbm2ddl.auto" value="create-drop"/>-->
            <!--<entry key="hibernate.hbm2ddl.auto" value="update"/>-->
        </stack-db:hibernate-properties>
    </stack-db:hibernate>
  • If you are using PostgreSQL, add the correct database driver to the application dependencies in the pom.xml
	<!-- For PostgreSQL -->
	<dependency>
		<groupId>postgresql</groupId>
		<artifactId>postgresql</artifactId>
		<version>8.4-701.jdbc4</version>
	</dependency>
  • Deprecated - If you're using the LDSTech Java Web Application IDE it will automatically refresh your Maven dependencies for you (see Getting started with the LDSTech Java WebApp Platform)
  • Run 'mvn eclipse:eclipse' in the command line at the <project>/web directory and refresh the project. This will load the library needed to connect to the database.

JPA with Hibernate

Since these technologies have documentation provided (see technology description above for links) we will focus on the main steps that are taken within a Java Stack project to configure it to use JPA and Hibernate.

  • Add mapping to Java Class that represents the database table or view. Full source: Calendar.java
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.Transient;

@Entity
@SuppressWarnings("serial")
@Table(name="LUC_CALENDAR")
@Cache(usage = CacheConcurrencyStrategy.TRANSACTIONAL)
public class Calendar extends BaseEntity implements Serializable {

	@Id
	@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SEQ_LUC_CALENDAR_PK")
	@SequenceGenerator(name="SEQ_LUC_CALENDAR_PK", sequenceName="SEQ_LUC_CALENDAR_PK")
	private Long id;

	private String name;

	@Column(name="DESCRIPTION")
	private String desc;

....
	<class>org.lds.ml.calendar.model.Calendar</class>
  • Use the EntityManager in the service layer implementation files and/or you can use the Service Proxy documented below.

Service Proxy

Service proxy is used by specifying annotations on method declarations of interfaces that are usually located in the web/src/main/java/org/lds/<myProjectName>/service/ directory.

@Service("calendarService")
@ServiceProxy
  • Specify the annotations on the interface file. There is not a full source file that include all the details shown below. Read the documentation above and in the link provided. Make sure you read up on the @Find section for more details on using StringTemplate to dynamically include or exclude sections of the query based upon a boolean expression or existence check.
	@Transactional(readOnly = true)
	@Find(query="SELECT t FROM Traveler t " +
			     "LEFT OUTER JOIN t.missionary mt " +
				 "LEFT OUTER JOIN t.assignment assign " +
				 "LEFT OUTER JOIN assign.processingTdMission pm " +
				 "LEFT OUTER JOIN assign.processingRtMission pm2 " +
			" WHERE 1 = 1 " +
			" AND (nvl(t.assignmentReleaseDate,SYSDATE) >= SYSDATE-1) " +
			"$if(accessTravelerTypes)$ AND (t.type in (:accessTravelerTypes)) $endif$ " +
			"$if(offsetCallDateNbr)$ AND ((t.type = 1 AND  mt.callDate < SYSDATE - :offsetCallDateNbr) " +
				"OR t.type <> 1) $endif$" +
			"$if(id)$ AND t.identification = :id $endif$" +
			"$if(name)$ AND UPPER(t.name) like UPPER(:name) $endif$" +
			"$if(mtcDate)$ AND mt.mtcDate = :mtcDate $endif$" +
			"$if(assignment)$ AND assign = :assignment $endif$" +
			"$if(assignmentCountry)$ AND t.assignmentCountry = :assignmentCountry $endif$" +
			"$if(areas)$ AND " +
				"(assign.area IN (:areas) " +
					"OR (assign.id IN (:areas)) " +
					"OR (pm.area IN (:areas))" +
					"OR (pm2.area IN (:areas))" +
					"OR (t.residencyCountry.id in (" +
						"SELECT countries.id " +
						"FROM Area a " +
						"JOIN a.countries countries " +
						"WHERE a IN (:areas))) " +
					")" +
			"$endif$ " +
			"$if(status)$ AND ((t.type = 1 AND mt.status in (:status))" +
				"OR t.type <> 1) $endif$"
			)
	public List<Traveler> findAllTravelers(
			@Param(name="id") Long id,
			@Param(name="name") String name,
			@Param(name="assignment") Assignment assignment,
			@Param(name="mtcDate") Date mtcDate,
			@Param(name="assignmentCountry") PoliticalPlace assignmentCountry,
			@Param(name="status") List<String> status,
			@Param(name="areas") List<Area> areas,
			@Param(name="offsetCallDateNbr") Integer offsetCallDateNbr,
			@Param(name="accessTravelerTypes") List<TravelerType> accessTravelerTypes,
			@FirstResult int firstResult,
			@MaxResults int maxResults,
			@OrderBy String orderBy
	);

Other application framework

If or when other application frameworks are used, they will be documented here.

This page was last modified on 24 September 2013, at 14:58.

Note: Content found in this wiki may not always reflect official Church information. See Terms of Use.