Java Tutorial/JSP/Database

Материал из Java эксперт
Перейти к: навигация, поиск

Accessing the Database Table

   <source lang="java">

<%@ page import="java.sql.*" %> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") ; %> <HTML> 

   <HEAD>
       <TITLE>Accessing the Publishers Database Table</TITLE>
   </HEAD>
   <BODY>

Accessing the Publishers Database Table

       <% 
           Connection connection = DriverManager.getConnection(
               "jdbc:odbc:data", "UserName", "password");
               Statement statement = connection.createStatement() ;
               ResultSet resultset = statement.executeQuery("select pub_name from Publishers") ; 
       %>
<% while(resultset.next()){ %> <% } %>
Name 
                       <%= resultset.getString(1)%>  

   </BODY>

</HTML></source>





Connect to Database in Servlet

Jsp page



   <source lang="java">

<%-- 

 Copyright (c) 2002 by Phil Hanna
 All rights reserved.
 
 You may study, use, modify, and distribute this
 software for any purpose provided that this
 copyright notice appears in all copies.
 
 This software is provided without warranty
 either expressed or implied.

--%> <%@ page extends="com.jspcr.servlets.NutrientDatabaseServlet" %> <%-- 

  This JSP page subclasses the NutrientDatabaseServlet
  parent class, which automatically loads the
  database driver and establishes the connection.

--%> <%@ page import="java.io.*,java.sql.*" %> <html> <head> <title>Food Groups</title> </head> <body>

Food Groups

<% 

  // Execute a query
  Statement stmt = con.createStatement();
  String sql = "SELECT * FROM FD_GROUP ORDER BY FDGP_DESC";
  ResultSet rs = stmt.executeQuery(sql);
  while (rs.next()) {
     String code = rs.getString(1);
     String desc = rs.getString(2);

%>

<% } // Close the database objects rs.close(); stmt.close(); %>
CodeDescription
<%= code %> <%= desc %>

</body> </html></source>





Creating a Table

   <source lang="java">

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

   <HEAD>
       <TITLE>Creating a Table</TITLE>
   </HEAD>
   <BODY>

