Java中带条件的查询

最新推荐文章于 2024-05-05 20:32:08 发布

武睿傲雪

最新推荐文章于 2024-05-05 20:32:08 发布

阅读量5.4k

点赞数 1

分类专栏： Java Web学习笔记文章标签：模糊查询 java

本文链接：https://blog.csdn.net/wuruiaoxue/article/details/50683639

版权

Java Web学习笔记专栏收录该内容

39 篇文章 1 订阅

订阅专栏

使用的查询页面能设置复杂的查询条件。其基本原理是把查询条件转化为对应的WHERE子句，然后使用包含该WHERE子句的SQL查询数据库。下面使用tb_person表进行复杂的查询为例：
searchPerson.jsp

<%@ page language="java" pageEncoding="UTF-8" contentType="text/html;charset=UTF-8" %>
<%@ page import="java.sql.ResultSet" %>
<jsp:directive.page import="java.sql.Date"/>
<jsp:directive.page import="java.sql.Timestamp"/>
<jsp:directive.page import="com.helloweenvsfei.util.DbManager"/>
<jsp:directive.page import="com.helloweenvsfei.util.Pagination"/>
<jsp:directive.page import="java.sql.Connection"/>
<jsp:directive.page import="java.sql.Statement"/>
<jsp:directive.page import="java.sql.SQLException"/>
<%!
    public String forSQL(String sql){
        return sql.replace("'", "\\'");
    }
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>My JSP 'listPerson.jsp' starting page</title>
    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">    
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">
    <style type="text/css">body, td, th{font-size:12px; text-align:center; }</style>
  </head>
  <body>
<%
    request.setCharacterEncoding("UTF-8");

    final int pageSize = 5;

    int pageNum = 1;

    try{
        pageNum = new Integer(request.getParameter("pageNum"));
    }catch(Exception e){}

    String nameSearch = request.getParameter("name");
    String sexSearch = request.getParameter("sex");
    String englishNameSearch = request.getParameter("englishName");
    String descriptionSearch = request.getParameter("description");
    String birthdayStart = request.getParameter("birthdayStart");
    String birthdayEnd = request.getParameter("birthdayEnd");

    String whereClause = "";

    // 模糊匹配
    if(nameSearch!=null && nameSearch.trim().length()!=0){
        if(whereClause.length() == 0)
            whereClause += " name LIKE '%" + forSQL(nameSearch) + "%'";
        else
            whereClause += " AND name LIKE '%" + forSQL(nameSearch) + "%'";
    }
    // 精确匹配
    if(sexSearch!=null && sexSearch.trim().length()!=0){
        if(whereClause.length() == 0)
            whereClause += " sex = '" + forSQL(sexSearch) + "' ";
        else
            whereClause += " AND sex = '" + forSQL(sexSearch) + "' ";
    }
    if(englishNameSearch!=null && englishNameSearch.trim().length()!=0){
        if(whereClause.length() == 0)
            whereClause += " english_name LIKE '%" + forSQL(englishNameSearch) + "%' ";
        else
            whereClause += " AND english_name LIKE '%" + forSQL(englishNameSearch) + "%' ";
    }
    if(descriptionSearch!=null && descriptionSearch.trim().length()!=0){
        if(whereClause.length() == 0)
            whereClause += " description LIKE '%" + forSQL(descriptionSearch) + "%' ";
        else
            whereClause += " AND description LIKE '%" + forSQL(descriptionSearch) + "%' ";
    }
    if(birthdayStart!=null && birthdayStart.trim().length()!=0){
        if(whereClause.length() == 0)
            whereClause += " birthday >= '" + birthdayStart + "' ";
        else
            whereClause += " AND birthday >= '" + birthdayStart + "' ";
    }
    if(birthdayEnd!=null && birthdayEnd.trim().length()!=0){
        if(whereClause.length() == 0)
            whereClause += " birthday <= '" + birthdayEnd + "' ";
        else
            whereClause += " AND birthday <= '" + birthdayEnd + "' ";
    }

    if(whereClause.length() != 0){
        whereClause = " WHERE " + whereClause;
    }

    String countSQL = " SELECT count(*) FROM tb_person " + whereClause;
    int recordCount = DbManager.getCount(countSQL); 

    int pageCount = (recordCount + pageSize) / pageSize;

    String querySQL = " SELECT * FROM tb_person " + whereClause + " LIMIT " + (pageNum-1)*pageSize + ", " + pageSize;

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    try{
        conn = DbManager.getConnection();
        stmt = conn.createStatement();
        rs = stmt.executeQuery(querySQL);
%>
 <form action="searchPerson.jsp" method=get>
  <fieldset style='width:80%'>
    <legend>查询条件</legend>
    <table >
        <tr>
            <td style="text-align:right; ">姓名</td>
            <td style="text-align:left; ">
                <input type='text' name='name' value="${ param.name }"/>
            </td>
            <td style="text-align:right; ">性别</td>
            <td style="text-align:left; ">
                <select name='sex' />
                    <option value="">无限制</option>
                    <option value="男" ${ '男'==param.sex ? 'selected' : '' }>男</option>
                    <option value="女" ${ '女'==param.sex ? 'selected' : '' }>女</option>
                </select>
            </td>
        </tr>
        <tr>
            <td style="text-align:right; ">英文名</td>
            <td style="text-align:left; ">
                <input type='text' name='englishName' value="${ param.englishName }"/>
            </td>
            <td style="text-align:right; ">备注</td>
            <td style="text-align:left; ">
                <input type='text' name='description' value="${ param.description }"/>
            </td>
        </tr>
        <tr>
            <td colspan=4>
                出生日期
                从 <input type='text' name='birthdayStart' onfocus="setday(birthdayStart);" value="${ param.birthdayStart }"/>
                <img src="images/calendar.gif" onclick="setday(birthdayStart);" />&nbsp;&nbsp;
                到 <input type='text' name='birthdayEnd' onfocus="setday(birthdayEnd);" value="${ param.birthdayEnd }"/>
                <img src="images/calendar.gif" onclick="setday(birthdayEnd);" />
            </td>
        </tr>
        <tr>
            <td colspan=4>
                <input type="submit" value="提交查询">
                <input type="reset" value="复位">
            </td>
        </tr>
    </table>
  </fieldset>
  <br/>
  <table bgcolor="#CCCCCC" cellspacing=1 cellpadding=5 width=100%>
    <tr bgcolor=#DDDDDD>
        <th>ID</th>
        <th>姓名</th>
        <th>英文名</th>
        <th>性别</th>
        <th>年龄</th>
        <th>生日</th>
        <th>备注</th>
        <th>记录创建时间</th>
    </tr>
<%
    // 遍历结果集。rs.next() 返回结果集中是否还有下一条记录。如果有，自动滚动到下一条记录并返回 true
    while(rs.next()){

        int id = rs.getInt("id");   // 整形类型
        int age = rs.getInt("age");

        String name = rs.getString("name"); // 字符串类型
        String englishName = rs.getString("english_name");
        String sex = rs.getString("sex");
        String description = rs.getString("description");

        Date birthday = rs.getDate("birthday"); // 日期类型，只有日期信息而没有时间信息
        Timestamp createTime = rs.getTimestamp("create_time"); // 时间戳类型，既有日期又有时间。

        out.println("       <tr bgcolor=#FFFFFF>");
        out.println("           <td>" + id + "</td>");
        out.println("           <td>" + name + "</td>");
        out.println("           <td>" + englishName + "</td>");
        out.println("           <td>" + sex + "</td>");
        out.println("           <td>" + age + "</td>");
        out.println("           <td>" + birthday + "</td>");
        out.println("           <td>" + description + "</td>");
        out.println("           <td>" + createTime + "</td>");
        out.println("       </tr>");
    }
%>
  </table>
  <table align=right><tr><td>
    <%= Pagination.getPagination(pageNum, pageCount, recordCount, request.getRequestURI()) %>
  </td></tr></table><br/><br/>
  <table width='100%'><tr><td style='text-align:center; '>
    <br/><br/> <%= "Count SQL: " + countSQL %>
    <br/><br/> <%= "Query SQL: " + querySQL %>
  </td></tr></table>
  </form>
<%
    }catch(SQLException e){
        out.println("执行SQL：" + querySQL + "时出错：" + e.getMessage());
    }finally{
        if(rs != null)  rs.close();
        if(stmt != null)    stmt.close();
        if(conn != null)    conn.close();
    }
