Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

formatting issue in Java UI

957995Jan 29 2019 — edited Feb 1 2019

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;

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

This post has been answered by [Deleted User] on Jan 29 2019
Jump to Answer

Comments

Processing

Post Details

Added on Jan 29 2019
2 comments
221 views