Getting data from MySQL using Java servlets

glueblacksmithInternet και Εφαρμογές Web

13 Νοε 2013 (πριν από 3 χρόνια και 10 μήνες)

66 εμφανίσεις

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

nd 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;
2
3
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("<?xml version=\"1.0\"?>");
54
pw.println("<document>");
55
/* **************************************
56
/* Connect to a MySQL database table
57
/* ************************************** */
58
try {
2
3
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., “<b>” + rs.getString(“lname”) + “</b>”);
90
/* --------------------------------------- */
91
while (rs.next()) {
92
pw.println("<student>" + rs.getString(1) + "</student>");
93
}
94
/* ---------------------------------------
95
/* No more data; show final tag.
96
/* --------------------------------------- */
97
pw.print("</document>");
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 {
3
4
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 fi
les (.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 fi
les 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 fi
le), and then deleting the temporary fi
les.
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/";
4
5
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 "myfi
le.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