java jdbc连接各种数据库的方式大全

Java Database Connectivity (JDBC),用来与数据库打交道,本文主要总结了 jdbc与mysql,oracle,postgresql 连接的方式,并总结了 Statement, PreparedStatement , CallableStatement 以及事务 JDBC Transaction 的处理

JDBC连接 MYSQL 数据库
响应mysql jdbc的jar 包下载:http://dev.mysql.com/downloads/connector/j/
程序代码 程序代码

Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
conn = DriverManager.getConnection("jdbc:mysql://hostname:port/dbname","username", "password");
conn.close();


JDBC连接ORACLE
相关jar 包下载地址:http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html
程序代码 程序代码

Class.forName("org.postgresql.Driver");
Connection connection = null;
connection = DriverManager.getConnection(
    "jdbc:oracle:thin:@localhost:1521:yihaomen","username","password");
connection.close();


JDBC连接Postgresql
需要下载postgresql 的jdbc jar包:http://jdbc.postgresql.org/download.html
程序代码 程序代码

Class.forName("org.postgresql.Driver");
Connection connection = null;
connection = DriverManager.getConnection(
   "jdbc:postgresql://hostname:port/dbname","username", "password");
connection.close();


上面介绍了 jdbc 连接几种主流数据库的方式,下面介绍各种SQL语句的处理方式, 用jdbc写程序要特别注意的是connection 的关闭等,因此要注意try catch等的作用,一般用如下模板得到connection,并处理SQL语句,以oracle连接为例子
程序代码 程序代码

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

public class JDBCStatementInsertExample {

    private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:yihaomen";
    private static final String DB_USER = "user";
    private static final String DB_PASSWORD = "password";
    private static final DateFormat dateFormat = new SimpleDateFormat(
            "yyyy/MM/dd HH:mm:ss");

    public static void main(String[] argv) {

        try {

            insertRecordIntoDbUserTable();

        } catch (SQLException e) {

            System.out.println(e.getMessage());

        }

    }

    private static void insertRecordIntoDbUserTable() throws SQLException {

        Connection dbConnection = null;
        Statement statement = null;

        String insertTableSQL = "Insert INTO DBUSER"
                + "(USER_ID, USERNAME, CreateD_BY, CreateD_DATE) " + "VALUES"
                + "(1,'mkyong','system', " + "to_date('"
                + getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";

        try {
            dbConnection = getDBConnection();
            statement = dbConnection.createStatement();

            System.out.println(insertTableSQL);

            // execute insert SQL stetement
            statement.executeUpdate(insertTableSQL);

            System.out.println("Record is inserted into DBUSER table!");

        } catch (SQLException e) {

            System.out.println(e.getMessage());

        } finally {

            if (statement != null) {
                statement.close();
            }

            if (dbConnection != null) {
                dbConnection.close();
            }

        }

    }

    private static Connection getDBConnection() {

        Connection dbConnection = null;

        try {

            Class.forName(DB_DRIVER);

        } catch (ClassNotFoundException e) {

            System.out.println(e.getMessage());

        }

        try {

            dbConnection = DriverManager.getConnection(
                               DB_CONNECTION, DB_USER,DB_PASSWORD);
            return dbConnection;

        } catch (SQLException e) {

            System.out.println(e.getMessage());

        }

        return dbConnection;

    }

    private static String getCurrentTimeStamp() {

        java.util.Date today = new java.util.Date();
        return dateFormat.format(today.getTime());

    }

}


上面的例子是插入数据库操作,其他的更新,增加等,都可以采用类似的方式。
程序代码 程序代码

Statement statement = dbConnection.createStatement();
// 执行插入语句
statement.executeUpdate(insertTableSQL);

或者用如下PreparedStatement方式
程序代码 程序代码

String insertTableSQL = "Insert INTO DBUSER"
        + "(USER_ID, USERNAME, CreateD_BY, CreateD_DATE) VALUES"
        + "(?,?,?,?)";
PreparedStatement preparedStatement = dbConnection.prepareStatement(insertTableSQL);
preparedStatement.setInt(1, 11);
preparedStatement.setString(2, "yihaomen");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
// execute insert SQL stetement
preparedStatement .executeUpdate();


执行删除语句
程序代码 程序代码

String deleteSQL = "Delete DBUSER Where USER_ID = ?";
PreparedStatement preparedStatement = dbConnection.prepareStatement(deleteSQL);
preparedStatement.setInt(1, 1001);
preparedStatement.executeUpdate();



