JDBC Basics - Part II

Discussion in 'Java' started by techgeek.in, Mar 8, 2010.

  1. techgeek.in

    techgeek.in New Member

    Joined:
    Dec 20, 2009
    Messages:
    572
    Likes Received:
    19
    Trophy Points:
    0
    Occupation:
    EOC (exploitation of computers)..i m a Terminator.
    Location:
    Not an alien!! for sure
    Home Page:
    http://www.techgeek.in
    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.

      [​IMG]
    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

      [​IMG]

      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.

    [​IMG]

    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:
     
    Last edited by a moderator: Jan 21, 2017
    shabbir and nikhil389 like this.
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
  3. seangtz

    seangtz New Member

    Joined:
    Jun 6, 2008
    Messages:
    126
    Likes Received:
    3
    Trophy Points:
    0
    Useful article...
     
  4. techgeek.in

    techgeek.in New Member

    Joined:
    Dec 20, 2009
    Messages:
    572
    Likes Received:
    19
    Trophy Points:
    0
    Occupation:
    EOC (exploitation of computers)..i m a Terminator.
    Location:
    Not an alien!! for sure
    Home Page:
    http://www.techgeek.in
    Thanks a lot.. :pleased:
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice