SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    bahrain
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up help with database connectivity

    hi

    i am new to servlets. i got this code which is making connection with MySql database but i could not run it on MySql so i have made the MS Access database and now want to changethis code into MS Access database connection . Please if anybody can help me to change it? here are the files.

    ConnectionPoolServlet.java

    Code:
    package sjservlets;
    
    import java.io.*;
    import javax.servlet.*;
    import javax.servlet.http.*;
    import java.sql.*;
    
    /** A class that extent the Connection Pool servlet which 
     *  JDBC connections for this applicaiton (Java Library)
     *
     *  
     */
     
    public class ConnectionPoolServlet extends HttpServlet {
      	protected ConnectionPool connectionPool;
      
      	public void doGet(HttpServletRequest request,
                          HttpServletResponse response)
      			throws ServletException, IOException {
      	}
    
      /** Initialize the connection pool when servlet is
       *  initialized. To avoid a delay on first access, load
       *  the servlet ahead of time yourself or have the
       *  server automatically load it after reboot.
       */
      
    	public void init() {
    		int vendor = DriverUtilities.MYSQL;
        	String driver = DriverUtilities.getDriver(vendor);
        	String host = "localhost";
        	String dbName = "jsp_library";
        	String url = DriverUtilities.makeURL(host, dbName, vendor);
        	String username = "root";
        	String password = "";
        	try {
          		connectionPool =
            	new ConnectionPool(driver, url, username, password,
                               initialConnections(),
                               maxConnections(),
                               true);
        	} catch(SQLException sqle) {
          		System.err.println("Error making pool: " + sqle);
          		getServletContext().log("Error making pool: " + sqle);
          		connectionPool = null;
        	}
      	}
    
      	public void destroy() {
        	connectionPool.closeAllConnections();
      	}
    
      	/** Override this in subclass to change number of initial
       	*  connections.
       	*/
      
      	protected int initialConnections() {
        	return(3);
      	}
    
      	/** Override this in subclass to change maximum number of 
       	*  connections.
       	*/
    
        protected int maxConnections() {
        	return(60);
      	}
      
      	public void doPost(HttpServletRequest request,
    					   HttpServletResponse response)
    		   throws ServletException, IOException{
    		doGet(request, response);
    	}
    	
    	protected void gotoPage(String address, 
    						  HttpServletRequest request,
    						  HttpServletResponse response)
    			throws ServletException, IOException {
    		RequestDispatcher dispatcher = 
    			getServletContext().getRequestDispatcher(address);
    		dispatcher.forward(request, response);
    	}
    }

    ConnectionPool.java

    Code:
    package sjservlets;
    
    import java.sql.*;
    import java.util.*;
    
    /** A class for preallocating, recycling, and managing
     *  JDBC connections.
     *
     *  
     */
    
    public class ConnectionPool implements Runnable {
      private String driver, url, username, password;
      private int maxConnections;
      private boolean waitIfBusy;
      private Vector availableConnections, busyConnections;
      private boolean connectionPending = false;
    
      public ConnectionPool(String driver, String url,
                            String username, String password,
                            int initialConnections,
                            int maxConnections,
                            boolean waitIfBusy)
          throws SQLException {
        this.driver = driver;
        this.url = url;
        this.username = username;
        this.password = password;
        this.maxConnections = maxConnections;
        this.waitIfBusy = waitIfBusy;
        if (initialConnections > maxConnections) {
          initialConnections = maxConnections;
        }
        availableConnections = new Vector(initialConnections);
        busyConnections = new Vector();
        for(int i=0; i<initialConnections; i++) {
          availableConnections.addElement(makeNewConnection());
        }
      }
      
      public synchronized Connection getConnection()
          throws SQLException {
        if (!availableConnections.isEmpty()) {
          Connection existingConnection =
            (Connection)availableConnections.lastElement();
          int lastIndex = availableConnections.size() - 1;
          availableConnections.removeElementAt(lastIndex);
          // If connection on available list is closed (e.g.,
          // it timed out), then remove it from available list
          // and repeat the process of obtaining a connection.
          // Also wake up threads that were waiting for a
          // connection because maxConnection limit was reached.
          if (existingConnection.isClosed()) {
            notifyAll(); // Freed up a spot for anybody waiting
            return(getConnection());
          } else {
            busyConnections.addElement(existingConnection);
            return(existingConnection);
          }
        } else {
          
          // Three possible cases:
          // 1) You haven't reached maxConnections limit. So
          //    establish one in the background if there isn't
          //    already one pending, then wait for
          //    the next available connection (whether or not
          //    it was the newly established one).
          // 2) You reached maxConnections limit and waitIfBusy
          //    flag is false. Throw SQLException in such a case.
          // 3) You reached maxConnections limit and waitIfBusy
          //    flag is true. Then do the same thing as in second
          //    part of step 1: wait for next available connection.
          
          if ((totalConnections() < maxConnections) &&
              !connectionPending) {
            makeBackgroundConnection();
          } else if (!waitIfBusy) {
            throw new SQLException("Connection limit reached");
          }
          // Wait for either a new connection to be established
          // (if you called makeBackgroundConnection) or for
          // an existing connection to be freed up.
          try {
            wait();
          } catch(InterruptedException ie) {}
          // Someone freed up a connection, so try again.
          return(getConnection());
        }
      }
    
      // You can't just make a new connection in the foreground
      // when none are available, since this can take several
      // seconds with a slow network connection. Instead,
      // start a thread that establishes a new connection,
      // then wait. You get woken up either when the new connection
      // is established or if someone finishes with an existing
      // connection.
    
      private void makeBackgroundConnection() {
        connectionPending = true;
        try {
          Thread connectThread = new Thread(this);
          connectThread.start();
        } catch(OutOfMemoryError oome) {
          // Give up on new connection
        }
      }
    
      public void run() {
        try {
          Connection connection = makeNewConnection();
          synchronized(this) {
            availableConnections.addElement(connection);
            connectionPending = false;
            notifyAll();
          }
        } catch(Exception e) { // SQLException or OutOfMemory
          // Give up on new connection and wait for existing one
          // to free up.
        }
      }
    
      // This explicitly makes a new connection. Called in
      // the foreground when initializing the ConnectionPool,
      // and called in the background when running.
      
      private Connection makeNewConnection()
          throws SQLException {
        try {
          // Load database driver if not already loaded
          Class.forName(driver);
          // Establish network connection to database
          Connection connection =
            DriverManager.getConnection(url, username, password);
          return(connection);
        } catch(ClassNotFoundException cnfe) {
          // Simplify try/catch blocks of people using this by
          // throwing only one exception type.
          throw new SQLException("Can't find class for driver: " +
                                 driver);
        }
      }
    
      public synchronized void free(Connection connection) {
        busyConnections.removeElement(connection);
        availableConnections.addElement(connection);
        // Wake up threads that are waiting for a connection
        notifyAll(); 
      }
        
      public synchronized int totalConnections() {
        return(availableConnections.size() +
               busyConnections.size());
      }
    
      /** Close all the connections. Use with caution:
       *  be sure no connections are in use before
       *  calling. Note that you are not <I>required</I> to
       *  call this when done with a ConnectionPool, since
       *  connections are guaranteed to be closed when
       *  garbage collected. But this method gives more control
       *  regarding when the connections are closed.
       */
    
      public synchronized void closeAllConnections() {
        closeConnections(availableConnections);
        availableConnections = new Vector();
        closeConnections(busyConnections);
        busyConnections = new Vector();
      }
    
      private void closeConnections(Vector connections) {
        try {
          for(int i=0; i<connections.size(); i++) {
            Connection connection =
              (Connection)connections.elementAt(i);
            if (!connection.isClosed()) {
              connection.close();
            }
          }
        } catch(SQLException sqle) {
          // Ignore errors; garbage collect anyhow
        }
      }
      
      public synchronized String toString() {
        String info =
          "ConnectionPool(" + url + "," + username + ")" +
          ", available=" + availableConnections.size() +
          ", busy=" + busyConnections.size() +
          ", max=" + maxConnections;
        return(info);
      }
    }
    its urgent plzzz.....
    regards
    phphelp

  2. #2
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    try {
       Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver") ;
       Connection conn = DriverManager.getConnection"jdbc:odbc:yourDataSourceName" + );
    
    } catch (ClassNotFoundException cnfe) {
        cnfe.printStackTrace () ;
    }
    <yourDataSourceName> should be setup throgh your control panel as follows:

    1. From control panel --> administrative tools --> select Data Sources (ODBC)

    2. Set up a user DSN Tab. select ADD --> select Microsoft Access driver (MDB)


    3. Give it a DatasourceName to be used in your URL and select your appropriate *.mdb file from the file System.



    good luck.

  3. #3
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    bahrain
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but i need to know where i have to put this code and what code i have to delete form my file as it is making connection with MYSql? plz because i don't know to make it?????
    its urgent plzzz.....
    regards
    phphelp

  4. #4
    SitePoint Zealot Mr. Ram's Avatar
    Join Date
    Nov 2005
    Posts
    139
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

  5. #5
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this
    public class Databaseconnection
    {

    public Connection getConnection()
    {
    Connection conn=null;
    try
    {
    Class.forName("Oracle.jdbc.driver.OracleDriver"); //this is for oracle.. use suitable driver for mysql
    conn=DriverManager.getConnection("jdbc.oracle.thin.@<ip>portname>schema name>","<username>","<password>");
    }
    catch(ClassNotFoundException ex)
    {
    ex.printStackTrace();
    }
    catch(SQLException ex)
    {
    ex.printStackTrace();
    }
    return conn;
    }

    }

    In servlet

    public void doGet(.....)
    {
    DatabaseConnection dbconn=new DatabaseConnection();
    Connection conn=dbconn.getConnection()
    }

  6. #6
    SitePoint Zealot
    Join Date
    Apr 2006
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what webServer you using mate?

  7. #7
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    bahrain
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    tomcat
    its urgent plzzz.....
    regards
    phphelp


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •