JDBC，java数据库连接java database connectivity

一、JDBC编程步骤

所有的JDBC都要遵循如下6个步骤：

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执行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 result./close the statement/close the connection

二、程序示例：所有示例是一个递进学习过程。

示例一：连接数据库，查询用户下的dept表信息

public class TestJDBC {
public static void main(String[] args) {

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try {
//1.加载驱动load the Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();

//实例时自动向DriverManager申请
//2.获得连接(路径，用户名，密码)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "jidi16", "123456");

//3.执行SQL语句
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from dept");

//4.循环取得结果集
while(rs.next()){

//5.显示数据，把oracle中的数据类型转化成java中的类型(getXxx()方法)
//rs.getString("deptno")
System.out.println(rs.getInt("deptno"));
System.out.println(rs.getString("dname"));
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
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();
}
}



}
}

示例二：测试DML语句，没有返回的结果集   

改备注：   window-show views-others-My Eclipse DataBase-DB Browser,
 * 通过Derby可直接连接数据库，查看数据库中的表内容，查看DML语句的结果

public class TestDML {
public static void main(String[] args) {

Connection conn = null;
Statement stmt = null;


try {
//1.加载驱动load the Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();

//实例时自动向DriverManager申请
//2.获得连接(路径，用户名，密码)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "jidi16", "123456");

//3.执行SQL语句
stmt = conn.createStatement();

String sql = "insert into dept values(60,'教学部','BJ')";

stmt.executeUpdate(sql);

} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
try {
if(stmt != null){
stmt.close();
stmt = null;
}

if(conn != null){
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}



}
}

示例三：测试DML语句，通过args数组传入3个参数，并向表中添加一条记录；

在run configuration 的 Arguments - program Arguments中输入这三个参数

缺点：sql语句太麻烦，在java中需要“ ‘dname’ ”表示，太过麻烦

public class TestDML2 {
public static void main(String[] args) {
int deptno = 0;
//可以统一声明，也可以用的时候再声明
Connection conn = null;
Statement stmt = null;


if(args.length != 3){
System.out.println("参数不正确，请输入三个参数：deptno,dname,loc");
System.exit(-1);
}

try{
deptno = Integer.parseInt(args[0]);
//考虑到用户可能不输入数字的情况
}catch(NumberFormatException e){
System.out.println("参数错误，请输入整数！");
System.exit(-1);
}

String dname = args[1];
String loc = args[2];

try {
//1.加载驱动load the Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();

//实例时自动向DriverManager申请
//2.获得连接(路径，用户名，密码)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "jidi16", "123456");

//3.执行SQL语句
stmt = conn.createStatement();

String sql = "insert into dept values("+deptno+",'"+dname+"','"+loc+"')";
//为保证正确，在控制台打印一下接收的sql语句，看是否有语法错误
System.out.println(sql);

stmt.executeUpdate(sql);

} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
try {
if(stmt != null){
stmt.close();
stmt = null;
}

if(conn != null){
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}



}
}

示例四：基于示例三的缺点，使用PreparedStatement可以灵活指定SQL语句中的变量  ,普通的Statement太麻烦

public class TestPreparedStmt {
public static void main(String[] args) {
int deptno = 0;
//可以统一声明，也可以用的时候再声明
Connection conn = null;
PreparedStatement pstmt = null;


if(args.length != 3){
System.out.println("参数不正确，请输入三个参数：deptno,dname,loc");
System.exit(-1);
}

try{
deptno = Integer.parseInt(args[0]);
//考虑到用户可能不输入数字的情况
}catch(NumberFormatException e){
System.out.println("参数错误，请输入整数！");
System.exit(-1);
}

String dname = args[1];
String loc = args[2];

try {
//1.加载驱动load the Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();

//实例时自动向DriverManager申请
//2.获得连接(路径，用户名，密码)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "jidi16", "123456");

//3.执行SQL语句
//PreparedStatement可以灵活指定SQL语句中的变量
pstmt = conn.prepareStatement("insert into dept values(?,?,?)");

//指定三个占位符?的类型
pstmt.setInt(1, deptno);
pstmt.setString(2,dname);
pstmt.setString(3,loc);

pstmt.executeUpdate();

} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
try {
if(pstmt != null){
pstmt.close();
pstmt = null;
}

if(conn != null){
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}



}
}

示例五：测试JDBC如何调用oracle中的存储过程procedure  

