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

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 连接数据库

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>");