Java experts ,
I need your help , suggestions and guidance in solving the below issue. I am designing a tool which will display explain plan information in Java UI. My tool displays the explain plan information in correct format in console but when it is displayed in JSP page the formatting is lost.
I have attached all the code . Could you please guide me how i can display the output in correct format in java UI. As you see , while printing in UI it is removing the space from id , operation , name and rows column etc.
UI input:
UI Output:
Console output:
---------------------------------------------------------------------------------------------------
Home.jsp:
<title>Insert title here</title>
</head>
<style>
.table_details {
border: 1px solid black;
}
</style>
<body onload="ui_function()">
<div style="text-align: center;">
<h1>Database tool</h1>
</div>
<div>
<p style="color: Red;">${message}</p>
</div>
<div>
<div>
<form action="querysubmit" method="POST">
<table>
<tr>
<td>Write the query</td>
<td><input type="text" name="query_field" value="${Query}"
style="height: 100px; width: 700px; wrap: hard" /></td>
<td><input type="submit" value="Execute" /></td>
</tr>
</table>
</form>
</div>
</div>
<div>
<table style="width: 700px">
<c:forEach items="${exp_plan}" var="exp_plan_value">
<tr style="width: 700px"><td >${exp_plan_value}</td></tr>
</c:forEach>
</table>
</div>
----------------------------------------------------------------------------------------------------
Databaseconnect.java
package com.ccc.dao;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import oracle.jdbc.OracleTypes;
/**
* @author xxxxx
*
*/
public class Databaseconnect {
private String message;
private Connection databaseConnect() throws ClassNotFoundException, SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@xxxxxx", "xxxxx","xxxxxx");
return con;
}
public List<String> createExplainedPlan(String query) throws ClassNotFoundException, SQLException {
Connection con = databaseConnect();
String rsetRow="";
int count_records = 0;
CallableStatement storedProc = con.prepareCall("BEGIN p_get_explan1(p_query=>?, country_list=>?); END;");
List<String> outputStrings= new ArrayList<String>();
try {
storedProc.setString(1, query);
storedProc.registerOutParameter(2, OracleTypes.CURSOR);
storedProc.execute();
ResultSet rset = (ResultSet)storedProc.getObject(2);
ResultSetMetaData rsetMeta = rset.getMetaData();
int count\_rows = rsetMeta.getColumnCount();
while (rset.next()) {
rsetRow = "";
for (int i=1; i\<=count\_rows; i++){
rsetRow = rsetRow + " " + rset.getString(i);
outputStrings.add(rsetRow);
}
System.out.println(rsetRow);
count\_records++;
}
message = "Explained plan created";
} catch (SQLException e) {
// TODO Auto-generated catch block
message = e.getMessage();
}
con.close();
return outputStrings;
}
-------------------------------------------------------------------------------------------------------------
Controller.java
package com.ccc.tool;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.cognizant.dao.Databaseconnect;
@WebServlet(value="/querysubmit")
public class Controller extends HttpServlet{
private static final long serialVersionUID = 1L;
@Override
public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException
{
}
@Override
public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException
{
String inputQuery= request.getParameter("query_field");
List<String> tablenameList= new ArrayList<String>();
List<String> explainedPlan=new ArrayList<String>();
try {
Databaseconnect dbconnect= new Databaseconnect();
explainedPlan=dbconnect.createExplainedPlan(inputQuery);
tablenameList= dbconnect.showTables();
int length= tablenameList.size();
request.setAttribute("Query",inputQuery );
request.setAttribute("exp_plan", explainedPlan);
request.setAttribute("size", length);
RequestDispatcher rd=request.getRequestDispatcher("Home.jsp");
rd.forward(request, response);
//response.sendRedirect("Home.jsp");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
------------
create or replace PROCEDURE p_get_explan1(p_query in varchar2,
country\_list out sys\_refcursor) is
st1 varchar2(20);
v_sql1 varchar2(4000);
begin
select 'v1'|| p\_seq1.nextval into st1 from dual;
v_sql1 := trim(UPPER(p_query));
EXECUTE IMMEDIATE 'EXPLAIN PLAN SET STATEMENT_ID=''' || st1 || ''' FOR '|| v_SQL1;
open country\_list for
select \* from table(dbms\_xplan.display(null,st1,'ALL'));
end;
-------------------------------