SQL Server

SQL Server

SQL Server uses UTF-16 as its encoding. SQL Server uses nchar (national
character), nvarchar, ntext datatypes for Unicode text fields. Programming
Java with SQL Server is pretty straight forward. Be sure to place an “N”
(for National) prefix in front of the Unicode string literals.

The program example queries Northwind database from SQL Server DBMS
and displays the resultset on JTable. If you run on Java prior to 1.5 or on Linux,
you will need to modify font.properties file (Windows
| Linux) to enable display
of Vietnamese on standard Swing components.

SQL Server sử dụng UTF-16 encoding. SQL Server dùng nchar (national
character), nvarchar, ntext datatypes cho Unicode text fields. Lập
trình với Unicode khá đơn giản. Hãy nhớ chỉ định Unicode string literals với “N”
(cho National) prefix.

Chương trình thí dụ query Northwind database từ SQL Server DBMS và liệt
kết quả ra trên JTable. Nếu bạn chạy trên Java trước 1.5 hay trên Linux, cần modify
font.properties file (Windows
| Linux) để Tiếng Việt
có thể hiển thị đầy đủ trên standard Swing components.

Configuration:

SQLServerEx.java:

import java.sql.*;
import java.util.*;
import javax.swing.*;
import java.awt.Font;

/**
 * Modify and query SQL Server 2000 database
 *
 */
public class SQLServerEx {
    
    /** Creates a new instance of SQLServerEx */
    public SQLServerEx() {
        try {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
//            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=northwind";
//            String url = "jdbc:jtds:sqlserver://localhost:1433/northwind";
            Properties props = new Properties();
            props.put("user", "sa");
            props.put("password", "root");
            Connection con = DriverManager.getConnection(url, props);
            Statement stmt = con.createStatement();
            String update = "UPDATE Customers SET City=N'Hà Nội' WHERE City=N'London'";
            stmt.execute(update);
            update = "UPDATE Customers SET CompanyName=N'Café Thượng Uyển', Address=N'Phố Lương Văn Can', Country=N'Việt Nam' WHERE City=N'Hà Nội'";
            stmt.execute(update);
            
            String updateString = "UPDATE Customers SET CompanyName=?, Address=?, City=?, Country=? WHERE City=?";
            PreparedStatement preStmt = con.prepareStatement(updateString);
            preStmt.setString(1, "Lục Huyền Cầm");
            preStmt.setString(2, "Trần Quốc Toản");
            preStmt.setString(3, "Đà Nẵng");
            preStmt.setString(4, "Việt Nam"); 
            preStmt.setString(5, "México D.F."); 
            preStmt.executeUpdate();

            String query = "SELECT CompanyName AS 'Tên tiệm', Address AS 'Địa chỉ', City AS 'Thành phố', Phone AS 'Điện thoại', Country AS 'Quốc gia' FROM Customers";
            ResultSet rs = stmt.executeQuery(query);
            displayResult(rs, "Khách hàng");
            
            rs.close();
            stmt.close();
            con.close();
        } catch (Exception exc) {
            exc.printStackTrace();
        }
    }
    
    /** 
     * Shows resultset 
     */
    void displayResult(ResultSet rs, String tableName) {
        ResultsModel model = new ResultsModel();     // Create a table model
        model.setResultSet(rs);
        JTable table = new JTable(model);            // Create a table from the model
        table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);   // Use scrollbars
        Font font = table.getTableHeader().getFont().deriveFont(Font.BOLD);
        table.getTableHeader().setFont(font); // Bold header font
        
        JFrame jf = new JFrame(tableName);
        jf.setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        jf.setSize(800, 400);
        JTextArea jt = new JTextArea();
        jf.getContentPane().add(new JScrollPane(table));        
        jf.setVisible(true);
    }
    
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        new SQLServerEx();
    }
}

:

import java.sql.*;
import javax.swing.table.*;

class ResultsModel extends DefaultTableModel {
    
    public void setResultSet(ResultSet results) {
        try {
            ResultSetMetaData metadata = results.getMetaData();
            
            int columns =  metadata.getColumnCount();
            
            // Get the column names and set header names
            for (int i = 0; i < columns; i++) {
                addColumn(metadata.getColumnLabel(i+1));
            }
            
            // Get all rows
            while (results.next()) {
                String[] rowData = new String[columns];    // Create array to hold the data
                for (int i = 0; i < columns; i++) {         // For each column
                    rowData[ i ] = results.getString(i+1);   // retrieve the data item
                }
                addRow(rowData);    // Add a row
            }
            fireTableChanged(null);           // Signal the table there is new model data
        } catch (SQLException sqle) {
            System.err.println(sqle.getMessage());
        }
    }
}

Save source files in UTF-8 format to retain Unicode characters, and then execute
the following commands:

Lưu mã nguồn trong UTF-8 format để giữ TV Unicode, rồi thực thi các lệnh sau:

javac -encoding utf8 *.java
java SQLServerEx

References:

International Features in Microsoft SQL Server 2000