Need to create a dynamic table with dynamic name in hibernate....

[john sal]

In my application we are using database table to store all the log. But now there is a change in requirement and instead of storing all the log related data in one table we have to create a table for that particular day and will have to store all the user logs int the table for that day....for Eg if today's date is 10 Aug then for storing a log a new database table will be created log_10_aug and all the data related to the current day will be stored in it... for next day again a new table will be created for storing the next day log activity..


We are using the Hibernate ORM in our application.....

Can someone let me know how to achieve the same using hibernate....how can we create the table dynamically with dynamic name....using hibernate...intent is that table strucuture will remain same however for each day the table name will be differenct (log_ preceeded by current date) ..

[Vijay Tidake]

Hi,

I'm following may be one way to create the table at run time using hibernate.

private void createTable(String tableName) {

		
		StringBuilder createTableQuery = new StringBuilder(
				"CREATE TABLE IF NOT EXISTS `" + tableName + "` (");

		
		//Sudo code
		for (<loop to get colunmnames from array>) {
			
			createTableQuery.append("`" + <columnname>
					+ "` ");
			createTableQuery.append(<datatype> + " ");

			createTableQuery
					.append(<constraint> + ", ");

		}
		//Sudo code ends

               //To replace last ',' character and place the bracket.
		createTableQuery.replace(createTableQuery.lastIndexOf(","),
				createTableQuery.length(), ")");

	
		Session session = null;
		Transaction transaction = null;
		try {

			session = HibernateUtil.getSessionFactory().getCurrentSession();
			transaction = session.beginTransaction();
			int count = session.createSQLQuery(createTableQuery.toString())
					.executeUpdate();	

			transaction.commit();
		} catch (Exception ex) {
			transaction.rollback();			
		}

	}

Hope this helps
Thanks

[Wouter Oet]

Why would you want separate tables? Why not just select by the date the log-entry was created? If it is for performance reasons (i.e. your table has millions and millions of rows (and even then ...)) why not move the rows to another table once every x days.

[Rebecca Green]

@Vijay Tidake, Your code shows how to create dynamic tables, but once these tables are created, how will i access them, I mean i need to create hbm files and also, i should include it in the hibernate.cfg file. How will I do that?

Vijay Tidake wrote:Hi,

I'm following may be one way to create the table at run time using hibernate.

private void createTable(String tableName) {

		
		StringBuilder createTableQuery = new StringBuilder(
				"CREATE TABLE IF NOT EXISTS `" + tableName + "` (");

		
		//Sudo code
		for (<loop to get colunmnames from array>) {
			
			createTableQuery.append("`" + <columnname>
					+ "` ");
			createTableQuery.append(<datatype> + " ");

			createTableQuery
					.append(<constraint> + ", ");

		}
		//Sudo code ends

               //To replace last ',' character and place the bracket.
		createTableQuery.replace(createTableQuery.lastIndexOf(","),
				createTableQuery.length(), ")");

	
		Session session = null;
		Transaction transaction = null;
		try {

			session = HibernateUtil.getSessionFactory().getCurrentSession();
			transaction = session.beginTransaction();
			int count = session.createSQLQuery(createTableQuery.toString())
					.executeUpdate();	

			transaction.commit();
		} catch (Exception ex) {
			transaction.rollback();			
		}

	}

Hope this helps
Thanks

[Vijitha Kumara]

Hi Rebecca Green,

Is your question is same as Vijay Tidake's? If not please create new thread for yours'.

[Rebecca Green]

It is very much relevant to Vijay Tidake's post. I came up with this doubt after reading Vijay's post.

Vijitha Kumara wrote:Hi Rebecca Green,

Is your question is same as Vijay Tidake's? If not please create new thread for yours'.

[Vijitha Kumara]

As there's not much details given by the OP and no other solutions are discussed you may need to clarify your situation properly. I suggest you start a new thread with more details in it.