Solved

Java: Database

Posted on 2006-12-01
5
190 Views
Last Modified: 2010-03-31
Hello: I have MS Access Table with 2 column:
Column1: Integet Number
Column2: Memo
I need to write java program that reads the table with few thousands of recoeds that has multiple enteries of same number with different memo.

I need to combine all memos and make it one so in new table it just have one entry for each number and has all the memos.

How am I suppose to do that?

I wrote java program but it does not get all memo...

Thanks
0
Comment
Question by:T_Shah
5 Comments
 
LVL 6

Expert Comment

by:SamsonChung
ID: 18055898
can i see your java code?

because I can only assume you are using JDBC.
0
 
LVL 17

Expert Comment

by:contactkarthi
ID: 18055914

create a hash map first to store number as key and memo as value

do a select and iterate the resultset one by one

see if the number exist in the map

if it exist get the value and append the new memo

if it does not exist put an entry with number as key and memo as value

finally iterate the hashmap and insert all the values into the new table using batch in preparedstatement
0
 
LVL 23

Accepted Solution

by:
Ajay-Singh earned 50 total points
ID: 18056190
You can try using this:

        ResultSet rs = connection.createStatement().executeQuery("select num, memo from table order by num");
        int last = -1;
        StringBuilder x = new StringBuilder(128);
        while (rs.next()) {
            int c = rs.getInt(1);
            if(c != last) {
                // Insert to new table.
                PreparedStatement statement = connection.prepareStatement("insert into new_table values (?, ?)");
                statement.setInt(1, last);
                statement.setString(2, x.toString());
                statement.executeUpdate();
               
                x.setLength(0);
                last = c;
            }
            else {
                x.append(rs.getString(2));
            }
        }
0
 
LVL 2

Expert Comment

by:saintsairforce
ID: 18081917
Here is a class that will allow you to get data from your database. I just recently made a database on Access that had about 35 tables and 10,000+enteries per table and this code worked great for it. In this code I called my database "database.mbd"


/************************************************************************
This class is the main database connection handler
************************************************************************/

import java.sql.*;

public class Database{
  protected static Connection connection;
  protected static Statement statement;
 
  /**************************************************************
  Database(boolean autoCommit)
 
  This function starts a connection to the database
  **************************************************************/
 
  public Database(boolean autoCommit){
    startConnection(autoCommit);      
  }  
 
  /**************************************************************
  startConnection(boolean autoCommit)
 
  This function handles the database and the method to connect to it.
  **************************************************************/
 
  public boolean startConnection(boolean autoCommit){
       try {
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String filename = "Server\\database.mdb";
        String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
        database+= filename.trim() + ";DriverID=22}"; // add on to the end
        // now we can get the connection from the DriverManager
        connection = DriverManager.getConnection( database ,"","");// try and create a java.sql.Statement so we can run queries
        connection.setAutoCommit(autoCommit);
        statement = connection.createStatement();
        return true;
        }
        catch (Exception e)
        {
            return false;
        }
         
    }
 
    /**************************************************************
       commitChanges()
 
       This function commits the changes to the database
    **************************************************************/
     
    public boolean commitChanges(){
      try{
         connection.commit();      
         return true;
      }catch( Exception e){
        return false;    
      }
    }

    /**************************************************************
       endConnection()
 
       This function closes the connection to the database.
    **************************************************************/
   
    public boolean endConnection(){
       try{
         connection.close();    
         return true;
       }catch (Exception e) {
            return false;
      }
    }
   
    /**************************************************************
       getResultSet(String sqlQuery)
 
       This function gets information from the database
    **************************************************************/
   
     public ResultSet getResultSet(String sqlQuery){
       try {
        statement.execute(sqlQuery); // select the data from the table
        ResultSet rs = statement.getResultSet(); // get any ResultSet that came from our query
        return rs;
        }catch (Exception e) {
            return null;
        }
    }
   
    /**************************************************************
     updateDatabase(String sqlQuery)

       This functions updates an existing value in the database.
    **************************************************************/
   
    public boolean updateDatabase(String sqlQuery){
       try {
        statement.execute(sqlQuery); // select the data from the table
        return true;
        }catch (Exception e) {
            return false;
        }
    }
}








Now you need to be able to use this tool. Here is a sample function for getting the data from the database

/***************************************************************
     getStringInfo(String sqlStatement, String column)
     
     This function gets a single result of data type String from a
     column in the database and returns it to the user.
     ***************************************************************/
     
     public static String[] getStringInfo(String sqlStatement, String column)
     {    
          //aquires rights to the database
          Semaphore.aquire();
         int counter=0;
         String tempString[];
         
          try
          {
               //Creates a database connection
               Database database = new Database(true);
               
               //gets the results from the database
               ResultSet result = database.getResultSet(sqlStatement);
               
               //verfies results were obtained
               if(result!=null)
               {
                    //loops through the results
                    while(result.next())
                    {
                         //gets the requested result
                         tempString[counter] = result.getString(column);
                                         counter++;
                    }
               }
                   
               //closes the database connection
               boolean status = database.endConnection();
               
               //verifies the database closed properly
               if(status == false)
                    throw new Exception();
          }
          catch( Exception e ){}  
   
          //release the rights to the database
          Semaphore.release();  

          //returns the requested results
             return tempString;
     }

To call this function use a statement like this
     
     public String getDepartment()
     {          
          String sqlStatement = "SELECT Department FROM tableName;";
         
          return .getStringInfo(sqlStatement,"Department");    
     }


You can modify the getStringInfo function to get all the data you want

Hope this helps.

Cheers,
Ricky
0
 

Author Comment

by:T_Shah
ID: 18082040
thanks a lot guys!!!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
network + 7 73
countPairs challenge 7 58
nestparen challenge 4 56
What is JNDI datasource in spring 1 38
For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
For customizing the look of your lightweight component and making it look lucid like it was made of glass. Or: how to make your component more Apple-ish ;) This tip assumes your component to be of rectangular shape and completely opaque. (COD…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
The viewer will learn how to implement Singleton Design Pattern in Java.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now