The NoteBook of EricKong

1．定義接口

（1）Subject接口

Subject.java

package com.leanhuadeng.DesignPattern.Observer;

public interface Subject {
    public void registerObserver(Observer o);
    public void removeObserver(Observer o);
    public void notifyObservers();
}

 
（2）Observer接口

Observer.java
package com.leanhuadeng.DesignPattern.Observer;

public interface Observer {
    public void update(float temp,float humidity,float pressure);
}
 
（3）Displayment接口

Displayment.java

package com.leanhuadeng.DesignPattern.Observer;

public interface Displayment {
    public void display();
}

2．實現接口

（1）WeatherData

WeatherData.java

package com.leanhuadeng.DesignPattern.Observer;

import java.util.ArrayList;

public class WeatherData implements Subject {
    private ArrayList observers;
    private float temperature;
    private float humidity;
    private float pressure;
   
    public WeatherData(){
        observers=new ArrayList();
    }
   
    public void notifyObservers() {
        for(int i=0;i<observers.size();i++){
            Observer observer=(Observer)observers.get(i);
            observer.update(temperature, humidity, pressure);
        }
    }

    public void registerObserver(Observer o) {
        observers.add(o);
    }

    public void removeObserver(Observer o) {
        int i=observers.indexOf(o);
        if(i>=0){
            observers.remove(i);
        }
    }
    public void measurementsChanged(){
        notifyObservers();
    }
    public void setMeasurements(float temperature,float humidity,float pressure){
        this.temperature=temperature;
        this.humidity=humidity;
        this.pressure=pressure;
        measurementsChanged();
    }
}

 
（2）CurrentConditionsDisplay

CurrentConditionsDisplay.java

package com.leanhuadeng.DesignPattern.Observer;

public class CurrentConditionsDisplay implements Observer, Displayment {
    private float temperature;
    private float humidity;
    private Subject weatherData;
   
    public CurrentConditionsDisplay(Subject weatherData){
        this.weatherData=weatherData;
        weatherData.registerObserver(this);
    }
   
    public void update(float temp, float humidity, float pressure) {
        this.temperature=temp;
        this.humidity=humidity;
        display();
    }

    public void display() {
        System.out.println("Current conditions:"+temperature+"F degrees and "+humidity+"% humidity");
    }

}

 
（3）StatisticsDisplay

StatisticsDisplay.java

package com.leanhuadeng.DesignPattern.Observer;

import java.util.*;

public class StatisticsDisplay implements Observer, Displayment {
    private float maxTemp = 0.0f;
    private float minTemp = 200;
    private float tempSum= 0.0f;
    private int numReadings;
    private WeatherData weatherData;

    public StatisticsDisplay(WeatherData weatherData) {
        this.weatherData = weatherData;
        weatherData.registerObserver(this);
    }

    public void update(float temp, float humidity, float pressure) {
        tempSum += temp;
        numReadings++;

        if (temp > maxTemp) {
            maxTemp = temp;
        }
 
        if (temp < minTemp) {
            minTemp = temp;
        }

        display();
    }

    public void display() {
        System.out.println("Avg/Max/Min temperature = " + (tempSum / numReadings)+ "/" + maxTemp + "/" + minTemp);
    }
}

 
（4）ForecastDisplay

ForecastDisplay.java

package com.leanhuadeng.DesignPattern.Observer;

import java.util.*;

public class ForecastDisplay implements Observer, Displayment {
    private float currentPressure = 29.92f; 
    private float lastPressure;
    private WeatherData weatherData;

    public ForecastDisplay(WeatherData weatherData) {
        this.weatherData = weatherData;
        weatherData.registerObserver(this);
    }

    public void update(float temp, float humidity, float pressure) {
                lastPressure = currentPressure;
        currentPressure = pressure;

        display();
    }

    public void display() {
        System.out.print("Forecast: ");
        if (currentPressure > lastPressure) {
            System.out.println("Improving weather on the way!");
        } else if (currentPressure == lastPressure) {
            System.out.println("More of the same");
        } else if (currentPressure < lastPressure) {
            System.out.println("Watch out for cooler, rainy weather");
        }
    }
}

 
3．實現氣象站

WeatherStation.java

