HoneyHoney

@Autowired annotation is picked up only if you use  or

http://stackoverflow.com/questions/7414794/difference-between-contextannotation-config-vs-contextcomponent-scan

package com.xxx;
public class B {
  public B() {
    System.out.println("creating bean B: " + this);
  }
}

package com.xxx;
public class C {
  public C() {
    System.out.println("creating bean C: " + this);
  }
}

package com.yyy;
import com.xxx.B;
import com.xxx.C;
public class A { 
  private B bbb;
  private C ccc;
  public A() {
    System.out.println("creating bean A: " + this);
  }
  public void setBbb(B bbb) {
    System.out.println("setting A.bbb with " + bbb);
    this.bbb = bbb;
  }
  public void setCcc(C ccc) {
    System.out.println("setting A.ccc with " + ccc);
    this.ccc = ccc; 
  }
}

<bean id="bBean" class="com.xxx.B" />
<bean id="cBean" class="com.xxx.C" />
<bean id="aBean" class="com.yyy.A">
  <property name="bbb" ref="bBean" />
  <property name="ccc" ref="cBean" />
</bean>

creating bean B: com.xxx.B@c2ff5
creating bean C: com.xxx.C@1e8a1f6
creating bean A: com.yyy.A@1e152c5
setting A.bbb with com.xxx.B@c2ff5
setting A.ccc with com.xxx.C@1e8a1f6

package com.yyy;
import org.springframework.beans.factory.annotation.Autowired;
import com.xxx.B;
import com.xxx.C;
public class A { 
  private B bbb;
  private C ccc;
  public A() {
    System.out.println("creating bean A: " + this);
  }
  @Autowired
  public void setBbb(B bbb) {
    System.out.println("setting A.bbb with " + bbb);
    this.bbb = bbb;
  }
  @Autowired
  public void setCcc(C ccc) {
    System.out.println("setting A.ccc with " + ccc);
    this.ccc = ccc;
  }
}

<property name="bbb" ref="bBean" />
<property name="ccc" ref="cBean" />

<bean id="bBean" class="com.xxx.B" />
<bean id="cBean" class="com.xxx.C" />
<bean id="aBean" class="com.yyy.A" />

creating bean B: com.xxx.B@5e5a50
creating bean C: com.xxx.C@54a328
creating bean A: com.yyy.A@a3d4cf

<context:annotation-config />
<bean id="bBean" class="com.xxx.B" />
<bean id="cBean" class="com.xxx.C" />
<bean id="aBean" class="com.yyy.A" />

creating bean B: com.xxx.B@15663a2
creating bean C: com.xxx.C@cd5f8b
creating bean A: com.yyy.A@157aa53
setting A.bbb with com.xxx.B@15663a2
setting A.ccc with com.xxx.C@cd5f8b

package com.xxx;
import org.springframework.stereotype.Component;
@Component
public class B {
  public B() {
    System.out.println("creating bean B: " + this);
  }
}

package com.xxx;
import org.springframework.stereotype.Component;
@Component
public class C {
  public C() {
    System.out.println("creating bean C: " + this);
  }
}

package com.yyy;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import com.xxx.B;
import com.xxx.C;
@Component
public class A { 
  private B bbb;
  private C ccc;
  public A() {
    System.out.println("creating bean A: " + this);
  }
  @Autowired
  public void setBbb(B bbb) {
    System.out.println("setting A.bbb with " + bbb);
    this.bbb = bbb;
  }
  @Autowired
  public void setCcc(C ccc) {
    System.out.println("setting A.ccc with " + ccc);
    this.ccc = ccc;
  }
}

<context:annotation-config />

<context:component-scan base-package="com.xxx" />

creating bean B: com.xxx.B@1be0f0a
creating bean C: com.xxx.C@80d1ff

<context:component-scan base-package="com.xxx,com.yyy" />

creating bean B: com.xxx.B@cd5f8b
creating bean C: com.xxx.C@15ac3c9
creating bean A: com.yyy.A@ec4a87
setting A.bbb with com.xxx.B@cd5f8b
setting A.ccc with com.xxx.C@15ac3c9

<context:component-scan base-package="com.xxx" />
<bean id="aBean" class="com.yyy.A" />

