Java/Servlets/Database
Содержание
- 1 Cached Connection Servlet
- 2 Database and Servlet: Database MetaData
- 3 Database and Servlet: Store procedure
- 4 Database transaction
- 5 Dedicated Connection Servlet
- 6 Delete Blob From Servlet
- 7 Delete Clob From Oracle in a Servlet
- 8 Delete Clob From Servlet
- 9 Display Blob Servlet
- 10 Display Clob Servlet
- 11 Get Column Names From ResultSet
- 12 Guest Book Servlet
- 13 Insert Clob to MySql Servlet
- 14 JDBC and Servlet
- 15 Login Servlets
- 16 OCCI Connection Servlet
- 17 Process a raw SQL query; use ResultSetMetaData to format it
- 18 See Account
- 19 Servlets Database Query
- 20 Session Login JDBC
- 21 Transaction Connection Servlet
- 22 Typical database commands
- 23 Update Clob data stored in MySql from a Servlet
- 24 Using JDBC in Servlets
Cached Connection Servlet
/*
Java Programming with Oracle JDBC
by Donald Bales
ISBN: 059600088X
Publisher: O"Reilly
*/
/*
Defining the Table: Oracle 9i
The following defines a table based on Oracle 9i:
create table DataFiles (
id INT PRIMARY KEY,
fileName VARCHAR(20),
fileBody CLOB
);
Defining the Table: MySQL
The following defines a table based on MySQL:
create table DataFiles (
id INT PRIMARY KEY,
fileName VARCHAR(20),
fileBody TEXT
);
*/
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.MissingResourceException;
import java.util.ResourceBundle;
import java.util.Vector;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class CachedConnectionServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println("<html>");
out.println("<head>");
out.println("<title>Cached Connection Servlet</title>");
out.println("</head>");
out.println("<body>");
// let"s turn on verbose output
CacheConnection.setVerbose(true);
// now let"s get a cached connection
Connection connection = CacheConnection.checkOut();
Statement statement = null;
ResultSet resultSet = null;
String userName = null;
try {
// test the connection
statement = connection.createStatement();
resultSet = statement
.executeQuery("select initcap(user) from sys.dual");
if (resultSet.next())
userName = resultSet.getString(1);
} catch (SQLException e) {
out.println("DedicatedConnection.doGet() SQLException: "
+ e.getMessage() + "<p>");
} finally {
if (resultSet != null)
try {
resultSet.close();
} catch (SQLException ignore) {
}
if (statement != null)
try {
statement.close();
} catch (SQLException ignore) {
}
}
// let"s return the conection
CacheConnection.checkIn(connection);
out.println("Hello " + userName + "!<p>");
out.println("You"re using a cached connection!<p>");
out.println("</body>");
out.println("</html>");
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
doGet(request, response);
}
}
class CacheConnection {
private static boolean verbose = false;
private static int numberConnections = 0;
private static Vector cachedConnections = new Vector();
private static Thread monitor = null;
private static long MAX_IDLE = 1000 * 60 * 60;
synchronized public static Connection checkOut() {
return checkOut("Database");
}
synchronized public static Connection checkOut(String baseName) {
boolean found = false;
CachedConnection cached = null;
if (verbose) {
System.out.println("There are "
+ Integer.toString(numberConnections)
+ " connections in the cache");
System.out.println("Searching for a connection not in use...");
}
for (int i = 0; !found && i < numberConnections; i++) {
if (verbose) {
System.out.println("Vector entry " + Integer.toString(i));
}
cached = (CachedConnection) cachedConnections.get(i);
if (!cached.isInUse() && cached.getBaseName().equals(baseName)) {
if (verbose) {
System.out.println("found cached entry "
+ Integer.toString(i) + " for " + baseName);
}
found = true;
}
}
if (found) {
cached.setInUse(true);
} else {
if (verbose) {
System.out.println("Cached entry not found ");
System.out.println("Allocating new entry for " + baseName);
}
cached = new CachedConnection(Database.getConnection(baseName),
true, baseName);
cachedConnections.add(cached);
numberConnections++;
}
if (monitor == null) {
monitor = new Thread(new Runnable() {
public void run() {
while (numberConnections > 0) {
runMonitor();
}
monitor = null;
if (verbose) {
System.out.println("CacheConnection monitor stopped");
}
}
});
monitor.setDaemon(true);
monitor.start();
}
return cached.getConnection();
}
synchronized public static void checkIn(Connection c) {
boolean found = false;
boolean closed = false;
CachedConnection cached = null;
Connection conn = null;
int i = 0;
if (verbose) {
System.out.println("Searching for connection to set not in use...");
}
for (i = 0; !found && i < numberConnections; i++) {
if (verbose) {
System.out.println("Vector entry " + Integer.toString(i));
}
cached = (CachedConnection) cachedConnections.get(i);
conn = cached.getConnection();
if (conn == c) {
if (verbose) {
System.out.println("found cached entry "
+ Integer.toString(i));
}
found = true;
}
}
if (found) {
try {
closed = conn.isClosed();
} catch (SQLException ignore) {
closed = true;
}
if (!closed)
cached.setInUse(false);
else {
cachedConnections.remove(i);
numberConnections--;
}
} else if (verbose) {
System.out.println("In use Connection not found!!!");
}
}
synchronized private static void checkUse() {
CachedConnection cached = null;
Connection conn = null;
int i = 0;
long now = System.currentTimeMillis();
long then = 0;
for (i = numberConnections - 1; i > -1; i--) {
if (verbose) {
System.out
.println("CacheConnection monitor checking vector entry "
+ Integer.toString(i) + " for use...");
}
cached = (CachedConnection) cachedConnections.get(i);
if (!cached.isInUse()) {
then = cached.getLastUsed();
if ((now - then) > MAX_IDLE) {
if (verbose) {
System.out.println("Cached entry "
+ Integer.toString(i)
+ " idle too long, being destroyed");
}
conn = cached.getConnection();
try {
conn.close();
} catch (SQLException e) {
System.err.println("Unable to close connection: "
+ e.getMessage());
}
cachedConnections.remove(i);
numberConnections--;
}
}
}
}
private static void runMonitor() {
checkUse();
if (numberConnections > 0) {
if (verbose) {
System.out.println("CacheConnection monitor going to sleep");
}
try {
// 1000 milliseconds/second x 60 seconds/minute x 5 minutes
monitor.sleep(1000 * 60 * 5);
} catch (InterruptedException ignore) {
if (verbose) {
System.out
.println("CacheConnection monitor"s sleep was interrupted");
}
}
}
}
public void finalize() throws Throwable {
CachedConnection cached = null;
for (int i = 0; i < numberConnections; i++) {
cached = (CachedConnection) cachedConnections.get(i);
if (cached.getConnection() != null) {
if (verbose) {
System.out.println("Closing connection on Vector entry "
+ Integer.toString(i));
}
try {
cached.getConnection().close();
} catch (SQLException ignore) {
System.err.println("Can"t close connection!!!");
}
}
}
numberConnections = 0;
}
public static void setVerbose(boolean v) {
verbose = v;
}
}
class CachedConnection {
private boolean inUse;
private Connection conn;
private long lastUsed;
private String baseName;
public CachedConnection() {
conn = null;
inUse = false;
lastUsed = System.currentTimeMillis();
baseName = "Database";
}
public CachedConnection(Connection conn, boolean inUse) {
this.conn = conn;
this.inUse = inUse;
this.lastUsed = System.currentTimeMillis();
this.baseName = "Database";
}
public CachedConnection(Connection conn, boolean inUse, String baseName) {
this.conn = conn;
this.inUse = inUse;
this.lastUsed = System.currentTimeMillis();
this.baseName = baseName;
}
public Connection getConnection() {
return conn;
}
public void setConnection(Connection conn) {
this.conn = conn;
}
public boolean getInUse() {
return inUse;
}
public boolean isInUse() {
return inUse;
}
public void setInUse(boolean inUse) {
if (!inUse)
lastUsed = System.currentTimeMillis();
this.inUse = inUse;
}
public String getBaseName() {
return baseName;
}
public void setBaseName(String baseName) {
this.baseName = baseName;
}
public long getLastUsed() {
return lastUsed;
}
}
class Database {
private static boolean verbose = false;
public static final Connection getConnection(String baseName) {
Connection conn = null;
String driver = null;
String url = null;
String username = null;
String password = null;
try {
ResourceBundle resb = ResourceBundle.getBundle(baseName);
driver = resb.getString("database.driver");
url = resb.getString("database.url");
username = resb.getString("database.username");
password = resb.getString("database.password");
Class.forName(driver);
} catch (MissingResourceException e) {
System.err.println("Missing Resource: " + e.getMessage());
return conn;
} catch (ClassNotFoundException e) {
System.err.println("Class not found: " + e.getMessage());
return conn;
}
try {
if (verbose) {
System.out.println("baseName=" + baseName);
System.out.println("driver=" + driver);
System.out.println("url=" + url);
System.out.println("username=" + username);
System.out.println("password=" + password);
}
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
System.err.println(e.getMessage());
System.err.println("in Database.getConnection");
System.err.println("on getConnection");
conn = null;
} finally {
return conn;
}
}
public static void setVerbose(boolean v) {
verbose = v;
}
}
Database and Servlet: Database MetaData
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
public class DbMetaServlet extends HttpServlet {
DataSource pool;
public void init() throws ServletException {
Context env = null;
try {
env = (Context) new InitialContext().lookup("java:comp/env");
pool = (DataSource) env.lookup("jdbc/oracle-8i-athletes");
if (pool == null)
throw new ServletException(
""oracle-8i-athletes" is an unknown DataSource");
} catch (NamingException ne) {
throw new ServletException(ne);
}
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, java.io.IOException {
String sql = "select * from aTable";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
ResultSetMetaData rsm = null;
response.setContentType("text/html");
java.io.PrintWriter out = response.getWriter();
out
.println("<html><head><title>Discover a ResultSet</title></head><body>");
out.println("<h2>Here is Info about the returned ResultSet</h2>");
out.println("<table border="1"><tr>");
try {
//Get a connection from the pool
conn = pool.getConnection();
//Create a Statement with which to run some SQL
stmt = conn.createStatement();
//Execute the SQL
rs = stmt.executeQuery(sql);
//Get a ResultSetMetaData object from the ResultSet
rsm = rs.getMetaData();
int colCount = rsm.getColumnCount();
//print column names
printMeta(rsm, "name", out, colCount);
//print column index
printMeta(rsm, "index", out, colCount);
//print column type
printMeta(rsm, "column type", out, colCount);
//print column display size
printMeta(rsm, "column display", out, colCount);
} catch (Exception e) {
throw new ServletException(e.getMessage());
} finally {
try {
stmt.close();
conn.close();
} catch (SQLException sqle) {
}
}
out.println("</table></body></html>");
out.close();
} //doGet
private void printMeta(ResultSetMetaData metaData, String type,
java.io.PrintWriter out, int colCount) throws SQLException {
if (metaData == null || type == null || out == null)
throw new IllegalArgumentException(
"Illegal args passed to printMeta()");
out.println("<tr>");
if (type.equals("table")) {
out.println("<td><strong>Table name</strong></td>");
for (int i = 1; i <= colCount; ++i) {
out.println("<td>" + metaData.getTableName(i) + "</td>");
}
} else if (type.equals("name")) {
out.println("<td><strong>Column name</strong></td>");
for (int i = 1; i <= colCount; ++i) {
out.println("<td>" + metaData.getColumnName(i) + "</td>");
}
} else if (type.equals("index")) {
out.println("<td><strong>Column index</strong></td>");
for (int i = 1; i <= colCount; ++i) {
out.println("<td>" + i + "</td>");
}
} else if (type.equals("column type")) {
out.println("<td><strong>Column type</strong></td>");
for (int i = 1; i <= colCount; ++i) {
out.println("<td>" + metaData.getColumnTypeName(i) + "</td>");
}
} else if (type.equals("column display")) {
out.println("<td><strong>Column display size</strong></td>");
for (int i = 1; i <= colCount; ++i) {
out
.println("<td>" + metaData.getColumnDisplaySize(i)
+ "</td>");
}
}
out.println("</tr>");
}//printMeta
}
Database and Servlet: Store procedure
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
public class StoredProcServlet extends HttpServlet {
DataSource pool;
public void init() throws ServletException {
Context env = null;
try {
env = (Context) new InitialContext().lookup("java:comp/env");
pool = (DataSource) env.lookup("jdbc/oracle-8i-athletes");
if (pool == null)
throw new ServletException(
""oracle-8i-athletes" is an unknown DataSource");
} catch (NamingException ne) {
throw new ServletException(ne);
}
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, java.io.IOException {
String eventName = request.getParameter("name");
String location = request.getParameter("location");
String date = request.getParameter("date");
List paramList = new ArrayList();
paramList.add(eventName);
paramList.add(location);
paramList.add(date);
try {
addRaceEvent(paramList);
} catch (SQLException sqle) {
throw new ServletException(sqle.getMessage());
}
response.setContentType("text/html");
java.io.PrintWriter out = response.getWriter();
out.println("<html><head><title>Add an Event</title></head><body>");
out.println("<h2>The Event named " + eventName
+ " has been added to the database</h2>");
out.println("</body>");
out.println("</html>");
out.close();
} //doGet
public Connection getConnection() {
Connection conn = null;
try {
conn = pool.getConnection();
} catch (SQLException sqle) {
throw new ServletException(sqle.getMessage());
} finally {
return conn;
}
}
public void addRaceEvent(List values) throws SQLException {
if (values == null)
throw new SQLException("Invalid parameter in addRaceEvent method.");
Connection conn = null;
conn = getConnection();
if (conn == null)
throw new SQLException("Invalid Connection in addRaceEvent method");
java.util.Iterator it = values.iterator();
CallableStatement cs = null;
//Create an instance of the CallableStatement
cs = conn.prepareCall("{call addEvent (?,?,?)}");
for (int i = 1; i <= values.size(); i++)
cs.setString(i, (String) it.next());
//Call the inherited PreparedStatement.executeUpdate() method
cs.executeUpdate();
// return the connection to the pool
conn.close();
}//addRaceEvent
}
Database transaction
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
public class DbServletTrans extends HttpServlet {
DataSource pool;
public void init() throws ServletException {
Context env = null;
try {
env = (Context) new InitialContext().lookup("java:comp/env");
pool = (DataSource) env.lookup("jdbc/oracle-8i-athletes");
if (pool == null)
throw new ServletException(
""oracle-8i-athletes" is an unknown DataSource");
} catch (NamingException ne) {
throw new ServletException(ne);
}
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, java.io.IOException {
Connection conn = null;
Statement stmt = null;
response.setContentType("text/html");
java.io.PrintWriter out = response.getWriter();
out
.println("<html><head><title>Using transactions</title></head><body>");
out.println("<h2>These SQL statements are part of a transaction</h2>");
out.println("CallableStatement.executeUpdate()");
out.println("<br><br>");
out.println("Statement.executeUpdate()");
out.println("<br><br>");
try {
conn = pool.getConnection();
out.println("AutoCommit before setAutoCommit(): "
+ conn.getAutoCommit() + "<br><br>");
out.println("Transaction isolation level: ");
switch (conn.getTransactionIsolation()) {
case 0:
out.println("TRANSACTION_NONE<br><br>");
break;
case 1:
out.println("TRANSACTION_READ_UNCOMMITTED<br><br>");
break;
case 2:
out.println("TRANSACTION_READ_COMMITTED<br><br>");
break;
case 4:
out.println("TRANSACTION_REPEATABLE_READ<br><br>");
break;
case 8:
out.println("TRANSACTION_SERIALIZABLE<br><br>");
break;
default:
out.println("UNKNOWN<br><br>");
}
conn.setAutoCommit(false);
CallableStatement cs = null;
//Create an instance of the CallableStatement
cs = conn.prepareCall("{call addEvent (?,?,?)}");
cs.setString(1, "Salisbury Beach 5-Miler");
cs.setString(2, "Salisbury MA");
cs.setString(3, "14-Aug-2003");
//Call the inherited PreparedStatement.executeUpdate() method
cs.executeUpdate();
String sql = "update raceevent set racedate="13-Aug-2003" "
+ "where name="Salisbury Beach 5-Miler"";
int res = 0;
stmt = conn.createStatement();
res = stmt.executeUpdate(sql);
//commit the two SQL statements
conn.rumit();
} catch (Exception e) {
try {
//rollback the transaction in case of a problem
conn.rollback();
} catch (SQLException sqle) {
}
throw new ServletException(e.getMessage());
} finally {
try {
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();//this returns the Connection to the
// Connection pool
} catch (SQLException sqle) {
}
}
out.println("</table></body></html>");
out.close();
} //doGet
}
Dedicated Connection Servlet
/*
Java Programming with Oracle JDBC
by Donald Bales
ISBN: 059600088X
Publisher: O"Reilly
*/
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.UnavailableException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class DedicatedConnectionServlet extends HttpServlet {
Connection connection;
long connected;
public void init(ServletConfig config) throws ServletException {
super.init(config);
try {
// load the driver
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
} catch (ClassNotFoundException e) {
throw new UnavailableException(
"DedicatedConnection.init() ClassNotFoundException: "
+ e.getMessage());
} catch (IllegalAccessException e) {
throw new UnavailableException(
"DedicatedConnection.init() IllegalAccessException: "
+ e.getMessage());
} catch (InstantiationException e) {
throw new UnavailableException(
"DedicatedConnection.init() InstantiationException: "
+ e.getMessage());
}
try {
// establish a connection
connection = DriverManager.getConnection(
"jdbc:oracle:thin:@dssw2k01:1521:orcl", "scott", "tiger");
connected = System.currentTimeMillis();
} catch (SQLException e) {
throw new UnavailableException(
"DedicatedConnection.init() SQLException: "
+ e.getMessage());
}
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println("<html>");
out.println("<head>");
out.println("<title>A Dedicated Connection</title>");
out.println("</head>");
out.println("<body>");
Statement statement = null;
ResultSet resultSet = null;
String userName = null;
try {
// test the connection
statement = connection.createStatement();
resultSet = statement
.executeQuery("select initcap(user) from sys.dual");
if (resultSet.next())
userName = resultSet.getString(1);
} catch (SQLException e) {
out.println("DedicatedConnection.doGet() SQLException: "
+ e.getMessage() + "<p>");
} finally {
if (resultSet != null)
try {
resultSet.close();
} catch (SQLException ignore) {
}
if (statement != null)
try {
statement.close();
} catch (SQLException ignore) {
}
}
out.println("Hello " + userName + "!<p>");
out.println("This Servlet"s database connection was created on "
+ new java.util.Date(connected) + "<p>");
out.println("</body>");
out.println("</html>");
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
doGet(request, response);
}
public void destroy() {
// close the connection
if (connection != null)
try {
connection.close();
} catch (SQLException ignore) {
}
}
}
Delete Blob From Servlet
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class DeleteBlobFromServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException,
ServletException {
Connection conn = null;
PreparedStatement pstmt = null;
String id = "0001";
ServletOutputStream out = response.getOutputStream();
response.setContentType("text/html");
out.println("<html><head><title>Delete Photo</title></head>");
try {
conn = getHSQLConnection();
pstmt = conn.prepareStatement("delete from MyPictures where id = ?");
pstmt.setString(1, id);
pstmt.executeUpdate();
out.println("<body><h3>deleted photo with id=" + id + "</h3></body></html>");
} catch (Exception e) {
out.println("<body><h1>Error=" + e.getMessage() + "</h1></body></html>");
} finally {
try {
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static Connection getHSQLConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver Loaded.");
String url = "jdbc:hsqldb:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
public static Connection getMySqlConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/demo2s";
String username = "oost";
String password = "oost";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "username";
String password = "password";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
Delete Clob From Oracle in a Servlet
/*
Defining the Table: Oracle 9i
The following defines a table based on Oracle 9i:
create table DataFiles (
id INT PRIMARY KEY,
fileName VARCHAR(20),
fileBody CLOB
);
Defining the Table: MySQL
The following defines a table based on MySQL:
create table DataFiles (
id INT PRIMARY KEY,
fileName VARCHAR(20),
fileBody TEXT
);
*/
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class DeleteClobFromOracleServlet extends HttpServlet {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "userName";
String password = "password";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException,
ServletException {
Connection conn = null;
PreparedStatement pstmt = null;
String id = "001";
ServletOutputStream out = response.getOutputStream();
response.setContentType("text/html");
out.println("<html><head><title>Delete CLOB Record</title></head>");
try {
conn = getConnection();
pstmt = conn.prepareStatement("delete from DataFiles where id = ?");
pstmt.setString(1, id);
pstmt.executeUpdate();
out.println("<body><h4>deleted CLOB record with id=" + id + "</h4></body></html>");
} catch (Exception e) {
out.println("<body><h4>Error=" + e.getMessage() + "</h4></body></html>");
} finally {
try {
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException,
ServletException {
doGet(request, response);
}
}
Delete Clob From Servlet
/*
Defining the Table: Oracle 9i
The following defines a table based on Oracle 9i:
create table DataFiles (
id INT PRIMARY KEY,
fileName VARCHAR(20),
fileBody CLOB
);
Defining the Table: MySQL
The following defines a table based on MySQL:
create table DataFiles (
id INT PRIMARY KEY,
fileName VARCHAR(20),
fileBody TEXT
);
*/
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class DeleteClobFromServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException,
ServletException {
Connection conn = null;
PreparedStatement pstmt = null;
String id = "0001";
ServletOutputStream out = response.getOutputStream();
response.setContentType("text/html");
out.println("<html><head><title>Delete CLOB Record</title></head>");
try {
conn = getHSQLConnection();
pstmt = conn.prepareStatement("delete from DataFiles where id = ?");
pstmt.setString(1, id);
pstmt.executeUpdate();
out.println("<body><h4>deleted CLOB record with id=" + id + "</h4></body></html>");
} catch (Exception e) {
out.println("<body><h1>Error=" + e.getMessage() + "</h1></body></html>");
} finally {
try {
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static Connection getHSQLConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver Loaded.");
String url = "jdbc:hsqldb:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
public static Connection getMySqlConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/demo2s";
String username = "oost";
String password = "oost";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "username";
String password = "password";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
Display Blob Servlet
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class DisplayBlobServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException,
ServletException {
Blob photo = null;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String query = "select photo from MyPictures where id = "001"";
ServletOutputStream out = response.getOutputStream();
try {
conn = getHSQLConnection();
} catch (Exception e) {
response.setContentType("text/html");
out.println("<html><head><title>Person Photo</title></head>");
out.println("<body><h1>Database Connection Problem.</h1></body></html>");
return;
}
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
photo = rs.getBlob(1);
} else {
response.setContentType("text/html");
out.println("<html><head><title>Person Photo</title></head>");
out.println("<body><h1>No photo found for id= 001 </h1></body></html>");
return;
}
response.setContentType("image/gif");
InputStream in = photo.getBinaryStream();
int length = (int) photo.length();
int bufferSize = 1024;
byte[] buffer = new byte[bufferSize];
while ((length = in.read(buffer)) != -1) {
System.out.println("writing " + length + " bytes");
out.write(buffer, 0, length);
}
in.close();
out.flush();
} catch (SQLException e) {
response.setContentType("text/html");
out.println("<html><head><title>Error: Person Photo</title></head>");
out.println("<body><h1>Error=" + e.getMessage() + "</h1></body></html>");
return;
} finally {
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static Connection getHSQLConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver Loaded.");
String url = "jdbc:hsqldb:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
public static Connection getMySqlConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/demo2s";
String username = "oost";
String password = "oost";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "username";
String password = "password";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
Display Clob Servlet
import java.io.IOException;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class DisplayClobServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException,
ServletException {
Clob fileAsCLOB = null;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String id = "001";
String query = "select fileBody from DataFiles where id = " + id;
ServletOutputStream out = response.getOutputStream();
// all responses will be in text/html format
response.setContentType("text/html");
try {
conn = getHSQLConnection();
} catch (Exception e) {
out.println("<html><head><title>CLOB Example</title></head>");
out.println("<body><h4>Database Connection Problem.</h4>");
out.println("<h5>" + e.getMessage() + "</h5></body></html>");
return;
}
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
fileAsCLOB = rs.getClob(1);
} else {
out.println("<html><head><title>CLOB Example</title></head>");
out.println("<body><h4>No file found for id=" + id + "</h4></body></html>");
return;
}
// Materialize the CLOB as a String object (get the whole clob).
long length = fileAsCLOB.length();
// note that the first character is at position 1
String fileAsString = fileAsCLOB.getSubString(1, (int) length);
// write it for display
out.println(fileAsString);
System.out.println("CLOB writing done.");
} catch (SQLException e) {
out.println("<html><head><title>Error: CLOB Example</title></head>");
out.println("<body><h4>Error=" + e.getMessage() + "</h4></body></html>");
return;
} finally {
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static Connection getHSQLConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver Loaded.");
String url = "jdbc:hsqldb:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
public static Connection getMySqlConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/demo2s";
String username = "oost";
String password = "oost";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "username";
String password = "password";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
Get Column Names From ResultSet
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class GetColumnNamesFromResultSetMySQL {
public static void getColumnNames(ResultSet rs) throws SQLException {
if (rs == null) {
return;
}
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
// get the column names; column indexes start from 1
for (int i = 1; i < numberOfColumns + 1; i++) {
String columnName = rsMetaData.getColumnName(i);
// Get the name of the column"s table name
String tableName = rsMetaData.getTableName(i);
System.out.println("column name=" + columnName + " table=" + tableName);
}
}
public static void main(String[] args) throws Exception {
Connection conn = getMySqlConnection();
Statement stmt = null;
ResultSet rs = null;
String query = "select id, name, age from employees";
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
getColumnNames(rs);
rs.close();
stmt.close();
conn.close();
}
public static Connection getMySqlConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/octopus";
String username = "root";
String password = "root";
Class.forName(driver); // load MySQL driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "username";
String password = "password";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
Guest Book Servlet
/*
Database Programming with JDBC and Java, Second Edition
By George Reese
ISBN: 1-56592-616-1
Publisher: O"Reilly
*/
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Date;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Locale;
import java.util.Properties;
import java.util.Random;
import java.util.StringTokenizer;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class GuestBookServlet extends HttpServlet {
private Properties connectionProperties = new Properties();
private Driver driver = null;
private String driverName = null;
private String jdbcURL = null;
private Random random = new Random();
/**
* Provides the servlet with the chance to get runtime configuration values
* and initialize itself. For a database servlet, you want to grab the
* driver name, URL, and any connection information. For this example, I
* assume a driver that requires a user name and password. For an example of
* more database independent configuration, see Chapter 4.
*
* @param cfg
* the servlet configuration information
* @throws javax.servlet.ServletException
* could not load the specified JDBC driver
*/
public void init(ServletConfig cfg) throws ServletException {
super.init(cfg);
{
String user, pw;
driverName = cfg.getInitParameter("gb.driver");
jdbcURL = cfg.getInitParameter("gb.jdbcURL");
user = cfg.getInitParameter("gb.user");
if (user != null) {
connectionProperties.put("user", user);
}
pw = cfg.getInitParameter("gb.pw");
if (pw != null) {
connectionProperties.put("password", pw);
}
try {
driver = (Driver) Class.forName(driverName).newInstance();
} catch (Exception e) {
throw new ServletException("Unable to load driver: "
+ e.getMessage());
}
}
}
/**
* Performs the HTTP GET. This is where we print out a form and a random
* sample of the comments.
*
* @param req
* the servlet request information
* @param res
* the servlet response information
* @throws javax.servlet.ServletException
* an error occurred talking to the database
* @throws java.io.IOException
* a socket error occurred
*/
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
PrintWriter out = res.getWriter();
Locale loc = getLocale(req);
res.setContentType("text/html");
printCommentForm(out, loc);
printComments(out, loc);
}
public void doPost(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
PrintWriter out = res.getWriter();
Locale loc = getLocale(req);
String name, email, comment;
Connection conn = null;
Exception err = null;
int id = -1;
String[] tmp;
// get the form values
tmp = req.getParameterValues("name");
if (tmp == null || tmp.length != 1) {
name = null;
} else {
name = tmp[0];
}
tmp = req.getParameterValues("email");
if (tmp == null || tmp.length != 1) {
email = null;
} else {
email = tmp[0];
}
tmp = req.getParameterValues("comments");
if (tmp == null || tmp.length != 1) {
comment = null;
} else {
comment = tmp[0];
}
res.setContentType("text/html");
// validate values
if (name.length() < 1) {
out.println("You must specify a valid name!");
printCommentForm(out, loc);
return;
}
if (email.length() < 3) {
out.println("You must specify a valid email address!");
printCommentForm(out, loc);
return;
}
if (email.indexOf("@") < 1) {
out.println("You must specify a valid email address!");
printCommentForm(out, loc);
return;
}
if (comment.length() < 1) {
out.println("You left no comments!");
printCommentForm(out, loc);
return;
}
try {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd", Locale.US);
java.util.Date date = new java.util.Date();
ResultSet result;
Statement stmt;
conn = DriverManager.getConnection(jdbcURL, connectionProperties);
// remove the "setAutoCommit(false)" line for mSQL or MySQL
conn.setAutoCommit(false);
stmt = conn.createStatement();
// generate a new comment ID
// more on ID generation in Chapter 4
result = stmt.executeQuery("SELECT NEXT_SEQ " + "FROM SEQGEN "
+ "WHERE NAME = "COMMENT_ID"");
if (!result.next()) {
throw new ServletException("Failed to generate id.");
}
id = result.getInt(1) + 1;
stmt.close();
// closing the statement closes the result
stmt = conn.createStatement();
stmt.executeUpdate("UPDATE SEQGEN SET NEXT_SEQ = " + id
+ " WHERE NAME = "COMMENT_ID"");
stmt.close();
stmt = conn.createStatement();
comment = fixComment(comment);
stmt.executeUpdate("INSERT INTO COMMENT "
+ "(COMMENT_ID, EMAIL, NAME, COMMENT, " + "CMT_DATE) "
+ "VALUES (" + id + ", "" + email + "", "" + name + "", ""
+ comment + "", "" + fmt.format(date) + "")");
conn.rumit();
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
err = e;
} finally {
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
}
}
}
if (err != null) {
out.println("An error occurred on save: " + err.getMessage());
} else {
printCommentForm(out, loc);
printComments(out, loc);
}
}
/**
* Find the desired locale from the HTTP header.
*
* @param req
* the servlet request from which the header is read
* @return the locale matching the first accepted language
*/
private Locale getLocale(HttpServletRequest req) {
String hdr = req.getHeader("Accept-Language");
StringTokenizer toks;
if (hdr == null) {
return Locale.getDefault();
}
toks = new StringTokenizer(hdr, ",");
if (toks.hasMoreTokens()) {
String lang = toks.nextToken();
int ind = lang.indexOf(";");
Locale loc;
if (ind != -1) {
lang = lang.substring(0, ind);
}
lang = lang.trim();
ind = lang.indexOf("-");
if (ind == -1) {
loc = new Locale(lang, "");
} else {
loc = new Locale(lang.substring(0, ind), lang
.substring(ind + 1));
}
return loc;
}
return Locale.getDefault();
}
public String getServletInfo() {
return "Guest Book Servlet\nFrom Database Programming with JDBC "
+ "and Java";
}
private void printCommentForm(PrintWriter out, Locale loc)
throws IOException {
out.println("<div class=\"gbform\">");
out.println("<form action=\"personal/guestbook.shtml\" "
+ "method=\"POST\">");
out.println("<table>");
out.println("<tr>");
out.println("<td>Name:</td>");
out.println("<td><input type=\"text\" name=\"name\" "
+ "size=\"30\"/></td>");
out.println("<td><input type=\"submit\" value=\"Save\"/></td>");
out.println("</tr>");
out.println("<tr>");
out.println("<td>Email:</td>");
out.println("<td><input type=\"text\" name=\"email\" "
+ "size=\"30\"/></td>");
out.println("<tr>");
out.println("<tr>");
out.println("<td>Comments:</td>");
out.println("<tr>");
out.println("<tr>");
out.println("<td colspan=\"3\">");
out.println("<textarea name=\"comments\" cols=\"40\" rows=\"7\">");
out.println("</textarea></td>");
out.println("<tr>");
out.println("</table>");
out.println("</form>");
}
private void printComments(PrintWriter out, Locale loc) throws IOException {
Connection conn = null;
try {
DateFormat fmt = DateFormat.getDateInstance(DateFormat.FULL, loc);
ResultSet results;
Statement stmt;
int rows, count;
conn = DriverManager.getConnection(jdbcURL, connectionProperties);
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
results = stmt.executeQuery("SELECT NAME, EMAIL, CMT_DATE, "
+ "COMMENT, COMMENT_ID " + "FROM COMMENT "
+ "ORDER BY CMT_DATE");
out.println("<dl>");
results.last();
results.next();
rows = results.getRow();
// pick a random row
rows = random.nextInt() % rows;
if (rows < 4) {
// if the random row is less than 4, print the first 4 rows
results.afterLast();
} else {
// otherwise go to the specified row, print the prior 5 rows
results.absolute(rows);
}
count = 0;
// print up to 5 rows going backwards from the randomly
// selected row
while (results.previous() && (count < 5)) {
String name, email, cmt;
Date date;
count++;
name = results.getString(1);
if (results.wasNull()) {
name = "Unknown User";
}
email = results.getString(2);
if (results.wasNull()) {
email = "user@host";
}
date = results.getDate(3);
if (results.wasNull()) {
date = new Date((new java.util.Date()).getTime());
}
cmt = results.getString(4);
if (results.wasNull()) {
cmt = "No comment.";
}
out.println("<dt><b>" + name + "</b> (" + email + ") on "
+ fmt.format(date) + "</dt>");
cmt = noXML(cmt);
out.println("<dd> " + cmt + "</dd>");
}
out.println("</dl>");
} catch (SQLException e) {
out.println("A database error occurred: " + e.getMessage());
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
}
}
}
/**
* Removes any XML-sensitive characters from a comment and replaces them
* with their character entities.
*
* @param cmt
* the raw comment
* @return the XML-safe comment
*/
private String noXML(String cmt) {
StringBuffer buff = new StringBuffer();
for (int i = 0; i < cmt.length(); i++) {
char c = cmt.charAt(i);
switch (c) {
case "<":
buff.append("<");
break;
case ">":
buff.append(">");
break;
case "&":
buff.append("&");
break;
case """:
buff.append(""");
break;
default:
buff.append(c);
break;
}
}
return buff.toString();
}
/**
* This method escapes single quotes so that database statements are not
* messed up.
*
* @param comment
* the raw comment
* @return a comment with any quotes escaped
*/
private String fixComment(String comment) {
if (comment.indexOf(""") != -1) {
String tmp = "";
for (int i = 0; i < comment.length(); i++) {
char c = comment.charAt(i);
if (c == "\"") {
tmp = tmp + "\\"";
} else {
tmp = tmp + c;
}
}
comment = tmp;
}
return comment;
}
}
Insert Clob to MySql Servlet
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class InsertClobToMySqlServlet extends HttpServlet {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException,
ServletException {
String clobData = null;
Connection conn = null;
String id = "001";
String name = "fileName";
String fileAsURL = "http://yourwebsite/fileName.dat";
ServletOutputStream out = response.getOutputStream();
response.setContentType("text/html");
out.println("<html><head><title>Insert Clob To MySql Servlet</title></head>");
try {
conn = getConnection();
clobData = getClobsContentAsString(fileAsURL);
insertCLOB(conn, id, name, clobData);
out.println("<body><h4>OK: inserted a new record with id=" + id + "</h4></body></html>");
} catch (Exception e) {
e.printStackTrace();
out.println("<body><h4>Error: " + e.getMessage() + "</h4></body></html>");
}
}
public void insertCLOB(Connection conn, String id, String name, String fileContent)
throws Exception {
PreparedStatement pstmt = null;
try {
pstmt = conn
.prepareStatement("insert into datafiles(id, filename, filebody) values (?, ?, ?)");
pstmt.setString(1, id);
pstmt.setString(2, name);
pstmt.setString(3, fileContent);
pstmt.executeUpdate();
} finally {
pstmt.close();
}
}
public static String getClobsContentAsString(String urlAsString) throws Exception {
InputStream content = null;
try {
URL url = new URL(urlAsString);
URLConnection urlConn = url.openConnection();
urlConn.connect();
content = urlConn.getInputStream();
int BUFFER_SIZE = 1024;
ByteArrayOutputStream output = new ByteArrayOutputStream();
int length;
byte[] buffer = new byte[BUFFER_SIZE];
while ((length = content.read(buffer)) != -1) {
output.write(buffer, 0, length);
}
return new String(output.toByteArray());
} finally {
content.close();
}
}
}
JDBC and Servlet
/*
MySQL and Java Developer"s Guide
Mark Matthews, Jim Cole, Joseph D. Gradecki
Publisher Wiley,
Published February 2003,
ISBN 0471269239
*/
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class JDBCServlet extends HttpServlet {
public void doGet(HttpServletRequest inRequest,
HttpServletResponse outResponse) throws ServletException,
IOException {
PrintWriter out = null;
Connection connection = null;
Statement statement;
ResultSet rs;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager
.getConnection("jdbc:mysql://localhost/products");
statement = connection.createStatement();
outResponse.setContentType("test/html");
out = outResponse.getWriter();
rs = statement.executeQuery("SELECT ID, title, price FROM product");
out.println("<HTML><HEAD><TITLE>Products</TITLE></HEAD>");
out.println("<BODY>");
out.println("<UL>");
while (rs.next()) {
out.println("<LI>" + rs.getString("ID") + " "
+ rs.getString("title") + " " + rs.getString("price"));
}
out.println("</UL>");
out.println("</BODY></HTML>");
} catch (ClassNotFoundException e) {
out.println("Driver Error");
} catch (SQLException e) {
out.println("SQLException: " + e.getMessage());
}
}
public void doPost(HttpServletRequest inRequest,
HttpServletResponse outResponse) throws ServletException,
IOException {
doGet(inRequest, outResponse);
}
}
Login Servlets
/*
Java Programming with Oracle JDBC
by Donald Bales
ISBN: 059600088X
Publisher: O"Reilly
*/
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.UnavailableException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
public class Login extends HttpServlet {
public void init(ServletConfig config) throws ServletException {
super.init(config);
try {
// load the driver
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
} catch (ClassNotFoundException e) {
throw new UnavailableException(
"Login init() ClassNotFoundException: " + e.getMessage());
} catch (IllegalAccessException e) {
throw new UnavailableException(
"Login init() IllegalAccessException: " + e.getMessage());
} catch (InstantiationException e) {
throw new UnavailableException(
"Login init() InstantiationException: " + e.getMessage());
}
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println("<html>");
out.println("<head>");
out.println("<title>Login</title>");
out.println("</head>");
out.println("<body>");
HttpSession session = request.getSession();
Connection connection = (Connection) session.getAttribute("connection");
if (connection == null) {
String userName = request.getParameter("username");
String password = request.getParameter("password");
if (userName == null || password == null) {
// prompt the user for her username and password
out.println("<form method=\"get\" action=\"Login\">");
out.println("Please specify the following to log in:<p>");
out.println("Username: <input type=\"text\" "
+ "name=\"username\" size=\"30\"><p>");
out.println("Password: <input type=\"password\" "
+ "name=\"password\" size=\"30\"><p>");
out.println("<input type=\"submit\" value=\"Login\">");
out.println("</form>");
} else {
// create the connection
try {
connection = DriverManager.getConnection(
"jdbc:oracle:thin:@dssw2k01:1521:orcl", userName,
password);
} catch (SQLException e) {
out.println("Login doGet() " + e.getMessage());
}
if (connection != null) {
// store the connection
session.setAttribute("connection", connection);
response.sendRedirect("Login");
return;
}
}
} else {
String logout = request.getParameter("logout");
if (logout == null) {
// test the connection
Statement statement = null;
ResultSet resultSet = null;
String userName = null;
try {
statement = connection.createStatement();
resultSet = statement
.executeQuery("select initcap(user) from sys.dual");
if (resultSet.next())
userName = resultSet.getString(1);
} catch (SQLException e) {
out.println("Login doGet() SQLException: " + e.getMessage()
+ "<p>");
} finally {
if (resultSet != null)
try {
resultSet.close();
} catch (SQLException ignore) {
}
if (statement != null)
try {
statement.close();
} catch (SQLException ignore) {
}
}
out.println("Hello " + userName + "!<p>");
out.println("Your session ID is " + session.getId() + "<p>");
out
.println("It was created on "
+ new java.util.Date(session.getCreationTime())
+ "<p>");
out.println("It was last accessed on "
+ new java.util.Date(session.getLastAccessedTime())
+ "<p>");
out.println("<form method=\"get\" action=\"Login\">");
out.println("<input type=\"submit\" name=\"logout\" "
+ "value=\"Logout\">");
out.println("</form>");
} else {
// close the connection and remove it from the session
try {
connection.close();
} catch (SQLException ignore) {
}
session.removeAttribute("connection");
out.println("You have been logged out.");
}
}
out.println("</body>");
out.println("</html>");
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
doGet(request, response);
}
}
OCCI Connection Servlet
/*
Java Programming with Oracle JDBC
by Donald Bales
ISBN: 059600088X
Publisher: O"Reilly
*/
import oracle.jdbc.pool.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Vector;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.ConnectionPoolDataSource;
public class OCCIConnectionServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println("<html>");
out.println("<head>");
out.println("<title>Oracle Cached Connection "
+ "Implementation Test Servlet</title>");
out.println("</head>");
out.println("<body>");
// let"s turn on verbose output
OCCIConnection.setVerbose(true);
// now let"s get a cached connection
Connection connection = OCCIConnection.checkOut();
Statement statement = null;
ResultSet resultSet = null;
String userName = null;
try {
// test the connection
statement = connection.createStatement();
resultSet = statement
.executeQuery("select initcap(user) from sys.dual");
if (resultSet.next())
userName = resultSet.getString(1);
} catch (SQLException e) {
out.println("DedicatedConnection.doGet() SQLException: "
+ e.getMessage() + "<p>");
} finally {
if (resultSet != null)
try {
resultSet.close();
} catch (SQLException ignore) {
}
if (statement != null)
try {
statement.close();
} catch (SQLException ignore) {
}
}
// let"s add a little delay so we can force
// multiple connections in the connection cache
for (int o = 0; o < 3; o++) {
for (int i = 0; i < 2147483647; i++) {
}
}
// let"s return the conection
OCCIConnection.checkIn(connection);
out.println("Hello " + userName + "!<p>");
out.println("You"re using an Oracle Cached "
+ "Connection Implementation connection!<p>");
out.println("</body>");
out.println("</html>");
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
doGet(request, response);
}
}
class OCCIConnection {
private static boolean verbose = false;
private static int numberImplementations = 0;
private static Vector cachedImplementations = new Vector();
public static synchronized Connection checkOut() {
return checkOut("Database");
}
public static synchronized Connection checkOut(String baseName) {
boolean found = false;
OracleConnectionCacheImpl cached = null;
Connection connection = null;
if (verbose) {
System.out.println("There are "
+ Integer.toString(numberImplementations)
+ " connections in the cache");
System.out.println("Searching for a matching implementation...");
}
for (int i = 0; !found && i < numberImplementations; i++) {
if (verbose) {
System.out.println("Vector entry " + Integer.toString(i));
}
cached = (OracleConnectionCacheImpl) cachedImplementations.get(i);
if (cached.getDescription().equals(baseName)) {
if (verbose) {
System.out.println("found cached entry "
+ Integer.toString(i) + " for " + baseName);
}
found = true;
}
}
if (!found) {
if (verbose) {
System.out.println("Cached entry not found ");
System.out.println("Allocating new entry for " + baseName);
}
try {
cached = new OracleConnectionCacheImpl(
getConnectionPoolDataSource(baseName));
cached.setDescription(baseName);
cachedImplementations.add(cached);
numberImplementations++;
} catch (SQLException e) {
System.err.println(e.getMessage()
+ " creating a new implementation for " + baseName);
}
}
if (cached != null) {
try {
connection = cached.getConnection();
} catch (SQLException e) {
System.err.println(e.getMessage() + " getting connection for "
+ baseName);
}
}
return connection;
}
public static ConnectionPoolDataSource getConnectionPoolDataSource(
String baseName) {
Context context = null;
ConnectionPoolDataSource cpds = null;
try {
Properties properties = new Properties();
properties.setProperty(Context.INITIAL_CONTEXT_FACTORY,
"com.sun.jndi.fscontext.RefFSContextFactory");
properties.setProperty(Context.PROVIDER_URL, "file:/JNDI/JDBC");
context = new InitialContext(properties);
cpds = (ConnectionPoolDataSource) context.lookup(baseName);
} catch (NamingException e) {
System.err.println(e.getMessage() + " creating JNDI context for "
+ baseName);
}
return cpds;
}
protected static synchronized void checkIn(Connection c) {
try {
c.close();
} catch (SQLException e) {
System.err.println(e.getMessage() + " closing connection");
}
}
public static String[] getReport() {
int line = 0;
String[] lines = new String[numberImplementations * 7];
OracleConnectionCacheImpl cached = null;
for (int i = 0; i < numberImplementations; i++) {
cached = (OracleConnectionCacheImpl) cachedImplementations.get(i);
lines[line++] = cached.getDescription() + ":";
switch (cached.getCacheScheme()) {
case OracleConnectionCacheImpl.DYNAMIC_SCHEME:
lines[line++] = "Cache Scheme = DYNAMIC_SCHEME";
break;
case OracleConnectionCacheImpl.FIXED_RETURN_NULL_SCHEME:
lines[line++] = "Cache Scheme = FIXED_RETURN_NULL_SCHEME";
break;
case OracleConnectionCacheImpl.FIXED_WAIT_SCHEME:
lines[line++] = "Cache Scheme = FIXED_WAIT_SCHEME";
break;
}
lines[line++] = "Minimum Limit = "
+ Integer.toString(cached.getMinLimit());
lines[line++] = "Maximum Limit = "
+ Integer.toString(cached.getMaxLimit());
lines[line++] = "Cache Size = "
+ Integer.toString(cached.getCacheSize());
lines[line++] = "Active Size = "
+ Integer.toString(cached.getActiveSize());
lines[line++] = " ";
}
return lines;
}
public static void setVerbose(boolean v) {
verbose = v;
}
}
Process a raw SQL query; use ResultSetMetaData to format it
/*
* Copyright (c) Ian F. Darwin, http://www.darwinsys.ru/, 1996-2002.
* All rights reserved. Software written by Ian F. Darwin and others.
* $Id: LICENSE,v 1.8 2004/02/09 03:33:38 ian Exp $
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
* 1. Redistributions of source code must retain the above copyright
* notice, this list of conditions and the following disclaimer.
* 2. Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in the
* documentation and/or other materials provided with the distribution.
*
* THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS""
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
* TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
* PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
* BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*
* Java, the Duke mascot, and all variants of Sun"s Java "steaming coffee
* cup" logo are trademarks of Sun Microsystems. Sun"s, and James Gosling"s,
* pioneering role in inventing and promulgating (and standardizing) the Java
* language and environment is gratefully acknowledged.
*
* The pioneering role of Dennis Ritchie and Bjarne Stroustrup, of AT&T, for
* inventing predecessor languages C and C++ is also gratefully acknowledged.
*/
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/** Process a raw SQL query; use ResultSetMetaData to format it.
*/
public class RawSQLServlet extends HttpServlet {
/** The application-wide servlet context */
protected ServletContext application;
/** The DB connection object */
protected Connection conn;
/** The JDBC statement object */
protected Statement stmt;
/** Initialize the servlet. */
public void init() throws ServletException {
application = getServletConfig().getServletContext();
String driver = null;
try {
driver = application.getInitParameter("db.driver");
Class.forName(driver);
// Get the connection
log(getClass() + ": Getting Connection");
Connection conn = DriverManager.getConnection (
application.getInitParameter("db.url"),
application.getInitParameter("db.user"),
application.getInitParameter("db.password"));
log(getClass() + ": Creating Statement");
stmt = conn.createStatement();
} catch (ClassNotFoundException ex) {
log(getClass() + ": init: Could not load SQL driver " + driver);
} catch (SQLException ex) {
log(getClass() + ": init: SQL Error: " + ex);
}
}
/** Do the SQL query */
public void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
String query = request.getParameter("sql");
response.setContentType("text/html");
PrintWriter out = response.getWriter();
if (query == null) {
out.println("<b>Error: malformed query, contact administrator</b>");
return;
}
// NB MUST also check for admin privs before proceding!
try { // SQL
out.println("<p>Your query: <b>" + query + "</b></p>");
stmt.execute(query);
ResultSet rs = stmt.getResultSet();
if (rs == null) {
// print updatecount
out.println("<p>Result: updateCount = <b>" +
stmt.getUpdateCount() + "</p>");
} else {
// process resultset
out.println("<br>Your response:");
ResultSetMetaData md = rs.getMetaData();
int count = md.getColumnCount();
out.println("<table border=1>");
out.print("<tr>");
for (int i=1; i<=count; i++) {
out.print("<th>");
out.print(md.getColumnName(i));
}
out.println("</tr>");
while (rs.next()) {
out.print("<tr>");
for (int i=1; i<=count; i++) {
out.print("<td>");
out.print(rs.getString(i));
}
out.println("</tr>");
}
}
out.println("</table>");
// rs.close();
} catch (SQLException ex) {
out.print("<B>" + getClass() + ": SQL Error:</B>\n" + ex);
out.print("<pre>");
ex.printStackTrace(out);
out.print("</pre>");
}
}
public void destroy() {
try {
conn.close(); // All done with that DB connection
} catch (SQLException ex) {
log(getClass() + ": destroy: " + ex);
}
}
}
See Account
/*
MySQL and Java Developer"s Guide
Mark Matthews, Jim Cole, Joseph D. Gradecki
Publisher Wiley,
Published February 2003,
ISBN 0471269239
*/
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
public class SeeAccount extends HttpServlet {
public void doGet(HttpServletRequest inRequest,
HttpServletResponse outResponse) throws ServletException,
IOException {
PrintWriter out = null;
Connection connection = null;
Statement statement = null;
ResultSet rs;
try {
outResponse.setContentType("text/html");
out = outResponse.getWriter();
Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx
.lookup("java:comp/env/jdbc/AccountsDB");
connection = ds.getConnection();
statement = connection.createStatement();
rs = statement.executeQuery("SELECT * FROM acc_acc");
ResultSetMetaData md = rs.getMetaData();
out
.println("<HTML><HEAD><TITLE> Thumbnail Identification Record</TITLE></HEAD>");
out.println("<BODY>");
out.println("Account Information:<BR>");
out.println("<table>");
out.println("<tr><td>");
for (int i = 1; i <= md.getColumnCount(); i++) {
out.println("Column #" + i + "<BR>");
out.println("getColumnName : " + md.getColumnName(i) + "<BR>");
out.println("getColumnClassName : " + md.getColumnClassName(i)
+ "<BR>");
out.println("getColumnDisplaySize : "
+ md.getColumnDisplaySize(i) + "<BR>");
out.println("getColumnType : " + md.getColumnType(i) + "<BR>");
out.println("getTableName : " + md.getTableName(i) + "<BR>");
out.println("<HR>");
}
out.println("</BODY></HTML>");
} catch (Exception e) {
e.printStackTrace();
}
}
public void doPost(HttpServletRequest inRequest,
HttpServletResponse outResponse) throws ServletException,
IOException {
doGet(inRequest, outResponse);
}
}
Servlets Database Query
Session Login JDBC
/*
Java Programming with Oracle JDBC
by Donald Bales
ISBN: 059600088X
Publisher: O"Reilly
*/
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.UnavailableException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.servlet.http.HttpSessionBindingEvent;
import javax.servlet.http.HttpSessionBindingListener;
public class SessionLogin extends HttpServlet {
public void init(ServletConfig config) throws ServletException {
super.init(config);
try {
// load the driver
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
} catch (ClassNotFoundException e) {
throw new UnavailableException(
"Login init() ClassNotFoundException: " + e.getMessage());
} catch (IllegalAccessException e) {
throw new UnavailableException(
"Login init() IllegalAccessException: " + e.getMessage());
} catch (InstantiationException e) {
throw new UnavailableException(
"Login init() InstantiationException: " + e.getMessage());
}
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println("<html>");
out.println("<head>");
out.println("<title>Login</title>");
out.println("</head>");
out.println("<body>");
HttpSession session = request.getSession();
SessionConnection sessionConnection = (SessionConnection) session
.getAttribute("sessionconnection");
Connection connection = null;
if (sessionConnection != null) {
connection = sessionConnection.getConnection();
}
if (connection == null) {
String userName = request.getParameter("username");
String password = request.getParameter("password");
if (userName == null || password == null) {
// prompt the user for her username and password
out.println("<form method=\"get\" action=\"SessionLogin\">");
out.println("Please specify the following to log in:<p>");
out.println("Username: <input type=\"text\" "
+ "name=\"username\" size=\"30\"><p>");
out.println("Password: <input type=\"password\" "
+ "name=\"password\" size=\"30\"><p>");
out.println("<input type=\"submit\" value=\"Login\">");
out.println("</form>");
} else {
// create the connection
try {
connection = DriverManager.getConnection(
"jdbc:oracle:thin:@dssw2k01:1521:orcl", userName,
password);
} catch (SQLException e) {
out.println("Login doGet() " + e.getMessage());
}
if (connection != null) {
// store the connection
sessionConnection = new SessionConnection();
sessionConnection.setConnection(connection);
session
.setAttribute("sessionconnection",
sessionConnection);
response.sendRedirect("SessionLogin");
return;
}
}
} else {
String logout = request.getParameter("logout");
if (logout == null) {
// test the connection
Statement statement = null;
ResultSet resultSet = null;
String userName = null;
try {
statement = connection.createStatement();
resultSet = statement
.executeQuery("select initcap(user) from sys.dual");
if (resultSet.next())
userName = resultSet.getString(1);
} catch (SQLException e) {
out.println("Login doGet() SQLException: " + e.getMessage()
+ "<p>");
} finally {
if (resultSet != null)
try {
resultSet.close();
} catch (SQLException ignore) {
}
if (statement != null)
try {
statement.close();
} catch (SQLException ignore) {
}
}
out.println("Hello " + userName + "!<p>");
out.println("Your session ID is " + session.getId() + "<p>");
out
.println("It was created on "
+ new java.util.Date(session.getCreationTime())
+ "<p>");
out.println("It was last accessed on "
+ new java.util.Date(session.getLastAccessedTime())
+ "<p>");
out.println("<form method=\"get\" action=\"SessionLogin\">");
out.println("<input type=\"submit\" name=\"logout\" "
+ "value=\"Logout\">");
out.println("</form>");
} else {
// close the connection and remove it from the session
try {
connection.close();
} catch (SQLException ignore) {
}
session.removeAttribute("sessionconnection");
out.println("You have been logged out.");
}
}
out.println("</body>");
out.println("</html>");
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
doGet(request, response);
}
}
class SessionConnection implements HttpSessionBindingListener {
Connection connection;
public SessionConnection() {
connection = null;
}
public SessionConnection(Connection connection) {
this.connection = connection;
}
public Connection getConnection() {
return connection;
}
public void setConnection(Connection connection) {
this.connection = connection;
}
public void valueBound(HttpSessionBindingEvent event) {
if (connection != null) {
System.out.println("Binding a valid connection");
} else {
System.out.println("Binding a null connection");
}
}
public void valueUnbound(HttpSessionBindingEvent event) {
if (connection != null) {
System.out
.println("Closing the bound connection as the session expires");
try {
connection.close();
} catch (SQLException ignore) {
}
}
}
}
Transaction Connection Servlet
/*
Java Programming with Oracle JDBC
by Donald Bales
ISBN: 059600088X
Publisher: O"Reilly
*/
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.UnavailableException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class TransactionConnectionServlet extends HttpServlet {
public void init(ServletConfig config) throws ServletException {
super.init(config);
try {
// load the driver
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
} catch (ClassNotFoundException e) {
throw new UnavailableException(
"TransactionConnection.init() ClassNotFoundException: "
+ e.getMessage());
} catch (IllegalAccessException e) {
throw new UnavailableException(
"TransactionConnection.init() IllegalAccessException: "
+ e.getMessage());
} catch (InstantiationException e) {
throw new UnavailableException(
"TransactionConnection.init() InstantiationException: "
+ e.getMessage());
}
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println("<html>");
out.println("<head>");
out.println("<title>A Per Transaction Connection</title>");
out.println("</head>");
out.println("<body>");
Connection connection = null;
try {
// establish a connection
connection = DriverManager.getConnection(
"jdbc:oracle:thin:@dssw2k01:1521:orcl", "scott", "tiger");
} catch (SQLException e) {
throw new UnavailableException(
"TransactionConnection.init() SQLException: "
+ e.getMessage());
}
Statement statement = null;
ResultSet resultSet = null;
String userName = null;
try {
// test the connection
statement = connection.createStatement();
resultSet = statement
.executeQuery("select initcap(user) from sys.dual");
if (resultSet.next())
userName = resultSet.getString(1);
} catch (SQLException e) {
out.println("TransactionConnection.doGet() SQLException: "
+ e.getMessage() + "<p>");
} finally {
if (resultSet != null)
try {
resultSet.close();
} catch (SQLException ignore) {
}
if (statement != null)
try {
statement.close();
} catch (SQLException ignore) {
}
}
if (connection != null) {
// close the connection
try {
connection.close();
} catch (SQLException ignore) {
}
}
out.println("Hello " + userName + "!<p>");
out.println("You"re using a per transaction connection!<p>");
out.println("</body>");
out.println("</html>");
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
doGet(request, response);
}
}
Typical database commands
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class DatabaseServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, java.io.IOException {
String sql = "select * from atable";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
ResultSetMetaData rsm = null;
response.setContentType("text/html");
java.io.PrintWriter out = response.getWriter();
out
.println("<html><head><title>Typical Database Access</title></head><body>");
out.println("<h2>Database info</h2>");
out.println("<table border="1"><tr>");
try {
//load the database driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//The JDBC URL for this Oracle database
String url = "jdbc:oracle:thin:@142.3.169.178:1521:ORCL";
//Create the java.sql.Connection to the database
conn = DriverManager.getConnection(url, "usr", "pass");
//Create a statement for executing some SQL
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
rsm = rs.getMetaData();
int colCount = rsm.getColumnCount();
//print column names
for (int i = 1; i <= colCount; ++i) {
out.println("<th>" + rsm.getColumnName(i) + "</th>");
}
out.println("</tr>");
while (rs.next()) {
out.println("<tr>");
for (int i = 1; i <= colCount; ++i)
out.println("<td>" + rs.getString(i) + "</td>");
out.println("</tr>");
}
} catch (Exception e) {
throw new ServletException(e.getMessage());
} finally {
try {
stmt.close();
conn.close();
} catch (SQLException sqle) {
}
}
out.println("</table><br><br>");
out.println("</body>");
out.println("</html>");
out.close();
} //doGet
}
Update Clob data stored in MySql from a Servlet
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class UpdateMySqlClobServlet extends HttpServlet {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException,
ServletException {
Connection conn = null;
String id = "001";
String fileAsURL = "http://yourwebsite/fileName.dat";
ServletOutputStream out = response.getOutputStream();
response.setContentType("text/html");
out.println("<html><head><title>UpdateMySqlClobServlet</title></head>");
try {
conn = getConnection();
String fileContent = getClobsContentAsString(fileAsURL);
updateCLOB(conn, id, fileContent);
out.println("<body><h4>OK: updated an existing record with id=" + id + "</h4></body></html>");
} catch (Exception e) {
e.printStackTrace();
out.println("<body><h4>Error: " + e.getMessage() + "</h4></body></html>");
}
}
public void updateCLOB(Connection conn, String id, String fileContent) throws Exception {
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement("update dataTable set filebody= ? where id = ?");
pstmt.setString(1, fileContent);
pstmt.setString(2, id);
pstmt.executeUpdate();
} finally {
pstmt.close();
}
}
public static String getClobsContentAsString(String urlAsString) throws Exception {
InputStream content = null;
try {
java.net.URL url = new java.net.URL(urlAsString);
java.net.URLConnection urlConn = url.openConnection();
urlConn.connect();
content = urlConn.getInputStream();
int BUFFER_SIZE = 1024;
ByteArrayOutputStream output = new ByteArrayOutputStream();
int length;
byte[] buffer = new byte[BUFFER_SIZE];
while ((length = content.read(buffer)) != -1) {
output.write(buffer, 0, length);
}
return new String(output.toByteArray());
} finally {
content.close();
}
}
}
