package com.book.dao;

import com.book.model.User;
import com.book.util.StringUtil;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class UserDao {
	
	public User login(Connection con,User user)throws Exception {
		User resultUser = null;
		String sql = "select * from user where username=? and password=? and role = ?";
	    PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
		pstmt.setString(1,user.getUserName());
		pstmt.setString(2,user.getPassword());
		pstmt.setInt(3,user.getRole());
	    ResultSet rs = pstmt.executeQuery();
	    if(rs.next()){
	    	resultUser = new User();
	    	resultUser.setUserId(rs.getInt("id"));
	    	resultUser.setUserName(rs.getString("username"));
	    }
		return resultUser;
	}
	
	public int addUser(Connection con,User user) throws Exception{
		//查询username是否存在
		String sql = "select * from user where userName=? ";
	    PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
		pstmt.setString(1,user.getUserName());
	    ResultSet rs = pstmt.executeQuery();
	    if(rs.next()){
	    	return 2;
	    }
	    
	    sql="insert into user (username,password,role) values (?,?,?)";
	    PreparedStatement pstmt2=(PreparedStatement) con.prepareStatement(sql);
		pstmt.setString(1, user.getUserName());
		pstmt.setString(2, user.getPassword());
		pstmt.setInt(3, user.getRole());
		return pstmt.executeUpdate();
	}
	
	
	public ResultSet list(Connection con,User user)throws Exception{
		StringBuffer sb=new StringBuffer("select * from user where role = 1");
		if(!StringUtil.isEmpty(user.getUserName())){
			sb.append(" and username like '%"+user.getUserName()+"%'");
		}
		PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sb.toString());
		return pstmt.executeQuery();
	}
	
	public int update(Connection con,User user)throws Exception{
		String sql="update user set username=?,password=? where id=?";
		PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sql);
		pstmt.setString(1, user.getUserName());
		pstmt.setString(2, user.getPassword());
		pstmt.setInt(3, user.getUserId());
		return pstmt.executeUpdate();
	}
}