一些小例子程序，用于说明JDBC的使用步骤。

最新推荐文章于 2022-10-02 11:26:09 发布

E-Kunt

最新推荐文章于 2022-10-02 11:26:09 发布

阅读量448

点赞数 1

分类专栏： SQL 文章标签： jdbc Java

本文链接：https://blog.csdn.net/e_kunt/article/details/50808712

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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

E-Kunt

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
一些小例子程序，用于说明JDBC的使用步骤。

Class.forName("oracle.jdbc.driver.OracleDriver");Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("select * from dept2");wh
复制链接

扫一扫