3. Servlets ou pages JSP

Exemple 3-1: Une simple page JSP avec fenêtre "Query"

• http://tecfa2.unige.ch/guides/jsp/ex/jdbc/coffee-break/CoffeeBreakLine.jsp
• http://tecfa2.unige.ch/guides/jsp/ex/jdbc/coffee-break/CoffeeBreakLine.jsp.text

<h1>Coffee Break JSP Example</h1>

<%@ page errorPage="error.jsp" import="java.sql.*" %>

<%! // --------------- inits for the servlet --------------

// The database connection

Connection con;

// The statement

Statement stmt;

// The queryString

String queryString = null;

// ---- configure this for your site

String username = "nobody";

String password = null;

// The URL that will connect to TECFA's MySQL server

// Syntax: jdbc:TYPE:machine:port/DB_NAME

// String url = "jdbc:mysql://localhost:3306/COFFEEBREAK";

String url = "jdbc:mysql://tecfa2.unige.ch:3306/COFFEEBREAK";

%>

<% // --------------- code for the service method --------------

// Let's see if we got a request

queryString = request.getParameter ("QUERYSTRING");

if ((queryString != "") && (queryString != null)) {

 

try {

Class.forName("org.gjt.mm.mysql.Driver");

// Establish Connection to the database at URL with usename and password

con = DriverManager.getConnection(url, username, password);

out.println ("Ok, connection to the DB is working.");

} catch (Exception e) // (ClassNotFoundException and SQLException)

{

throw(new UnavailableException(this, "Sorry! The Database didn't load!"));

}

try {

out.println ("<h2>You asked: </h2>");

out.println ( "Query: " + queryString + "<BR>" );

out.println("<h3>Query Result</h3>");

out.println("<table border>");

stmt = con.createStatement();

ResultSet rs = stmt.executeQuery(queryString);

ResultSetMetaData rsMeta = rs.getMetaData();

// Get the N of Cols in the ResultSet

int noCols = rsMeta.getColumnCount();

out.println("<tr>");

for (int c=1; c<=noCols; c++) {

String el = rsMeta.getColumnLabel(c);

out.println("<th> " + el + " </th>");

}

out.println("</tr>");

while (rs.next()) {

out.println("<tr>");

for (int c=1; c<=noCols; c++) {

String el = rs.getString(c);

out.println("<td> " + el + " </td>");

}

out.println("</tr>");

}

out.println("</table>");

} catch (SQLException ex ) {

out.println ( "<P><PRE>" );

while (ex != null) {

out.println("Message: " + ex. getMessage ());

out.println("SQLState: " + ex. getSQLState ());

out.println("ErrorCode: " + ex. getErrorCode ());

ex = ex. getNextException ();

out.println("");

}

out.println ( "</PRE><P>" );

}

}

%>

 

<hr>You can now try to retrieve something.

 

< FORM METHOD=POST ACTION=" CoffeeBreakLine.jsp ">

Query: <INPUT TYPE=TEXT SIZE=50 NAME=" QUERYSTRING ">

<INPUT TYPE=SUBMIT VALUE="GO!">

</ FORM >

<hr><pre>e.g.:

SELECT * FROM COFFEES

SELECT * FROM COFFEES WHERE PRICE > 9

SELECT PRICE, COF_NAME FROM COFFEES

<pre>

 

<hr><a href="CoffeeBreakLine.jsp">Encore une fois ?</a> |

Source: <A HREF="CoffeeBreakLine.jsp.text">CoffeeBreakLine.jsp.text</A>

Exemple 3-2: Une simple page JSP avec fenêtres Query et UpDate

• presque pareil que l'exemple précédent, mais rajoute une fenêtre pour faire des updates
• http://tecfa2.unige.ch/guides/jsp/ex/jdbc/coffee-break/CoffeeBreakLine2.jsp

Exemple 3-3: Simple Servlet

• http://tecfa2.unige.ch:8080/servlet/CoffeeBreakServlet (Sun/Jws)
• http://tecfa2.unige.ch/servlets/CoffeeBreakServlet (Apache/JServ)
• http://tecfa2.unige.ch/guides/java/staf2x/ex/jdbc/coffee-break/servlet/

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

import java.sql.*;

[ ........]

public class CoffeeBreakServlet extends HttpServlet {
// The database connection
Connection con;
// The statement
Statement stmt;
// The queryString
String queryString = null;

public void init (ServletConfig conf) throws ServletException {
super.init(conf);
// ---- configure this for your site
String username = "nobody";
String password = null;
// The URL that will connect to TECFA's MySQL server
String url = "jdbc:mysql://tecfa2.unige.ch:3306/COFFEEBREAK";
// ---- configure END

try {
Class.forName("org.gjt.mm.mysql.Driver");
// Connection to the database at URL with username and password
con = DriverManager.getConnection(url, username,password);
System.out.println ("Ok, connection to the DB is working.");
} catch (Exception e) // (ClassNotFoundException and SQLException)
{
throw(new UnavailableException(this, "Sorry! The Database didn't load!"));
}

}

public void service (HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {

res.setContentType ( "text/html" );
PrintWriter out = res.getWriter ( );

try {
String title = "Coffee Break JDBC Demo Java Servlet";
out.println ( "<html><head><title>" + title
+ "</title></head>" );
out.println ( "<body><H1>" + title + "</H1>" );
String queryString = req.getParameter ("QUERYSTRING");

if ((queryString != "") && (queryString != null)) {
out.println ("<h2>You asked: </h2>");
out.println ( "Query: " + queryString + "<BR>" );
out.println("<h3>Query Result</h3>");
out.println("<table border>");
Statement stmt = con. createStatement ();
ResultSet rs = stmt. executeQuery (queryString);
ResultSetMetaData rsMeta = rs. getMetaData ();
// Get the N of Cols in the ResultSet
int noCols = rsMeta. getColumnCount ();
out.println("<tr>");
for (int c=1; c<=noCols; c++) {
String el = rsMeta. getColumnLabel (c);
out.println("<th> " + el + " </th>");
}
out.println("</tr>");
while (rs.next()) {
out.println("<tr>");
for (int c=1; c<=noCols; c++) {
String el = rs. getString (c);
out.println("<td> " + el + " </td>");
}
out.println("</tr>");
}
out.println("</table>");
}

} catch { [ .. gérer les erreurs de la requête......] }

out.println ("<hr>You can now try to retrieve something.");
out.println("< FORM METHOD=POST ACTION='/servlet/CoffeeBreakServlet' >");
out.println("Query: <INPUT TYPE=TEXT SIZE=50 NAME=' QUERYSTRING '> ");
out.println("<INPUT TYPE=SUBMIT VALUE='GO!'>");
out.println("</ FORM >");
out.println("<hr><pre>e.g.:");
out.println("SELECT * FROM COFFEES");
out.println("SELECT * FROM COFFEES WHERE PRICE > 9");
out.println("SELECT PRICE, COF_NAME FROM COFFEES");
out.println("<pre>");

out.println ("<hr><a href='/servlet/CoffeeBreakServlet'>Enocre une fois ?</a> | Source: <A HREF='/develop/servlets-ex/coffee-break/CoffeeBreakServlet.java'>CoffeeBreakServlet.java</A>");
out.println ( "</body></html>" );
return ;
}

}