jsp+jdbc+Servlet实现登录系统(.java)

user.java

public class User {
private int id;
private String username;
private String password;
private int age;

private int minAge;
private int maxAge;

public User() {
// TODO Auto-generated constructor stub
}




public User(int id, String username, String password, int age) {
super();
this.id = id;
this.username = username;
this.password = password;
this.age = age;
}


public User(String username, String password, int age) {
super();
this.username = username;
this.password = password;
this.age = age;
}






public int getId() {
return id;
}


public void setId(int id) {
this.id = id;
}


public String getUsername() {
return username;
}


public void setUsername(String username) {
this.username = username;
}


public String getPassword() {
return password;
}


public void setPassword(String password) {
this.password = password;
}


public int getAge() {
return age;
}


public void setAge(int age) {
this.age = age;
}






@Override
public String toString() {
return "User [age=" + age + ", id=" + id + ", password=" + password
+ ", username=" + username + "]";
}


public void setMaxAge(int maxAge) {
this.maxAge = maxAge;
}

public void setMinAge(int minAge) {
this.minAge = minAge;
}
public int getMaxAge() {
return maxAge;
}
public int getMinAge() {
return minAge;
}

}

userdao.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import com.soft.bean.User;
import com.soft.util.DBUtil;


public class UserDAO {
public boolean addUser(User user) {
Connection conn = null;
// Statement stmt = null;
PreparedStatement pstmt = null;
try {
// 1. 加载驱动类
String dbDriver = "com.mysql.jdbc.Driver";
Class.forName(dbDriver);

// 2. 建立数据库的连接对象
String url = "jdbc:mysql://127.0.0.1:3306/test";
String username = "root";
String password = "root";
conn = DriverManager.getConnection(url, username, password);

// 3. 创建封装SQL语句的对象
String sql = "INSERT INTO user (username, password, age) VALUES (?, ?, ?)";
    pstmt = conn.prepareStatement(sql);
    
    // 4. 编写SQL语句，执行具体的逻辑操作
   pstmt.setString(1, user.getUsername());
   pstmt.setString(2, user.getPassword());
   pstmt.setInt(3, user.getAge());
   
    int resNum = pstmt.executeUpdate();
    
    // 5. 处理SQL操作的结果
    if (resNum > 0) {
    // 添加成功
    return true;
    } else {
    // 添加失败
    return false;
    }
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
// 6. 关闭所有打开的流、连接等对象
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} 
}

}


}


public boolean queryUserByNameAndPwd(String username, String password) {

Connection conn = null;
// Statement stmt = null;
PreparedStatement pstmt = null;
try {
// 1 和 2 封装到DBUtil类中
conn = DBUtil.getConn();

// 3. 创建封装SQL语句的对象
String sql = "SELECT id FROM user where username=? AND password = ?";
    pstmt = conn.prepareStatement(sql);
    
    // 4. 编写SQL语句，执行具体的逻辑操作
   pstmt.setString(1, username);
   pstmt.setString(2, password);
 
   
   ResultSet rs = pstmt.executeQuery();
    
    // 5. 处理SQL操作的结果
    if (rs.next()) {
    // 添加成功
    return true;
    } else {
    // 添加失败
    return false;
    }
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
// 6. 关闭所有打开的流、连接等对象
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} 
}

}

}

// 查询所有用户记录
public List<User> queryAllUser() {

List<User> list =  new ArrayList<User>();

Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1 和 2 封装到DBUtil类中
conn = DBUtil.getConn();

// 3. 创建封装SQL语句的对象
String sql = "SELECT id, username, password, age FROM user";
    pstmt = conn.prepareStatement(sql);
    
    // 4. 编写SQL语句，执行具体的逻辑操作
   ResultSet rs = pstmt.executeQuery();
    
    // 5. 处理SQL操作的结果
   while(rs.next()) {
  int id = rs.getInt("id");
  String username = rs.getString("username");
  String password = rs.getString("password");
  int age = rs.getInt("age");
  
  User user = new User();
  user.setId(id);
  user.setUsername(username);
  user.setPassword(password);
  user.setAge(age);
  
  list.add(user);
   }
} catch (Exception e) {
e.printStackTrace();
} finally {
// 6. 关闭所有打开的流、连接等对象
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} 
}

}

return list;

}

// 删除一条用户记录
public boolean delteUser(int id) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1 和 2 封装到DBUtil类中
conn = DBUtil.getConn();

