What Is a Very Simple Servlet Example Using JDBC? JDBC

Here I provide a very simple servlet, which can access MySQL and Oracle databases. This servlet accepts one parameter, which is the database vendor (vendor). Based on this parameter (the possible values are mysql and oracle), this servlet creates a database Connection object, and finally displays all data from the employees table in an HTML table format.

MySQL Database Setup

mysql> select * from employees;
+-----+--------+------+
| id | name | age |
+-----+--------+------+
| 88 | Peter | 80 |
| 77 | Donald | 70 |
| 33 | Mary | 30 |
| 44 | Monica | 40 |
| 999 | Andre | 90 |
+-----+--------+------+
5 rows in set (0.15 sec)

Oracle Database Setup

SQL> select * from employees;
ID NAME AGE
---------- -------------------- ----------
11 Alex Smith 25
22 Don Knuth 65
33 Mary Kent 35
44 Monica Seles 30
99 Alex Edison 80
100 Al Sumner 70
105 Al Sumner 90
7 rows selected.

The Solution As a Servlet

import java.io.PrintWriter;
import java.io.IOException;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import jcb.util.DatabaseUtil;
import jcb.db.VeryBasicConnectionManager;
public class MyDatabaseServlet extends HttpServlet {
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
String query = "SELECT id, name, age FROM employees";
ResultSet rs = null;
Statement stmt = null;
Connection conn = null;
try {
String dbVendor = request.getParameter("vendor").trim();
conn = VeryBasicConnectionManager.getConnection(dbVendor);
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
printResultSet(response, rs );
}
catch(Exception e) {
printError(response, e.getMessage());
}
finally {
DatabaseUtil.close(rs);
DatabaseUtil.close(stmt);
DatabaseUtil.close(conn);
}
} // end doGet
private static void printResultSet(HttpServletResponse response,
ResultSet rs)
throws Exception {
PrintWriter out = response.getWriter();
StringBuffer buffer = new StringBuffer();
buffer.append("<html><body><table border=1 cellspacing=0 cellpadding=0>");
buffer.append("<TR><TH>id</TH><TH>name</TH><TH>age</TH></TR>");
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
int age = rs.getInt(3);
buffer.append("<TR><TD>"+id+"</TD><TD>"+name+
"</TD><TD>"+age+"</TD></TR>");
}
buffer.append("</table></body></html>");
out.println(buffer.toString());
}
private static void printError(HttpServletResponse response,
String message) {
try {
PrintWriter out = response.getWriter();
StringBuffer buffer = new StringBuffer();
buffer.append("<html><body>");
buffer.append(message);
buffer.append("</body></html>");
out.println(buffer);
}
catch(Exception ignore) {
// here I ignored the exception, but you can
// modify the code to do more here …
}
}
}

Invoking MyDatabaseServlet for MySQL

Figure : shows how to invoke MyDatabaseServlet with a single parameter (where the parametername is vendor and its value is mysql).

Invoking MyDatabaseServlet for MySQL

Invoking MyDatabaseServlet for Oracle

Figure :shows how to invoke MyDatabaseServlet with a single parameter (where the parameter
name is vendor and its value is oracle).

Invoking MyDatabase Servlet for Oracle



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics