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 7 of 7

Thread: I trying to calculate total price.

  1. #1
    Junior Member
    Join Date
    Jun 2011
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Post I trying to calculate total price.

    i have two columns named quantity and unit price in mysql database. Am using an abstactTablemodel. i need some help to calculate the general total prices of all products in the database. thanx in Advance.

    Here is my code.
    import java.sql.*;
    import javax.swing.table.AbstractTableModel;
    public class MyDb extends AbstractTableModel {
     
        private Connection connection;
        private Statement statement;
        private ResultSet resultSet;
        private ResultSetMetaData metaData ;
        private int numberOfRows ;
     
        private boolean  connectedToDatabase = false;
     
        public MyDb(String driver,String url,String username,String password,String query)
     
        throws SQLException , ClassNotFoundException
        {
     
            Class.forName(driver);
            connection = DriverManager.getConnection(url,username,password);
            statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
     
     
              connectedToDatabase = true;
     
              setQuery(query);
     
        }
     
        public Class getColumnClass(int column) throws IllegalStateException
        {
            if ( !connectedToDatabase )
                throw new IllegalStateException( "Not Connected to Database" );
     
            try{
                String className = metaData.getColumnClassName( column + 1 );
     
                return Class.forName( className );              
     
                }
            catch ( Exception exception ){
                exception.printStackTrace();
     
            }
            return Object. class;
     
        }
     
        public int getColumnCount() throws IllegalStateException
        {
            if ( !connectedToDatabase )
               throw new IllegalStateException( "Not Connected to Database" );
     
            try
            {
                return metaData.getColumnCount();
     
            }
            catch( SQLException sqlException )
    {
                sqlException.printStackTrace();
     
            }
            return 0;
        }
     
               public String getColumnName(int column) throws IllegalStateException
                     {
                         if ( !connectedToDatabase )
                    throw new IllegalStateException( "Not Connected to Database" );
     
                         try
                         {
                            return metaData.getColumnName( column + 1 );
     
                         }
     
                         catch ( SQLException sqlException ){
     
                           sqlException.printStackTrace();
     
                          }
                          return ""; 
     
                   }
                      public int getRowCount() throws IllegalStateException
                      {
                         if ( !connectedToDatabase )
                      throw new IllegalStateException( "Not Connected to Database" );
                       return numberOfRows;
     
     
                      }
                      public Object getValueAt( int row, int column )
                       throws IllegalStateException
                      {
                          try
                          {
                              resultSet.absolute( row + 1 );
                            return resultSet.getObject( column + 1 );
     
                          }
                          catch(SQLException sqlException){
                              sqlException.getStackTrace();
                          }
                          return "";
                      }
     
                public void setQuery( String query )
                 throws SQLException, IllegalStateException
                {
                    if ( !connectedToDatabase )
                    throw new IllegalStateException( "Not Connected to Database" );
                    resultSet = statement.executeQuery(query);
                    metaData = resultSet.getMetaData();
                    resultSet.last();               
                    numberOfRows = resultSet.getRow(); 
                    fireTableStructureChanged();           
                }
                    public void disconnectFromDatabase()  
                    {
                        if (!connectedToDatabase)                    
                   return;
                        try
                        {
                            statement.close();                        
                            connection.close(); 
                        }
                        catch(SQLException sqlException )
                        {
                            sqlException.printStackTrace();
                        }
                        finally{
                            connectedToDatabase = false; 
                        }
                    }
    Last edited by copeg; June 15th, 2011 at 02:36 PM.


  2. #2
    Member
    Join Date
    Jun 2011
    Location
    Rhode Island
    Posts
    69
    My Mood
    Bored
    Thanks
    11
    Thanked 7 Times in 6 Posts

    Default Re: I trying to calculate total price.

    Quote Originally Posted by Muhanguzi View Post
    i have two columns named quantity and unit price in mysql database. Am using an abstactTablemodel. i need some help to calculate the general total prices of all products in the database. thanx in Advance.
    To get the total unit price you just multiply the two columns together. then add all the total unit prices together to have a sum() of unit prices.

    quanity * unit price = total unit price;
    general total price = sum (total unit price);

  3. #3
    Junior Member
    Join Date
    Jun 2011
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default Re: I trying to calculate total price.

    Thanks William, but i need a sample code which would retrieve data from those columns, then use the methods above to calculate the general total.

  4. #4
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,320
    Thanks
    181
    Thanked 833 Times in 772 Posts
    Blog Entries
    5

    Default Re: I trying to calculate total price.

    Quote Originally Posted by Muhanguzi View Post
    Thanks William, but i need a sample code which would retrieve data from those columns, then use the methods above to calculate the general total.
    We are not a code repository, so asking for (and expecting) code most likely will not get you very far. Rather, take William's advice and try to write the code yourself. Alternative to William's advice, you might also be able to write an SQL query that performs the operation for you. If you have a specific problem then by all means ask. Lastly, please use the code tags, as they actually help make the code readable.
    Last edited by copeg; June 15th, 2011 at 02:37 PM.

  5. #5
    Member
    Join Date
    Jun 2011
    Location
    Rhode Island
    Posts
    69
    My Mood
    Bored
    Thanks
    11
    Thanked 7 Times in 6 Posts

    Default Re: I trying to calculate total price.

    Quote Originally Posted by Muhanguzi View Post
    i need a sample code which would retrieve data from those columns, then use the methods above to calculate the general total.
    There are many ways to program Java and SQL together, you can have the SQL functions do it for you or just gather all the data and write the program for calculation. You have your connection you query the table.
     select * from mytable;
    get the ResultSet= rs then loop through your rs in a for loop and do your calculation during that time. Return out of your Method with the answer.

    /**
     * takes a string statement and returns the result set.
     * @param statement
     * @return
     * @throws SQLException
     */
        public ResultSet queryDB(String statement) throws SQLException{
            if(isConnected){
                ps = conns.prepareStatement(statement);
                rs = ps.executeQuery();
            }else{
                System.out.println("getting connection in updateDB()");
                conns = getConnection();
                ps = conns.prepareStatement(statement);
                rs = ps.executeQuery();
            }
            return rs;
        }

    this sample fills a Object class with variables from the database.
    public LinkedList<SessionVo> getAll() throws Exception{
     
            LinkedList<SessionVo> ll = new LinkedList<SessionVo>();
            Connection conn = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;
     
            try{
                //Setup SQL Connection
                conn = ServiceLocator.getInstance().getDBConn(ServiceLocator.SMART_DB);
                stmt = conn.prepareStatement(QUERY_GET_ALL);
                rs = stmt.executeQuery();
     
    /*looping though the result set here*/
                while(rs.next()){
                    SessionVo vo = fillVO(rs);
                    ll.add(vo);
                }
     
     
            }catch(Exception e){
                throw new Exception("ERROR Getting All Sessions :" + e.getMessage());
            }
            finally {
                if(rs != null){
                    rs.close();
                }
                if(stmt != null){
                    stmt.close();
                }
                if(conn != null && !conn.isClosed()){
                    conn.close();
                }
            }
            return ll;
        }

    hope the helps a bit more for what your looking for in logic.
    Creating a list of your variable objects then run through the list to get the desired calculation from what you want.
    Last edited by william; June 15th, 2011 at 03:20 PM. Reason: formating and word choice

  6. #6
    Junior Member
    Join Date
    Jun 2011
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default Re: I trying to calculate total price.

    Thanx alot William.

  7. #7
    Member
    Join Date
    Jun 2011
    Location
    Rhode Island
    Posts
    69
    My Mood
    Bored
    Thanks
    11
    Thanked 7 Times in 6 Posts

    Default Re: I trying to calculate total price.

    additionally to get the data from the rs.
    how I filled my Value Object.

     
    /* this is on my class variables */
     
    //my db tables is  sessions
        private static final String COLUMN_ID           = "session_id";  //my columns names in my db  
        private static final String COLUMN_HULL         = "hull_id";
        private static final String COLUMN_TRAINER      = "trainer_id";
     
     
    /**
         * Fill the SessionVo based on data in the ResultSet object
         * @param rs
         * @return completed Value Object 
         * @throws java.sql.SQLException
         */
        private SessionVo fillVO(ResultSet rs) throws SQLException{
     
            SessionVo vo = new SessionVo();
     
            vo.setSessionId(rs.getString(COLUMN_ID));
            vo.setHullId(rs.getInt(COLUMN_HULL));
            vo.setTrainerId(rs.getInt(COLUMN_TRAINER));
            vo.setSystemTimeStart(rs.getDouble(COLUMN_STARTTIME));
            vo.setSystemTimeEnd(rs.getDouble(COLUMN_ENDTIME));
    ...
    ~~~
    ...
            return vo;
        }

    you know your table lay out so you really don't need the meta data set.
    Last edited by william; June 15th, 2011 at 03:38 PM. Reason: code format

Similar Threads

  1. Replies: 5
    Last Post: May 24th, 2011, 10:39 AM
  2. Calculating Price with Command Line - Please Help!
    By rjdelight in forum Object Oriented Programming
    Replies: 3
    Last Post: February 6th, 2011, 04:49 PM
  3. program wont renew total
    By that_guy in forum What's Wrong With My Code?
    Replies: 4
    Last Post: January 17th, 2011, 01:00 PM
  4. Find total number less than average
    By maximus20895 in forum Collections and Generics
    Replies: 2
    Last Post: December 1st, 2010, 01:46 PM
  5. Little Help, Price Reset is not working
    By drkossa in forum AWT / Java Swing
    Replies: 1
    Last Post: January 14th, 2010, 06:00 PM