执行oracle数据库操作

亦凉 2022-05-12 08:10 241阅读 0赞

1.![70][]将该包复制到javaprogram项目里，然后buildd path-->add to build path

package com.stu.entity;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.text.SimpleDateFormat;
    
    //工具类
    public class StuConn {
    	public static Connection conn;
    	public static ResultSet rs;
    	public static Statement st;
    
    	// 建立连接
    	public static void getConn() {
    		String[] user = new String[2];
    		
    		try {
    			Class.forName("oracle.jdbc.driver.OracleDriver");
    			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "root");
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    
    		
    
    	}
    	// 释放连接
    
    	public static void closeConn() { // 关闭连接
    		try {
    			if (rs != null) {
    				rs.close();
    			}
    			if (st != null) {
    				st.close();
    			}
    			if (conn != null) {
    				conn.close();
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    
    	}
    
    	// 查询数据 emp
    	public static void searchAll() throws Exception {
    		getConn();
    		// 3.写sql语句
    		String sql = "select * from students";
    		// 4.创建statement实例
    		st = conn.createStatement();
    		// 5.执行sql 执行查询方法，有返回值,返回的是一个结果集
    		rs = st.executeQuery(sql);
    
    		// 6.遍历结果集，输出结果
    		System.out.println("学号 \t" + "姓名 \t" + "年龄\t"+ "性别\t"+ "年级\t  "+ "电话\t " + "\tEmail\t"
    				+ " " + "\t地址");
    		while (rs.next()) {
    			System.out.println(rs.getInt("stuNo") + "\t" + rs.getString("name") + "\t" + rs.getInt("age") + "\t"
    					+ rs.getString("sex") + "\t" + rs.getString("grade")+"\t"+rs.getLong("tel")+" \t "+rs.getString("email")+"\t"+rs.getString("address"));
    		}
    		// 7.关闭连接 先开的后关，后开的先关
    		closeConn();
    	}
    
    	// 查询数据 emp 中某一条记录
    	public static Stu searchById(int id) throws Exception {
    		Stu stu= new Stu();
    		getConn();
    		// 3.写sql语句
    		String sql = "select * from students where stuNo=" + id;
    		// 4.创建statement实例
    		st = conn.createStatement();
    //		System.out.println("st  byid: " + st);
    		// 5.执行sql 执行查询方法，有返回值,返回的是一个结果集
    		rs = st.executeQuery(sql);
    
    		// 6.遍历结果集，输出结果
    	/*	System.out.println("学号 \t" + "姓名 \t" + "年龄\t"+ "性别\t"+ "年级\t  "+ "电话\t " + "Email"
    				+ " " + "地址");*/
    		if (rs.next()) {
    		/*	System.out.println(rs.getInt("stuNo") + "\t" + rs.getString("name") + "\t" + rs.getInt("age") + "\t"
    					+ rs.getString("sex") + "\t" + rs.getString("grade")+"\t"+rs.getLong("tel")+" \t "+rs.getString("email")+"\t"+rs.getString("address"));*/
    			stu.setStuNo( rs.getInt("stuNo") );
    			stu.setName(rs.getString("name"));
    			stu.setAge(rs.getInt("age"));
    			stu.setSex(rs.getString("sex"));
    			stu.setGrade(rs.getString("grade"));
    			stu.setTel(rs.getLong("tel"));
    			stu.setAddress(rs.getString("address"));
    		}
    		// 7.关闭连接 先开的后关，后开的先关
    		closeConn();
    		return stu;
    	}
    
    	// 更新数据  (增删改)
    	public static void updateEmp(String sql) throws Exception {
    		getConn();
    		// 3.写sql语句
    		// 4.创建statement实例
    		st = conn.createStatement();
    		// 调用此方法实现数据的删除,返回值类型是int类型，返回的是受影响的行数
    		// 如果成功的删除了若干（3）行，就会返回3；如果删除失败，则返回0;
    		// 我们就可以根据返回值是否为0，判断是否删除成功
    		int i = st.executeUpdate(sql);
    		if (i > 0) {
    			System.out.println("更新成功");
    		} else {
    			System.out.println("更新失败");
    		}
    		// 7.关闭连接 先开的后关，后开的先关
    		closeConn();
    	}
    }

[70]: /images/20220512/f6b0cc634a464e5182c898ab46b87abb.png