jdbc代码:
需要引入相关jar包(Oracle需要class12.jar)(mysql需要mysql-connector-java-xxx.jar);
1.
Oracle的JDBC测试:
import java.sql.*;
public class TestJDBC {
public static void main(String[] args) {
ResultSet rs = null;
Statement stmt = null;
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
// new oracle.jdbc.driver.OracleDriver();
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.101:1521:orcl", "scott", "tiger");
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from dept");
while(rs.next()) {
System.out.println(rs.getString("deptno"));
System.out.println(rs.getInt("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();
}
}
}
}
2.
mysql相关jdbc测试:
import java.sql.*;
public class TestMysqlConnection {
/**
* @param args
*/
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
//还可以是Class.forName("com.mysql.jdbc.Driver").newInstance()
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager
.getConnection("jdbc:mysql://localhost/mydata?user=root&password=root");
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from dept");
while (rs.next()) {
System.out.println(rs.getString("deptno"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException ex) {
// handle any errors
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
} 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();
}
}
}
}
3.
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();
}
}
}
}
4.
存储过程测试
import java.sql.*;
public class TestProc {
/**
* @param args
*/
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger");
//存储过程见图片
CallableStatement 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));
cstmt.close();
conn.close();
}
}
5.可滚动的结果集测试
import java.sql.*;
public class TestScroll {
public static void main(String args[]) {
try {
new oracle.jdbc.driver.OracleDriver();
String url = "jdbc:oracle:thin:@192.168.1.101:1521:orcl";
Connection conn = DriverManager.getConnection(url, "scott", "tiger");
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,//对滚动不敏感的,得到的结果集,你可以来回的滚来滚去
ResultSet.CONCUR_READ_ONLY);//只读
ResultSet 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));
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
6.
JDBC事务处理
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);//把默认的自动提交给取消,因为它的DML语句有自动提交
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();
}
}
}
}
7.
可更新的结果集
import java.sql.*;
public class TestUpdataRs {
/**
* 下面的这个用法没有得到Oracle的支持,但是不代表其他数据库不支持
*/
public static void main(String args[]) {
try {
new oracle.jdbc.driver.OracleDriver();
String url = "jdbc:oracle:thin:@192.168.0.1:1521:SXT";
Connection conn = DriverManager
.getConnection(url, "scott", "tiger");
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);//这个statement创建的结果集是可以更新的;
ResultSet 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();//将新建的行,插入到数据库,但是class12.jar不支持这个用法
// 将光标移动到新建的行
rs.moveToCurrentRow();
// 删除行
rs.absolute(5);
rs.deleteRow();
// 取消更新
// rs.cancelRowUpdates();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
8.
DML操作
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDML {
public static void main(String[] args) {
Statement stmt = 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");
stmt = conn.createStatement();
String sql = "insert into dept2 values (98, 'GAME', 'BJ')";
stmt.executeUpdate(sql);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(stmt != null) {
stmt.close();
stmt = null;
}
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
9.
DML操作
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDML2 {
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];
Statement stmt = null;
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
//new oracle.jdbc.driver.OracleDriver();
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:orcl", "scott", "tiger");
stmt = conn.createStatement();
String sql = "insert into dept values (" + deptno + ",'" + dname + "','" + loc + "')";
System.out.println(sql);
stmt.executeUpdate(sql);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(stmt != null) {
stmt.close();
stmt = null;
}
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
10.
批处理操作
import java.sql.*;
public class TestBatch {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
/*
Statement 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();
stmt.close();
*/
PreparedStatement ps = conn.prepareStatement("insert into dept 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();
ps.close();
conn.close();
}
}