Java：JDBC篇，Connection连接至几种常用数据库(Oracle,MySQL,Access,SQL Server)

1. 描述

尝试用JDBC分别连接到Oracle/SQL/MySQL/Access等常用数据库。

1. Oracle 11g数据库，需要ojdbc6.jar
2. MySQL 5.5数据库，需要mysql-connector-java-3.1.10-bin.jar
3. Access 2003数据库，需要jackcess-1.2.9.jar，依赖包：commons-lang-2.6.jar，commons-logging-1.1.1.jar
4. SQL Server 2012数据库，需要sqljdbc4.jar（JDK版本为1.6以上）

2. 示范代码

package com.clzhang.sample.jdbc;

import java.io.*;
import java.util.*;
import java.sql.*;

import org.junit.Test;

import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.Table;

/**
 * 常用数据库的连接创建操作
 * 1.Oracle 11g数据库，需要ojdbc6.jar
 * 2.MySQL 5.5数据库，需要mysql-connector-java-3.1.10-bin.jar
 * 3.Access 2003数据库，需要jackcess-1.2.9.jar，依赖包：commons-lang-2.6.jar，commons-logging-1.1.1.jar
 * 4.SQL Server 2012数据库，需要sqljdbc4.jar（JDK版本为1.6以上）
 * @author acer
 *
 */
public class ConnectionTest {
    
    @Test
    public void testOracle() throws Exception {
        // 数据库连接配置参数
        String driver_name = "oracle.jdbc.driver.OracleDriver";
        String jdbc_url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        String jdbc_user = "mytest";
        String jdbc_password = "yourpass";
        
        Class.forName(driver_name);
        Connection conn = DriverManager.getConnection(jdbc_url, jdbc_user,
                jdbc_password);
        Statement stmt= conn.createStatement();
        ResultSet rs = stmt.executeQuery("select SYSDATE from DUAL");
        if(rs.next()) {
            System.out.println(rs.getString(1));
        }
        rs.close();
        stmt.close();
        conn.close();
        
        System.out.println("--------------------------------");
    }
    
    @Test
    public void testMySQL() throws Exception {
        // 数据库连接配置参数
        String driver_name = "org.gjt.mm.mysql.Driver";
        String jdbc_url = "jdbc:mysql://localhost/xuejia?user=root&password=yourpass&useUnicode=true&characterEncoding=gb2312";
        
        Class.forName(driver_name);
        Connection conn = DriverManager.getConnection(jdbc_url);
        Statement stmt= conn.createStatement();
        ResultSet rs = stmt.executeQuery("select CONTENTID,TITLE from CONTENTINFO");
        while(rs.next()) {
            System.out.println(rs.getString(1) + rs.getString(2));
        }
        rs.close();
        stmt.close();
        conn.close();
        System.out.println("--------------------------------");
    }    
    
    @SuppressWarnings("rawtypes")
    @Test
    public void testAccess() throws Exception {
        // Access最简单
        String db_filename = "D:\\MyDocuments\\Docs\\Contacts.mdb";
        
        Table table = Database.open(new File(db_filename)).getTable("RECORDS");
        for(Map row : table) {  
               System.out.println(row.get("LASTNAME") + ":" + row.get("TEL1")); 
        }

        System.out.println("--------------------------------");
    }
    
    @Test
    public void testSQLServer() throws Exception {
        // 因为是JDBC 4.0，所以不需要加载JDBC驱动包
        String jdbc_url = "jdbc:sqlserver://localhost:1434;databaseName=mydb;user=sa;password=yourpass;"; 
        Connection conn = DriverManager.getConnection(jdbc_url);
        Statement stmt= conn.createStatement();
        ResultSet rs = stmt.executeQuery("select ID,TITLE,WEBCONTENT from dbo.WEBINFO");
        while(rs.next()) {
            System.out.println(rs.getString(1) + rs.getString(2));
        }
        rs.close();
        stmt.close();
        conn.close();

        System.out.println("--------------------------------");
    }        
}