NexJ Express Connecting to PostgreSQL
PostgreSQL Adapter for NexJ - Create Test Environment
Contents
1- Setup the postgresql-connection
For testing the connection, the 'PostgreSQLAdapterTest.java' is being used.
- TODOs
- Files to Modify
- STEPS for Connection
- Installed the
JDBC3 Postgresql Driver, Version 9.0-801
and added it to the libraries in JDK- The driver class which is being used for NexJ Express Model is:
org.postgresql.xa.PGXADataSource
- The driver is loaded from RelationalDatabaseFragment.java and RelationalDatabase.java
- The driver class which is being used for NexJ Express Model is:
- Metadata is loaded from the particular driver
/* MySQLAdapterTest.java */
try
{
s_metadata = loadMetadata(ADAPTER_NAME);
}
/* SQLDataTest.java */
protected static Metadata loadMetadata(String sAdapter)
{
Properties props = SysUtil.getConfigProperties(); // sets DEFAULT_CONFIG_URL = '/' + NAMESPACE + "/default.config"
props = new Properties(props);
props.setProperty(XMLMetadataLoader.CONNECTIONS_URL_PROPERTY, "/nexj/" + sAdapter.toLowerCase(Locale.ENGLISH) + ".connections"); // sets the properties to postgresql.connection
- After all properties are loaded, it creates the connection through Connection Factory files.
Modifications
Changes Done to MySQL adapter |
---|
/* MySQLAdapter.java */
public String getInitialSQL()
{
StringBuffer buf = new StringBuffer();
buf.append("set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES')"); // allow using doublequote when quoting column names in "CREATE TABLE" statements
buf.append(";set optimizer_search_depth = 0"); // let DB automatically decide on how long it takes to examine plans, improves long planning sessions
buf.append(";set max_sort_length = ").append(Math.max(MAX_VARCHAR_PRECISION, MAX_VARBINARY_PRECISION)); // set TEXT/BLOB minimum sorting length to be same as cutoff between varchar/text
return buf.toString();
}
/* nexj/core/persistence/sql/etc/postgresql_create.sql */
set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES');
create table test.Account(
id binary(16) not null, contactId binary(16) not null, account Type varchar(16) character set utf8 not null, funds double null,
constraint Account_PK primary key(id)
);
|
2- Create a PostgreSQL database through some scripts
- Through the process in postgresql_setup.sql, the database was created, initialized and tested:
- A database called 'test is created in PostgreSQL :
CREATE DATABASE test
- To create a
user
along withpassword
:CREATE USER test WITH PASSWORD 'test';
- Login to the database 'test' with username 'test':
psql -U test test
- Create a
Schema
called 'test' inside the database 'test' with particular owner 'test':CREATE SCHEMA test AUTHORIZATION test;
- Create a
table
called 'Mutex' with primary key:CREATE TABLE test.Mutex(id INT PRIMARY KEY);
- Insert a value into the primary key:
INSERT INTO test.Mutex(id) VALUES (1);
- A database called 'test is created in PostgreSQL :