图书购买系统之登录注页面--查询数据库进行登录

package com.chinasoft.dao;

import java.sql.SQLException;

import com.chinasoft.entity.User;

public interface UserDao {
	//添加一个用户
	public void addUser(User user) throws SQLException;
	//根据激活码查询指定用户
	public User findUserByActiveCode(String activeCode) throws SQLException;
	//根据激活码跟新用户的激活状态
	public void updateActiveStateByActiveCode(String activeCode) throws SQLException;
	//根据用户名和密码查询指定用户
	public User findUserByUserNameAndPassword(String username, String password) throws SQLException;
	//根据id查询指定用户
	public User findUserById(String id) throws SQLException;
	//修改指定用户信息
	public User modifyUser(User user) throws SQLException;
	//根据用户名查询数据库中该用户是否存在
	public Boolean findUserByName(String name) throws SQLException;
	//根据email查询数据库该用户是否存在
	public Boolean findUserByEmail(String email) throws SQLException;
}

5.ImplUserDao.java,接口实现类

package com.chinasoft.dao.impl;

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

import org.apache.commons.dbutils.QueryRunner;

import com.chinasoft.dao.UserDao;
import com.chinasoft.entity.User;
import com.chinasoft.util.C3P0Util;
import com.chinasoft.util.JdbcUtil;



public class ImplUserDao implements UserDao{

