Java/JSP/Database — различия между версиями

Текущая версия на 07:10, 1 июня 2010

Содержание

1 Accessing the table field in Database
2 A First JSP Database
3 Calling a Stored procedure within a JSP
4 Creating a Table
5 Display table in Database
6 Fetching Data From a Database
7 Filling a Table
8 Joining Tables
9 JSP Access to Databases
10 JSP Database Demo
11 JSP Database Query
12 JSTL: Transaction with a JSP
13 Navigating in a Database Table
14 Navigating in a Database Table 2
15 New Address Creation using executeUpdate
16 Obtaining a Connection in JSP
17 Obtaining a database Connection
18 Presenting database content
19 Presenting database content using tags
20 Selecting records with condition From a Database
21 Updating a database using the sql:update tag
22 Using a DataSource
23 Using a Preconfigured DataSource
24 Using a Result object
25 Using Table Metadata
26 Using the SortedMap
27 Using Transactions

Accessing the table field in Database

<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") ; %>
<HTML>
    <HEAD>
        <TITLE>Accessing the tableName Database Table</TITLE>
    </HEAD>
    <BODY>
        <H1>Accessing the tableName Database Table</H1>
        <% 
            Connection connection = DriverManager.getConnection(
                "jdbc:odbc:data", "userName", "password");
                Statement statement = connection.createStatement() ;
                ResultSet resultset = 
                    statement.executeQuery("select name from tableName") ; 
        %>
        <TABLE BORDER="1">
            <TR>
                <TH>Name</TH>
            </TR>
            <% while(resultset.next()){ %>
                <TR>
                    <TD> 
                        <%= resultset.getString(1)%>  
                    </TD>
                </TR>
            <% } %>
        </TABLE>
    </BODY>
</HTML>

A First JSP Database

<%@ taglib prefix="c" uri="http://java.sun.ru/jstl/core_rt" %>
<%@ taglib prefix="sql" uri="http://java.sun.ru/jstl/sql_rt" %>
<sql:setDataSource var="datasource" 
                driver="com.mysql.jdbc.Driver" 
                   url="jdbc:mysql://localhost/publish" />
<sql:query var="books" dataSource="${datasource}">
  SELECT id, title, price FROM book 
</sql:query>
 
<html>
  <head>
    <title>A First JSP Database</title>
  </head>
  <body>
    <table border="1">
      <tr>
        <td>id</td><td>title</td><td>price</td>
      </tr>
      <c:forEach items="${books.rows}" var="row"> 
      <tr>
        <td><c:out value="${row.id}" /></td>
        <td><c:out value="${row.title}" /></td>
        <td><c:out value="${row.price}" /></td>
      </tr>
      </c:forEach> 
    </table>
  </body>
</html>

Calling a Stored procedure within a JSP

<%@ taglib uri="jexp.ru.tags" prefix="cbck" %>
<html>
<head><title>Calling a Stored procedure</title></head>
<body>
<h2>This JSP calls a stored procedure with a JSP 2.0 function</h2>
${cbck:addEvent("Event Name","place","16-Jul-2005")}
</body>
</html>

Creating a Table

<%@ page import="java.sql.*" %>
<HTML>
    <HEAD>
        <TITLE>Creating a Table</TITLE>
    </HEAD>
    <BODY>
        <H1>Creating a Table</H1>
        <%  
            Connection connection = null;
            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
                connection = DriverManager.getConnection("jdbc:odbc:data", "userName", "password");
                Statement statement = connection.createStatement();
                String command = "CREATE TABLE Employees (ID INTEGER, Name CHAR(50));";
                statement.executeUpdate(command);
            } catch (Exception e) {
                out.println("An error occurred.");
            }
        %>
        The Employees table was created.
    </BODY>
</HTML>

Display table in Database

