servlet+JDBC实现登录 增删查改 分页显示

     本人是初学者，网上的搜了一下基本很少有详细的讲解，所以自己亲自动手做了下，把遇到的一些问题也说明了一下，希望对和我一样的菜鸟有所帮助，共同进步。

    本案例没有添加AJAX以及JS验证，只是简单的功能实现。实现方法有不合理之处请大神指教，小弟不胜感激。

运行环境：Eclipse Java EE IDE for Web Developers Mars Release (4.5.0)

 apache-tomcat-8.0.22

jdk-7u17-windows-i586  

SQL2008 R2

需要添加的JAR包sqljdbc4.jar

表结构：
CREATE TABLE [dbo].[users](
[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[name] [varchar](50) ,
[password] [varchar](50) ,
[sex] [varchar](50) 
) 

项目文件结构：

业务逻辑为登录成功直接分页显示所有结果，和修改删除功能。注册成功同样是直接显示此界面。。比较简单哈哈运行结果如下：

实体类User.java

package com.wu.entity;
public class User {
private int id;
private String name;
private String password;
private String sex;
public int getid(){
return id;
}
public void setid(int id){
this.id=id;
}
public String getname(){
return name;
}
public void setname(String name){
this.name=name;
}
public String getpassword(){
return password;
}
public void setpassword(String password){
this.password=password;
}
public String getsex(){
return sex;
}
public void setsex(String sex){
this.sex=sex;
}
}

创建数据库连接sqlutil.java

package com.wu.util;
import java.sql.Connection;
import java.sql.DriverManager;
public class sqlutil {
private static Connection conn;

public static Connection getconn() {
String url="jdbc:sqlserver://localhost:1433;databaseName=test";
String username="sa";
String password="6321194";
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn=DriverManager.getConnection(url, username, password);
}catch(Exception e){
e.printStackTrace();
return null;
}
return conn;
}
}

创建UserDao接口

package com.wu.dao;
import java.sql.SQLException;
import com.wu.entity.User;

public interface UserDao {
boolean checkLogin(User user) throws Exception;    //登录验证
int adduser(User user) throws Exception;     //添加用户
User serchforid(int id) throws SQLException;   //根据ID查找用户
int delet(int id) throws SQLException;    //删除用户
int update(int id,User user) throws SQLException;   //修改用户信息
}

实现方法：UserDaoimpl.java

package com.wu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.wu.entity.User;
import com.wu.util.sqlutil;

public  class UserDaoimpl implements UserDao{
private Connection conn=null;
private PreparedStatement pstmt = null;
private ResultSet rs=null;

@Override
public boolean checkLogin(User user) throws Exception {
conn = sqlutil.getconn();
String sql="select * from users where name=? and password=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, user.getname());
pstmt.setString(2, user.getpassword());
rs=pstmt.executeQuery();
if(rs.next()){
return true;
}
return false;
}
@Override
public int adduser(User user) throws Exception {
conn=sqlutil.getconn();
String sql="insert into users(name,password,sex) values(?,?,?)";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, user.getname());
pstmt.setString(2, user.getpassword());
pstmt.setString(3, user.getsex());
int row=pstmt.executeUpdate();
return row;
}
@Override
public User serchforid(int id) throws SQLException {
User user=new User();
conn=sqlutil.getconn();
String sql="select * from users where id=?";
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, id);
rs=pstmt.executeQuery();
while(rs.next()){
user.setid(rs.getInt("id"));
user.setname(rs.getString("name"));
user.setpassword(rs.getString("password"));
user.setsex(rs.getString("sex"));
}
System.out.print(user.getsex());
return user;
}
@Override
public int delet(int id) throws SQLException {
conn=sqlutil.getconn();
String sql="delete from users where id=?";
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, id);
int row=pstmt.executeUpdate();
return row;
}
@Override
public int update(int id,User user) throws SQLException {
conn=sqlutil.getconn();
String sql="update  users set name=?,password=?,sex=? where id=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, user.getname());
pstmt.setString(2, user.getpassword());
pstmt.setString(3, user.getsex());
pstmt.setInt(4, id);
int row=pstmt.executeUpdate();
return row;
}
}

PageDao接口

package com.wu.dao;

import java.sql.SQLException;
import java.util.List;
import com.wu.entity.User;
public interface PageDao {
int maxPage(int rowsperpage) throws SQLException;  //总共页数
List<User> getsearch(User user,int rowsperpage,int nowpage) throws Exception; //根据每页记录数，当前页码返回记录集
}

实现方法PageDaoimpl.java

package com.wu.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.wu.entity.User;
import com.wu.util.sqlutil;


public class PageDaoimpl implements PageDao{
private Connection conn=null;
private PreparedStatement pstmt=null;
private ResultSet rs=null;


@Override
public int maxPage(int rowsperpage) throws SQLException {
conn = sqlutil.getconn();
String sql="select count(id) from users";
Statement stmt = conn.createStatement();
rs=stmt.executeQuery(sql);
int maxrecord = 0;
if(rs.next()){
maxrecord=rs.getInt(1);
System.out.print("啊啊啊");
}
int maxpage=(maxrecord+rowsperpage-1)/rowsperpage;

return maxpage;
}


@Override
public List<User> getsearch(User user,int rowsperpage,int nowpage) throws Exception {
int page=rowsperpage*(nowpage-1);
List<User> list=new ArrayList<User>();
conn=sqlutil.getconn();
String sql="select top "+rowsperpage+" * from users where id not in (select top "+page+" id from users) order by id asc";//这里注意不要使用？通配符，不然会报错
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()){
User user2 = new User();
user2.setid(rs.getInt("id"));
user2.setname(rs.getString("name"));
user2.setpassword(rs.getString("password"));
user2.setsex(rs.getString("sex"));
list.add(user2);
}
return list;
}
}

控制层：

delet.java

package com.wu.servlet;


import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


import com.wu.dao.UserDao;
import com.wu.dao.UserDaoimpl;


@WebServlet("/delet")
public class delet extends HttpServlet {
private static final long serialVersionUID = 1L;


    public delet() {
        super();
        // TODO Auto-generated constructor stub
    }
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request,response);
}


protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
int id=Integer.parseInt(request.getParameter("id").toString());
UserDao userdao=new UserDaoimpl();
int d=0;
try {
d=userdao.delet(id);
} catch (SQLException e) {
e.printStackTrace();
}
if(d>0){
request.getRequestDispatcher("cg.jsp").forward(request, response);
}else{
request.getRequestDispatcher("error.jsp").forward(request, response);
}
}
}

登陆验证loginservlet.java

package com.wu.servlet;


import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.wu.dao.UserDao;
import com.wu.dao.UserDaoimpl;
import com.wu.entity.User;


@WebServlet("/loginservlet")
public class loginservlet extends HttpServlet {
private static final long serialVersionUID = 1L;
    public loginservlet() {
        super();
        // TODO Auto-generated constructor stub
    }
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String username = request.getParameter("username");
String password = request.getParameter("password");
User user=new User();
user.setname(username);
user.setpassword(password);
boolean b = false;
UserDao userdao=new UserDaoimpl();
try {
b= userdao.checkLogin(user);
} catch (Exception e) {
e.printStackTrace();
}
if (b==true){  
request.getRequestDispatcher("/shall").forward(request, response);
}else{request.getRequestDispatcher("/error.jsp").forward(request, response); }
}
}