JDBC操作数据库汇总

JDBC（Java Database Connectivity）

一、SQL语言回顾

1、Select select * from T where …

2、Insert insert into T values(…)

3、Create create table T(…)

4、Delete delete from T where…

5、Update update T set t1=… and t2=.. where t3=…

6、Drop drop table T

二、JDBC编程步骤

1、Load the Driver

Class.forName( )|Class.forName( ).newInstance( )|new DriverName( )

实例化时自动向DriverManager注册，不需显式调用DriverManager.registerDriver方法

2、Connect to the DataBase

DriverManager.getConnection( )

3、Execute the SQL

Connection.CreateStatement( )

Statement.executeQuery( )

Statement.executeUpdate( )

4、Retrieve the result data

循环取得结果while(rs.next( ))

5、Show the result data

将数据库中的各种类型转换为Java中的类型(getXXX)方法

6、Close

Close the resultset ./ close the statement / close the connection

三、JDBC操作oracle数据库

1、 JDBC连接oracle数据库：

import java.sql.*;

public class TestJDBC {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");

//new oracle.jdbc.driver.OracleDriver();

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:solid", "scott", "tiger");

}

}

2、 向数据库中取字段，遍历数据库，并显示出相应字段：

import java.sql.*;

public class TestJDBC {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");

//new oracle.jdbc.driver.OracleDriver();

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:solid", "scott", "tiger");

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("select * from dept");

while(rs.next()) {

System.out.println(rs.getString("deptno"));

System.out.println(rs.getInt("deptno"));

}

rs.close();

stmt.close();

conn.close();

}

}

3、 捕捉相关异常，完善JDBC编程：

import java.sql.*;

public class TestJDBC {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

ResultSet rs = null;

Statement stmt = null;

Connection conn = null;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:solid", "scott", "tiger");

stmt = conn.createStatement();

rs = stmt.executeQuery("select * from dept");

while(rs.next()) {

System.out.println(rs.getString("deptno"));

System.out.println(rs.getInt("deptno"));

}

} catch(ClassNotFoundException e) {

e.printStackTrace();

} catch(SQLException e) {

e.printStackTrace();

} finally {

try {

if(rs != null) {

rs.close();

rs = null;

}

if(stmt != null) {

stmt.close();

stmt = null;

}

if(conn != null) {

conn.close();

conn = null;

}

} catch(SQLException e) {

e.printStackTrace();

}

}

}

}

四、JDBC编程高级

1、 JDBC处理DML语句：

import java.sql.*;

public class TestDML {

public static void main(String[] args) {

Connection conn = null;

Statement stmt = null;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

conn = DriverManager.getConnection(

"jdbc:oracle:thin:@127.0.0.1:1521:solid", "scott", "tiger");

stmt = conn.createStatement();

String sql = "insert into dept2 values (99,'develop','changsha')";

stmt.executeUpdate(sql);

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

if (stmt != null) {

stmt.close();

stmt = null;

}

if (conn != null) {

conn.close();

conn = null;

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

2、 JDBC处理DML语句2：

import java.sql.*;

public class TestDML2 {

public static void main(String[] args) {

if (args.length != 3) {

System.out.println("Arguments error,please enter again!");

System.exit(-1);

}

int deptno = 0;

try {

deptno = Integer.parseInt(args[0]);

} catch (NumberFormatException e) {

System.out.println("Arguments error");

System.exit(-1);

}

String dname = args[1];

String loc = args[2];

Connection conn = null;

Statement stmt = null;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:solid", "scott", "tiger");

stmt = conn.createStatement();

String sql = "insert into dept2 values (" + deptno + ",'" + dname + "','" + loc + "' )";

System.out.println(sql);

stmt.executeUpdate(sql);

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

if (conn != null) {

conn.close();

conn = null;

}

if (stmt != null) {

stmt.close();

stmt = null;

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

3、 JDBC处理PreparedStatement，可以灵活指定SQL语句中的变量

import java.sql.*;

public class TestPrepStmt {

public static void main(String[] args) {

if (args.length != 3) {

System.out.println("Arguments error,please enter again!");

System.exit(-1);

}

int deptno = 0;

try {

deptno = Integer.parseInt(args[0]);

} catch (NumberFormatException e) {

System.out.println("Arguments error");

System.exit(-1);

}

String dname = args[1];

String loc = args[2];

Connection conn = null;

PreparedStatement pstmt = null;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:solid", "scott", "tiger");

pstmt = conn.prepareStatement("insert into dept2 values (?, ?, ?)");

pstmt.setInt(1, deptno);

pstmt.setString(2, dname);

pstmt.setString(3, loc);

pstmt.executeUpdate();

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

if (conn != null) {

conn.close();

conn = null;

}

if (pstmt != null) {

pstmt.close();

pstmt = null;

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

4、 JDBC处理储存过程

import java.sql.*;

public class TestProc {

public static void main(String[] args) throws Exception {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger");

CallableStatement cstmt = conn.prepareCall("{call p(?, ?, ?, ?)}");

cstmt.registerOutParameter(3, Types.INTEGER);

cstmt.registerOutParameter(4, Types.INTEGER);

cstmt.setInt(1, 3);

cstmt.setInt(2, 4);

cstmt.setInt(4, 5);

cstmt.execute();

System.out.println(cstmt.getInt(3));

System.out.println(cstmt.getInt(4));

cstmt.close();

conn.close();

}

}

5、 JDBC进行批处理

import java.sql.*;

public class TestBatch {

public static void main(String[] args) throws Exception {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger");

/*

Statement stmt = conn.createStatement();

stmt.addBatch("insert into dept2 values (51, '500', 'haha')");

stmt.addBatch("insert into dept2 values (52, '500', 'haha')");

stmt.addBatch("insert into dept2 values (53, '500', 'haha')");

stmt.executeBatch();

stmt.close();

*/

PreparedStatement ps = conn.prepareStatement("insert into dept2 values (?, ?, ?)");

ps.setInt(1, 61);

ps.setString(2, "haha");

ps.setString(3, "bj");