[JAVA] SQL Server Connection

本文介紹JAVA連線SQL server，操作資料庫基本指令方法。

import

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

DB configuration for connection

private static String DbUrl = "jdbc:sqlserver://xxx.xxx.xxx.xxx:1433;databaseName=xxx";
private static String DbUser = "xxx";
private static String DbPassword = "xxx";

資料處理

以下展示實際程式碼範例 (SELECT)

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class run {
    private static String DbUrl = "jdbc:sqlserver://xxx.xxx.xxx.xxx:1433;databaseName=xxx";
    private static String DbUser = "xxx";
    private static String DbPassword = "xxx";

    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(DbUrl, DbUser, DbPassword);
            connection.setAutoCommit(false);

            // Print database metadata: not really necessary.
            // DatabaseMetaData metadata = connection.getMetaData();
            // System.out.println("Database Name: " + metadata.getDatabaseProductName());

            Statement stmt = connection.createStatement();
            StringBuilder InsertSql = new StringBuilder();
            // Construct SQL statement
            InsertSql.append("SELECT * FROM dBo.Mytable");
            // Execute SQL statement and get return result set
            ResultSet rows = stmt.executeQuery(InsertSql.toString());
            ResultSetMetaData rsmd = rows.getMetaData();
            // Get return column number
            int columnCount = rsmd.getColumnCount();

            int rowRec = 1; // Count start from 1
            while (rows.next()) { // Row
                // System.out.println("Row:" + rowRec); // print the row count now
                for(int i=1; i

上方是依照資料列依序印出所有欄位的值。
這邊也有另一種方法整理資料: 存至MAP中(參考下方程式碼)。
該方法是將兩個資料欄位存為MAP中的key, value。
若有對照需求，這是不錯的方法。

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import java.util.HashMap;

public class run {
    private static String DbUrl = "jdbc:sqlserver://xxx.xxx.xxx.xxx:1433;databaseName=xxx";
    private static String DbUser = "xxx";
    private static String DbPassword = "xxx";

    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(DbUrl, DbUser, DbPassword);
            connection.setAutoCommit(false);

            // Print database metadata: not really necessary.
            // DatabaseMetaData metadata = connection.getMetaData();
            // System.out.println("Database Name: " + metadata.getDatabaseProductName());

            Statement stmt = connection.createStatement();
            StringBuilder InsertSql = new StringBuilder();
            // Construct SQL statement
            InsertSql.append("SELECT * FROM dBo.Mytable");
            // Execute SQL statement and get return result set
            ResultSet rows = stmt.executeQuery(InsertSql.toString());
            ResultSetMetaData rsmd = rows.getMetaData();
            // Get return column number
            int columnCount = rsmd.getColumnCount();
            // Initial an empty map
            Map mapping = new HashMap();

            int rowRec = 1; // Count start from 1
            while (rows.next()) { // Row
                // Put Col1 as map key, Col02 as map value
                mapping.put(rows.getString("Col01"), rows.getString("Col02"));
            }
            // Print the map out
            System.out.println(mapping.toString());
            // Close connection in the end
            connection.close();
        }catch (SQLException ex) {
            ex.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Jys