Click here to Skip to main content
14,601,122 members
Rate this:
Please Sign up or sign in to vote.
I have an excel sheet with multiple columns.

i want this to be imported into sql via 12 tables.

but three of this table have foreign keys from other tables

how would i do this using java.

Please help.
Thanks


What I have tried:

package importDB;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.Iterator;

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.poi.poifs.filesystem.POIFSFileSystem;

import java.sql.Statement;

public class excelReading {

	
	@SuppressWarnings("rawtypes")
	public static void main(String[] args) {
		
		ArrayList<String> values = new ArrayList<String>();
		Connection connection;
		Statement statement;
		
		
		
		try {
			
			InputStream input = new FileInputStream("etudiants.xls");
			POIFSFileSystem fs = new POIFSFileSystem(input);
			HSSFWorkbook wb =new HSSFWorkbook(fs);
			HSSFSheet sheet = wb.getSheetAt(0);
			Iterator rows = sheet.rowIterator();
			
			while (rows.hasNext()) {
				
				values.clear();
				
				HSSFRow row = (HSSFRow) rows.next();
				
				Iterator cells =row.cellIterator();
				
				while (cells.hasNext()) {
					HSSFCell cell = (HSSFCell) cells.next();
					
					switch (cell.getCellType()) {
					case BOOLEAN:
						values.add(Boolean.toString((boolean)cell.getBooleanCellValue()));
					case NUMERIC:
						values.add(Integer.toString((int) cell.getNumericCellValue()));
					default:
						values.add(cell.getStringCellValue());
					}
				}
				
				
				try {
					Class.forName("com.mysql.jdbc.Driver").newInstance();
					
					connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1/mydb1", "root","");
					
					statement = connection.createStatement();
					
					String sql00 = String.format("INSERT INTO filiere (lib_Filiere) VALUES ('%s')", values.get(22));
					String sql01 = String.format("INSERT INTO specialite (lib_specialite) VALUES ('%s')", values.get(12));
					String sql02 = String.format("INSERT INTO ville (lib_ville) VALUES ('%s')", values.get(8));
					String sql03 = String.format("INSERT INTO diplome (lib_diplome) VALUES ('%s')", values.get(0));
					String sql04 = String.format("INSERT INTO etablissement (lib_etablissement) VALUES ('%s')", values.get(0));
					String sql05 = String.format("INSERT INTO mention (lib_mention) VALUES ('%s')", values.get(0));
					String sql06 = String.format("INSERT INTO semestre (lib_semestre, Date_debut, Date_fin) VALUES ('s1','NULL','NULL')");
					String sql07 = String.format("INSERT INTO etudiant (nom, prenom, Date_naissance, CIN, Telephone, Email) VALUES ('%s','%s','%s','%s','%s','%s')", 
							values.get(0), values.get(1), values.get(4), values.get(3),
							values.get(5), values.get(6));
					
		//here is where i'm blocked i don't know what to do
					String sql08 = String.format("INSERT INTO obtenir_diplome (id_Diplome, id_mention, id_Specialite, id_Ville, id_Etablissement, Id_etudiant, annee_obtention) "
							+ "VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s')", 
							select id_Diplome from diplome, values.get(0));
					String sql09 = String.format("INSERT INTO obtenir_semestre (id_semestre, Id_etudiant, note) VALUES (select id_semestre from)", 
							values.get(0));
					String sql10 = String.format("INSERT INTO choisir_filiere (Id_etudiant, id_Filiere, premiere_choix) VALUES ('%s', '%s', '%s')", 
							values.get(0));
					
					
					int count00 = statement.executeUpdate(sql00);
					if (count00 > 0) {
						System.out.println("Enregistrement effectué!");
					}
					
					int count01 = statement.executeUpdate(sql01);
					if (count01 > 0) {
						System.out.println("Enregistrement effectué!");
					}
					
					int count02 = statement.executeUpdate(sql02);
					if (count02 > 0) {
						System.out.println("Enregistrement effectué!");
					}
					
					int count03 = statement.executeUpdate(sql03);
					if (count03 > 0) {
						System.out.println("Enregistrement effectué!");
					}
					
					int count04 = statement.executeUpdate(sql04);
					if (count04 > 0) {
						System.out.println("Enregistrement effectué!");
					}
					
					int count05 = statement.executeUpdate(sql05);
					if (count05 > 0) {
						System.out.println("Enregistrement effectué!");
					}
					
					int count06 = statement.executeUpdate(sql06);
					if (count06 > 0) {
						System.out.println("Enregistrement effectué!");
					}
					
					int count07 = statement.executeUpdate(sql07);
					if (count07 > 0) {
						System.out.println("Enregistrement effectué!");
					}
					
					int count08 = statement.executeUpdate(sql08);
					if (count08 > 0) {
						System.out.println("Enregistrement effectué!");
					}
					
					int count09 = statement.executeUpdate(sql09);
					if (count09 > 0) {
						System.out.println("Enregistrement effectué!");
					}
					
					int count10 = statement.executeUpdate(sql10);
					if (count10 > 0) {
						System.out.println("Enregistrement effectué!");
					}
					
					
				}catch(Exception e) {
					e.printStackTrace();
				}
				
			}
		}catch (IOException e) {
			e.printStackTrace();
		}
		
	}

}
Posted
Updated 13-May-20 0:35am
Comments

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Instead of commenting that you "are blocked" state actually happens I.e. What is the error message?

Based on the comment in your code I think this is the solution to your problem..

In whatever language you use, you have to make sure that the values referred to in foreign keys already exist on the other tables

You haven't given us any detail about your tables or data so this simple example will have to do..

Say you have two tables - Car which has columns (id int, color int) where color is a foreign key to ..
the Color table which has columns (colorid int, description varchar(25)).

For this example let's assume that both tables are completely empty.

This code will produce an error
INSERT INTO Car (id, color) VALUES (1,1);
INSERT INTO Color (id, description) VALUES (1, 'Blue');
because the row for 'Blue' with an Id of 1 does not exist in the Color table at the point in time where you are trying to reference it.

This code will work
INSERT INTO Color (id, description) VALUES (1, 'Blue');
INSERT INTO Car (id, color) VALUES (1,1);

If you have more than one foreign key then all the values must already exist on their relevant table e.g.
INSERT INTO Color (id, description) VALUES (1, 'Blue');
INSERT INTO Make (id, maker) VALUES (1, 'Fiat');
INSERT INTO Car (id, color) VALUES (1,1);
not
INSERT INTO Car (id, color) VALUES (1,1);
INSERT INTO Color (id, description) VALUES (1, 'Blue');
INSERT INTO Make (id, maker) VALUES (1, 'Fiat');
You are also blindly inserting whatever is in the Excel worksheet into your database - what if two rows have ville = 'Honfleur'? If you have put a constraint on your table then this could be ther error you are getting.

You should be checking to see if the value already exists or use INSERT IGNORE or REPLACE or INSERT .. ON DUPLICATE KEY UPDATE(these are MySql specific, the concept is known as "UPSERT")
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100