how to execute multiple sql commands on a database simultaneously...?

[Abhijit Kangale]

Hi all,
I want to execute multiple sql commands in a single program. But one command is a "select" and another is a "insert". Next thing is that both commands uses different tables of same database. How can I do this task.... I tried it using batch but is not working. Below is my trial code..

    
<%
	try
	{	
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","");
		Statement st=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		con.setAutoCommit(false);
		ResultSet rs=st.executeQuery("select IMMUNISATION_NAME from IMMUNISATION_REF_T");
		while(rs.next())
		{
				
			String str=rs.getString("IMMUNISATION_NAME");
%>         
<option value="<%=str%>", name="<%=str%>"> <%=str%> </option> 
<%		
		}
		String str_query="insert into IMMUNIZATION_SELECTED_REF values (str,'---','---','---','---')";
		st.addBatch(str_query);
		st.executeBatch();
		con.commit();
		con.close();
		
		} 
		catch(Exception e)
		{
			out.println("Some Error has occoured:"+e.getMessage());
			e.printStackTrace();
		}
%>

Can anyone please tell me where I m committing mistake....? Suggestions are welcome...
Regards,
Abhijit

[Sebastian Janisch]

You will have to use Threads if you want to process simultaneously.

JDBCSupport is a good start for your SQL interaction.

[Jan Cumps]

Hi Abhijit ,

Putting JDBC code in JSP pages is not reccommended. I would like to advise you not to do that.
Attempting to multi-thread JDBC inside a JSP page is even more daring.
Such complex logic does not belong in JSP.

Regards, Jan

[Sebastian Janisch]

Very true.

If you want to process things simultaneously, you will eventually have to go multi threaded, but not in a JSP.

That code belongs into a Servlet or other class outside the presentation layer.

[Abhijit Kangale]

Hiiii Jan ,

How can I achieve this task...? please suggest me something...?

Regards,
Abhijit

[Sebastian Janisch]

Remove all scriptlets out of your JSP, because they don't belong there.

If you decide to check out JDBCSupport import it as a library and in your servlet do the transactions.

This is the easiest way, usually you would modularize much more.

[Jan Cumps]

Abhijit Kangale wrote:Hiiii Jan ,

How can I achieve this task...? please suggest me something...?

Regards,
Abhijit

Abhijit,

you should only keep display (=presentation) logic in the jsp page. Anything else *belongs somewhere else* .
There is an article on the server side that is more then 10 years old, but it does explain the concept, and gives you something you can try yourself. Please also read our faq on this topic.


On running queries in parallel:
I would first check if I really need to do that. If you have performance issues, please find out what is causing them, before designing a fix.
Performance issues have the tendency not to be caused by what you think they are caused by (what a sentence).
Your sql seems to be simple. Are they giving you performance issues?

If you need to run the queries in parallel, you will have to learn about multi-threading in java, and about creating thread-safe JDBC code.

Regards, Jan