Results 1 to 3 of 3
  1. #1
    initmax's Avatar
    initmax is offline Member
    Join Date
    Oct 2009
    Posts
    9
    Rep Power
    0

    Default J2EE = Servlet+Mysql connect

    Hello All!
    I need your help to solve my question.
    I developed a Servlet + jsp application.
    I tested it in local with Apache Tomcat/6.0.20 and it works correctly.
    I write and use these classes:

    PHP Code:
    /*
     * Class CreateBaseConnection provide connect to base MySQL
     * 
     */
    package MySQL;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    
    
    /**
     *
     * @author initmax
     */
    public class MySQLConnect {
    
    private String url = null;
    private String name = null;
    private String password = null;
    
    
    //По умолчанию
        public MySQLConnect()
        {
          url = "jdbc:mysql://localhost:3030/initmax"+"?autoReconnect=true&useUnicode=true&characterEncoding=utf8"+
                  "&SET NAMES utf8";
          name = "root";
          password = "pass";
        }
    
    
    
        public MySQLConnect(String a_url, String a_name, String a_password){
            url = a_url;
            name = a_name;
            password = a_password;
        }
    
    
    //***********************************************************************
    private Connection CurrentConnect;
    
    //this line need out to txt file
    
    
    //***********************************************************************
    //first need download driver, for work MySQL connect
        public void DownloadDriver()
        {
            try
            {
                Class.forName("com.mysql.jdbc.Driver");
    //            System.out.println("Driver loading success!");
            }
    
            catch (ClassNotFoundException e)
            {
                e.printStackTrace();
            }
        }
    
    //***********************************************************************
    //Connected to MySQL Base use download driver
         public void Connected()
         {          
                try
                {
                    CurrentConnect = DriverManager.getConnection(url, name, password);
    //                System.out.println("Connected.");
                }
    
                catch (SQLException e)
                {
                        e.printStackTrace();
                }
         }
            
        
    //*********************************************************************
    //disconnected base after finish work, get object connect
         public int DisConnected()
         {
             try
             {
              CurrentConnect.close();
              System.out.println("Disconnected.");
             }
    
             catch (SQLException e)
             {
                    e.printStackTrace();
             }
    
             return 0;
          }
    
    //*********************************************************************
    //get current connection
          public Connection GetConnection()
          {
              return this.CurrentConnect;
          }
    
    }


    package MySQL;

    /*
    * Class for create page, based MySQL data
    */


    //import java.sql.Connection;
    //import java.io.*;

    /**
    *
    * @author initmax
    */
    public class GenPageMySQL {

    private int id=0;
    private String theme=null;
    private String page=null;
    private String column=null;


    public GenPageMySQL(){

    }


    public GenPageMySQL(int a_id, String a_theme, String a_page){
    id = a_id;
    theme = a_theme;
    page = a_page;
    }


    /**
    * @return the id
    */
    public int getId() {
    return id;
    }

    /**
    * @param id the id to set
    */
    public void setId(int id) {
    this.id = id;
    }

    /**
    * @return the theme
    */
    public String getTheme() {
    return theme;
    }

    /**
    * @param theme the theme to set
    */
    public void setTheme(String theme) {
    this.theme = theme;
    }

    /**
    * @return the page
    */
    public String getPage() {
    return page;
    }

    /**
    * @param page the page to set
    */
    public void setPage(String page) {
    this.page = page;
    }

    /**
    * @return the column
    */
    public String getColumn() {
    return column;
    }

    /**
    * @param column the column to set
    */
    public void setColumn(String column) {
    this.column = column;
    }

    }



    package MySQL;

    /*
    * Class for create MySQL query
    */

    import java.util.*;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;

    /**
    *
    * @author initmax
    */
    public class MySQLQuery {

    private Connection CurrentConnect;
    private Vector VectorPageObj = new Vector();


    public MySQLQuery() {}


    public MySQLQuery(Connection CurrentConnectBase)
    {
    CurrentConnect = CurrentConnectBase;
    }

    //************************************************** ***************

    public Vector SelectAllField(String NameTable)
    {
    try
    {

    Statement st = CurrentConnect.createStatement();
    String query = ("select * from "+NameTable);
    ResultSet resultQuery = null;
    resultQuery = st.executeQuery(query);


    if(resultQuery == null) { return VectorPageObj; }


    while (resultQuery.next())
    {
    GenPageMySQL PageObj = new GenPageMySQL();

    PageObj.setId(resultQuery.getInt("id"));
    PageObj.setTheme(resultQuery.getString("theme"));
    PageObj.setPage(resultQuery.getString("page"));

    VectorPageObj.add(PageObj);
    }


    }
    catch (SQLException e) {
    e.printStackTrace();
    }

    return VectorPageObj;
    }



    /**
    *@set the CurrentConnect
    */
    public void setConnection(Connection CurrentConnectBase) {
    CurrentConnect = CurrentConnectBase;
    }

    }


    And Servlet "start point"

    import java.util.*;
    import java.io.*;
    import javax.servlet.*;
    import javax.servlet.http.*;
    import MySQL.*;

    public class indexServlet extends HttpServlet {

    private String getpage;
    private MySQLConnect MySQLConnectObj = new MySQLConnect();
    private MySQLQuery MySQLQueryObj = new MySQLQuery();


    public void init(){
    MySQLConnectObj.DownloadDriver();
    MySQLConnectObj.Connected();


    MySQLQueryObj.setConnection(MySQLConnectObj.GetCon nection());
    }


    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException, NullPointerException {
    init();


    PrintWriter out = response.getWriter();
    getpage = request.getParameter("page");


    out.print("MySQLConnectObj.GetConnection() = "+MySQLConnectObj.GetConnection());



    if(MySQLConnectObj.GetConnection() != null)
    {

    Vector VectorPage = new Vector();
    VectorPage = MySQLQueryObj.SelectAllField("up_menu");

    GenPageMySQL PageObj = new GenPageMySQL();
    for(int i=0; i<VectorPage.size(); ++i)
    {
    // PageObj = (PageObj)VectorUpMenuObj.elementAt(i);
    request.setAttribute("up_menu_theme",VectorPage.el ementAt(i));

    RequestDispatcher Dispatcher = getServletContext().getRequestDispatcher("/WEB-INF/jsp/index.jsp");
    Dispatcher.forward(request, response);
    }

    }


    else if(MySQLConnectObj.GetConnection() == null){


    out.print("MySQLConnectObj.GetConnection() = "+MySQLConnectObj.GetConnection());

    init() ;
    }


    return;
    }

    }

    If i start this classes in Java Application, connect MySQL and query works.
    But if I start this project how J2ee in NetBeans, get error in log Tomcat


    28.10.2009 15:41:58 org.apache.catalina.core.AprLifecycleListener init
    INFO: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: C:\Program Files\Java\jdk1.5.0_06\bin;.;C:\WINDOWS\system32;C :\WINDOWS;C:\Program Files\PC Connectivity Solution\;C:\Java\jre6\bin;;;C:\Program Files\Java\jdk1.5.0_06\bin
    28.10.2009 15:41:58 org.apache.coyote.http11.Http11Protocol init
    INFO: Initializing Coyote HTTP/1.1 on http-8080
    28.10.2009 15:41:58 org.apache.catalina.startup.Catalina load
    INFO: Initialization processed in 899 ms
    28.10.2009 15:41:58 org.apache.catalina.core.StandardService start
    INFO: Starting service Catalina
    28.10.2009 15:41:58 org.apache.catalina.core.StandardEngine start
    INFO: Starting Servlet Engine: Apache Tomcat/6.0.20
    28.10.2009 15:41:59 org.apache.catalina.core.StandardContext resourcesStart
    SEVERE: Error starting static Resources
    java.lang.IllegalArgumentException: Document base K:\NetBeans_project\FirstServlet\build\web does not exist or is not a readable directory
    at org.apache.naming.resources.FileDirContext.setDocB ase(FileDirContext.java:142)
    at org.apache.catalina.core.StandardContext.resources Start(StandardContext.java:4048)
    at org.apache.catalina.core.StandardContext.start(Sta ndardContext.java:4217)
    at org.apache.catalina.core.ContainerBase.addChildInt ernal(ContainerBase.java:791)
    at org.apache.catalina.core.ContainerBase.addChild(Co ntainerBase.java:771)
    at org.apache.catalina.core.StandardHost.addChild(Sta ndardHost.java:526)
    at org.apache.catalina.startup.HostConfig.deployDescr iptor(HostConfig.java:630)
    at org.apache.catalina.startup.HostConfig.deployDescr iptors(HostConfig.java:556)
    at org.apache.catalina.startup.HostConfig.deployApps( HostConfig.java:491)
    at org.apache.catalina.startup.HostConfig.start(HostC onfig.java:1206)
    at org.apache.catalina.startup.HostConfig.lifecycleEv ent(HostConfig.java:314)
    at org.apache.catalina.util.LifecycleSupport.fireLife cycleEvent(LifecycleSupport.java:119)
    at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1053)
    at org.apache.catalina.core.StandardHost.start(Standa rdHost.java:722)
    at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1045)
    at org.apache.catalina.core.StandardEngine.start(Stan dardEngine.java:443)
    at org.apache.catalina.core.StandardService.start(Sta ndardService.java:516)
    at org.apache.catalina.core.StandardServer.start(Stan dardServer.java:710)
    at org.apache.catalina.startup.Catalina.start(Catalin a.java:583)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:585)
    at org.apache.catalina.startup.Bootstrap.start(Bootst rap.java:288)
    at org.apache.catalina.startup.Bootstrap.main(Bootstr ap.java:413)
    28.10.2009 15:41:59 org.apache.catalina.core.StandardContext start
    SEVERE: Error in resourceStart()
    28.10.2009 15:41:59 org.apache.catalina.core.StandardContext start
    SEVERE: Error getConfigured
    28.10.2009 15:41:59 org.apache.catalina.core.StandardContext start
    SEVERE: Context [/FirstServlet] startup failed due to previous errors
    28.10.2009 15:41:59 org.apache.catalina.core.StandardContext stop
    INFO: Container org.apache.catalina.core.ContainerBase.[Catalina].[localhost].[/FirstServlet] has not been started
    28.10.2009 15:42:01 org.apache.coyote.http11.Http11Protocol start
    INFO: Starting Coyote HTTP/1.1 on http-8080
    28.10.2009 15:42:01 org.apache.jk.common.ChannelSocket init
    INFO: JK: ajp13 listening on /0.0.0.0:8009
    28.10.2009 15:42:01 org.apache.jk.server.JkMain start
    INFO: Jk running ID=0 time=0/156 config=null
    28.10.2009 15:42:01 org.apache.catalina.startup.Catalina start
    INFO: Server startup in 2502 ms
    28.10.2009 15:42:02 org.apache.catalina.startup.HostConfig checkResources
    INFO: Undeploying context [/initmax]
    28.10.2009 15:42:02 org.apache.catalina.core.StandardContext start
    INFO: Container org.apache.catalina.core.ContainerBase.[Catalina].[localhost].[/initmax] has already been started

    what could be wrong?

  2. #2
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    835
    Rep Power
    6

    Default

    have u checked ur tomcat path?
    Ramya:cool:

  3. #3
    travishein's Avatar
    travishein is offline Senior Member
    Join Date
    Sep 2009
    Location
    Canada
    Posts
    684
    Rep Power
    5

    Default

    p.s. not related, but instead of building your own mysql jdbc connect / disconnect, have you considered using a pool of connections managed by the commons-pool.

    the motivation being, there is sometimes a latency in the time required to establish a connection to the database. and also for concurrency, many users could possibly be invoking the web application concurrently and the requests would fall to the same object instance (servlet) with that one reference to teh jdbc connection. its possible if one user causes an application, or sql exception, the other user's would also see an error message, like if something causes teh connection to cose for some reason.

    tomcat even has an implementation built in

    but sometimees its convenient to have it entirely booted from within the web application, such as to drop in the commons-dbcp and commons-pool.jar files into WEB-INF/lib and then do some
    Java Code:
    BasicDataSource ds = new BasicDataSource();
          ds.setUrl(this.getJdbcUrl());
          ds.setUsername(this.getDbUser());
          ds.setPassword(this.getDbPass());
          ds.setDriverClassName(this.getDriverClass());
    where the commons-pool datasource implements the javax.sql.DataSource interface, and the datasource handle can be shoved into the application properties, or JNDI variables. then your jdbc code

    your query object would no longer have a member reference to teh connection object, and in stead would fetch the connection from the pool when it needed it. just close the connection when finished, and it recycles it into the pool. Heck, we can instead jam the DataSource object into this servlet bean.

    also, it is very important that you close your JDBC ResultSet, Statements. oherwise its possible to have an exausted resource within the database (requiring mysql hard restart). Even if the objects are naturally garbage collected, it is better to always close them explicitly after you no longer need them, as in high site activity, its possible for the user requests and database querying to overrun the java garbage collection, and thus stalling the database by having all available cursors, file handles, etc tied up to defunct java objects.

    for example (modified your function)
    Java Code:
    DataSource dataSource
    public void setDataSource(DataSource ds) { this.dataSource = ds; }
    
    public Vector SelectAllField(String NameTable)
    {
      Connection connection = null;
      Statement st = null
      ResultSet resultQuery = null;
      try
      {
        connection = dataSource.getConnection();
        st = connection.createStatement();
        String query = ("select * from "+NameTable);
        resultQuery = st.executeQuery(query);
        while (resultQuery.next())
        {
          GenPageMySQL PageObj = new GenPageMySQL();
          PageObj.setId(resultQuery.getInt("id"));
          PageObj.setTheme(resultQuery.getString("theme"));
          PageObj.setPage(resultQuery.getString("page"));
          VectorPageObj.add(PageObj);
        } // while (rs.next())
      }
      catch (SQLException e) {
        e.printStackTrace();
      }
      finally {
        if (resultQuery != null) {
          try {
            resultQuery.close();
          }
          catch (SQLException ex) {}
        }
    
        if (st != null) {
          try {
            st.close();
          }
          catch(SQLException ex) {}
        }
    
        if (connection != null) {
          try {
            connection.close();  // the connection is wrapped by  datasource connection pool 
                                         // it won't really get closed, but will get released back to the pool.
          }
          catch(SQLException ex) {}
        }
      }
      return VectorPageObj;
    }
    , so yes, all that fun stuff in the finally {} is needed. from now on every jdbc query should have that block.

    I personally hate writing all that crunk out every time, so I made a convenience method (taking advantage of java 5 varargs)

    Java Code:
    public class SQLUtil {
      /**
       * Allows for closing of any of conneciton, statement, resultset.
       * 
       * @param items
       *          Any instanceof {Connection, Statement, ResultSet}. Parameters should be specified in order of most
       *          specific, i.e. ResultSet, Statement, then Connection last.
       */
      public static void close(Object... items) {
        for (Object item : items) {
          if (item == null) { 
            continue;
          }
          if (item instanceof ResultSet) {
            close((ResultSet) item);
          }
          else if (item instanceof Statement) {
            close((Statement) item);
          }
          else if (item instanceof Connection) {
            close((Connection) item);
          }
        }
      }
    
    }
    so the finally block of that method would now look like:
    Java Code:
    public Vector SelectAllField(String NameTable)
    {
      Connection connection = null;
      Statement st = null
      ResultSet resultQuery = null;
      try
      {
        connection = dataSource.getConnection();
        st = connection.createStatement();
        String query = ("select * from "+NameTable);
        resultQuery = st.executeQuery(query);
        while (resultQuery.next())
        {
      // .stuff.. 
        } // while (rs.next())
      }
      catch (SQLException e) {
        e.printStackTrace();
      }
      finally {
        SQLUtil.close(resultQuery, st, connection);
      }
      return VectorPageObj;
    }
    . note the reverse order is important, most specific result set upwards to the connection last.

    hope that;s helpful.

Similar Threads

  1. connect java with mysql in rhel5
    By niraj.vara in forum New To Java
    Replies: 0
    Last Post: 07-17-2009, 09:23 AM
  2. Replies: 15
    Last Post: 04-15-2009, 01:51 AM
  3. Replies: 2
    Last Post: 06-12-2008, 07:09 AM
  4. Connect applet with mysql
    By baltimore in forum Java Applets
    Replies: 1
    Last Post: 08-07-2007, 06:39 AM
  5. Connect applet with mysql
    By Felissa in forum Java Applets
    Replies: 3
    Last Post: 07-05-2007, 06:26 PM

Posting Permissions

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