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(, username);  
            pstmt.setString(, 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(, username);  
            pstmt.setString(, Nusername);  
            pstmt.setString(, 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(, 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==){  
                doo.find();  
            }else if(str==){  
                System.out.println("請輸入使用者名");  
                String username=sc.next();  
                System.out.println("請輸入密碼");  
                String password=sc.next();  
                doo.reg(username,password);  
            }else if(str==){  
                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==){  
                System.out.println("請輸入需要删除的使用者名");  
                String username=sc.next();  
                doo.delete(username);  
            }  

        }  
    }  
}