Thread: 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; 
                    }
                }

Originally Posted by Muhanguzi

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.

Originally Posted by Muhanguzi

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.

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.