cardado.java

辅助办公系统,具有发布公告、站内邮箱、日程安排、日志查看等功能

package com.x3408.card;

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

import com.x3408.database.CNProvider;
import com.x3408.employees.UserInfo;

public class CardADO {
	public static boolean cardInsert(CardInfo cardInfo){
		Connection conn=null;
		PreparedStatement pstat=null;
		if(!cardInfo.isValid()||!UserInfo.employeeIDVaild(cardInfo.getOwnerID())){
			return false;
		}
		conn=CNProvider.getConnection();
		if(conn==null){
			return false;
		}
		try {
			pstat=conn.prepareStatement("insert card(ownerID,employeeID,introduce,name,sex,birthday,postCode,email,mobile,phone,college,department,position,holder)" +
					"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			pstat.setString(1, cardInfo.getOwnerID());
			pstat.setString(2, cardInfo.getEmployeeID());
			pstat.setString(3, cardInfo.getIntroduce());
			pstat.setString(4, cardInfo.getName());
			pstat.setString(5, cardInfo.getSex());
			pstat.setString(6, cardInfo.getBirthday());
			pstat.setString(7, cardInfo.getPostCode());
			pstat.setString(8, cardInfo.getEmail());
			pstat.setString(9, cardInfo.getMobile());
			pstat.setString(10, cardInfo.getPhone());
			pstat.setString(11, cardInfo.getCollege());
			pstat.setString(12, cardInfo.getDepartment());
			pstat.setString(13, cardInfo.getPosition());
			pstat.setBoolean(14, cardInfo.getHolder());
			pstat.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(pstat,conn);
		}
		return false;
	}
	public static boolean cardUpdate(CardInfo cardInfo){
		Connection conn=null;
		PreparedStatement pstat=null;
		if(!cardInfo.isValid()||cardInfo.getCardID()==0){
			return false;
		}
		conn=CNProvider.getConnection();
		if(conn==null){
			return false;
		}
		try {
			pstat=conn.prepareStatement("update card set employeeID=?,Introduce=?,name=?,sex=?,birthday=?,postCode=?," +
					"email=?,mobile=?,phone=?,college=?,department=?,position=? where cardID=?");
			pstat.setString(1, cardInfo.getEmployeeID());
			pstat.setString(2, cardInfo.getIntroduce());
			pstat.setString(3, cardInfo.getName());
			pstat.setString(4, cardInfo.getSex());
			pstat.setString(5, cardInfo.getBirthday());
			pstat.setString(6, cardInfo.getPostCode());
			pstat.setString(7, cardInfo.getEmail());
			pstat.setString(8, cardInfo.getMobile());
			pstat.setString(9, cardInfo.getPhone());
			pstat.setString(10, cardInfo.getCollege());
			pstat.setString(11, cardInfo.getDepartment());
			pstat.setString(12, cardInfo.getPosition());
			pstat.setInt(13, cardInfo.getCardID());
			pstat.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(pstat,conn);
		}
		return false;
	}
	public static boolean cardDelete(String cardID){
		Connection conn=null;
		PreparedStatement pstat=null;
		if(cardID==null||"".equals(cardID.trim())){
			return false;
		}
		conn=CNProvider.getConnection();
		try {
			pstat=conn.prepareStatement("delete from card where cardID=?");
			pstat.setString(1, cardID.trim());
			pstat.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(pstat,conn);
		}
		return false;
	}
	public static Vector allUserCardQuery(String employeeID){
		Connection conn=null;
		PreparedStatement pstat=null;
		ResultSet rs=null;
		Vector<CardInfo> cardList=null;
		CardInfo cardInfo=null;
		if(!UserInfo.employeeIDVaild(employeeID)){
			return null;
		}
		conn=CNProvider.getConnection();
		if(conn==null){
			return null;
		}
		cardList=new Vector<CardInfo>();
		try {
			pstat=conn.prepareStatement("select * from card where ownerID=?");
			pstat.setString(1, employeeID.trim());
			rs=pstat.executeQuery();
			while(rs.next()){
				cardInfo=new CardInfo(rs.getString("ownerID"),rs.getString("employeeID"),rs.getString("cardID"),rs.getString("introduce"),
						rs.getString("name"),rs.getString("sex"),rs.getString("birthday"),rs.getString("age"),
						rs.getString("postCode"),rs.getString("email"),rs.getString("mobile"),rs.getString("phone"),
						rs.getString("college"),rs.getString("department"),rs.getString("position"),rs.getString("holder"));
				cardList.addElement(cardInfo);
			}
			return cardList.size()<1?null:cardList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(rs,pstat,conn);
		}
		return null;
	}
	public static CardInfo cardQuery(String cardID){
		Connection conn=null;
		PreparedStatement pstat=null;
		ResultSet rs=null;
		CardInfo cardInfo=null;
		if(cardID==null||"".equals(cardID.trim())){
			return null;
		}
		conn=CNProvider.getConnection();
		if(conn==null){
			return null;
		}
		try {
			pstat=conn.prepareStatement("select * from card where cardID=?");
			pstat.setString(1, cardID.trim());
			rs=pstat.executeQuery();
			if(rs.next()){
				cardInfo=new CardInfo(rs.getString("ownerID"),rs.getString("employeeID"),rs.getString("cardID"),rs.getString("introduce"),
						rs.getString("name"),rs.getString("sex"),rs.getString("birthday"),rs.getString("age"),
						rs.getString("postCode"),rs.getString("email"),rs.getString("mobile"),rs.getString("phone"),
						rs.getString("college"),rs.getString("department"),rs.getString("position"),rs.getString("holder"));
			}
			return cardInfo;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(rs,pstat,conn);
		}
		return null;
	}
	public static CardInfo baseCardQuery(String employeeID){
		Connection conn=null;
		PreparedStatement pstat=null;
		ResultSet rs=null;
		CardInfo cardInfo=null;
		if(!UserInfo.employeeIDVaild(employeeID)){
			return null;
		}
		conn=CNProvider.getConnection();
		if(conn==null){
			return null;
		}
		try {
			pstat=conn.prepareStatement("select employeeID,name,sex,birthday,year(getDate())-year(birthday) as age,mobile,phone,department,position " +
					"from employees where employeeID=?");
			pstat.setString(1, employeeID.trim());
			rs=pstat.executeQuery();
			if(rs.next()){
				cardInfo=new CardInfo(rs.getString("employeeID"),rs.getString("name"),rs.getString("sex"),rs.getString("birthday"),rs.getString("age"),
						rs.getString("mobile"),rs.getString("phone"),rs.getString("department"),rs.getString("position"));
			}
			return cardInfo;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(rs,pstat,conn);
		}
		return null;
	}
	public static Vector detailCardQuery(){
		Connection conn=null;
		PreparedStatement pstat=null;
		ResultSet rs=null;
		Vector<CardInfo> cardList=null;
		CardInfo cardInfo=null;
		conn=CNProvider.getConnection();
		if(conn==null){
			return null;
		}
		cardList=new Vector<CardInfo>();
		try {
			pstat = conn
					.prepareStatement("SELECT ownerID,employeeID,cardID,name,sex,birthday,age,mobile,phone,department,position,holder FROM card WHERE holder = 1");
			rs=pstat.executeQuery();
			while(rs.next()){
				cardInfo=new CardInfo(rs.getString("ownerID"),rs.getString("employeeID"),rs.getString("cardID"),rs.getString("name"),rs.getString("sex"),
						rs.getString("birthday"),rs.getString("age"),rs.getString("mobile"),rs.getString("phone"),rs.getString("department"),
						rs.getString("position"),rs.getString("holder"));
				cardList.addElement(cardInfo);
			}
			return cardList.size()<1?null:cardList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(rs,pstat,conn);
		}
		return null;
	}
	public static Vector baseCardQuery(){
		Connection conn=null;
		PreparedStatement pstat=null;
		ResultSet rs=null;
		Vector<CardInfo> cardList=null;
		CardInfo cardInfo=null;
		conn=CNProvider.getConnection();
		if(conn==null){
			return null;
		}
		cardList=new Vector<CardInfo>();
		try {
			pstat=conn.prepareStatement("SELECT employeeID,name,sex,birthday,year(getDate())-year(birthday) as age,mobile,phone,department,position " +
					"FROM employees WHERE NOT EXISTS (SELECT * FROM card WHERE card.employeeID = employees.employeeID AND card.holder = 1)");
			rs=pstat.executeQuery();
			while(rs.next()){
				cardInfo=new CardInfo(rs.getString("employeeID"),rs.getString("name"),rs.getString("sex"),rs.getString("birthday"),rs.getString("age"),
						rs.getString("mobile"),rs.getString("phone"),rs.getString("department"),rs.getString("position"));
				cardList.addElement(cardInfo);
			}
			return cardList.size()<1?null:cardList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(rs,pstat,conn);
		}
		return null;
	}
}