asked on

JSP Group Results from database

Hi,

I have a JSP file written to retrieve the records from H2 database. Below is the code. I want the records to be grouped based on each category. How can that be achieved?
Attached is the current result screenshot and the expected result.

<%
try{ 
// STEP 2: Open a connection
System.out.println("Connecting to database..."); 
connection = DriverManager.getConnection(connectionUrl, userId, password);
// STEP 3: Execute a query 
System.out.println("Connected database successfully..."); 
statement=connection.createStatement();
String sql ="SELECT * FROM tb_faq";

resultSet = statement.executeQuery(sql);
while(resultSet.next()){
%>
 <div class="container">
     <div class="row-fluid">        
    <div class="col-auto">
         <div class="panel-group" id="faqAccordion">                    
             <h4 style="color:#B98D2D;"> <%=resultSet.getString("category") %> </h4>
                <div class="panel panel-default ">
                    <div class="panel-heading accordion-toggle question-toggle collapsed" data-toggle="collapse" data-parent="#faqAccordion" data-target="#<%=resultSet.getString("ID") %>">
                        <h6 class="panel-title">
                            <a class="ing"> <%=resultSet.getString("question") %> </a>
                        </h6>
                    </div>
                <div id="<%=resultSet.getString("ID") %>" class="panel-collapse collapse" style="height: 0px;">
             <div class="panel-body">
                <h7><span class="label label-primary">Answer</span></h7>
                    <p>
                        <%=resultSet.getString("answer") %>
                    </p>
             </div>
        </div>
    </div>
    </div>
 </div>
</div>
</div>

Open in new window

Current-Result.JPG
Expected-Result.JPG

Norie

Have you looked at using LISTAGG?

SELECT category, LISTAGG(question, ',') questions, LISTAGG(answer,',') answers
FROM tb_faq
GROUP BY category;

That would give you 2 comma delimited lists, one for answers and one for questions.

You would then need to process those lists further to get what you want.

angel7170

ASKER

Thank you, how do I process the comma delimited list into separate records in the panel-title and panel-body?

Norie

You'll need another loop that splits the questions and answers out to arrays.

Not sure exactly how you would incorporate that into your code but here's some simple code that loops through the arrays and outputs their values.

Perhaps something like this.

<h4 style="color:#B98D2D;"> <%=resultSet.getString("category") %> </h4>
<div class="panel panel-default ">

<%
 // get questions and answers
String[] arrQuestions= resultSet.getString("questions").split(",");
String[] arrAnswers= resultSet.getString("answers").split(",");
%>


<% for( int idx=0 ; idx<arrQuestions.length ; idx++){ %>

	<div class="panel-heading accordion-toggle question-toggle collapsed" data-toggle="collapse" data-parent="#faqAccordion" data-target="#<%=resultSet.getString("ID") %>">
		<h6 class="panel-title">
			<a class="ing"> <%=arrQuestions[idx]") %> </a>
		</h6>
	</div>
	<div class="panel-collapse collapse" style="height: 0px;">
		<div class="panel-body">
			<h7><span class="label label-primary">Answer</span></h7>
			<p><%=arrAnswers[idx]) %></p>
	</div>

<% } %>
</div>

Open in new window

Norie

I've had a look over the code I posted and there are a couple of typos and other problems with it so it probably won't work as is.

I've fixed the typos, I think, but you'll need to remove any reference to the ID field as the query I posted won't return that.

Anyway, hopefully it'll give some idea how to proceed.

angel7170

ASKER

Thank you, that seems to work except the data-target is not working. Please see attached is what I am getting now.
Capture.JPG

Norie

I think that'll be a problem with the query not returning the ID and therefore it's not possible to use that for the ID of the question/answer.

What is the 'data-target' attribute meant to do?

Should it collapse the question or the category?

angel7170

ASKER

Data-target is meant for the answer to collapse/expand based on the question

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial