oracle连接数据库增删改查

目录

• • oracle连接数据库增删改查
  • java与Oracle数据库插入 *** 作

oracle连接数据库增删改查

package i;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class connect {

	private final static String URL = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL";
	static final private String USERNAME = "scott";
	private final static String PSW = "tiger";
	
	public static void add() {
		Connection connection = null;
		PreparedStatement pstmt = null;
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			connection = DriverManager.getConnection(URL,USERNAME,PSW);
			String sql = "insert into student(sno,sname,sage,saddress) values(?,?,?,?)";	
			pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, 8);
			pstmt.setString(2,"鲁班");
			pstmt.setInt(3,6);
			pstmt.setString(4,"天美工作室");
			
			int count=pstmt.executeUpdate();
			
			if(count>0)
				System.out.println("添加成功！");
			else
				System.out.println("添加失败");
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.out.println("添加失败");
		} catch (SQLException e) {
			
			e.printStackTrace();
			System.out.println("添加失败");
		}catch (Exception e) {
			
			e.printStackTrace();
			System.out.println("添加失败");
		}finally {
				try {
					
					if(pstmt!=null) pstmt.close();
					if(connection!=null) connection.close();	
					
				} catch (SQLException e) {	
					
					e.printStackTrace();
					
				}
		}
	}
	
	public static void delete() {
		Connection connection = null;
		PreparedStatement pstmt = null;
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			connection = DriverManager.getConnection(URL,USERNAME,PSW);
			String sql = "delete from student where sno=? ";	
			pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, 8);
			
			int count=pstmt.executeUpdate();
			
			if(count>0)
				System.out.println("删除成功！");
			else
				System.out.println("删除失败");
			
		} catch (ClassNotFoundException e) {
			
			e.printStackTrace();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}catch (Exception e) {
			
			e.printStackTrace();
		}finally {
				try {
					
					if(pstmt!=null) pstmt.close();
					if(connection!=null) connection.close();	
					
				} catch (SQLException e) {	
					
					e.printStackTrace();
					
				}
		}
	}
	
	public static void update() {
		Connection connection = null;
		PreparedStatement pstmt = null;
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			connection = DriverManager.getConnection(URL,USERNAME,PSW);
			String sql = "update student set sno=?,sname=?,sage=?,saddress=?  where sno=?";

			pstmt = connection.prepareStatement(sql);
			//修改后的内容
			pstmt.setInt(1,100);
			pstmt.setString(2, "项羽");
			pstmt.setInt(3,1000);
			pstmt.setString(4,"楚国");
			//要修改的人
			pstmt.setInt(5, 7);
			
			
			int count=pstmt.executeUpdate();
			
			if(count>0)
				System.out.println("修改成功！");
			else
				System.out.println("修改失败");
			
		} catch (ClassNotFoundException e) {
			
			e.printStackTrace();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}catch (Exception e) {
			
			e.printStackTrace();
		}finally {
				try {
					
					if(pstmt!=null) pstmt.close();
					if(connection!=null) connection.close();	
					
				} catch (SQLException e) {	
					
					e.printStackTrace();
					
				}
		}
	}
	public static void query() {
		Connection connection = null;
		PreparedStatement pstmt = null;
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			connection = DriverManager.getConnection(URL,USERNAME,PSW);
			String sql = "select * from student order by sno";
			
			pstmt = connection.prepareStatement(sql);	
			
			ResultSet rs = pstmt.executeQuery();
			//处理结果集
			while(rs.next()) {
				int sno = rs.getInt("sno");
				int sage = rs.getInt("sage");
				String sname = rs.getString("sname");
				String saddress = rs.getString("saddress");
				System.out.println(sno+"-"+sname+sage+saddress);		
			}
			
		} catch (ClassNotFoundException e) {
			
			e.printStackTrace();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}catch (Exception e) {
			
			e.printStackTrace();
		}finally {
				try {
					
					if(pstmt!=null) pstmt.close();
					if(connection!=null) connection.close();	
					
				} catch (SQLException e) {	
					
					e.printStackTrace();
					
				}
		}
	}
	
	public static void main(String[] args) {
	//	add();		
	//delete();
		//update();
		query();
		
	}
}

java与Oracle数据库插入 *** 作

关键步骤

//1，导入驱动，加载具体驱动类
Class.forName("oracle.jdbc.OracleDriver");//加载具体驱动类
//2，与数据库建立连接
connection = DriverManager.getConnection(URL,USERNAME,PWD);
//3，执行sql语句,用来创建一个连接的statement即stmt	，有了stmt就可以执行了。
stmt = connection.createStatement();
String sql = "insert into student values(10,'李白',20,'s2')";
stmt.executeUpdate(sql)；

增加 *** 作

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCdemo01 {
	private static final String URL="jdbc:oracle:thin:@localhost:1521:ORCL";
	private static final String USERNAME="scott";
	private static final String PWD="tiger";
	
	public static void update()  {//增删改
		Connection connection =null;
		Statement stmt = null;
		try {
		//1，导入驱动，加载具体驱动类
		Class.forName("oracle.jdbc.OracleDriver");//加载具体驱动类
		//2，与数据库建立连接
		connection = DriverManager.getConnection(URL,USERNAME,PWD);
		//3，执行sql语句,用来创建一个连接的statement即stmt
		stmt = connection.createStatement();
		//有了stmt就可以执行了。
		String sql = "insert into student values(10,'李白',20,'s2')";
		
		int count = stmt.executeUpdate(sql);//返回值表示增删改了  多少条数据
		//4，处理结果集
		if(count>0) {
			
			System.out.println(" *** 作成功！");
		}
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		}catch(SQLException e) {
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				if(stmt!=null)	stmt.close();
				if(connection!=null) connection.close();
		}catch(SQLException e) {
			e.printStackTrace();
		}
			
			}
		
		
	}
	
	public static void main(String[] args) {
		update(); 
	}

}

修改 *** 作

	String sql = "update student set stuname='韩信' where stuno=1";

删除 *** 作（只需要改sql即可）

String sql = "delete from student where stuno=2";