Results 1 to 2 of 2
  1. #1
    efoikonom is offline Member
    Join Date
    Apr 2013
    Posts
    10
    Rep Power
    0

    Default having error when inserting multiple value into mysql db table using java

    Here is my problem. I have to make a program where I will read data from an excel file and load this data into a table in a database. The table would have the name of the excel file and the fields of the table would be the data that is in the first row of the excel file. I have made the code to read the excel file and create the table with the name that I want. Now I have to insert the values in the table but here I have the error.

    The code below is the whole program that I have made.

    Java Code:
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.List;
    
    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 java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class readexcel {
    
    	
    	public static void main (String[] args) throws Exception {
    		
    		Class.forName("com.mysql.jdbc.Driver");
    		Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/kainourgia","root", "root");
    		Statement  stmt = con.createStatement();
    		//String filename = "C:\\Users\\Efi\\Documents\\test6.xls";
    		String fullPath = "C:\\Users\\Efi\\Documents\\test8.xls";
    		String Path = "C:\\Users\\Efi\\Documents\\";
    		String filename	= "test8.xml";
    		String[] parts = filename.split("\\.");
    		String tablename = parts[0];
    		
    		
    		List sheetData = new ArrayList();
    		FileInputStream fis = null;
    		try {
    			fis = new FileInputStream(fullPath);
    			HSSFWorkbook workbook = new HSSFWorkbook(fis);
    			HSSFSheet sheet = workbook.getSheetAt(0);
    			
    			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);
        HashMap<String,Integer> tFields = new HashMap();
        tFields = parseExcelData(sheetData);
        String str = getCreateTable(con, tablename, tFields);
    	str = fillTable ( con, tablename, tFields);
    	}
    	
    	private static void showExcelData(List sheetData) {
    //		HashMap<String, String> tableFields = new HashMap();
            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("");
    	    }
       	}
    
    
    	@SuppressWarnings({ "unchecked", "unused" })
    	private static HashMap parseExcelData (List sheetData){
    		
    			HashMap<String,Integer> tableFields = new HashMap();
    			List list = (List) sheetData.get(0);
    			for (int j=0; j<list.size(); j++){
    				Cell cell=(Cell) list.get(j);
    				tableFields.put(cell.getStringCellValue(),cell.getCellType());
    	    }
    	    
    			return tableFields;
    	     	
    		}
    	
    	private static String getCreateTable(Connection con, String tablename, HashMap<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;
    			}
    			allFields[i++]= str;
    		}
    		try 
    		{
    			Statement  stmt = con.createStatement();
    //			try
    //			{
    //				System.out.println( "Use the database..." );
    //				stmt.executeUpdate( "USE kainourgia;" );
    //			}
    //			catch( SQLException e )
    //			{
    //				System.out.println( "SQLException: " + e.getMessage() );
    //				System.out.println( "SQLState:     " + e.getSQLState() );
    //				System.out.println( "VendorError:  " + e.getErrorCode() );
    //			}
    			try
    			{
    				String all = org.apache.commons.lang3.StringUtils.join(allFields, ",");
    				String createTableStr = "CREATE TABLE " + 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)
    		{
    		}
    		return str;
    	}
    
    	private static String fillTable(Connection con, String fieldname, HashMap<String, Integer> tableFields){
    		Iterator iter = tableFields.keySet().iterator();
    		Iterator cells = tableFields.keySet().iterator(); 
    		String str="";
    		String[] tousFields = new String[tableFields.size() + 1];
    		int i = 1; 
    		while (iter.hasNext()){
    			String fieldName = (String) iter.next();
    			Integer fieldType=(Integer)tableFields.get(fieldname);
    			while (cells.hasNext()){
    				String fieldName1 = (String) cells.next();
    				Integer fieldType1=(Integer)tableFields.get(fieldName1);
    				}
    				tousFields[i++]= str;	
    		}
    		try
    		{
    			Statement  stmt = con.createStatement();
    			System.out.println ( "Use the table");
    			String all = org.apache.commons.lang3.StringUtils.join(tousFields, ",");
    			String sql= "INSERT INTO" + fieldname + "VALUES (" +all +")";
    			stmt.executeUpdate (sql);
    		}
    		catch( SQLException e )
    		{
    			System.out.println( "SQLException: " + e.getMessage());
    			System.out.println( "SQLState: " + e.getSQLState() );
    			System.out.println( "VendorError: " + e.getErrorCode());
    		}
    					
    		return str;
    	}
    	private Statement createStatement() {
    		return null;
    	}			
    	
    	
    }

    The exception that i get is that:
    Java Code:
    SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',,)' at line 1
    SQLState: 42000
    VendorError: 1064
    Could anyone help me fix it? What do I have to do to insert the values in each cell? What's the wrong in the command where i am trying to insert the values?

    Thank you in advance!

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,182
    Rep Power
    20

    Default Re: having error when inserting multiple value into mysql db table using java

    Print out yoru SQL before you execute it.
    Since the fields are always the same then I would suggest creating the SQL text first, with '?', and use a PreparedStatement.
    I expect it's going to be something to do with quote marks...that's the most common thing when you try and create statement in this way.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

Similar Threads

  1. Replies: 3
    Last Post: 10-18-2012, 04:00 PM
  2. Replies: 3
    Last Post: 12-02-2010, 03:14 PM
  3. Help on inserting into table
    By ShinTec in forum JDBC
    Replies: 4
    Last Post: 06-08-2010, 10:26 AM
  4. Replies: 1
    Last Post: 04-23-2009, 08:55 PM
  5. Inserting into a table (Example)
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-06-2008, 10:25 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •