用jdbc模拟验证用户登录功能

1.导入3个jar包，并build to path

commons-dbcp-1.2.1.jar

commons-pool.jar

mysql-connector-java-5.1.40-bin.jar

2.创建db.properties放在src目录下，要不然加载时找不到位置

jdbc.url=jdbc:mysql://localhost:3306/gjxfj_user?useSSL=true
jdbc.username=root
jdbc.password=root
jdbc.initialSize=20
jdbc.maxActive=100
jdbc.maxWait=5000

3.创建三个包并在每个包下创建类

com.jdbc.util（JdbcUtil.java）

package com.gjxfj.jdbc;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

public class JdbcUtil {
	private static BasicDataSource dataSource = null;
	private JdbcUtil(){}
	static{
		dataSource = new BasicDataSource();
		Properties prop = new Properties();
		try {
			prop.load(JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties"));
			String url = prop.getProperty("jdbc.url");
			String username = prop.getProperty("jdbc.username");
			String password = prop.getProperty("jdbc.password");
			String initialSizeStr = prop.getProperty("jdbc.initialSize"); 
			int initialSize = Integer.parseInt(initialSizeStr);
			String maxActiveStr = prop.getProperty("jdbc.maxActive");
			int maxActive = Integer.parseInt(maxActiveStr);
			String maxWaitStr = prop.getProperty("jdbc.maxWait");
			int maxWait = Integer.parseInt(maxWaitStr);
			try {
				Class.forName("com.mysql.jdbc.Driver");
				dataSource.setUrl(url);
				dataSource.setUsername(username);
				dataSource.setPassword(password);
				dataSource.setInitialSize(initialSize);
				dataSource.setMaxActive(maxActive);
				dataSource.setMaxWait(maxWait);	
			} catch (ClassNotFoundException e) {				
				e.printStackTrace();
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	public static Connection getConn() throws SQLException{
		return dataSource.getConnection();
	}
	public static void close(Connection conn){
		if (conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}	
}

com.jdbc.entity（User.java）

package com.jdbc.entity;

import java.io.Serializable;
import java.sql.Timestamp;
/**
 * 项目中实体类的开发
 * 一个实体数据表对应一个实体类
 * 1、定义一组private属性
 * 2、定义一组对应属性的setter和getter
 *    特殊的属性：只读属性
 * 3、定义一组构造器
 * 4、重写hashCode(),equals(),toString()
 * 5、实现Serializable接口
 *    Serializable可序列化标识接口
 */
public class User implements Serializable{

	private static final long serialVersionUID = 1L;
	private Integer id;
	private String username;
	private String password;
	private String email;
	private String phone;
	private Timestamp created;
	private Timestamp updated;
	public Integer getId() {
		return id;
	}
	public void setId(Integer 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 String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	//返回系统当前时间
	public Timestamp getCreated() {
		return new Timestamp(System.currentTimeMillis());
	}
	//返回系统当前时间
	public Timestamp getUpdated() {
		return new Timestamp(System.currentTimeMillis());
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", password=" + password + ", email=" + email + ", phone="
				+ phone + ", created=" + created + ", updated=" + updated + "]";
	}
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((id == null) ? 0 : id.hashCode());
		return result;
	}
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		User other = (User) obj;
		if (id == null) {
			if (other.id != null)
				return false;
		} else if (!id.equals(other.id))
			return false;
		return true;
	}
	public User(Integer id, String username, String password, String email, String phone, Timestamp created,
			Timestamp updated) {
		super();
		this.id = id;
		this.username = username;
		this.password = password;
		this.email = email;
		this.phone = phone;
		this.created = created;
		this.updated = updated;
	}
	public User() {
		super();
	}
	public User(Integer id, String username, String password, String email, String phone) {
		super();
		this.id = id;
		this.username = username;
		this.password = password;
		this.email = email;
		this.phone = phone;
	}
	
}

com.jdbc.dao.impl（UserDaoImpl.java）

package com.jdbc.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.jdbc.entity.User;
import com.jdbc.util.JdbcUtil;

public class UserDaoImpl {
	/*
	 * 用户注册
	 * 将用户的信息保存到数据库表中
	 * 面向对象
	 *   方法的设计
	 *   * 方法的参数 方法中参与运算的不确定的数据
	 *   * 方法返回值类型 
	 *     方法执行结束是否有运算结果，如果有，返回值
	 *     类型即是结果的类型,如果没有结果void
	 * 定义一个类，封装用户信息
	 * User user = new User();
	 * user.setUserName(userName);
	 * save(user);
	 */
	/**
	 * 保存账号信息
	 * @param user 账号信息
	 * @return int 添加记录数
	 */
	public int save(User user){
		String sql = "insert into gjxfj(username,password,email,phone,created,updated) values(?,?,?,?,?,?)";
		Connection conn = null;
		int result = 0;
		try {
			conn = JdbcUtil.getConn();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getUsername());
			pstmt.setString(2, user.getPassword());
			pstmt.setString(3, user.getEmail());
			pstmt.setString(4, user.getPhone());
			pstmt.setTimestamp(5, user.getCreated());
			pstmt.setTimestamp(6, user.getUpdated());
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn);
		}
		return result;
		
	}
	/**
	 * 根据账号查询 验证账号是否被占用
	 * @param userName 账号
	 * @return boolean 是否存在
	 */
	public boolean findByUsername(String username){
		boolean isUsed = false;
		Connection conn = null;
		try {
			conn = JdbcUtil.getConn();
			String sql = "select username,password,email,phone from gjxfj_user where username = ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, username);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				isUsed = true;
			}		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn);
		}
		return isUsed;
	}
	/**
	 * 根据邮箱查询 验证邮箱是否已经注册
	 * @param email 邮箱
	 * @return boolean 是否已经注册
	 */
	public boolean findByEmail(String email){
		boolean isUsed = false;
		Connection conn = null;
		String sql = "select username,password,email,phone from gjxfj_user where email = ?";
		try {
			conn = JdbcUtil.getConn();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, email);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				isUsed = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn);
		}
		return isUsed;
	}
	/**
	 * 根据手机号查询 验证手机号是否已经注册
	 * @param phone 手机号
	 * @return boolean 是否已经注册
	 */
	public boolean findByPhone(String phone){
		boolean isUsed = false;
		String sql = "select username,password,email,phone from gjxfj_user where phone = ?";
		Connection conn = null;
		try {
			conn = JdbcUtil.getConn();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				isUsed = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn);
		}
		return isUsed;
		
	}
	/**
	 * 用户登录
	 * 根据用户的账号和密码查询数据
	 * @param userName 账号
	 * @param password 密码
	 * @return User 登录用户的账号信息
	 */
	public User findByUser(String username,String password){
		User user = null;
		Connection conn = null;
		String sql = "select username,password,email,phone from gjxfj_user "
				+ "where username = ? and password = ?";
		try {
			conn = JdbcUtil.getConn();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, password);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				user.setPhone(rs.getString("phone"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn);
		}
		return user;
	}
}