Java Tutorial/JSP/Database
Содержание
- 1 Accessing the Database Table
- 2 Connect to Database in Servlet
- 3 Creating a Table
- 4 Database Table Navigation Based On Form
- 5 Insert data to a table
- 6 Joining Tables
- 7 Make Database connection
- 8 Navigate Database Table With Javascript and JSP
- 9 Output data in database as XML
- 10 Output ResultSet
- 11 Retrieve data in Database based on form input
- 12 Using Table Metadata
Accessing the Database Table
<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") ; %>
<HTML>
<HEAD>
<TITLE>Accessing the Publishers Database Table</TITLE>
</HEAD>
<BODY>
<H1>Accessing the Publishers Database Table</H1>
<%
Connection connection = DriverManager.getConnection(
"jdbc:odbc:data", "UserName", "password");
Statement statement = connection.createStatement() ;
ResultSet resultset = statement.executeQuery("select pub_name from Publishers") ;
%>
<TABLE BORDER="1">
<TR>
<TH>Name</TH>
</TR>
<% while(resultset.next()){ %>
<TR>
<TD>
<%= resultset.getString(1)%>
</TD>
</TR>
<% } %>
</TABLE>
</BODY>
</HTML>
Connect to Database in Servlet
Jsp page
<%--
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>
<h1>Food Groups</h1>
<table border="1" cellpadding="3" cellspacing="0">
<tr><TH>Code</th><TH>Description</th></tr>
<%
// 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);
%>
<tr>
<td><%= code %></td>
<td><%= desc %></td>
</tr>
<%
}
// Close the database objects
rs.close();
stmt.close();
%>
</table>
</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", "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>
<%@ 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="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();
}
%>
<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>
Insert data to 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", "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()){
%>
<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", "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) ;
%>
<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>
Make Database connection
<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
<HTML>
<HEAD>
<TITLE>The Publishers Database Table </TITLE>
</HEAD>
<BODY>
<H1>The Publishers Database Table </H1>
<%
Connection connection = DriverManager.getConnection(
"jdbc:odbc:data", "userName", "password");
Statement statement = connection.createStatement() ;
ResultSet resultset = statement.executeQuery("select * from Publishers") ;
%>
<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>
<%@ 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="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);
}
%>
<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>
Output data in database as XML
<%@ 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>
Output ResultSet
<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
<HTML>
<HEAD>
<TITLE>Selecting Publishers From a Database</TITLE>
</HEAD>
<BODY>
<H1>Selecting Publishers From a Database</H1>
<%
Connection connection = DriverManager.getConnection(
"jdbc:odbc:data", "userName", "password");
Statement statement = connection.createStatement() ;
ResultSet resultset = statement.executeQuery("select * from Publishers 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>
Retrieve data in Database based on form input
index.jsp
<HTML>
<HEAD>
<TITLE>Database Lookup</TITLE>
</HEAD>
<BODY>
<H1>Database Lookup</H1>
<FORM ACTION="basic.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>
</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", "YourName", "password");
Statement statement = connection.createStatement() ;
ResultSet resultset = statement.executeQuery("select * from Publishers") ;
%>
<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>
