本文介紹JAVA連線SQL server，操作資料庫基本指令方法。
import
|
1 2 3 4 5 6 7 |
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
|
1 2 3 |
private static String DbUrl = "jdbc:sqlserver://xxx.xxx.xxx.xxx:1433;databaseName=xxx"; private static String DbUser = "xxx"; private static String DbPassword = "xxx"; |
資料處理
以下展示實際程式碼範例 (SELECT)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
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 <= columnCount; i++) { // Column System.out.println(rows.getString(i)); // Print values in column } rowRec++; } // Close connection in the end connection.close(); }catch (SQLException ex) { ex.printStackTrace(); try { connection.rollback(); } catch (SQLException e) { e.printStackTrace(); } } } } |
上方是依照資料列依序印出所有欄位的值。
這邊也有另一種方法整理資料: 存至MAP中(參考下方程式碼)。
該方法是將兩個資料欄位存為MAP中的key, value。
若有對照需求，這是不錯的方法。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
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<String, String> mapping = new HashMap<String, String>(); 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(); } } } } |
留言