how to batch insert
This is rather basic Java and can be found in a very simple JAVA search, not really related to Bonita...
lets start with
sb.append("a,b,c,d,e"); //create temp string
List<String> l = sb.toString().split(","); //create the list
for(String li : l){ //for each list item
String insertStr = String.format("Insert into db col values('%s')", li);
}
regards Seán
can you please ellobarate.Thanks
Comments
Use "Add new comment" rather than "Answer" please. It is easier to follow the discussion.
First, create your ArrayList of thing to insert. Create a script task where you add a database connector (MySQL for example). In the data tab of your task, add a variable "thingToInsert". In the Iteration tab of your task, check "is Multi instantiated" and choose your list as the collection to iterate on. In the "Input data" chose your variable "thingToInsert". To finish, in the connector your can put the query "INSERT INTO table VALUES (${thingToInsert})
I cannot determine the issue without more informations.
What error do you have in the logs when the insert fails ?
Can you post the code you use to create and fill your ArrayList ?
Here is how I do it : 1) Create a list in the "data tab" of the pool : (in this example I have a list of job) Creation of the list
2) Fill the list : (I fill my list with data from a form but you can do it as you wish)
for (ArrayList<String> j : field_Table1) {
jobs.add(j.get(0));
}
return jobs;
4) Add the iteration : Iteration
5) Add the connector : Connector
In your query you need to have something like "INSERT INTO jobTable(jobName) VALUES (${job})".
What kind of output did you have ? And what say your error log ?
If you want to use the output of the Iteration tab, you need : - create a variable of type List in your Process - create a varable of type String (or other, it depends of your output) in the task - set the "output data" with the String variable - set the "List of output results" with the List variable
After that, you can use the list in the next Task.
Did you set your output in connector ? And above all, did you have an output for your connector ? Insert queries don't have any output by default.
What is the thing you call "output" ? Can you explain what you try to do, how you do it, what is the error ? Maybe you can add a picture to explain the problem.
If you just want to " Iterating the list and storing it to a string variable and the insert that string variable using inser statement", the solution is here : http://community.bonitasoft.com/answers/how-batch-insert#comment-38421
You don't need the "Output Data" field to do that.
It's the "Input Data" that you need to use in your SQL query to insert it.
In my example, the "Input Data" is "job". So, in my query I have "Insert into mytable(jobName) values(${job})". This is "job" that is inserted in the column "jobName" of my table.
Apologies I had a bad day, but to fix your problem you have to be clear on what you want, so far we have given you everything but we still cannot seem to make it work. Yannick has done a wonderful job and he cannot see what is happening for your question to fail.
You do not even say what version on Bonita you are using, which would help, especially if you do not see the same things as Yannick has shown in pictures.
So far you have said.
I have a list and I want to insert the data in the list into a database...there are two ways to do this.
Via Connector which Yannick has shown you, or in code similar to (note you will have to change the code to suit your environment, this code is based on postgres, and is not tested...):
sb.append("a,b,c,d,e"); //create temp string
List<String> jobs = sb.toString().split(",");
//set basics for db
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
String sql2 = null;
String dbTable = null;
String user = null;
String password = null;
String url = "jdbc:postgresql://"+dbHost+":"+dbPort+"/"+dbDatabase);
try {
Class.forName("org.postgresql.Driver");
try {
con = DriverManager.getConnection(url, dbUser, dbPassword);
for (String job : jobs){
String templateSQL = "INSERT into dbTable (colName) values('"+job+"')";
pst = con.prepareStatement(templateSQL);
rs = pst.executeQuery();
}
} catch (SQLException ex1) {
logger.severe(module+": Error (ex1): "+ex1.getMessage());
} finally {
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (ClassNotFoundException e) {
if(debug){logger.info("After Load Class: ClassNotFoundException");}
}
Comments
Don't understand, need more info re the process...