import csv in mysql | how to read data from csv file in java

import csv in mysql | how to read data from csv file in java

In mysql insert .csv file regarding same manner fields.

you have to modify for your requirement table fields.

Requrie library : mysqlConnector

Table Script

CREATE TABLE `client_master` (

  `client_master_id` INT NOT NULL AUTO_INCREMENT,

  `client_name` VARCHAR(55) NULL,

  `client_region` VARCHAR(55) NULL,

  `client_order_number` VARCHAR(55) NULL,

  `client_status` INT(1) NULL,

  PRIMARY KEY (`client_master_id`));

 require File save in .csv format

1,Vishal,India,Vishal_111089095,Active
2,Dhiren,India,Dhiren_222059526,InActive
3,Nikunj,UK,Nikunj_594215459,Active
4,Rahul,USA,Rahul_49851321852,InActive
5,Haresh,Germany,Harsh_94216456,Active
6,Mahesh,Canada,Mahesh_2549549874,Active
7,Jayesh,AUS,Jayesh_8798798,InActive
8,Bhavesh,AUS,Bhavesh_987872875,Active

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author vishal.khokhar
 */
public class ImportCSVDemo {

    public static void main(String[] args) {
        String databaseName = "client";
        String databaseConnectionString = "jdbc:mysql://localhost:3306/" + databaseName;

        //Set your Database Credential
        String databaseUser = "vishal";
        String databasePassword = "vishal";

        //Set your file path
        String filePath = "/home/vishal/Desktop/Demo.csv";

        try {
            ImportCSVDemo importCSVDemoObj = new ImportCSVDemo();
            Connection connection = importCSVDemoObj.connect(databaseConnectionString, databaseUser, databasePassword);

            connection.createStatement().executeUpdate("SET FOREIGN_KEY_CHECKS=0");

            importCSVDemoObj.importData(connection, filePath);
            System.out.println("Data imported into table Sucessfully");
        } catch (SQLException ex) {
            Logger.getLogger(ImportCSVDemo.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public Connection connect(String databaseConnectionString, String databaseUser, String databasePassword) {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(databaseConnectionString, databaseUser, databasePassword);
        } catch (Exception ex) {
            Logger.getLogger(ImportCSVDemo.class.getName()).log(Level.SEVERE, null, ex);
        }
        return connection;
    }

    public void importData(Connection connection, String file) {
        try {
            //Set Your Table Name
            String tableName = "client_master";
            Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            String insertQuery = "LOAD DATA LOCAL INFILE '" + file
                    + "' INTO TABLE " + tableName + " FIELDS TERMINATED BY ','"
                    + "OPTIONALLY ENCLOSED BY '\"'"
                    + " LINES TERMINATED BY '\n' "
                    + "(`client_master_id`,\n"
                    + "`client_name`,\n"
                    + "`client_region`,\n"
                    + "`client_order_number`,\n"
                    + "`client_status`)";
            statement.executeUpdate(insertQuery);
        } catch (SQLException se) {
            Logger.getLogger(ImportCSVDemo.class.getName()).log(Level.SEVERE, null, se);
        }
    }
}