关于JDBC学习笔记（一）

加载JDBC驱动

try {

        // Load the JDBC driver 加载指定名称的类
   
   

        String driverName = "org.gjt.mm.mysql.Driver";

        Class.forName(driverName);

    } catch (ClassNotFoundException e) {

        // Could not find the driver
   
   

    }

列出加载的全部JDBC驱动名称

List drivers = Collections.list(DriverManager.getDrivers());

    for (int i=0; i<drivers.size(); i++) {

        Driver driver = (Driver)drivers.get(i);

        // Get name of driver 驱动名称
   
   

        String name = driver.getClass().getName();

        // Get version info 驱动程序的版本信息
   
   

        int majorVersion = driver.getMajorVersion();

        int minorVersion = driver.getMinorVersion();

        boolean isJdbcCompliant = driver.jdbcCompliant();

    }

连接Oracle 数据库

Connection connection = null;     try {         // Load the JDBC driver         String driverName = "oracle.jdbc.driver.OracleDriver";         Class.forName(driverName);             // Create a connection to the database         String serverName = "127.0.0.1";         String portNumber = "1521";         String sid = "mydatabase";         String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;         String username = "username";         String password = "password";         connection = DriverManager.getConnection(url, username, password);     } catch (ClassNotFoundException e) {         // Could not find the database driver     } catch (SQLException e) {         // Could not connect to the database     }

连接MYSQL 数据库

mysql> GRANT ALL PRIVILEGES ON *.* TO username@localhost     IDENTIFIED BY 'password' WITH GRANT OPTION;         Connection connection = null;     try {         // Load the JDBC driver         String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver         Class.forName(driverName);             // Create a connection to the database         String serverName = "localhost";         String mydatabase = "mydatabase";         String url = "jdbc:mysql://" + serverName +  "/" + mydatabase; // a JDBC url         String username = "username";         String password = "password";         connection = DriverManager.getConnection(url, username, password);     } catch (ClassNotFoundException e) {         // Could not find the database driver     } catch (SQLException e) {         // Could not connect to the database     }

连接SQL2000数据库

Connection connection = null;     try {         String driverName = "com.jnetdirect.jsql.JSQLDriver"; // NetDirect JDBC driver         String serverName = "127.0.0.1";         String portNumber = "1433";         String mydatabase = serverName + ":" + portNumber;         String url = "jdbc:JSQLConnect://" + mydatabase; // a JDBC url         String username = "username";         String password = "password";             // Load the JDBC driver         Class.forName(driverName);             // Create a connection to the database         connection = DriverManager.getConnection(url, username, password);     } catch (ClassNotFoundException e) {         // Could not find the database driver     } catch (SQLException e) {         // Could not connect to the database }

直接连接ODBC数据源，不用再注册ODBC数据源

private  void getConnect(){            try{              URL url=this.getClass().getResource("../DataBase/"+DBname.toString());              String uri = new String(url.toString());              String sql_url="jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ="+uri.substring(6);              connect = DriverManager.getConnection(sql_url);              statem = connect.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);              // 创建数据库操作类的实例,该管理器对该数据库享有修改的权限            }catch(SQLException er){//捕获错误 ：： SQL错误               System.out.println("Driver can not get connection"+er.getMessage());               System.exit(0);//软件正常退出             }//end_catch();           }

列出全部创建的可以使用数据库连接接口

try {

        // Load the driver

        String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver

        Class.forName(driverName);

   

        // Get the Driver instance 获取驱动的实例类

        String url = "jdbc:mysql://a/b";

        Driver driver = DriverManager.getDriver(url);

   

        // Get available properties 获取可以使用的属性

        DriverPropertyInfo[] info = driver.getPropertyInfo(url, null);

        for (int i=0; i<info.length; i++) {

            // Get name of property

            String name = info[i].name;

   

            // Is property value required?

            boolean isRequired = info[i].required;

   

            // Get current value

            String value = info[i].value;

   

            // Get description of property

            String desc = info[i].description;

   

            // Get possible choices for property; if null, value can be any string

            String[] choices = info[i].choices;

        }

    } catch (ClassNotFoundException e) {

       // Could not find the database driver

    } catch (SQLException e) {

    }

Here's the property values for the MySql driver:

    Name(isRequired): Description

        default: default value

        choices: ...

   

    HOST(true): Hostname of MySQL Server

        default: a

   

    PORT(false): Port number of MySQL Server

        default: 3306

   

    DBNAME(false): Database name

        default: b

   

    user(true): Username to authenticate as

        default: null

   

    password(true): Password to use for authentication

        default: null

   

    autoReconnect(false): Should the driver try to re-establish bad connections?

        default: false

        choices: true, false

   

    maxReconnects(false): Maximum number of reconnects to attempt if autoReconnect is true

        default: 3

   

    initialTimeout(false): Initial timeout (seconds) to wait between failed connections

        default: 2

查询数据库是否支持存储过程

try {         DatabaseMetaData dmd = connection.getMetaData();         if (dmd.supportsTransactions()) {             // Transactions are supported         } else {             // Transactions are not supported         }     } catch (SQLException e) {     }

假如发生操作时据库的错误，本次操作回滚，取消本次的全部操作

    try {

        // Disable auto commit 设置提交的为手动提交数据操作动作

        connection.setAutoCommit(false);

   

        // Do SQL updates...

   

        // Commit updates 提交数据库操作

        connection.commit();

        connection.setAutoCommit(true);

 

    } catch (SQLException e) {

        // Rollback update 取消本次全部的数据库操作

        connection.rollback();

    }

操作处理SQL Exception

try {         // Execute SQL statements... 操作SQL的语句     } catch (SQLException e) {         while (e != null) {             // Retrieve a human-readable message identifying the reason for the exception             String message = e.getMessage();                 // This vendor-independent string contains a code that identifies             // the reason for the exception.             // The code follows the Open Group SQL conventions.             String sqlState = e.getSQLState();                 // Retrieve a vendor-specific code identifying the reason for the exception.             int errorCode = e.getErrorCode();                 // If it is necessary to execute code based on this error code,             // you should ensure that the expected driver is being             // used before using the error code.                 // Get driver name             String driverName = connection.getMetaData().getDriverName();             if (driverName.equals("Oracle JDBC Driver") && errorCode == 123) {                 // Process error...             }                 // The exception may have been chained; process the next chained exception             e = e.getNextException();         }     }

查询是否有SQL Warning 发生

try {         // Get warnings on Connection object         SQLWarning warning = connection.getWarnings();         while (warning != null) {             // Process connection warning             String message = warning.getMessage();             String sqlState = warning.getSQLState();             int errorCode = warning.getErrorCode();             warning = warning.getNextWarning();         }             // Create a statement         Statement stmt = connection.createStatement();             // Use the statement...             // Get warnings on Statement object         warning = stmt.getWarnings();         if (warning != null) {             // Process statement warnings...         }             // Get a result set         ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");         while (resultSet.next()) {             // Use result set                 // Get warnings on the current row of the ResultSet object             warning = resultSet.getWarnings();             if (warning != null) {                 // Process result set warnings...             }         }     } catch (SQLException e) {     }

获取数据库驱动的连接

try {

        DatabaseMetaData dmd = connection.getMetaData();

        String driverName = dmd.getDriverName();  // Mark Matthew's MySQL Driver

    } catch (SQLException e) {

    }

//The best you can do is to use the URL used to create the connection: 最好的方法

    try {

        // Create connection from URL

        Connection conn = DriverManager.getConnection(url, username, password);

   

        // Get driver from URL

        Driver driver = DriverManager.getDriver(url);

    } catch (SQLException e) {

    }

设置SQL操作返回的影响的行数

try {         // Get the fetch size of a statement         Statement stmt = connection.createStatement ();         int fetchSize = stmt.getFetchSize();             // Set the fetch size on the statement 设置返回的行数         stmt.setFetchSize(100);             // Create a result set 创建一个结果集         ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");             // Change the fetch size on the result set 指定结果集的容量         resultSet.setFetchSize(100);     } catch (SQLException e) {     }