	/**
	 * 添加一个用户
	 */
	@Override
	public void addUser(User user) throws SQLException {
		//1.先导入的数据库驱动程序
		//2.获取数据库的连接对象
		Connection conn = C3P0Util.getConnection();
		//3.创建一个是stmtment对象,写sql语句
		String sql = "INSERT INTO USER(NAME,pass,gender,email,phone,introduce) VALUES(?,?,?,?,?,?)";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, user.getName());
		pstmt.setString(2, user.getPass());
		pstmt.setString(3, user.getGender());
		pstmt.setString(4, user.getEmail());
		pstmt.setString(5, user.getPhone());
		pstmt.setString(6, user.getIntroduce());
		//4.执行sql语句
		pstmt.executeUpdate();
		//5.关闭资源
		C3P0Util.release(conn, pstmt, null);
	}

	/**
	 * 根据激活码查询指定用户
	 */
	@Override
	public User findUserByActiveCode(String activeCode) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * 根据激活码跟新用户的激活状态
	 */
	@Override
	public void updateActiveStateByActiveCode(String activeCode) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		qr.update("update user set activeState = ? where activeCode = ?", "1",activeCode);
	}
	

	/**
	 * 根据用户名和密码查询指定用户
	 */
	@Override
	public User findUserByUserNameAndPassword(String username, String password) throws SQLException {
		// 1.先导入的数据库驱动程序
		// 2.获取数据库的连接对象
		Connection conn = JdbcUtil.getConnection();
		// 3.创建一个是stmtment对象,写sql语句
		String sql = "select * from user where name = ? and pass = ?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, username);
		pstmt.setString(2, password);
		// 4.执行sql语句
		ResultSet rs = pstmt.executeQuery();
		// 5.取出查询的数据
		User user = null;
		if (rs.next()) {
			user = new User();
			user.setId(rs.getInt(1));
			user.setName(rs.getString(2));
			user.setPass(rs.getString(3));
			user.setGender(rs.getString(4));
			user.setEmail(rs.getString(5));
			user.setPhone(rs.getString(6));
			user.setIntroduce(rs.getString(7));
			user.setActiveCode(rs.getString(8));
			user.setActiveState(rs.getInt(9));
			user.setRole(rs.getString(10));
			user.setRegistTime(rs.getDate(11));
		}
		// 5.关闭资源
		JdbcUtil.CloseAll(conn, pstmt, null);
		return user;
	}

	
	/**
	 * 根据id查询指定用户
	 */
	@Override
	public User findUserById(String id) throws SQLException {
		// 1.先导入的数据库驱动程序
				// 2.获取数据库的连接对象
				Connection conn = C3P0Util.getConnection();
				// 3.创建一个是stmtment对象,写sql语句
				String sql = "select * from user where id = ?";
				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, id);
				// 4.执行sql语句
				ResultSet rs = pstmt.executeQuery();
				// 5.取出查询的数据
				User user = new User();
				if (rs.next()) {
					user.setId(rs.getInt(1));
					user.setName(rs.getString(2));
					user.setPass(rs.getString(3));
					user.setGender(rs.getString(4));
					user.setEmail(rs.getString(5));
					user.setPhone(rs.getString(6));
					user.setIntroduce(rs.getString(7));
					user.setActiveCode(rs.getString(8));
					user.setActiveState(rs.getInt(9));
					user.setRole(rs.getString(10));
					user.setRegistTime(rs.getDate(11));
				}
				// 5.关闭资源
				C3P0Util.release(conn, pstmt, rs);
				return user;
	}

	/**
	 * 修改指定用户信息
	 */
	@Override
	public User modifyUser(User user) throws SQLException {
		// 1.先导入的数据库驱动程序
		// 2.获取数据库的连接对象
		Connection conn = JdbcUtil.getConnection();
		// 3.创建一个是stmtment对象,写sql语句
		String sql = "update user set pass =?, gender=?,phone = ? where id = ?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, user.getPass());
		pstmt.setString(2, user.getGender());
		pstmt.setString(3, user.getPhone());
		pstmt.setInt(4, user.getId());
		// 4.执行sql语句
		pstmt.executeUpdate();
		// 5.关闭资源
		JdbcUtil.CloseAll(conn, pstmt, null);
		// 6.返回修改后的用户
		User user2 = findUserById(Integer.toString(user.getId()));
		return user2;
	}

	/**
	 * 根据用户名查询数据库中该用户是否存在
	 */
	@Override
	public Boolean findUserByName(String name) throws SQLException {
		// 1.先导入的数据库驱动程序
		// 2.获取数据库的连接对象
		Connection conn = JdbcUtil.getConnection();
		// 3.创建一个是stmtment对象,写sql语句
		String sql = "select * from user where name = ?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, name);
		// 4.执行sql语句
		ResultSet rs = pstmt.executeQuery();
		// 5.取出查询的数据
		User user = null;
		if (rs.next()) {
			user = new User();
			user.setId(rs.getInt(1));
			user.setName(rs.getString(2));
			user.setPass(rs.getString(3));
			user.setGender(rs.getString(4));
			user.setEmail(rs.getString(5));
			user.setPhone(rs.getString(6));
			user.setIntroduce(rs.getString(7));
			user.setActiveCode(rs.getString(8));
			user.setActiveState(rs.getInt(9));
			user.setRole(rs.getString(10));
			user.setRegistTime(rs.getDate(11));
		}
		// 5.关闭资源
		JdbcUtil.CloseAll(conn, pstmt, null);
		if(user!=null){
			return true;
		}
		else{
			return false;
		}
	}

	/**
	 * 根据email查询数据库该用户是否存在
	 */
	@Override
	public Boolean findUserByEmail(String email) throws SQLException {
		// 1.先导入的数据库驱动程序
		// 2.获取数据库的连接对象
		Connection conn = JdbcUtil.getConnection();
		// 3.创建一个是stmtment对象,写sql语句
		String sql = "select * from user where email = ?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, email);
		// 4.执行sql语句
		ResultSet rs = pstmt.executeQuery();
		// 5.取出查询的数据
		User user = null;
		if (rs.next()) {
			user = new User();
			user.setId(rs.getInt(1));
			user.setName(rs.getString(2));
			user.setPass(rs.getString(3));
			user.setGender(rs.getString(4));
			user.setEmail(rs.getString(5));
			user.setPhone(rs.getString(6));
			user.setIntroduce(rs.getString(7));
			user.setActiveCode(rs.getString(8));
			user.setActiveState(rs.getInt(9));
			user.setRole(rs.getString(10));
			user.setRegistTime(rs.getDate(11));
		}
		// 5.关闭资源
		JdbcUtil.CloseAll(conn, pstmt, null);
		if(user!=null){
			return true;
		}
		else{
			return false;
		}
	}
}