Jdbc标准编程

package com.yelang;

import java.sql.*;

public class TestJdbc
{
public static void main(String[] args)
{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try
{
// Class.forName("com.mysql.jdbc.Driver").newInstance();
// new com.mysql.jdbc.Driver();
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/shiyan", "root", "111");
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from user");
while (rs.next())
{
System.out.println(rs.getString("username"));
System.out.println(rs.getString("password"));
}
}
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();
}

}
}
}

PreparedStatement

import java.sql.*;

public class TestPrepStmt
{

public static void main(String[] args)
{
if (args.length != 3)
{
System.out.println("Parameter Error! Please Input Again!");
System.exit(-1);
}

int deptno = 0;

try
{
deptno = Integer.parseInt(args[0]);
}
catch (NumberFormatException e)
{
System.out
.println("Parameter Error! Deptno should be Number Format!");
System.exit(-1);
}

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

PreparedStatement pstmt = null;
Connection conn = null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
// new oracle.jdbc.driver.OracleDriver();
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.0.1:1521:SXT", "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();
}
}
}

}

存储过程

SQL>create or replace procedure p
2 (v_a in number,v_b number,v_ret out number,v_temp in out number)
3 is
4
5 begin
6 if(v_a > v_b) then
7 v_ret := v_a;
8 else
9 v_ret := v_b;
10 end if;
11 v_temp := v_temp + 1;
12 end;

import java.sql.*;

public class TestProc
{
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:@192.168.0.1:1521:SXT", "scott", "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();
System.out.println(cstmt.getInt(3));
System.out.println(cstmt.getInt(4));
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
try
{
if (cstmt != null)
{
cstmt.close();
cstmt = null;
}
if (conn != null)
{
conn.close();
conn = null;
}
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
}

批处理

import java.sql.*;

public class TestBatch
{

public static void main(String[] args)
{
Connection conn = null;

PreparedStatement ps = null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger");
ps = conn.prepareStatement("insert into dept2 values (?, ?, ?)");
ps.setInt(1, 61);
ps.setString(2, "haha");
ps.setString(3, "bj");
ps.addBatch();
ps.setInt(1, 62);
ps.setString(2, "haha");
ps.setString(3, "bj");
ps.addBatch();
ps.setInt(1, 63);
ps.setString(2, "haha");
ps.setString(3, "bj");
ps.addBatch();
ps.executeBatch();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
try
{
if (ps != null)
{
ps.close();
ps = null;
}
if (conn != null)
{
conn.close();
conn = null;
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
}

Transaction，事务处理

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

JDBC处理可滚动的结果集

import java.sql.*;

public class TestScroll
{
public static void main(String args[])
{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@192.168.0.1:1521:SXT";
conn = DriverManager.getConnection(url, "scott", "tiger");
stmt = conn.createStatement(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));
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)
{
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();
}
}
}
}

JDBC处理可更新的结果集

import java.sql.*;

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

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@192.168.0.1:1521:SXT";
conn = DriverManager.getConnection(url, "scott", "tiger");
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from emp2");
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();

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