Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 4 of 4

Thread: updating database

  1. #1
    Junior Member
    Join Date
    Aug 2009
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Exclamation updating database

    import java.sql.*;
     
    public class database
    {Connection con;
     int count;  
     number_sequence nsq;
     public database() throws ClassNotFoundException
     {nsq=new number_sequence();
      try
      {Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      con=DriverManager.getConnection("jdbc:odbc:customerBase");
      Statement st=con.createStatement();
      ResultSet res = st.executeQuery("SELECT COUNT(*) FROM customerbase");
      while (res.next())
      {count = res.getInt(1);
      }
      System.out.println("Number of Records"+count);
     
      }catch(SQLException e)
      {System.out.println(e);
      }
      catch(Exception e)
      {System.out.println(e.getMessage());
      }
     }
     
     
    public void add_to_database(customer c)
    {try
     {PreparedStatement ps=con.prepareStatement("insert into customerbase values(?,?,?)");
      int random=nsq.integer_generator_upto(10000);
      c.set_acc_no(random);
      ps.setInt(1,random );
      ps.setString(2, c.get_name());
      ps.setInt(3,c.get_no_of_rentals());
      int x=ps.executeUpdate();
      System.out.println(x);
      System.out.println("ID generated for customer is "+random);
     }
     catch(SQLException e)
     {System.out.println(e);
      System.out.println('*');
     }
     catch(Exception e)
     {System.out.println(e.getMessage());
     }
     }
     
    public void copy_database_into(LinkedList<customer> list) throws SQLException
    {customer temp;
     try
     {PreparedStatement ps=con.prepareStatement("select * from customerbase");
     ResultSet rs=ps.executeQuery();
     while(rs.next())
     {temp=new customer(rs.getInt("ID"),rs.getString("cust_name"),rs.getInt         ("no_of_rentals"));
      list.add(temp);
     }
     }
     catch(SQLException e)
     {System.out.println(e);
      System.out.println("this is an SQL error");
     }
     catch(Exception e)
     {System.out.println(e.getMessage());
     }
     System.out.println("database has been successfully copied to list");
    }
     
    public void update_rentals_for(int id,int by_)
    {
     try
     {PreparedStatement ps=con.prepareStatement("select no_of_rentals from customerbase where ID=id");
      ResultSet rs=ps.executeQuery();
      rs.next();
      int rentals=rs.getInt("no_of_rentals");
      System.out.println("no of rentals is "+rentals);
      System.out.println("the rentals is to be updated by "+by_);
      rentals+=by_;
      PreparedStatement ps2=con.prepareStatement("update customerbase set no_of_rentals=? where ID=?");
      ps2.setInt(1, rentals);
      ps2.setInt(2,id);
      int r=ps2.executeUpdate();
      System.out.println("updated "+rentals);
      }
     catch(SQLException e)
     {System.out.println(e);
     }
     catch(Exception e)
     {}
    }

    The output is

    run:
    Number of Records23
    1
    ID generated for customer is 3070
    BUILD SUCCESSFUL (total time: 10 seconds)



    This means there is no error in constructor....

    as it gives the no of records in the database...

    the update statement returns 1

    means there was no database access error..etc.

    no SQLException or general exception was thrown....

    n the last line has been displayed

    means the control passed through all the statements...

    bt the problem is no update gets done in either of the function....

    Of the 23 records that it mentioned,the function worked fine initilly bt then even though i didn't changed the code...it literally stopped responding....

    How come this happen without any error?

    plz help i am stuck up............


  2. #2
    Junior Member
    Join Date
    Aug 2009
    Posts
    8
    Thanks
    1
    Thanked 1 Time in 1 Post

    Default Re: updating database

    Hi gurpreetm13,

    Try giving a shot to the program i gave you below. Its your program only. But i did make some changes in it. See carefully i have added three lines:
    st.close();
    con.commit();
    con.close();
     
    public class database
    {
        Connection con;
        int count; 
        number_sequence nsq;
        public database() throws ClassNotFoundException
       {
           nsq=new number_sequence();
           try
          {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            con=DriverManager.getConnection("jdbc:odbc:custome rBase");
            Statement st=con.createStatement();
            ResultSet res = st.executeQuery("SELECT COUNT FROM customerbase");
            while (res.next())
            {
              count = res.getInt(1);
             }
             System.out.println("Number of Records"+count);
     
             st.close();
             con.commit();
             con.close();
         }
         catch(SQLException e)
         {
           System.out.println(e);
          }
          catch(Exception e)
         {
           System.out.println(e.getMessage());
          }
    }

    the database doesnot gets updated untill you commit it.

    Regards,
    guptapr

  3. The Following User Says Thank You to guptapr For This Useful Post:

    gurpreetm13 (October 9th, 2009)

  4. #3
    Super Moderator Json's Avatar
    Join Date
    Jul 2009
    Location
    Warrington, United Kingdom
    Posts
    1,274
    My Mood
    Happy
    Thanks
    70
    Thanked 156 Times in 152 Posts

    Default Re: updating database

    You should actually put that in a try finally block I'd say because if an exception is thrown before you reach the call to the close method the connection will still stay open.

    // Json

  5. #4
    Junior Member
    Join Date
    Aug 2009
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default Re: updating database

    Thanks so much...u were right buddy..
    thank u so much...

Similar Threads

  1. Database synchronization from RMS with MySQL in Online mode
    By jeremyraj in forum Java ME (Mobile Edition)
    Replies: 1
    Last Post: January 24th, 2011, 07:08 AM
  2. application Task problem - updating JTree
    By idandush in forum AWT / Java Swing
    Replies: 2
    Last Post: June 18th, 2009, 03:15 AM
  3. Database connection using NetBeans
    By jcc285 in forum Java IDEs
    Replies: 6
    Last Post: June 9th, 2009, 03:23 AM
  4. Desktop Database Application
    By TCoomer in forum JDBC & Databases
    Replies: 2
    Last Post: June 4th, 2009, 03:51 PM
  5. Website to study Struts framework
    By kirman in forum Java IDEs
    Replies: 2
    Last Post: October 17th, 2008, 07:26 AM