Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Why is the number of records returned by my search incorrect?

user-d019hMay 22 2023

Hello,

I have encountered an issue with my code. When I remove the angle condition from the query, I get the correct data. However, when I include the angle condition in the query, the data returned is incorrect.

Could someone please help me identify where the error might be?

Thank you.

<%@ page import="java.sql.*" %>

<%@ page import="java.util.ArrayList" %>

<%@ page import="java.util.List" %>

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>

<%

// 连接数据库

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/product";

String username = "root";

String password = "123456";

Connection conn = null;

PreparedStatement preparedStatement = null;

ResultSet resultSet = null;

conn = DriverManager.getConnection(url, username, password);

request.setCharacterEncoding("UTF-8"); // 设置字符编码,避免出现乱码

String\[\] sizeOptions = request.getParameterValues("sizeOption");

String\[\] CurOptions = request.getParameterValues("curOption");

String d\_minParam = request.getParameter("d\_min");

String d\_maxParam = request.getParameter("d\_max");

String angle\_minParam = request.getParameter("angle\_min");

String angle\_maxParam = request.getParameter("angle\_max");

String i\_minParam = request.getParameter("i\_min");

String i\_maxParam = request.getParameter("i\_max");

// 处理 sizeOptions

boolean isSizeOptionsEmpty = (sizeOptions == null || sizeOptions.length == 0);

String sizeOptionQuery = "";

if (!isSizeOptionsEmpty) {

   sizeOptionQuery = " AND (size IN (";

    for (int i = 0; i \< sizeOptions.length; i++) {

       sizeOptionQuery += "'" + sizeOptions\[i\] + "'";

       if (i \< sizeOptions.length - 1) {

           sizeOptionQuery += ",";

        }

    }

   sizeOptionQuery += ") OR size IS NULL)";

}

// 处理 d\_min 和 d\_max

int d\_min = Integer.MIN\_VALUE; // 默认值,根据需要进行修改

int d\_max = Integer.MAX\_VALUE; // 默认值,根据需要进行修改

if (d\_minParam != null && !d\_minParam.isEmpty() && d\_maxParam != null && !d\_maxParam.isEmpty()) {

    d\_min = Integer.parseInt(d\_minParam);

    d\_max = Integer.parseInt(d\_maxParam);

}

// 处理 angle\_min 和 angle\_max

int angle\_min = Integer.MIN\_VALUE; // 默认值,根据需要进行修改

int angle\_max = Integer.MAX\_VALUE; // 默认值,根据需要进行修改

if (angle\_minParam != null && !angle\_minParam.isEmpty() && angle\_maxParam != null && !angle\_maxParam.isEmpty()) {

   angle\_min = Integer.parseInt(angle\_minParam);

    angle\_max = Integer.parseInt(angle\_maxParam);

}

// 处理 i\_min 和 i\_max

float i\_min = Float.MIN\_VALUE; // 默认值,根据需要进行修改

float i\_max = Float.MAX\_VALUE; // 默认值,根据需要进行修改

if (i\_minParam != null && !i\_minParam.isEmpty() && i\_maxParam != null && !i\_maxParam.isEmpty()) {

    i\_min = Float.parseFloat(i\_minParam);

    i\_max = Float.parseFloat(i\_maxParam);

}

// 处理 CurOptions

boolean isCurOptionsEmpty = (CurOptions == null || CurOptions.length == 0);

String CurOptionQuery = "";

if (!isCurOptionsEmpty) {

   CurOptionQuery = " AND (";

    if (CurOptions.length > 0) {

CurOptionQuery += "cur IN (";

    for (int i = 0; i \< CurOptions.length; i++) {

       CurOptionQuery += "'" + CurOptions\[i\] + "'";

            if (i \< CurOptions.length - 1) {

               CurOptionQuery += ",";

           }

        }

   CurOptionQuery += ")";

   } else {

       CurOptionQuery += "cur IS NOT NULL";

    }

    CurOptionQuery += ")";

}

// 建立 SQL 查询语句

String sql = "SELECT pd\_name, ifnull(size,'') as 'size', ifnull(d\_1,'') as 'd\_1', ifnull(d\_2,'') as 'd\_2', ifnull(d\_3,'') as 'd\_3', ifnull(i\_1, '') as 'i\_1', ifnull(i\_2,'') as 'i\_2', ifnull(i\_3,'') as 'i\_3', ifnull(angle,'') as 'angle', ifnull(cur,'') as 'cur', file FROM test WHERE 1=1";

