H2 Database Creation & Configuration

We’ll start by preparing a database for this SQLXML exercise. You can use any database which is accessible via JDBC drivers to build your SQLXML, but for this exercise, we’ll be using “H2” since it is extremely simple to get up and running and requires no configuration. If you do not have H2 installed, you can find it here:  http://www.h2database.com/html/main.html

NOTE: Download only a stable version of H2.

Once H2 is installed, launch it (typically with a shortcut named “H2 Console”) and it should open a new browser window that looks like this:

We can provide values for the user name and password if we wish to change them, but for this tutorial, we’ll leave them at their defaults (“sa” and blank). The one field we’ll need to modify is the “JDBC URL,” which is the unique URL and name for our database instance: jdbc:h2:tcp://localhost/~/test

H2 database credentials:

Username: sa

Password: (Password is not required for this tutorial)

Driver: org.h2.Driver

URL: jdbc:h2:tcp://localhost/~/test

We’ll create one called test.

When you create these database tables, make sure you remember to add the unique, primary, and foreign key as highlighted in each SQL Create statement below.

Create PATIENT table

CREATE TABLE PATIENT (

MRN VARCHAR(20) not null,
ACCOUNTNUMBER VARCHAR(20) not null,
LAST_NAME VARCHAR(20),
MIDDLE_NAME VARCHAR(15),
FIRST_NAME VARCHAR(20),
GENDER VARCHAR(1),
BIRTH_DATE VARCHAR(10),
STATUS VARCHAR(10),
ADDRESS1 VARCHAR(60),
ADDRESS2 VARCHAR(60),
CITY VARCHAR(30),
STATE VARCHAR(2),
ZIPCODE VARCHAR(5),
NEXTOFKIN_LASTNAME VARCHAR(20),
NEXTOFKIN_MIDDLENAME VARCHAR(15),
NEXTOFKIN_FIRSTNAME VARCHAR(20),
NEXTOFKIN_PHONE VARCHAR(15),
NEXTOFKIN_ADDRESS VARCHAR(60),
NEXTOFKIN_CITY VARCHAR(20),
NEXTOFKIN_STATE VARCHAR(2),
NEXTOFKIN_ZIP VARCHAR(5),
SSN VARCHAR(12),
RACE VARCHAR(20),
PHONE_HOME VARCHAR(15),
PHONE_BUSINESS VARCHAR(15),
RELIGION VARCHAR(15),
PATIENT_DEATH_DATETIME DATETIME,
NEXTOFKIN_RELATIONSHIP VARCHAR(60),
NEXTOFKIN_CONTACTROLE VARCHAR(60),
UNIQUE (MRN),
PRIMARY KEY (ACCOUNTNUMBER)

);

Create PATIENT_VISIT table

CREATE TABLE PATIENT_VISIT (

ACCOUNTNUMBER VARCHAR(20) not null,
MRN VARCHAR(20) not null,
FACILITY_ID VARCHAR(20),
FACILITY_NAME VARCHAR(60),
PATIENT_LOCATION VARCHAR(15),
ROOM VARCHAR(10),
BED VARCHAR(10),
ADMISSION_TYPE VARCHAR(20),
PATIENT_CLASS VARCHAR(15),
PATIENT_TYPE VARCHAR(6),
HOSPITAL_SERVICE VARCHAR(30),
ADMIT_SOURCE VARCHAR(20),
VISIT_NUMBER VARCHAR(20),
ADMIT_DATETIME DATETIME,
DISCHARGE_DATETIME DATETIME,
ADMITTING_DOCTOR VARCHAR(60),
ATTENDING_DOCTOR VARCHAR(60),
REFERRING_DOCTOR VARCHAR(60),
CONSULTING_DOCTOR VARCHAR(60),
FINANCIAL_CLASS VARCHAR(5),
FOREIGN KEY (ACCOUNTNUMBER) REFERENCES PATIENT (ACCOUNTNUMBER)

);

Create DIAGNOSIS table

CREATE TABLE DIAGNOSIS (

ACCOUNTNUMBER VARCHAR(20) not null,
DIAGNOSIS_CODING_METHOD VARCHAR(10),
DIAGNOSIS_CODE VARCHAR(250),
DIAGNOSIS_DESCRIPTION VARCHAR(250),
DIAGNOSIS_DATETIME DATETIME,
DIAGNOSIS_TYPE VARCHAR(2),
DIAGNOSIS_PRIORITY VARCHAR(2),
ATTESTATION_DATETIME DATETIME,
FOREIGN KEY (ACCOUNTNUMBER) REFERENCES PATIENT (ACCOUNTNUMBER)

);

Create INSURANCE Table

CREATE TABLE INSURANCE (

ACCOUNTNUMBER VARCHAR(20) not null,

INS_PLANID VARCHAR(30),
INS_COMPANYID VARCHAR(30),
INS_COMPANYNAME VARCHAR(60),
INS_COMPANYADDRESS VARCHAR(80),
INS_PHONE VARCHAR(15),
INS_GROUPNAME VARCHAR(60),
INS_GROUPNUMBER VARCHAR(30),
INS_GROUP_EMPLOYERID VARCHAR(20),
INS_GROUP_EMPLOYERNAME VARCHAR(60),
PLAN_EFFECTIVE_DATE DATETIME,
PLAN_EXPIRATION_DATE DATETIME,
PLAN_TYPE VARCHAR(30),
NAME_OF_INSURED VARCHAR(60),
INSURED_DATEOFBIRTH VARCHAR(12),
INSURED_RELATIONSHIP_TOPATIENT VARCHAR(10),
INSURED_ADDRESS VARCHAR(80),
POLICY_NUMBER VARCHAR(20),
COMPANY_PLANCODE VARCHAR(30),
INSURED_EMPLOYSTATUS VARCHAR(20),
INSURED_EMPLOYER_ADDRESS VARCHAR(80),
COVERAGE_TYPE VARCHAR(30),
INSURED_ID_NUMBER VARCHAR(20),
FOREIGN KEY (ACCOUNTNUMBER) REFERENCES PATIENT (ACCOUNTNUMBER)

);

PATIENT table

PATIENT_VISIT Table

DIAGNOSIS Table

INSURANCE Table

The examples below show how we configured the SQLXML database connection Database (SQL) database connection.

When you create your SQLXML Mapper, you will need to provide your database connection credentials. In this exercise we will be using the H2 database credentials as shown in the example below:

See the H2 database connection credentials configuration example below: