JDBC详解

##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();
			}
		}
		

	}

}