Database connectivity with JSF application

Database connectivity with JSF application:(DB2)

The tables required need to be present in a schema in the database.
The database maybe on local or remote server.

(I) Through JDBC connection:

In the java class(can be a bean), we need to insert the following code in the function, which when called needs to connect to the database for data.

try
{
Class.forName("com.ibm.db2.jcc.DB2Driver");
Connection conn = DriverManager.getConnection( "jdbc:db2://:port_no>/database_name>","","");

Statement stmt = conn.createStatement();
ResultSet results = stmt.executeQuery("");
while (results.next())
{
results.getString("")
results.getString("")
results.getString("")
.
.
// can write any logic using data in resultset


}

results.close();
stmt.close();
conn.close();

}
catch (SQLException sqlExcept) {
sqlExcept.printStackTrace();
}catch (ClassNotFoundException e){
e.printStackTrace();
}


(II)Through Websphere Application Server (WAS) database connection (JNDI)

[A] DATABASE CONFIGURATION WITH WAS:

1. Start WebSphere and launch the Admin Console.

2. Create a new J2C Authentication Alias (Expand Security --> JAAS Configuration --> J2C Authentication Data) to specify the userid and password to be used to connect to the DB2 for OS/390 database.

3. Edit the DB2UNIVERSAL_JDBC_DRIVER_PATH environment variable (Expand Environment --> Manage WebSphere Variables) to specify the fully qualified path for the directory that contains the db2jcc.jar, db2jcc_license_cisuz.jar, and db2jcc_license_cu.jar files.
(*********These files should be present on the server machine even if the database is on some other machine)

4. Create a new JDBC Provider (Expand Resources --> JDBC Providers, and click New). Select DB2 Universal JDBC Driver Provider from the drop-down list and accept the defaults on the next page.

5. Save the configuration and navigate back to the new JDBC Provider. Select the new JDBC Provider and then select Data Sources under Additional Properties. A new data source is created. Enter a Name, JNDI Name, Component-managed Authentication Alias, and Container-managed Authentication Alias. Select the check box for Container Managed Persistence if the data source is to be used for CMP entity beans. Click OK and then select Custom Properties under Additional Properties in the data source configuration. The following properties are required:

databaseName
enableSQLJ true(default value)
portNumber (50000)
serverName

6. Save the configuration, and click the Test Connection button in the data source configuration to see if the data source is now set up successfully to connect to the DB2 for OS/390 database.

[B] CODE IN THE JAVA CLASS

In the java class(can be a bean), we need to insert the following code in the function, which when called needs to connect to the database for data.

try {
Context ctx=new InitialContext();
DataSource ds=(DataSource)ctx.lookup("");
Connection conn=ds.getConnection();

Statement stmt = conn.createStatement();
ResultSet results = stmt.executeQuery("");
while (results.next())
{
results.getString("")
results.getString("")
results.getString("")
.
.
// can write any logic using data in resultset


}

results.close();
stmt.close();
conn.close();

}
catch (SQLException sqlExcept) {
sqlExcept.printStackTrace();
}catch (NamingException e){
e.printStackTrace();
}