JDBC Examples

JDBC example usage
Using JDBC to access various databases.

Home
›
Code snippets
›
Java
›
JDBC example usage

Here's an example of using JDBC with Access, SQL Server, mySQL and Oracle.

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

/*
mySQL JDBC type4 driver - http://mmmysql.sourceforge.net/
*/

public class JDBCExample extends HttpServlet
{

public void doAccessGet(HttpServletRequest request,HttpServletResponse response)
   throws ServletException, IOException
   {

try
      {
         // Driver
         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

// Connect to db
         String dbinfo = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\\test.mdb";

Connection conn = DriverManager.getConnection(dbinfo);

// Execute SQL, get recordset
         Statement stmt = conn.createStatement();
         ResultSet RS = stmt.executeQuery("SELECT * FROM table");
         // NB use: "stmt.executeUpdate()" for INSERT,UPDATE,DELETE

// If there are rows
         if (RS != null)
         {
            response.setContentType("text/html");
            PrintWriter out = response.getWriter();
            out.println(dbinfo);
            while(RS.next())
            {
               out.println(RS.getString("row")+"<br>");
            }
         }
         stmt.close();
         conn.close();

} catch(Exception e){
         response.setContentType("text/html");
         PrintWriter out = response.getWriter();
         out.println("Error: "+e);
      }

}

public void doSqlServerGet(HttpServletRequest request,HttpServletResponse response)
   throws ServletException, IOException
   {

try
      {
         // Driver
         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

// Connect to db
         String dbinfo = "jdbc:odbc:Driver={SQL Server};Server=server.domain.com;";
         dbinfo += "Database=database;UID=****;PWD=****;";

Connection conn = DriverManager.getConnection(dbinfo);

// If there are rows
         if (RS != null)
         {
            response.setContentType("text/html");
            PrintWriter out = response.getWriter();
            out.println(dbinfo);
            while(RS.next())
            {
               out.println(RS.getString("row")+"<br>");
               response.flushBuffer();
            }
         }
         stmt.close();
         conn.close();

} catch(Exception e){
         response.setContentType("text/html");
         PrintWriter out = response.getWriter();
         out.println("Error: "+e);
      }

}

public void doMySqlGet(HttpServletRequest request,HttpServletResponse response)
   throws ServletException, IOException
   {

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

// Connect to db
         String dbinfo = "jdbc:mysql:///server?user=***&password=***";

Connection conn = DriverManager.getConnection(dbinfo);

// Example of parsing a string to int     
         int limit = 100;
         if (request.getParameter("limit") != null)
         {
            try
            {
               limit = Integer.parseInt(request.getParameter("limit"));
            } catch (NumberFormatException e){
               limit = 100;
            }

}

// Execute SQL, get recordset
         Statement stmt = conn.createStatement();
         ResultSet RS = stmt.executeQuery("SELECT * FROM table LIMIT "+limit);
         // NB use: "stmt.executeUpdate()" for INSERT,UPDATE,DELETE

// If there are rows
         if (RS != null)
         {
            response.setContentType("text/html");
            PrintWriter out = response.getWriter();
            while(RS.next())
            {
               out.println(RS.getString("row")+"<br>");
            }
         }
         stmt.close();
         conn.close();

} catch(Exception e){
         response.setContentType("text/html");
         PrintWriter out = response.getWriter();
         out.println("Error: "+e);
      }

}

public void doOracleGet(HttpServletRequest request,HttpServletResponse response)
   throws ServletException, IOException
   {

try
      {

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

Class.forName("oracle.jdbc.driver.OracleDriver");

// Connect to db, assuming you have a TNSNAME.ORA file with the entry         
         Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:@database.domain.com",
         "username","password");

// Execute SQL, get recordset
         Statement stmt = conn.createStatement();
         ResultSet RS = stmt.executeQuery("SELECT * FROM \"table\"");
         int i = 0;

// If there are rows
         if (RS != null)
         {
            while(RS.next())
            {
               out.println(RS.getString("row")+"<br>");
            }
         }
         stmt.close();
         conn.close();

} catch(ClassNotFoundException e){
         response.setContentType("text/html");
         PrintWriter out = response.getWriter();
         out.println("Error: "+e);
      } catch(SQLException s){
         response.setContentType("text/html");
         PrintWriter out = response.getWriter();
         out.println("Error: "+s.getSQLState()+","+s.getMessage()+" ("+s+")");
      }

}