store data by their ascending IDs using java

1001916

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

Tolls

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?

gimbal2

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.

1001916

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!

1001916

Of sourse i know it but in my program I would like to happened programmatically!

gimbal2

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?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

1001916

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.                                                                                                                                                                                                                                                                                                                                            

PhHein

998913 wrote:
Where is the

 tag? Sorry but i can't find it:S

https://forums.oracle.com/forums/help.jspa

gimbal2

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.

Tolls

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

1001916

The data are stored as there are in the excel file! It seems that the class ID.java didn't use it at all.