Dao第一次作业

/**
 * 在eclipse里创建一个出版社的模型类
 */

/**
*
*/
package com.yidu.model;

/**

• 描述：出版社类
• @author zhouyuxian
• @date 2019年8月22日 下午4:18:35
• @version 1.0
  */
  public class Public {
  private Integer pubID;
  private String pubName;
  private String pubArea;
  private String pubHead;
  public Integer getPubID() {
  return pubID;
  }
  public void setPubID(Integer pubID) {
  this.pubID = pubID;
  }
  public String getPubName() {
  return pubName;
  }
  public void setPubName(String pubName) {
  this.pubName = pubName;
  }
  public String getPubArea() {
  return pubArea;
  }
  public void setPubArea(String pubArea) {
  this.pubArea = pubArea;
  }
  public String getPubHead() {
  return pubHead;
  }
  public void setPubHead(String pubHead) {
  this.pubHead = pubHead;
  }

}

/**
 * 创建一个出版社的Dao
 */

package com.yidu.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.yidu.model.Public;

/**

• 描述：出版社DAO

• @author zhouyuxian

• @date 2019年8月22日 下午4:25:55

• @version 1.0
  */
  public class PublicDao {

  /**

  • 添加出版社
  • @param pub
  • @return
    */
    public int insert(Public pub){
    int rows = 0;
    try {
    Class.forName(“com.mysql.jdbc.Driver”);
    String url = “jdbc:mysql://localhost:3306/stu_db”;
    String user = “root”;
    String password = “123”;
    Connection con = DriverManager.getConnection(url, user, password);
    String sql = “insert into public values(null,?,?,?)”;
    PreparedStatement pstm = con.prepareStatement(sql);
    pstm.setString(1, pub.getPubName());
    pstm.setString(2, pub.getPubArea());
    pstm.setString(3, pub.getPubHead());
    rows = pstm.executeUpdate();
    pstm.close();
    con.close();
    } catch (ClassNotFoundException e) {
    e.printStackTrace();
    } catch (SQLException e) {
    e.printStackTrace();
    }
    return rows;
    }

  /**

  • 根据ID删除出版社
  • @param pubID
  • @return
    */
    public int delete(int pubID){
    int rows = 0;
    try {
    Class.forName(“com.mysql.jdbc.Driver”);
    String url = “jdbc:mysql://localhost/stu_db”;
    String user = “root”;
    String password = “123”;
    Connection con = DriverManager.getConnection(url, user, password);
    String sql = “delete from public where p_id=?”;
    PreparedStatement pstm = con.prepareStatement(sql);
    pstm.setInt(1, pubID);
    rows = pstm.executeUpdate();
    pstm.close();
    con.close();
    } catch (ClassNotFoundException e) {
    e.printStackTrace();
    } catch (SQLException e) {
    e.printStackTrace();
    }
    return rows;
    }

  /**

  • 修改出版社
  • @param pub
  • @return
    */
    public int update(Public pub){
    int rows = 0;
    try {
    Class.forName(“com.mysql.jdbc.Driver”);
    String url = “jdbc:mysql://localhost:3306/stu_db”;
    String user = “root”;
    String password = “123”;
    Connection con = DriverManager.getConnection(url, user, password);
    String sql = “update public set p_name=?,p_address=?,p_head=? where p_id=?”;
    PreparedStatement pstm = con.prepareStatement(sql);
    pstm.setString(1, pub.getPubName());
    pstm.setString(2, pub.getPubArea());
    pstm.setString(3, pub.getPubHead());
    pstm.setInt(4, pub.getPubID());
    rows = pstm.executeUpdate();
    pstm.close();
    con.close();
    } catch (ClassNotFoundException e) {
    e.printStackTrace();
    } catch (SQLException e) {
    e.printStackTrace();
    }
    return rows ;
    }

  /**

  • 查询所有出版社
  • @return
    */
    public List selectAll(){
    List list = new ArrayList();
    try {
    Class.forName(“com.mysql.jdbc.Driver”);
    String url = “jdbc:mysql://localhost:3306/stu_db”;
    String user = “root”;
    String password = “123”;
    Connection con = DriverManager.getConnection(url, user, password);
    String sql = “select * from public”;
    PreparedStatement pstm = con.prepareStatement(sql);
    ResultSet rs = pstm.executeQuery();
    while(rs.next()){
    Public pub = new Public();
    int pubID = rs.getInt(“p_id”);
    pub.setPubID(pubID);
    String pubName = rs.getString(“P_name”);
    pub.setPubName(pubName);
    String pubArea = rs.getString(“p_address”);
    pub.setPubArea(pubArea);
    String pubHead = rs.getString(“p_head”);
    pub.setPubHead(pubHead);
    list.add(pub);
    }
    rs.close();
    pstm.close();
    con.close();
    } catch (ClassNotFoundException e) {
    e.printStackTrace();
    } catch (SQLException e) {
    e.printStackTrace();
    }
    return list;
    }

  /**

  • 根据ID 查询
  • @param pubID
  • @return
    */
    public Public findID(int pubID){
    try {
    Class.forName(“com.mysql.jdbc.Driver”);
    Class.forName(“com.mysql.jdbc.Driver”);
    String url = “jdbc:mysql://localhost:3306/stu_db”;
    String user = “root”;
    String password = “123”;
    Connection con = DriverManager.getConnection(url, user, password);
    String sql = “select * from public where p_id=?”;
    PreparedStatement pstm = con.prepareStatement(sql);
    pstm.setInt(1, pubID);
    ResultSet rs = pstm.executeQuery();
    while(rs.next()){
    Public pub = new Public();
    int pubId = rs.getInt(“p_id”);
    pub.setPubID(pubId);
    String pubName = rs.getString(“P_name”);
    pub.setPubName(pubName);
    String pubArea = rs.getString(“p_address”);
    pub.setPubArea(pubArea);
    String pubHead = rs.getString(“p_head”);
    pub.setPubHead(pubHead);
    return pub;
    }
    rs.close();
    pstm.close();
    con.close();
    } catch (ClassNotFoundException e) {
    e.printStackTrace();
    } catch (SQLException e) {
    e.printStackTrace();
    }
    return null;
    }
    }

