Oracle使用JDBC进行增删改查

数据库和表

create table USERS
(
USERNAME VARCHAR2 ( 20 ) not null ,
PASSWORD VARCHAR2 ( 20 )
)
alter table USERS
add constraint U_PK primary key (USERNAME)

/**
* JdbcExample.java
*
* Provider: CoderDream's Studio
*
* History
* Date(DD/MM/YYYY) Author Description
* ----------------------------------------------------------------------------
* Apr 14, 2008 CoderDream Created
*/

package com.coderdream.jdbc.oracle;

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

/**
* @author XL
*
*/
public class JdbcExample {


private static Connection getConn() {
String driver = " oracle.jdbc.driver.OracleDriver " ;
String url = " jdbc:oracle:thin:@10.5.15.117:1521:csi " ;
String username = " scott " ;
String password = " tiger " ;
Connection conn = null ;
try {
Class.forName(driver);
// new oracle.jdbc.driver.OracleDriver();
conn = DriverManager.getConnection(url, username, password);
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
catch (SQLException e) {
e.printStackTrace();
}

return conn;
}

private static int insert(String username, String password) {
Connection conn = getConn();
int i = 0 ;
String sql = " insert into users (username,password) values(?,?) " ;
PreparedStatement pstmt;
try {
pstmt = conn.prepareStatement(sql);
// Statement stat = conn.createStatement();
pstmt.setString( 1 , username);
pstmt.setString( 2 , password);
i = pstmt.executeUpdate();
System.out.println( " resutl: " + i);

pstmt.close();
conn.close();
}
catch (SQLException e) {
e.printStackTrace();
}

return i;
}

private static void query() {
Connection conn = getConn();
String sql = " select * from users " ;
PreparedStatement pstmt;
try {
pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println( " name: " + rs.getString( " username " )
+ " \tpassword: " + rs.getString( " password " ));
}

rs.close();
pstmt.close();
conn.close();
}
catch (SQLException e) {
e.printStackTrace();
}

}

private static int update(String oldName, String newPass) {
Connection conn = getConn();
int i = 0 ;
String sql = " update users set password=' " + newPass
+ " ' where username=' " + oldName + " ' " ;
PreparedStatement pstmt;
try {
pstmt = conn.prepareStatement(sql);

i = pstmt.executeUpdate();
System.out.println( " resutl: " + i);

pstmt.close();
conn.close();
}
catch (SQLException e) {
e.printStackTrace();
}

return i;
}

private static int delete(String username) {
Connection conn = getConn();
int i = 0 ;
String sql = " delete users where username=' " + username + " ' " ;
PreparedStatement pstmt;
try {
pstmt = conn.prepareStatement(sql);

i = pstmt.executeUpdate();
System.out.println( " resutl: " + i);

pstmt.close();
conn.close();
}
catch (SQLException e) {
e.printStackTrace();
}

return i;
}

/**
* @param args
*/
public static void main(String[] args) {
insert( " CDE " , " 123 " );
insert( " CoderDream " , " 456 " );
query();
update( " CoderDream " , " 456 " );
query();
delete( " CoderDream " );
query();
}
}