Unit – 3

Database Connectivity

3.1 Database programming using JDBC

Despite its many advantages, Java became well-known and highly accepted in the industry due to its platform independence. The acceptability of Java will not be complete until the corporate community endorses it. Every enterprise (business) program must connect to a database, either to retrieve data to be processed or to store data that has been processed. Through JDBC, Java provides a clean and straightforward solution to database connectivity, allowing a Java program to connect to almost any database. As a result, businesses embraced Java, and the rest is history.

What is JDBC?

Sun's trademark name for JDBC is JDBC. Java Database Connectivity is not an acronym. JDBC is a programming interface. We can create java programs that communicate with databases using this API. Two packages provide support for the JDBC API in Java applications.

1. Java.sql
2. Javax.sql

The java.sql package contains a set of basic interfaces and classes for communicating with databases in Java. For advanced Java-database operations, the extension package is utilized.

Java Architecture

●      Java Application - It might be a standalone Java program, a servlet, a jsp, a Java bean, or an EJB that connects to the database and performs database operations using the JDBC API.

●      JDBC API - It is a set of classes and interfaces that are used to perform database operations in a Java program. The library support is provided by the java.sql and javax.sql packages.

●      DriverManger - To connect to the database, a Java program uses DriverManager.

●      Driver - It is the software that establishes the database connection. The JDBC method calls are translated by the translation program. This software allows a Java program to communicate with a database.

●      Database - The database stores all of the company's information.

Fig 1: Java architecture

Standard steps to connect to the database from a Java program

1. Loading the driver
2. Preparing the connection string
3. Requesting for the connection
4. After performing database operations closing the connection.

Loading the driver

The driver is an important component of the Java-Database communication. A JDBC driver is a type of object. We must programmatically load this Java class into memory. There are so many different types of drivers. Initially, we use a driver developed by Sun Microsystems. “sun.jdbc.odbc.JdbcOdbcDriver” is the fully qualified name of that driver class. The following static method is used to dynamically load the class.

Preparing the connection string

For connectivity purposes, we must provide the database URL. The connection string is the name given to this URL. When utilizing the Sun driver, the connection string will look like this.

String cs=”jdbc:odbc:dsn”;

We must configure the Data Source Name (DSN) from the outside. After configuring, we give the configuration a name. We utilize that name in the connection string.

Requesting for connection:

To obtain database connections, the Java program utilizes the DriverManager's function.

Connection con=DriverManager.getConnection(cs,”username”,”password”);

The driver's connection procedure is started by DriverManager. The driver establishes a connection and passes it on to the DriverManager. It then passes the information to the Java program.

Closing the connection:

We shut the database connection after conducting database operations (insertion, deletion, update, or selection).

Example:

/*

  Source code: -ConnectToOracle.java

*/

Import java.sql.*;

Class ConnectToOracle

{

  public static void main(String args[]) throws Exception

{

          String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;

          String cs=”jdbc:odbc:student”;

          String user=”scott”;

          String pwd=”tiger”;

          Class.forName(driver);

          System.out.println(“Driver loaded”);

          Connection con=DriverManager.getConnection(cs,user,pwd);

          System.out.println(“Connected to the oracle database”);

          con.close();

}//main()

}//class

If the above program is successfully executed, we get the following output.

  Driver loaded

  Connected to the oracle database

Key takeaway

1. Through JDBC, Java provides a clean and straightforward solution to database connectivity, allowing a Java program to connect to almost any database. As a result, businesses embraced Java, and the rest is history.
2. JDBC is a programming interface.
3. We can create java programs that communicate with databases using this API.

3.2 Studying Javax.sql.*package

JDBC extended API is another name for this package. It provides classes and an interface for accessing data on the server.

Important classes and interface of javax.sql package

3.3 Accessing a database from a JSP page

Almost all online apps connect with a variety of databases. Databases are used to store a variety of different types of data for a variety of purposes. Different sorts of databases are being utilized in the industry nowadays. However, it is critical to note that relational databases are by far the most popular.

In most cases, a relational database employs tables to represent the data it manages. A table is made up of rows and columns, with each column containing a single value of a predefined data type. Rows are identified by record, while columns are identified by field name.