sql += sizeOptionQuery;

sql += " AND ((d\_1 BETWEEN ? AND ?) OR (d\_2 BETWEEN ? AND ?) OR (d\_3 BETWEEN ? AND ?))";

sql += CurOptionQuery;

sql += " AND (angle BETWEEN ? AND ?)";

sql += " AND ((i\_1 BETWEEN ? AND ?) OR (i\_2 BETWEEN ? AND ?) OR (i\_3 BETWEEN ? AND ?))";

preparedStatement = conn.prepareStatement(sql);

int paramIndex = 1;

preparedStatement.setInt(paramIndex++, d_min);

preparedStatement.setInt(paramIndex++, d_max);

preparedStatement.setInt(paramIndex++, d\_min);

preparedStatement.setInt(paramIndex++, d_max);

preparedStatement.setInt(paramIndex++, d_min);

preparedStatement.setInt(paramIndex++, d_max);

preparedStatement.setFloat(paramIndex++, i_min);

preparedStatement.setFloat(paramIndex++, i\_max);

preparedStatement.setFloat(paramIndex++, i_min);

preparedStatement.setFloat(paramIndex++, i_max);

preparedStatement.setFloat(paramIndex++, i_min);

preparedStatement.setFloat(paramIndex++, i_max);

preparedStatement.setInt(paramIndex++, angle\_min);

preparedStatement.setInt(paramIndex++, angle_max);

// 执行查询

resultSet = preparedStatement.executeQuery();

// 建立结果列表

List resultList = new ArrayList();

// 将查询结果添加到结果列表

while (resultSet.next()) {

   String\[\] rowData = new String\[11\];

   rowData\[0\] = resultSet.getString("pd\_name");

   rowData\[1\] = resultSet.getString("size");

    rowData\[2\] = resultSet.getString("d\_1");

   rowData\[3\] = resultSet.getString("d\_2");

    rowData\[4\] = resultSet.getString("d\_3");

   rowData\[5\] = resultSet.getString("i\_1");

   rowData\[6\] = resultSet.getString("i\_2");

   rowData\[7\] = resultSet.getString("i\_3");

   rowData\[8\] = resultSet.getString("angle");

   rowData\[9\] = resultSet.getString("cur");

   rowData\[10\] = resultSet.getString("file");

   resultList.add(rowData);

}

// 关闭资源

if (resultSet != null) {

   resultSet.close();

}

if (preparedStatement != null) {

   preparedStatement.close();

}

if (conn != null) {

   conn.close();

}

%>

<html>

<body>

\<h1 align=center>Query Result\</h1>

\<table align='center' border='1' bordercolor="F28500" width=90%>

   \<thead>

       \<tr>

           \<th>pd\_name\</th>

           \<th>size\</th>

           \<th>d\_1\</th>

           \<th>d\_2\</th>

           \<th>d\_3\</th>

            \<th>i\_1\</th>

           \<th>i\_2\</th>

           \<th>i\_3\</th>

           \<th>angle\</th>

           \<th>cur\</th>

           \<th>file\</th>

       \</tr>

   \</thead>

   \<tbody>

       \<%

           for (Object obj : resultList) {

               String\[\] rowData = (String\[\]) obj;

       %>

       \<tr>

           \<td>\<%= rowData\[0\] %>\</td>

           \<td>\<%= rowData\[1\] %>\</td>

           \<td>\<%= rowData\[2\] %>\</td>

           \<td>\<%= rowData\[3\] %>\</td>

           \<td>\<%= rowData\[4\] %>\</td>

           \<td>\<%= rowData\[5\] %>\</td>

           \<td>\<%= rowData\[6\] %>\</td>

           \<td>\<%= rowData\[7\] %>\</td>

           \<td>\<%= rowData\[8\] %>\</td>

           \<td>\<%= rowData\[9\] %>\</td>

           \<td>\<%= rowData\[10\]%>\</td>

       \</tr>

       \<% } %>

   \</tbody>

</table>

</body>

</html>

Comments
Post Details
Added on May 22 2023
0 comments
143 views