WebSphere Liberty :- Simple Servlet ( DB )

 

Preparation

WebSphere / Liberty

Java Database Connectivity ( JDBC )

Download

Download JDBC Driver from your Database Software provider.

For us Microsoft, and we get the JDBC Driver from here :-

Docs / SQL / Connect your client to SQL / JDBC
Download Microsoft JDBC Driver for SQL Server
Link

Configuration

Server.xml

Data Source

Outline
  1. Data Source
    • jndi
      • Unique name within each application
      • Will be referred to by the Application
      • Sample :- jdbc/inform
    • jdbcDriver
      • libraryRef
        • Sample :- MSJDBCLib
    • properties.microsoft.sqlserver
      • serverName=”localhost”
      • portNumber=”1433″
      • databaseName=”StackOverflow2010″
      • user=”stackoverflow”
      • password=”password”
  2. Library
    • id
      • Sample :- MSJDBCLib
      • file name :- C:\Downloads\Microsoft\Java\jdbc\v6.0.8112.200\extract\sqljdbc_6.0\enu\jre8\sqljdbc42.jar
Sample
Image

serverDotxml_20180723_0819AM

 

Code

	<dataSource
		id="jdbc/inform"
		jndiName="jdbc/inform"
	>
		<jdbcDriver
			libraryRef="MSJDBCLib"
		/>

		<properties.microsoft.sqlserver
			serverName="localhost"
			portNumber="1433"
			databaseName="StackOverflow2010"
			user="stackoverflow"
			password="hIy8jA2lNl"
		/>

	</dataSource>

	<library id="MSJDBCLib">
		<file
			name="C:\Downloads\Microsoft\Java\jdbc\v6.0.8112.200\extract\sqljdbc_6.0\enu\jre8\sqljdbc42.jar"
		/>
	</library>

Code

Outline

  1. table.java
    • java source code
  2. compileDBInform.cmd
    • Command file for compiling Java Source Code
  3. web.xml
    • web.xml file
  4.  packageDBInform.cmd
    • Package java class files into War file
  5. deployDBInform.cmd
    • Copyy War file unto WebSphere Server dropins folder

Source Code

table.java


package dbInform;

import java.io.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.annotation.Resource;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.WebServlet;

import javax.sql.DataSource;

import javax.naming.*;

public class table
    extends HttpServlet
{

    private static final long serialVersionUID = 1L;

    private DataSource objDS;
    private Connection objDBConn = null;

    private static final String DB = "jdbc/inform";  

    private static final String QUERY_TABLE
        = "select * "
            + " from INFORMATION_SCHEMA.TABLES tblIST "
            + " order by TABLE_SCHEMA, TABLE_NAME"
            ;

    private String strData;
    private String strRow;

    private String strBGColor = "";
    private int    iRowNumber = 0;

    private String TR_COLUMN_EVEN = " bgcolor = '#AF601A'";
    private String TR_COLUMN_ODD = " bgcolor = '#1D8348'";

    private String strTableSchema;
    private String strTableName;
    private String strTableType; 

    public table()
    {
        super();
    }
    public void doGet
    (
          HttpServletRequest request
        , HttpServletResponse response
    )
        throws
               ServletException
             , IOException
    {

        response.setContentType("text/html");

        PrintWriter out = response.getWriter();

        out.println("
<H1>Inform.table</H1>

\n");

        try
        {

            Context objCTX = new InitialContext();

            Object obj = null;

            obj = objCTX.lookup
                    (
                        DB
                    );

            objDS = (DataSource) obj;

            objDBConn = objDS.getConnection();

            Statement stmt = null;

            stmt = objDBConn.createStatement();

            ResultSet rs = stmt.executeQuery
                            (
                                QUERY_TABLE
                            );

            out.println("
<TABLE BORDER="1" WIDTH='80%'>");    

            strData =
                          "
<TH>" + "SCHEMA" + "</TH>

"
                        + "
<TH>" + "TABLE" + "</TH>

"
                        + "
<TH>" + "TYPE" + "</TH>

"
                  ;

        strRow = "
<TR>" + strData + "</TH>

";

        out.println(strRow);

       while (rs.next())
       {

            iRowNumber = iRowNumber + 1;

            if ((iRowNumber %2) == 0)
            {
                strBGColor = TR_COLUMN_EVEN;
            }
            else
            {
                strBGColor = TR_COLUMN_ODD;
            }

            strTableSchema = rs.getString("TABLE_SCHEMA");
            strTableName = rs.getString("TABLE_NAME");
            strTableType = rs.getString("TABLE_TYPE");

            strData =
                        "
<TD>" + strTableSchema + "</TD>

"
                      + "
<TD>" + strTableName + "</TD>

"
                      + "
<TD>" + strTableType + "</TD>

"

                      ;

            strRow = "<TR>"
                        + strData
                        + "</TR>

";

            out.println(strRow);

        }

       out.println("</TABLE>

"); 

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

       if (objDBConn != null)
       {
            try
            {
                objDBConn.close();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            } 

           }
       }
    }
}

Compile Java Source Code

compileDBInform.cmd


setlocal

set "_folderCompiler=C:\Program Files\Java\jdk-10.0.2\bin"

set "_relVer=8"

set "_relTag= --release %_relVer%"

set "_folderJar=C:\IBM\WebSphere\Liberty\v18.0.0.2\extract\\wlp\dev\api\spec"

set "_fileServlet=com.ibm.websphere.javaee.servlet.3.1_1.0.21.jar"

set "_fileAnnotation=com.ibm.websphere.javaee.annotation.1.3_1.0.21.jar"

set "_folderDeploy=C:\IBM\WebSphere\Liberty\v18.0.0.2\extract\wlp\usr\servers\defaultServer\dropins"

set "_targetFolder=WEB-INF\classes"

"%_folderCompiler%\javac" -cp %_folderJar%\%_fileServlet%;%_folderJar%\%_fileAnnotation%  %_relTag% -d %_targetFolder% table.java

endlocal

Configuration Files

xml.web

Image

webDotxml_20180723_0813AM

Code


<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
    version="2.4">

    <display-name>
        DBInform
    </display-name>

    <description>
    Simple Application that shows database schema via INFORMATION_SCHEMA.
    </description>

    <!--
        dbInformTable
            dbInform.table
    -->
    <servlet>
        <servlet-name>table</servlet-name>
        <servlet-class>dbInform.table</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>table</servlet-name>
        <url-pattern>/table</url-pattern>
    </servlet-mapping>

</web-app>

Package

packageDBInform.cmd


setlocal

set "_folderCompiler=C:\Program Files\Java\jdk-10.0.2\bin"
set "_targetFolder=WEB-INF\classes\"

"%_folderCompiler%\jar" cvf dbInform.war WEB-INF\web.xml %_targetFolder%\dbInform\table.class

endlocal

Deploy WAR File

deployDBInform.cmd


setlocal

set "_folderBase=C:\IBM\WebSphere\Liberty\v18.0.0.2\extract\wlp\usr\servers\defaultServer\dropins"

echo F | xcopy dbInform.war %_folderBase%\dbInform.war /d /Y /Q 

endlocal

Browser

Launched browser and pointed it at ( https://localhost:9443/dbInform/table )

and we got a list of the tables in our database.

dbInform_table_20180722_1054PM

Source Code Control

Github

DanielAdeniji/javaDBInform
link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s