package com.kjlm.beans;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

public class MasterMgr {
	Connection conn = null;

	Statement stmt = null;

	ResultSet rs = null;

	public int getCount() {
		int count = 0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost/news?user=root&password=3819568";
			conn = DriverManager.getConnection(url);
			stmt = conn.createStatement();
			String sql = "select count(*) from master";
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return count;
	}

	public Collection getAllMaster() {
		Collection<Master> c = new ArrayList<Master>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost/news?user=root&password=3819568";
			conn = DriverManager.getConnection(url);
			stmt = conn.createStatement();
			String sql = "select * from master";
			ResultSet rs = stmt.executeQuery(sql);
			Master master = null;
			while (rs.next()) {
				master = new Master();
				master.setId(rs.getInt("id"));
				master.setUsername(rs.getString("username"));
				master.setPassword(rs.getString("password"));
//				System.out.println(master.getUsername());
				c.add(master);
				master = null;
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return c;
	}
	public Collection getAllMaster(int page,int pageSize, int pageTotalNum) {
		Collection<Master> c = new ArrayList<Master>();

		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost/news?user=root&password=3819568";
			conn = DriverManager.getConnection(url);
			stmt = conn.createStatement();
			if(page>pageTotalNum){
				page =pageTotalNum;
			}
			String sql = "select * from master limit " +(page-1)*pageSize +"," + pageSize;
			ResultSet rs = stmt.executeQuery(sql);
			Master master = null;
			while (rs.next()) {
				master = new Master();
				master.setId(rs.getInt("id"));
				master.setUsername(rs.getString("username"));
				master.setPassword(rs.getString("password"));
//				System.out.println(master.getUsername());
				c.add(master);
				master = null;
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return c;
	}

	public Boolean exist(Master master) {
		// TODO Auto-generated method stub
		Boolean flag = false;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost/news?user=root&password=3819568";
			conn = DriverManager.getConnection(url);
			stmt = conn.createStatement();
			String userName = master.getUsername();
			String sql = "select * from master where username ='" + userName
					+ "'";
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				flag = true;
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return flag;
	}

	public void insert(Master master) {
		// TODO Auto-generated method stub
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost/news?user=root&password=3819568";
			conn = DriverManager.getConnection(url);
			stmt = conn.createStatement();
			String userName = master.getUsername();
			String password = master.getPassword();
			String mgrCategory = master.getMgrCategory();
			
			String sql = "insert into master values(null,'" + userName + "','"
					+ password + "','" + mgrCategory + "',now() )";
			stmt.executeUpdate(sql);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	
	public Master getAllById(int id) {
		Master master = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost/news?user=root&password=3819568";
			conn = DriverManager.getConnection(url);
			stmt = conn.createStatement();
			String sql = "select * from master where id =" +id;
			ResultSet rs = stmt.executeQuery(sql);
		
			while (rs.next()) {
				master = new Master();
				master.setId(rs.getInt("id"));
				master.setUsername(rs.getString("username"));
				master.setPassword(rs.getString("password"));
				master.setMgrCategory(rs.getString("mgrcategory"));
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return master;

	}

	public void modify(Master master) {
		// TODO Auto-generated method stub
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost/news?user=root&password=3819568";
			conn = DriverManager.getConnection(url);
			stmt = conn.createStatement();
			int id = master.getId();
			String userName = master.getUsername();
			String password = master.getPassword();
			String mgrCategory = master.getMgrCategory();
			
			String sql = "update master set username='" + userName + "',password ='"
					+ password + "',mgrCategory='" + mgrCategory + "' where id="+id;
			stmt.executeUpdate(sql);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	
	public void deleteMasterById(int id) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost/news?user=root&password=3819568";
			conn = DriverManager.getConnection(url);
			stmt = conn.createStatement();
			String sql = "delete from master where id =" +id;
			stmt.executeUpdate(sql);
		
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}

	}

}