change-table-name-runtime

This is a demonstration of how to change entity names used for mapping objects to tables at runtime. This is achieved by use of Hibernate's interceptor to intercept the SQL query before it's executed by Hibernate.

How it works

Sometimes, There are the tables that are being generated on a monthly basis but the table structure of all monthly tables are same.

Let's say we have a biometric fingerprint attendance machine for our employees and this machine is automatically storing attendance details in the database on monthly basis. Hence the table name changes every month but its column/structure remains the same.

Example:- Suppose today is 13-01-2019. Now for today's data will be stored into table ATTENDANCE_1_2019. Similarly, our tables name will be ATTENDANCE_2_2019, ATTENDANCE_3_2019 ...... ATTENDANCE_12_2019.

But the columns of all the tables remains the same. deviceLogId, empId, checkin checkout

Now, at end of the month, we need to calculate the salary for every employee. For this, we need to read the data from these tables. For this, we are using Hibernate. As we know Hibernate is an ORM Framework. Means for a per table we need to create Entity class so that Hibernate can map the table into the entity class and vice-versa.

So, let's create our entity class.

@Entity
@Table(name = "ATTENDANCE_1_2019")
public class Attendance {
 @Id
 private int sno;
 private int deviceLogId;
 private int employeeId;
 private Date checkin;
 private Date checkout;

//setters & getters
}

Now we will write our DAO class to fetch to fetch the data from table ATTENDANCE_1_2019. It will work fine but what happens if month comes to February?

In February month our Biometric fingerprint attendance machine stores attendance details in ATTENDANCE_2_2019 table. Then how can we fetch the data from ATTENDANCE_2_2019(February) table because our Attendance entity class is mapped with ATTENDANCE_1_2019 table?

Hence again we need to write entity class for February(ATTENDANCE_2_2019), March(ATTENDANCE_3_2019), April(ATTENDANCE_4_2019) and so on. Now the question is how many entity class you will write? In a year you need to write 12 entity class. So is it a good approach to write too many entity class even all the entity class has the same structure/attributes just because of table name?

Hence we can solve this problem by changing the Entity class name at runtime by using Hibernate Interceptor.

The Hibernate Interceptor is an interface that allows us to react to certain events within Hibernate. Interceptor Interface provides methods, which can be called at different stages to perform some required tasks like onSave(), onDelete(), onPrepareStatement() etc. You can read more in Hibernate Documentation.

Hence we will take advantage of onPrepareStatement(String sql) method. As we Hibernate uses HQL(Hibernate Query Language) but internally these query is converted into SQL. Means at the time of generating SQL if there is any callback method where we can get the SQL and can change the table name then we can solve this problem. Hence Hibernate has provided one method called onPrepareStatement(). Before sending the query to the Database first it will call onPrepareStatement(String sql). Hence here we can change the table name by replacing the older name with the new table name.

Example:- So currently, we have the Attendance list of January & February month. We will fetch this data using Hibernate.

[Table structure shown here]https://1.bp.blogspot.com/-3c-XuaKiRQc/XD17xCYdjtI/AAAAAAAAADw/o8nyTI5V-QMPglTMpFuC0RV1addAZQz_gCLcBGAs/s1600/Attendance_1_2019.png

For this, we need to create one DAO like this:

@Repository
public class AttendanceDAO {
 @Autowired
 private SessionFactory sessionFactory;

 public List getAllAttendance() {
  Session session = sessionFactory.openSession();
  Query query = session.createQuery("from Attendance");
  List attendanceList = query.list();
  return attendanceList;
 }
}

As we are using show_sql=true, we can see the query generated by the Hibernate.

Hibernate:
    select
        attendance0_.sno as sno1_0_,
        attendance0_.checkin as checkin2_0_,
        attendance0_.checkout as checkout3_0_,
        attendance0_.deviceLogId as deviceLo4_0_,
        attendance0_.empId as empId5_0_
    from
        ATTENDANCE_1_2019 attendance0_

But here the problem is, This session will always go to ATTENDANCE_1_2019 table because in entity class it is mapped to this table. Hence we need to write our custom Interceptor class like this:

public class CustomInterceptor extends EmptyInterceptor {
 @Override
 public String onPrepareStatement(String sql) {
  System.err.println("Before Modifying SQL =" + sql);
  sql = sql.replace("ATTENDANCE_1_2019 ", "ATTENDANCE_2_2019 ");
  System.err.println("After Modifying SQL =" + sql);
  return sql;
 }
}

And in this class, we are modifying the existing query by replacing the table name and again we are sending this query to the HIbernate. Now Hibernate will fetch the data from ATTENDANCE_2_2019 table.

Here I have hardcoded the table name which we should not. Hence we can use the Calendar class of Java to get the current month and year. Hence we can change your CustomInterceptor like this:

 public class CustomInterceptor extends EmptyInterceptor {
 @Override
 public String onPrepareStatement(String sql) {
  System.err.println("Before Modifying SQL =" + sql);
  Calendar calendar = Calendar.getInstance();
  int month = calendar.get(Calendar.MONTH) + 1;
  int year = calendar.get(Calendar.YEAR);
  String tableName = "ATTENDANCE_" + month + "_" + year;
  sql = sql.replace("ATTENDANCE_1_2019 ", tableName);
  System.err.println("After Modifying SQL =" + sql);
  return sql;
 }
}

In this way, we can change the entity class table at runtime. No need to write multiple entity class. One entity class is enough.

Credits

This code was forked from https://github.com/altafjava/change-table-name-runtime.git

The explanation was extracted from: https://javaaltaf.blogspot.com/2019/01/change-table-name-of-entity-at-runtime.html