creating bean B: com.xxx.B@157aa53
creating bean C: com.xxx.C@ec4a87
creating bean A: com.yyy.A@1d64c37
setting A.bbb with com.xxx.B@157aa53
setting A.ccc with com.xxx.C@ec4a87

<context:annotation-config />
<context:component-scan base-package="com.xxx" />
<bean id="aBean" class="com.yyy.A" />

creating bean B: com.xxx.B@157aa53
creating bean C: com.xxx.C@ec4a87
creating bean A: com.yyy.A@1d64c37
setting A.bbb with com.xxx.B@157aa53
setting A.ccc with com.xxx.C@ec4a87

<context:annotation-config />
<context:component-scan base-package="com.xxx" />
<bean id="aBean" class="com.yyy.A" />
<bean id="bla" class="org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor" />
<bean id="bla1" class="org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor" />
<bean id="bla2" class="org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor" />
<bean id="bla3" class="org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor" />

creating bean B: com.xxx.B@157aa53
creating bean C: com.xxx.C@ec4a87
creating bean A: com.yyy.A@25d2b2
setting A.bbb with com.xxx.B@157aa53
setting A.ccc with com.xxx.C@ec4a87

参考: https://www.simple-talk.com/sql/learn-sql-server/the-xml-methods-in-sql-server/

包括value(), query(), exists(), nodes()

use adventureworks2008r2

--IF OBJECT_ID('XML_TEST') IS NOT NULL
--    DROP TABLE XML_TEST

--GO

--CREATE TABLE XML_TEST (
--    ID INT IDENTITY(1, 1),
--    NAME VARCHAR(20),
--    DATA XML,
--CONSTRAINT PK_XML_TEST PRIMARY KEY CLUSTERED (ID)
--)


