Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Java (http://www.go4expert.com/articles/java-tutorials/)
-   -   JDBC Basics - Part II (http://www.go4expert.com/articles/jdbc-basics-part-ii-t21255/)

techgeek.in 9Mar2010 00:08

JDBC Basics - Part II
 
JDBC Basics - Part I

DSN



In the first article we have discussed about Type 1 JDBC driver. In this article we will discuss about the practical configurations essential for the successful execution of the JDBC program. According to the previous article "sun.jdbc.odbc.JdbcOdbcDriver" is the driver class name of the JDBC-ODBC driver. This driver is included in the JDK run time environment.Therefore we need not specify any JAR file in the classpath. To load this driver we need to call Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"). To test JDBC-ODBC Bridge we need to create a DSN (Data Source Name) for a flat file, and then use this DSN in the JDBC connection URL.

Following are the steps to create a DSN:
  1. Run Control Panel > Administrative Tools > Data Sources (ODBC).
  2. Click the System DSN tab. Then click the Add button.
  3. From the ODBC driver list, select Microsoft Access Driver (*.mdb) and click the Finish button.

    http://www.go4expert.com/images/arti...asics/pic1.jpg
  4. Now fill in the DSN header information as below:

    Data Source Name: harsh
    Description: DSN for harsh.mdb
  5. Then click on the select button and select the database to be used.

    Select database: harsh.mdb

    http://www.go4expert.com/images/arti...asics/pic2.jpg

    Then click Ok. DSN harsh is ready.
After the DSN is created then we will try to connect a java program to this DSN with JDBC-ODBC Bridge using the DriverManager.getConnection() method:
Code:

  DriverManager.getConnection("jdbc:odbc:dsn_name");
Here is the sample program showing the above concept:
Code:

import java.sql.*;
import java.io.*;
class OdbcDsnConnection
{
    public static void main(String args[])
    {
        Connection con;
        Statement stmt;
        ResultSet rs;
        String str;
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            con=DriverManager.getConnection("jdbc:odbc:harsh");
            System.out.println("Connection successful");
            stmt=con.createStatement();
            rs=stmt.executeQuery("select * from student");
            while(rs.next())
            {
                str=rs.getString(1);
                System.out.println(str);

            }
        }
        catch(Exception ex)
        {}
    }
}

Code:


The output:
  C:\>javac OdbcDsnConnection.java
 
  C:\>java OdbcDsnConnection
  DSN Connection ok.

It is very common that the DSN you entered in the connection URL is not defined or defined incorrectly. In this case, the JDBC-ODBC Bridge driver will raise an exception on the DriverManager.getConnection() method. Here is an example showing this:

Code:

import java.sql.*;
import java.io.*;
class  OdbcConnectionWrongDsn
{
    public static void main(String args[])
    {
        Connection con;
        Statement stmt;
        ResultSet rs;
        String str;
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            con=DriverManager.getConnection("jdbc:odbc:hasrh");
            System.out.println("Connection successful");
            stmt=con.createStatement();
            rs=stmt.executeQuery("select * from student");
            while(rs.next())
            {
                str=rs.getString(1);
                System.out.println(str);

            }
        }
        catch(Exception ex)
        {}
    }
}

On executing this program, the Microsoft ODBC Driver Manager will return an error through the JDBC-ODBC Bridge driver:
Code:

   
  C:\>javac OdbcConnectionWrongDsn.java
 
  C:\>java OdbcConnectionWrongDsn
  Exception: [Microsoft][ODBC Driver Manager]
  Data source name not found and no default driver specified

Use Of JDBC In JSP



Login Page (login.html):-

Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
        <title>Login Page</title>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body >
        <form name="form1" action="next.jsp">
                USERNAME  :    <input type="text" name="Username" value="" size="20" /><br><br><br>
                PASSWORD  :    <input type="password" name="Password" value="" size="20" /><br>
                <br>
                <input type="submit" value="Login" name="submit" />

        </form>
</body>
</html>

This is a login page . The user needs to enter username and password to go to the next page. Here HTML tags are used like <form></form> tag where two input text fields and a submit button are inserted. When the user enters its username and password and clicks on the submit button he/she is directed to the next JSP page named “next.jsp” using get method.

http://www.go4expert.com/images/arti...asics/pic3.jpg

Checking Login Credentials And Authenticate (Database Access For Login):-

Code:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!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>JSP Next Page</title>
</head>
<body>
        <%@page import="java.sql.*" %>
        <%!String uname;
        String passwd;
        Connection conn;
        PreparedStatement ps;
        ResultSet rs;
        uname=request.getParameter("Username");
        passwd=request.getParameter("Password");
                try
                {
                        Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
                        conn = DriverManager.getConnection ("jdbc:odbc:harsh");
                        System.out.println ("Connected....");
                        ps = conn.prepareStatement ("select * from admin where username = ? and password = ?");
                        ps.setString (1, uname);
                        ps.setString (2, passwd);
                        rs = ps.executeQuery ();

                        if(rs.next())
                        {
        %>
                                <jsp:forward page="success.jsp"/>

        <% 
                                //out.println(rs.getString(2));
                        }
                        else
                        {
        %>
                                <jsp:forward page="error.jsp"/>

        <%
                        }
                }
                catch (Exception e)
                {
                        System.out.println (e);
                }
        %>
</body>
</html>

