9 Replies Latest reply: Jan 31, 2007 11:28 AM by 807607 RSS

    How to make one database connectivity and use it again and again

    807607
      My problem is that i didnt want to use jdbc:odbc connectivity again and again but make one instance and use it again and again(in jsp also)
        • 1. Re: How to make one database connectivity and use it again and again
          807607
          What, like connection pooling?

          Anyway, if you're thinking along these lines, it might be worth making the jump to a full object-relational mapping tool (like Hibernate) if you're just doing JDBC right now.
          • 2. Re: How to make one database connectivity and use it again and again
            807607
            If ur doign JDBC in normal java programe then establish the connection in the constructor of that class.if it is in servlets or jsp do it in the init method.
            • 3. Re: How to make one database connectivity and use it again and again
              807607
              f it is in servlets or jsp do it in the init method.
              I strongly suggest that that is a bad idea. Use connection pooling in web apps. Full stop.
              • 4. Re: How to make one database connectivity and use it again and again
                807607
                You may create a class and implement this code and then use it in another class or servlet.By the way this code is written for mysql,you can modify it for another databases.

                public class DBConnection {
                     public Connection con(){
                          Connection conTest=null ;
                          try {
                               Class.forName("com.mysql.jdbc.Driver").newInstance();     conTest=DriverManager.getConnection("jdbc:mysql://localhost:3306/nameofthedatabase","username","password");
                               System.out.println("Connection established...");               
                          } catch (Exception e) {
                               System.out.println("Error...");
                               e.printStackTrace();
                          }
                          return conTest;
                     }
                     public static void main(String[] args) {
                          DBConnection dbc= new DBConnection();
                          dbc.con();
                     }
                }
                • 5. Re: How to make one database connectivity and use it again and again
                  807607
                  If you want to use it again, then just don't close it!!! Easy as.

                  Ted.
                  • 6. Re: How to make one database connectivity and use it again and again
                    807607
                    You may create a class and implement this code and then use it in another
                    class or servlet.By the way this code is written for mysql,you can modify it for
                    another databases.
                    Once again, no, no, no. There are two things wrong with this:
                    1. We're talking about a web application, and in a web app, you should be pooling connections. No excuses.
                    2. Even if you set that aside, your code shouldn't include connect information. You want to be able to change that without recompiling code. It should be in a config file.
                    • 7. Re: How to make one database connectivity and use it again and again
                      807607
                      If you want to use it again, then just don't close
                      it!!! Easy as.

                      Ted.
                      I generally wouldn't advocate leaving a connection open when you leave the method in which it was created, and I would never recommend passing a connection or other database resource to a jsp. It's very easy to create an orphaned resource situation where you think something is closed but the db thinks it still needs it. This is a very good way to spin an application into the ground.

                      Just my 2 krupplenicks worth, your milage may of course vary.

                      PS.
                      • 8. Re: How to make one database connectivity and use it again and again
                        807607
                        I generally wouldn't advocate leaving a connection
                        open when you leave the method in which it was
                        created, and I would never recommend passing a
                        connection or other database resource to a jsp.
                        Excellent point, PS. There are frameworks out there that support this, or you can roll a bit of your own:
                        interface ConnectionClient {
                            void useConnection(Connection con) throws SQLException;
                        }
                        
                        ...
                        
                        public void connect(ConnectionClient client) throws SQLException {
                            Connection con = getConnection();
                            try {
                                client.useConnection(con);
                            } finally {
                                closeConnection(con);
                            }
                        }
                        I also agree that JSP pages shouldn't see database code. No Connections, Statements or ResultSets. Your code should be putting the data into beans and collections of beans, or using disconnected rowsets.
                        • 9. Re: How to make one database connectivity and use it again and again
                          807607
                          be aware, though, that your JSP shouldn't really contain any data access logic at all