/**
* 创建一个书籍的类
*/

/**
*
*/
package com.yidu.model;

import java.sql.Date;

/**

• 描述：书籍类
• @author zhouyuxian
• @date 2019年8月22日 下午5:16:51
• @version 1.0
  */
  public class Book {
  private Integer bkID;
  private String bkName;
  private String bkAuthor;
  private Date bkDate;
  private Integer pubID;
  public Integer getBkID() {
  return bkID;
  }
  public void setBkID(Integer bkID) {
  this.bkID = bkID;
  }
  public String getBkName() {
  return bkName;
  }
  public void setBkName(String bkName) {
  this.bkName = bkName;
  }
  public String getBkAuthor() {
  return bkAuthor;
  }
  public void setBkAuthor(String bkAuthor) {
  this.bkAuthor = bkAuthor;
  }
  public Date getBkDate() {
  return bkDate;
  }
  public void setBkDate(Date bkDate) {
  this.bkDate = bkDate;
  }
  public Integer getPubID() {
  return pubID;
  }
  public void setPubID(Integer pubID) {
  this.pubID = pubID;
  }

}

/**
* 创建一个出版社的Dao
*/

/**

*/
package com.yidu.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.yidu.model.Book;

/**

• 描述：书籍DAO

• @author zhouyuxian

• @date 2019年8月22日 下午5:26:41

• @version 1.0
  */
  public class BookDao {

  /**

  • 添加书籍
  • @param book
  • @return
    */
    public int insert(Book book){
    int rows = 0;
    try {
    Class.forName(“com.mysql.jdbc.Driver”);
    String url = “jdbc:mysql://localhost:3306/stu_db”;
    String user = “root”;
    String password = “123”;
    Connection con = DriverManager.getConnection(url, user, password);
    String sql = “insert into book values(null,?,?,?,?)”;
    PreparedStatement pstm = con.prepareStatement(sql);
    pstm.setString(1, book.getBkName());
    pstm.setString(2, book.getBkAuthor());
    pstm.setDate(3, book.getBkDate());
    pstm.setInt(4, book.getPubID());
    rows = pstm.executeUpdate();
    pstm.close();
    con.close();
    } catch (ClassNotFoundException e) {
    e.printStackTrace();
    } catch (SQLException e) {
    e.printStackTrace();
    }
    return rows;
    }

  /**

  • 删除书籍
  • @param bookID
  • @return
    */
    public int delete(int bookID){
    int rows = 0;
    try {
    Class.forName(“com.mysql.jdbc.Driver”);
    String url = “jdbc:mysql://localhost:3306/stu_db”;
    String user = “root”;
    String password = “123”;
    Connection con = DriverManager.getConnection(url, user, password);
    String sql = “delete from book where book_id=?”;
    PreparedStatement pstm = con.prepareStatement(sql);
    pstm.setInt(1, bookID);
    rows = pstm.executeUpdate();
    pstm.close();
    con.close();
    } catch (ClassNotFoundException e) {
    e.printStackTrace();
    } catch (SQLException e) {
    e.printStackTrace();
    }
    return rows;
    }

  /**

  • 修改书籍
    */
    public int update(Book book){
    int rows = 0;
    try {
    Class.forName(“com.mysql.jdbc.Driver”);
    String url = “jdbc:mysql://localhost:3306/stu_db”;
    String user = “root”;
    String password = “123”;
    Connection con = DriverManager.getConnection(url, user, password);
    String sql = “update book set book_id=?,book_name=?,book_auther,book_date=?,p_id=?”;
    PreparedStatement pstm = con.prepareStatement(sql);
    pstm.setInt(1, book.getBkID());
    pstm.setString(2, book.getBkName());
    pstm.setString(3, book.getBkAuthor());
    pstm.setDate(4, book.getBkDate());
    pstm.setInt(5, book.getPubID());
    rows = pstm.executeUpdate();
    pstm.close();
    con.close();
    } catch (ClassNotFoundException e) {
    e.printStackTrace();
    } catch (SQLException e) {
    e.printStackTrace();
    }
    return rows;
    }

  public List selectAll(){
  List list = new ArrayList();
  try {
  Class.forName(“com.mysql.jdbc.Driver”);
  String url = “jdbc:mysql://localhost:3306/stu_db”;
  String user = “root”;
  String password = “123”;
  Connection con = DriverManager.getConnection(url, user, password);
  String sql = “select * from book”;
  PreparedStatement pstm = con.prepareStatement(sql);
  ResultSet rs = pstm.executeQuery();
  while(rs.next()){
  Book book = new Book();
  int bkID = rs.getInt(“book_id”);
  book.setBkID(bkID);
  String bkName = rs.getString(“book_name”);
  book.setBkName(bkName);
  String bkAuthor = rs.getString(“book_author”);
  book.setBkAuthor(bkAuthor);
  Date bkDate = rs.getDate(“book_date”);
  book.setBkDate(bkDate);
  int pubID = rs.getInt(“p_id”);
  book.setPubID(pubID);
  list.add(book);
  }
  rs.close();
  pstm.close();
  con.close();
  } catch (ClassNotFoundException e) {
  e.printStackTrace();
  } catch (SQLException e) {
  e.printStackTrace();
  }
  return list;
  }

  /**

  • 根据ID 查询
  • @param bookID
  • @return
    */
    public Book findID(int bookID){
    try {
    Class.forName(“com.mysql.jdbc.Driver”);
    Class.forName(“com.mysql.jdbc.Driver”);
    String url = “jdbc:mysql://localhost:3306/stu_db”;
    String user = “root”;
    String password = “123”;
    Connection con = DriverManager.getConnection(url, user, password);
    String sql = “select * from public where p_id=?”;
    PreparedStatement pstm = con.prepareStatement(sql);
    pstm.setInt(1, bookID);
    ResultSet rs = pstm.executeQuery();
    while(rs.next()){
    Book book = new Book();
    int bkID = rs.getInt(“book_id”);
    book.setBkID(bkID);
    String bkName = rs.getString(“book_name”);
    book.setBkName(bkName);
    String bkAuthor = rs.getString(“book_author”);
    book.setBkAuthor(bkAuthor);
    Date bkDate = rs.getDate(“book_date”);
    book.setBkDate(bkDate);
    int pubID = rs.getInt(“p_id”);
    book.setPubID(pubID);
    return book;
    }
    rs.close();
    pstm.close();
    con.close();
    } catch (ClassNotFoundException e) {
    e.printStackTrace();
    } catch (SQLException e) {
    e.printStackTrace();
    }
    return null;
    }
    }