6 Replies Latest reply: Jan 31, 2007 5:58 AM by 807599 RSS

    how to get data from database?

    807599
      like we have the method like getValueAt(row,column) for JTable,
      did we have any method for retrive from database?
        • 1. Re: how to get data from database?
          rahul_akkina
          Well i'd say it more of 50 - 50 thing...

          through you do not have a method by using you can get Data by refering to row & col indexs.
          However, one can simulate it by creating a Datastructure (Collection Object) of such kind.

          REASON:
          =======
          in general a query produce results which could have thousands of rows & columns. Storing that in memory is not a good idea as it could give rise to MemoryOutOf BoundExceptions.


          However you can check the following snippet for a brief understanding of how to pull data from database.... which uses Jdbc-Odbc Driver Bridge to get connected database via odbc
          Connection con = null;
          Statement stmt = null;
          
          HashMap<String,ArrayList> hm = new HashMap<String,ArrayList>();
          
          ArrayList<String>  al;
          
          try{
          
          /** Loads DB Driver class into memory **/
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          
          } catch(ClassNotFoundException cnfe){
          
          
          try{
          /** manually registering driver **/
          DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver);
          
          /** or try
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance(); **/
          
          
          } catch(Exception exp){
          
          System.err.println(exp.getMessage());
          exp.printStackTrace();
          
          }
          
          }
          
          
          
          try{
          
          /** Establishes a Connection to DB VIA odbc **/
          con = DriverManager.getConnection("jdbc:odbc:DataSourceName","username","password");
          
          /** Creating an Object which can execute a Query on DB for Us **/
          stmt = con.createStatement();
          
          /** Holds the a pointer to location where results of the executed query are found **/
          ResultSet rs = stmt.executeQuery("select empid,name from emp");
          
          /*
          
          NOTE: ResultSet in general could be accessed in FORWARD_ONLY direction.Inorder to make it scrollable we have to intialize the mode while declaring the statement
          
          Lets just consider the default case
          */
          
          /** Looping the constraint by the condition until the next record exists **/
          while(rs.next()){
          
          System.out.println(rs.getInt("empid")); // getting the data by refering to the rowname
          // System.out.println(rs.getInt(1)); refering by the index of the row.
          
          ArrayList<String> arl = null;
          
          try{
          
          arl = hm.get("empid");
          arl.add(rs.getString("empid"));
          
          } catch(Exception exp){
          
          arl = new ArrayList<String>;
          arl.add(rs.getString("empid"));
          
          }
          
          hm.put("empid",arl);
          
          ArrayList<String> arll = null;
          
          try{
          
          arll = hm.get("name");
          arl.add(rs.getString("name"));
          
          } catch(Exception exp){
          
          arl = new ArrayList<String>;
          arl.add(rs.getString("name"));
          
          }
          
          hm.put("name",arll);
           
          }
          
          } catch(SQLException sqe){
          
          System.err.println(sqe.getMessage());
          System.err.println("SQL STATE:"+sqe.getSQLState()+",ERROR CODE"+sqe.getErrorCode());
          sqe.printStackTrace();
          
          } finally{
          
          
          try{
          
          if(stmt != null)
          stmt.close();
          
          } catch(SQLException sqe){
          
          System.err.println(sqe.getMessage());
          System.err.println("SQL STATE:"+sqe.getSQLState()+",ERROR CODE"+sqe.getErrorCode());
          sqe.printStackTrace();
          
          }
          
          
          try{
          
          if(con != null && con.isClosed() == false)
          con.close();
          
          } catch(SQLException sqe){
          
          System.err.println(sqe.getMessage());
          System.err.println("SQL STATE:"+sqe.getSQLState()+",ERROR CODE"+sqe.getErrorCode());
          sqe.printStackTrace();
          
          }
          
          con = null;
          stmt = null;
          
          }
          
          /** you can write a function accorindgly based upon using the HashMap of Lists **/
          if you are instrested in getting value of the record of some randomnamed column at that row make use of
          Object obj = rs.getObject("colname");
           or
          Object obj = rs.getObject(col_num);
          Later depending on you requirement you can type cast it to appropriate datatype (class instance).

          Hope this would be of some help.... :)

          REGARDS,
          RaHuL
          • 2. Re: how to get data from database?
            807599
            thanks Rahul.it helps me a lot.
            Another doubt,
            how we can check particular record in our ResultSet?

            for eg:

            if i want to get a data of 'name=ram' from the database , how can we code it?
            • 3. Re: how to get data from database?
              807599
              http://java.sun.com/docs/books/tutorial/jdbc/index.html
              • 4. Re: how to get data from database?
                807599
                String s = rs.getString("b");

                by using this we get the datas in column "b";
                is it any possiblities for getting the data of the particular row ?
                • 5. Re: how to get data from database?
                  807599
                  You can chip away with various question, but honestly, if you are going to use the JDBC, you should read the tutorial.
                  • 6. Re: how to get data from database?
                    807599
                    ok .
                    i will go through the tutorial.
                    thanks .,