JDBC编程步骤
import java.sql.*;
public class TestJDBC {
public static void main(String[] args) {
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver"); //注册driver
//new oracle.jdbc.driver.OracleDriver();
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "tiger"); //拿到connection
stmt=conn.createStatement(); //创建statement
rs=stmt.executeQuery("select * from dept"); //执行sql语句放入结果集中
while(rs.next()) {
System.out.println(rs.getString("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();
}
}
}
}
=====================================DML=========================================
import java.sql.*;
public class TestDML {
public static void main(String[] args) {
Connection conn=null;
Statement smt=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott","tiger");
String sql="insert into dept2 values(90,'Game','bj')";
smt=conn.createStatement();
smt.executeUpdate(sql);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
==================================PreparedStatement=================================
使用占位符构建sql语句
import java.sql.*;
public class TestPrepStmt {
public static void main(String[] args) {
if(args.length!=3) {
System.out.println("args error!");
System.exit(-1);
}
int deptno=0;
try {
deptno=Integer.parseInt(args[0]);
}catch(NumberFormatException e) {
e.printStackTrace();
System.exit(-1);
}
String dName=args[1];
String loc=args[2];
Connection conn=null;
PreparedStatement presmt=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott","tiger");
presmt=conn.prepareStatement("insert into dept2 values(?,?,?)");
presmt.setInt(1,deptno);
presmt.setString(2,dName);
presmt.setString(3, loc);
presmt.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
============================================CallableStatement===========================
执行存储过程
存储过程如下:
create or replace procedure
getMaxAndSum1(var_x number,var_y in out number)
is
begin
if var_x<var_y then
dbms_output.put_line(var_y);
else
dbms_output.put_line(var_x);
end if;
var_y:=var_x+var_y;
end;
/
---------------------------------------------------------------
import java.sql.*;
public class TestProc {
public static void main(String[] args) {
Connection conn=null;
CallableStatement csmt=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "tiger");
csmt=conn.prepareCall("{call getMaxAndSum1(?,?)}");
csmt.registerOutParameter(2,Types.INTEGER);
csmt.setInt(1,2);
csmt.setInt(2,3);
csmt.execute();
System.out.println(csmt.getInt(2));
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
try {
csmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
======================================Batch===========================================
可用于批处理sql语句
Statement 和 PreparedStatement都可使用addBatch方法添加sql语句,使用executeBatch方法执行。
import java.sql.*;
public class TestBatch {
public static void main(String[] args) {
Connection conn=null;
Statement smt=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "tiger");
smt=conn.createStatement();
smt.addBatch("insert into dept2 values(1,'Game','bj')");
smt.addBatch("insert into dept2 values(2,'Game','bj')");
smt.addBatch("insert into dept2 values(3,'Game','bj')");
smt.executeBatch();
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
smt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
=====================================Transaction========================================
java看实现transaction
1、设置connection的AutoCommit为fasle
2、执行sql语句,然后进行手动commit
3、将AutoCommit设置回true
4、在sql异常中添加回滚语句
import java.sql.*;
public class TestTransaction {
public static void main(String[] args) {
Connection conn=null;
Statement smt=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "tiger");
conn.setAutoCommit(false); //将自动提交设置为false
smt=conn.createStatement();
smt.addBatch("insert into dept2 values(1,'Game','bj')");
smt.addBatch("insert into dept2 values(2,'Game','bj')");
smt.addBatch("insert into dept2 values(3,'Game','bj')");
smt.executeBatch();
conn.commit(); //手动进行提交
conn.setAutoCommit(true); //将自动提交设置回来
} catch (ClassNotFoundException e) {
e.printStackTrace();
}catch(SQLException e){
if(conn!=null) {
try {
conn.rollback(); //出错后进行回滚
conn.setAutoCommit(true);
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}finally {
try {
if(smt!=null) {
smt.close();
}
if(conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
=============================setScroll=============================================
可在创建statement时将数据集中的游标滚动设置为不敏感ResultSet.TYPE_SCROLL_INSENSITIVE,由自己来手动进行滚动操作。
import java.sql.*;
public class TestScroll {
public static void main(String[] args) {
try {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "tiger");
Statement smt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, //设置滚动不敏感
ResultSet.CONCUR_READ_ONLY);
ResultSet rs=smt.executeQuery("select * from emp order by sal");
rs.next();
System.out.println(rs.getInt(1)); //取第一列数据
rs.last();
System.out.println(rs.getString(1));
System.out.println(rs.isLast());
System.out.println(rs.isAfterLast());
System.out.println(rs.getRow());
rs.previous();
System.out.println(rs.getInt(1));
rs.absolute(6); //取第6行数据
System.out.println(rs.getInt(1));
rs.close();
smt.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
================================处理可更新结果集======================================
创建statement时添加参数 ResultSet.CONCUR_UPDATABLE
查询时不能写select * 需要把列名都写出来
import java.sql.*;
public class TestUpdateRs {
public static void main(String[] args) {
try {
new oracle.jdbc.driver.OracleDriver();
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "tiger");
Statement smt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, //设置滚动不敏感
ResultSet.CONCUR_UPDATABLE); //设置更新时可写
ResultSet rs=smt.executeQuery(
"select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp2"); //不能使用select *
rs.next();
//更新一行数据
rs.updateString("ename","AAAA");
rs.updateRow();
//插入新行
rs.moveToInsertRow();
rs.updateInt(1,9999);
rs.updateString("ename","AAAA");
rs.updateInt("mgr",7839);
rs.updateDouble("sal",99.99);
rs.insertRow();
//将光标移动到新建行
rs.moveToCurrentRow();
//删除行
rs.absolute(5);
rs.deleteRow();
//取消更新
//rs.cancelRowUpdates();
rs.close();
smt.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}