DAO Sql 得到 insert 主鍵的方法，包括一般 JDBC 和 Spring JDBC Template

一般 JDBC：

int id = 0;
String sql = "INSERT INTO ...........";

Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;

try{
	con = DriverManager.getConnection(Constant.DB_MAIN);
	pstmt = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
	pstmt.executeUpdate();

	ResultSet generatedKeys = pstmt.getGeneratedKeys();
	if (generatedKeys.next()){
		id = generatedKeys.getInt(1);
	}
}catch (Exception e) {
	e.printStackTrace();
} finally {
	ConControl.freeConnection(rs, pstmt, con);
}

Spring JDBC Template：

public int insertPopularFaq(PopularFaqBean popularFaq) {
		String sql = "DECLARE @popular_faq_rule_id INT "
				   + "DECLARE @faq_id INT "
				   + "SET @popular_faq_rule_id = ? "
				   + "SET @faq_id = ? "
				   + "INSERT INTO popular_faq(popular_faq_rule_id, faq_id) VALUES(@popular_faq_rule_id, @faq_id)";
		
		KeyHolder keyHolder = new GeneratedKeyHolder();
		cs_JdbcTemplate.update((Connection con) -> {
			PreparedStatement pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			int i = 1;
			pstmt.setInt(i++, popularFaq.getPopularFaqRuleId());
			pstmt.setInt(i++, popularFaq.getFaqId());
			
			return pstmt;
		}, keyHolder);
        
        Number key = keyHolder.getKey();
		
		return key.intValue();
	}

Sql Server 查出所有 Foreign Key (外鍵) 相依關係的 Sql

紀錄下
 Sql Server 查出所有 Foreign Key (外鍵) 相依關係的 Sql
參考自這篇文

How can I list all foreign keys referencing a given table in SQL Server?

Gustavo Rubio
的回答

 SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

參考資料：

1. How can I list all foreign keys referencing a given table in SQL Server?

Microfost 官方 JDBC driver 的 getMoreResults Method (int current) 不支援以 Statement.KEEP_CURRENT_RESULT = 2 做為輸入值

Java 的 java.sql.CallableStatement 類別有一個 叫做 getMoreResults(int current) 的 method，
可以傳入 int current 作為參數，
int current 的值可以使用 java.sql.Statement 的一些 static 屬性值，
例如：

Statement.CLOSE_CURRENT_RESULT = 1

Statement.KEEP_CURRENT_RESULT = 2

但要注意不是每一個 JDBC driver 都支援所有值，
例如 Microfost 官方 getMoreResults Method (int) 這篇文有說到其官方 JDBC driver

(com.microsoft.sqlserver.jdbc.SQLServerDrive)
並不支援
Statement.KEEP_CURRENT_RESULT ：
    ==> KEEP_CURRENT_RESULT (not supported by the JDBC driver)

安裝有 full-text search 功能的 mssql Docker container

如果有使用 mssql 官方的 Docker image 的話，
會發現並沒有預設安裝 Full-Text Search 功能 (可以提供 contains({columnName}, {containsValue}) 等語法)，
這時候就要進入 mssql 的 Docker container 手動下指令進行安裝，
或是直接修改 Dockerfile 在裡面寫上安裝 Full-Text Search 功能的指令。

以下分享如何進行 MS Sql Full-Text Search 功能的安裝，
首先可以先用以下 Sql 指令判斷有無已安裝了 Full-Text Search

select FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

如果查詢結果是 1 就是有安裝，否則就是 0 代表沒安裝。

接著修改 Dockerfile 如下所示：

FROM mcr.microsoft.com/mssql/server:2019-latest

USER root

RUN export DEBIAN_FRONTEND=noninteractive && \
apt-get update --fix-missing && \
apt-get install -y gnupg2 && \
apt-get install -yq curl apt-transport-https && \
curl https://packages.microsoft.com/keys/microsoft.asc | tac | tac | apt-key add - && \
curl https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list | tac | tac | tee /etc/apt/sources.list.d/mssql-server.list && \
apt-get update

RUN apt-get install -y mssql-server-fts

CMD /opt/mssql/bin/sqlservr

EXPOSE 1433

就可以成功安裝 Full-Text Search 了

參考資料：

1. Issue Full-Text Search is not installed, or a full-text component cannot be loaded. on windows container #161
2. How to detect if full text search is installed in SQL Server
3. 適用于 Microsoft 產品的 Linux 軟體存放庫

MS Sql Server 的 SYSDATETIMEOFFSET()、SWITCHOFFSET() 和 TODATETIMEOFFSET()

在 Microsoft Sql Server 中，
datetime 資料型別是沒有時區資訊的，，
比如 2022-01-01 00:00:00 如果沒有時區資訊的話，
它可以是美國時區的 2022-01-01 00:00:00 ，也可以是台灣時區的 2022-01-01 00:00:00，
對 1970-01-01 00:00:00 UTC+0 的毫秒數間隔是不一樣的。

而 datetimeoffset 資料型別就有時區資訊，
例如 2022-01-01 00:00:00 UTC+8 就是台灣時區的 2022-01-01 00:00:00，
對應到 UTC-8 的時區 就是 2001-12-31 08:00:00 UTC-8，
只是表示方式不同，
但對 1970-01-01 00:00:00 UTC+0 的毫秒數間隔通通都是一樣的。

以下介紹我常用的三個好用 Sql Server 函式，
SYSDATETIMEOFFSET()、SWITCHOFFSET() 和 TODATETIMEOFFSET()，
可以用來對日期格式做不同處理：