%>
  <script type="text/javascript" src="js/calendar.js"></script>
  </body>
</html>

实现分页的代码Pagination.java
Pagination.java

package com.helloweenvsfei.util;

public class Pagination {

    /**
     * 
     * @param pageNum
     *            当前页数
     * @param pageCount
     *            总页数
     * @param recordCount
     *            总记录数
     * @param pageUrl
     *            页面 URL
     * @return
     */
    public static String getPagination(int pageNum, int pageCount,
            int recordCount, String pageUrl) {

        String url = pageUrl.contains("?") ? pageUrl : pageUrl + "?";
        if(!url.endsWith("?") && !url.endsWith("&")){
            url += "&";
        }

        StringBuffer buffer = new StringBuffer();
        buffer.append("第 " + pageNum + "/" + pageCount + " 页 共 " + recordCount
                + " 记录 ");

        buffer.append(pageNum == 1 ? " 第一页 " : " <a href='" + url
                + "pageNum=1'>第一页</a> ");
        buffer.append(pageNum == 1 ? " 上一页 " : " <a href='" + url + "pageNum="
                + (pageNum - 1) + "'>上一页</a> ");
        buffer.append(pageNum == pageCount ? " 下一页 " : " <a href='" + url
                + "pageNum=" + (pageNum + 1) + "'>下一页</a> ");
        buffer.append(pageNum == pageCount ? " 最后一页 " : " <a href='" + url
                + "pageNum=" + pageCount + "'>最后一页</a> ");

        buffer.append(" 到 <input type='text' ");
        buffer.append("  name='helloweenvsfei_goto_input' ");
        buffer.append("  style='width:25px; text-align:center; '> 页 ");
        buffer.append(" <input type='button'");
        buffer.append("  name='helloweenvsfei_goto_button' value='Go'>");

        buffer.append("<script language='javascript'>");
        buffer.append("function helloweenvsfei_enter(){");
        buffer.append(" if(event.keyCode == 13){");
        buffer.append("     helloweenvsfei_goto();");
        buffer.append("     return false;");
        buffer.append(" }");
        buffer.append(" return true;");
        buffer.append("} ");
        buffer.append("function helloweenvsfei_goto(){");
        buffer
                .append("   var numText = document.getElementsByName('helloweenvsfei_goto_input')[0].value;");
        buffer.append(" var num = parseInt(numText, 10);");
        buffer.append(" if(!num){");
        buffer.append("     alert('页数必须为数字');   ");
        buffer.append("     return;");
        buffer.append(" }");
        buffer.append(" if(num<1 || num>" + pageCount + "){");
        buffer.append("     alert('页数必须大于 1，且小于总页数 " + pageCount + " ');    ");
        buffer.append("     return;");
        buffer.append(" }");
        buffer.append(" location='" + url + "pageNum=' + num;");
        buffer.append("}");
        buffer
                .append("document.getElementsByName('helloweenvsfei_goto_input')[0].onkeypress = helloweenvsfei_enter;");
        buffer
                .append("document.getElementsByName('helloweenvsfei_goto_button')[0].onclick = helloweenvsfei_goto;");
        buffer.append("</script>");

        return buffer.toString();
    }
}

