Current Topic: 4.4.4.6.2. DataService handling DB connections
You have a privilege to create a quiz (QnA) related to this subject and obtain creativity score...
DataService handling DB connections
The framework does most of the plumbing work of connecting to DB and executing the SQL statements.
To better understand how this is done, take a look at the extracts from several sources.
DataService framework can be used in a stand alone desktop application as well as in a web application.
In the extracts we focus on the case of web applications where main configuration variables are provided in the web.xml - file.
For a big application with many action-services there is a need for action-specific configuration messages.
Such data are provided in specific configuration files, which are handled by the ConversationManager class considered in another lesson.
When a web application starts, DataService framework automatically initiates both: messages and data pool by providing these two lines in the ItsBaseServlet.contextInitialized() method:
ConversationManager.init(appName); // init messages
DataService.init(appName); // init DB connections
Sample Here are the extracts from the DataService class that reads the DB configuration variables and creates a connection pool.
public class DataService {
public static String defaultMySqlPort = "3306";
public static String defaultDbType = "mysql";
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/content?user=root&password=lowtide"; // mySql
public static String defaultOracleXeConURL = "jdbc:oracle:thin:admin/pswdt@localhost:1521:xe";
public static String defaultPostgreConURL = "jdbc:postgresql://localhost:5432/lportal?user=postgres&password=postgres"; // postgtres
public static String dynamicMySqlConURL = "jdbc:mysql://{hostname}:{dbPort}/{dbName}?user={userName}&password={password}"; // mySql
public static String dynamicMsSqlConURL = "jdbc:microsoft:sqlserver://{hostname}:{dbPort};User={userName};Password={password};DatabaseName={dbName}"; // MS SQL conURL - must replace {parameters}
public static String dynamicPostgreConURL = "jdbc:postgresql://{hostname}:{dbPort}/{dbName}?user={userName}&password={password}"; // postgtres
public static String dynamicOracleConURL = "jdbc:oracle:thin:{userName}/{password}@{hostname}:{dbPort}:{dbName}";
public static String defaultSqlLocation = "config/sql";
public static String oraclePoolDataSourceClassName = "oracle.jdbc.pool.OracleDataSource";
public static String mySqlPoolDataSourceClassName = "com.mysql.jdbc.jdbc2.optional.MysqlDataSource";
private static String[] dbConnectComponents = {
"microsoft",msSqlDriverName, dynamicMsSqlConURL, "SQLServer2008Dialect", //A dialect for Microsoft SQL Server 2008 with JDBC Driver 3.0 and above
"postgresql", postgreSqlDriverName, dynamicPostgreConURL, "PostgreSQLDialect",
"mysql", mySqlDriverName, dynamicMySqlConURL, "MySQLDialect", // An SQL dialect for MySQL (prior to 5.x) or MySQL5Dialect for 5.0 features
"oracle", oracleDriverName, dynamicOracleConURL, "OracleDialect"};
public static List listOfDbConnectComponents = Arrays.asList(dbConnectComponents);
public static Hashtable dataSources = new Hashtable();
/**
* initDs() is called usually at a start time; for web apps by contextInitialized() - automatic call by a system
* In the DataService framework it is called by the ItsBaseServlet.contextInitialized()
* @param appName
*/
public static void initDs(String appName) {
// dbParamString in web.xml: dbName, userName, psw, dsName, sqlLocation, hostname, dbPort, dbType-->
String dbParamString = Stats.getAppDetailsByKeyName(appName, "dbParamString");
String[] dbParams = Stringer.split(",", dbParamString);
String dbName = dbParams[0].trim();
String userName = dbParams[1].trim();
String psw = dbParams[2].trim();
String originDsName = dbParams[3].trim(); // for example, "sampleDB"
String dsName = appName +"/" + originDsName; // for example, "BASE/sampleDB"
String pathToDeploy = Stats.getAppDetailsByKeyName(appName, "pathToDeploy");
String appLocationName = Stats.getAppDetailsByKeyName(appName, "appLocationName");
String sqlLocation = pathToDeploy + appLocationName + "/" + DataService.defaultSqlLocation; // config/sql
if(dbParams.length > 4) {
sqlLocation = pathToDeploy + appLocationName + "/" + dbParams[4].trim();
}
String hostname = "localhost"; // default hostname
if(dbParams.length > 5) {
hostname = dbParams[5].trim();
}
String dbPort = DataService.defaultMySqlPort; // default port for mysql
if(dbParams.length > 6) {
dbPort = dbParams[6].trim();
}
String dbType = DataService.defaultDbType; // "mysql"
String dbDriverName = DataService.mySqlDriverName;
String dbURL = DataService.defaultMySqlConURL;
if(dbParams.length > 7) {
// use dbType to get dbURL and dbDriver
dbType = dbParams[7].trim().toLowerCase(); // microsoft,postgresql,mysql,oracle (db2?)
int indexOfDbType = DataService.listOfDbConnectComponents.indexOf(dbType);
if(indexOfDbType >= 0) {
dbDriverName = DataService.listOfDbConnectComponents.get(indexOfDbType + 1);
dbURL = DataService.listOfDbConnectComponents.get(indexOfDbType + 2);
}
}
createConnectionPool(dbURL, dbName, userName, psw, dbDriverName, dsName);
// source is not provided, consists of simple IOMaster.readTextFile() in the loop
readAndStoreSql(appName, sqlLocation); // read SQL files and cache in the Hashtable
}
/**
* 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);
// dataSources - a Hashtable with dsName as a key and DataSource object populated in the createConnectionPool()
// get a connection from the pool
return ds.getConnection();
}
/**
* createConnectionPool() uses db params from web.xml
* @param dbURL
* @param dbName
* @param username
* @param password
* @param driverName
* @param dsName
* @return dbURL
*/
public static String createConnectionPool(String dbURL, String dbName, String username, String password,
String driverName, String dsName) {
if(driverName.equals(msSqlDriverName)) {
/** Uncomment to work with MS SQL Server (0), Postgresql (1), or MySql (2) DB ....
* if this application needs more DB types, provide proper jars in the library and add similar code
com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource msds = new com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource();
msds.setURL(connectionUrl);
msds.setUser(username);
msds.setPassword(password);
setDataSource(dsName, msds);
return;
] else if(driverName.equals(postgreSqlDriverName)) {
org.postgresql.ds.PGSimpleDataSource pds = new org.postgresql.ds.PGSimpleDataSource();
pds.setDatabaseName(dbName);
pds.setServerName(hostname);
pds.setPortNumber(Integer.parseInt(dbPort));
pds.setUser(username);
pds.setPassword(password);
setDataSource(dsName, pds);
return;
//optional for mysql, remove if mysql.jar is not in the lib
*/
} else if(driverName.equals(mySqlDriverName)) {
MysqlDataSource pds = new MysqlDataSource();
pds.setURL(dbURL);
pds.setUser(username);
pds.setPassword(password);
dataSources.put(dsName, pds);
return dbURL;
} else if(driverName.equals(oracleDriverName)) {
try {
OracleDataSource opds = new OracleDataSource();
opds.setURL(dbURL);
opds.setUser(username);
opds.setPassword(password);
dataSources.put(dsName, opds);
} catch(Exception e) {
System.out.println("ERROR: "+e.getMessage());
}
}
return dbURL;
}
}
Was it clear so far?
Assignments: 1. Read the source and expand the description of each method in the Discussion section
2. Add suggestions and questions to the source there.
3. Provide 2 QnA related to this lesson.
<br/>ConversationManager.init(appName); // init messages
<br/>DataService.init(appName); // init DB connections
<br/>
Sample Here are the extracts from the DataService class that reads the DB configuration variables and creates a connection pool.
<br/>public class DataService {
<br/> public static String defaultMySqlPort = "3306";
<br/> public static String defaultDbType = "mysql";
<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/>
<br/> public static String defaultMySqlConURL = "jdbc:mysql://localhost:3306/content?user=root&password=lowtide"; // mySql
<br/> public static String defaultOracleXeConURL = "jdbc:oracle:thin:admin/pswdt@localhost:1521:xe";
<br/> public static String defaultPostgreConURL = "jdbc:postgresql://localhost:5432/lportal?user=postgres&password=postgres"; // postgtres
<br/>
<br/> public static String dynamicMySqlConURL = "jdbc:mysql://{hostname}:{dbPort}/{dbName}?user={userName}&password={password}"; // mySql
<br/> public static String dynamicMsSqlConURL = "jdbc:microsoft:sqlserver://{hostname}:{dbPort};User={userName};Password={password};DatabaseName={dbName}"; // MS SQL conURL - must replace {parameters}
<br/> public static String dynamicPostgreConURL = "jdbc:postgresql://{hostname}:{dbPort}/{dbName}?user={userName}&password={password}"; // postgtres
<br/> public static String dynamicOracleConURL = "jdbc:oracle:thin:{userName}/{password}@{hostname}:{dbPort}:{dbName}";
<br/>
<br/> public static String defaultSqlLocation = "config/sql";
<br/> public static String oraclePoolDataSourceClassName = "oracle.jdbc.pool.OracleDataSource";
<br/> public static String mySqlPoolDataSourceClassName = "com.mysql.jdbc.jdbc2.optional.MysqlDataSource";
<br/>
<br/> private static String[] dbConnectComponents = {
<br/> "microsoft",msSqlDriverName, dynamicMsSqlConURL, "SQLServer2008Dialect", //A dialect for Microsoft SQL Server 2008 with JDBC Driver 3.0 and above
<br/> "postgresql", postgreSqlDriverName, dynamicPostgreConURL, "PostgreSQLDialect",
<br/> "mysql", mySqlDriverName, dynamicMySqlConURL, "MySQLDialect", // An SQL dialect for MySQL (prior to 5.x) or MySQL5Dialect for 5.0 features
<br/> "oracle", oracleDriverName, dynamicOracleConURL, "OracleDialect"};
<br/> public static List<String> listOfDbConnectComponents = Arrays.asList(dbConnectComponents);
<br/>
<br/> public static Hashtable<String, DataSource> dataSources = new Hashtable<String, DataSource>();
<br/> /**
<br/> * initDs() is called usually at a start time; for web apps by contextInitialized() - automatic call by a system
<br/> * In the DataService framework it is called by the ItsBaseServlet.contextInitialized()
<br/> * @param appName
<br/> */
<br/> public static void initDs(String appName) {
<br/> // dbParamString in web.xml: dbName, userName, psw, dsName, sqlLocation, hostname, dbPort, dbType-->
<br/> String dbParamString = Stats.getAppDetailsByKeyName(appName, "dbParamString");
<br/> String[] dbParams = Stringer.split(",", dbParamString);
<br/> String dbName = dbParams[0].trim();
<br/> String userName = dbParams[1].trim();
<br/> String psw = dbParams[2].trim();
<br/> String originDsName = dbParams[3].trim(); // for example, "sampleDB"
<br/> String dsName = appName +"/" + originDsName; // for example, "BASE/sampleDB"
<br/> String pathToDeploy = Stats.getAppDetailsByKeyName(appName, "pathToDeploy");
<br/> String appLocationName = Stats.getAppDetailsByKeyName(appName, "appLocationName");
<br/> String sqlLocation = pathToDeploy + appLocationName + "/" + DataService.defaultSqlLocation; // config/sql
<br/> if(dbParams.length > 4) {
<br/> sqlLocation = pathToDeploy + appLocationName + "/" + dbParams[4].trim();
<br/> }
<br/> String hostname = "localhost"; // default hostname
<br/> if(dbParams.length > 5) {
<br/> hostname = dbParams[5].trim();
<br/> }
<br/> String dbPort = DataService.defaultMySqlPort; // default port for mysql
<br/> if(dbParams.length > 6) {
<br/> dbPort = dbParams[6].trim();
<br/> }
<br/> String dbType = DataService.defaultDbType; // "mysql"
<br/> String dbDriverName = DataService.mySqlDriverName;
<br/> String dbURL = DataService.defaultMySqlConURL;
<br/> if(dbParams.length > 7) {
<br/> // use dbType to get dbURL and dbDriver
<br/> dbType = dbParams[7].trim().toLowerCase(); // microsoft,postgresql,mysql,oracle (db2?)
<br/> int indexOfDbType = DataService.listOfDbConnectComponents.indexOf(dbType);
<br/> if(indexOfDbType >= 0) {
<br/> dbDriverName = DataService.listOfDbConnectComponents.get(indexOfDbType + 1);
<br/> dbURL = DataService.listOfDbConnectComponents.get(indexOfDbType + 2);
<br/> }
<br/> }
<br/> createConnectionPool(dbURL, dbName, userName, psw, dbDriverName, dsName);
<br/> // source is not provided, consists of simple IOMaster.readTextFile() in the loop
<br/> readAndStoreSql(appName, sqlLocation); // read SQL files and cache in the Hashtable
<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/> // dataSources - a Hashtable with dsName as a key and DataSource object populated in the createConnectionPool()
<br/> // get a connection from the pool
<br/> return ds.getConnection();
<br/> }
<br/> /**
<br/> * createConnectionPool() uses db params from web.xml
<br/> * @param dbURL
<br/> * @param dbName
<br/> * @param username
<br/> * @param password
<br/> * @param driverName
<br/> * @param dsName
<br/> * @return dbURL
<br/> */
<br/> public static String createConnectionPool(String dbURL, String dbName, String username, String password,
<br/> String driverName, String dsName) {
<br/> if(driverName.equals(msSqlDriverName)) {
<br/> /** Uncomment to work with MS SQL Server (0), Postgresql (1), or MySql (2) DB ....
<br/> * if this application needs more DB types, provide proper jars in the library and add similar code
<br/>
<br/> com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource msds = new com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource();
<br/>
<br/> msds.setURL(connectionUrl);
<br/> msds.setUser(username);
<br/> msds.setPassword(password);
<br/> setDataSource(dsName, msds);
<br/> return;
<br/> ] else if(driverName.equals(postgreSqlDriverName)) {
<br/> org.postgresql.ds.PGSimpleDataSource pds = new org.postgresql.ds.PGSimpleDataSource();
<br/> pds.setDatabaseName(dbName);
<br/> pds.setServerName(hostname);
<br/> pds.setPortNumber(Integer.parseInt(dbPort));
<br/> pds.setUser(username);
<br/> pds.setPassword(password);
<br/> setDataSource(dsName, pds);
<br/> return;
<br/> //optional for mysql, remove if mysql.jar is not in the lib
<br/> */
<br/> } else if(driverName.equals(mySqlDriverName)) {
<br/> MysqlDataSource pds = new MysqlDataSource();
<br/> pds.setURL(dbURL);
<br/> pds.setUser(username);
<br/> pds.setPassword(password);
<br/> dataSources.put(dsName, pds);
<br/> return dbURL;
<br/> } else if(driverName.equals(oracleDriverName)) {
<br/> try {
<br/> OracleDataSource opds = new OracleDataSource();
<br/> opds.setURL(dbURL);
<br/> opds.setUser(username);
<br/> opds.setPassword(password);
<br/> dataSources.put(dsName, opds);
<br/> } catch(Exception e) {
<br/> System.out.println("ERROR: "+e.getMessage());
<br/> }
<br/> }
<br/> return dbURL;
<br/> }
<br/>}
<br/>
Was it clear so far?
onclick="window.location.href='/BASE/jsp/demo.jsp?checkFlavor=itsp&issueID=474&intro=general&group=aitu&ur=f'">
Assignments: 1. Read the source and expand the description of each method in the Discussion section
2. Add suggestions and questions to the source there.
3. Provide 2 QnA related to this lesson.