package com.leanhuadeng.DesignPattern.Observer;

 package com.leanhuadeng.DesignPattern.Decorator;

/*
 * Beverage是一個抽象類,有兩個方法
 */
public abstract class Beverage {
        public String description="Unknown Beverage";
       
        /*
         * getDescription()已經在此實現了,但是cost()必須在子類中實現
         */
        public String getDescription() {
            return description;
        }
       
        public abstract double cost();
}

三、代碼實現
 
(2)調料抽象類CondimentDecorator
CondimentDecorator.java
package com.leanhuadeng.DesignPattern.Decorator;
/*
 * 首先,必須讓CondimentDecorator能夠取代Beverage,所以將CondimentDecorator擴展自Beverage類
 */
public abstract class CondimentDecorator extends Beverage {

    //所有的調料裝飾者都必須重新實現getDescription()方法.
    public abstract String getDescription();
}
 
2．飲料實現

(1)Espresso

Espresso.java

package com.leanhuadeng.DesignPattern.Decorator;

/**//*
 * 首先,必須讓CondimentDecorator能夠取代Beverage,所以將CondimentDecorator擴展自Beverage類
 */
public abstract class CondimentDecorator extends Beverage {

    //所有的調料裝飾者都必須重新實現getDescription()方法.
    public abstract String getDescription();
}

 
(2)HouseBlend

HouseBlend.java

package com.leanhuadeng.DesignPattern.Decorator.drink;

import com.leanhuadeng.DesignPattern.Decorator.Beverage;

public class HouseBlend extends Beverage {
   
   
    public HouseBlend() {
        description="House Blend Coffee";
    }

    @Override
    public double cost() {
        return 0.89;
    }

}
 
(3)DarkRoast

DarkRoast.java

package com.leanhuadeng.DesignPattern.Decorator.drink;

import com.leanhuadeng.DesignPattern.Decorator.Beverage;

public class DarkRoast extends Beverage {
   
   
    public DarkRoast() {
        description="Dark Roast Coffee";
    }

    @Override
    public double cost() {
        return 0.99;
    }
}
 
(4)Decaf

Decaf.java

package com.leanhuadeng.DesignPattern.Decorator.drink;

import com.leanhuadeng.DesignPattern.Decorator.Beverage;

public class Decaf extends Beverage {
   
   
    public Decaf() {
        description="Decaf Coffee";
    }

    @Override
    public double cost() {
        return 1.05;
    }
}

 
3．調料實現

(1)Mocha

Mocha.java

package com.leanhuadeng.DesignPattern.Decorator.condiment;

import com.leanhuadeng.DesignPattern.Decorator.Beverage;
import com.leanhuadeng.DesignPattern.Decorator.CondimentDecorator;

public class Mocha extends CondimentDecorator {
    /*
     * 要讓Mocha能夠引用一個Beverage,做法如下:一是用一個實例變量記錄飲料,也就是被裝飾者.
     * 二是想辦法讓裝飾者(飲料)記錄到實例變量中,即把飲料當作構造器的參數,再由構造器將此飲料記錄在實例變量中
     */
    Beverage beverage;

    public Mocha(Beverage beverage) {
        this.beverage = beverage;
    }

    @Override
    public String getDescription() {
        /*
         * 我們希望敘述不只是描述飲料,而是完整的連調料都描述出來
         */
        return beverage.getDescription()+",Mocha";
    }
           
    @Override
    public double cost() {
        /*
         * 要計算帶Mocha飲料的價錢,首先把調用委托給裝飾對象,以計算價錢,然后再加上Mocha的價錢,得到最后結果
         */
        return 0.20+beverage.cost();
    }
}

 
(2)Soy

Soy.java

package com.leanhuadeng.DesignPattern.Decorator.condiment;

import com.leanhuadeng.DesignPattern.Decorator.Beverage;
import com.leanhuadeng.DesignPattern.Decorator.CondimentDecorator;

public class Soy extends CondimentDecorator {
    Beverage beverage;

    public Soy(Beverage beverage) {
        this.beverage = beverage;
    }

    public String getDescription() {
        return beverage.getDescription() + ", Soy";
    }

