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

欢迎使用Markdown编辑器写博客

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

sqlplus scott/tiger

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

新建类Doo.java

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

        }  
    }  
}