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

From：一号门