Creating a Table

       <%  
           Connection connection = null;
           try {
               Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
               connection = DriverManager.getConnection("jdbc:odbc:data", "YourName", "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></source>





Database Table Navigation Based On Form

   <source lang="java">

<%@ page import="java.sql.*" %> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") ; %> <HTML> 

   <HEAD>
       <TITLE>Navigating in a Database Table </TITLE>
   </HEAD>
   <BODY>

Navigating in a Database Table

       <FORM NAME="form1" ACTION="basic.jsp" METHOD="POST">
       <% 
           int current = 0;
           if(request.getParameter("hidden") != null) {
               current = Integer.parseInt(request.getParameter("hidden"));
           }
           Connection connection = DriverManager.getConnection(
               "jdbc:odbc:data", "YourName", "password");
           Statement statement = connection.createStatement();
           ResultSet resultset = 
               statement.executeQuery("select * from Publishers"); 
           for(int i = 0; i <= current; i++){
               resultset.next();
           }
       %>
ID Name City State Country
<%= resultset.getString(1) %> <%= resultset.getString(2) %> <%= resultset.getString(3) %> <%= resultset.getString(4) %> <%= resultset.getString(5) %> 
       

       <INPUT TYPE="HIDDEN" NAME="hidden" VALUE="<%= current %>">
       <INPUT TYPE="BUTTON" VALUE="Next Record" ONCLICK="moveNext()">
   </FORM>
   <SCRIPT LANGUAGE="JavaScript">
        
   </SCRIPT>

</HTML></source>





Insert data to a table

   <source lang="java">

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

   <HEAD>
       <TITLE>Filling a Table</TITLE>
   </HEAD>
   <BODY>

Filling a Table

       <%  
           Connection connection = null;
           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
           connection = DriverManager.getConnection("jdbc:odbc:data", "YourName", "password");
           Statement statement = connection.createStatement();
           String command = "INSERT INTO Employees (ID, Name) VALUES (1, "Tom")";
           statement.executeUpdate(command);
           command = "INSERT INTO Employees (ID, Name) VALUES (2, "Peter")";
           statement.executeUpdate(command);
           ResultSet resultset = statement.executeQuery("select * from Employees");
           while(resultset.next()){ 
       %>
ID Name
<%= resultset.getString(1) %> <%= resultset.getString(2) %> 
       <% 
           } 
       %>
   </BODY>

</HTML></source>





Joining Tables

   <source lang="java">

<%@ page import="java.sql.*" %> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %> <HTML> 

   <HEAD>
       <TITLE>Joining Tables</TITLE>
   </HEAD>
   <BODY>

Joining Tables

       <% 
           Connection connection = DriverManager.getConnection(
               "jdbc:odbc:data", "YourName", "password");
           Statement statement = connection.createStatement();
           String query = "SELECT authors.au_fname, authors.au_lname, titleauthor.title_id " +
             "FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id";
           ResultSet resultset = 
               statement.executeQuery(query) ; 
       %>
<% while(resultset.next()){ %> <% } %>
First Name Last Name Book ID
<%= resultset.getString(1) %> <%= resultset.getString(2) %> <%= resultset.getString(3) %> 
   </BODY>

</HTML></source>





Make Database connection

   <source lang="java">

<%@ page import="java.sql.*" %> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %> <HTML> 

   <HEAD>
       <TITLE>The Publishers Database Table </TITLE>
   </HEAD>
   <BODY>

The Publishers Database Table

       <% 
           Connection connection = DriverManager.getConnection(
               "jdbc:odbc:data", "userName", "password");
           Statement statement = connection.createStatement() ;
           ResultSet resultset = statement.executeQuery("select * from Publishers") ; 
       %>
<% while(resultset.next()){ %> <% } %>
ID Name City State Country
<%= resultset.getString(1) %> <%= resultset.getString(2) %> <%= resultset.getString(3) %> <%= resultset.getString(4) %> <%= resultset.getString(5) %> 
   </BODY>

</HTML></source>





Navigate Database Table With Javascript and JSP

   <source lang="java">

<%@ page import="java.sql.*" %> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %> <HTML> 

   <HEAD>
       <TITLE>Navigating in a Database Table </TITLE>
   </HEAD>
   <BODY>

Navigating in a Database Table

       <FORM NAME="form1" ACTION="basic.jsp" METHOD="POST">
       <% 
           int current = 1;
           if(request.getParameter("hidden") != null) {
               current = Integer.parseInt(request.getParameter("hidden"));
           }
           Connection connection = DriverManager.getConnection(
               "jdbc:odbc:data", "YourName", "password");
           Statement statement = connection.createStatement(
               ResultSet.TYPE_SCROLL_INSENSITIVE,
               ResultSet.CONCUR_READ_ONLY);
           ResultSet resultset = statement.executeQuery("select * from Publishers"); 
           if(current < 1){
               current = 1;
           }
           resultset.last();
           int rows = resultset.getRow();
           if(current <= rows){
               resultset.absolute(current);
           }    
       %>
ID Name City State Country
<%= resultset.getString(1) %> <%= resultset.getString(2) %> <%= resultset.getString(3) %> <%= resultset.getString(4) %> <%= resultset.getString(5) %> 
           

           <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">
            
       </SCRIPT>
   </BODY>

</HTML></source>





Output data in database as XML

   <source lang="java">

<%@ page contentType="application/xml" import="java.sql.*" %> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") ; %> <?xml version="1.0"?> <publishers> <% Connection connection = DriverManager.getConnection( 

   "jdbc:odbc:data", "YourName", "password");

Statement statement = connection.createStatement() ; ResultSet resultset = statement.executeQuery("select name, id from Publishers");%> 

   <% 
       while(resultset.next()){ 
   %>
   <publisher>
   <<%=resultset.getMetaData().getColumnName(1)%>> 
       <% 
           String s = resultset.getString(1);
           int index = s.indexOf("&");
           StringBuffer sb = new StringBuffer(s);
           if(index > 0){
               sb.replace(index, index + 1, "and");
           }
           out.println(sb);
       %>  
   </<%=resultset.getMetaData().getColumnName(1)%>> 
   <<%=resultset.getMetaData().getColumnName(2)%>> 
           <%= resultset.getString(2) %>
   </<%=resultset.getMetaData().getColumnName(2)%>> 
   </publisher>
   <% 
       } 
   %>

</publishers></source>





Output ResultSet

   <source lang="java">

<%@ page import="java.sql.*" %> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %> <HTML> 

   <HEAD>
       <TITLE>Selecting Publishers From a Database</TITLE>
   </HEAD>
   <BODY>

Selecting Publishers From a Database

       <% 
           Connection connection = DriverManager.getConnection(
               "jdbc:odbc:data", "userName", "password");
           Statement statement = connection.createStatement() ;
           ResultSet resultset = statement.executeQuery("select * from Publishers where city = "Boston"") ; 
       %>
<% while(resultset.next()){ %> <% } %>
ID Name City State Country
<%= resultset.getString(1) %> <%= resultset.getString(2) %> <%= resultset.getString(3) %> <%= resultset.getString(4) %> <%= resultset.getString(5) %> 
   </BODY>

</HTML></source>





Retrieve data in Database based on form input

index.jsp



   <source lang="java">

<HTML> 

   <HEAD>
       <TITLE>Database Lookup</TITLE>
   </HEAD>

   <BODY>

Database Lookup

       <FORM ACTION="basic.jsp" METHOD="POST">
           Please enter the ID of the publisher you want to find:
           

           <INPUT TYPE="TEXT" NAME="id">
           

           <INPUT TYPE="SUBMIT" value="Submit">
       </FORM>
   </BODY>

<HTML></source>





Using Table Metadata

   <source lang="java">

<%@ page import="java.sql.*" %> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %> <HTML> 

   <HEAD>
       <TITLE>Using Table Metadata</TITLE>
   </HEAD>
   <BODY>

Using Table Metadata

       <% 
           Connection connection = DriverManager.getConnection(
               "jdbc:odbc:data", "YourName", "password");
           Statement statement = connection.createStatement() ;
           ResultSet resultset = statement.executeQuery("select * from Publishers") ; 
       %>
<% while(resultset.next()){ %> <% } %>
<%= resultset.getMetaData().getColumnName(1)%> <%= resultset.getMetaData().getColumnName(2)%> <%= resultset.getMetaData().getColumnName(3)%> <%= resultset.getMetaData().getColumnName(4)%> <%= resultset.getMetaData().getColumnName(5)%>
<%= resultset.getString(1) %> <%= resultset.getString(2) %> <%= resultset.getString(3) %> <%= resultset.getString(4) %> <%= resultset.getString(5) %> 
   </BODY>

</HTML></source>


Источник — «http://jexp.ru/index.php?title=Java_Tutorial/JSP/Database&oldid=5024»