package com.deng.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCTest {
public static void main(String[] args) {
try {
jdbcTest();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void jdbcTest() throws SQLException{
Connection conn = null;
PreparedStatement ps1 = null;
PreparedStatement ps2 = null;
PreparedStatement ps3 = null;
try {
String username = "test";//数据库用户名
String password = "123456";//密码
String ip = "192.168.1.xxx";//IP地址
String port = "1521";//oracle 端口 固定1521
String instance = "orcl";//数据库实例
StringBuffer url = new StringBuffer();
url.append("jdbc:oracle:thin:@");
url.append(ip);
url.append(":");
url.append(port);
url.append(":");
url.append(instance);
System.out.println("url = " + url.toString());
Class.forName("oracle.jdbc.driver.OracleDriver");//加载Oracle驱动,需要引入Oracle驱动的jar包
conn = DriverManager.getConnection(url.toString(),username,password);
String sql = "select fid, fname from (select fid, fname from t_test_table order by fcreatetime desc) where rownum < 2";
ps1 = conn.prepareStatement(sql);
ResultSet rs1 = ps1.executeQuery();//执行查询
//返回结果用next()方法取出,一行用if,多行用while遍历
if(rs1.next()){
String id = rs1.getString("fid");//通过列名获取数据,或者通过cloumnIdex
// String id = rs1.getString(1);
System.out.println("beforeUpdate: name = " + rs1.getString("fname"));
sql = "update t_test_table set fname = ? where fid = ? ";
ps2 = conn.prepareStatement(sql);//预编译
ps2.setString(1, "修改之后");//传参 从1开始 一个问号对应一个值
ps2.setString(2, id);
int effectRows = ps2.executeUpdate();
System.out.println(effectRows);
sql = "select fname from t_test_table where fid = ?";
ps3 = conn.prepareStatement(sql);
ps3.setString(1, id);
ResultSet rs2 = ps3.executeQuery();
if(rs2.next())
System.out.println("afterUpdate: name = " + rs2.getString("fname"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭连接
if(conn != null)
conn.close();
if(ps1 != null)
ps1.close();
if(ps2 != null)
ps2.close();
if(ps3 != null)
ps3.close();
}
}
}