Java程序辅导

1[This reading continues the reading accessingDB-UsingPHP
* * * ********** * * *
Getting data from MySQL using Java servlets
Here are two examples.  Later in the term, I’ll proved a far more robust example that you’ll 
find helpful in real life.
In this example, line numbers are used to help you follow the code.  Don’t type them in your own 
file.  Be encouraged to try this code. Use a text editor (not a word processing program) and type 
the file line by line.  You must name the file the same name as that in the “public class” statement 
[line x].  Note the location of your file when you save it.  Then start a terminal window and 
navigate to that folder so the folder that contains your file is the current working folder (in 
Unix).  
Enter the command javac yourfileName.java and press the enter key.  For 
example, this program is called “xmlGenericServlet” and so is stored in a file called 
“xmlGenericServlet.java”.  When compiling, type javac 
xmlGenericServlet.java and press the enter key.  If the Java Compiler was not installed 
on the computer, you’ll see an error message.  If the compiler is installed and you made a typo, 
you’ll see an error message that includes the number of the line in your code that is wrong.  Fix it 
and try again!  Ultimately you’ll have code that works. … but we have a few things to do first 
before running it live on the web server.  [Advanced or adventurous students may have installed 
Apache on their own computer (it’s built into the Mac but you can download it, too, on Macs 
and any computer).  See the instructions about setting up your own web server software from 
the class homepage.  Comments and notes about your program that’s stored in the program are 
shielded from the compiler by using either //  (double slashes) for a single line or /*  …  */ for a 
single or multiple lines of comments.
To run this program on the server, your user account will have to have read/write 
permissions on the folder.  Usually creating a subfolder under your publicly-viewable folder on 
the webserver is the best way to store files.  Permissions on the server are not controlled by the 
professor - if there’s a problem speak to the GSLIS Lab Staff.
/* IMPORT THE LIBRARIES NEEDED FOR THIS PROJECT */
1 import java.net.*;
2 import java.sql.*;
3 import java.awt.*;
4 import java.awt.event.*;
5 import java.io.*;
6 import javax.servlet.*;
/* DECLARE THE CLASS AND WHAT YOUR CLASSES IS BUILT UPON (extends) */
8 public class xmlGenericServlet extends GenericServlet {
9 /* declare the variables needed to connect and communicate with SQL */
10 Connection connection;
11 Statement statement;
23
12 /* **************************************
13 /* Variables for database access
14 /* ************************************** */
15 String dbName = "userAccts";
16 String userName = "";
17 String password = "";
18 String realPath = "";
19 String server = "";
20 public void service(ServletRequest request,
21 ServletResponse response) 
22 throws ServletException, IOException {
23 /* --------------------------------------
24 /* What is the server's name?
25 /* --------------------------------------
26 server = request.getServerName();
27 realPath = getServletContext().getRealPath("/"); 
28 // looks like /var/gslis/tomcat5/webapps/xmlDemo/
29 // NOTE! DOES NOT WORK WITH SYMBOLIC LINKS! and Simmons GSLIS
30 // USES symbolic links so we have to be klunky and hard code!
31 /* **************************************
32 /* Open connection back to source (browser)
33 /* Get ready to send (to print) data in browser
34 /* ************************************** */
35 response.setContentType("application/xml");
36 PrintWriter pw = response.getWriter();
37 /* ---------------------------------------
38 /* NOTE!!! 
39 /* There are much more efficient ways of parsing
40 /* XML (using XMLFactories) and better ways of 
41 /* sending data via the server - ServletOutputStream
42 /* this code is just an example emphasizing DB connectivity
43 /* We’ll have an example of this, too.
44 /* --------------------------------------- */
45 /* **************************************
46 /* Hardcode the XML declaration and our
47 /* tags.  Print the tag; get the data from
48 /* a database.
49 /* ************************************** */
50 /* ---------------------------------------
51 /* Send the XML declaration and our root
52 /* --------------------------------------- */
53 pw.println("");
54 pw.println("");
55 /* **************************************
56 /* Connect to a MySQL database table 
57 /* ************************************** */
58 try {
23
4
59 /* THIS IS IMPORTANT */
60 /* ---------------------------------------
61 /* Load the correct driver so the web server can
62 /* communicate with the database
63 /* For MS Access use:
64 /* Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
65 /* connection = DriverManager.getConnection
66 /* "jdbc:odbc:tableName",
67 /* "username",
68 /* "password"
69 /*  Replace "tableName" with the real table;
70 /*  Replace "username" and "password" with the real ones.
71 /* --------------------------------------- */
72 Class.forName("com.mysql.jdbc.Driver").newInstance();
73 /* ---------------------------------------
74 /*  Create a connection to the database:
75 /*  Here we’re using jdbc to connect to mysql.
76 /*  Use odbc if you’re using MS Access - but you’ll need
77 /*  a bridge between odbc and jdbc (not described here)
78 /*  We prepare for the data by creating a “Statement”
79 /*  that is the bridge for our SQL request to communicate
80 /*  with the database itself.
81 /* --------------------------------------- */
82 connection = DriverManager.getConnection("jdbc:mysql://" + 
83 server +":3306/"+dbName, userName, password );
84 statement = connection.createStatement();
85 ResultSet rs = statement.executeQuery("SELECT * FROM students");
86 /* ---------------------------------------
87 /* cycle through all the results.  
88 /* Wrap them in xml and print on browser but you could easily
89 /* use html: e.g.,  “” + rs.getString(“lname”) + “”);
90 /* --------------------------------------- */
91 while (rs.next()) {
92 pw.println("" + rs.getString(1) + "");
93 }
94 /* ---------------------------------------
95 /* No more data; show final tag.
96 /* --------------------------------------- */
97 pw.print("");
98 } catch (Exception e) {
99 pw.println("An error has occurred.");
100 if (e instanceof SQLException) {
101 SQLException sqlex3 = (SQLException)e;
102 pw.print("SQL Error code = "+sqlex3.getErrorCode());
103 }
104 /* ---------------------------------------
105 /* Close the connections.  
106 /* --------------------------------------- */
107 } finally {
34
5
108 if (connection != null) {
109 try {
110 connection.close();
111 } catch (Exception onclose) {
112 }
113 }
114 }
115 }
116 }
*********************
Finally, both students of relational databases and of XML need to know that both 
technologies work together for real-world information system solutions.  Above we passed a 
single parameter (“season=Summer”).  We can pass other data.  In this example, the data 
passed are names of files (.xml and .xsl) that are processed the same way on the web server 
as DB transactions are performed - the servlet does all the work.  Note, too, that XML may 
rely on temporary files the same way that many database solutions require creating a 
temporary database table, integrating those results with html or xml, sending them back to 
the user (or writing to a file), and then deleting the temporary files.
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.stream.StreamSource;
import javax.xml.transform.stream.StreamResult;
import java.io.File;
import java.io.PrintWriter;
import java.io.IOException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class MyTransform extends HttpServlet {
public void doGet(HttpServletRequest req, 
HttpServletResponse res)
throws IOException {
/* NOTE: because gslis.simmons.edu uses symbolic links we can't use the function 
that lets our code be  independent of the server name:
String realPath = getServletContext().getRealPath("/");
So, in this situation, we must actually hardcode the path!
*/
String realPath ="/gslis/tomcat5/webapps/courses/";
45
res.setContentType("text/html");
String docName = req.getParameter("docName");
String sheetName = req.getParameter("sheetName");
TransformerFactory factory;
Transformer transformer;
File oldFile = null;
File xslFile = null;
PrintWriter out = null;
StreamSource oldStream = null;
StreamSource xslStream = null;
StreamResult newStream = null;
factory = TransformerFactory.newInstance();
try {
oldFile = new File(realPath+docName);
// oldFile should look like "myfile.xml"
oldStream = new StreamSource(oldFile);
xslFile = new File(realPath+sheetName);
// sheetName should look like "mysheet.xsl"
xslStream = new StreamSource(xslFile);
out = res.getWriter();
newStream = new StreamResult(out);
transformer = factory.newTransformer(xslStream);
transformer.transform(oldStream, newStream);
}
catch (TransformerException e) {
System.out.println(e.getMessage());
}
}
}
filename: accessingDB-UsingJava.rtf
Updated: Jan 2, 2012
[Original: April 11, 2009, 3 pm; Updated Jan 2, 2012]
ZZZZZZZZZZZZZZZZZ