package org.mgq.dao;

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

import org.mgq.model.Student;
import org.mgq.util.DBstudent;

public class StudentImpl implements StudentInterface{
	
	DBstudent db=new DBstudent();
	Connection conn=db.getConnection();
	
	
	public List<Student> queryByPage(int pageSize,int pageNow){
		String sql="SELECT * FROM(SELECT A.*, ROWNUM RN FROM" +
		"(SELECT * FROM student) A WHERE ROWNUM <= ?)" +
		" WHERE RN >= ?";
		List<Student> list=new ArrayList<Student>();
		try {
			if(conn!=null&&pageSize>0&&pageNow>0){
				PreparedStatement pstmt = conn.prepareStatement(sql);
				
				pstmt.setInt(1, pageNow*pageSize);
				pstmt.setInt(2, pageSize*(pageNow-1)+1);
				
				ResultSet rs=pstmt.executeQuery();
				
				Student student = null;
				while(rs.next()){
					
					student = new Student();
					
					student.setId(rs.getInt("id"));
					student.setName(rs.getString("name"));
					student.setAge(rs.getInt("age"));
					
					list.add(student);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	
	public Student query(int count,int page){
		int i=5;
		String sql="select count(*) from student";
		Student student=new Student();
		try {
			PreparedStatement pstmt=conn.prepareStatement(sql);
			ResultSet rs=pstmt.executeQuery();

			while(rs.next()){
				count=rs.getInt(1);
			}
			if(count%i!=0){
				page=count/i+1;
			}else{
				page=count/i;
			}
			student.setCount(count);
			student.setPage(page);
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return student;
	}

}