select:
import java.sql.*;
public class Select {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver"); //MySQL : "com.mysql.jdbc.Driver"
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");
//MySQL : "jdbc:mysql://localhost:3306/mydata?user=root&password=tiger&useSSL=true"
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from dept2");
while(rs.next()) {
System.out.println(rs.getInt("deptno") + "," + rs.getString("dname") + "," + rs.getString("loc"));
}
} catch (Exception 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(Exception e) {
e.printStackTrace();
}
}
}
}
insert:
import java.sql.*;
import java.io.*;
public class Insert {
public static void main(String[] args) {
int changeLine = 0;
int deptno = 0;
String dname = null;
String loc = null;
Connection conn = null;
PreparedStatement pstmt = null;
BufferedReader reader = new BufferedReader(new InputStreamReader(System.in));
try {
System.out.println("请输入部门编号:");
deptno = Integer.parseInt(reader.readLine());
System.out.println("请输入部门名称:");
dname = reader.readLine();
System.out.println("请输入部门地点:");
loc = reader.readLine();
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
pstmt = conn.prepareStatement("insert into Dept2 values (?,?,?)");
pstmt.setInt(1, deptno);
pstmt.setString(2, dname);
pstmt.setString(3, loc);
changeLine = pstmt.executeUpdate();
System.out.println("插入成功,"+ changeLine + "行受影响。");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (reader != null) {
reader.close();
reader = null;
}
if(pstmt != null) {
pstmt.close();
pstmt = null;
}
if(conn != null) {
conn.close();
conn = null;
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
批量处理SQL语句(Batch):
import java.sql.*;
public class Batch {
public static void main(String[] args) {
Connection conn = null;
//Statement stmt = null;
PreparedStatement pstmt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
//批处理第一种方式:
/*
stmt = conn.createStatement();
stmt.addBatch("insert into Dept2 values (93,'销售','韶关')");
stmt.addBatch("insert into Dept2 values (94,'销售','韶关')");
stmt.addBatch("insert into Dept2 values (95,'销售','韶关')");
stmt.executeBatch();
*/
//批处理第二种方式:
pstmt = conn.prepareStatement("insert into Dept2 values (?,?,?)");
for(int i = 90; i<93; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "销售");
pstmt.setString(3, "汕尾");
pstmt.addBatch();
}
pstmt.executeBatch();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
/*
if(stmt != null) {
stmt.close();
stmt = null;
}
*/
if(pstmt != null) {
pstmt.close();
pstmt = null;
}
if(conn != null) {
conn.close();
conn = null;
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
使用事务(Transection):
import java.sql.*;
public class Transection {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
//事务的使用方法:
//1.取消自动提交
conn.setAutoCommit(false);
//2.处理SQL语句
pstmt = conn.prepareStatement("insert into Dept2 values (?,?,?)");
for(int i = 10; i<13; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "生产");
pstmt.setString(3, "汕尾");
pstmt.addBatch();
}
pstmt.executeBatch();
//3.手动提交
conn.commit();
//4.恢复现场
conn.setAutoCommit(true);
} catch (SQLException e) {
//5.异常时回滚
try {
if(conn != null) {
conn.rollback();
conn.setAutoCommit(true);
}
} catch (Exception ee) {
ee.printStackTrace();
}
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(pstmt != null) {
pstmt.close();
pstmt = null;
}
if(conn != null) {
conn.close();
conn = null;
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
调用存储过程(procedure):
import java.sql.*;
public class Procedure {
//假设存储过程的定义为:p(v_a in number,v_b number, v_ret out number, v_temp in out number) 比较v_a和v_b,返回最小值。
public static void main(String[] args) {
Connection conn = null;
CallableStatement cstmt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
cstmt = conn.prepareCall("call p(?,?,?,?)");
cstmt.registerOutParameter(3,Types.INTEGER);
cstmt.registerOutParameter(4,Types.INTEGER);
cstmt.setInt(1, 7);
cstmt.setInt(2, 10);
cstmt.setInt(4, 1);
cstmt.execute();
System.out.println("v_ret = " + cstmt.getString(3) + " , v_temp = " + cstmt.getString(4));
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(cstmt != null) {
cstmt.close();
cstmt = null;
}
if(conn != null) {
conn.close();
conn = null;
}
} catch(Exception e) {
e.printStackTrace();
}
}
}
}
使用可滚动的结果集:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Scroll {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");
//设置结果集可滚动
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery("select * from dept2");
rs.next();
System.out.println("结果集的第一条记录:" + rs.getString(1));
rs.last();
System.out.println("结果集的总条数:" + rs.getRow());
System.out.println("结果集的最后一条记录:" + rs.getString(1));
System.out.println("是否最后一条:" + rs.isLast());
System.out.println("是否在最后一条之下:" + rs.isAfterLast());
rs.previous();
System.out.println("最后一条的前一条记录:" + rs.getString(1));
rs.absolute(6);
System.out.println("第六条记录:" + rs.getString(1));
} catch (Exception 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(Exception e) {
e.printStackTrace();
}
}
}
}
使用可更新的结果集:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class UpdateResultSet {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");
//设置结果集可更新(Oracle不支持,此处仅作步骤展示)
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from emp2");
rs.next();
//更新一行数据
rs.updateString("ename", "Eric");
rs.updateRow();
//插入新行
rs.moveToInsertRow();
rs.updateInt(1, 9999);
rs.updateString("ename","E-Kunt");
rs.updateInt("mgr", 7839);
rs.updateDouble("sal",8888.88);
rs.insertRow();
//将游码移动到新建的行
rs.moveToCurrentRow();
//删除行
rs.absolute(5);
rs.deleteRow();
//取消更新
rs.cancelRowUpdates();
} catch (Exception 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(Exception e) {
e.printStackTrace();
}
}
}
}