Java/Servlets/Database

Материал из Java эксперт
Перейти к: навигация, поиск

Содержание

Cached Connection Servlet

   <source lang="java">

/* 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() + "

"); } 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; } } </source>

Database and Servlet: Database MetaData

   <source lang="java">

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("

Here is Info about the returned ResultSet

"); out.println(""); 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("
</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>Table name</td>");
     for (int i = 1; i <= colCount; ++i) {
       out.println("<td>" + metaData.getTableName(i) + "</td>");
     }
   } else if (type.equals("name")) {
     out.println("<td>Column name</td>");
     for (int i = 1; i <= colCount; ++i) {
       out.println("<td>" + metaData.getColumnName(i) + "</td>");
     }
   } else if (type.equals("index")) {
     out.println("<td>Column index</td>");
     for (int i = 1; i <= colCount; ++i) {
       out.println("<td>" + i + "</td>");
     }
   } else if (type.equals("column type")) {
     out.println("<td>Column type</td>");
     for (int i = 1; i <= colCount; ++i) {
       out.println("<td>" + metaData.getColumnTypeName(i) + "</td>");
     }
   } else if (type.equals("column display")) {
     out.println("<td>Column display size</td>");
     for (int i = 1; i <= colCount; ++i) {
       out
           .println("<td>" + metaData.getColumnDisplaySize(i)
               + "</td>");
     }
   }
   out.println("</tr>");
 }//printMeta

} 


      </source>



Database and Servlet: Store procedure

   <source lang="java">

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("

The Event named " + eventName + " has been added to the database

"); 
   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

} 


      </source>



Database transaction

   <source lang="java">

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("

These SQL statements are part of a transaction

"); 
   out.println("CallableStatement.executeUpdate()");
   out.println("

");
   out.println("Statement.executeUpdate()");
   out.println("

");
   try {
     conn = pool.getConnection();
     out.println("AutoCommit before setAutoCommit(): "
         + conn.getAutoCommit() + "

");
     out.println("Transaction isolation level: ");
     switch (conn.getTransactionIsolation()) {
     case 0:
       out.println("TRANSACTION_NONE

");
       break;
     case 1:
       out.println("TRANSACTION_READ_UNCOMMITTED

");
       break;
     case 2:
       out.println("TRANSACTION_READ_COMMITTED

");
       break;
     case 4:
       out.println("TRANSACTION_REPEATABLE_READ

");
       break;
     case 8:
       out.println("TRANSACTION_SERIALIZABLE

");
       break;
     default:
       out.println("UNKNOWN

");
     }
     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

} 


      </source>



Dedicated Connection Servlet

   <source lang="java">

/* 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() + "

"); } 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) { } } } </source>

Delete Blob From Servlet

   <source lang="java">

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>

deleted photo with id=" + id + "

</body></html>"); 
   } catch (Exception e) {
out.println("<body>

Error=" + e.getMessage() + "

</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;
 }

} 

      </source>



Delete Clob From Oracle in a Servlet

   <source lang="java">

/* 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>

deleted CLOB record with id=" + id + "

</body></html>"); 
   } catch (Exception e) {
out.println("<body>

Error=" + e.getMessage() + "

</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);
 }

} 

      </source>



Delete Clob From Servlet

   <source lang="java">

/* 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>

deleted CLOB record with id=" + id + "

</body></html>"); 
   } catch (Exception e) {
out.println("<body>

Error=" + e.getMessage() + "

</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;
 }

} 

      </source>



Display Blob Servlet

   <source lang="java">

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>

Database Connection Problem.

</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>

No photo found for id= 001

</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>

Error=" + e.getMessage() + "

</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;
 }

} 

      </source>



Display Clob Servlet

   <source lang="java">

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>

Database Connection Problem.

"); out.println("
" + e.getMessage() + "
</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>

No file found for id=" + id + "

</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>

Error=" + e.getMessage() + "

</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;
 }

} 

      </source>



Get Column Names From ResultSet

   <source lang="java">

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;
 }

} 

      </source>



Guest Book Servlet

   <source lang="java">

/* 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("
"); 
   out.println("<form action=\"personal/guestbook.shtml\" "
       + "method=\"POST\">");
out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println("
Name:<input type=\"text\" name=\"name\" " + "size=\"30\"/><input type=\"submit\" value=\"Save\"/>
Email:<input type=\"text\" name=\"email\" " + "size=\"30\"/>
Comments:
"); 
   out.println("<textarea name=\"comments\" cols=\"40\" rows=\"7\">");
out.println("</textarea>
"); 
   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("
"); 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("
" + name + " (" + email + ") on " + fmt.format(date) + "
"); cmt = noXML(cmt); out.println("
" + cmt + "
"); } out.println("
"); 
   } 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;
 }

} 

      </source>



Insert Clob to MySql Servlet

   <source lang="java">

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>

OK: inserted a new record with id=" + id + "

</body></html>"); 
   } catch (Exception e) {
     e.printStackTrace();
out.println("<body>

Error: " + e.getMessage() + "

</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();
   }
 }

} 

      </source>



JDBC and Servlet

   <source lang="java">

/* 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("
    "); while (rs.next()) { out.println("
  • " + rs.getString("ID") + " " + rs.getString("title") + " " + rs.getString("price")); } out.println("
"); 
     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);
 }

} 

      </source>



Login Servlets

   <source lang="java">

/* 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:

"); 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); } } </source>

OCCI Connection Servlet

   <source lang="java">

/* 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;
 }

} 

      </source>



Process a raw SQL query; use ResultSetMetaData to format it

   <source lang="java">

/* 

* 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("Error: malformed query, contact administrator");
     return;
   }
   // NB MUST also check for admin privs before proceding!
   try {  // SQL
out.println("<p>Your query: " + query + "

"); 
     stmt.execute(query);
     ResultSet rs = stmt.getResultSet();
     if (rs == null) {
       // print updatecount
out.println("

Result: updateCount = " + stmt.getUpdateCount() + "</p>"); } else { // process resultset out.println("
Your response:"); ResultSetMetaData md = rs.getMetaData(); int count = md.getColumnCount(); out.println("

"); out.print(""); for (int i=1; i<=count; i++) { out.print(""); while (rs.next()) { out.print(""); for (int i=1; i<=count; i++) { out.print(""); } } out.println("
"); 
         out.print(md.getColumnName(i));
       }
out.println("
"); 
         out.print(rs.getString(i));
       }
out.println("
"); 
     // rs.close();
   } catch (SQLException ex) {
     out.print("<B>" + getClass() + ": SQL Error:\n" + ex);
out.print("
");
      ex.printStackTrace(out);
      out.print("
"); 
   }
 }
 public void destroy() {
   try {
     conn.close();  // All done with that DB connection
   } catch (SQLException ex) {
     log(getClass() + ": destroy: " + ex);
   }
 }

} 


      </source>



See Account

   <source lang="java">

/* 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:
");
out.println(""); out.println("
"); 
     for (int i = 1; i <= md.getColumnCount(); i++) {
       out.println("Column #" + i + "
");
       out.println("getColumnName : " + md.getColumnName(i) + "
");
       out.println("getColumnClassName : " + md.getColumnClassName(i)
           + "
");
       out.println("getColumnDisplaySize : "
           + md.getColumnDisplaySize(i) + "
");
       out.println("getColumnType : " + md.getColumnType(i) + "
");
       out.println("getTableName : " + md.getTableName(i) + "
");
out.println("
"); 
     }
     out.println("</BODY></HTML>");
   } catch (Exception e) {
     e.printStackTrace();
   }
 }
 public void doPost(HttpServletRequest inRequest,
     HttpServletResponse outResponse) throws ServletException,
     IOException {
   doGet(inRequest, outResponse);
 }

} 

      </source>



Servlets Database Query

Session Login JDBC

   <source lang="java">

/* 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:

"); 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) { } } } } </source>

Transaction Connection Servlet

   <source lang="java">

/* 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);
 }

} 

      </source>



Typical database commands

   <source lang="java">

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("

Database info

"); out.println(""); 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(""); 
     }
out.println(""); while (rs.next()) { out.println(""); for (int i = 1; i <= colCount; ++i) out.println(""); out.println(""); } } catch (Exception e) { throw new ServletException(e.getMessage()); } finally { try { stmt.close(); conn.close(); } catch (SQLException sqle) { } } out.println("
" + rsm.getColumnName(i) + "
" + rs.getString(i) + "


"); 
   out.println("</body>");
   out.println("</html>");
   out.close();
 } //doGet

} 


      </source>



Update Clob data stored in MySql from a Servlet

   <source lang="java">

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>

OK: updated an existing record with id=" + id + "

</body></html>"); 
   } catch (Exception e) {
     e.printStackTrace();
out.println("<body>

Error: " + e.getMessage() + "

</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();
   }
 }

} 

      </source>



== Using JDBC in Servlets ==

Источник — «http://www.jexp.ru/index.php?title=Java/Servlets/Database&oldid=6317»

Навигация