// 3. 创建封装SQL语句的对象
String sql = "DELETE FROM user WHERE id = " + id;
    pstmt = conn.prepareStatement(sql);
    
    // 4. 编写SQL语句，执行具体的逻辑操作
  int num = pstmt.executeUpdate();
    
    // 5. 处理SQL操作的结果
   if (num > 0) {
   return true;
   } else {
   return false;
   }
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
// 6. 关闭所有打开的流、连接等对象
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} 
}

}
}


// 查询一个用户通过ID
public User queryUserById(int id) {
User user = null;
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1 和 2 封装到DBUtil类中
conn = DBUtil.getConn();

// 3. 创建封装SQL语句的对象
String sql = "SELECT username, age FROM user WHERE id = " + id;
    pstmt = conn.prepareStatement(sql);
    
    // 4. 编写SQL语句，执行具体的逻辑操作
    ResultSet rs = pstmt.executeQuery();
    
    // 5. 处理SQL操作的结果
    if (rs.next()) {
    String username = rs.getString("username");
    int age = rs.getInt("age");
    user = new User();
    user.setUsername(username);
    user.setAge(age);
    user.setId(id);
    }
  
} catch (Exception e) {
e.printStackTrace();

} finally {
// 6. 关闭所有打开的流、连接等对象
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} 
}

}
return user;
}


// 更新一条记录
public boolean updateUser(User user) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1 和 2 封装到DBUtil类中
conn = DBUtil.getConn();

// 3. 创建封装SQL语句的对象
String sql = "UPDATE user SET username = ?, age = ? WHERE id = ?";
    pstmt = conn.prepareStatement(sql);
    
    // 4. 编写SQL语句，执行具体的逻辑操作
    pstmt.setString(1, user.getUsername());
    pstmt.setInt(2, user.getAge());
    pstmt.setInt(3, user.getId());
    
  int num = pstmt.executeUpdate();
    
    // 5. 处理SQL操作的结果
   if (num > 0) {
   return true;
   } else {
   return false;
   }
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
// 6. 关闭所有打开的流、连接等对象
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} 
}

}
}


public List<User> queryUser(User user) {
List<User> list =  new ArrayList<User>();

Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1 和 2 封装到DBUtil类中
conn = DBUtil.getConn();

// 3. 创建封装SQL语句的对象
StringBuilder sql = new StringBuilder("SELECT id, username, password, age FROM user ");
boolean hasWhere =false;

if (user.getUsername() != null && !user.getUsername().equals("")) {
hasWhere = this.ifNeedAppendWhere(sql, hasWhere);
sql.append(" username like '%" + user.getUsername() + "%'");
}

if (user.getMinAge() != 0) {
hasWhere = this.ifNeedAppendWhere(sql, hasWhere);
sql.append("  age > " + user.getMinAge());
}

if (user.getMaxAge() != 0) {
hasWhere = this.ifNeedAppendWhere(sql, hasWhere);
sql.append(" age < " + user.getMaxAge());
}

System.out.println("sql: " + sql.toString());
    pstmt = conn.prepareStatement(sql.toString());
    
    // 4. 编写SQL语句，执行具体的逻辑操作
   ResultSet rs = pstmt.executeQuery();
    
    // 5. 处理SQL操作的结果
   while(rs.next()) {
  int id = rs.getInt("id");
  String username = rs.getString("username");
  String password = rs.getString("password");
  int age = rs.getInt("age");
  
  User u = new User();
  u.setId(id);
  u.setUsername(username);
  u.setPassword(password);
  u.setAge(age);
  
  list.add(u);
   }
} catch (Exception e) {
e.printStackTrace();
} finally {
// 6. 关闭所有打开的流、连接等对象
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} 
}

}

return list;

}




private boolean ifNeedAppendWhere(StringBuilder sql, boolean hasWhere) {
if (!hasWhere) {
sql.append(" where ");
} else {
sql.append(" and ");
}
return true;
}
}

dbutil.java

import java.sql.Connection;
import java.sql.DriverManager;


public class DBUtil {
public static Connection getConn() throws Exception {
// 1. 加载驱动类
String dbDriver = "com.mysql.jdbc.Driver";
Class.forName(dbDriver);

// 2. 建立数据库的连接对象
String url = "jdbc:mysql://127.0.0.1:3306/test";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username, password);

return conn;

}
}

//testuserdao.java

import com.soft.dao.UserDAO;


public class TestUserDAO {
public static void main(String[] args) {
UserDAO userDAO = new UserDAO();


// User user = new User();
// user.setAge(45);
// user.setUsername("Zhangsan");
// user.setPassword("222222");
//
// boolean res = userDAO.addUser(user);
// System.out.println("add res: " + res);

List<User> list = userDAO.queryAllUser();

for (User user : list) {
System.out.println("user usernameL: " + user.getUsername());
}
}
}