<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
<HTML>
    <HEAD>
        <TITLE>The tableName Database Table </TITLE>
    </HEAD>
    <BODY>
        <H1>The tableName Database Table </H1>
        <% 
            Connection connection = DriverManager.getConnection(
                "jdbc:odbc:data", "Steve", "password");
            Statement statement = connection.createStatement() ;
            ResultSet resultset = 
                statement.executeQuery("select * from tableName") ; 
        %>
        <TABLE BORDER="1">
            <TR>
                <TH>ID</TH>
                <TH>Name</TH>
                <TH>City</TH>
                <TH>State</TH>
                <TH>Country</TH>
            </TR>
            <% while(resultset.next()){ %>
            <TR>
                <TD> <%= resultset.getString(1) %></td>
                <TD> <%= resultset.getString(2) %></TD>
                <TD> <%= resultset.getString(3) %></TD>
                <TD> <%= resultset.getString(4) %></TD>
                <TD> <%= resultset.getString(5) %></TD>
            </TR>
            <% } %>
        </TABLE>
    </BODY>
</HTML>

Fetching Data From a Database

<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
<HTML>
    <HEAD>
        <TITLE>Fetching Data From a Database</TITLE>
    </HEAD>
    <BODY>
        <H1>Database Lookup</H1>
        <FORM ACTION="self.jsp" METHOD="POST">
            Please enter the ID of the publisher you want to find:
            <BR>
            <INPUT TYPE="TEXT" NAME="id">
            <BR>
            <INPUT TYPE="SUBMIT" value="Submit">
        </FORM>

        <H1>Fetching Data From a Database</H1>
        <% 
            Connection connection = DriverManager.getConnection(
                "jdbc:odbc:data", "userName", "password");
            Statement statement = connection.createStatement();
            String id = request.getParameter("id");  
            ResultSet resultset = 
                statement.executeQuery("select * from tableName where id = "" + id + """) ; 
            if(!resultset.next()) {
                out.println("Sorry, could not find that publisher. " +
                "Please .");
            } else {
        %>
        <TABLE BORDER="1">
            <TR>
               <TH>ID</TH>
               <TH>Name</TH>
               <TH>City</TH>
               <TH>State</TH>
               <TH>Country</TH>
           </TR>
           <TR>
               <TD> <%= resultset.getString(1) %> </TD>
               <TD> <%= resultset.getString(2) %> </TD>
               <TD> <%= resultset.getString(3) %> </TD>
               <TD> <%= resultset.getString(4) %> </TD>
               <TD> <%= resultset.getString(5) %> </TD>
           </TR>
       </TABLE>
       <BR>
       <% 
           } 
       %>
    </BODY>
</HTML>

Filling a Table

<%@ page import="java.sql.*" %>
<HTML>
    <HEAD>
        <TITLE>Filling a Table</TITLE>
    </HEAD>
    <BODY>
        <H1>Filling a Table</H1>
        <%  
            Connection connection = null;
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            connection = DriverManager.getConnection("jdbc:odbc:data", "userName", "password");
            Statement statement = connection.createStatement();
            String command = "INSERT INTO Employees (ID, Name) VALUES (1, "Joe")";
            statement.executeUpdate(command);
            command = "INSERT INTO Employees (ID, Name) VALUES (2, "Yin")";
            statement.executeUpdate(command);
            ResultSet resultset = 
                statement.executeQuery("select * from Employees");
            while(resultset.next()){ 
        %>
            <TABLE BORDER="1">
                <TR>
                    <TH>ID</TH>
                    <TH>Name</TH>
                </TR>
                <TR>
                    <TD> <%= resultset.getString(1) %> </TD>
                    <TD> <%= resultset.getString(2) %> </TD>
                </TR>
            </TABLE>
        <% 
            } 
        %>
    </BODY>
</HTML>

Joining Tables

