How To Update SQL Table from CSV Files Using Hibernate in Maven Java Project



In this post, I will show you how to update your SQL table from CSV files using Hibernate in Maven.
Before we move on, make sure you have followed below posts because its going to help us updating the SQL table with CSV data:

In short, let me tell you what I will be doing. I will first convert CSV data into an object of Product class and then I will use the data inside the object to move them into our SQL table.

In this project, we will be having three classes:
  • App.java: This class will have the main method
  • Product.java: This class will have all the variables and Getters and Setters and a parametrized constructors for storing data into these objects.
  • readCSV.java: This class will be used for getting data from CSV files and then storing these data into our MySQL table.

File: App.java (package: in.sumitkp.HibernateProject)

Important packages to import:
1. java.io.IOException
2. com.opencsv.exceptions.CsvValidationException
3. in.sumitkp.csv.*;

This class will be the entry point for your program so main method must reside in this. Make sure this main method throws CsvValidationException and IOException. Next, call the readCSV method inside readCSV.java. So, syntax for this would be: readCSV.readCSVData( );

File: Product.java (package: in.sumitkp.csv)

Important packages to import:
1. javax.persistence.Column
2. javax.persistence.Entity
3. javax.persistence.Id 
4. com.opencsv.bean.CsvBindByName

Above this line "public class Product {", type '@Entity', this will help Hibernate to understand that this class needs to be the entity. After the project run, 'product' will be the name of your table in the MySQL database.

Inside this class, if you want to make any variable act as primary key in the table, annotate it with '@Id'.

EDIT: If you are getting "No identifier specified for entity" error in console then try having at least one field set as primary key or '@Id'.

Annotations in Hibernate are case-sensitive so make sure to follow this post properly.

Example:
@Entity
public class Product{
@Id@CsvBindByName(column="ID")
private String id;

@Column(name="NAME")@CsvBindByName(column="NAME")
private String name;
.
.
.
//getters and setters here

.
}

File: readCSV.java (package: in.sumitkp.csv)

Important packages to import:
1. java.io.*;
2. java.util.*;
3. org.hibernate.Session;
4. org.hibernate.SessionFactory
5. org.hibernate.Transaction
6. org.hibernate.cfg.Configuration
7. com.opencsv.bean.CsvToBean
8. com.opencsv.bean.CsvToBeanBuilder
9. com.opencsv.exceptions.CsvValidationException

This class will have a method called readCSVData(). Make sure this method throws IOException and CsvValidationException. Let's write our code to configure Hibernate with objects to send our data across.


public static void readCSVdata() throws IOException, CsvValidationException {
Configuration cfg = new Configuration();
cfg.configure("hibernate.cfg.xml");
SessionFactory factory = cfg.buildSessionFactory();
Session session = factory.openSession();
Transaction tx = session.beginTransaction();

  File dir = new File("csvDataFiles"); //folder directory where all csv files are stored
  File[] files = dir.listFiles(); //returns array of files in the specified folder
  Reader reader = null; //initiating the value of reader with null

  /*
   * fetching all the files from the array of files
   */
  for (File file: files) {
    try {
      reader = new BufferedReader(new FileReader(file));
      CsvToBean <Product> csvReader = new CsvToBeanBuilder <Product> (reader).withType(Product.class).withSeparator('|').build();
      Iterator <Product> itr = csvReader.iterator();
      while (itr.hasNext()) {
        Product p1= itr.next();
        session.save(p1);
      } //end of while block
    } //end of try block
    catch (IOException e) {
      System.out.println(e);
    } //end of catch block
    finally {
      if (reader != null) {
        reader.close();
      } //end of if block
    } //end of finally block
  } //end of for block
tx.commit();
session.close();
} // end of readCSVdata() method

Elements of Hibernate Architecture:
1. SessionFactory: The SessionFactory is a factory of session and client of ConnectionProvider.
2. Session: The session object provides an interface between the application and data stored in the database.
3. Transaction: The transaction object specifies the atomic unit of work.
4. ConnectionProvider: It is a factory of JDBC connection. It abstracts the application from Driver Manager or DataSource.
5. TransactionFactory: It is a factory of Transaction.

Run your project and you will see that your SQL table is now populated with data from CSV files.

If you are still getting errors, edit your hibernate.cfg.xml file:
1. Change property inside connection.driver_class to com.mysql.cj.jdbc.Driver
2. Change property inside dialect to org.hibernate.dialect.MySQL5InnoDBDialect
3. Change property inside hbm2ddl.auto to update
4. Annotate your columns in Product.java with a custom name. If you already have @CsvBindByName(column= "NAME") then write the following before it: @Column(name="NAME")and import javax.persistence.Column



Comments

Post a Comment

Popular

How To Create MySQL Table with SequelizeJS

Image
Hello, in this post I will be showing how you can create an SQL table using JavaScript without the knowledge of MySQL. Sequelize is a promise-based  Node.js   ORM tool  for  Postgres ,  MySQL ,  MariaDB ,  SQLite ,  Microsoft SQL Server ,  Amazon Redshift  and  Snowflake’s Data Cloud . It features solid transaction support, relations, eager and lazy loading, read replication and more.
OPEN

How To Read CSV Files in Java as Maven Project

Image
Many of us have problems reading CSV files in Java but not anymore!. In this guide, I will share how to read CSV files as Maven Project. For creating a Maven project: In your Eclipse IDE, click on File > New > Project... > Maven >Maven Project > Next > Check "Create a simple project" checkbox. Under Group Id: The name could be anything. It is same as any package name. E.g. in.sumitkp Under Artifact Id: Same as above It is same as any project name. E.g. maven-demo1 Let the Version be the default. Under Name: Give the name for your project Click Finish. In your Project Explorer, all the necessary files will be automatically generated. Inside the root folder, there will be an XML file auto-generated called  pom.xml . About pom.xml: POM stands for Project Object Model It is an XML file that contains information about the project and configuration details used by Maven to build the project. When running a task, Maven looks for the pom.xml file in the directory. I...
OPEN

How To Create A Hibernate Project in Maven

Image
In this post, I will be showing you how to create a Hibernate project using Maven and how we can add MySQL dependencies to it. Here, is a whole process picture which you can click and follow the steps. The direction of the process has been indicated by arrow icons. Now, configuring the Hibernate XML file: Inside your project folder, right-click on 'src/main/java' and select New > Other... > Type filter text: xml > Under XML folder, click on XML File > Click Next > Under File Name, type: hibernate.cfg.xml > Finish. Inside this hibernate.cfg.xml file, we would now need a dtd line. dtd stands for 'document type definition'. It defines the tagging structure of an XML document. For this, search on Google or any search engine: hibernate dtd. Find for the result that says "Index of /dtd - Hibernate". Click the link or go to  https://hibernate.org/dtd/  . On the next page, click on either of these three links. A dtd file will be downloaded. Open the ...
OPEN