SYSDATETIMEOFFSET()：
傳回擁有時區資訊的系統目前時間 (格式為 datetimeoffset(7)，即小數位數有到 7 位的有時區時間)
例如 print SYSDATETIMEOFFSET() 可印出如下結果：
2022-07-19 20:40:24.8558075 -07:00
跟 SYSDATETIME() 的差別是 SYSDATETIME() 是傳回 datetime2 格式的無時區時間，其印出結果如下：
2022-07-19 20:40:24.8558075
可以看到只差在有無包含時區資訊而已

SWITCHOFFSET(datetimeoffset_expression, timezoneoffset_expression)：
對特定有時區資訊 (沒給時區的話會被當做是 UTC+0) 的日期(datetimeoffset_expression) 用指定的時區位移(timezoneoffset_expression)
進行換算並返回相應的 dateoffset 型別結果，例如：
print SWITCHOFFSET('2022-01-01 03:00:00 +07:00', '+08:00')
的結果為：
2022-01-01 04:00:00.0000000 +08:00
可以看到 SWITCHOFFSET() 並不會改變日期的值，
也就是其日期和 1970-01-01 00:00:00 之間差距的毫秒數還是一樣，
指的還是同一個日期，只是用不同的時區格式寫出來而已。

TODATETIMEOFFSET(datetime_expression , timezoneoffset_expression)：
對特定無時區資訊 (有給時區的話會被忽略) 的日期(datetime_expression) 加上指定的時區位移(timezoneoffset_expression)
資訊，返回日期和時區組合好後的有時區資訊日期，型別為 datetimeoffset，
例如：
print TODATETIMEOFFSET('2022-01-01 03:00:00 +07:00', '+08:00')
的結果為：
2022-01-01 03:00:00.0000000 +08:00
可以看到只有時區部份的資訊被改變了，表示無時區部份的日期資訊並沒有被改變，
所以其日期和 1970-01-01 00:00:00 之間差距的毫秒數也改變了，
變成用新時區去看日期部份得到的新日期。

參考資料：

1. Transact-SQL (日期和時間資料類型和函式)
2. 使用內建函式查詢

[MS sql server] 使用 Sql 語法在不同 database 中間倒資料的方法

 在登入一個 sql server下，

可以用以下語法存取另一個 sql  server，

例如以下語法可以登入名為 xxxServer, port 為 1300 的 sql server 

exec sp_addlinkedserver 'myXxxServer', '', 'SQLOLEDB', 'xxxServer,1300'  -- create linked server to xxxServer
exec sp_addlinkedsrvlogin 'myXxxServer', 'false',null, '{帳號}', '{密碼}' -- login xxxServer

然後就可以用 myXxxServer 這個名字 (可自取) 存取 xxxServer，

例如讀取其中的一個名為 xxxTable 的 table，其 table 在名為 xxxDatabase 的 database 中：

SELECT *
FROM myXxxServer.xxxDatabase.dbo.xxxTable 

還可以做到跨 sql server 的 JOIN 等操作，非常方便，例如：

SELECT *
FROM myXxxServer.xxxDatabase.dbo.xxxTable A INNER JOIN
someTable B ON A.a = B.b

或是倒資料，例如：

INSERT INTO A(a1, a2)
SELECT b1, b2
FROM myXxxServer.xxxDatabase.dbo.B

最後再用以下語法登出 xxxServer

exec sp_dropserver 'myXxxServer', 'droplogins' -- free myXxxServer linked server

**補充：

如果要倒的資料欄位裡有主鍵 (Primary Key)，

需要把禁止修改主鍵的功能關掉，Update 資料完後再開回來。

再來此時不能用星號 "*" 來代表所有欄位，要把欄位名稱一個個寫出來才能成功寫入。

例如：

SET IDENTITY_INSERT A ON;

INSERT INTO A(a1, a2)
SELECT b1, b2
FROM myXxxServer.xxxDatabase.dbo.B


SET IDENTITY_INSERT A OFF;

如果想要快速的得到欄位名稱的字串，可以用以下語法來得到
(以下為得到名為 A 這個 table 的所有欄位名稱，會用逗號分隔欄位名，最後用成一串字來輸出)：

SELECT SUBSTRING(
    (
	SELECT ', ' + QUOTENAME(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'A'
        ORDER BY ORDINAL_POSITION
        FOR XML path('')
    )
    , 3, 200000
)

執行結果就像是： [a1], [a2], [a3]

Node.js 連接 Sql Server -

這邊紀錄如何使用node.js連接Sql Server的範例，

首先我使用的node.js module是mssql，它有npm網站連結及Github連結
只要用node.js 打上
npm install mssql
即可安裝

以下是範例程式碼，

var config = {
    user: 'XXX',
    password: 'XXX',
    server: 'XXX', // You can use 'localhost\\instance' to connect to named instance
    port: 0000,
    database: 'XXX'
    /*
    ,options: {
        encrypt: true // Use this if you're on Windows Azure
    }
    */
}
//獲取連線
var connection = new sql.Connection(config); 
connection.connect(function() {
 // Query 範例   
        //建立Request來進行query，query會回傳Promise，
        //call back function裡的recordset是一個陣例，為物件的集合，
        //物件各Key為Query結果的欄位名稱，Value為值
    new sql.Request(connection).query('SELECT * FROM someTable').then(function(recordset) {
  var i = 0;
  //印出各row的各個欄位
  for (i=0; i < recordset.length; i++){
   console.log(recordset[i].column1);
   console.log(recordset[i].column2);
   console.log(recordset[i].column3);
   connection.close();  //關閉連接
  }
 }).catch(function(err) {
  // ... error checks
  console.log(err);
  connection.close();  //關閉連接
 });
});