数据库连接的代码DbManager.java
Dbmanager.java

package com.helloweenvsfei.util;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;

import com.mysql.jdbc.Driver;

public class DbManager {

    /**
     * 获取默认数据库连接
     * 
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return getConnection("databaseWeb", "root", "admin");
    }

    /**
     * 获取数据库连接
     * 
     * @param dbName
     * @param userName
     * @param password
     * @return
     * @throws SQLException
     */
    public static Connection getConnection(String dbName, String userName,
            String password) throws SQLException {

        String url = "jdbc:mysql://localhost:3306/" + dbName
                + "?characterEncoding=utf-8";

        DriverManager.registerDriver(new Driver());

        return DriverManager.getConnection(url, userName, password);
    }

    /**
     * 设置 PreparedStatement 参数
     * 
     * @param preStmt
     * @param params
     * @throws SQLException
     */
    public static void setParams(PreparedStatement preStmt, Object... params)
            throws SQLException {

        if (params == null || params.length == 0)
            return;

        for (int i = 1; i <= params.length; i++) {
            Object param = params[i - 1];
            if (param == null) {
                preStmt.setNull(i, Types.NULL);
            } else if (param instanceof Integer) {
                preStmt.setInt(i, (Integer) param);
            } else if (param instanceof String) {
                preStmt.setString(i, (String) param);
            } else if (param instanceof Double) {
                preStmt.setDouble(i, (Double) param);
            } else if (param instanceof Long) {
                preStmt.setDouble(i, (Long) param);
            } else if (param instanceof Timestamp) {
                preStmt.setTimestamp(i, (Timestamp) param);
            } else if (param instanceof Boolean) {
                preStmt.setBoolean(i, (Boolean) param);
            } else if (param instanceof Date) {
                preStmt.setDate(i, (Date) param);
            }
        }
    }

    /**
     * 执行 SQL，返回影响的行数
     * 
     * @param sql
     * @return
     * @throws SQLException
     */
    public static int executeUpdate(String sql) throws SQLException {
        return executeUpdate(sql, new Object[] {});
    }

    /**
     * 带参数执行SQL，返回影响的行数
     * 
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static int executeUpdate(String sql, Object... params)
            throws SQLException {

        Connection conn = null;
        PreparedStatement preStmt = null;

        try {
            conn = getConnection();

            preStmt = conn.prepareStatement(sql);

            setParams(preStmt, params);

            return preStmt.executeUpdate();

        } finally {
            if (preStmt != null)
                preStmt.close();
            if (conn != null)
                conn.close();
        }
    }

    /**
     * 获取总数。
     * 
     * @param sql
     *            格式必须为 SELECT count(*) FROM ...
     * @return
     * @throws SQLException
     */
    public static int getCount(String sql) throws SQLException {

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            rs.next();
            return rs.getInt(1);
        } finally {
            if (rs != null)
                rs.close();
            if (stmt != null)
                stmt.close();
            if (conn != null)
                conn.close();
        }
    }

}

注意当客户没有填写姓名时Where条件里不能出现姓名列。程序中对name参数做了判断，如果name为空则不查询name.另外还要注意吧用户填写的信息在查询结果页面上显示。程序运行效果如图所示：
这里写图片描述

武睿傲雪

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
2
评论
Java中带条件的查询

使用的查询页面能设置复杂的查询条件。其基本原理是把查询条件转化为对应的WHERE子句，然后使用包含该WHERE子句的SQL查询数据库。下面使用tb_person表进行复杂的查询为例： searchPerson.jsp<%@ page language="java" pageEncoding="UTF-8" contentType="text/html;charset=UTF-8" %><%@ p
复制链接

扫一扫