Text data, numeric data, dates, and binary data, such as images and sound, are all examples of data kinds that can be used to meet our needs. To access the data, a special language called Structured Query Language (SQL) is utilized. All of the major database vendors maintain the Structured Query Language, which is an ANSI (American National Standards Institute) standard.

Relational database engines exist in a variety of sizes and designs. The API (Application Programming Language) for Structured Query Language is used to run structured queries in various database engines. The Java Database Connectivity (JDBC) API is an API (Application Programming Language) included in the standard Java libraries.

Accessing a database from a JSP Page

To improve simple database-driven Java Server Page applications, Java Server Pages contains a Standard Tag Library that includes a number of operations for database access.

These acts are mostly utilized to deliver the following benefits:

●      For greater performance and scalability, use a connection pool.

●      The features are designed to help with queries, updates, and insertion.

●      The most common data-type conversions are supported.

●      To support a number of different databases.

Example of Database program using JSP

Three sample examples, registerinsert.jsp, updateprogram.jsp, and registrationsearch.html, will be discussed today. These three applications are used to put data into a database, update data in a database, and search data in a database.

We also used an MS Access database to set the DSN name "sgc" in this case.

<%@page contentType="text/html"%>

<%@page pageEncoding="UTF-8"%>

<%@page import="java.sql.*"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01    Transitional//EN” "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html;       charset=UTF-8">

<title>Example of Java Server Page with JDBC</title>

</head>

<body>

<%

String u=request.getParameter("userid");
String p=request.getParameter("password");

String n=request.getParameter("sname");

String e=request.getParameter("eid"); String a=request.getParameter("addr");

Try

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:sgc","",""); /* Passing argument through the question mark */

PreparedStatement ps=con.prepareStatement("insert into login values(?,?,?,?,?)") ;

Ps.setString(1,u); ps.setString(2,p);

Ps.setString(3,n);

Ps.setString(4,a);

Ps.setString(5,e);

Int i=ps.executeUpdate();    /*Set the Update query command */ if(i!=0)

{

Response.sendRedirect("index.jsp?msg=Thank You for registering with us in Mrbool !");

}

Else

{

Response.sendRedirect("registerinsert.jsp?msg=Insertion Failed!! Please try again!!! ");

}

Con.close();

}

Catch(Exception ex)

{

Out.println(ex);

}

%>

</body>

</html>

Key takeaway

1. Databases are used to store a variety of different types of data for a variety of purposes.
2. To access the data, a special language called Structured Query Language (SQL) is utilized.
3. All of the major database vendors maintain the Structured Query Language, which is an ANSI (American National Standards Institute) standard.

3.4 Application - specific Database Action

Without being a Java programmer, you may leverage the database activities to create a variety of fascinating web apps, such as product catalog interfaces, personnel directories, or online billboards. These programs make up a significant portion of the web applications created nowadays.

However, inserting SQL statements directly in web pages might become a maintenance issue at a certain degree of sophistication. Business logic is represented by SQL statements, and it is better written as separate Java classes for more complicated systems.

Example

<%@ page language="java" contentType="text/html" %>

<%@ taglib uri="/orataglib" prefix="ora" %><ora:useDataSource id="example"

  className="sun.jdbc.odbc.JdbcOdbcDriver"

  url="jdbc:odbc:example" />

<%--

  See if the employee is already defined. If not, insert the

  info, else update it.

--%>

<ora:sqlQuery id="empDbInfo" dataSource="example">

  SELECT * FROM Employee

    WHERE UserName = ?

  <ora:sqlStringValue param="userName" />

</ora:sqlQuery>

