Monday, 27 April 2009

3-SIMPLE AND COMPLETE DATA SOURCE EXAMPLE

Finally my program which accesses the MySQL database using the above context.xml ‘s datasource definition follows:

package dsweb;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class DStest extends HttpServlet {

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, java.io.IOException {

response.setContentType("text/html");
java.io.PrintWriter out = response.getWriter();
out.println("<html><head><title>Data Source test</title></head><body>");
out.println("<h2>Data Source test by ARS</h2>");

try{
getData(out);
} catch(Exception e){out.println("problem "+ "<BR>"); e.printStackTrace();};

out.println("finitooooooooooo"+ "<BR>");
out.println("</body></html>");
} //end doGet



public void getData(java.io.PrintWriter out) throws Exception {
out.println("getData "+ "<BR>");
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/MySQLDB");
out.println("datasource is read " + "<BR>");
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try {
conn = ds.getConnection();
out.println("connection is created "+ "<BR>");
stmt = conn.createStatement();
String textQuery = "select * from users";
rs = stmt.executeQuery(textQuery);

while (rs.next()) {
System.out.println(rs.getString("FIRST_NAME") + " " + rs.getString("LAST_NAME") + " "
+ rs.getString("AGE"));
out.println(rs.getString("FIRST_NAME") + " " + rs.getString("LAST_NAME") + " "
+ rs.getString("AGE")+ "<BR>");
}
stmt.close();
stmt = null;

conn.close();
conn = null;
} finally {
/*
* close any jdbc instances here that weren't
* explicitly closed during normal code path, so
* that we don't 'leak' resources...
*/

if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlex) {
// ignore -- as we can't do anything about it here
}

stmt = null;
}

if (conn != null) {
try {
conn.close();
} catch (SQLException sqlex) {
// ignore -- as we can't do anything about it here
}

conn = null;
}
}
}
}

Please be informed that you need to have DB named “testars” and a table on this DB named “users”.
I obtained the definition of the “users” with TOAD:

CREATE TABLE `users` (
`USER_ID` varchar(30) DEFAULT NULL,
`FIRST_NAME` varchar(30) DEFAULT NULL,
`LAST_NAME` varchar(30) DEFAULT NULL,
`AGE` int(11) DEFAULT NULL,
`EMAIL` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The DATA is:
USER_ID FIRST_NAME LAST_NAME AGE EMAIL
userid1 first1 last1 21 first1@some.com
userid2 first2 last2 21 first2@some.com
userid3 first3 last3 21 first3@some.com
userid4 first4 last4 21 first4@some.com
userid5 first5 last5 21 first5@some.com
6 Ali SARAL 40 ali@ya.com
userid7 Deniz Gezici 3 first6@some.com
admin admin adnim 0



After the program ran, the output was:

Data Source test by ARS
getData datasource is read connection is created
first1 last1 21
first2 last2 21
first3 last3 21
first4 last4 21
first5 last5 21
Ali SARAL 40
Deniz Gezici 3
admin adnim 0
finitooooooooooo

( prev )
Posted by at