  存储过程：
 * CREATE OR REPLACE PROCEDURE p
 * (v_a IN NUMBER,v_b NUMBER,v_ret OUT NUMBER,v_temp IN OUT NUMBER)
IS 

BEGIN
 IF (v_a > v_b) THEN
   v_ret := v_a;
 ELSE
   v_ret := v_b;
 END IF;
 v_temp := v_temp + 1;
END;
/
 */
public class TestProcedure {
public static void main(String[] args) {
Connection conn = null;
CallableStatement cstmt = null;


try {
//1.加载驱动load the Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();

//实例时自动向DriverManager申请
//2.获得连接(路径，用户名，密码)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "jidi16", "123456");

//3.执行SQL语句
//前两个是输入参数，后两个是输出参数，为输出参数注册指定类型
cstmt = conn.prepareCall("{call p(?,?,?,?)}");

cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.registerOutParameter(4, Types.INTEGER);

//传入int类型的实际参数,第三个参数是out类型，不传入参数，只带出值
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));

} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
try {
if(cstmt != null){
cstmt.close();
cstmt = null;
}

if(conn != null){
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}



}
}

示例六： 测试批处理，处理SQL语句集

public class TestBatch {
public static void main(String[] args) {

Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;


try {
//1.加载驱动load the Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();

//实例时自动向DriverManager申请
//2.获得连接(路径，用户名，密码)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "jidi16", "123456");

//3.执行SQL语句
//第一种方式执行批处理
/* stmt = conn.createStatement();

String sql1 = "insert into dept values(61,'教学部','BJ')";
String sql2 = "insert into dept values(62,'教学部','BJ')";
String sql3 = "insert into dept values(63,'教学部','BJ')";

stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt.addBatch(sql3);
stmt.executeBatch();*/

//第二种方式

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

pstmt.setInt(1, 64);
pstmt.setString(2,"game");
pstmt.setString(3,"BJ");
pstmt.addBatch();

pstmt.setInt(1, 65);
pstmt.setString(2,"game");
pstmt.setString(3,"BJ");
pstmt.addBatch();

pstmt.setInt(1, 66);
pstmt.setString(2,"game");
pstmt.setString(3,"BJ");
pstmt.addBatch();

pstmt.executeBatch();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
try {
if(stmt != null){
stmt.close();
stmt = null;
}

if(conn != null){
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}



}
}

示例七：测试可滚动的结果集   

控制台结果： 
 * 7369
7839
true
false
14
7654

public class TestResultSetScroll {
public static void main(String[] args) {

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try {
//1.加载驱动load the Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();

//实例时自动向DriverManager申请
//2.获得连接(路径，用户名，密码)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "jidi16", "123456");

//3.执行SQL语句
stmt = conn.createStatement(
//加上 滚动不敏感 和 只读 这两个参数，结果集就可以使用除了next()之外的指定行的方法
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery("select * from emp order by sal");

rs.next();
System.out.println(rs.getInt(1));//第一个字段empno
System.out.println(rs.getRow());//得到当前行数

rs.last();//滚到最后一个
System.out.println(rs.getString("empno"));//与上同
System.out.println(rs.isLast());//是不是最后一行
System.out.println(rs.isAfterLast());//是不是最后一行的下一行
System.out.println(rs.getRow());//得到总行数

rs.absolute(5);//绝对定位到第五行
System.out.println(rs.getString(1));
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
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();
}
}



}
}

示例八：仅作了解，可更改的结果集，不支持，不推荐使用，没有效率上的提升，了解即可  

控制台报错：

java.sql.SQLException: 对只读结果集的无效操作: updateString
at oracle.jdbc.driver.BaseResultSet.updateString(BaseResultSet.java:419)
at oracle.jdbc.driver.OracleResultSet.updateString(OracleResultSet.java:819)
at jdbc.TestUpdateRS.main(TestUpdateRS.java:44)

public class TestUpdateRS {
public static void main(String[] args) {

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try {
//1.加载驱动load the Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();

//实例时自动向DriverManager申请
//2.获得连接(路径，用户名，密码)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TEST", "jidi16", "123456");

//3.执行SQL语句
stmt = conn.createStatement(
//加上 滚动不敏感 和 只读 这两个参数，结果集就可以使用除了next()之外的指定行的方法
//更新
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from emp");

rs.next();
rs.updateString("empno", "9999");
rs.updateRow();

//插入新行
rs.moveToInsertRow();
rs.updateInt(1, 9999);
rs.updateString("ename", "wang");
rs.insertRow();

//将光标移动到当前的行
rs.moveToCurrentRow();

//删除行
rs.absolute(5);
rs.deleteRow();

//取消更新
//rs.cancelRowUpdates();


} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
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();
}
}



}
}