package edu.njust.cs;
import java.sql.*;
import javax.swing.*;
import java.util.*;
import java.io.*;
public class SqlUtil{
	//讀入配置文件
	public static Properties loadProperty(String fileName){
		Properties prop=new Properties();
		try{			
			FileInputStream in=new FileInputStream(System.getProperties().get("user.dir")+"/"+fileName);
			prop.load(in);	
			in.close();			
		}catch(IOException e){
			e.printStackTrace();
			JOptionPane.showMessageDialog(null,
										  "配置文件丟失!\n建議重新安裝程序",
										  "信息",
										  JOptionPane.ERROR_MESSAGE);
			prop=null;
		}
		return prop;
	}	
	//建立和MS SQL SERVER的連接
	public static Connection acquireConnection(String host,String port,String dbName,
											   String user,String pwd) throws ClassNotFoundException,SQLException{
		Connection connection=null;
		try{
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");	
			String url ="jdbc:microsoft:sqlserver://"+
						host+":"+port+";User="+user+";Password="+pwd; 
			connection= DriverManager.getConnection(url);
			connection.setCatalog(dbName);
		}catch(ClassNotFoundException e){				
			e.printStackTrace();
			throw e;
		}catch(SQLException e){				
			e.printStackTrace();
			throw e;
		}	
		return connection;
	}
	//使用指定的SQL語句和數據,向數據庫插入一條記錄
	public static boolean addRowToDB(Connection con,String insertSql,Object []lineForDBAdd){
		boolean flag=true;
		PreparedStatement update=null;		
		try{
			update=con.prepareStatement(insertSql);
			if(lineForDBAdd!=null)
				for(int i=0;i<lineForDBAdd.length;i++)
					update.setObject(i+1,lineForDBAdd[i]);				
			update.executeUpdate();
		}catch(SQLException e){		
			e.printStackTrace();
			flag=false;
			JOptionPane.showMessageDialog(null,
										  "從數據庫插入數據時發生錯誤"+e,
										  "信息",
										  JOptionPane.ERROR_MESSAGE);
		}finally{
			if(update!=null)
				try{
					update.close();
				}catch(SQLException ex){
					ex.printStackTrace();
				}
		}
		return flag;
	}
	//將適合數據庫存儲的一行數據(LineForDB)轉化為適合表格顯示的數據(LineForTable)
	//依據數據類型的不同，將代表空值的特殊值轉化為空值
	public static Object []  getLineForTableFromLineForDB(Object []lineForDB,Class []dataType)
	{
		Object []lineForTable=new Object[lineForDB.length];
		for(int i=0;i<lineForDB.length;i++){
			 if(dataType[i]==java.lang.String.class)				 
				 lineForTable[i]=lineForDB[i];
			 else if(dataType[i]==java.lang.Double.class){				
				 if(((Double)lineForDB[i]).doubleValue()==Double.NEGATIVE_INFINITY)
					 lineForTable[i]=null;
				 else
					 lineForTable[i]=lineForDB[i];
			 }
			 else if(dataType[i]==java.lang.Integer.class){				
				 if(((Integer)lineForDB[i]).doubleValue()==Integer.MIN_VALUE)
					 lineForTable[i]=null;
				 else
					 lineForTable[i]=lineForDB[i];
			 }
			 else if(dataType[i]==Boolean.class){
				 if(((String)lineForDB[i]).equals("Y"))
					 lineForTable[i]=new Boolean(true);
				 else
					 lineForTable[i]=new Boolean(false);
			 }
			  else if(dataType[i]==Timestamp.class){
				 Timestamp t=((Timestamp)lineForDB[i]);
				 Timestamp b=DateUtil.getTSFromYMD("1900-01-01");
				 if(t.equals(b))
					 lineForTable[i]=null;
				 else
					 lineForTable[i]=t;
			 }
		}
		return lineForTable;
	}
	//清空表格中所有的數據
	public static void clearAllRowsInTable(CustomTableModel model){
		while(model.getRowCount()>0)
			model.removeRow(0);
	}
	//從數據庫讀取數據到表格, dataType指明表格中每一列的數據類型
	//當前只考慮了String, Double ,Integer,Boolean ,Timestamp類型.
	public static void readDBToTable(Connection con,String readSql,CustomTableModel model, Class []dataType){
		clearAllRowsInTable(model);
		PreparedStatement query=null;
		 try{			
			 query= con.prepareStatement(readSql);
			 query.clearParameters();
			 ResultSet rs=query.executeQuery();
			 while(rs.next()){	
				 int column=model.getColumnCount();	
				 Object []line=new Object[column];
				 for(int i=0;i<column;i++){		
					 if(dataType[i]==java.lang.String.class)
						line[i]=rs.getString(i+1).trim();
					 else if(dataType[i]==java.lang.Double.class){
						 if(rs.getDouble(i+1)==Double.NEGATIVE_INFINITY)
						   line[i]=null;
						 else
						   line[i]=new Double(rs.getDouble(i+1));
					 }
					 else if(dataType[i]==java.lang.Integer.class){
						if(rs.getInt(i+1)==Integer.MIN_VALUE)
						   line[i]=null;
						else
						   line[i]=new Integer(rs.getInt(i+1));
					 }
					  else if(dataType[i] ==Boolean.class){
						 if(rs.getString(i+1).trim().equals("Y"))
							 line[i]=new Boolean(true);
						 else
							 line[i]=new Boolean(false);
					 }
					 else if(dataType[i]==Timestamp.class){
						 Timestamp t=rs.getTimestamp(i+1);
						 Timestamp b=DateUtil.getTSFromYMD("1900-01-01");
						 if(t.equals(b))	 line[i]=null;
						 else	 line[i]=t;
					 }
				 }
				 model.addRow(line);
			 }
		 }catch(SQLException e){
			 e.printStackTrace();
			 JOptionPane.showMessageDialog(null,
										   "從數據庫讀取數據時發生錯誤!"+
										   "SQL語句為:"+readSql+e,"提示",
										   JOptionPane.ERROR_MESSAGE);					
		 }finally{
			if(query!=null)
				try{
					query.close();
				}catch(SQLException ex){
					ex.printStackTrace();
				}
		}	
	}	
	//使用指定的SQL語句刪除數據庫中的記錄
	public static boolean deleteFromDB(Connection con,String deleteSql,Object []keys){
		boolean flag=true;
		PreparedStatement update=null;
		try{
			update=con.prepareStatement(deleteSql);			
			if(keys!=null)
				for(int i=0;i<keys.length;i++)
					update.setObject(i+1,keys[i]);	
			update.executeUpdate();
		}catch(SQLException e){	
			flag=false;
	       JOptionPane.showMessageDialog(null,
										 "從數據庫刪除時發生錯誤!\n"+e,
										 "提示",
										 JOptionPane.ERROR_MESSAGE);		

		}finally{
			if(update!=null)
				try{
					update.close();
				}catch(SQLException ex){
					ex.printStackTrace();
				}
		}	
		return flag;
	}
	//記錄在數據庫中是否已經存在
	public static boolean isRecordExist(Connection con,String querySql,Object []keys){
		boolean exist=false;
		PreparedStatement query=null;
		try{
			query= con.prepareStatement(querySql);
			query.clearParameters();
			for(int i=0;i<keys.length;i++)
				query.setObject(i+1,keys[i]);
			ResultSet rs=query.executeQuery();			
			if(rs.next())
				exist=true;
		}catch(SQLException e){
			e.printStackTrace();
			JOptionPane.showMessageDialog(null,
										  "判斷記錄是否存在時發生錯誤!\n"+e,
										  "提示",
										  JOptionPane.ERROR_MESSAGE);		
		}finally{
			if(query!=null)
				try{
					query.close();
				}catch(SQLException ex){
					ex.printStackTrace();
				}
		}	
		return exist;
	}
	//使用指定的SQL語句和數據,向數據庫更新一條記錄
	public static boolean  updateRowInDB(Connection con,String updateSql,Object []lineForDBUpdate){
		boolean flag=true;
		PreparedStatement update=null;
		try{
			update=con.prepareStatement(updateSql);
			for(int i=0;i<lineForDBUpdate.length;i++)
				update.setObject(i+1,lineForDBUpdate[i]);
			update.executeUpdate();
		}catch(SQLException e){	
			e.printStackTrace();
			flag=false;
			JOptionPane.showMessageDialog(null,
										  "更新數據庫時發生錯誤!\n"+e,
										  "提示",
										  JOptionPane.ERROR_MESSAGE);						
			
		}finally{
			if(update!=null)
				try{
					update.close();
				}catch(SQLException ex){
					ex.printStackTrace();
				}
		}	
		return flag;
	}	
	//從數據表中讀取指定字段的值
	//注意：如果SQL語句中選擇了多個字段，則取第一個字段的值
	//如果有多條記錄滿足SQL語句，則取第一條記錄
	//也就是說，返回值為滿足SQL語句的第一條記錄的第一個字段的值
	//沒有符合SQL的記錄則返回null;
	public static Object readFieldValueFromDB(Connection con,String readSql)	{
		Object result=null;
		 PreparedStatement query=null;
		 try{			
			 query= con.prepareStatement(readSql);
			 query.clearParameters();
			 ResultSet rs=query.executeQuery();
			 if(rs.next())
				 result=rs.getObject(1);			 
			 query.close();
		 }catch(SQLException e){
			 e.printStackTrace();
			 result=null;
			 JOptionPane.showMessageDialog(null,
										   "從數據庫讀取指定字段時發生錯誤!\n"+e,
										   "提示",
										   JOptionPane.ERROR_MESSAGE);		
		 }finally{
			if(query!=null)
				try{
					query.close();
				}catch(SQLException ex){
					ex.printStackTrace();
				}
		}			
		 return result;
	}
}