--INSERT INTO XML_TEST (NAME, DATA)
--VALUES
--('ZhOnG', '<storesurvey>
--    <annualsales>700000</annualsales>
--    <annualrevenue>70000</annualrevenue>
--    <bankname>United Phoenix</bankname>
--    <businesstype>BM</businesstype>
--    <yearopened>2016</yearopened>
--    <specialty>Mountain</specialty>
--    <squarefeet>21000</squarefeet>
--    <brands>2</brands>
--    <internet>ISDN</internet>
--    <numberemployees>13</numberemployees>
--    <products type="Swimming">
--      <product>Mountain</product>
--      <product>Road</product>
--      <product>Racing</product>
--    </products>
--    <products type="Endurance">
--      <product>Jerseys</product>
--      <product>Jackets</product>
--      <product>Shorts</product>
--    </products>
--  </storesurvey>')


SELECT *,
    data.query('/StoreSurvey/AnnualRevenue'),
    data.value('(/StoreSurvey/AnnualRevenue)[1]', 'INT'),
    data.value('(/StoreSurvey/Products/@Type)[2]', 'varchar(10)'),

    data.query('/StoreSurvey/child::node()'),
    data.exist('/StoreSurvey[BusinessType="BM"]'),
    data.query('/StoreSurvey/Products'),
    data.query('/StoreSurvey/Products[@Type="Bikes"]')
FROM XML_TEST


SELECT Bike.Product.query('./text()') AS BikeTypes,
        Bike.Product.value('local-name(.)[1]', 'varchar(100)')
FROM XML_TEST
CROSS APPLY DATA.nodes('/StoreSurvey/Products[@Type="Bikes"]/Product') AS Bike(Product);


DECLARE @bikes XML
SET @bikes =
  '<products>
    <product>Mountain</product>
    <product>Road</product>
    <product>Racing</product>
   </products>'

SELECT
  --Category, -- you could not use this directly
  Category.query('./text()') AS BikeTypes
FROM
  @bikes.nodes('/Products/Product') AS Bike(Category);

在余额宝出来之前，对于普通老百姓来说，大家的投资渠道还是只知道股票，银行，和房子。而中国的股票，由于其特殊性，更加像是一个赌场。低买高卖，击鼓传花，为了谋求更多的利益，可能倾家荡产，只为博一次大涨。

在《一沙一世界》里面提到，放在银行里面的存款年利率是3.6%，抛去通胀2%（暂时按照这个计），投资回报率只有可怜的1.6%。而房子能够带来的投资回报率，大约算下来也就3%。在投资渠道匮乏的情况下，更多的资金会选择购房，进而推高房价。

在余额宝出来之后，大部分的年轻人有了更多的投资理财渠道。各种银行宝宝，招财宝，炒外汇，炒黄金，炒基金，股票，等等。年轻人的思维的改变，在现在体现的还不是很明显，但当这一代人占领社会的主要地位的时候，以前所谓中国人的高存款率可能要打折扣了。可能，那个时候央行身上的压力更大了。

无论如何，正是由于余额宝的出现，促使了中国金融的巨变。至少对于我来说，我会一直寻找更多的投资渠道，而不会一直放在银行里面了。

引用 http://sqlblog.com/blogs/hugo_kornelis/archive/2014/10/19/43466.aspx 

创建一个测试表

CREATE TABLE dbo.LargeTable 
(KeyVal int NOT NULL PRIMARY KEY, 
DataVal int NOT NULL CHECK (DataVal BETWEEN 1 AND 10) 
);

WITH Digits 
AS (SELECT d FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(d)) 
INSERT INTO dbo.LargeTable (KeyVal, DataVal) 
SELECT 10000 * tt.d + 1000 * st.d 
+ 100 * h.d + 10 * t.d + s.d + 1, 
10 * RAND(CHECKSUM(NEWID())) + 1 
FROM   Digits AS s,  Digits AS t,  Digits AS h, 
Digits AS st, Digits AS tt;

创建三个function

--Scalar function
CREATE FUNCTION dbo.Triple(@Input int) 
RETURNS int 
AS 
BEGIN; 
DECLARE @Result int; 
SET @Result = @Input * 3; 
RETURN @Result; 
END; 
go 


--inline TVF
CREATE FUNCTION dbo.TripleV2(@Input int)
RETURNS TABLE
AS
RETURN
SELECT 3*@Input AS amt
GO


--Multlple-Line TVF
CREATE FUNCTION dbo.TripleV3(@Input int) 
RETURNS @Result TABLE(amt INT)
AS 
BEGIN; 
INSERT INTO @Result(amt) 
SELECT @Input * 3; 
RETURN; 
END; 
go 

直接用SQL Sentry Plan Explorer开测

SELECT MAX(dbo.Triple(DataVal)) AS MaxTriple 
FROM   dbo.LargeTable;

SELECT MAX(t.amt)
FROM dbo.LargeTable lt
CROSS APPLY dbo.TripleV3(lt.DataVal) t

SELECT MAX(t.amt)
FROM dbo.LargeTable lt
CROSS APPLY dbo.TripleV2(lt.DataVal) t

直接上结果

1. 最糟糕的是Multiple-Line Table Value Function，一次都要10秒。最主要还是要执行100000次。
2. 次之的是Scalar Value Function。一次大概1.6秒的样子。其实每个function也要执行100000次。
3. 最好的是Inline Table Value Function。无论CPU还是执行时间，都非常得好。

最近在玩基金，主要玩了一下债基，混合基。本来胆子就小，想着找一个收益能够比余额宝高的理财就好了。谁知道这一玩突然发现了另外一个世界。庆幸这个不是毒品，在自己还没有完全进去的时候，选择适当退出。

到现在，自己还没有完全解套。打算回本就安心玩债基了。按照原来书本介绍的，大家买股票是觉得这个企业能够盈利，试图去分上一杯羹，比如股息和分红。但是人们后来发现，这个来钱还不够快，相反，采取低买高卖的方法可以快速聚集财富。与低买高卖相关的另外一个词就是“炒”。

原本只要一块的股票，当大量的资金涌入的时候，被炒成2块，3块，4块...10块。等到了最高点的时候，突然间，所有人都抛了。最后一个以10块钱买入的人直接被套牢了。等到自己需要用钱，或者忍痛割肉的时候，他就把自己的股票贱卖了。又会有一批人以后买到便宜货，买入，股价继续跌，更多的人被套牢，或者割肉。赚钱的，是前面那波人，亏的，是后面那波割肉的。财富并没有增加，只是简单的转移了。

换个角度看，这个其实就是在赌博。而背后的操作股价的人是最后的赢家，他却不在创造任何社会价值的情况下，剥削了他人的财富。这只是一个“合法”的抢劫！

问题

有时候，SQL SERVER 会抛两个error message出来，比如







这个时候，如果你用ERROR_MESSAGE()，你只会拿到最后一条error message。那如何拿到所有的error message呢？

解决

Paulo Santos 创建了一个SP来实现这件事。http://pjondevelopment.50webs.com/articles/retrieveErrorMessage.htm 。我拿来以后稍稍改了一下里面的bug，现在就可以捕获所有的error message了。

IF NOT EXISTS(SELECT
                *
              FROM sys.procedures
              WHERE name = 'usp_GET_LastErrorMessage')
    EXEC ('
    CREATE PROCEDURE dbo.usp_GET_LastErrorMessage
    AS 
        DECLARE @error_message NVARCHAR(2000)
        SET @error_message = ''Stored procedure ''+OBJECT_NAME(@@PROCID)+'' not yet implemented'' 
        RAISERROR(@error_message, 16, 1)
    ')
GO

ALTER PROCEDURE dbo.usp_GET_LastErrorMessage
    @output_result BIT = 1
AS
BEGIN
  DECLARE 
    @dbccrow CHAR(77),
    @byte INT,
    @pos INT, -- position in buffer line
    @numMsg INT, -- number of error messages
    @gather INT, -- error message piece
    @count INT, -- byte position within a collection of bytes
    @messageLength INT,
    @stringLength INT,
    @nchar INT,
    @errNumber BIGINT,
    @errState INT,
    @errLevel INT,
    @errMessage NVARCHAR(1000),
    @errInstance NVARCHAR(256),
    @errProcedure NVARCHAR(256),
    @errLine INT,
    @bytesRead INT;

/*
Buffer sample
--------------------------------------------------
00000000   04 01 00 c8 00 37 01 00 aa 30 00 50 c3 00 00 01   ...È.7..ª0.PÃ...
00000010   10 0c 00 73 00 69 00 6d 00 70 00 6c 00 65 00 20   ...s.i.m.p.l.e. 
00000020   00 65 00 72 00 72 00 6f 00 72 00 0b 46 00 4e 00   .e.r.r.o.r..F.N.
00000030   44 00 44 00 45 00 56 00 4f 00 52 00 41 00 30 00   D.D.E.V.O.R.A.0.
00000040   31 00 00 02 00 00 00 fd 03 00 f6 00 00 00 00 00   1......ý..ö.....

Buffer description
--------------------------------------------------
0 - We need to scan the buffer for the byte marker 0xAA that starts an error message.
1 - Size of the entire error message (2 bytes)
2 - Error Number (4 bytes)
3 - Error State (1 byte)
4 - Error Level (1 byte)
5 - Size of the text, in character, of the error message (2 bytes)
6 - Variable length of bytes with the Unicode error message
7 - Size of the name of SQL Server instance (1 byte)
8 - Variable length of bytes with the Unicode name of the SQL Server instance
9 - Size of the name of the stored procedure where the error occurred (1 byte)
10 - Variable length of bytes with the Unicode name stored procedure
11 - Line where the error occurred (2 bytes)


Buffer sample as it will be parsed
--------------------------------------------------
___________________________________0#_1####_2##########_3#
00000000   04 01 00 c8 00 37 01 00 aa 3c 00 50 c3 00 00 01   ...È.7..ª0.PÃ...
___________4#_5####_6#####################################
00000010   10 0c 00 73 00 69 00 6d 00 70 00 6c 00 65 00 20   ...s.i.m.p.l.e. 
___________################################_7#_8##########
00000020   00 65 00 72 00 72 00 6f 00 72 00 0b 46 00 4e 00   .e.r.r.o.r..F.N.
___________###############################################
00000030   44 00 44 00 45 00 56 00 4f 00 52 00 41 00 30 00   D.D.E.V.O.R.A.0.
___________##_9#_10_11###_________________________________
00000040   31 00 00 02 00 00 00 fd 03 00 f6 00 00 00 00 00   1......ý..ö.....


1 - 0X003c = 60
2 - 0X0000C350 = 50000
3 - 0X01 = 1
4 - 0X10 = 16
5 - 0X000C = 12
6 - "simple error"
7 - 0X0B = 11
8 - "LOCALHOST"
9 - 0X0
10 - 0X0
11 - 0X0002 = 2


The problem with this approach is that if the buffer contains any user data it might have the marker byte and thus provoking a false response.
This has been mitigated by comparing the message bytes and the bytes read for the message.

*/

  -- Catch the output buffer.
  CREATE TABLE #OutputBuffer(contents CHAR(77));
  INSERT INTO #OutputBuffer EXEC('DBCC OUTPUTBUFFER(@@spid) WITH NO_INFOMSGS');

  IF OBJECT_ID('TEMPDB..#errors') IS NULL
      CREATE TABLE #errors(
        id INT,
        errNumber BIGINT,
        errState INT,
        errLevel INT,
        errMessage NVARCHAR(2000),
        errInstance NVARCHAR(256),
        errProcedure NVARCHAR(256),
        errLine INT
      );

  -- Step through the buffer lines.
  DECLARE error_cursor CURSOR STATIC FORWARD_ONLY FOR SELECT contents FROM #OutputBuffer ORDER BY contents;

  -- Init variable, and open cursor.
  OPEN error_cursor;
  FETCH NEXT FROM error_cursor INTO @dbccrow;

    -- Count the number of error messages
    SET @numMsg = 0
    SET @pos    = 12
    SET @gather = 0

  -- Now assemble rest of string.
  WHILE @@FETCH_STATUS = 0
  BEGIN
Start:
    IF @pos > 57
    BEGIN
      SET @pos = 12;
      GOTO NextRow;
    END;

    -- Get a byte from the stream
    SET @byte = CAST(CONVERT(VARBINARY(2), SUBSTRING(@dbccrow, @pos, 2), 2) AS INT)

    -- move to the next byte
    SET @pos = @pos + 3

    -- Searching for the 0xAA marker
    IF @gather = 0
    BEGIN
      IF @byte != 170 GOTO Start;
      SELECT @gather = 1, @bytesRead = 0, @count = 0, @messageLength = 0;
      GOTO Start;
    END;

    SET @bytesRead += 1; -- record number of bytes read for message
--    IF @bytesRead > @messageLength AND @gather > 1 GOTO NextRow; -- bail out if we have read beyond the message length

    -- Get the message length
    IF @gather = 1
    BEGIN
      IF @count = 0 SET @messageLength = 0;
      SET @messageLength += (@byte * POWER(256, @count));
      SET @count = @count + 1;
      IF @count = 2 SELECT @count = 0, @gather = 2;
      GOTO Start;
    END;

    -- Get the error message
    IF @gather = 2
    BEGIN
      IF @count = 0 SET @errNumber = 0;
      SET @errNumber += (@byte * POWER(256, @count));
      SET @count = @count + 1;
      IF @count = 4 SELECT @count = 0, @gather = 3;
      GOTO Start
    END;

    -- Get the Error State
    IF @gather = 3
    BEGIN
      SELECT @gather = 4, @errState = @byte;
      GOTO Start
    END;

    -- Get the Error Level
    IF @gather = 4
    BEGIN
      SELECT @gather = 5, @errLevel = @byte;
      GOTO Start;
    END;

    -- Get the error message length
    IF @gather = 5
    BEGIN
      SET @stringLength = ISNULL(@stringLength, 0) + (@byte * POWER(256, @count));
      SET @count = @count + 1;
      IF @count = 2 SELECT @nchar = 0, @count = 0, @gather = 6;
      GOTO Start;
    END;

    -- Get the error message
    IF @gather = 6
    BEGIN
      IF @stringLength > 0
      BEGIN
        IF @count = 0 SET @nchar = 0;
        SET @nchar += (@byte * POWER(256, @count));
        SET @count = @count + 1;
        IF @count = 2 SELECT @count = 0, @errMessage = ISNULL(@errMessage, '') + nchar(@nchar);
        IF LEN(@errMessage) = @stringLength SET @gather = 7;
        GOTO Start;
      END;
      ELSE SET @gather = 7;
    END;

    -- Get the instance name size
    IF @gather = 7
    BEGIN
      SELECT @gather = 8, @stringLength = @byte;
      GOTO Start;
    END;

    -- Get the instance name
    IF @gather = 8
    BEGIN
      IF @stringLength > 0
      BEGIN
        IF @count = 0 SET @nchar = 0;
        SET @nchar += (@byte * POWER(256, @count));
        SET @count = @count + 1;
        IF @count = 2 SELECT @count = 0, @errInstance = ISNULL(@errInstance, '') + nchar(@nchar);
        IF LEN(@errInstance) = @stringLength SET @gather = 9;
        GOTO Start;
      END;
      ELSE SET @gather = 9;
    END;

    -- Get the procedure name size
    IF @gather = 9
    BEGIN
      SELECT @gather  = 10, @stringLength = @byte, @nchar = 0;
      GOTO Start;
    END;

    -- Get the procedure name
    IF @gather = 10
    BEGIN
      IF @stringLength > 0
      BEGIN
        IF @count = 0 SET @nchar = 0;
        SET @nchar += (@byte * POWER(256, @count));
        SET @count = @count + 1
        IF @count = 2 SELECT @count = 0, @errProcedure = ISNULL(@errProcedure, '') + nchar(@nchar);
        IF LEN(@errProcedure) = @stringLength SET @gather = 11;
        GOTO Start
      END;
      ELSE SET @gather = 11;
    END;

    -- Get the error line
    IF @gather = 11
    BEGIN
      SET @errLine = ISNULL(@errLine, 0) + (@byte * POWER(256, @count));
      SET @count = @count + 1;

      IF @count = 2
      BEGIN
        SET @numMsg += 1;
        -- validate the message and insert
        IF @bytesRead = @messageLength INSERT #errors VALUES(@numMsg, @errNumber, @errState, @errLevel, @errMessage, @errInstance, @errProcedure, @errLine);
        -- reset variables
        SELECT @errMessage = NULL, @errInstance = NULL, @errProcedure = NULL, @errLine = NULL;
        SET @gather = 0
        SET @count = 0
        SET @nchar = 0;
      END;

      GOTO Start;
    END;

NextRow:
    FETCH NEXT FROM error_cursor INTO @dbccrow;
  END;

  CLOSE error_cursor;
  DEALLOCATE error_cursor;
  IF @output_result = 1
    SELECT * FROM #errors ORDER BY id;
END;
GO

IF OBJECT_ID('dbo.usp_GET_LastErrorMessage') IS NOT NULL
    PRINT '*** CREATED PROCEDURE dbo.usp_GET_LastErrorMessage ***'
ELSE
    PRINT '*** FAILED CREATING PROCEDURE dbo.usp_GET_LastErrorMessage ***'
GO

部署上面的SP以后，

IF OBJECT_ID('TEMPDB..#foo') IS NOT NULL
    DROP TABLE #foo

CREATE TABLE #foo
(
c INT DEFAULT(0)
)

ALTER TABLE #foo ALTER COLUMN c VARCHAR(10)

GO
EXEC usp_GET_LastErrorMessage;

你就可以看到所有的error message了。




看上去挺不错了吧。

延伸

但是，如果你想通过TRY/CATCH来根据里面的error number进行不同的异常处理，那你还是洗洗睡吧！最主要的问题在于：直到你THROW了整个error，上面的SP才会捕获到异常信息。但是一旦已经抛异常了，TSQL就不会再继续往下执行了，你也就更加没法进行异常捕获操作了。

IF OBJECT_ID('TEMPDB..#foo') IS NOT NULL
    DROP TABLE #foo

CREATE TABLE #foo
(
c INT DEFAULT(0)
)

BEGIN TRY
ALTER TABLE #foo ALTER COLUMN c VARCHAR(10)
END TRY
BEGIN CATCH
    EXEC usp_GET_LastErrorMessage; -- Empty
    THROW;
END CATCH
GO

EXEC usp_GET_LastErrorMessage; -- Show Data

上面就是一个例子，第一次执行SP的时候，你会得到一个空结果集，第二次执行SP的时候，你就可以拿到正常的结果集了。基于这点，我的任务就没法完成了，真是然并卵！