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操作相关的东西，都懂了，增删改查到事务，游标，存储过程的处理都懂了。