I have an application that involves a spreadsheet or direct entry where I map the columns to textual tokens and use those tokens and what they represent to fill in rows in multiple SQL tables. Kind of like document templates.

To give a example to explain better what I mean, Lets say I have a csv file with two columns “First Name” and “Last Name” and will will map those to tokens “++fname++” and “++lname++” and the app will map token “++domain++” to “example.com”. Here would be the example csv –

"First Name","Last Name
John,Doe
Jane,Smith

So then I would use a HashMap or SQL database or something similar to store the values so I would end up with the first row looking like this in memory.

"++fname++" = "John"
"++fnamei++" = "J"
"++lname++" = "Smith"
"++lnamei++" = "S"
"++domain++" = "example.com"

The SQl table for example would have columns fname, lname, name, email. A preperared statement would be like –

String col[] = new String[4](); 
col[0] = "++fname++";
col[1] = "++lname++";
col[2] = "++fname++ ++lname++";
col[3] = "++fnamei++.++lname++@++domain++";
for (int i = 0; i < col.length; i++ ) {
    p.setString(i + 1,TokenEval(col[i]));
}

The TokenEval() would then insert the proper information so the table would have this inserted –

+++++++++++++++++++++++++++++++++++++++++++++
| John | Doe | John Doe | [email protected] |

The first thing to mention is that I could create classes for each table and use static variables, but there are 50 or so tables that I may have to update at times, and the input is also variable. There also may be many tokens, 20, 30, or even 50+, by the time I am done. The goal is to make the application flexible to changes in the input and the SQL tables where instead of having static classes, get the columns from the SQL server and then map them to token values that are mapped to data so if the table changes or the input changes I can map those in software rather than having to build new versions. Also, a lot of the columns in the database tables are either duplicate information, default values, or system-wide values I can define or possibly change in real-time.

My questions are: first, what would have better performance or be cleaner, using a HashMap to map the tokens to the values and using the .get() to get the mapped value or implementing something like an H2 memory SQL table?

Second, doing the string substitution. I know I can easily match and do the single tokens for fname and lname, but when there are multiple tokens in the string or other characters, like for the name and email, I am not sure what would be the best approach. I could do a char by char over the string and extract the tokens used, get them and rebuild the string, or maybe use the indexOf() to find the tokens and use .replace() once I know what they are. With so many tokens, doing a .replace() for every one for each field would take a long time. Or could I pass the string to the H2 SQL server, and it sends back matches, and I do the substitution from the result? Or is there a library already existing for this application for doing templates/token matching in Java?

Thanks!