<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
<HTML>
    <HEAD>
        <TITLE>Joining Tables</TITLE>
    </HEAD>
    <BODY>
        <H1>Joining Tables</H1>
        <% 
            Connection connection = DriverManager.getConnection(
                "jdbc:odbc:data", "Steve", "password");
            Statement statement = connection.createStatement();
            String query = "SELECT a.fname, a.lname, titleauthor.id " +
              "FROM authors JOIN titleauthor ON authors.id = titleauthor.id";
            ResultSet resultset = 
                statement.executeQuery(query) ; 
        %>
        <TABLE BORDER="1">
            <TR>
                <TH>First Name</TH>
                <TH>Last Name</TH>
                <TH>Book ID</TH>
            </TR>
            <% while(resultset.next()){ %>
            <TR>
                <TD> <%= resultset.getString(1) %></TD>
                <TD> <%= resultset.getString(2) %></TD>
                <TD> <%= resultset.getString(3) %></TD>
            </TR>
            <% } %>
        </TABLE>
    </BODY>
</HTML>

JSP Access to Databases

/*
Beginning JavaServer Pages
Vivek Chopra, Jon Eaves, Rupert Jones, Sing Li, John T. Bell
ISBN: 0-7645-7485-X
*/

JSP Database Demo

JSP Database Query

JSTL: Transaction with a JSP

<%@ taglib uri="http://java.sun.ru/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.ru/jstl/sql" prefix="sql" %>
<html>
<HEAD>
      <TITLE>Using a Transaction with a JSP</TITLE>
     </HEAD>
<body bgcolor="white">
          <h2>View table Data</h2>
  
<sql:transaction>  
    
  <sql:update>
  insert into atable values(2, "Joe","Id","Feb-24-1996","F")
  </sql:update>
  
    <sql:query var="resultObj">
    select * from atable
    </sql:query>
</sql:transaction>  
<table>
<c:forEach items="${resultObj.rows}" var="row">
  <c:forEach items="${row}" var="column">
    <tr>
   <td align="right">
     <b><c:out value="${column.key}" /></b>
     </td>
     <td>
       <c:out value="${column.value}" />
     </td></tr>
     </c:forEach>
       </c:forEach>
     </table>
</body>
</html>

Navigating in a Database Table

<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
<HTML>
    <HEAD>
        <TITLE>Navigating in a Database Table </TITLE>
    </HEAD>
    <BODY>
        <H1>Navigating in a Database Table </H1>
        <FORM NAME="form1" ACTION="self.jsp" METHOD="POST">
        <% 
            int current = 1;
            if(request.getParameter("hidden") != null) {
                current = Integer.parseInt(request.getParameter("hidden"));
            }
            Connection connection = DriverManager.getConnection(
                "jdbc:odbc:data", "userName", "password");
            Statement statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
            ResultSet resultset = 
                statement.executeQuery("select * from tableName"); 
            if(current < 1){
                current = 1;
            }
            resultset.last();
            int rows = resultset.getRow();
            if(current <= rows){
                resultset.absolute(current);
            }    
        %>
            <TABLE BORDER="1">
                <TR>
                    <TH>ID</TH>
                    <TH>Name</TH>
                    <TH>City</TH>
                    <TH>State</TH>
                    <TH>Country</TH>
                </TR>
                <TR>
                    <TD> <%= resultset.getString(1) %> </TD>
                    <TD> <%= resultset.getString(2) %> </TD>
                    <TD> <%= resultset.getString(3) %> </TD>
                    <TD> <%= resultset.getString(4) %> </TD>
                    <TD> <%= resultset.getString(5) %> </TD>
                </TR>
            </TABLE>
            <BR>
            <INPUT TYPE="HIDDEN" NAME="hidden" VALUE="<%= current %>">
            <INPUT TYPE="BUTTON" VALUE="Next Record" ONCLICK="moveNext()">
            <INPUT TYPE="BUTTON" VALUE="Previous Record" ONCLICK="movePrevious()">
        </FORM>
        <SCRIPT LANGUAGE="JavaScript">
            <!--
            function moveNext()
            {
                var counter = 0
                counter = parseInt(document.form1.hidden.value) + 1
                document.form1.hidden.value = counter
                form1.submit()
            }    
            function movePrevious()
            {
                var counter = 0
                counter = parseInt(document.form1.hidden.value) - 1
                document.form1.hidden.value = counter
                form1.submit()
            }    
            // --> 
        </SCRIPT>
    </BODY>
</HTML>

Navigating in a Database Table 2

