数据库设计
用户表user1
数据库创建
create table user1(
userid number(10) primary key,
username varchar2(50) unique,
password varchar2(50),
isActive number(1),//0表示账号未激活,1表示账号激活
nickname varchar2(50),
address varchar2(200),
phonenumber varchar2(50)
) segment creation immediate
create sequence seq_user1;
插入数据
insert into user1 values(1,'张三','111','1','幸福的猪','北京','13899025463');
insert into user1 values(2,'李四','222','0','隔壁老王','北京','13899025453');
insert into user1 values(3,'李程程','111','1','小星星','北京','13899025233');
数据库连接
实体图
登录流程图
用户登录的前台设计
登录实现界面截图
调用流程如图:
目录结构如下:
1.实体类User
package com.vo;
public class User {
private Integer userid;
private String username;
private String password;
private Integer isActive;
private String nickname;
private String address;
@Override
public String toString() {
return "User [userid=" + userid + ", username=" + username
+ ", password=" + password + ", isActive=" + isActive
+ ", nickname=" + nickname + ", address=" + address + "]";
}
public Integer getUserid() {
return userid;
}
public void setUserid(Integer userid) {
this.userid = userid;
}
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 Integer getIsActive() {
return isActive;
}
public void setIsActive(Integer isActive) {
this.isActive = isActive;
}
public String getNickname() {
return nickname;
}
public void setNickname(String nickname) {
this.nickname = nickname;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
2.dao接口:UserDao
public interface UserDao {
//一.增加
public int addUser(User user) throws Exception;
//二.删除
public int deleteUser(User user) throws Exception;
//三.修改
public int updateUser(User user) throws Exception;
//四.查1
public User getUserById(Integer userid) throws Exception;
//五.按SQL语句查
public List<User> getPageByQuery(String sql) throws Exception;
//六.查询总共有多少条记录
public int getTotalRecordSum(String sql) throws Exception;
}
3.接口实现类:UserDaoImpl
public class UserDaoImpl implements UserDao {
private Connection conn;
public UserDaoImpl() {
conn = ConnOracle.getConnection();
}
@Override
public int addUser(User user) throws Exception {
int count = 0;
String sql = "insert into user1 values(seq_category.nextval,?,?)";
PreparedStatement pstmt = null;
// 三.建立通道
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, user.getUsername());
pstmt.setString(2, user.getPassword());
// 四.执行并返回结果集
count = pstmt.executeUpdate();// 执行dml 或 ddl语句的 返回受影响的行数
if (count >= 1) {
System.out.println("添加用户成功!");
} else {
System.out.println("没有添加任何用户!");
}
} catch (SQLException e) {
System.out.println("建立通道失败");
e.printStackTrace();
throw new Exception("添加用户失败");
} finally {
// 5.关闭
ConnOracle.closeConnection(null, pstmt, conn);
}
return count;
}
@Override
public int deleteUser(User user) throws Exception{
int count = 0;
String sql = "delete from user where userid=?";
PreparedStatement pstmt = null;
// 三.建立通道
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, user.getUserid());
// 四.执行并返回结果集
count = pstmt.executeUpdate();// 执行dml 或 ddl语句的 返回受影响的行数
if (count >= 1) {
System.out.println("删除用户成功!");
} else {
System.out.println("没有删除任何用户!");
}
} catch (SQLException e) {
System.out.println("建立通道失败");
e.printStackTrace();
throw new Exception("删除用户失败");
} finally {
// 5.关闭
ConnOracle.closeConnection(null, pstmt, conn);
}
return count;
}
// 三.修改
public int updateUser(User user) throws Exception{
int count;
String sql = "update user1 set cname=?,cdesc=? where cid=?";
PreparedStatement pstmt = null;
// 三.建立通道
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, user.getUsername());
pstmt.setString(2, user.getPassword());
pstmt.setInt(3, user.getUserid());
// 四.执行并返回结果集
count = pstmt.executeUpdate();// 执行dml 或 ddl语句的 返回受影响的行数
if (count >= 1) {
System.out.println("修改用户成功!");
} else {
System.out.println("没有修改任何用户!");
}
} catch (SQLException e) {
System.out.println("建立通道失败");
e.printStackTrace();
throw new Exception("修改用户失败");
} finally {
// 5.关闭
ConnOracle.closeConnection(null, pstmt, conn);
}
return count;
}
// 四.查1
public User getUserById(Integer userid) throws Exception{
User user = new User();
String sql = "select * from user where userid=?";
PreparedStatement pstmt = null;
ResultSet rs = null;
// 三.建立通道
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, userid);
// 四.执行并返回结果集
// ResultSet 结果集 封装了 数据库查询的结果集
rs = pstmt.executeQuery();
while (rs.next()) {
user.setUserid(rs.getInt(1));
user.setUsername(rs.getString(2));
user.setPassword(rs.getString(3));
}
} catch (SQLException e) {
System.out.println("建立通道失败");
e.printStackTrace();
throw new Exception("查询单个用户失败");
} finally {
// 五.关闭
ConnOracle.closeConnection(rs, pstmt, conn);
}
return user;
}
// 五.按SQL语句查
public List<User> getPageByQuery(String sql) throws Exception{
Statement stmt = null;
ResultSet rs = null;
List<User> list = new ArrayList<User>();
User user = null;
// 三.建立通道
try {
stmt = conn.createStatement();
// 四.执行并返回结果集
rs = stmt.executeQuery(sql);
while (rs.next()) {
user = new User();
user.setUserid(rs.getInt("userid"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setIsActive(rs.getInt("isActive"));
user.setNickname(rs.getString("nickname"));
user.setAddress(rs.getString("address"));
list.add(user);
}
} catch (SQLException e) {
System.out.println("建立通道失败!");
e.printStackTrace();
throw new Exception("查询用户失败");
} finally {
// 五.关闭
ConnOracle.closeConnection(rs, stmt, conn);
}
return list;
}
public int getTotalRecordSum(String sql) throws Exception{
int totalRecordSum = 0;
PreparedStatement pstmt = null;
ResultSet rs = null;
// 三.建立通道
try {
pstmt = conn.prepareStatement(sql);
// 四.执行并返回结果集
// ResultSet 结果集 封装了 数据库查询的结果集
rs = pstmt.executeQuery();
while (rs.next()) {
totalRecordSum = rs.getInt(1);
}
} catch (SQLException e) {
System.out.println("建立通道失败");
e.printStackTrace();
throw new Exception("查询用户数量失败");
} finally {
// 五.关闭
ConnOracle.closeConnection(rs, pstmt, conn);
}
return totalRecordSum;
}
}
4.servlet
public class UserServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String action = request.getParameter("action");
if("login".equals(action)){
this.login(request,response);
}else if("logout".equals(action)){
this.logout(request,response);
}
}
public void login(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String target = "";
//一.填充数据
String username = request.getParameter("username");
String password = request.getParameter("password");
//二.调用业务逻辑
try {
UserServiceImpl service = new UserServiceImpl();
User user = service.login(username, password);
if(user!=null){//根据用户名和密码找到了该用户
Integer isActive = user.getIsActive();
if(isActive==1){
//登录成功 跳到京东首页 firstPage
HttpSession session = request.getSession(true);
session.setAttribute("user", user);//把user对象存到session中 以后每个页面中都可以取出来使用
target = "/WEB-INF/jsp/user/welcome.jsp";
}else{
//登录失败 跳回登录页面 显示 "用户尚未激活,请激活后再尝试登录"
request.setAttribute("msg", "用户尚未激活,请激活后再尝试登录");
target = "/jsp/user/login.jsp";
}
}else{
//登录失败 跳回登录页面 显示 "用户名或密码错误"
request.setAttribute("msg", "用户名或密码错误,请重新输入");
target = "/jsp/user/login.jsp";
}
} catch (Exception e) {
target = "/WEB-INF/msg.jsp";
request.setAttribute("msg","登录失败 请回到登录页面从新登录");
e.printStackTrace();
}
//三.转发视图
request.getRequestDispatcher(target).forward(request, response);
}
public void logout(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String target = "";
//一.填充数据
//二.调用业务逻辑
//退出登录 注销session即可
HttpSession session = request.getSession(true);
session.invalidate();
//三.转发视图
target = "/jsp/user/login.jsp";
request.getRequestDispatcher(target).forward(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doGet(request, response);
}
}
5.用户服务端接口:service
public interface UserService {
public User login(String username,String password) throws Exception;
}
6.用户服务端接口实现类:serviceImpl
public class UserServiceImpl implements UserService{
private UserDaoImpl dao = new UserDaoImpl();
public User login(String username, String password) throws Exception{
User user = null;
String sql = "select * from user1 where username='" + username+ "' and password='" + password + "'";
List<User> list = dao.getPageByQuery(sql);
if(list.size()>0){
//用户存在
user = list.get(0);
}
return user;
}
}
7.数据库连接
public class ConnOracle {
public static Connection getConnection(){
Connection conn = null;
String className = "oracle.jdbc.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "111";
//1.加载驱动
try {
Class.forName(className);
} catch (ClassNotFoundException e) {
System.out.println("驱动类没有找到");
e.printStackTrace();
}
//2.创建连接
try {
conn = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
System.out.println("连接数据库失败");
e.printStackTrace();
}
return conn;
}
public static void closeConnection(ResultSet rs,Statement stmt,Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
System.out.println("关闭通道失败");
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
System.out.println("关闭数据库连接失败");
e.printStackTrace();
}
}
}
public static void main(String[] args) {
System.out.println(ConnOracle.getConnection());
}
}
综上:jsp页面非常简单,只需要两个文本框和按钮即可实现简单的登录功能,想要实现界面美观又实现功能的登录界面可参考本文代码以及查看各大电商网站源码