Servlet（12）连接数据库并显示用户列表

喜欢ヅ旅行 2024-02-17 21:09 66阅读 0赞

### 1 创建数据库 ###

create table users(
        id number primary key,
        username varchar2(32) not null,
        email varchar2(64) not null,
        grade number default 1,
        passwd varchar2(32) not null);
    
    insert into users values(1, 'aaa1', 'aaa1@sohu.com', 1, '123');
    insert into users values(2, 'aaa2', 'aaa2@sohu.com', 1, '123');
    insert into users values(3, 'aaa3', 'aaa3@sohu.com', 1, '123');
    insert into users values(4, 'aaa4', 'aaa4@sohu.com', 1, '123');
    insert into users values(5, 'aaa5', 'aaa5@sohu.com', 5, '123');
    
    insert into users(id, username, email, grade, passwd) (select id+(select count(*) from users), username, email, grade, passwd from users);

### 2 连接数据库 ###

![这里写图片描述][SouthEast]

Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        con = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:orcl",    
                "scott", "tiger");
        ps = con.prepareStatement("select * from users where id=? and passwd=?");
        ps.setObject(1, username);
        ps.setObject(2, password);
    
        rs = ps.executeQuery();
        System.out.println("before");
        if(rs.next()) {
            System.out.println("in ini in");
            //合法
            // 把参数放入Request域对象中
            request.setAttribute("username", username);
            request.setAttribute("password", password);
            // 使用转发方法，把Request和Response对象传递给下一个servlet
            request.getRequestDispatcher("/MainFrame").forward(request, response);
    
        } else {
            // 跳回
            request.getRequestDispatcher("/LoginServlet").forward(request, response);
        }
    
    } catch (Exception e) {
        // TODO: handle exception
        e.printStackTrace();
    } finally {
    
    }

### 3 用户列表 ###

out.println("<table border=1 width=500>");
    out.println("<tr><th>id</th><th>用户名</th><th>email</th><th>级别</th></tr>");
    while(rs.next()) {
        out.println("<tr>"
                + "<td>"+ rs.getInt(1) +"</td>"
                + "<td>"+ rs.getString(2) +"</td>"
                + "<td>"+ rs.getString(3) +"</td>"
                + "<td>"+ rs.getString(4) +"</td>"
                + "</tr>");
    
    }
    out.println("</table>");

[SouthEast]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/39afce95b1f14c8eb2e7653da484a163.png