Current Topic: 2.5.4. DataService class simplified
You have a privilege to create a quiz (QnA) related to this subject and obtain creativity score...
The previous section Data Service Framework describes the concepts and provides the methods in the IOMaster, Parser, Stringer, and Stats classes. These methods are used by the DataService class.
We start the description with the init() method in the DataService class to read configuration files, including database configuration and SQL statement files.
For each data source we will have a Hashtable with key-value pairs, where SQL file name will serve as the key and the statement in the file as the value. There could be more than one data source, so we will associate such Hashtable with the name of a data source and store this Hashtable in the umbrella Hashtable appDetails.
Continue with the hints on Data Services Implementation Note:
Data Services is a small library collected in a single com.its.util.jar file.
There is no need to know these internals to use this library.
A complete API is provided at http://ITUniversity.us/downloads/html/api
But we find it useful for you to have some hints on how this is done, so you can see no magic involved and you can do similar or better when it comes to more complex matters.
A simplified version of the the DataService class is provided below:
/**
* The DataService class is the core class of DataService framework by ITS.
* The framework allows developers describe dependencies and desirable objects
* as configuration parameters saving a lot of coding efforts.
* DataService expects a simple configuration file with DB properties.
* SQL statements are not provided with Java code but stored separately in the {project}/config/sql/ - directory as small SQL files.
* DataService works with other framework classes, such as IOMaster and Stats to read configuration details and SQL statements nd
* store them in a static Hashtable called appDetails.
* At runtime DataService takes the name of an SQL file as an argument and retrieves the statement from the Hashtable.
* This allows to separate two different languages and makes easy testing SQL statements with existing SQL tools.
* Preferred way of database access is via PreparedStatement.
* PreparedStatement is safer than Statement objects from security prospects, often accelerate performance,
* and provides better handling for data with unexpected characters,
* such as single quote, which can confuse a regular Statement processing.
Recommended usage:
* 1. Prepare a configuration file describing your databases (can be more than one)
Example of a configuration file, its-ds.xml:
its its jdbc:oracle:thin:@localhost:1521:xe oracle.jdbc.driver.OracleDriver its its
* 2. Create SQL statements and store them in the SQL location with the extension ".sql"
Example:
select * from users where userName = ? and job = ?
* Store the line above in the file {project}/config/sql/selectUsersByNameAndJob.sql
* using PreparedStatement
*
* 3. At application start read configuration and associate a Database with its data source name (dsName) to
* prepare for work with the pool of connections. The method below will also read all SQL files into a Hashtable.
DataService.init(applicationName, dataSourceLogicalName, pathToConfigurationPropertyFile);
*
* 4. In the application at runtime use the method below, which will find a proper SQL statement and replace runtime variables
Example:
List listOfRecords = DataService.getPrepDataBySqlName("selectUsersByNameAndJob", new String[] {userName,job}, dsName);
* @author Jeff.Zhuk@Javaschool.com - http://ITUniversity.us - ITS, Inc.
Copyrights:
Jeff Zhuk and ITS, Inc. grant to you non-exclusive rights to use and modify the source.
*/
public class DataService {
// default drivers and URLs
public static String oracleDriverName = "oracle.jdbc.driver.OracleDriver";
public static String mySqlDriverName = "com.mysql.jdbc.Driver";
public static String msSqlDriverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
public static String postgreSqlDriverName = "org.postgresql.Driver";
public static String defaultMySqlConURL =
"jdbc:mysql://localhost:3306/lportal?user=root&password=lowtide"; // mySql
public static String defaultMsSqlConURL =
// MS SQL conURL - must replace {parameters}
"jdbc:microsoft:sqlserver://{dbInstanceName}:{dbPort};User={username};Password={password};DatabaseName={dbName}";
public static String defaultOracleXeConURL = "jdbc:oracle:thin:@localhost:1521:xe";
public static String defaultPostgreConURL = "jdbc:postgresql://localhost:5432/lportal?user=postgres&password=postgres"; // postgtres
public static String defaultSqlLocation = "c:/ITS/resources/config/sql";
public static String oraclePoolDataSourceClassName = "oracle.jdbc.pool.OracleDataSource";
public static String mySqlPoolDataSourceClassName = "com.mysql.jdbc.jdbc2.optional.MysqlDataSource";
public static Hashtable dataSources = new Hashtable();
/**
* The init() method reads the content of the configuration file
* extracts from the file the name of a data source, dsName, (can be several names, separated by comma);
* Then, for each dsName finds a proper dsContent to retrieve and capture data source properties.
* At the end the init() method calls for each data source the setDsForConnection() method to create a proper connection pool
* and readAndStoreSql statements, which are usually in the pathToConfig/dsName + "-sql" - directory
* @param appName
* @param pathToConfig
*/
public static void init(String appName, String pathToConfigFile) {
// get the directory name: if c:/ITS/project1/config/its-ds.xml -> c:/ITS/project1/config
String pathToConfig = Parser.parseWithPatterns(pathToConfigFile, "before last,/");
// store path to config directory
Stats.setAppDetails(appName, "pathToConfig", pathToConfig);
// read configuration file and SQL directory files
String configContent = IOMaster.readTextFile(pathToConfigFile);
String dsNamesString = Stringer.getStringBetweenTags(configContent, "dsNames");
// can be more than one name separated by comma
String[] dsNames = dsNamesString.split(",");
for(String dsName : dsNames) {
dsName = dsName.trim(); // in the case it includes spaces
String dsContent = Stringer.getStringBetweenTags(configContent, "local-tx-datasource-"+dsName);
String connectionUrl = Stringer.getStringBetweenTags(dsContent, "connection-url");
String driverClass = Stringer.getStringBetweenTags(dsContent, "driver-class");
String username = Stringer.getStringBetweenTags(dsContent, "user-name");
String password = Stringer.getStringBetweenTags(dsContent, "password");
Stats.setAppDetails(appName, dsName + "-connection-url", connectionUrl);
Stats.setAppDetails(appName, dsName + "-driver-class", driverClass);
Stats.setAppDetails(appName, dsName + "-user-name", username);
Stats.setAppDetails(appName, dsName + "-password", password);
String sqlLocation = pathToConfig + "/" + dsName + "-sql";
Stats.setAppDetails(appName, dsName + "-sqlLocation", sqlLocation);
setDsForConnection(connectionUrl, username, password, driverClass, dsName, sqlLocation);
}
}
/**
* The setDsForConnection() method sets DataSource values for a specific DB connection pool DB
* and associate this DataSource with the logical dsName
* Example:
setDsForConnection(defaultOracleXeConURL, "root", "jz", oracleDriverName, "lportal",
"d:/liferay/tomcat7/webapps/BASE/WEB-INF/sql");
* @param connectionUrl
* @param userName
* @param password
* @param driverName
* @param dsName
* @param sqlLocation if null will use default location
*/
public static void setDsForConnection(String connectionUrl, String username, String password,
String driverName, String dsName, String sqlLocation) {
readAndStoreSql(dsName, sqlLocation);
// specific pool requires specific jar files in the library
if(driverName.toLowerCase().contains("oracle") || driverName.toLowerCase().contains("mysql")) {
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
try {
if(driverName.toLowerCase().contains("oracle") ) {
pds.setConnectionFactoryClassName(oraclePoolDataSourceClassName);
} else {
pds.setConnectionFactoryClassName(mySqlPoolDataSourceClassName);
}
pds.setURL(connectionUrl);
pds.setUser(username);
pds.setPassword(password);
dataSources.put(dsName, pds);
} catch(Exception e) {
System.out.println("ERROR: "+e.getMessage());
}
}
// if this application needs more DB types, provide proper jars in the library and add similar code
// example for Microsoft SQL Server - uncomment when necessary after getting library in place
/*
else if(driverName.toLowerCase().contains("sqlserver")) { {
com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource msds = new com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource();
msds.setURL(connectionUrl);
msds.setUser(userName);
msds.setPassword(password);
setDataSource(dsName, msds);
}
*/
}
/**
* The readAndStoreSql() method scans sqlLocation for SQL files inside a proper directory dsName + "-sql"
* For example, if the dsName is "its", sql location is c:/ITS/resources/config/its-sql/
* All SQL statements will be stored in the Hashtable, which is placed in the appDetails with the key="its-sql"
* Each SQL statement will be stored as a value with the key equals the name of a proper SQL file with no extension
* For example, SQL statement in the file c:/ITS/resources/config/its-sql/selectAccountsByEmail.sql
* will be stored with the key="selectAccountsByEmail"
* @param dsName
* @param pathToSqlLocation
*/
public static void readAndStoreSql(String dsName, String pathToSqlLocation) {
File directory = new File(pathToSqlLocation);
String[] files = directory.list();
String hashTableName = dsName + "-sql";
for(String sqlFileName : files) {
if(!sqlFileName.endsWith(".sql")) {
// skip a file that is not SQL file
continue;
}
String sqlStatement = IOMaster.readTextFile(pathToSqlLocation + "/" + sqlFileName);
// the name without the extension ".sql"
String sqlName = sqlFileName.substring(0, sqlFileName.length()-4);
Stats.setAppDetails(hashTableName, sqlName, sqlStatement);
}
}
/**
* Getting a connection from a pool of connections
* @param dsName
* @return connection
* @throws Exception
*/
public static Connection getConnection(String dsName) throws Exception {
// retrieve a proper data source
DataSource ds = dataSources.get(dsName);
// get a connection from the pool
return ds.getConnection();
}
/**
* Select string data with PreparedStatement
* @param SqlName - the name of the SQL file
* @param runTimeParams passed to replace "?"
* @param dsName DataSource name
* @return listOfRows
* @throws Exception
*/
public static List getPrepDataBySqlName(
String sqlName, String[] runTimeParams, String dsName) throws Exception {
return getPrepDataBySqlName(sqlName, runTimeParams, dsName, null); // no types defined
}
/**
* The getPrepDataBySqlName() method retrieves the SQL and connects to a DB; distinguish some sql types
* @param sqlName
* @param runTimeParams
* @param dsName
* @param types such as java.sql.Types.INTEGER, java.sql.Types.DOUBLE, java.sql.Types.VARCHAR
* @return a list of records, each is an array of fields
* @throws Exception
*/
public static List getPrepDataBySqlName(
String sqlName, String[] runTimeParams, String dsName, int[] types) throws Exception {
String sql = Stats.getAppDetailsByKeyName(dsName+"-sql", sqlName);
Connection con = getConnection(dsName);
// the last argument is null for unknown but might be different column types
return getPrepData(con, sql, runTimeParams, types);
}
/**
* The method getPrepData() uses PreparedStatement to select data,
* replaces run-time variables with the passed runTimeParams
* The method discovers Column Types of the record and uses to properly retrieve values
* @param con
* @param sql
* @param runTimeParams
* @param types of parameters or if null it assumes all parameters are String types
* @return listOfRows
* @throws Exception
*/
public static List getPrepData(Connection con, String sql,
String[] runTimeParams, int[] types) throws Exception {
PreparedStatement pst = con.prepareStatement(sql);
for(int i=0; runTimeParams != null && i < runTimeParams.length; i++) {
if(types != null && types.length > i) {
int type = types[i];
if(type == java.sql.Types.INTEGER) {
int number = Integer.parseInt(runTimeParams[i]);
pst.setInt(i+1, number);
} else if(type == java.sql.Types.DOUBLE || type == java.sql.Types.FLOAT) {
double d = Double.parseDouble(runTimeParams[i]);
pst.setDouble(i+1, d);
} else {
pst.setString(i+1, runTimeParams[i]);
}
} else {
pst.setString(i+1, runTimeParams[i]);
}
}
ResultSet rs = pst.executeQuery();
// When the ResultSet is available, it is possible to discover the names and types of the columns
ResultSetMetaData rsmd = rs.getMetaData();
int nFields = rsmd.getColumnCount();
int[] columnTypes = new int[nFields];
for(int i=0; i < nFields; i++) {
columnTypes[i] = rsmd.getColumnType(i+1);
}
List listOfRows = new ArrayList();
for(;rs.next();) {
// prepare an array of fields to be filled with the values from the record
String[] fields = new String[nFields];
for(int j=0; j < nFields; j++) {
int type = columnTypes[j];
// this example only distinguishes String and Number types
// In the DataService framework this is extended with more types
if(type == java.sql.Types.INTEGER) {
int number = rs.getInt(j+1);
fields[j] = number+"";
} else if(type == java.sql.Types.DOUBLE || type == java.sql.Types.FLOAT) {
double d = rs.getDouble(j+1);
fields[j] = d + "";
} else {
fields[j] = rs.getString(j+1);
}
}
listOfRows.add(fields);
}
con.close();
return listOfRows;
}
/**
* Insert, update or delete a set of rows using PreparedStatement
* @param sqlName the name of the sql file which is the key in the Hashtable for the SQL statement
* @param runTimeParams runtime field replacements
* @param dsName
* @param types of parameters if null it assumes all parameters are String types
* Example of types: java.sql.Types.INTEGER, java.sql.Types.DOUBLE, java.sql.Types.VARCHAR
* @throws SQLException
*/
public static void setPrepDataBySqlName(String sqlName,
String[] runTimeParams, String dsName, int[] types) throws Exception {
String sql = Stats.getAppDetailsByKeyName(dsName+"-sql", sqlName);
Connection con = getConnection(dsName);
setPrepData(con, sql, runTimeParams, types);
}
/**
* Insert, update or delete a set of rows using PreparedStatement
* @param con
* @param sql
* @param runTimeParams
* @param types - if null it assumes all parameters are String types
* @throws Exception
*/
public static void setPrepData(Connection con, String sql,
String[] runTimeParams, int[] types) throws Exception {
PreparedStatement pst = con.prepareStatement(sql);
for(int i=0; runTimeParams != null && i < runTimeParams.length; i++) {
if(types != null && types.length > i) {
int type = types[i];
if(type == java.sql.Types.INTEGER) {
int number = Integer.parseInt(runTimeParams[i]);
pst.setInt(i+1, number);
} else if(type == java.sql.Types.DOUBLE || type == java.sql.Types.FLOAT) {
double d = Double.parseDouble(runTimeParams[i]);
pst.setDouble(i+1, d);
} else {
pst.setString(i+1, runTimeParams[i]);
}
} else {
pst.setString(i+1, runTimeParams[i]);
}
}
pst.execute();
con.close();
}
}
Was it clear so far?
The comments before each method give you good hints. Nevertheless, let us briefly discuss what is going on there.
The init() method reads a configuration file and uses data source properties to create data source objects and prepare for using a pool of connections.
In the case of Oracle or MySql, the program uses Universal Connection Pool (UCP) library, which provides the following default settings. Minimum number of connections by default is 0. UCP allows the pool to continue creating connections up to Integer.MAX_VALUE (2147483647 by default). This might not be the optimum setting. UCP allows developers provide their own minimum and maximum values by using the following methods:
pds.setMinPoolSize(2); // developers valu
pds.setMaxPoolSize(20); // developers value
This is an illustration to Universal Connection Pool by Oracle.
The init() method calls the setDsForConnection() method creating the pool objects. The method currently focuses on Oracle pool objects supported by the Oracle drivers in the library: ojdbc6.jar and ucp.jar.
The method can be easily expanded to other DB vendors if necessary. But keep in mind, that in this case proper supporting drivers must be placed in the library and added to the classpath (Build Java Path – Add to Build Path).
Each data source might have a set of prepared SQL statements, usually stored in the directory {configPath}/ sName+"-sql", for example, c:/ITS/config/its-sql/ - directory, which is called sqlLocation for a specific data source.
The method setDsForConnection() calls the readAndStoreSql() method to read and store these SQL statements. They are captured as values with the keys based on the file names where the statements are stored.
Naming recommendations are easy: the name of the file should clearly tell its intention, for example, selectAccountsByEmail.sql. The key will be selectAccountsByEmail and the value will be an SQL statement, the content of that file.
The next method is Connection getConnection(String dsName). The method retrieves a data source object from the dataSources Hashtable while using a data source name as a key.
Then it uses the standard method of a data source object, getConnection(), which returns a reusable connection from a pool of connections.
The getPrepDataBySqlName() method retrieves data from a database with a PreparedStatement. The arguments include a name of an SQL statement, runtime parameters if necessary to replace question marks in this statement, a data source logical name, and optionally types of the runtime parameters.
If there is no types defined, the method assumes that all parameter types are strings.
The method retrieves a proper SQL statement by its name and establishes a connection with the pool of connections. Then the method calls the getPrepData() and this poor servant does most of work.
First of all the program creates a PreparedStatement and replaces question marks with runtime variables while using proper data types. So, for text, the program uses setString() and for numeric – setInt() methods. Current extract only distinguishes numeric and text variables but DataService framework extends this method with more types in a similar manner.
Note that in SQL language numbering of variables starts with 1, while in Java with 0.
This is very well visible in the statement like below:
pst.setString(i+1, runTimeParams[i]);
Then the program executes the query and gets the ResultSet. When the ResultSet is available, it is possible to discover the types of the columns in the records of the ResultSet. The ResultSetMetaData class works as our discovery instrument.
ResultSetMetaData rsmd = rs.getMetaData();
int nFields = rsmd.getColumnCount();
String[] columnTypes = new String[nFields];
for(int i=0; i < nFields; i++) {
columnTypes[i] = rsmd.getColumnTypeName(i+1);
}
Why do we bother to know the types of the columns?
The program must properly retrieve data from the ResultSet, for example with rs.getString(1) or rs.getInt(1).
This is the extract of getting the records out of the ResultSet in the List of records, where each record is an array of string-fields.
List listOfRows = new ArrayList();
for(;rs.next();) {
// prepare an array of fields to be filled with the values from the record
String[] fields = new String[nFields];
for(int j=0; j < nFields; j++) {
int type = columnTypes[j];
// this example only distinguishes String and Number types
// In the DataService framework this is extended with more types
if(type == java.sql.Types.INTEGER) {
int number = rs.getInt(j+1);
fields[j] = number+"";
} else if(type == java.sql.Types.DOUBLE || type == java.sql.Types.FLOAT) {
double d = rs.getDouble(j+1);
fields[j] = d + "";
} else {
fields[j] = rs.getString(j+1);
}
}
listOfRows.add(fields);
}
con.close();
return listOfRows;
In a very similar manner the program uses a PreparedStatement to Insert, Update, or Delete records.
The arguments include a name of an SQL statement, runtime parameters if necessary to replace question marks in this statement, a data source logical name, and optionally types of the runtime parameters.
If there is no types defined, the method assumes that all parameter types are strings.
The method retrieves a proper SQL statement by its name and establishes a connection with the pool of connections. Then the method calls the setPrepData() to perform the rest of work.
The setPrepData() walks over the same steps as the getPrepData() method:
a) First of all the program creates a PreparedStatement and replaces question marks with runtime variables while using proper data types. So, for text, the program uses setString() and for numeric – setInt() methods. Current extract only distinguishes numeric and text variables but DataService framework extends this method with more types in a similar manner.
Note that in SQL language numbering of variables starts with 1, while in Java with 0. This is very well visible in the statement like below:
pst.setString(i+1, runTimeParams[i]);
b) Then the program executes the PreparedStatement which will change (Insert, Update, or Delete) data.
c) There is no ResultSet, so no need for further processing, done!
Assignments: 1. Open Eclipse and navigate to the project week4db.
2. Add to the package its.day11.db a new class DataService.
3. One by one type the headers and methods from this section to the class
4. Get rid of red underscore lines provided by Eclipse to indicate errors.
5. Create 2 QnAs related to the subject and email the instructor
<br/>package com.its.util;
<br/>
<br/>import java.io.File;
<br/>import java.sql.Connection;
<br/>import java.sql.PreparedStatement;
<br/>import java.sql.ResultSet;
<br/>import java.sql.ResultSetMetaData;
<br/>import java.sql.SQLException;
<br/>import java.util.ArrayList;
<br/>import java.util.Hashtable;
<br/>import java.util.List;
<br/>// generic DataSource interface
<br/>import javax.sql.DataSource;
<br/>// specific Universal Connection Pool (UCP) implementation of Oracle
<br/>import oracle.ucp.jdbc.PoolDataSource;
<br/>import oracle.ucp.jdbc.PoolDataSourceFactory;
<br/>
<br/>
<br/>/**
<br/> * The DataService class is the core class of DataService framework by ITS.
<br/> * The framework allows developers describe dependencies and desirable objects
<br/> * as configuration parameters saving a lot of coding efforts.
<br/> * DataService expects a simple configuration file with DB properties.
<br/> * SQL statements are not provided with Java code but stored separately in the {project}/config/sql/ - directory as small SQL files.
<br/> * DataService works with other framework classes, such as IOMaster and Stats to read configuration details and SQL statements nd
<br/> * store them in a static Hashtable called appDetails.
<br/> * At runtime DataService takes the name of an SQL file as an argument and retrieves the statement from the Hashtable.
<br/> * This allows to separate two different languages and makes easy testing SQL statements with existing SQL tools.
<br/> * Preferred way of database access is via PreparedStatement.
<br/> * PreparedStatement is safer than Statement objects from security prospects, often accelerate performance,
<br/> * and provides better handling for data with unexpected characters,
<br/> * such as single quote, which can confuse a regular Statement processing.
<br/>
<br/>
<br/> Recommended usage:
<br/> * 1. Prepare a configuration file describing your databases (can be more than one)
<br/> Example of a configuration file, its-ds.xml:
<br/>
<br/><?xml version="1.0" encoding="UTF-8"?>
<br/><datasources>
<br/> <dsNames>its</dsNames>
<br/> <local-tx-datasource-its>
<br/> <jndi-name>its</jndi-name>
<br/> <connection-url>jdbc:oracle:thin:@localhost:1521:xe</connection-url>
<br/> <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<br/> <user-name>its</user-name>
<br/> <password>its</password>
<br/> </local-tx-datasource-its>
<br/></datasources>
<br/>
<br/> * 2. Create SQL statements and store them in the SQL location with the extension ".sql"
<br/> Example:
<br/> select * from users where userName = ? and job = ?
<br/> * Store the line above in the file {project}/config/sql/selectUsersByNameAndJob.sql
<br/> * using PreparedStatement
<br/> *
<br/> * 3. At application start read configuration and associate a Database with its data source name (dsName) to
<br/> * prepare for work with the pool of connections. The method below will also read all SQL files into a Hashtable.
<br/>DataService.init(applicationName, dataSourceLogicalName, pathToConfigurationPropertyFile);
<br/> *
<br/> * 4. In the application at runtime use the method below, which will find a proper SQL statement and replace runtime variables
<br/> Example:
<br/> List<String[]> listOfRecords = DataService.getPrepDataBySqlName("selectUsersByNameAndJob", new String[] {userName,job}, dsName);
<br/>
<br/> * @author Jeff.Zhuk@Javaschool.com - http://ITUniversity.us - ITS, Inc.
<br/>Copyrights:
<br/>Jeff Zhuk and ITS, Inc. grant to you non-exclusive rights to use and modify the source.
<br/> */
<br/>public class DataService {
<br/> // default drivers and URLs
<br/> public static String oracleDriverName = "oracle.jdbc.driver.OracleDriver";
<br/> public static String mySqlDriverName = "com.mysql.jdbc.Driver";
<br/> public static String msSqlDriverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
<br/> public static String postgreSqlDriverName = "org.postgresql.Driver";
<br/> public static String defaultMySqlConURL =
<br/>"jdbc:mysql://localhost:3306/lportal?user=root&password=lowtide"; // mySql
<br/> public static String defaultMsSqlConURL =
<br/>// MS SQL conURL - must replace {parameters}
<br/>"jdbc:microsoft:sqlserver://{dbInstanceName}:{dbPort};User={username};Password={password};DatabaseName={dbName}";
<br/> public static String defaultOracleXeConURL = "jdbc:oracle:thin:@localhost:1521:xe";
<br/> public static String defaultPostgreConURL = "jdbc:postgresql://localhost:5432/lportal?user=postgres&password=postgres"; // postgtres
<br/> public static String defaultSqlLocation = "c:/ITS/resources/config/sql";
<br/> public static String oraclePoolDataSourceClassName = "oracle.jdbc.pool.OracleDataSource";
<br/> public static String mySqlPoolDataSourceClassName = "com.mysql.jdbc.jdbc2.optional.MysqlDataSource";
<br/> public static Hashtable<String, DataSource> dataSources = new Hashtable<String, DataSource>();
<br/> /**
<br/> * The init() method reads the content of the configuration file
<br/> * extracts from the file the name of a data source, dsName, (can be several names, separated by comma);
<br/> * Then, for each dsName finds a proper dsContent to retrieve and capture data source properties.
<br/> * At the end the init() method calls for each data source the setDsForConnection() method to create a proper connection pool
<br/> * and readAndStoreSql statements, which are usually in the pathToConfig/dsName + "-sql" - directory
<br/> * @param appName
<br/> * @param pathToConfig
<br/> */
<br/> public static void init(String appName, String pathToConfigFile) {
<br/> // get the directory name: if c:/ITS/project1/config/its-ds.xml -> c:/ITS/project1/config
<br/> String pathToConfig = Parser.parseWithPatterns(pathToConfigFile, "before last,/");
<br/> // store path to config directory
<br/> Stats.setAppDetails(appName, "pathToConfig", pathToConfig);
<br/> // read configuration file and SQL directory files
<br/> String configContent = IOMaster.readTextFile(pathToConfigFile);
<br/> String dsNamesString = Stringer.getStringBetweenTags(configContent, "dsNames");
<br/> // can be more than one name separated by comma
<br/> String[] dsNames = dsNamesString.split(",");
<br/> for(String dsName : dsNames) {
<br/> dsName = dsName.trim(); // in the case it includes spaces
<br/> String dsContent = Stringer.getStringBetweenTags(configContent, "local-tx-datasource-"+dsName);
<br/> String connectionUrl = Stringer.getStringBetweenTags(dsContent, "connection-url");
<br/> String driverClass = Stringer.getStringBetweenTags(dsContent, "driver-class");
<br/> String username = Stringer.getStringBetweenTags(dsContent, "user-name");
<br/> String password = Stringer.getStringBetweenTags(dsContent, "password");
<br/> Stats.setAppDetails(appName, dsName + "-connection-url", connectionUrl);
<br/> Stats.setAppDetails(appName, dsName + "-driver-class", driverClass);
<br/> Stats.setAppDetails(appName, dsName + "-user-name", username);
<br/> Stats.setAppDetails(appName, dsName + "-password", password);
<br/> String sqlLocation = pathToConfig + "/" + dsName + "-sql";
<br/> Stats.setAppDetails(appName, dsName + "-sqlLocation", sqlLocation);
<br/> setDsForConnection(connectionUrl, username, password, driverClass, dsName, sqlLocation);
<br/> }
<br/>
<br/> }
<br/> /**
<br/> * The setDsForConnection() method sets DataSource values for a specific DB connection pool DB
<br/> * and associate this DataSource with the logical dsName
<br/> * Example:
<br/> setDsForConnection(defaultOracleXeConURL, "root", "jz", oracleDriverName, "lportal",
<br/> "d:/liferay/tomcat7/webapps/BASE/WEB-INF/sql");
<br/> * @param connectionUrl
<br/> * @param userName
<br/> * @param password
<br/> * @param driverName
<br/> * @param dsName
<br/> * @param sqlLocation if null will use default location
<br/> */
<br/> public static void setDsForConnection(String connectionUrl, String username, String password,
<br/> String driverName, String dsName, String sqlLocation) {
<br/> readAndStoreSql(dsName, sqlLocation);
<br/> // specific pool requires specific jar files in the library
<br/> if(driverName.toLowerCase().contains("oracle") || driverName.toLowerCase().contains("mysql")) {
<br/> PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
<br/> try {
<br/> if(driverName.toLowerCase().contains("oracle") ) {
<br/> pds.setConnectionFactoryClassName(oraclePoolDataSourceClassName);
<br/> } else {
<br/> pds.setConnectionFactoryClassName(mySqlPoolDataSourceClassName);
<br/> }
<br/> pds.setURL(connectionUrl);
<br/> pds.setUser(username);
<br/> pds.setPassword(password);
<br/> dataSources.put(dsName, pds);
<br/> } catch(Exception e) {
<br/> System.out.println("ERROR: "+e.getMessage());
<br/> }
<br/> }
<br/> // if this application needs more DB types, provide proper jars in the library and add similar code
<br/> // example for Microsoft SQL Server - uncomment when necessary after getting library in place
<br/> /*
<br/> else if(driverName.toLowerCase().contains("sqlserver")) { {
<br/> com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource msds = new com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource();
<br/> msds.setURL(connectionUrl);
<br/> msds.setUser(userName);
<br/> msds.setPassword(password);
<br/> setDataSource(dsName, msds);
<br/> }
<br/> */
<br/> }
<br/> /**
<br/> * The readAndStoreSql() method scans sqlLocation for SQL files inside a proper directory dsName + "-sql"
<br/> * For example, if the dsName is "its", sql location is c:/ITS/resources/config/its-sql/
<br/> * All SQL statements will be stored in the Hashtable, which is placed in the appDetails with the key="its-sql"
<br/> * Each SQL statement will be stored as a value with the key equals the name of a proper SQL file with no extension
<br/> * For example, SQL statement in the file c:/ITS/resources/config/its-sql/selectAccountsByEmail.sql
<br/> * will be stored with the key="selectAccountsByEmail"
<br/> * @param dsName
<br/> * @param pathToSqlLocation
<br/> */
<br/> public static void readAndStoreSql(String dsName, String pathToSqlLocation) {
<br/> File directory = new File(pathToSqlLocation);
<br/> String[] files = directory.list();
<br/> String hashTableName = dsName + "-sql";
<br/> for(String sqlFileName : files) {
<br/> if(!sqlFileName.endsWith(".sql")) {
<br/> // skip a file that is not SQL file
<br/> continue;
<br/> }
<br/> String sqlStatement = IOMaster.readTextFile(pathToSqlLocation + "/" + sqlFileName);
<br/> // the name without the extension ".sql"
<br/> String sqlName = sqlFileName.substring(0, sqlFileName.length()-4);
<br/> Stats.setAppDetails(hashTableName, sqlName, sqlStatement);
<br/> }
<br/> }
<br/> /**
<br/> * Getting a connection from a pool of connections
<br/> * @param dsName
<br/> * @return connection
<br/> * @throws Exception
<br/> */
<br/> public static Connection getConnection(String dsName) throws Exception {
<br/> // retrieve a proper data source
<br/> DataSource ds = dataSources.get(dsName);
<br/> // get a connection from the pool
<br/> return ds.getConnection();
<br/> }
<br/> /**
<br/> * Select string data with PreparedStatement
<br/> * @param SqlName - the name of the SQL file
<br/> * @param runTimeParams passed to replace "?"
<br/> * @param dsName DataSource name
<br/> * @return listOfRows
<br/> * @throws Exception
<br/> */
<br/> public static List<String[]> getPrepDataBySqlName(
<br/> String sqlName, String[] runTimeParams, String dsName) throws Exception {
<br/> return getPrepDataBySqlName(sqlName, runTimeParams, dsName, null); // no types defined
<br/> }
<br/> /**
<br/> * The getPrepDataBySqlName() method retrieves the SQL and connects to a DB; distinguish some sql types
<br/> * @param sqlName
<br/> * @param runTimeParams
<br/> * @param dsName
<br/> * @param types such as java.sql.Types.INTEGER, java.sql.Types.DOUBLE, java.sql.Types.VARCHAR
<br/> * @return a list of records, each is an array of fields
<br/> * @throws Exception
<br/> */
<br/> public static List<String[]> getPrepDataBySqlName(
<br/> String sqlName, String[] runTimeParams, String dsName, int[] types) throws Exception {
<br/>
<br/> String sql = Stats.getAppDetailsByKeyName(dsName+"-sql", sqlName);
<br/> Connection con = getConnection(dsName);
<br/> // the last argument is null for unknown but might be different column types
<br/> return getPrepData(con, sql, runTimeParams, types);
<br/> }
<br/> /**
<br/> * The method getPrepData() uses PreparedStatement to select data,
<br/> * replaces run-time variables with the passed runTimeParams
<br/> * The method discovers Column Types of the record and uses to properly retrieve values
<br/> * @param con
<br/> * @param sql
<br/> * @param runTimeParams
<br/> * @param types of parameters or if null it assumes all parameters are String types
<br/> * @return listOfRows
<br/> * @throws Exception
<br/> */
<br/> public static List<String[]> getPrepData(Connection con, String sql,
<br/> String[] runTimeParams, int[] types) throws Exception {
<br/>
<br/> PreparedStatement pst = con.prepareStatement(sql);
<br/> for(int i=0; runTimeParams != null && i < runTimeParams.length; i++) {
<br/> if(types != null && types.length > i) {
<br/> int type = types[i];
<br/> if(type == java.sql.Types.INTEGER) {
<br/> int number = Integer.parseInt(runTimeParams[i]);
<br/> pst.setInt(i+1, number);
<br/> } else if(type == java.sql.Types.DOUBLE || type == java.sql.Types.FLOAT) {
<br/> double d = Double.parseDouble(runTimeParams[i]);
<br/> pst.setDouble(i+1, d);
<br/> } else {
<br/> pst.setString(i+1, runTimeParams[i]);
<br/> }
<br/> } else {
<br/> pst.setString(i+1, runTimeParams[i]);
<br/> }
<br/> }
<br/> ResultSet rs = pst.executeQuery();
<br/> // When the ResultSet is available, it is possible to discover the names and types of the columns
<br/> ResultSetMetaData rsmd = rs.getMetaData();
<br/> int nFields = rsmd.getColumnCount();
<br/> int[] columnTypes = new int[nFields];
<br/> for(int i=0; i < nFields; i++) {
<br/> columnTypes[i] = rsmd.getColumnType(i+1);
<br/> }
<br/> List<String[]> listOfRows = new ArrayList<String[]>();
<br/> for(;rs.next();) {
<br/> // prepare an array of fields to be filled with the values from the record
<br/> String[] fields = new String[nFields];
<br/> for(int j=0; j < nFields; j++) {
<br/> int type = columnTypes[j];
<br/> // this example only distinguishes String and Number types
<br/> // In the DataService framework this is extended with more types
<br/> if(type == java.sql.Types.INTEGER) {
<br/> int number = rs.getInt(j+1);
<br/> fields[j] = number+"";
<br/> } else if(type == java.sql.Types.DOUBLE || type == java.sql.Types.FLOAT) {
<br/> double d = rs.getDouble(j+1);
<br/> fields[j] = d + "";
<br/> } else {
<br/> fields[j] = rs.getString(j+1);
<br/> }
<br/> }
<br/> listOfRows.add(fields);
<br/> }
<br/> con.close();
<br/> return listOfRows;
<br/> }
<br/> /**
<br/> * Insert, update or delete a set of rows using PreparedStatement
<br/> * @param sqlName the name of the sql file which is the key in the Hashtable for the SQL statement
<br/> * @param runTimeParams runtime field replacements
<br/> * @param dsName
<br/> * @param types of parameters if null it assumes all parameters are String types
<br/> * Example of types: java.sql.Types.INTEGER, java.sql.Types.DOUBLE, java.sql.Types.VARCHAR
<br/> * @throws SQLException
<br/> */
<br/> public static void setPrepDataBySqlName(String sqlName,
<br/> String[] runTimeParams, String dsName, int[] types) throws Exception {
<br/> String sql = Stats.getAppDetailsByKeyName(dsName+"-sql", sqlName);
<br/> Connection con = getConnection(dsName);
<br/> setPrepData(con, sql, runTimeParams, types);
<br/> }
<br/> /**
<br/> * Insert, update or delete a set of rows using PreparedStatement
<br/> * @param con
<br/> * @param sql
<br/> * @param runTimeParams
<br/> * @param types - if null it assumes all parameters are String types
<br/> * @throws Exception
<br/> */
<br/> public static void setPrepData(Connection con, String sql,
<br/> String[] runTimeParams, int[] types) throws Exception {
<br/> PreparedStatement pst = con.prepareStatement(sql);
<br/> for(int i=0; runTimeParams != null && i < runTimeParams.length; i++) {
<br/> if(types != null && types.length > i) {
<br/> int type = types[i];
<br/> if(type == java.sql.Types.INTEGER) {
<br/> int number = Integer.parseInt(runTimeParams[i]);
<br/> pst.setInt(i+1, number);
<br/> } else if(type == java.sql.Types.DOUBLE || type == java.sql.Types.FLOAT) {
<br/> double d = Double.parseDouble(runTimeParams[i]);
<br/> pst.setDouble(i+1, d);
<br/> } else {
<br/> pst.setString(i+1, runTimeParams[i]);
<br/> }
<br/> } else {
<br/> pst.setString(i+1, runTimeParams[i]);
<br/> }
<br/> }
<br/> pst.execute();
<br/> con.close();
<br/> }
<br/>}
<br/>
Was it clear so far?
onclick="window.location.href='/BASE/jsp/demo.jsp?checkFlavor=itsp&issueID=59&intro=general&group=aitu&ur=f'">
The comments before each method give you good hints. Nevertheless, let us briefly discuss what is going on there.
The init() method reads a configuration file and uses data source properties to create data source objects and prepare for using a pool of connections.
In the case of Oracle or MySql, the program uses Universal Connection Pool (UCP) library, which provides the following default settings. Minimum number of connections by default is 0. UCP allows the pool to continue creating connections up to Integer.MAX_VALUE (2147483647 by default). This might not be the optimum setting. UCP allows developers provide their own minimum and maximum values by using the following methods:
pds.setMinPoolSize(2); // developers valu
pds.setMaxPoolSize(20); // developers value
This is an illustration to Universal Connection Pool by Oracle.
The init() method calls the setDsForConnection() method creating the pool objects. The method currently focuses on Oracle pool objects supported by the Oracle drivers in the library: ojdbc6.jar and ucp.jar.
The method can be easily expanded to other DB vendors if necessary. But keep in mind, that in this case proper supporting drivers must be placed in the library and added to the classpath (Build Java Path – Add to Build Path).
Each data source might have a set of prepared SQL statements, usually stored in the directory {configPath}/ sName+"-sql", for example, c:/ITS/config/its-sql/ - directory, which is called sqlLocation for a specific data source.
The method setDsForConnection() calls the readAndStoreSql() method to read and store these SQL statements. They are captured as values with the keys based on the file names where the statements are stored.
Naming recommendations are easy: the name of the file should clearly tell its intention, for example, selectAccountsByEmail.sql. The key will be selectAccountsByEmail and the value will be an SQL statement, the content of that file.
The next method is Connection getConnection(String dsName). The method retrieves a data source object from the dataSources Hashtable while using a data source name as a key.
Then it uses the standard method of a data source object, getConnection(), which returns a reusable connection from a pool of connections.
The getPrepDataBySqlName() method retrieves data from a database with a PreparedStatement. The arguments include a name of an SQL statement, runtime parameters if necessary to replace question marks in this statement, a data source logical name, and optionally types of the runtime parameters.
If there is no types defined, the method assumes that all parameter types are strings.
The method retrieves a proper SQL statement by its name and establishes a connection with the pool of connections. Then the method calls the getPrepData() and this poor servant does most of work.
First of all the program creates a PreparedStatement and replaces question marks with runtime variables while using proper data types. So, for text, the program uses setString() and for numeric – setInt() methods. Current extract only distinguishes numeric and text variables but DataService framework extends this method with more types in a similar manner.
Note that in SQL language numbering of variables starts with 1, while in Java with 0.
This is very well visible in the statement like below:
pst.setString(i+1, runTimeParams[i]);
Then the program executes the query and gets the ResultSet. When the ResultSet is available, it is possible to discover the types of the columns in the records of the ResultSet. The ResultSetMetaData class works as our discovery instrument.
Why do we bother to know the types of the columns?
The program must properly retrieve data from the ResultSet, for example with rs.getString(1) or rs.getInt(1).
This is the extract of getting the records out of the ResultSet in the List of records, where each record is an array of string-fields.
<br/> List<String[]> listOfRows = new ArrayList<String[]>();
<br/>
<br/> for(;rs.next();) {
<br/> // prepare an array of fields to be filled with the values from the record
<br/> String[] fields = new String[nFields];
<br/> for(int j=0; j < nFields; j++) {
<br/> int type = columnTypes[j];
<br/> // this example only distinguishes String and Number types
<br/> // In the DataService framework this is extended with more types
<br/> if(type == java.sql.Types.INTEGER) {
<br/> int number = rs.getInt(j+1);
<br/> fields[j] = number+"";
<br/> } else if(type == java.sql.Types.DOUBLE || type == java.sql.Types.FLOAT) {
<br/> double d = rs.getDouble(j+1);
<br/> fields[j] = d + "";
<br/> } else {
<br/> fields[j] = rs.getString(j+1);
<br/> }
<br/> }
<br/> listOfRows.add(fields);
<br/> }
<br/> con.close();
<br/> return listOfRows;
<br/>
In a very similar manner the program uses a PreparedStatement to Insert, Update, or Delete records.
The arguments include a name of an SQL statement, runtime parameters if necessary to replace question marks in this statement, a data source logical name, and optionally types of the runtime parameters.
If there is no types defined, the method assumes that all parameter types are strings.
The method retrieves a proper SQL statement by its name and establishes a connection with the pool of connections. Then the method calls the setPrepData() to perform the rest of work.
The setPrepData() walks over the same steps as the getPrepData() method:
a) First of all the program creates a PreparedStatement and replaces question marks with runtime variables while using proper data types. So, for text, the program uses setString() and for numeric – setInt() methods. Current extract only distinguishes numeric and text variables but DataService framework extends this method with more types in a similar manner.
Note that in SQL language numbering of variables starts with 1, while in Java with 0. This is very well visible in the statement like below:
pst.setString(i+1, runTimeParams[i]);
b) Then the program executes the PreparedStatement which will change (Insert, Update, or Delete) data.
c) There is no ResultSet, so no need for further processing, done!
Assignments: 1. Open Eclipse and navigate to the project week4db.
2. Add to the package its.day11.db a new class DataService.
3. One by one type the headers and methods from this section to the class
4. Get rid of red underscore lines provided by Eclipse to indicate errors.
5. Create 2 QnAs related to the subject and email the instructor
>> 80. Technology >> Internet Technology Summit Program >> 2. Java and Databases >> 2.5. Data Handling Frameworks
