jdbc連接oracle數(shù)據(jù)庫(kù)步驟代碼

更新時(shí)間:2021-08-06 來(lái)源:黑馬程序員瀏覽量:

IT培訓(xùn)班

(一)創(chuàng)建工程，引入驅(qū)動(dòng)包

在下圖目錄中可以找到驅(qū)動(dòng)包ORACLE的JDBC驅(qū)動(dòng)包，拷貝到工程即可使用

jdbc鏈接oracel數(shù)據(jù)庫(kù)01

創(chuàng)建java工程waterboss，建立lib文件夾，將ojdbc.jar拷貝到此文件夾，然后add build path

(二)BaseDao

我們通常編寫(xiě)B(tài)aseDao負(fù)責(zé)加載驅(qū)動(dòng)，獲取數(shù)據(jù)庫(kù)連接，關(guān)閉資源，代碼如下：

package cn.itcast.waterboss.dao;

import java.sql.SQLException;

/**
 * 基本數(shù)據(jù)訪問(wèn)類(lèi)
 * @author Administrator
 *
 */
public class BaseDao {
  //加載驅(qū)動(dòng)
  static {
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    }
  }

  /**
   * 獲取數(shù)據(jù)庫(kù)連接
   * @return
   * @throws SQLException
   */
  public static java.sql.Connection getConnection() throws SQLException {
    return java.sql.DriverManager.getConnection(
      "jdbc:oracle:thin:@192.168.80.10:1521:orcl",
      "wateruser",
      "itcast"
    );
  }

  /**
   * 關(guān)閉資源
   * @param rs
   * @param stmt
   * @param conn
   */
  public static void closeAll(
    java.sql.ResultSet rs,
    java.sql.Statement stmt,
    java.sql.Connection conn
  ) {
    //關(guān)閉結(jié)果集
    if (rs != null) {
      try {
        rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    //關(guān)閉執(zhí)行對(duì)象
    if (stmt != null) {
      try {
        stmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    //關(guān)閉執(zhí)行對(duì)象
    if (conn != null) {
      try {
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

JDBC驅(qū)動(dòng)為：

oracle.jdbc.OracleDriver

連接字符串( 瘦連接 )：

jdbc:oracle:thin:@虛擬機(jī)的 IP:1521:orcl

(三)業(yè)主增刪改代碼編寫(xiě)

1.創(chuàng)建實(shí)體類(lèi)

package cn.itcast.waterboss.entity;

import java.util.Date;

/**
 * 業(yè)主實(shí)體類(lèi)
 * @author Administrator
 *
 */
public class Owners {

  private Long id; //編號(hào)
  private String name; //業(yè)主名稱(chēng)
  private Long addressid; //地址編號(hào)
  private String housenumber; //門(mén)牌號(hào)
  private String watermeter; //水表編號(hào)
  private Date adddate; //登記日期
  private Long ownertypeid; //業(yè)主類(lèi)型 ID

  public Long getId() {
    return id;
  }

  public void setId(Long id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public Long getAddressid() {
    return addressid;
  }

  public void setAddressid(Long addressid) {
    this.addressid = addressid;
  }

  public String getHousenumber() {
    return housenumber;
  }

  public void setHousenumber(String housenumber) {
    this.housenumber = housenumber;
  }

  public String getWatermeter() {
    return watermeter;
  }

  public void setWatermeter(String watermeter) {
    this.watermeter = watermeter;
  }

  public Date getAdddate() {
    return adddate;
  }

  public void setAdddate(Date adddate) {
    this.adddate = adddate;
  }

  public Long getOwnertypeid() {
    return ownertypeid;
  }

  public void setOwnertypeid(Long ownertypeid) {
    this.ownertypeid = ownertypeid;
  }
}

2.創(chuàng)建Dao類(lèi)實(shí)現(xiàn)增刪改

package cn.itcast.waterboss.dao;

import cn.itcast.waterboss.entity.Owners;
import java.sql.SQLException;

/**
 * 業(yè)主數(shù)據(jù)訪問(wèn)類(lèi)
 * @author Administrator
 *
 */
public class OwnersDao {

  /**
   * 新增業(yè)主
   * @param owners
   */
  public static void add(Owners owners) {
    java.sql.Connection conn = null;
    java.sql.PreparedStatement stmt = null;
    try {
      conn = BaseDao.getConnection();
      stmt =
        conn.prepareStatement("insert into T_OWNERS 
values(?,?,?,?,?,?,?)");
      stmt.setLong(1, owners.getId());
      stmt.setString(2, owners.getName());
      stmt.setLong(3, owners.getAddressid());
      stmt.setString(4, owners.getHousenumber());
      stmt.setString(5, owners.getWatermeter());
      stmt.setDate(6, new java.sql.Date(owners.getAdddate().getTime()));
      stmt.setLong(7, owners.getOwnertypeid());
      stmt.execute();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      BaseDao.closeAll(null, stmt, conn);
    }
  }

  /**
   * 修改業(yè)主
   * @param owners
   */
  public static void update(Owners owners) {
    java.sql.Connection conn = null;
    java.sql.PreparedStatement stmt = null;
    try {
      conn = BaseDao.getConnection();
      stmt =
        conn.prepareStatement(
          "update T_OWNERS set 
    name=?,addressid=?,housenumber=?," +
          "watermeter=?,adddate=?, ownertypeid=? where 
    id=?"
        );
      stmt.setString(1, owners.getName());
      stmt.setLong(2, owners.getAddressid());
      stmt.setString(3, owners.getHousenumber());
      stmt.setString(4, owners.getWatermeter());
      stmt.setDate(5, new java.sql.Date(owners.getAdddate().getTime()));
      stmt.setLong(6, owners.getOwnertypeid());
      stmt.setLong(7, owners.getId());
      stmt.execute();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      BaseDao.closeAll(null, stmt, conn);
    }
  }

  /**
   * 刪除業(yè)主
   * @param owners
   */
  public static void delete(Long id) {
    java.sql.Connection conn = null;
    java.sql.PreparedStatement stmt = null;
    try {
      conn = BaseDao.getConnection();
      stmt = conn.prepareStatement("delete from T_OWNERS where 
id=?");
      stmt.setLong(1, id);
      stmt.execute();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      BaseDao.closeAll(null, stmt, conn);
    }
  }
}