JDBC连接oracle数据库（增删改查）

新建数据库user，创建表users为方便仅定义两个字段username，password

sqlplus scott/tiger

CREATE TABLE users(username varchar2(32) primary key,password varchar2(64);

新建类Doo.java

<pre class="java" name="code">package ww.jdbc;

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

public class Doo {
	public  void find() {

		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:user", "scott", "tiger");
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from users");
			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();
			}
		}
	}

	public void reg(String username,String password) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			StringBuilder sql = new StringBuilder();
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:user", "scott", "tiger");

			sql.append("insert into users(username,password) ");
			sql.append("values(?,?)");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, username);
			pstmt.setString(2, password);
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void edit(String username,String Nusername,String Npassword) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:user", "scott", "tiger");

			StringBuilder sql = new StringBuilder();
			sql.append("update users set username=?,password=? where username=?");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(3, username);
			pstmt.setString(1, Nusername);
			pstmt.setString(2, Npassword);
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pstmt.close();
			conn.close();
		}
	}
	
	public void delete(String username) throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:user", "scott", "tiger");
			pstmt = conn.prepareStatement("delete from users where username=?");
			pstmt.setString(1, username);
			pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			pstmt.close();
			conn.close();
		}
	}

}

 

 

测试类Test.java

package ww.jdbc;

import java.sql.SQLException;
import java.util.Scanner;



public class Test {

	public static void main(String[] args) throws SQLException {
		Doo doo=new Doo();
		
		while(true){
			System.out.println("1查看 	2 插入 	3 修改 	4 删除");
			Scanner sc=new Scanner(System.in); 

			int str=sc.nextInt(); 
			if(str==1){
				doo.find();
			}else if(str==2){
				System.out.println("请输入用户名");
				String username=sc.next();
				System.out.println("请输入密码");
				String password=sc.next();
 				doo.reg(username,password);
			}else if(str==3){
				System.out.println("请输入原用户名");
				String username=sc.next();
				System.out.println("请输入新用户名");
				String Nusername=sc.next(); 
				System.out.println("请输入新密码");
				String Npassword=sc.next();
				doo.edit(username,Nusername,Npassword);
			}else if(str==4){
				System.out.println("请输入需要删除的用户名");
				String username=sc.next();
				doo.delete(username);
			}

		}
	}
}