带参数的数据查询

作者: 神坛下的我 | 来源:发表于2018-08-19 16:25 被阅读0次

带参数的数据查询
ES-查询语句说明
JFinal2.2初体验之数据库操作扩展上（六）
MyBatis的映射
elasticsearch 查询语句
7.有参方法和包的概念
参数化查询原理
SpringMVC接受请求参数
有参方法和包
第七章有参方法和包的概念

Util.java

import java.sql.*;

public class Util {
    private Connection conn;
    private Statement stm;
    private ResultSet rs;
    private String classname="com.mysql.jdbc.Driver";
    private String url="jdbc:mysql://localhost:3306/test";
    public Util(){
        try {
            Class.forName(classname);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    /*获取连接*/
    public Connection getConn() {
        try {
            conn=DriverManager.getConnection(url,"root","123456");
        } catch (SQLException e) {
            e.printStackTrace(System.err);
            conn=null;
        }
        return conn;
    }
    /*关闭连接*/
    public void closed(){
        
            try {
                if (rs!=null) {
                    rs.close();
                }
                if (stm!=null) {
                    stm.close();
                }
                if(conn!=null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
    
    
    public void setConn(Connection conn) {
        this.conn = conn;
    }
    /*执行语句*/
    public Statement getStm() {
        
        try {
            conn=getConn();
            stm=conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stm;
    }
    public Statement getStmed(){
        
        try {
            conn=getConn();
            stm=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stm;
    }
    public ResultSet getAllRs(){
        String sql="select * from u";
        
        try {
            stm=getStmed();
            rs=stm.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }
    public ResultSet getPartRs(String subsql,String subsqlvalue){
        if(subsql==null){
            subsql="";
        }
        if(subsqlvalue==null){
            subsqlvalue="";
        }
        String sql= "select * from u where "+subsql+" ='"+subsqlvalue+"'";
        
        try {
            stm=getStmed();
            rs=stm.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

index.jsp

<body>
    <form action="result.jsp" method="post">
        <table>
            <tr bgcolor="lightgrey" height="25">
                <td align="center">Id</td>
                <td align="center">用户名</td>
                <td align="center">密码</td>
            </tr>
            <jsp:useBean id="db" class="com.count.Util" scope="page"></jsp:useBean>
            <%
                ResultSet rsall = db.getAllRs();
                while(rsall.next()){
            %>
                <tr>
                    <td align="center"><%=rsall.getString("id") %></td>
                    <td align="center"><%=rsall.getString("name") %></td>
                    <td align="center"><%=rsall.getString("pwd") %></td>
                </tr>
            <%
                }
            %>
                <tr bgcolor="lightgrey">
                    <td align="center" colspan="5">
                    查询类型：
                    <select name="subsql">
                        <option value="id">Id</option>
                        <option value="name">用户名</option>
                        <option value="pwd">密码</option> 
                    </select>
                    <input type="text" name="subsqlvalue" size="17">
                    <input type="submit" name="searchpart" value="查询" onclick="return check()">
                    </td>
                </tr>
        </table>
    </form>
    
    <table>
        <tr bgcolor="skyblue">
            <td align="center">生成的版权信息</td>
        </tr>
        <tr>
            <td>
                <jsp:useBean id="copyright" class="com.count.Copyright"></jsp:useBean>
                <jsp:getProperty property="copyrightStr" name="copyright"/>
            </td>
        </tr>
    </table>
</body>

result.jsp

<body>
    <jsp:useBean id="db" class="com.count.Util" scope="page"></jsp:useBean>
    <%
        ResultSet rs = null;
        boolean mark=true;
        String mess="";
        String subsql= request.getParameter("subsql");
        String subsqlvalue=request.getParameter("subsqlvalue");
    %>
    <table>
        <tr>
            <td align="center" colspan="5"><%=mess %></td>
        </tr>
        <tr bgcolor="lightgrey" height="20">
            <td align="center">Id</td>
            <td align="center">用户名</td>
            <td align="center">密码</td>
        </tr>
        <%
            rs=db.getPartRs(subsql, subsqlvalue);
            if(!rs.next()){
        %>
        <tr>
            <td align="center" colspan="5">没有记录显示！</td>
        </tr>
        <%
            }else{
                rs.previous();
                while(rs.next()){
        %>
            <tr>
                <td align="center"><%=rs.getString(1) %></td>
                <td align="center"><%=rs.getString(2) %></td>
                <td align="center"><%=rs.getString(3) %></td>
            </tr>
        <%
                }
            }
        %>
    </table>
</body>