JavaWeb：Servlet中使用JDBC连接数据库

1.创建数据库

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `role` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `t_user` VALUES ('1', 'admin', '123', '管理员');
INSERT INTO `t_user` VALUES ('2', 'user', '123', '普通用户');

2.编写SysUserServlet.java

public class SysUserServlet implements Servlet {
    @Override
    public void init(ServletConfig servletConfig) throws ServletException {
    }

    @Override
    public ServletConfig getServletConfig() {
        return null;
    }

    @Override
    public void service(ServletRequest servletRequest, ServletResponse servletResponse) throws ServletException, IOException {
        // 设置响应的内容类型
        servletResponse.setContentType("text/html");

        // 输出信息到浏览器
        PrintWriter writer = servletResponse.getWriter();

        // 编写JDBC代码，连接数据库，查询所有信息
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            // 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 获取连接
            String url = "jdbc:mysql://localhost:3306/javawebtest";
            String user = "root";
            String password = "123456";
            conn = DriverManager.getConnection(url,user,password);
            // 获取预编译的数据库操作对象
            String sql = "select * from t_user";
            ps = conn.prepareStatement(sql);
            // 执行sql
            rs = ps.executeQuery();
            // 处理查询结果集
            while (rs.next()) {
                String username = rs.getString("username");
                String password1 = rs.getString("password");
                //System.out.println(username + " " + password1);
                writer.print(username + " " + password1 + "<br>");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public String getServletInfo() {
        return null;
    }

    @Override
    public void destroy() {
    }
}

在web.xml文件中完成SysUserServlet类的注册

<servlet>
        <servlet-name>SysUserServlet</servlet-name>
        <servlet-class>cn.lwx.javaweb.servletJDBC.SysUserServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>SysUserServlet</servlet-name>
        <url-pattern>/user</url-pattern>
    </servlet-mapping>

配置Tomcat

运行tomcat