<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") ; %>
<HTML>
    <HEAD>
        <TITLE>Navigating in a Database Table </TITLE>
    </HEAD>
    <BODY>
        <H1>Navigating in a Database Table </H1>
        <FORM NAME="form1" ACTION="self.jsp" METHOD="POST">
        <% 
            int current = 0;
            if(request.getParameter("hidden") != null) {
                current = Integer.parseInt(request.getParameter("hidden"));
            }
            Connection connection = DriverManager.getConnection(
                "jdbc:odbc:data", "userName", "password");
            Statement statement = connection.createStatement();
            ResultSet resultset = 
                statement.executeQuery("select * from tableName"); 
            for(int i = 0; i <= current; i++){
                resultset.next();
            }
        %>
        <TABLE BORDER="1">
            <TR>
               <TH>ID</TH>
               <TH>Name</TH>
               <TH>City</TH>
               <TH>State</TH>
               <TH>Country</TH>
            </TR>
            <TR>
                <TD> <%= resultset.getString(1) %> </TD>
                <TD> <%= resultset.getString(2) %> </TD>
                <TD> <%= resultset.getString(3) %> </TD>
                <TD> <%= resultset.getString(4) %> </TD>
                <TD> <%= resultset.getString(5) %> </TD>
            </TR>
        </TABLE>
        <BR>
        <INPUT TYPE="HIDDEN" NAME="hidden" VALUE="<%= current %>">
        <INPUT TYPE="BUTTON" VALUE="Next Record" ONCLICK="moveNext()">
    </FORM>
    <SCRIPT LANGUAGE="JavaScript">
        <!--
            function moveNext()
            {
                var counter = 0
                counter = parseInt(document.form1.hidden.value) + 1
                document.form1.hidden.value = counter
                form1.submit()
            }    
        // --> 
    </SCRIPT>
</HTML>

New Address Creation using executeUpdate

//File: newAddress.html
<html>
<head>
<title>Add a new entry</title>
</head>
<body>
<h1>Please enter the new address information</h1>
<form method="POST" action="addNewAddress.jsp">
  Name: <input type="text" name="name" size="20"><br>
  Street: <input type="text" name="street" size="20"><br>
  City: <input type="text" name="city" size="20"><br>
  Zip Code: <input type="text" name="zip" size="20"><br>
  Country: <input type="text" name="country" size="20"><br>
  Telephone: <input type="text" name="tel" size="20">
  <p><input type="submit" value="Submit"></p>
</form>
</body>
</html>

//File: addNewAddress.jsp

<%@page import="java.sql.*"%>
<html>
<head>
<title>Add a new Address</title>
</head>
<body>
<h1>New Address Creation using executeUpdate()</h1>
<%
    Connection conn = null;
    PreparedStatement stmt = null;
    try {
      Class c = Class.forName("com.mysql.jdbc.Driver");
    }
    catch (Exception e) {
      System.out.println("Error occurred " + e);
     }
     try {
       conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ADDRESS");
     }
     catch (SQLException e) {
        System.out.println("Error occurred " + e);
     }
     try {
        stmt = conn.prepareStatement("INSERT INTO AddressList (name, street, city, zip, country, telephone) VALUES (?, ?, ?,?, ?, ?)");
        stmt.setString(1, request.getParameter("name"));
        stmt.setString(2, request.getParameter("street"));
        stmt.setString(3, request.getParameter("city"));
        stmt.setString(4, request.getParameter("zip"));
        stmt.setString(5, request.getParameter("country"));
        stmt.setString(6, request.getParameter("tel"));
        stmt.executeUpdate();
        stmt.close();
        conn.close();
     }
     catch (SQLException e) {
         System.out.println("Error occurred " + e);
      }
     finally {
      try {
        if (stmt != null)
         stmt.close();
        }  catch (SQLException e) {}
        try {
         if (conn != null)
         conn.close();
         } catch (SQLException e) {}
     }
   
%>
The new address has been created.
</body>
</html>

Obtaining a Connection in JSP

