Java Tutorial/JSP/Database
Содержание
- 1 Accessing the Database Table
- 2 Accessing the Publishers Database Table
- 3 Food Groups
- 4 Creating a Table
- 5 Navigating in a Database Table
- 6 Filling a Table
- 7 Joining Tables
- 8 The Publishers Database Table
- 9 Navigating in a Database Table
- 10 Selecting Publishers From a Database
- 11 Database Lookup
- 12 Using Table Metadata
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") ; %>
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
Code | Description |
---|---|
<%= 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>
<source lang="java">
<%@ page import="java.sql.*" %> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") ; %> <HTML>
<HEAD> <TITLE>Navigating in a Database Table </TITLE> </HEAD> <BODY>
<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) ; %>
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") ; %>
ID | Name | City | State | Country |
---|---|---|---|---|
<%= resultset.getString(1) %> | <%= resultset.getString(2) %> | <%= resultset.getString(3) %> | <%= resultset.getString(4) %> | <%= resultset.getString(5) %> |
</BODY>
</HTML></source>
<source lang="java">
<%@ page import="java.sql.*" %> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %> <HTML>
<HEAD> <TITLE>Navigating in a Database Table </TITLE> </HEAD> <BODY>
<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"") ; %>
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") ; %>
<%= 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>