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:@192.168.0.1:1521:SXT","scoot","tiger");
stmt = conn.createStatement();//连接声明
String sql = "select * from dept";
rs = stmt.executeQuery(sql);//执行sql语句,获取结果集
while(rs.next()){//遍历结果
}
}catch(ClassNotFoundException e){//找不到类
e.printStackTrace();
}catch(SQLException e){//SQL语句错误
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){//SQL错误
e.printStackTrace();
}
}
}
}
插入数据
import java.sql.*;
public class TestJDBC {
//从命令行输入三个数据插入数据库
public static void main(String[] args){
if(args.length != 3){
System.out.println("请输入3个参数");
}
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];
Connection conn = null;
PreparedStatement pstmt = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");//通过反射,实例化driver
//new oracle.jdbc.driver.OracleDriver();
//建立数据库连接
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT","scoot","tiger");
pstmt = conn.prepareStatement("insert into dept values(?,?,?)");//连接声明
pstmt.setInt(1,deptno);
pstmt.setString(2, dname);
pstmt.setString(3, loc);
pstmt.executeUpdate();
}catch(ClassNotFoundException e){//找不到类
e.printStackTrace();
}catch(SQLException e){//SQL语句错误
e.printStackTrace();
}finally{
try{
if(pstmt != null){//关闭声明连接
pstmt.close();
pstmt = null;
}
if(conn != null){//关闭数据库连接
conn.close();
conn = null;
}
}catch(SQLException e){//SQL错误
e.printStackTrace();
}
}
}
}
存储过程
import java.sql.*;
public class TestJDBC {
//从命令行输入三个数据插入数据库
public static void main(String[] args){
Connection conn = null;
CallableStatement cstmt = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");//通过反射,实例化driver
//new oracle.jdbc.driver.OracleDriver();
//建立数据库连接
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT","scoot","tiger");
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();
}catch(ClassNotFoundException e){//找不到类
e.printStackTrace();
}catch(SQLException e){//SQL语句错误
e.printStackTrace();
}finally{
try{
if(cstmt != null){//关闭声明连接
cstmt.close();
cstmt = null;
}
if(conn != null){//关闭数据库连接
conn.close();
conn = null;
}
}catch(SQLException e){//SQL错误
e.printStackTrace();
}
}
}
}
批处理
import java.sql.*;
public class TestJDBC {
//从命令行输入三个数据插入数据库
public static void main(String[] args){
Connection conn = null;
Statement stmt = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");//通过反射,实例化driver
//new oracle.jdbc.driver.OracleDriver();
//建立数据库连接
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT","scoot","tiger");
stmt.addBatch("insert into dept values(1,'44','haha')");
stmt.addBatch("insert into dept values(2,'44','haha')");
stmt.addBatch("insert into dept values(3,'44','haha')");
stmt.executeBatch();
}catch(ClassNotFoundException e){//找不到类
e.printStackTrace();
}catch(SQLException e){//SQL语句错误
e.printStackTrace();
}finally{
try{
if(stmt != null){//关闭声明连接
stmt.close();
stmt = null;
}
if(conn != null){//关闭数据库连接
conn.close();
conn = null;
}
}catch(SQLException e){//SQL错误
e.printStackTrace();
}
}
}
}
事务
import java.sql.*;
public class TestJDBC {
//从命令行输入三个数据插入数据库
public static void main(String[] args){
Connection conn = null;
Statement stmt = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");//通过反射,实例化driver
//new oracle.jdbc.driver.OracleDriver();
//建立数据库连接
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT","scoot","tiger");
conn.setAutoCommit(false);
stmt = conn.createStatement();
stmt.addBatch("insert into dept values(1,'44','haha')");
stmt.addBatch("insert into dept values(2,'44','haha')");
stmt.addBatch("insert into dept values(3,'44','haha')");
stmt.executeBatch();
conn.commit();
conn.setAutoCommit(true);
}catch(ClassNotFoundException e){//找不到类
e.printStackTrace();
}catch(SQLException e){//SQL语句错误
e.printStackTrace();
try{
if(conn != null){
conn.rollback();
conn.setAutoCommit(true);
}
}catch(SQLException e1){
e1.printStackTrace();
}
}finally{
try{
if(stmt != null){//关闭声明连接
stmt.close();
stmt = null;
}
if(conn != null){//关闭数据库连接
conn.close();
conn = null;
}
}catch(SQLException e){//SQL错误
e.printStackTrace();
}
}
}
}
可回滚的结果集
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:@192.168.0.1:1521:SXT","scoot","tiger");
conn.setAutoCommit(false);
stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery("select * from dept");
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.getString(1));
rs.absolute(6);
System.out.println(rs.getString(1));
}catch(ClassNotFoundException e){//找不到类
e.printStackTrace();
}catch(SQLException e){//SQL语句错误
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){//SQL错误
e.printStackTrace();
}
}
}
}
可更新的结果集
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:@192.168.0.1:1521:SXT","scoot","tiger");
conn.setAutoCommit(false);
stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from dept");
rs.next();
//更新一行数据
rs.updateString("ename", "AAA");
rs.updateRow();
//插入新行
rs.moveToCurrentRow();
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();
}catch(ClassNotFoundException e){//找不到类
e.printStackTrace();
}catch(SQLException e){//SQL语句错误
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){//SQL错误
e.printStackTrace();
}
}
}
}