<% if (empDbInfo.size( ) == 0) { %>

     <ora:sqlUpdate dataSource="example">

       INSERT INTO Employee

         (UserName, Password, FirstName, LastName, Dept,

           EmpDate, EmailAddr, ModDate)

         VALUES(?, ?, ?, ?, ?, ?, ?, ?)

       <ora:sqlStringValue param="userName" />

       <ora:sqlStringValue param="password" />

       <ora:sqlStringValue param="firstName" />

       <ora:sqlStringValue param="lastName" />

       <ora:sqlStringValue param="dept" />

       <ora:sqlDateValue param="empDate" pattern="yyyy-MM-dd" />

       <ora:sqlStringValue param="emailAddr" />

       <ora:sqlTimestampValue value="<%= new java.util.Date( ) %>" />

     </ora:sqlUpdate>

<% } else { %>

     <ora:sqlUpdate dataSource="example">

       UPDATE Employee

         SET Password = ?,

             FirstName = ?,

             LastName = ?,

             Dept = ?,

             EmpDate = ?,

             EmailAddr = ?,

             ModDate = ?

         WHERE UserName = ?

       <ora:sqlStringValue param="password" />

       <ora:sqlStringValue param="firstName" />

       <ora:sqlStringValue param="lastName" />

       <ora:sqlStringValue param="dept" />

       <ora:sqlDateValue param="empDate" pattern="yyyy-MM-dd" />

     <ora:sqlStringValue param="emailAddr" />

       <ora:sqlTimestampValue value="<%= new java.util.Date( ) %>" />

       <ora:sqlStringValue param="userName" />

     </ora:sqlUpdate>

<% } %>

<%-- Get the new or updated data from the database --%>

<ora:sqlQuery id="newEmpDbInfo" dataSource="example" scope="session">

  SELECT * FROM Employee

    WHERE UserName = ?

  <ora:sqlStringValue param="userName" />

</ora:sqlQuery>

<%-- Redirect to the confirmation page --%>

<ora:redirect page="confirmation.jsp" />

As a result, instead of using generic database actions, application-specific custom actions may be preferable for a complicated application. For instance, all of the generic database activities in the preceding Example, which SELECT and then INSERT or UPDATE the database, can be replaced with a single application-specific activity, such as this:

<myLib:saveEmployeeInfo dataSource="example" />

The beauty of employing an application-specific custom action is that it allows you to evolve the application behind the scenes, in addition to making it easier for the page designer to deal with. Initially, this action can be set up to utilize JDBC to directly access the database, just like the generic actions. However, it may be necessary to transition the program to an Enterprise JavaBeans architecture at some point in the future, potentially to accommodate clients other than web browsers.

Instead of directly contacting the database, the action can be adjusted to interact with an Enterprise JavaBeans component. It makes no difference to the JSP page developer because the custom action is still used in the same way.

Another option is to do all database processing in a servlet and use JSP pages simply to display the results.

Key takeaway

1. Without being a Java programmer, you may leverage the database activities to create a variety of fascinating web apps, such as product catalog interfaces, personnel directories, or online billboards.
2. Inserting SQL statements directly in web pages might become a maintenance issue at a certain degree of sophistication.

3.5 Developing Java Beans in a JSP page

We'll illustrate how to use a Bean in a JSP page using this example. JavaServer Pages (JSP) is a server-side programming language that allows developers to create dynamic, platform-independent Web applications. JSPs have access to the whole Java API family, including the JDBC API, which allows them to connect to business databases. To summarize, in order to use a Bean in a JSP page, you must:

●      To begin, create a Java Bean. The Java Bean is a specially built Java class with a default, no-argument constructor, Serializable interface implementation, and getter and setter methods for its properties.

●      Using the percent code fragment percent > scriptlet, create a jsp page. It can contain any number of JAVA language statements, variable or method declarations, or valid page scripting language expressions.

●      Declare the JavaBean for use in the JSP page with the useBean action. The bean becomes a scripting variable once it is declared, and it may be accessed by both scripting elements and other custom tags in the JSP.

●      To access the bean's get methods, use the getProperty action, and to access the bean's set methods, use the setProperty action.

Look at the following code snippets for a sample Bean and a JSP page that uses it:

Package com.javacodegeeks.snippets.enterprise;

Import java.util.Date;

Public class SampleBean {

    private String param1;

    private Date param2 = new Date();

    public String getParam1() {

        return param1;

    }

    public void setParam1(String param1) {

        this.param1 = param1;

    }

    public Date getParam2() {

        return param2;

    }

    public void setParam2(Date param2) {

        this.param2 = param2;

    }

    @Override

    public String toString() {

        return "SampleBean [param1=" + param1 + ", param2=" + param2 + "]";

    }

}

UseBean.jsp

<%@ page language="java" contentType="text/html;charset=UTF-8" %>

<%@ page import="com.javacodegeeks.snippets.enterprise.SampleBean"%>

<html>

<head>

    <title>Java Code Geeks Snippets - Use a Bean in JSP Page</title>

