This discussion is archived
13 Replies Latest reply: May 10, 2013 9:32 AM by 939520 RSS

store data by their ascending IDs using java

1001916 Newbie
Currently Being Moderated
I am making a program to read data from excel files and store them in tables by their IDs. I have managed to read all the data from excel files as a string and store them in a table. But my project is to store them in table by ascending IDs. I have created the comparator in another class but when i call it in the main class nothing happened.Can someone help me why this is happened? The data that I have to store is like the below:

ID     Name     Salary     
50     christine     2349000     
43     paulina     1245874     
54     laura     4587894     
23     efi      3456457     
43     jim      4512878     

The codes are below:
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Iterator;
import java.util.List;
import java.util.Scanner;
import java.util.TreeMap;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;

import com.mysql.jdbc.DatabaseMetaData;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class suma {
     static LinkedHashMap<String, Integer> tFields = new LinkedHashMap();
     static LinkedHashMap[] tData;
     public static void main(String[] args) throws Exception {

          try {
               System.out.println("get the connection");
          }
          catch( Exception e )
           {
           System.out.println( "SQLException: " + e.getMessage() );
           }
          
          Class.forName("com.mysql.jdbc.Driver");
          Connection con = (Connection) DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/kainourgia", "root", "root");
          DatabaseMetaData meta = (DatabaseMetaData) con.getMetaData();
          ResultSet res = meta.getCatalogs();
          System.out.println("List of the databases: ");
          while (res.next()){
               System.out.println (" " +res.getString(1));
          }
          String strfullPath = "";
          Scanner scanner = new Scanner(System.in);
          System.out.println("Please enter the fullpath of the file");
          strfullPath = scanner.nextLine();
          String file = strfullPath.substring(strfullPath.lastIndexOf('/') + 1);
          System.out.println(file.substring(0, file.indexOf('.')));
          @SuppressWarnings("unused")
          String filename = strfullPath.substring(strfullPath.lastIndexOf('\\') +1);
          System.out.println(filename);
          String[] parts = filename.split("\\.");
          String tablename = parts[0];
          System.out.println(tablename);
              
          //Create an ArrayList to store the data read from excel sheet.
          List sheetData = new ArrayList();
          FileInputStream fis = null;
          try {
               //Create a FileInputStream that will be use to read the
               // excel file.
               fis = new FileInputStream(strfullPath);
               //Create an excel workbook from the file system
               HSSFWorkbook workbook = new HSSFWorkbook(fis);
               //Get the first sheet on the workbook.
               HSSFSheet sheet = workbook.getSheetAt(0);

               //store the data read on an ArrayList so that we can printed the
               // content of the excel to the console.
               Iterator rows = sheet.rowIterator();
               while (rows.hasNext()) {
                    HSSFRow row = (HSSFRow) rows.next();
                    Iterator cells = row.cellIterator();

                    List data = new ArrayList();
                    while (cells.hasNext()) {
                         HSSFCell cell = (HSSFCell) cells.next();
                         data.add(cell);
                    }
                    sheetData.add(data);
               }

          } catch (IOException e) {
               e.printStackTrace();
          } finally {
               if (fis != null) {
                    fis.close();   
               }
          }
     
     showExcelData(sheetData);     
     tFields = parseExcelColumnTitles(sheetData);
     String str = getCreateTable(con, tablename, tFields);
     tData = parseExcelColumnData(sheetData);
     ID ids = new ID();
     fillTable(con, tablename, tData);
     }
     
     
          private static void showExcelData(List sheetData) {
               // LinkedHashMap<String, String> tableFields = new LinkedHashMap();
               for (int i = 0; i < sheetData.size(); i++) {
                    List list = (List) sheetData.get(i);
                    for (int j = 0; j < list.size(); j++) {
                         Cell cell = (Cell) list.get(j);
                         if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                              System.out.print(cell.getNumericCellValue());
                         } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                              System.out.print(cell.getRichStringCellValue());
                         } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                              System.out.print(cell.getBooleanCellValue());
                         }
                         if (j < list.size() - 1) {
                              System.out.print(", ");
                         }
                    }
                    System.out.println("");
               }
          }
     
          private static LinkedHashMap parseExcelColumnTitles(List sheetData) {
               
               List list = (List) sheetData.get(0);
               LinkedHashMap<String, Integer> tableFields = new LinkedHashMap(list.size());
               for (int j = 0; j < list.size(); j++) {
                    Cell cell = (Cell) list.get(j);
                    tableFields.put(cell.getStringCellValue(), cell.getCellType());
               }

               return tableFields;

          }
          
          private static LinkedHashMap[] parseExcelColumnData(List sheetData) {
               
               LinkedHashMap[] tousRows = new LinkedHashMap[sheetData.size() - 1];
               for (int rowCounter = 1; rowCounter < sheetData.size(); rowCounter++) {

                    List list = (List) sheetData.get(rowCounter);

                    LinkedHashMap<String, Integer> tableFields = new LinkedHashMap(list.size());
                    String str;
                    String[] tousFields = new String[list.size()];
                    
                    int i = 0;

                    for (int j = 0; j < list.size(); j++) {
                         Cell cell = (Cell) list.get(j);
                         if (cell != null) {
                              if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                   tableFields.put(String.valueOf(cell
                                             .getNumericCellValue()), cell.getCellType());
                              } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                                   tableFields.put(cell.getStringCellValue(), cell
                                             .getCellType());
                              } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                                   tableFields.put(String.valueOf(cell
                                             .getBooleanCellValue()), cell.getCellType());
                              }
                         }

                    }
                    tousRows[rowCounter - 1] = tableFields;
               }

               return tousRows;

          }
          
          private static String getCreateTable(Connection con, String tablename,
                    LinkedHashMap<String, Integer> tableFields) {
               Iterator iter = tableFields.keySet().iterator();
               Iterator cells = tableFields.keySet().iterator();
               String str = "";
               String[] allFields = new String[tableFields.size()];
               int i = 0;
               while (iter.hasNext()) {
                    String fieldName = (String) iter.next();
                    Integer fieldType = (Integer) tableFields.get(fieldName);

                    switch (fieldType) {
                    case Cell.CELL_TYPE_NUMERIC:
                         str = fieldName + " INTEGER";
                         break;
                    case Cell.CELL_TYPE_STRING:
                         str = fieldName + " VARCHAR(255)";
                         break;
                    case Cell.CELL_TYPE_BOOLEAN:
                         str = fieldName + " INTEGER";
                         break;
                    default:
                         str = "";
                         break;
                    }
                    allFields[i++] = str;
               }
               try {
                    Statement stmt = con.createStatement();
                    
                    try {
                         String all = org.apache.commons.lang3.StringUtils.join(
                                   allFields, ",");
                         String createTableStr = "CREATE TABLE IF NOT EXISTS "
                                   + tablename + " ( " + all + ")";

                         System.out.println("Create a new table in the database");
                         stmt.executeUpdate(createTableStr);
                    } catch (SQLException e) {
                         System.out.println("SQLException: " + e.getMessage());
                         System.out.println("SQLState:     " + e.getSQLState());
                         System.out.println("VendorError:  " + e.getErrorCode());
                    }
               } catch (Exception e) 
               {
                    System.out.println( ((SQLException) e).getSQLState() );
                    System.out.println( e.getMessage() );
                    e.printStackTrace();
               }
               return str;
          }
     
          private static void fillTable(Connection con, String fieldname,
                    LinkedHashMap[] tableData) {
               for (int row = 0; row < tableData.length; row++) {
                    LinkedHashMap<String, Integer> rowData = tableData[row];
                    Iterator iter = rowData.entrySet().iterator();
                    String str;
                    String[] tousFields = new String[rowData.size()];
                    int i = 0;
                    while (iter.hasNext()) {
                         Map.Entry pairs = (Map.Entry) iter.next();
                         Integer fieldType = (Integer) pairs.getValue();
                         String fieldValue = (String) pairs.getKey();
                         switch (fieldType) {
                         case Cell.CELL_TYPE_NUMERIC:
                              str = fieldValue;
                              break;
                         case Cell.CELL_TYPE_STRING:
                              str = "\'" + fieldValue + "\'";
                              break;
                         case Cell.CELL_TYPE_BOOLEAN:
                              str = fieldValue;
                              break;
                         default:
                              str = "";
                              break;
                         }
                         tousFields[i++] = str;
                    }

                    try {
                         Statement stmt = con.createStatement();
                         String all = org.apache.commons.lang3.StringUtils.join(
                                   tousFields, ",");
                         String sql = "INSERT INTO " + fieldname + " VALUES (" + all
                                   + ")";
                         stmt.executeUpdate(sql);
                         System.out.println("Fill table...");
                    } catch (SQLException e) {
                         System.out.println("SQLException: " + e.getMessage());
                         System.out.println("SQLState: " + e.getSQLState());
                         System.out.println("VendorError: " + e.getErrorCode());
                    }

               }

               // return str;
          }

                    
}
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class ID implements Comparable<ID> {
     private Long ID;
     private String Name;
     private Double Salary;
     private static LinkedHashMap<String, Integer>myMap;
     
public Long getID(){
          return ID;
     }
    public void setID(Long ID){
          this.ID = ID;
     }
public String getName(){
     return Name;
}
    public void setName(String Name){
        this.Name = Name;
   }
public Double getSalary(){
     return Salary;
}
    public void setSalary(Double Salary){
         this.Salary = Salary;
    }
    @Override
    public int compareTo(ID o) {
         
        if (o.getID() < this.ID) {
            return -1;
        } else if (o.getID() > this.ID) {
            return 1;
        }
        return 0;
    }
    
    @SuppressWarnings("unused")
     private static void listbyID (){       
    
    List<Map.Entry<String, Integer>> entryList = new ArrayList<Map.Entry<String, Integer>>(myMap.entrySet());
    Collections.sort(entryList,
              new Comparator<Map.Entry<String, Integer>>()
              {
              public int compare(Map.Entry<String, Integer> o1, Map.Entry<String, Integer> o2) 
              
              {
                    return o1.getValue().compareTo(o2.getValue());
                }
    });
    Map<String,Integer> sortedMap = new LinkedHashMap< String, Integer>();
    for (Map.Entry<String, Integer>entry : entryList){
         sortedMap.put(entry.getKey(),entry.getValue());
}

}
}
Edited by: 998913 on May 8, 2013 5:59 AM
  • 1. Re: store data by their ascending IDs using java
    Tolls Journeyer
    Currently Being Moderated
    998913 wrote:
    But my project is to store them in table by ascending IDs.
    What a pointless requirement.
    Is the person writing the requirements completely unaware that SQL has an ORDER BY?
  • 2. Re: store data by their ascending IDs using java
    gimbal2 Guru
    Currently Being Moderated
    Tolls wrote:
    998913 wrote:
    But my project is to store them in table by ascending IDs.
    What a pointless requirement.
    Agreed, my spider sense is tingling; I detect requirements being completely misunderstood here.

    I mean what would be the point? Even when you manage to get the sort right (and I don't see anything in the sorting code that is really wrong other than it being overly complex), what would be the point? Selecting the records is not going to guarantee any order, you -must- add an order by clause to force it.
  • 3. Re: store data by their ascending IDs using java
    1001916 Newbie
    Currently Being Moderated
    Of courde i know that mysql has ORDER BY but in my program I would like to happened programmatically! That's why I am asking here!
  • 4. Re: store data by their ascending IDs using java
    1001916 Newbie
    Currently Being Moderated
    Of sourse i know it but in my program I would like to happened programmatically!
  • 5. Re: store data by their ascending IDs using java
    gimbal2 Guru
    Currently Being Moderated
    998913 wrote:
    Of sourse i know it but in my program I would like to happened programmatically!
    Fine, you will find out soon enough what we're talking about. Your code right now is very unreadable and so nobody will want to wade through it to perhaps spot a mistake, repost it between \
     tags.
    
    Also: how do you know that "nothing is happening" ? How did you test the result of the sort?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 6. Re: store data by their ascending IDs using java
    1001916 Newbie
    Currently Being Moderated
    Where is the
     tag? Sorry but i can't find it:S
    Furthermore, i am using mysql client to see if I have the result that I want. With the commands that are known.                                                                                                                                                                                                                                                                                                                                            
  • 7. Re: store data by their ascending IDs using java
    PhHein Guru Moderator
    Currently Being Moderated
    998913 wrote:
    Where is the
     tag? Sorry but i can't find it:S
    https://forums.oracle.com/forums/help.jspa
  • 8. Re: store data by their ascending IDs using java
    gimbal2 Guru
    Currently Being Moderated
    998913 wrote:
    Where is the
     tag? Sorry but i can't find it:S
    You managed to write it just fine.
    Furthermore, i am using mysql client to see if I have the result that I want.
    You can't check Java code from MySQL. Try looping through the sorted list and doing a simple System.out.println() to see in which order the entries are stored.
  • 9. Re: store data by their ascending IDs using java
    Tolls Journeyer
    Currently Being Moderated
    998913 wrote:
    Of sourse i know it but in my program I would like to happened programmatically!
    Maybe it'll be obvious once that code is legible...
  • 10. Re: store data by their ascending IDs using java
    1001916 Newbie
    Currently Being Moderated
    The data are stored as there are in the excel file! It seems that the class ID.java didn't use it at all.
  • 11. Re: store data by their ascending IDs using java
    1001916 Newbie
    Currently Being Moderated
    yes, you are totally right!
  • 12. Re: store data by their ascending IDs using java
    Tolls Journeyer
    Currently Being Moderated
    998913 wrote:
    yes, you are totally right!
    Well, not really.
    It still makes no sense.
    There is absolutely no guarantee that the order in which you insert into a database will bear any resemblance to the order in which rows are retrieved from the database. The database is completely within its rights to do whatever it likes with the rows so long as it actually stores them, and return them in whichever way it wants if an 'order by' is not provided.
  • 13. Re: store data by their ascending IDs using java
    939520 Explorer
    Currently Being Moderated
    In an unrelated observation, it appears you are creating new database tables to hold each document. I don't think this is a good idea. Your database tables should be created using the database's utility program and not programmatically. The database schema should hardly ever change once the project is complete and should not change due to the addition of data.

    As a design approach: One database table can hold your document names, versions, and date they were uploaded. Another table will hold the column names and data types. Another table can hold the data (type for all data = String). This way, you can join the three tables to retrieve a document (the tables should have primary and foreign keys). Your design will only consists of those three tables no matter how many unique documents you have. You probably should seek the advice of a DBA or experienced Java developer on exactly how structure those tables. My design is a rough layout.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points