<%@page import="java.sql.*"%>
<html>
<head>
<title>Obtaining a Connection</title>
</head>
<body>
<h1>This Page Obtains a Connection to a Database and executes a query</h1>
The query is based upon a PreparedStatement
<%
    Connection conn = null;
    ResultSet result = null;
    PreparedStatement stmt = null;
    ResultSetMetaData rsmd = null;
    try {
      Class c = Class.forName("com.mysql.jdbc.Driver");
    }
    catch (Exception e) {
      System.out.println("Error occurred " + e);
     }
     try {
       conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ADDRESS");
     }
     catch (SQLException e) {
        System.out.println("Error occurred " + e);
     }
     try {
        stmt = conn.prepareStatement("SELECT * FROM AddressList WHERE name= ?");
        stmt.setString(1, "Alex");
        result = stmt.executeQuery();
        stmt.close();
        conn.close();
     }
     catch (SQLException e) {
         System.out.println("Error occurred " + e);
      }
      finally {
        try {
          if (stmt != null)
            stmt.close();
        }  catch (SQLException e) {}
        try {
          if (conn != null)
            conn.close();
        } catch (SQLException e) {}
      }
%>
</body>
</html>

Obtaining a database Connection

<%@page import="java.sql.*"%>
<html>
<head>
<title>Obtaining a Connection</title>
</head>
<body>
<h1>This Page Obtains a Connection to a Database and executes a query</h1>
<%
    Connection conn = null;
    ResultSet result = null;
    Statement stmt = null;

    try {
      Class c = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    }
    catch (Exception e) {
      System.out.println("Error occurred " + e);
     }
     try {
       conn = DriverManager.getConnection("jdbc:odbc:ADDRESS", "","");
     }
     catch (SQLException e) {
        System.out.println("Error occurred " + e);
     }
     try {
        stmt = conn.createStatement();
        result = stmt.executeQuery("SELECT * FROM ADDRESS");
     }
     catch (SQLException e) {
         System.out.println("Error occurred " + e);
      }
%>

Presenting database content

<%@page import="java.sql.*"%>
<html>
<head>
<title>Presenting database content</title>
</head>
<body>
<h1>Address List</h1>
<%
    Connection conn = null;
    ResultSet result = null;
    Statement stmt = null;
    ResultSetMetaData rsmd = null;
    try {
      Class c = Class.forName("com.mysql.jdbc.Driver");
    }
    catch (Exception e) {
      System.out.println("Error occurred " + e);
     }
     try {
       conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ADDRESS");
     }
     catch (SQLException e) {
        System.out.println("Error occurred " + e);
     }
     try {
        stmt = conn.createStatement();
        result = stmt.executeQuery("SELECT * FROM AddressList");
     }
     catch (SQLException e) {
         System.out.println("Error occurred " + e);
      }
     int columns=0;
     try {
       rsmd = result.getMetaData();
       columns = rsmd.getColumnCount();
     }
     catch (SQLException e) {
        System.out.println("Error occurred " + e);
     }
%>
<table width="90%" border="1">
  <tr>
  <% // write out the header cells containing the column labels
     try {
        for (int i=1; i<=columns; i++) {
             out.write("<th>" + rsmd.getColumnLabel(i) + "</th>");
        }
  %>
  </tr>

  <% // now write out one row for each entry in the database table
        while (result.next()) {
           out.write("<tr>");
           for (int i=1; i<=columns; i++) {
             out.write("<td>" + result.getString(i) + "</td>");
           }
           out.write("</tr>");
        }
        // close the connection and the statement
        stmt.close();
        conn.close();
     } // end of the try block
     catch (SQLException e) {
        System.out.println("Error " + e);
     }
     // ensure everything is closed
   finally {
    try {
      if (stmt != null)
       stmt.close();
      }  catch (SQLException e) {}
      try {
       if (conn != null)
        conn.close();
       } catch (SQLException e) {}
   }
   %>
</table>
</body>
</html>

Presenting database content using tags

//web.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
  "http://java.sun.ru/dtd/web-app_2_3.dtd">