执行更新语句
程序代码 程序代码

String updateTableSQL = "Update DBUSER SET USERNAME = ? Where USER_ID = ?";
PreparedStatement preparedStatement = dbConnection.prepareStatement(updateTableSQL);
preparedStatement.setString(1, "mkyong_new_value");
preparedStatement.setInt(2, 1001);
preparedStatement .executeUpdate();


得到List  记录,得到多条记录:
程序代码 程序代码

String selectSQL = "Select USER_ID, USERNAME FROM DBUSER Where USER_ID = ?";
PreparedStatement preparedStatement = dbConnection.prepareStatement(selectSQL);
preparedStatement.setInt(1, 1001);
ResultSet rs = preparedStatement.executeQuery(selectSQL );
while (rs.next()) {
    String userid = rs.getString("USER_ID");
    String username = rs.getString("USERNAME");    
}


批量执行SQL语句,要启用 事务
程序代码 程序代码

dbConnection.setAutoCommit(false);
statement = dbConnection.createStatement();
statement.addBatch(insertTableSQL1);
statement.addBatch(insertTableSQL2);
statement.addBatch(insertTableSQL3);
statement.executeBatch();
dbConnection.commit();


或者采用如下preparedStatement 方式
程序代码 程序代码

dbConnection.setAutoCommit(false);//commit trasaction manually

String insertTableSQL = "Insert INTO DBUSER"
            + "(USER_ID, USERNAME, CreateD_BY, CreateD_DATE) VALUES"
            + "(?,?,?,?)";                
PreparedStatement = dbConnection.prepareStatement(insertTableSQL);

preparedStatement.setInt(1, 101);
preparedStatement.setString(2, "mkyong101");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();

preparedStatement.setInt(1, 102);
preparedStatement.setString(2, "mkyong102");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();
preparedStatement.executeBatch();

dbConnection.commit();


jdbc调用存储过程
1. 只有输入参数的情况
程序代码 程序代码

String insertStoreProc = "{call insertDBUSER(?,?,?,?)}";
callableStatement = dbConnection.prepareCall(insertStoreProc);
callableStatement.setInt(1, 1000);
callableStatement.setString(2, "mkyong");
callableStatement.setString(3, "system");
callableStatement.setDate(4, getCurrentDate());
callableStatement.executeUpdate();


2.调用存储过程,存储过程有返回值的情况
程序代码 程序代码

//getDBUSERByUserId is a stored procedure
String getDBUSERByUserIdSql = "{call getDBUSERByUserId(?,?,?,?)}";
callableStatement = dbConnection.prepareCall(getDBUSERByUserIdSql);
callableStatement.setInt(1, 10);
callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(4, java.sql.Types.DATE);

// execute getDBUSERByUserId store procedure
callableStatement.executeUpdate();

String userName = callableStatement.getString(2);
String createdBy = callableStatement.getString(3);
Date createdDate = callableStatement.getDate(4);

需要注意的是:通过 CallableStatement.registerOutParameter(index,sqlType) 注册返回的参数,最后通过CallableStatement.getDataType(index) 取得结果

3. 如果是 oracle 数据库,而存储过程返回的是 cursor 游标的处理方式
程序代码 程序代码

String getDBUSERCursorSql = "{call getDBUSERCursor(?,?)}";
callableStatement = dbConnection.prepareCall(getDBUSERCursorSql);
callableStatement.setString(1, "mkyong");
callableStatement.registerOutParameter(2, oracleTypes.CURSOR);

// execute getDBUSERCursor store procedure
callableStatement.executeUpdate();

// get cursor and cast it to ResultSet
rs = (ResultSet) callableStatement.getObject(2);

// loop it like normal
while (rs.next()) {
    String userid = rs.getString("USER_ID");
    String userName = rs.getString("USERNAME");
}

首先通过 CallableStatement.registerOutParameter(index,OracleTypes.CURSOR). 注册要返回的类型,最后用 callableStatement.getObject(index). 获取结果.

掌握了这些之后,几乎所有与jdbc操作相关的东西,都懂了,增删改查到事务,游标,存储过程的处理都懂了。

除非申明,文章均为一号门原创,转载请注明本文地址,谢谢!
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags: java jdbc
相关日志:
评论: 0 | 引用: 0 | 查看次数: -
发表评论
昵 称:
密 码: 游客发言不需要密码.
内 容:
验证码: 验证码
选 项:
虽然发表评论不用注册,但是为了保护您的发言权,建议您注册帐号.