java jdbc连接各种数据库的方式大全
摘要: Java Database Connectivity (JDBC),用来与数据库打交道,本文主要总结了 jdbc与mysql,oracle,postgresql 连接的方式，并总结了 Statement, PreparedStatement , CallableStatement 以及事务 JDBC Transaction 的处理
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操作相关的东西，都懂了，增删改查到事务，游标，存储过程的处理都懂了。