    public double cost() {
        return .15 + beverage.cost();
    }
}

 
(3)Whip

Whip.java

package com.leanhuadeng.DesignPattern.Decorator.condiment;

import com.leanhuadeng.DesignPattern.Decorator.Beverage;
import com.leanhuadeng.DesignPattern.Decorator.CondimentDecorator;
 
public class Whip extends CondimentDecorator {
    Beverage beverage;
 
    public Whip(Beverage beverage) {
        this.beverage = beverage;
    }
 
    public String getDescription() {
        return beverage.getDescription() + ", Whip";
    }
 
    public double cost() {
        return .10 + beverage.cost();
    }
}

 
4．測試類StarbuzzCoffee

StarbuzzCoffee.java

package com.leanhuadeng.DesignPattern.Decorator;

import com.leanhuadeng.DesignPattern.Decorator.condiment.Mocha;
import com.leanhuadeng.DesignPattern.Decorator.condiment.Soy;
import com.leanhuadeng.DesignPattern.Decorator.condiment.Whip;
import com.leanhuadeng.DesignPattern.Decorator.drink.DarkRoast;
import com.leanhuadeng.DesignPattern.Decorator.drink.Espresso;
import com.leanhuadeng.DesignPattern.Decorator.drink.HouseBlend;

public class StarbuzzCoffee {
    public static void main(String args[]){
        /*
         * 訂一杯Espresso,不需要調料,打印出它的描述和價錢.
         */
        Beverage beverage=new Espresso();
        System.out.println(beverage.getDescription()+" $"+beverage.cost());
       
        /*
         * 制造一個DarkRoast對象,用Mocha,Whip裝飾它
         */
        Beverage beverage2=new DarkRoast();
        beverage2=new Mocha(beverage2);
        beverage2=new Mocha(beverage2);
        beverage2=new Whip(beverage2);
        System.out.println(beverage2.getDescription()+" $"+beverage2.cost());   
       
        /*
         * 最后,再來一杯調料為豆漿,摩卡\奶泡的HouseBlend咖啡
         */
        Beverage beverage3=new HouseBlend();
        beverage3=new Soy(beverage3);
        beverage3=new Mocha(beverage3);
        beverage3=new Whip(beverage3);
        System.out.println(beverage3.getDescription()+" $"+beverage3.cost());   
    }
}

對取包含字段時用到了FOR XML PATH這個功能，可以方便的把包含列組織成A,B,C的形式。然后使用CROSS APPLY得出最終的結果。腳本定義如下：

USE AdventureWorks;
GO
DROP INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader
GO
CREATE INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader(CustomerID)
INCLUDE(ShipDate,Freight)
GO

--sp_helpindex不能反應出包含字段
EXEC sp_helpindex 'Sales.SalesOrderHeader'
GO
--SQL2005下用于診斷索引重復的腳本
DECLARE @Result TABLE(
    objname            sysname            NOT NULL,
    objtype            char(2)            NOT NULL,
    indexname        sysname            NOT NULL,
    index_id        int                NOT NULL,
    indextype        tinyint            NOT NULL,
    is_primary_key  bit                NOT NULL,
    is_unique        bit                NOT NULL,
    fill_factor        tinyint            NOT NULL,
    IndexKeys        nvarchar(2126)    NOT NULL,
    Included        nvarchar(max)    NULL,
    rows            bigint            NOT NULL,
    IndexDesc        varchar(210)    NULL
)

CREATE TABLE #IndexInfo
(
    IndexName    sysname         NOT NULL,
    IndexDesc    varchar(210)     NULL,
    IndexKeys    nvarchar(2126)     NULL
)
DECLARE @objname     sysname

DECLARE ObjectList CURSOR FAST_FORWARD FOR
    SELECT SCHEMA_NAME(o.schema_id)+'.'+o.name AS objname
    FROM sys.indexes i JOIN sys.objects o ON i.object_id=o.object_id
    WHERE o.type IN('U','V') AND i.index_id IN(0,1)
        --AND o.object_id=OBJECT_ID(N'Sales.SalesOrderHeader')

