• CSV file exception error in SQL/JAVA

    ShikhaTan Member

    Hello, I was wondering if anyone can help with this issue?

    I am trying to load a csv file, but the program keeps catching the exception error. It’s able to create the table, but I can’t load the csv file into it. Any suggestions? I believe the error is in:

     String query =  "LOAD DATA INFILE ‘Employees.csv’ INTO TABLE Employee FIELDS (id,lastname,HireDate,Birthday,Sex,Jobstatus,Paytype,annualsalary,YearsofService) TERMINATED BY ‘,’ "; 
    import java.sql.*;
    
    public class jdbcdemo{
    
    public static void main(String args[]){
    
    try {
    
       String url = "jdbc:mysql://localhost:3306/finalproject";
       String username = "root";
       String password = "";
       Connection connection = null;
    
           System.out.println("Connecting database...");
           connection = DriverManager.getConnection(url, username, password);
           System.out.println("Database connected!");
    
    Statement stmt = connection.createStatement();
    
    try{
        String table ="CREATE TABLE Employee( id integer(30), lastname varchar(50),HireDate date,Birthday date, Sex     varchar(10),Jobstatus varchar(50),Paytype varchar(50),annualsalary float(30),YearsofService float(50))";
          stmt.executeUpdate(table);
         System.out.println("Table creation process successfully!");
         // LINES TERMINATED BY ‘\n’
        String query =  "LOAD DATA INFILE ‘Employees.csv’ INTO TABLE Employee FIELDS (id,lastname,HireDate,Birthday,Sex,Jobstatus,Paytype,annualsalary,YearsofService) TERMINATED BY ‘,’ ";
       
        System.out.println("Hello , is this working?");
         }
         catch(SQLException s){
         System.out.println("Table already exists!");
         }
         connection.close();
         }
         catch (Exception e){
         e.printStackTrace();
         }
         }
       } 
  • ShikhaTan Member

    Execute the query and iterate over the ResultSet to view your data.

    http://www.fluffycat.com/Java/ResultSet/

  • Amit Member

    Ill keep posting on this because I am still working on this project

    I know it’s possible to have an IF ELSE statement in MySQL, but is there a way, I could display the message on the command line or eclipse
    This is my query, but If the user doesn’t match an ID, then I would like to display an error on the console

    String query = "SELECT * FROM Employee WHERE id="+viewEmployeeID+";";
    statement.executeQuery(query);
  • Adelaid Member

    If the user doesn’t match your SQL statement, you won’t have any results in your ResultSet. In other words, you’ll need to do an if statement on the ResultSet object and if it is empty or null, show your error message.

    Your query might be vulnerable to SQL injection (is viewEmployeeID a String or int?). It is recommended to use PreparedStatements to get around this, but also do validation on your input before sticking it into the query.

  • Amit Member

    viewEmployeeID is an int
    Yeah my professor suggested I use PreparedStatement, in which I did for some cases.

    This is actually my final project for one of my courses, and we are running on our Local Host

    I actually tried

    if(statement.executeQuery(query)){
     System.out.println("Employee found");
    }
    else{
    System.out.println("Employee not found");
    }
    
Viewing 4 reply threads
  • You must be logged in to reply to this topic.
en_USEnglish