Transcript COMPS311F-9-database

COMPS311F
Li Tak Sing
Case study
 Assume that we have the following table created:
 create table student(id varchar(20) primary key, name
varchar(50), birthdate date);
 create table course(courseid varchar(15) primary key,
coursename varchar(50));
 create table study(id varchar(20), courseid varchar(15), primary
key(id,courseid));
Case study
 We now create an application which allows the user to type
in new student's record.
 You can find the source at:
 http://plbpc001.ouhk.edu.hk/~mt311f/2010sep/database/database/src/database/InsertStudent.java
Inserting a student
 The application has three
textfield for inputting the name,
id and date of birth of the
student.
 If there is any error, a message
will be display at the top of the
window.
Attributes
 JTextField name; for inputing the name
 JTextField id; for inputing the id
 JTextField dob; for inputing the date of birth
 JLabel message; for displaying any error message
 JButton insert; for inserting the record
 Connection conn; the database connection
 PreparedStatement pre; the prepared statement
Initialization of the database
connection
Class.forName("com.mysql.jdbc.Driver");
conn=java.sql.DriverManager.getConnection("jdbc:mysql:/
/127.0.0.1/mydata","usera","userapass");
pre=conn.prepareStatement("insert into student
values(?,?,?)");
The action listener of the button
public void actionPerformed(ActionEvent e) {
try {
pre.setString(1, id.getText());
pre.setString(2, name.getText());
pre.setDate(3, new
java.sql.Date(java.text.DateFormat.getDateInstance(java.text.DateFormat.SH
ORT, java.util.Locale.UK).parse(dob.getText()).getTime()));
pre.executeUpdate();
}
catch (Exception ee) {
message.setText("error");
ee.printStackTrace();
}
}
Another application listing
students' records
 List the courses studied by a particular student.
 The textfield is for the user to type in the name of a student.
 After the user has pressed the List button, the courses he/she
studies will be listed in the text area.
 You can download the file at:
 http://plbpc001.ouhk.edu.hk/~mt311f/2010sep/database/database/src/database/ListCoursesStudied.java
Attributes
 JTextField name; for the user to type in the name of a
student
 JTextArea result; for the displaying of the result.
 Connection conn; the database connection
 PreparedStatement pre; the prepared statement.
Database connection
 The same as previous applicaiton.
 The prepared statement is initialized this way:
pre = conn.prepareStatement("select coursename,
course.courseid from study, student,course where
student.id=study.id and course.courseid=study.courseid and
name=?");
ActionListener of the button
public void actionPerformed(ActionEvent e) {
try {
pre.setString(1, name.getText().trim());
ResultSet re=pre.executeQuery();
result.setText("");
while (re.next()) {
String coursename=re.getString(1);
String courseid=re.getString(2);
result.append(courseid+" "+coursename+"\n");
}
}
catch (Exception ee) {
}
}
Web programming
 Dynamic Web contents
 Shopping web sites
 News
 Facebooks
 Youtube
CGI
 CGI stands for Common Gateway Interface.
 It is not a programming language but an interface that allows
the Web server to invoke an external program.
 The actual program can be written in any programming
languages.
 CGI programs are usually stored in a special directory on
Web servers. For example: /cgi-bin.
 Web servers redirect HTTP requests from end-users to
appropriate CGI programs.
Disadvantages of CGIs
 In CGI, a new process is started to handle each HTTP
request. The overhead of starting new processes is high. The
performance of CGI suffers when many HTTP requests are
received within a short period of time.
 To improve performance, we can have a process perpetually
running on the Web server. When a new HTTP request is
received, the process uses a thread to handle it. This thread
can be created new or selected from a thread pool. The
overhead to handle a request is significantly lowered.
Java alternative to CGI
 Servlets
 A servlet is an object that receives a request and generates a
response based on that request.
 Before you can use servlets, you need a servlets enabled Web
server.
 One of the most popular servlet enabled web server is Tomcat.
Advantages of Servlets
 Efficient. No need to create a new process for a request. A
single copy of the servlet is used to serve multiple requests. A
separate thread is used to serve a new request.
 Powerful. Servlets can talk directly to the Web server.
Servlets can also share data among each other. It is also easy
to maintain session information.
 Portable. Java is much more portable than other languages.
 Inexpensive. There are many Java serlvet servers that are
free.
Download Tomcat 6.0
 Download Tomcat 6.0 from
 http://tomcat.apache.org/download-60.cgi
 After extracting the files, there should be a folder named bin.
 This folder contains batch files with which you can start up
or shut down tomcat.
 Before you can start up tomcat, you need to setup the
JAVA_HOME environment variable pointing to the Java
home.
 startup.bat: the batch file to start up Tomcat.
 shutdown.bat: the batch file to shutdown Tomcat.
Enabling Netbeans to create Web
applications
 Tool -> Plugins -> Available Plugins -> Java Web
Applications
 After doing this, we can create web applications using
Netbeans.
A simple servlet
 All servlets should be subclasses of HttpServlet.
Methods of HttpServlet
 void doGet(HttpServletRequest req, HttpServletResponse
resp)
 This method will be called by the server to allow a servlet to
handle a GET request.
 Overriding this method to support a GET request.
 Input should be read from req and output should be written to
resp.
Methods of HttpSerlvet
 void init(ServletConfig config)
 Called by the servlet container to indicate to a servlet that the
servlet is being placed into service.
 We should put initialization code to this method.
 If this method is overridden, we should call the
super.init(config) method first.
Methods of HttpServlet
 void doPost(HttpServletRequest req, HttpServletResponse
resp)
 Similar to doGet except that this method is used to serve POST
requests.
HttpServletRequest
 This class encapsulates all the request for the servlet service.
Methods of HttpServletRequest
 Cookie[] getCookies()
 returns an array containing all of the cookies the client sent
with this request.
 String getHeader(String st)
 returns the value of the specified request header as a string.
 String getMethod()
 Returns the name of the HTTP method with which this request
was made, for example, GET, POST,..
 String getQueryString()
 Returns the query string that is contained in the request URL.
Methods of HttpServletRequest
 HttpSession getSession()
 Returns the current session associated with this request.
 String getParameter(String name)
 returns the value of a request parameter as a String, or null if
the parameter does not exist. Parameters are contained in the
query string or posted form data.
HttpSerlvetResponse
 void addCookie(Cookie c)
 Add the specified cookie to the response.
 void addHeader(String name, String value)
 Adds a response header with the given name and value.
 ServletOutputStream getOutputStream()
 return an output stream suitable for writing binary data in the
response.
 PrintWriter getWriter()
 Returns a PrintWriter object that can send character text to the
client.
HttpSerlvetResponse
 void setContentType(String type)
 Set the content type of the response being sent to the client.
A simple servlet
package test;
public class HelloServlet extends HttpServlet {
public void doGet (HttpServletRequest req,
HttpServletResponse res) throws ServletException, IOException
{
PrintWriter out = res.getWriter();
out.println("Hello, world!");
out.close();
}
}
Connection to database
 Consider the following servlet's doGet method:
public void doGet (HttpServletRequest req,
HttpServletResponse res) throws ServletException,
IOException {
Class.forName("com.mysql.jdbc.Driver");
conn=java.sql.DriverManager.getConnection("jdbc:mys
ql://127.0.0.1/mydata","usera","userapass");
pre=conn.prepareStatement("select name from
student");
..... //do something for the code
Disadvantage of the previous
method
 The connection is made everytime when a request comes in.
This would be very inefficient.
 A more efficient code would be to include the code in init
method of the servlet.
HTTP Sessions
 HTTP is a stateless protocol. This means that requests to a
server are independent of each other. The protocol does not
store information regarding a previous requests from the
same user.
 This would be a problem as http requests are most of the
time related with each other.
 For example, when a user is visiting an online shop, there is a
need to remember what he/she has put into the shopping
cart.
Keeping session information
 We can use the concept of a session to refer to a sequence of
requests that are related to each other.
 Information for this sequence of requests is called session
information.
 For example, items in a shopping cart, user name etc. are
typical session information.
Storing session information
 There are a number of ways to store session information:
 Using hidden values in an HTML form.
 If the servlet wants to remember a value in subsequent requests from the
Web client, the servlet can include a hidden input value in the response:
<input type="hidden" name="myname" value="myvalue">
In order for this method to work, this input value has to be inside a form.
This input value will be submit to the server when the form is submit.
 Using the embedded query string in an URL.
 The other method is to embed a query string in an URL. For example:
<a href="abc.html? myname=myvalue>a link</a>
 In this way, the query string would be submit to the web server when this
link is clicked.
Storing session information
 Using cookies
 A cookie, is a piece of text stored by a user's web broswer. A
cookie can be used for authentication, storing site preferences,
shopping cart contents, the identifier for a server-based session.
 A cookie consists of one or more name-value pairs containing
bits of information, which may be encrypted.
 The cookie is sent as an HTTP header by a web server to a web
browser and then sent back unchanged by the browser each
time it accessess that server.
Session tracking with cookies
Cookies are created by Web servers to be stored on the client
computer’s hard disk as small text files. A cookie contains the
following information.
1 name of the cookie
2 value of the cookie
3 expiry date
4 domain name of the website
5 path information.
Drawbacks of using cookies
 One drawback to using cookies is that they reside on a
computer and do not follow the end-user when he or she
uses another computer. This drawback concerns convenience.
There are more serious drawbacks that concern security.
 More and more computers refuse to accept cookies.
 Cookies may lead to privacy problem.
 Cookie theft, cookie poisoning etc are possible attack to
cookies.
Creating and returning cookies
 The servlet can create a cookie by calling the Cookie
constructor with the name and the value of the cookie:
Cookie cookiename=new Cookie("cookiename",cookiname);
 To set a cookie:
 response.addCookie(cookiename);
 The cookie would be sent back to the client.
Reading cookie values
When a Web client sends a request to the servlet, the servlet
can call the getCookies( ) method on the HttpServletRequest
object. The call returns an array of Cookie objects stored in
the client. The servlet iterates through the array to find a
cookie with a matching name.
Cookie[] cookies=rquest.getCookies();
for (int i=0;i<cookies.length;i++) {
cookies[i].getName(); //for the name;
cookies[i].getValue(); //for the value;
}
Modifying cookie values
 You can use the setValue method of Cookie to chang the
value of the cookie:
if (cookies[i].getName().equals("abc")) {
cookies[i].getValue("ddd");
response.addCookie(cookies[i]);
}