OPEN ObjectList
FETCH NEXT FROM ObjectList INTO @objname

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #IndexInfo EXEC sp_helpindex @objname--使用全名稱，防止直接使用表名稱時無法獲取其它架構表的信息
   
    INSERT INTO @Result
     SELECT SCHEMA_NAME(o.schema_id)+'.'+o.name AS objname, o.type AS objtype,
         i.name AS indexname,i.index_id,i.type AS indextype,i.is_primary_key,i.is_unique,i.fill_factor,
        t.IndexKeys,
        c.name AS Included,
        p.rows,t.IndexDesc
    FROM sys.indexes i
         INNER JOIN sys.objects o ON i.object_id=o.object_id
         INNER JOIN #IndexInfo t ON t.IndexName=i.name
         CROSS APPLY (SELECT SUM(rows) AS rows
                         FROM sys.partitions p
                         WHERE p.index_id = i.index_id AND p.object_id = i.object_id
                     ) p
         CROSS APPLY (SELECT name=STUFF((SELECT N',' + QUOTENAME(y) AS [text()]
                       FROM (SELECT c.name AS y
                                FROM sys.index_columns ic
                                    JOIN sys.columns c ON ic.column_id=c.column_id AND ic.object_id=c.object_id
                                WHERE ic.object_id=i.object_id AND ic.index_id=i.index_id  AND ic.is_included_column=1
                            ) AS Y
                       ORDER BY y FOR XML PATH('')), 1, 1, N'')
                     ) c
    WHERE o.object_id=OBJECT_ID(@objname)
           
    TRUNCATE TABLE #IndexInfo
   
  FETCH NEXT FROM ObjectList INTO @objname
END

CLOSE ObjectList
DEALLOCATE ObjectList

DROP TABLE #IndexInfo
SELECT * FROM @Result ORDER BY objname,index_id
用于SQL2000的腳本：

--SQL2000下用于診斷索引重復的腳本
DECLARE @Result TABLE (
    [objname] [sysname] NOT NULL ,
    [indexname] [sysname] NOT NULL ,
    [indid] [smallint] NOT NULL ,
    [IsUnique] [int] NOT NULL ,
    [IndexKeys] [nvarchar] (2126) NOT NULL ,
    [rowcnt] [bigint] NOT NULL ,
    [rowmodctr] [int] NOT NULL ,
    [keycnt] [smallint] NOT NULL ,
    [OrigFillFactor] [tinyint] NOT NULL ,
    [dpages] [int] NOT NULL ,
    [IndexDesc] [varchar] (210) NULL
)

CREATE TABLE #IndexInfo
(
    IndexName    sysname     NOT NULL,
    IndexDesc    varchar(210)     NULL,
    IndexKeys    nvarchar(2126)     NULL
)
DECLARE @objname     sysname,
        @objid        int

DECLARE ObjectList CURSOR FAST_FORWARD FOR
    SELECT USER_NAME(o.uid)+'.'+o.name AS objname,o.id AS objid
    FROM dbo.sysobjects o JOIN dbo.sysindexes i ON i.id = o.id
    WHERE o.type IN( 'U','V') AND i.indid IN(0,1) AND o.name<>'dtproperties'--用于保存關系圖的系統表
    ORDER BY o.name,o.uid

OPEN ObjectList
FETCH NEXT FROM ObjectList INTO @objname,@objid

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #IndexInfo EXEC sp_helpindex @objname--使用全名稱，防止直接使用表名稱時無法獲取其它用戶表的信息
   
    INSERT INTO @Result
     SELECT USER_NAME(o.uid)+'.'+o.name AS objname, i.name AS indexname, i.indid,
        CASE WHEN t.IndexDesc LIKE '%unique%' THEN 1 ELSE 0 END AS IsUnique,
        t.IndexKeys, i.rowcnt, i.rowmodctr, i.keycnt, i.OrigFillFactor, i.dpages,t.IndexDesc
    FROM dbo.sysindexes i
         INNER JOIN dbo.sysobjects o ON i.id = o.id
         INNER JOIN #IndexInfo t ON t.IndexName=i.name
    WHERE o.id=@objid
    TRUNCATE TABLE #IndexInfo
   
  FETCH NEXT FROM ObjectList INTO @objname,@objid
END

CLOSE ObjectList
DEALLOCATE ObjectList