Forum Stats

  • 3,852,417 Users
  • 2,264,102 Discussions
  • 7,905,063 Comments

Discussions

formatting issue in Java UI

957995
957995 Member Posts: 25
edited Feb 1, 2019 9:58AM in General Database Discussions

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:

pastedImage_0.png

UI Output:

pastedImage_3.png

Console output:

pastedImage_11.png

---------------------------------------------------------------------------------------------------

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;

-------------------------------

957995

Best Answer

Answers