0

We need a java code which can insert the row in a table, if table not exist, then it will create a new and then insert into that table.

Like we are tracking the each request to our server, and then save the same in a table. The table name should be like request_month-name_*year*, which is passed dynamically. The purpose is to keep the table of each month independent of others.

The code which we are using is 

try{
  dao.insertTracking(object, year, month);
}catch(BadSqlGrammarException e){
    dao.createTable(year, month);
    dao.insertracking(object, year, month);
}catch(Exception e){
    e.printStackTrace();
}

The issue is that the the BadSqlGrammarException can be thrown in many other cases as well.

So my question is to how to handle this type of situation efficiently without catching generalized exception and then processing again?

Also, We can first check the existence of table, but that would put heavy load on system, since large volume of request are handled each day.

Using jdbctemplates and/with mysql.

vinay
  • 1,366
  • 1
  • 13
  • 23

4 Answers4

2

You want to change the logic a little. Create the table if it doesn't exist, and then insert into it:

create table if not exists tablename . . .

insert into tablename(cols)
    select values;

If you do it this way, then you don't need any conditional logic. The same code works regardless of whether or not the table already exists.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

It is generally a bad idea to use Exception handling to perform expected business logic, and the issue mentioned in your question "The issue is that the the BadSqlGrammarException can be thrown in many other cases as well." gives the very reason.

In your create table method you could test for the table's existence first and if it exists do nothing.

Then you can call create table all the time and not just in the Exception case.

user3360944
  • 538
  • 4
  • 11
  • what would be the performance results while checking table existence each time? is this a good idea? – vinay Feb 28 '14 at 13:44
  • actually I read Gordon Linoff's answer and I like that idea better. create table if not exists is much cleaner then asking if it exists first. – user3360944 Feb 28 '14 at 13:54
0

Create a function that checks if the table exists first. This SO question shows how to use the INFORMATION_SCHEMA for that. If the table doesn't exist, call another function that will call your create table script.

if(!FunctionForTableExists()) {
    FunctionForCreateTable();
}

...code for insert...
Community
  • 1
  • 1
Jay McKinney
  • 109
  • 1
  • 4
0

finally did by using the quartz scheduler to run at the desired interval.

vinay
  • 1,366
  • 1
  • 13
  • 23