import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
//與數(shù)據(jù)庫建立連接
public class ConnectionTest {
  //獲取連接的三個(gè)基本信息
  private String dbURL = "jdbc:oracle:thin:@localhost:1521:orcl";
  private String dbUser = "test";
  private String dbPwd = "test";
  // 獲取數(shù)據(jù)庫連接方法, 返回Connection對象
  private Connection con = null;
  //創(chuàng)建數(shù)據(jù)庫連接
  public Connection getDBConnect() {
    try {
      // 加載數(shù)據(jù)庫驅(qū)動 不同的數(shù)據(jù)庫下面這個(gè)驅(qū)動是不同的，這個(gè)千萬要注意!!!
      Class.forName("oracle.jdbc.driver.OracleDriver");
      con = DriverManager.getConnection(dbURL, dbUser, dbPwd);
    } catch (Exception e) {
      System.out.println(e);
    }
    return con;
  }
}

public void testInsert() {
	  PreparedStatement ps1 = null; //使用該類執(zhí)行增刪改操作不會發(fā)生sql注入現(xiàn)象！！
    //在進(jìn)行插入操作的時(shí)候 保證你的表、以及對應(yīng)的列名都存在 ？在這里表示占位符
    String sql = "insert into t_user(ID,USERNAME,PASSWORD1,EMAIL) values(?,?,?,?)";
    try {
     //1、預(yù)編譯sql語句 返回PreparedStatement的實(shí)例給ps1 con表示Connection的對象
      ps1 = con.prepareStatement(sql);
      //2、填充占位符=？
      ps1.setString(1, "2");
      ps1.setString(2, "ww");
      ps1.setString(3, "12345");
      ps1.setString(4, "907372669@qq.com");
      //執(zhí)行操作
      ps1.execute();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    //資源的關(guān)閉
    try {
      if (ps1 != null)
        ps1.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    try {
      if (con != null)
        con.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

public void testUpdate() {
    PreparedStatement ps2 = null;
    //編寫sql語句 返回PreparedStatement的實(shí)例
    String sql = "update T_USER set USERNAME = ? where ID = ? ";
    try {
      ps2 = con.prepareStatement(sql);
       //填充占位符=？
      ps2.setObject(1,"newgg");
      ps2.setObject(2,"1");
      //執(zhí)行
      ps2.execute();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    //實(shí)現(xiàn)資源關(guān)閉的方法
    closeSources(ps2,con);
  }
  
  //實(shí)現(xiàn)資源關(guān)閉的方法如下:
public void closeSources(PreparedStatement ps, Connection con) {
    try {
      if (ps != null)
        ps.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    try {
      if (con != null)
        con.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
}

public void testUpdate() {
 //編寫sql語句 返回PreparedStatement的實(shí)例
    PreparedStatement ps2 = null;
    String sql = "update T_USER set USERNAME = ? where ID = ? ";
    try {
      ps2 = con.prepareStatement(sql);
      //填充占位符=?
      ps2.setObject(1,"newgg");
      ps2.setObject(2,"1");
      //執(zhí)行
      ps2.execute();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    //實(shí)現(xiàn)資源的關(guān)閉
    closeSources(ps2,con);
  }

 //實(shí)現(xiàn)增刪改的通用操作
// sql占位符中的個(gè)數(shù)與可變長參數(shù)的長度一致?。?
  public void insert_Delete_Update(String sql, Object ...args) {
   //預(yù)編譯SQL語句，返回PreparedStatement的實(shí)例
    PreparedStatement ps4 = null;
    try {
      ps4 = con.prepareStatement(sql);
      //填充占位符
      for(int i=0; i<args.length; i++) {
        ps4.setObject(i+1,args[i]);
      }
      //執(zhí)行
      ps4.execute();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    //實(shí)現(xiàn)資源的關(guān)閉
    closeSources(ps4,con);
  }

 public void test_Insert_Delete_Update() {
 
    String sql1 = "update T_USER set username = ? where ID = ? ";
    //仔細(xì)觀察這個(gè)調(diào)用的方法 username=“newQ” ID=“2”這兩個(gè)參數(shù)傳入這個(gè)方法里 
    insert_Delete_Update(sql1,"newQ","2");
  }