<web-app>
  <resource-ref>
    <res-ref-name>jdbc/address</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>
</web-app>


<%@ taglib prefix="sql" uri="http://java.sun.ru/jstl/sql" %>
<%@ taglib prefix="c" uri="http://java.sun.ru/jstl/core" %>
<html>
<head>
<title>Presenting database content using tags</title>
<sql:setDataSource
  dataSource="jdbc/address"
  var="conn"
/>
</head>
<body>
<h1>Address List</h1>
<sql:query dataSource="${conn}" var="addresses">
    SELECT * FROM AddressList
</sql:query>
<table width="90%" border="1">
<tr>
<!-- add the table column headings -->
<c:forEach var="columnName" items="${addresses.columnNames}">
  <th> <c:out value="${columnName}"/> </th>
</c:forEach>
</tr>
<!-- add the table rows from the result set -->
<c:forEach var="row" items="${addresses.rowsByIndex}">
  <tr>
    <c:forEach var="column" items="${row}">
      <td><c:out value="${column}"/></td>
    </c:forEach>
  </tr>
</c:forEach>
</table>
</body>
</html>

Selecting records with condition From a Database

<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
<HTML>
    <HEAD>
        <TITLE>Selecting tableName From a Database</TITLE>
    </HEAD>
    <BODY>
        <H1>Selecting tableName From a Database</H1>
        <% 
            Connection connection = DriverManager.getConnection(
                "jdbc:odbc:data", "userName", "password");
            Statement statement = connection.createStatement() ;
            ResultSet resultset = 
                statement.executeQuery("select * from tableName where city = "Boston"") ; 
        %>
        <TABLE BORDER="1">
            <TR>
                <TH>ID</TH>
                <TH>Name</TH>
                <TH>City</TH>
                <TH>State</TH>
                <TH>Country</TH>
            </TR>
            <% while(resultset.next()){ %>
            <TR>
                <TD> <%= resultset.getString(1) %></td>
                <TD> <%= resultset.getString(2) %></TD>
                <TD> <%= resultset.getString(3) %></TD>
                <TD> <%= resultset.getString(4) %></TD>
                <TD> <%= resultset.getString(5) %></TD>
            </TR>
            <% } %>
        </TABLE>
    </BODY>
</HTML>

Updating a database using the sql:update tag

//web.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
  "http://java.sun.ru/dtd/web-app_2_3.dtd">
<web-app>
  <resource-ref>
    <res-ref-name>jdbc/address</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>
</web-app>

<%@ taglib prefix="sql" uri="http://java.sun.ru/jstl/sql" %>
<%@ taglib prefix="c" uri="http://java.sun.ru/jstl/core" %>
<html>
<head>
<title>Updating a database using the sql:update tag</title>
<sql:setDataSource
  var="conn"
  dataSource="jdbc/address"
/>
</head>
<body>
<h1>Modify Address List</h1>
<sql:update dataSource="${conn}" var="addresses">
    INSERT INTO AddressList (name, street, city, country, telephone) VALUES (?, ?, ?, ?, ?)
    <sql:param value="${param["name"]}"/>
    <sql:param value="${param["street"]}"/>
    <sql:param value="${param["city"]}"/>
    <sql:param value="${param["country"]}"/>
    <sql:param value="${param["tel"]}"/>
</sql:update>
</body>
</html>

Using a DataSource

