##JDBC编程步骤
1. Load the Driver
Class.forName() | Class.forName().newInstance() | new DriverName()
实例化时自动向DriverManager注册,不需要显示调用DriverManager.registerDriver方法
2. Connect 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进阶
1. 灵活指定SQL语句中的变量
PreparedStatement
2. 对存储过程进行调用
CallableStatement
3. 批处理
Batch
4. 运用事务处理
Transaction
5. Movable ResultSet
可以前后移动的结果集
- Rs.last()
- Rs.getRow()返回值可以确定一共有多少条记录
6. Updatable ResultSet(*)
##DataSource&RowSet
1. DataSource
DriverManager
连接池实现
分布式实现
- DataSource的属性可以动态改变
2. RowSet
新的ResultSet
从ResultSet继承
支持断开的结果集
支持javaBean
##学习方法
1. 记住常用各种类型程序段
2. 将程序处理完善
JDBC初步
DML
Transaction
DataSource&RowSet
##记住四个程序
TestJDBC.java
TestDML.java
TestPrepStmt.java
TestTransaction.java
TestJDBC.java:
import java.sql.*;
public class TestJDBC {
//此程序需要做异常处理,这里没有做
public static void main(String[] args) throws Exception {
//相当于new一个OracleDriver实例
//自动向DriverManager注册
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();
//拿到数据库的连接
Connection conn = DriverManager.getConnection(
//"jdbc:oracle:thin:@PC1:1521:orcl",//数据库的连接字符串
"jdbc:oracle:thin:@127.0.0.1:1521:orcl",
//"jdbc:oracle:thin:@localhost:1521:orcl",
//"jdbc:oracle:thin:@192.168.1.152:1521:orcl",
"scott", //用户名
"tiger"); //密码
//通过连接创建语句对象
Statement stmt = conn.createStatement();
//返回一个结果集,这个结果集像游标,但指在第一条记录的上边
ResultSet rs = stmt.executeQuery("select * from dept");
while(rs.next()){
//把这给字段当前记录里边的内容当成字符串拿出来
System.out.println(rs.getString("deptno"));
//把这给字段当前记录里边的内容当成int拿出来
System.out.println(rs.getInt("deptno"));
}
rs.close();
stmt.close();
conn.close();
}
}
TestDML.java:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDML {
public static void main(String[] args) {
Statement stmt = null;
Connection conn = null;
try{
//相当于new一个OracleDriver实例
//自动向DriverManager注册
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();
//拿到数据库的连接
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl",//数据库的连接字符串
"scott", //用户名
"tiger"); //密码
//通过连接创建语句对象
stmt = conn.createStatement();
String sql = "insert into dept2 values (98, 'GAME', 'BJ')";
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();
}
}
}
}
TestPrepStmt.java:
import java.sql.*;
public class TestPrepStmt {
public static void main(String[] args) {
if(args.length != 3) {
System.out.println("参数个数错误,请重新输入!");
System.exit(-1);
}
int deptno = 0;
try {
deptno = Integer.parseInt(args[0]);
} catch (NumberFormatException e) {
System.out.println("参数输入错误,请输入数字!");
System.exit(-1);
}
String dname = args[1];
String loc = args[2];
PreparedStatement pstmt = null;
Connection conn = null;
try{
//相当于new一个OracleDriver实例
//自动向DriverManager注册
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();
//拿到数据库的连接
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl",//数据库的连接字符串
"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(pstmt != null) {
pstmt.close();
pstmt = null;
}
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
TestTransaction.java:
import java.sql.*;
public class TestTransaction {
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:SXT", "scott", "tiger");
//transaction:一系列操作要么同时完成,要么同时不完成
//任何一条DML语句在执行过程中会自动提交, 因为在数据库连接里有一个属性autoCommit默认值是true
//sql plus里DML语句不是自动提交
//要想把某些语句放入transaction里,首先要把autoCommit设置位false,不让自动提交
conn.setAutoCommit(false); // 不让自动提交
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();
conn.commit(); //手动提交
conn.setAutoCommit(true); //恢复现场
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch(SQLException e) {
e.printStackTrace();
try {
if(conn != null)
{
conn.rollback(); //出现异常,要rollback
conn.setAutoCommit(true);
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally {
try {
if(stmt != null)
stmt.close();
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}