{"id":314,"date":"2018-07-27T08:56:12","date_gmt":"2018-07-27T08:56:12","guid":{"rendered":"http:\/\/csp12.epgpbooks.inflibnet.ac.in\/?post_type=chapter&p=314"},"modified":"2018-07-27T08:56:12","modified_gmt":"2018-07-27T08:56:12","slug":"jsp-with-jdbc","status":"publish","type":"chapter","link":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/chapter\/jsp-with-jdbc\/","title":{"rendered":"JSP with JDBC"},"content":{"raw":"
\r\n\r\n\u00a0 \u00a0 \u00a0JDBC<\/strong>\r\n\r\n \r\n

JDBC is an application programming interface between Java programs and database management systems. JDBC is a core part of the Java platform and is included in the standard JDK distribution. The purpose of JDBC is to connect database and manipulate the data in database from a Servlet page or from a JSP page.<\/p>\r\n \r\n\r\nBasic Steps to Connect Database<\/strong>\r\n\r\n \r\n\r\n1.\u00a0 Establish a connection.\r\n\r\n2.\u00a0 Create JDBC Statements.\r\n\r\n3.\u00a0 Execute SQL Statements.\r\n\r\n4.\u00a0 Get ResultSet.<\/span>\r\n\r\n5.\u00a0 Close connections.<\/span>\r\n\r\n<\/div>\r\n

\r\n\r\n\u00a0 \u00a0 Step Description<\/strong>\r\n\r\n \r\n\r\n1. Establish a connection<\/strong>\r\n\r\n \r\n\r\nA connection to MySQL is established by using the package,\r\n\r\n \r\n\r\nimport java.sql.*;\r\n\r\nThen load the vendor specific driver,\r\n\r\nClass.forName(\"oracle.jdbc.driver.OracleDriver\");\r\n\r\nThis code dynamically loads a driver class, for Oracle database.\r\n\r\nMake the connection,\r\n\r\nConnection conn = DriverManager.getConnection(DB_URL,USER,PASS);\r\n\r\nThis code establishes connection to database by obtaining a Connection object.\r\n\r\n \r\n\r\n2. Create JDBC statement(s)<\/strong>\r\n\r\n \r\n\r\nJDBC statements are created using the following code,\r\n\r\nStatement stmt = con.createStatement() ;\r\n\r\nThis creates a Statement object for sending SQL statements to the database.\r\n\r\n \r\n\r\n3. Executing SQL Statements<\/strong>\r\n\r\n \r\n\r\nSQL statements are executed using the query,\r\n\r\nsql = \"SELECT attributes FROM Table\";\r\n\r\n<\/div>\r\n
    \r\n \t
  1. Get ResultSet<\/strong>\u00a0<\/strong><\/li>\r\n<\/ol>\r\nThe Resultset object is obtained by the executeQuery() function,\r\n\r\n\u00a0<\/strong>\r\n\r\nResultSet rs = Stmt.executeQuery(sql);\u00a0<\/strong>\r\n
      \r\n \t
    1. Close Connection<\/strong><\/li>\r\n<\/ol>\r\nThe JDBC connection and SQL query execution is closed by calling the close() function,\r\n\r\ncon.close();\r\n\r\n \r\n\r\nInstall Mysql<\/strong>\r\n\r\n \r\n\r\nInstall Mysql Database from MYSQL official website dev.mysql.com\r\n\r\n \r\n\r\n\"\"\r\n\r\n \r\n\r\nSelect the Platform type (i.e Windows or Mac)and download the Installer.\r\n\r\n \r\n\r\n\"\"\r\n\r\n \r\n
      \r\n\r\n\u00a0 \u00a0Steps to run JSP using Tomcat server<\/strong>\r\n\r\n \r\n\r\nSTEP 1: To run JSP pages, Download TOMCAT SERVER from Website.<\/strong>\r\n\r\nURL: https:\/\/tomcat.apache.org\/download-60.cgi<\/strong>\r\n\r\nSTEP 2: The tomcat folder has the following files as bin, conf, lib, logs, temp, webapps and work.\r\n\r\nSTEP 3: Create directory \u201cmyjsp\u201d under the webapps directory.\r\n\r\nSTEP 4: Save this file in shown in the given path,\r\n\r\nC:\/ Program Files\/\u2026\/\u2026\/webapps\/myjsp\/Filename.\r\n\r\nSTEP 5: Download Mysql connector jar file from the following url.\r\n\r\nURL:https:\/\/dev.mysql.com\/downloads\/connector\/j\/3.1.html<\/strong>\r\n\r\nSTEP 6:Copy the jar file to apache-tomcat\\webapps\\ROOT\\WEB-INF\\lib directory.\r\n\r\nSTEP 7: To run JSP program\r\n\r\nStart the tomcat service,\r\n\r\nStart -> run -> services.msc -> Select Apache Tomcat -> start<\/strong>\r\n\r\nOpen new tab in browser or open new window,\r\n\r\nhttp:\/\/localhost:8080\/myjsp\/Filename.jsp<\/strong>\r\n\r\n<\/div>\r\n
      \r\n\r\n\u00a0 \u00a0 \u00a0Creating a Table using JSP<\/strong>\r\n\r\n \r\n\r\nExample Code:<\/strong>\r\n\r\n \r\n

      The following code is for creating a table using a JSP page. First establish a connection to the database from the JSP page. When connection is successful, execute the SQl statement \"CREATE table test (testid mediumint(8), name varchar(100))\" using the executeUpdate() function.<\/p>\r\n \r\n\r\n<%@page contentType=\"text\/html\" pageEncoding=\"UTF-8\"%> <!DOCTYPE html><%@ page import=\"java.sql.*\" %> <%@ page import=\"com.mysql.jdbc.Driver\" %>\r\n\r\n \r\n\r\n<%!String driver = \"com.mysql.jdbc.Driver\";\r\n\r\nString url = \"jdbc:mysql:\/\/localhost\/PUB\";\r\n\r\nString name = \"root\"; String pass=\"password\"; %>\r\n\r\n<html>\r\n\r\n<head>\r\n\r\n<title>testJSP<\/title>\r\n\r\n<\/head>\r\n\r\n<body>\r\n\r\n<p>Attempting to open JDBC connection to:... <\/p><%=url%>\r\n\r\n<%\r\n\r\ntry{\r\n\r\nString tableStr = \"CREATE table test (testid mediumint(8), name varchar(100))\"; Class.forName( driver );\r\n\r\nConnection con = DriverManager.getConnection( url, name, pass ); Statement stat = con.createStatement(); %><p> executing: <%=tableStr%><\/p>%<\/span>stat.executeUpdate( tableStr );<\/span>%><\/span><p> success.... <\/p><\/span><%<\/span>con.close();<\/span>\r\n\r\n}<\/span>\r\n\r\ncatch (SQLException sqle)<\/span>\r\n\r\n{<\/span>\r\n\r\nout.println(\"<p> Error opening JDBC, cause:<\/p> <b> \" + sqle + \"<\/b>\");<\/span>\r\n\r\n}<\/span>\r\n\r\ncatch(ClassNotFoundException cnfe)<\/span>\r\n\r\n{<\/span>\r\n\r\nout.println(\"<p> Error opening JDBC, cause:<\/p> <b>\" + cnfe + \"<\/b>\");<\/span>\r\n\r\n}<\/span>\r\n\r\n%><\/span>\r\n\r\n<\/body><\/span>\r\n\r\n<\/html><\/span>\r\n\r\n<\/div>\r\n

      \r\n\r\n\u00a0 \u00a0Creating a Table: Database<\/strong>\r\n\r\n \r\n\r\nOUTPUT:<\/strong>\r\n\r\n \r\n\r\nThe output of the above code is shown below.\r\n\r\n<\/div>\r\n

      \"\"<\/p>\r\n \r\n\r\nDATABASE:<\/strong>\r\n\r\n \r\n

      \"\"<\/p>\r\n \r\n

      \r\n\r\nSELECT Operation<\/strong>\r\n\r\n \r\n\r\nExample Code:<\/strong>\r\n\r\n \r\n

      The following code is for selecting records from a table using a JSP page and display the records in a table format. First establish a connection to the database from the JSP page. When connection is successful, execute the SQL statement \"select * from Publisher\" using the executeQuery() function.<\/p>\r\n \r\n\r\n<%@page contentType=\"text\/html\" pageEncoding=\"UTF-8\"%> <!DOCTYPE html>\r\n\r\n<%@ page import=\"java.sql.*\" %>\r\n\r\n<% Class.forName(\"com.mysql.jdbc.Driver\"); %>\r\n\r\n<HTML>\r\n\r\n<HEAD>\r\n<\/span>\u00a0 \u00a0 <TITLE>The Publishers Database Table <\/TITLE><\/span>\r\n\r\n<\/HEAD><\/span>\r\n\r\n<BODY><\/span>\r\n\r\n<H1>The Publishers Database Table <\/H1><\/span>\r\n\r\n<%<\/strong>Connection connection = DriverManager.getConnection(<\/span>\"jdbc:mysql:\/\/localhost\/PUB\", \"root\", \"password\");<\/span>Statement statement = connection.createStatement() ;<\/span>ResultSet resultset = statement.executeQuery(\"select * from Publisher\") ; %><\/strong><\/span>\r\n\r\n<TABLE BORDER=\"1\"><\/span>\r\n\r\n<TR><\/span>\r\n\r\n<TH>ID<\/TH><\/span>\r\n\r\n<TH>Name<\/TH><\/span>\r\n\r\n<TH>City<\/TH><\/span>\r\n\r\n<TH>State<\/TH><\/span>\r\n\r\n<TH>Country<\/TH><\/span>\r\n\r\n<\/TR><\/span>\r\n\r\n<% while(resultset.next()){ %><\/span>\r\n\r\n<TR><\/span>\r\n\r\n<TD> <%= resultset.getString(1) %><\/td><\/span>\r\n\r\n<TD> <%= resultset.getString(2) %><\/TD><\/span>\r\n\r\n<TD> <%= resultset.getString(3) %><\/TD><\/span>\r\n\r\n<TD> <%= resultset.getString(4) %><\/TD><\/span>\r\n\r\n<TD> <%= resultset.getString(5) %><\/TD><\/span>\r\n\r\n<\/TR><\/span>\r\n\r\n<% } %><\/span>\r\n\r\n<\/TABLE><\/span>\r\n\r\n<\/BODY><\/span>\r\n\r\n<\/HTML><\/span>\r\n\r\n \r\n\r\n<\/div>\r\nOUTPUT:<\/strong>\r\n\r\n \r\n\r\nThe output of the above code is shown below.\r\n\r\n \r\n

      \"\"<\/p>\r\n \r\n

      \r\n\r\nINSERT Operation<\/strong>\r\n\r\n \r\n\r\nExample Code:<\/strong>\r\n\r\n \r\n

      The following code is for inserting records into a table using a JSP page. First establish a connection to the database from the JSP page. When connection is successful, execute the SQl statement \"INSERT<\/p>\r\n \r\n\r\nINTO Employees VALUES (105, 22, \u2018Jeeva', \u2018Kumar')\".\r\n\r\n<%@ page import=\"java.io.*,java.util.*,java.sql.*\"%>\r\n\r\n<%@ page import=\"javax.servlet.http.*,javax.servlet.*\" %>\r\n\r\n<%@ taglib uri=\"http:\/\/java.sun.com\/jsp\/jstl\/core\" prefix=\"c\"%>\r\n\r\n<%@ taglib uri=\"http:\/\/java.sun.com\/jsp\/jstl\/sql\" prefix=\"sql\"%>\r\n\r\n<html>\r\n\r\n<head>\r\n\r\n<title>JINSERT Operation<\/title>\r\n\r\n<\/head>\r\n\r\n<body>\r\n\r\n<sql:setDataSource var=\"snapshot\" driver=\"com.mysql.jdbc.Driver\"\u00a0url=\"jdbc:mysql:\/\/localhost\/TEST\"\u00a0<\/span>user=\"root\"\u00a0 password=\"password\"\/><\/span>\r\n\r\n<sql:update dataSource=\"${snapshot}\" var=\"result\"><\/span>\r\n\r\nINSERT INTO Employees VALUES (105, 22, \u2018Jeeva', \u2018Kumar'); <\/sql:update><\/span>\r\n\r\n<sql:query dataSource=\"${snapshot}\" var=\"result\"> SELECT * from Employees; <\/sql:query><\/span>\r\n\r\n<\/div>\r\n\u00a0 \u00a0Output<\/strong>:\r\n\r\n \r\n\r\nThe output of the above code is shown below\r\n\r\n \r\n

      \"\"<\/p>\r\n \r\n\r\nDatabase (Before Executing)<\/strong>:\r\n\r\n \r\n

      \"\"<\/p>\r\n \r\n

      \r\n\r\n\u00a0 \u00a0DELETE Operation<\/strong>\r\n\r\n \r\n

      The following code is for deleting records from a table using a JSP page. First establish a connection to the database from the JSP page. When connection is successful, execute the SQL statement \"DELETE FROM Employees WHERE Id = ?\".<\/p>\r\n \r\n\r\nExample<\/strong>\r\n\r\n \r\n\r\n<%@ page import=\"java.io.*,java.util.*,java.sql.*\"%>\r\n\r\n<%@ page import=\"javax.servlet.http.*,javax.servlet.*\" %>\r\n\r\n<%@ taglib uri=\"http:\/\/java.sun.com\/jsp\/jstl\/core\" prefix=\"c\"%>\r\n\r\n<%@ taglib uri=\"http:\/\/java.sun.com\/jsp\/jstl\/sql\" prefix=\"sql\"%>\r\n\r\n<html>\r\n\r\n<head>\r\n\r\n<title>DELETE Operation<\/title>\r\n\r\n<\/head><\/span>\r\n\r\n<body><\/span>\r\n\r\n<sql:setDataSource var=\"snapshot\" driver=\"com.mysql.jdbc.Driver\"<\/span>url=\"jdbc:mysql:\/\/localhost\/TEST\"<\/span>user=\"root\"\u00a0 password=\"password\"\/><\/span>\r\n\r\n<c:set var=\"empId\" value=\"102\"\/><\/span>\r\n\r\n<sql:update dataSource=\"${snapshot}\" var=\"count\"> DELETE FROM Employees WHERE Id = ? <sql:param value=\"${empId}\" \/> <\/sql:update><\/span>\r\n\r\n<sql:query dataSource=\"${snapshot}\" var=\"result\"> SELECT * from Employees;<\/span>\r\n\r\n<\/sql:query><\/span>\r\n\r\n<table border=\"1\" width \"100%\"><\/span>\r\n\r\n<tr><\/span>\r\n\r\n<th>Emp ID<\/th><\/span>\r\n\r\n<th>First Name<\/th><\/span>\r\n\r\n<th>Last Name<\/th><\/span>\r\n\r\n<th>Age<\/th><\/span>\r\n\r\n<\/tr><\/span>\r\n\r\n<c:forEach var=\"row\" items=\"${result.rows}\"><\/span>\r\n\r\n<tr><\/span>\r\n\r\n<td><c:out value=\"${row.id}\"\/><\/td><\/span>\r\n\r\n<td><c:out value=\"${row.first}\"\/><\/td><\/span>\r\n\r\n<td><c:out value=\"${row.last}\"\/><\/td><\/span>\r\n\r\n<td><c:out value=\"${row.age}\"\/><\/td><\/span>\r\n\r\n<\/tr><\/span>\r\n\r\n<\/c:forEach><\/span>\r\n\r\n<\/table><\/span>\r\n\r\n<\/body><\/span>\r\n\r\n<\/html><\/span>\r\n\r\n<\/div>\r\n\u00a0 \u00a0 \u00a0Output:<\/strong>\r\n\r\n \r\n\r\nThe output of the above code is shown below.\r\n\r\n \r\n

      \"\"<\/p>\r\n \r\n

      \r\n\r\n\u00a0 \u00a0 Login Application<\/strong>\r\n\r\n \r\n\r\nExample<\/strong>\r\n\r\n\/\/Index.html<\/strong>\r\n\r\n \r\n\r\n<body>\r\n\r\n<form action=\"Login.jsp\" method=\"post\">\r\n\r\nUser name :<input type=\"text\" name=\"userid\" \/> password :<input type=\"password\" name=\"password\" \/> <input type=\"submit\" \/>\r\n\r\n<\/form><\/span>\r\n\r\n<\/body><\/span>\r\n\r\nLogin.jsp<\/span>\r\n\r\n<%@ page import =\"java.sql.*\" %><\/span>\r\n\r\n<%@ page import =\"javax.servlet.http.*\" %><\/span>\r\n\r\n<%<\/span>String userid=request.getParameter(\"userid\");<\/span>\r\n\r\nsession.putValue(\"userid\",userid);<\/span>\r\n\r\nString password=request.getParameter(\"password\"); Class.forName(\"com.mysql.jdbc.Driver\"); java.sql.Connection con =<\/span>DriverManager.getConnection(\"jdbc:mysql:\/\/localhost\/test\",\"root\",\"password\");<\/span>\r\n\r\nStatement st= con.createStatement();<\/span>\r\n\r\nResultSet rs=st.executeQuery(\"select * from users where user_id='\"+userid+\"'\");<\/span>\r\n\r\nif(rs.next())<\/span>\r\n\r\n{<\/span>\r\n\r\nif(rs.getString(2).equals(password))<\/span>\r\n\r\n{<\/span>\r\n\r\nout.println(\"welcome\"+userid);<\/span>\r\n\r\n}<\/span>\r\n\r\nelse<\/span>\r\n\r\n{<\/span>\r\n\r\nout.println(\"Invalid password try again\");<\/span>\r\n\r\n}<\/span>\r\n\r\n}<\/span>\r\n\r\nelse<\/span>%><\/span>\r\n\r\n<\/div>\r\n
      \r\n\r\n\u00a0 \u00a0 \u00a0Output:<\/strong>\r\n\r\n \r\n\r\nThe output of the above code is shown below.\r\n\r\n<\/div>\r\n

      \"\"<\/p>\r\n \r\n\r\nDatabase:<\/strong>\r\n\r\n \r\n

      \"\"<\/p>\r\n\r\n

      \r\n\r\nSummary<\/strong>\r\n\r\n \r\n

      In this section we discussed about how to install MySQL and run JSP pages in Tomcat. The module also explains about the operational views of how to Create table, Insert and Delete data in JDBC from JSP page illustrated with needed examples. Finally, this module also demonstrates a simple login Application created using JSP with JDBC.<\/p>\r\n\r\n<\/div>\r\nWeb Links<\/strong>\r\n

        \r\n \t
      • Herbert Schildt, \" Java: The Complete Reference\", 9th Edition,<\/em> Mcgraw-Hill, 2014.<\/li>\r\n \t
      • https:\/\/www.tutorialspoint.com<\/em><\/li>\r\n<\/ul>\r\n \r\n\r\n ","rendered":"
        \n

        \u00a0 \u00a0 \u00a0JDBC<\/strong><\/p>\n

         <\/p>\n

        JDBC is an application programming interface between Java programs and database management systems. JDBC is a core part of the Java platform and is included in the standard JDK distribution. The purpose of JDBC is to connect database and manipulate the data in database from a Servlet page or from a JSP page.<\/p>\n

         <\/p>\n

        Basic Steps to Connect Database<\/strong><\/p>\n

         <\/p>\n

        1.\u00a0 Establish a connection.<\/p>\n

        2.\u00a0 Create JDBC Statements.<\/p>\n

        3.\u00a0 Execute SQL Statements.<\/p>\n

        4.\u00a0 Get ResultSet.<\/span><\/p>\n

        5.\u00a0 Close connections.<\/span><\/p>\n<\/div>\n

        \n

        \u00a0 \u00a0 Step Description<\/strong><\/p>\n

         <\/p>\n

        1. Establish a connection<\/strong><\/p>\n

         <\/p>\n

        A connection to MySQL is established by using the package,<\/p>\n

         <\/p>\n

        import java.sql.*;<\/p>\n

        Then load the vendor specific driver,<\/p>\n

        Class.forName(“oracle.jdbc.driver.OracleDriver”);<\/p>\n

        This code dynamically loads a driver class, for Oracle database.<\/p>\n

        Make the connection,<\/p>\n

        Connection conn = DriverManager.getConnection(DB_URL,USER,PASS);<\/p>\n

        This code establishes connection to database by obtaining a Connection object.<\/p>\n

         <\/p>\n

        2. Create JDBC statement(s)<\/strong><\/p>\n

         <\/p>\n

        JDBC statements are created using the following code,<\/p>\n

        Statement stmt = con.createStatement() ;<\/p>\n

        This creates a Statement object for sending SQL statements to the database.<\/p>\n

         <\/p>\n

        3. Executing SQL Statements<\/strong><\/p>\n

         <\/p>\n

        SQL statements are executed using the query,<\/p>\n

        sql = “SELECT attributes FROM Table”;<\/p>\n<\/div>\n

          \n
        1. Get ResultSet<\/strong>\u00a0<\/strong><\/li>\n<\/ol>\n

          The Resultset object is obtained by the executeQuery() function,<\/p>\n

          \u00a0<\/strong><\/p>\n

          ResultSet rs = Stmt.executeQuery(sql);\u00a0<\/strong><\/p>\n

            \n
          1. Close Connection<\/strong><\/li>\n<\/ol>\n

            The JDBC connection and SQL query execution is closed by calling the close() function,<\/p>\n

            con.close();<\/p>\n

             <\/p>\n

            Install Mysql<\/strong><\/p>\n

             <\/p>\n

            Install Mysql Database from MYSQL official website dev.mysql.com<\/p>\n

             <\/p>\n

            \"\"<\/p>\n

             <\/p>\n

            Select the Platform type (i.e Windows or Mac)and download the Installer.<\/p>\n

             <\/p>\n

            \"\"<\/p>\n

             <\/p>\n

            \n

            \u00a0 \u00a0Steps to run JSP using Tomcat server<\/strong><\/p>\n

             <\/p>\n

            STEP 1: To run JSP pages, Download TOMCAT SERVER from Website.<\/strong><\/p>\n

            URL: https:\/\/tomcat.apache.org\/download-60.cgi<\/strong><\/p>\n

            STEP 2: The tomcat folder has the following files as bin, conf, lib, logs, temp, webapps and work.<\/p>\n

            STEP 3: Create directory \u201cmyjsp\u201d under the webapps directory.<\/p>\n

            STEP 4: Save this file in shown in the given path,<\/p>\n

            C:\/ Program Files\/\u2026\/\u2026\/webapps\/myjsp\/Filename.<\/p>\n

            STEP 5: Download Mysql connector jar file from the following url.<\/p>\n

            URL:https:\/\/dev.mysql.com\/downloads\/connector\/j\/3.1.html<\/strong><\/p>\n

            STEP 6:Copy the jar file to apache-tomcat\\webapps\\ROOT\\WEB-INF\\lib directory.<\/p>\n

            STEP 7: To run JSP program<\/p>\n

            Start the tomcat service,<\/p>\n

            Start -> run -> services.msc -> Select Apache Tomcat -> start<\/strong><\/p>\n

            Open new tab in browser or open new window,<\/p>\n

            http:\/\/localhost:8080\/myjsp\/Filename.jsp<\/strong><\/p>\n<\/div>\n

            \n

            \u00a0 \u00a0 \u00a0Creating a Table using JSP<\/strong><\/p>\n

             <\/p>\n

            Example Code:<\/strong><\/p>\n

             <\/p>\n

            The following code is for creating a table using a JSP page. First establish a connection to the database from the JSP page. When connection is successful, execute the SQl statement “CREATE table test (testid mediumint(8), name varchar(100))” using the executeUpdate() function.<\/p>\n

             <\/p>\n

            <%@page contentType=”text\/html” pageEncoding=”UTF-8″%> <!DOCTYPE html><%@ page import=”java.sql.*” %> <%@ page import=”com.mysql.jdbc.Driver” %><\/p>\n

             <\/p>\n

            <%!String driver = “com.mysql.jdbc.Driver”;<\/p>\n

            String url = “jdbc:mysql:\/\/localhost\/PUB”;<\/p>\n

            String name = “root”; String pass=”password”; %><\/p>\n

            <html><\/p>\n

            <head><\/p>\n

            <title>testJSP<\/title><\/p>\n

            <\/head><\/p>\n

            <body><\/p>\n

            <p>Attempting to open JDBC connection to:… <\/p><%=url%><\/p>\n

            <%<\/p>\n

            try{<\/p>\n

            String tableStr = “CREATE table test (testid mediumint(8), name varchar(100))”; Class.forName( driver );<\/p>\n

            Connection con = DriverManager.getConnection( url, name, pass ); Statement stat = con.createStatement(); %><p> executing: <%=tableStr%><\/p>%<\/span>stat.executeUpdate( tableStr );<\/span>%><\/span><p> success…. <\/p><\/span><%<\/span>con.close();<\/span><\/p>\n

            }<\/span><\/p>\n

            catch (SQLException sqle)<\/span><\/p>\n

            {<\/span><\/p>\n

            out.println(“<p> Error opening JDBC, cause:<\/p> <b> ” + sqle + “<\/b>”);<\/span><\/p>\n

            }<\/span><\/p>\n

            catch(ClassNotFoundException cnfe)<\/span><\/p>\n

            {<\/span><\/p>\n

            out.println(“<p> Error opening JDBC, cause:<\/p> <b>” + cnfe + “<\/b>”);<\/span><\/p>\n

            }<\/span><\/p>\n

            %><\/span><\/p>\n

            <\/body><\/span><\/p>\n

            <\/html><\/span><\/p>\n<\/div>\n

            \n

            \u00a0 \u00a0Creating a Table: Database<\/strong><\/p>\n

             <\/p>\n

            OUTPUT:<\/strong><\/p>\n

             <\/p>\n

            The output of the above code is shown below.<\/p>\n<\/div>\n

            \"\"<\/p>\n

             <\/p>\n

            DATABASE:<\/strong><\/p>\n

             <\/p>\n

            \"\"<\/p>\n

             <\/p>\n

            \n

            SELECT Operation<\/strong><\/p>\n

             <\/p>\n

            Example Code:<\/strong><\/p>\n

             <\/p>\n

            The following code is for selecting records from a table using a JSP page and display the records in a table format. First establish a connection to the database from the JSP page. When connection is successful, execute the SQL statement “select * from Publisher” using the executeQuery() function.<\/p>\n

             <\/p>\n

            <%@page contentType=”text\/html” pageEncoding=”UTF-8″%> <!DOCTYPE html><\/p>\n

            <%@ page import=”java.sql.*” %><\/p>\n

            <% Class.forName(“com.mysql.jdbc.Driver”); %><\/p>\n

            <HTML><\/p>\n

            <HEAD>
            \n<\/span>\u00a0 \u00a0 <TITLE>The Publishers Database Table <\/TITLE><\/span><\/p>\n

            <\/HEAD><\/span><\/p>\n

            <BODY><\/span><\/p>\n

            <H1>The Publishers Database Table <\/H1><\/span><\/p>\n

            <%<\/strong>Connection connection = DriverManager.getConnection(<\/span>“jdbc:mysql:\/\/localhost\/PUB”, “root”, “password”);<\/span>Statement statement = connection.createStatement() ;<\/span>ResultSet resultset = statement.executeQuery(“select * from Publisher”) ; %><\/strong><\/span><\/p>\n

            <TABLE BORDER=”1″><\/span><\/p>\n

            <TR><\/span><\/p>\n

            <TH>ID<\/TH><\/span><\/p>\n

            <TH>Name<\/TH><\/span><\/p>\n

            <TH>City<\/TH><\/span><\/p>\n

            <TH>State<\/TH><\/span><\/p>\n

            <TH>Country<\/TH><\/span><\/p>\n

            <\/TR><\/span><\/p>\n

            <% while(resultset.next()){ %><\/span><\/p>\n

            <TR><\/span><\/p>\n

            <TD> <%= resultset.getString(1) %><\/td><\/span><\/p>\n

            <TD> <%= resultset.getString(2) %><\/TD><\/span><\/p>\n

            <TD> <%= resultset.getString(3) %><\/TD><\/span><\/p>\n

            <TD> <%= resultset.getString(4) %><\/TD><\/span><\/p>\n

            <TD> <%= resultset.getString(5) %><\/TD><\/span><\/p>\n

            <\/TR><\/span><\/p>\n

            <% } %><\/span><\/p>\n

            <\/TABLE><\/span><\/p>\n

            <\/BODY><\/span><\/p>\n

            <\/HTML><\/span><\/p>\n

             <\/p>\n<\/div>\n

            OUTPUT:<\/strong><\/p>\n

             <\/p>\n

            The output of the above code is shown below.<\/p>\n

             <\/p>\n

            \"\"<\/p>\n

             <\/p>\n

            \n

            INSERT Operation<\/strong><\/p>\n

             <\/p>\n

            Example Code:<\/strong><\/p>\n

             <\/p>\n

            The following code is for inserting records into a table using a JSP page. First establish a connection to the database from the JSP page. When connection is successful, execute the SQl statement “INSERT<\/p>\n

             <\/p>\n

            INTO Employees VALUES (105, 22, \u2018Jeeva’, \u2018Kumar’)”.<\/p>\n

            <%@ page import=”java.io.*,java.util.*,java.sql.*”%><\/p>\n

            <%@ page import=”javax.servlet.http.*,javax.servlet.*” %><\/p>\n

            <%@ taglib uri=”http:\/\/java.sun.com\/jsp\/jstl\/core” prefix=”c”%><\/p>\n

            <%@ taglib uri=”http:\/\/java.sun.com\/jsp\/jstl\/sql” prefix=”sql”%><\/p>\n

            <html><\/p>\n

            <head><\/p>\n

            <title>JINSERT Operation<\/title><\/p>\n

            <\/head><\/p>\n

            <body><\/p>\n

            <sql:setDataSource var=”snapshot” driver=”com.mysql.jdbc.Driver”\u00a0url=”jdbc:mysql:\/\/localhost\/TEST”\u00a0<\/span>user=”root”\u00a0 password=”password”\/><\/span><\/p>\n

            <sql:update dataSource=”${snapshot}” var=”result”><\/span><\/p>\n

            INSERT INTO Employees VALUES (105, 22, \u2018Jeeva’, \u2018Kumar’); <\/sql:update><\/span><\/p>\n

            <sql:query dataSource=”${snapshot}” var=”result”> SELECT * from Employees; <\/sql:query><\/span><\/p>\n<\/div>\n

            \u00a0 \u00a0Output<\/strong>:<\/p>\n

             <\/p>\n

            The output of the above code is shown below<\/p>\n

             <\/p>\n

            \"\"<\/p>\n

             <\/p>\n

            Database (Before Executing)<\/strong>:<\/p>\n

             <\/p>\n

            \"\"<\/p>\n

             <\/p>\n

            \n

            \u00a0 \u00a0DELETE Operation<\/strong><\/p>\n

             <\/p>\n

            The following code is for deleting records from a table using a JSP page. First establish a connection to the database from the JSP page. When connection is successful, execute the SQL statement “DELETE FROM Employees WHERE Id = ?”.<\/p>\n

             <\/p>\n

            Example<\/strong><\/p>\n

             <\/p>\n

            <%@ page import=”java.io.*,java.util.*,java.sql.*”%><\/p>\n

            <%@ page import=”javax.servlet.http.*,javax.servlet.*” %><\/p>\n

            <%@ taglib uri=”http:\/\/java.sun.com\/jsp\/jstl\/core” prefix=”c”%><\/p>\n

            <%@ taglib uri=”http:\/\/java.sun.com\/jsp\/jstl\/sql” prefix=”sql”%><\/p>\n

            <html><\/p>\n

            <head><\/p>\n

            <title>DELETE Operation<\/title><\/p>\n

            <\/head><\/span><\/p>\n

            <body><\/span><\/p>\n

            <sql:setDataSource var=”snapshot” driver=”com.mysql.jdbc.Driver”<\/span>url=”jdbc:mysql:\/\/localhost\/TEST”<\/span>user=”root”\u00a0 password=”password”\/><\/span><\/p>\n

            <c:set var=”empId” value=”102″\/><\/span><\/p>\n

            <sql:update dataSource=”${snapshot}” var=”count”> DELETE FROM Employees WHERE Id = ? <sql:param value=”${empId}” \/> <\/sql:update><\/span><\/p>\n

            <sql:query dataSource=”${snapshot}” var=”result”> SELECT * from Employees;<\/span><\/p>\n

            <\/sql:query><\/span><\/p>\n

            <table border=”1″ width “100%”><\/span><\/p>\n

            <tr><\/span><\/p>\n

            <th>Emp ID<\/th><\/span><\/p>\n

            <th>First Name<\/th><\/span><\/p>\n

            <th>Last Name<\/th><\/span><\/p>\n

            <th>Age<\/th><\/span><\/p>\n

            <\/tr><\/span><\/p>\n

            <c:forEach var=”row” items=”${result.rows}”><\/span><\/p>\n

            <tr><\/span><\/p>\n

            <td><c:out value=”${row.id}”\/><\/td><\/span><\/p>\n

            <td><c:out value=”${row.first}”\/><\/td><\/span><\/p>\n

            <td><c:out value=”${row.last}”\/><\/td><\/span><\/p>\n

            <td><c:out value=”${row.age}”\/><\/td><\/span><\/p>\n

            <\/tr><\/span><\/p>\n

            <\/c:forEach><\/span><\/p>\n

            <\/table><\/span><\/p>\n

            <\/body><\/span><\/p>\n

            <\/html><\/span><\/p>\n<\/div>\n

            \u00a0 \u00a0 \u00a0Output:<\/strong><\/p>\n

             <\/p>\n

            The output of the above code is shown below.<\/p>\n

             <\/p>\n

            \"\"<\/p>\n

             <\/p>\n

            \n

            \u00a0 \u00a0 Login Application<\/strong><\/p>\n

             <\/p>\n

            Example<\/strong><\/p>\n

            \/\/Index.html<\/strong><\/p>\n

             <\/p>\n

            <body><\/p>\n

            <form action=”Login.jsp” method=”post”><\/p>\n

            User name :<input type=”text” name=”userid” \/> password :<input type=”password” name=”password” \/> <input type=”submit” \/><\/p>\n

            <\/form><\/span><\/p>\n

            <\/body><\/span><\/p>\n

            Login.jsp<\/span><\/p>\n

            <%@ page import =”java.sql.*” %><\/span><\/p>\n

            <%@ page import =”javax.servlet.http.*” %><\/span><\/p>\n

            <%<\/span>String userid=request.getParameter(“userid”);<\/span><\/p>\n

            session.putValue(“userid”,userid);<\/span><\/p>\n

            String password=request.getParameter(“password”); Class.forName(“com.mysql.jdbc.Driver”); java.sql.Connection con =<\/span>DriverManager.getConnection(“jdbc:mysql:\/\/localhost\/test”,”root”,”password”);<\/span><\/p>\n

            Statement st= con.createStatement();<\/span><\/p>\n

            ResultSet rs=st.executeQuery(“select * from users where user_id='”+userid+”‘”);<\/span><\/p>\n

            if(rs.next())<\/span><\/p>\n

            {<\/span><\/p>\n

            if(rs.getString(2).equals(password))<\/span><\/p>\n

            {<\/span><\/p>\n

            out.println(“welcome”+userid);<\/span><\/p>\n

            }<\/span><\/p>\n

            else<\/span><\/p>\n

            {<\/span><\/p>\n

            out.println(“Invalid password try again”);<\/span><\/p>\n

            }<\/span><\/p>\n

            }<\/span><\/p>\n

            else<\/span>%><\/span><\/p>\n<\/div>\n

            \n

            \u00a0 \u00a0 \u00a0Output:<\/strong><\/p>\n

             <\/p>\n

            The output of the above code is shown below.<\/p>\n<\/div>\n

            \"\"<\/p>\n

             <\/p>\n

            Database:<\/strong><\/p>\n

             <\/p>\n

            \"\"<\/p>\n

            \n

            Summary<\/strong><\/p>\n

             <\/p>\n

            In this section we discussed about how to install MySQL and run JSP pages in Tomcat. The module also explains about the operational views of how to Create table, Insert and Delete data in JDBC from JSP page illustrated with needed examples. Finally, this module also demonstrates a simple login Application created using JSP with JDBC.<\/p>\n<\/div>\n

            Web Links<\/strong><\/p>\n

              \n
            • Herbert Schildt, ” Java: The Complete Reference”, 9th Edition,<\/em> Mcgraw-Hill, 2014.<\/li>\n
            • https:\/\/www.tutorialspoint.com<\/em><\/li>\n<\/ul>\n

               <\/p>\n

               <\/p>\n","protected":false},"author":4,"menu_order":31,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":["dr-m-vijayalakshmi"],"pb_section_license":""},"chapter-type":[],"contributor":[58],"license":[],"class_list":["post-314","chapter","type-chapter","status-publish","hentry","contributor-dr-m-vijayalakshmi"],"part":3,"_links":{"self":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/pressbooks\/v2\/chapters\/314","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/wp\/v2\/users\/4"}],"version-history":[{"count":1,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/pressbooks\/v2\/chapters\/314\/revisions"}],"predecessor-version":[{"id":325,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/pressbooks\/v2\/chapters\/314\/revisions\/325"}],"part":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/pressbooks\/v2\/chapters\/314\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/wp\/v2\/media?parent=314"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/pressbooks\/v2\/chapter-type?post=314"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/wp\/v2\/contributor?post=314"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/wp-json\/wp\/v2\/license?post=314"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}