</head>

<body>

    <jsp:useBean id="sampleBean" class="com.javacodegeeks.snippets.enterprise.SampleBean" scope="session">

        <%-- intialize bean properties --%>

        <jsp:setProperty name="sampleBean" property="param1" value="value1" />

    </jsp:useBean>

    Sample Bean: <%= sampleBean %>

    param1: <jsp:getProperty name="sampleBean" property="param1" />

    param2: <jsp:getProperty name="sampleBean" property="param2" />

</body>

Output

Sample Bean: SampleBean [param1=value1, param2=Thu Nov 17 21:28:03 EET 2011]

Param1: value1 param2: Thu Nov 17 21:28:03 EET 2011

JavaBeans as JSP Component

JavaBeans are just standard Java classes that follow a set of criteria. Development tools can figure out how the bean is intended to be used and how it can be related to other beans by following these rules. Beans are defined by the JavaBeans specification as classes that:

●      Introspection should be supported so that a builder tool may analyze how a bean function.

●      Support customization so that a user can alter the appearance and behavior of a bean when using an application builder.

●      Support events as a simple communication metaphor for notifying beans of noteworthy events.

●      Property support, both for tool customization and for programmatic use.

●      Support persistence, such that a bean can be altered in an application builder, then saved and reloaded later.

Key takeaway

1.  JavaServer Pages (JSP) is a server-side programming language that allows developers to create dynamic, platform-independent Web applications.
2. JSPs have access to the whole Java API family, including the JDBC API, which allows them to connect to business databases.
3. Declare the JavaBean for use in the JSP page with the useBean action.
4. The bean becomes a scripting variable once it is declared, and it may be accessed by both scripting elements and other custom tags in the JSP.

3.6 Introduction to Struts framework

Struts is a framework that implements the MVC architecture using a set of Java technologies such as Servlet, JSP, JSTL, and XML. The framework also includes a validation mechanism that is ready to use. Struts' strength resides in its model layer, which allows it to work with other Java technologies such as JDBC, EJB, Spring, Hibernate, and others.

The composite view in struts maintains the layout of its subviews and can apply a template, making a consistent appearance and feel throughout the entire application easy to build and customize. A composite view is constructed up of various reusable sub views, so that any changes made to one are automatically updated in all composite views.

Struts is made up of a collection of bespoke tag libraries. Struts is built on a pattern-oriented MVC framework that contains JSP custom tag libraries. Utility classes are also supported by Struts.

The Apache Software Foundation created Struts, an open source framework. It is available as a free source on the Apache website. Struts are divided into two categories: Struts 1.X and Struts 2.X. Despite the fact that Struts 2.X is the successor to Struts 1.X, there is a significant difference between the two. WebWorks2 is the framework that Struts 2.x is based on. Because data that has to be displayed to the user can be taken from the Action, Struts 2.x is also known as a pull-MVC architecture (a class which contains business logic).

Features of struts

The following are some of the features of Struts:

●      Because Struts is built on “time-proven” design patterns, it fosters sound design techniques and modeling.

●      Struts is almost simplistic, making it straightforward to understand and operate.

●      It has a number of useful features, like input validation and internationalization.

●      It removes a lot of the complexity by allowing you to utilize struts instead of creating your own MVC framework.

●      Struts and J2EE are extremely nicely integrated.

●      Struts has a huge user base.

●      Because it is flexible and extendable, existing web applications can easily adapt to the struts framework.

●      The tag libraries provided by Struts are excellent.

●      It allows you to save data from input forms into Action forms, which are Javabean objects.

●      It also delegated standard error handling, both declaratively and programmatically.

Key takeaway

1. Struts is a framework that implements the MVC architecture using a set of Java technologies such as Servlet, JSP, JSTL, and XML.
2. Struts is built on a pattern-oriented MVC framework that contains JSP custom tag libraries.
3. Utility classes are also supported by Struts.

References:

1. Elliotte Rusty Harold, “Java Network Programming”, O’Reilly publisher.
2. Patrick Naughton, “COMPLETE REFERENCE: JAVA2”, Tata McGraw - Hill.
3. Web references: http://java.sun.com.
4. Https://examples.javacodegeeks.com/
5. Https://www.geeksforgeeks.org/
6. Https://www.tutorialspoint.com