package procudure;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
public class JDBCOracle {
public static final String SEQUENCESQL = "select jdbc_serial.nextval from dual";
public static final String insertSQL = "insert into jdbc_table(id,name,age,sax)values(?,?,?,?)";
public static final String querySQL = "select t.id,t.name,t.age,t.sax from jdbc_table t";
public static void main(String[] args) {
SQLStatement();
}
/*
* 增加查询数据
*/
public static void SQLStatement() {
Connection conn = getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn.setAutoCommit(false);
/*
* 插入值
*/
ps = conn.prepareStatement(insertSQL);
ps.setInt(1,JDBCSequence());
ps.setString(2, "Kate");
ps.setInt(3, 30);
ps.setString(4, "girl");
ps.execute();
conn.commit();
/*
* 查询值
*/
ps = conn.prepareStatement(querySQL);
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
for(int i = 1;i<=rsmd.getColumnCount();i++) {
System.out.print(rsmd.getColumnLabel(i)+"\t");
}
System.out.println();
while(rs.next()) {
System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getInt(3)+"\t"+rs.getString(4));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(null!=rs) {
try {
rs.close();
rs=null;
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null!=ps) {
try{
ps.close();
ps=null;
}catch(SQLException e) {
e.printStackTrace();
}
}
if(null!=conn) {
try {
conn.close();
conn = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/*
* jdbc取sequence的值
*/
public static int JDBCSequence() {
Connection conn = getConnection();
PreparedStatement ps = null;
int id = 0 ;
try {
ps = conn.prepareStatement(SEQUENCESQL);
ResultSet rs = ps.executeQuery();
rs.next();
id = rs.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(null!= conn) {
try {
conn.close();
conn=null;
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null!= ps) {
try {
ps.close();
ps=null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return id;
}
/*
* 拿到链接
*/
public static Connection getConnection() {
Connection conn = null;
String driver = "oracle.jdbc.driver.OracleDriver";
try {
Class.forName(driver);
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:july", "sunit","snaillocke");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}