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