MySQL allowMultiQueries JSP Example

This example is given here for demonstrating you about how to write multiple queries in a single query in Java. This example explains you all the steps for allowing multiple queries using MySQL as a database, Eclipse as an IDE for compiling and the Tomcat 7 server for deploying the application.

MySQL allowMultiQueries JSP Example

MySQL allowMultiQueries JSP Example

In this section we will discuss about how to run multiple sql queries in Java.

This example is given here for demonstrating you about how to write multiple queries in a single query in Java. This example explains you all the steps for allowing multiple queries using MySQL as a database, Eclipse as an IDE for compiling and the Tomcat 7 server for deploying the application.

allowMultiQueries is a property of MySQL which default value is 'false'. Value of this property inform the server whether a single query will be executed or a set of query. To run a set of queries into a single query value of this property is required to set to 'true'.

Example

In this example at first we will create a database table and then we will insert some value into it. Then we will create a JSP page where we will write the code for connecting the database and a single sql query which will contain the two queries. In the url value we will add the properties allowMultiQueries="true" which will instruct the database server that there can be multiple queries to execute. To execute the sql query we will use the execute() method of PreparedStatement. And to get the resultset we will use the getResultSet() method of PreparedStatement. And to get the more result set we will use the getMoreResults()

Query for creating database table

CREATE TABLE `employee` ( 
`tag_id` char(3) NOT NULL, 
`serial_id` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT, 
`dd` int(2) unsigned zerofill DEFAULT NULL, 
`mm` int(2) unsigned zerofill DEFAULT NULL, 
PRIMARY KEY (`tag_id`,`serial_id`), 
UNIQUE KEY `serial_id` (`serial_id`) 
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

And inserted some value to the table

insert into `employee`(`tag_id`,`serial_id`,`dd`,`mm`) values ( 'SCS','0001 ','08','02')
insert into `employee`(`tag_id`,`serial_id`,`dd`,`mm`) values ( 'DCS','0002','09','02')

Then created a dynamic web project into the Eclipse and created a JSP page inside the WebContent folder

Directory Structure of project

Source Code

allowMultiQueries.jsp

<%@ page language="java" contentType="text/html;
charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 
Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ page import="java.sql.*" %>
<html>
<head>
<meta http-equiv="Content-Type" 
content="text/html; charset=ISO-8859-1">
<title>Get Employee Detail</title>
</head>
<body>
<%!String driver = "com.mysql.jdbc.Driver";%>
<%!String url = "jdbc:mysql://localhost/record?allowMultiQueries=true";%>
<%!String uid = "root";%>
<%!String psw = "root";%>
<%!String sql = "SELECT serial_id,tag_id FROM employee WHERE serial_id=0001;"+
"SELECT serial_id,tag_id FROM employee WHERE tag_id='DCS'";
%>
<%
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;

try
{
Class.forName(driver);
con = DriverManager.getConnection(url,uid,psw);
ps = con.prepareStatement(sql);
boolean bol = ps.execute();
//rs = ps.executeQuery();
%>
<table border="1">
<tr>
<th>serial_id</th>
<th>tag_id</th>
</tr>
<tr>
<% 
do {
rs = ps.getResultSet();
while (rs.next())
{

%>
<td> <%= rs.getString(1) %> </td>
<td> <%= rs.getString(2) %> </td> 
</tr> 
<%
}
bol = ps.getMoreResults();
} while (bol);
%>
</table>
<br>
<%

}
catch(SQLException sqle)
{
out.println(sqle);
sqle.printStackTrace();
}
finally{
try{
if(rs != null)
{
rs.close();
}
if(ps != null)
{
ps.close();
}
if(con != null)
{
con.close();
}
}
catch(Exception e)
{
out.println(e);
}
}
%>
</body>
</html>

Output :

When you will execute the above example you will get the output as follows :

You can download the WAR file of project from the link given below :

Download Source Code