matthew irving

How do i retrieve the value of the input and convert it to a variable?

It has to be a stored procedure.

Thats right i need to take both the key and the value from each pair and and put them into two columns. I'm having the most trouble with taking the json input and turning it into a variable i can work with.

I am trying to retrieve both parts of a JSON object through a stored procedure, for example from {bob:ben, 3:sue, James:cat} I want to get each part of the pair separately using sql and put them in a table. So it would be like this
col1 | col2 bob  | ben 3    | sue James| cat

How do i do this?

I almost have it but it won't execute the string.


<%@taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%> <%@ page language ="java" import="java.sql.*" %> <%@ page import ="javax.sql.*" %> <%@ page import="java.sql.*, javax.sql.*, java.io.*, javax.naming.*" %> <%@page contentType="text/html" pageEncoding="UTF-8"%> <% String casenumber = request.getParameter("casenumber"); String membernumber = request.getParameter("membernumber"); String membername = request.getParameter("membername"); String caseworker = request.getParameter("caseworker"); String descriptor = request.getParameter("descriptor"); String problem = request.getParameter("problem"); Connection connection = null; StringBuilder stringBuilder = new StringBuilder(); PreparedStatement st = null; %> <sql:setDataSource var="searchcase" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/UCU" user="root" password="password"/> <% try{ stringBuilder.append("SELECT * FROM cases where "); int fieldcount = 0; if (casenumber != null) { stringBuilder.append("CaseNumber=" + request.getParameter("casenumber")); } if (membernumber != null || membername != null || caseworker != null || descriptor != null || problem != null && casenumber != null) { stringBuilder.append(" AND "); } if (membernumber != null) { stringBuilder.append("MemberNumber=" + request.getParameter("membernumber")); } if (casenumber != null || membernumber != null && membername != null) { stringBuilder.append(" AND "); } if (membername != null) { stringBuilder.append("MemberName=" + request.getParameter("membername")); } if (casenumber != null || membernumber != null || membername != null && caseworker != null) { stringBuilder.append(" AND "); } if (caseworker != null) { stringBuilder.append("Caseworker=" + request.getParameter("caseworker")); } if (casenumber != null || membernumber != null || membername != null || caseworker != null && descriptor != null) { stringBuilder.append(" AND "); } if (descriptor != null) { stringBuilder.append("Descriptor=" + request.getParameter("descriptor")); } if (casenumber != null || membernumber != null || membername != null || caseworker != null || descriptor != null && problem != null) { stringBuilder.append(" AND "); } if (problem != null) { stringBuilder.append("Problem=" + request.getParameter("problem")); } String sql = stringBuilder.toString(); ResultSet rs = st.executeQuery(sql); while (rs.next()) { %> <h1>Search Results</h1> <table border="1"> <tr> <th>Case Number</th> <th>Member Number</th> </tr> <tr> <td><%=rs.getString(1)%></td> <td><%=rs.getString(2)%></td> <%} } catch (Exception e) { throw new RuntimeException("Cannot connect the database!", e); } %> </tr> </table>

you have to add some parenthsis, in the if condition that filters for the AND, its some conditions AND next parameter

I'm sorry I don't fully understand what you mean could you clarify please?

I think i have it.

int fieldcount = 0; if (casenumber != null) { stringBuilder.append("CaseNumber = ? "); fieldcount++; st.setString(fieldcount, casenumber); } if (casenumber != null && membernumber != null || membername != null || caseworker != null || descriptor != null || problem != null) { stringBuilder.append("AND"); } if (membernumber != null) { stringBuilder.append("MemberNumber =? "); fieldcount++; st.setString(fieldcount, membernumber); } if (casenumber != null || membernumber != null && membername != null) { stringBuilder.append("AND"); } if (membername != null) { stringBuilder.append("MemberName =? "); fieldcount++; st.setString(fieldcount, membername); } if (casenumber != null || membernumber != null || membername != null && caseworker != null) { stringBuilder.append("AND"); } if (caseworker != null) { stringBuilder.append("Caseworker =? "); fieldcount++; st.setString(fieldcount, caseworker); } if (casenumber != null || membernumber != null || membername != null || caseworker != null && descriptor != null) { stringBuilder.append("AND"); } if (descriptor != null) { stringBuilder.append("Descriptor =? "); fieldcount++; st.setString(fieldcount, descriptor); } if (casenumber != null || membernumber != null || membername != null || caseworker != null || descriptor != null && problem != null) { stringBuilder.append("AND"); } if (problem != null) { stringBuilder.append("Problem =?"); fieldcount++; st.setString(fieldcount, problem); }

Can you please provide an example?

Would the statement eliminate the need for a large number of if statements?

I want any that HAVE matches to match and any null to not be searched against.

Would this sql work?

SELECT * FROM cases where CaseNumber=? OR MemberNumber=? OR MemberName=? OR Caseworker=? OR Descriptor=? OR Problem=?

I have six fields. Any of the fields can have a search term in them. I want the code to be able to understand how to handle a query where some of the search terms can be null.

I think the code below would work but i can't figure out how to get the st.executeQuery() to recognise which case to display.

<%@taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%> <%@ page language ="java" import="java.sql.*" %> <%@ page import ="javax.sql.*" %> <%@ page import="java.sql.*, javax.sql.*, java.io.*, javax.naming.*" %> <%@page contentType="text/html" pageEncoding="UTF-8"%> <% String casenumber = request.getParameter("casenumber"); String membernumber = request.getParameter("membernumber"); String membername = request.getParameter("membername"); String caseworker = request.getParameter("caseworker"); String descriptor = request.getParameter("descriptor"); String problem = request.getParameter("problem"); Connection connection = null; PreparedStatement st; %> <sql:setDataSource var="searchcase" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/UCU" user="root" password="password"/> <% if(casenumber!=null){ st = connection.prepareStatement( "SELECT * " + "FROM cases " + "where CaseNumber=?"); st.setString(1,casenumber); } if(membernumber!=null){ st = connection.prepareStatement( "SELECT * " + "FROM cases " + "where MemberNumber=?"); st.setString(1,membernumber); } // other if statements ResultSet rs = st.executeQuery(); while (rs.next()) { %> <table border="1"> <tr> <th>col1</th> <th>col2</th> </tr> <!-- column data --> <tr> <td><%=rs.getString(1)%></td> <td><%=rs.getString(2)%></td> <%}%> </tr> </table>

Is there anyway to shorten the number of If statements?

So do i use a new prepared statement for each if statement?