<%@page import="java.sql.*, javax.sql.*, javax.naming.*"%>
<html>
<head>
<title>Using a DataSource</title>
</head>
<body>
<h1>Using a DataSource</h1>
<%
    DataSource ds = null;
    Connection conn = null;
    ResultSet result = null;
    Statement stmt = null;
    ResultSetMetaData rsmd = null;
    try{
      Context context = new InitialContext();
      Context envCtx = (Context) context.lookup("java:comp/env");
      ds =  (DataSource)envCtx.lookup("jdbc/address");
      if (ds != null) {
        conn = ds.getConnection();
        stmt = conn.createStatement();
        result = stmt.executeQuery("SELECT * FROM AddressList");
       }
     }
     catch (SQLException e) {
        System.out.println("Error occurred " + e);
      }
      int columns=0;
      try {
        rsmd = result.getMetaData();
        columns = rsmd.getColumnCount();
      }
      catch (SQLException e) {
         System.out.println("Error occurred " + e);
      }
 %>
 <table width="90%" border="1">
   <tr>
   <% // write out the header cells containing the column labels
      try {
         for (int i=1; i<=columns; i++) {
              out.write("<th>" + rsmd.getColumnLabel(i) + "</th>");
         }
   %>
   </tr>
   <% // now write out one row for each entry in the database table
         while (result.next()) {
            out.write("<tr>");
            for (int i=1; i<=columns; i++) {
              out.write("<td>" + result.getString(i) + "</td>");
            }
            out.write("</tr>");
         }
 
         // close the connection, resultset, and the statement
         result.close();
         stmt.close();
         conn.close();
      } // end of the try block
      catch (SQLException e) {
         System.out.println("Error " + e);
      }
      // ensure everything is closed
    finally {
     try {
       if (stmt != null)
        stmt.close();
       }  catch (SQLException e) {}
       try {
        if (conn != null)
         conn.close();
        } catch (SQLException e) {}
    }
 
    %>
</table>
</body>
</html>

Using a Preconfigured DataSource

//web.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
  "http://java.sun.ru/dtd/web-app_2_3.dtd">
<web-app>
  <resource-ref>
    <res-ref-name>jdbc/address</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>
</web-app>

<%@ taglib prefix="sql" uri="http://java.sun.ru/jstl/sql" %>
<%@ taglib prefix="c" uri="http://java.sun.ru/jstl/core" %>
<html>
<head>
<title>Using a Preconfigured DataSource</title>
</head>
<body>
<h1>Address List</h1>
<sql:query dataSource="jdbc/address" var="addresses">
    SELECT * FROM AddressList
</sql:query>
<table width="90%" border="1">
<tr>
<!-- add the table column headings -->
<c:forEach var="columnName" items="${addresses.columnNames}">
  <th> <c:out value="${columnName}"/> </th>
</c:forEach>
</tr>
<!-- add the table rows from the result set -->
<c:forEach var="row" items="${addresses.rowsByIndex}">
  <tr>
    <c:forEach var="column" items="${row}">
      <td><c:out value="${column}"/></td>
    </c:forEach>
  </tr>
</c:forEach>
</table>
</body>
</html>

Using a Result object

<%@ taglib uri="http://java.sun.ru/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.ru/jstl/sql" prefix="sql" %>
<html>
<HEAD>
      <TITLE>Using a Result object</TITLE>
     </HEAD>
<body bgcolor="white">
<h2>View Database Data</h2>
<c:set var="resultObj" value="${sessionScope[\"javax.servlet.jsp.jstl.sql.Result\"]}" />
<table border="1" cellspacing="2">
<c:forEach items="${resultObj.rows}" var="row">
  <c:forEach items="${row}" var="column">
    <tr>
     <td align="right">
       <b><c:out value="${column.key}" /></b>
       </td>
       <td>
         <c:out value="${column.value}" />
         </td></tr>
       </c:forEach>
         </c:forEach>
         </table>
</body>
</html>

Using Table Metadata

<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
<HTML>
    <HEAD>
        <TITLE>Using Table Metadata</TITLE>
    </HEAD>
    <BODY>
        <H1>Using Table Metadata</H1>
        <% 
            Connection connection = DriverManager.getConnection(
                "jdbc:odbc:data", "userName", "password");
            Statement statement = connection.createStatement() ;
            ResultSet resultset = 
                statement.executeQuery("select * from tableName") ; 
        %>
        <TABLE BORDER="1">
            <TR>
                <TH><%= resultset.getMetaData().getColumnName(1)%></TH>
                <TH><%= resultset.getMetaData().getColumnName(2)%></TH>
                <TH><%= resultset.getMetaData().getColumnName(3)%></TH>
                <TH><%= resultset.getMetaData().getColumnName(4)%></TH>
                <TH><%= resultset.getMetaData().getColumnName(5)%></TH>
            </TR>
            <% while(resultset.next()){ %>
            <TR>
                <TD> <%= resultset.getString(1) %></td>
                <TD> <%= resultset.getString(2) %></TD>
                <TD> <%= resultset.getString(3) %></TD>
                <TD> <%= resultset.getString(4) %></TD>
                <TD> <%= resultset.getString(5) %></TD>
            </TR>
            <% } %>
        </TABLE>
    </BODY>
</HTML>

Using the SortedMap

<%@ taglib prefix="sql" uri="http://java.sun.ru/jstl/sql" %>
<%@ taglib prefix="c" uri="http://java.sun.ru/jstl/core" %>
<html>
<head>
<title>Using the SortedMap[] </title>
</head>
<body>
<h1>Address List</h1>
<sql:setDataSource dataSource="jdbc/address" var="conn" />
<sql:query dataSource="${conn}" var="addresses">
    SELECT * FROM AddressList
</sql:query>

<table width="90%" border="1">
<!-- add the table rows from the result set -->
<c:forEach var="row" items="${addresses.rows}">
  <tr>
    <c:forEach var="column" items="${row}">
      <td><c:out value="${column}"/></td>
    </c:forEach>
  </tr>
</c:forEach>
</table>
</body>
</html>

Using Transactions

//web.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
  "http://java.sun.ru/dtd/web-app_2_3.dtd">
<web-app>
  <resource-ref>
    <res-ref-name>jdbc/address</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>
</web-app>

<%@ taglib prefix="sql" uri="http://java.sun.ru/jstl/sql" %>
<%@ taglib prefix="c" uri="http://java.sun.ru/jstl/core" %>
<html>
<head>
<title>Using Transactions</title>
<sql:setDataSource
  var="conn"
  dataSource="jdbc/address"
/>
</head>
<body>
<h1>Modify Address List</h1>
<sql:transaction dataSource="${conn}" isolation="TRANSACTION_SERIALIZABLE">
  <sql:update>
      INSERT INTO AddressList (name, street, city, country, telephone) VALUES (?, ?, ?, ?, ?)
      <sql:param value="${param["name"]}"/>
      <sql:param value="${param["street"]}"/>
      <sql:param value="${param["city"]}"/>
      <sql:param value="${param["country"]}"/>
      <sql:param value="${param["tel"]}"/>
  </sql:update>
  <sql:query var="addresses">
      SELECT * FROM AddressList
  </sql:query>
  <table width="90%" border="1">
  <tr>
  <!-- add the table column headings -->
  <c:forEach var="columnName" items="${addresses.columnNames}">
    <th> <c:out value="${columnName}"/> </th>
  </c:forEach>
  </tr>
  <!-- add the table rows from the result set -->
  <c:forEach var="row" items="${addresses.rowsByIndex}">
    <tr>
      <c:forEach var="column" items="${row}">
        <td><c:out value="${column}"/></td>
      </c:forEach>
    </tr>
  </c:forEach>
  </table>
</sql:transaction>
</body>
</html>

Java/JSP/Database — различия между версиями

Текущая версия на 07:10, 1 июня 2010

Содержание

Accessing the table field in Database

A First JSP Database

Calling a Stored procedure within a JSP

Creating a Table

Display table in Database

Fetching Data From a Database

Filling a Table

Joining Tables

JSP Access to Databases

JSP Database Demo

JSP Database Query

JSTL: Transaction with a JSP

Navigating in a Database Table

Navigating in a Database Table 2

New Address Creation using executeUpdate

Obtaining a Connection in JSP

Obtaining a database Connection

Presenting database content

Presenting database content using tags

Selecting records with condition From a Database

Updating a database using the sql:update tag

Using a DataSource

Using a Preconfigured DataSource

Using a Result object

Using Table Metadata

Using the SortedMap

Using Transactions

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты

Версия 18:01, 31 мая 2010 (просмотреть исходный код) (обсуждение)	Текущая версия на 07:10, 1 июня 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)
(нет различий)