In this page the username and password entered in the previous page is retrieved using the appropriate method:
Var=request.getParameter(name_of_input_field);
Then the sun.jdbc.odbc.JdbcOdbcDriver is loaded and connected with this program through the DSN “harsh” set up. Then the preparedStatement is used to retrieve the username and password from the table in the database. preparedStatement is used as we pass dynamic values in the query at runtime. If there is a user then he/she is redirected to another JSP page “success.jsp” else to a error page “error.jsp”.


Form For Insert/Update/Delete From Student Database (success.jsp):-

Code:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!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>JSP Page</title>
</head>
<body>
        <h2>Successfully logged in</h2>
        <br>
        <h4>Enter Student Records</h4>   
        <form action="insert.jsp" name="f2">
                NAME  :    <input type="text" name="name" value="" size="20" /><br><br><br>
                ROLL  :    <input type="TEXT" name="roll" value="" size="20" /><br>
                <input type="submit" value="INSERT" name="Insert" />
        </form>
        <form action="update.jsp">
                OLD ROLL  :    <input type="TEXT" name="roll" value="" size="20" /><br><BR>
                NEW NAME  :    <input type="text" name="name" value="" size="20" /><br><br><br>
                <input type="submit" value="UPDATE" name="UPDATE" />
        </form>
        <form name="f3" action="delete.jsp">
                ROLL  :    <input type="TEXT" name="roll" value="" size="20" /><br>
                <input type="submit" value="Delete" name="Delete" />
        </form>
</body>
</html>

This is a form where we can perform insertion/updation/deletion of a student record in the student database as per our need.

Insert Into The Student Database (Insert.jsp):-
Code:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!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>JSP Page</title>
</head>
<body>
        <%@ page import="java.sql.*" %>
        <%!
        Connection conn;
        PreparedStatement ps;
        ResultSet rs;
        String name=request.getParameter("name");
        int roll=Integer.parseInt(request.getParameter("roll"));
        try
        {
                Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
                conn = DriverManager.getConnection ("jdbc:odbc:harsh");
                System.out.println ("Connected....");
                ps=conn.prepareStatement("insert into Student values(?,?)");
                ps.setString(1,name);
                ps.setInt(2,roll);
                if(ps.executeUpdate() ==0)
                {
%>
                        <jsp:forward page="errror.jsp"></jsp:forward>
<%
                }
        }
        catch(Exception ex)
        {}
        %>
        <h2>Successfully Inserted</h2>
</body>
</html>

In this page the actual insertion of the details of the record entered in the previous page in the backend database is done. First the details are retrieved from the previous page. Then the same is inserted using prepareStatement as dynamic values are entered at runtime which changes as per the user.

Update Student Database (update.jsp) :-

Code:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!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>JSP Page</title>
</head>
<body>
        <%@ page import="java.sql.*" %>
        <%!
        Connection conn;
        PreparedStatement ps;
        ResultSet rs;
        int roll=Integer.parseInt(request.getParameter("roll"));

        String name=request.getParameter("name");

        try
        {
                Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
                conn = DriverManager.getConnection ("jdbc:odbc:harsh");
                System.out.println ("Connected....");
                ps=conn.prepareStatement("update Student set name=? where roll=?");
                ps.setString(1,name);
                ps.setInt(2,roll);
                if(ps.executeUpdate ()==0)
                {
%>
                <jsp:forward page="error.jsp"></jsp:forward>
<%
                }
        }
        catch(Exception ex)
        {}
        %>
        <h2>SUCCESSFULLY UPDATED</h2>
</body>
</html>

Delete From Student Database (delete.jsp) :-

Code:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!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>JSP Page</title>
</head>
<body>
        <%@ page import="java.sql.*" %>
        <%!
        Connection conn;
        PreparedStatement ps;
        ResultSet rs;
        String name=request.getParameter("name");
        int roll=Integer.parseInt(request.getParameter("roll"));
        try
        {
                Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
                conn = DriverManager.getConnection ("jdbc:odbc:harsh");
                System.out.println ("Connected....");
                ps=conn.prepareStatement("delete from Student where roll=?");
                ps.setInt(1,roll);
                if(ps.executeUpdate ()==0)
                {
%>
                        <jsp:forward page="error.jsp"></jsp:forward>
<%
        }
        catch(Exception ex)
        {}
        %>
        <h2>Successfully Deleted</h2>
</body>
</html>

In this page the actual deletion of the record entered in the previous page in the backend database is done. First the details are retrieved from the previous page. Then the corresponding record is deleted using prepareStatement as dynamic values are entered at runtime which changes as per the user.

If the login is not successful,
Error Page (error.jsp):-
Code:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!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>ERROR PAGE</title>
</head>
<body>
        <h2>DATA NOT FOUND</h2>
</body>
</html>

This is the error page which is displayed when any mismatch occurs.

I hope you enjoyed reading this article.:pleased:

shabbir 2Apr2010 09:12

Re: JDBC Basics - Part II
 
If you like this this article nominate it for Article of the month - Mar 2010

seangtz 5Apr2010 10:26

Re: JDBC Basics - Part II
 
Useful article...

techgeek.in 11Apr2010 13:38

Re: JDBC Basics - Part II
 
Quote:

Originally Posted by seangtz (Post 66703)
Useful article...

Thanks a lot.. :pleased:


All times are GMT +5.5. The time now is 14:55.