book的增删查改

public class Book {
private int id;
private String bookName;
private int price;
private String description;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}

public String toString() {
	return "Book [id=" + id + ", bookName=" +bookName + ",description=" +description + ", price=" + price + "]";
}

}

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DbcpConnection {
//����һ��DataSource����
private static DataSource ds=null;
//�����ִֻ��һ��
static{
try {
//���������ļ�����ȡ
Properties p=new Properties();
FileInputStream in=new FileInputStream(“dbcp.properties”);
p.load(in);
ds=BasicDataSourceFactory.createDataSource§;
} catch (Exception e) {

				e.printStackTrace();
			}
		}
		/*
		 * ��ȡ���Ӷ��?�
		 */
		public static Connection getConnection(){

			Connection conn;
			try {
				conn = ds.getConnection();
				return conn;
			} catch (SQLException e) {
				
				e.printStackTrace();
				return null;
			}
			
		}
		
		public static void close(Connection conn){
			try {
				conn.close();
				
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}
		
		public static void close(PreparedStatement ps){
			try {
				ps.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
		}
		public static void close(ResultSet rs){
			try {
				rs.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
		}

}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBUtil {
//�����ִֻ��һ��
static{
try {
Class.forName(“com.mysql.jdbc.Driver”);
} catch (ClassNotFoundException e) {

				e.printStackTrace();
			}
		}
		public static Connection getConnection(){
			String sql="jdbc:mysql://127.0.0.1:3306/book?useUnicode=true&characterEncoding=UTF-8";
			Connection conn;
			try {
				conn = DriverManager.getConnection(sql,"root","123456");
				return conn;
			} catch (SQLException e) {
				
				e.printStackTrace();
				return null;
			}
			
		}
		
		public static void close(Connection conn){
			try {
				conn.close();
				
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}
		
		public static void close(PreparedStatement ps){
			try {
				ps.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
		}
		public static void close(ResultSet rs){
			try {
				rs.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
		}

}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JdbcConnection {
public static void main(String[] args) {
//ע��mysql���ݿ�����
try {
Class.forName(“com.mysql.jdbc.Driver”);
String url=“jdbc:mysql://127.0.0.1:3306/book?useUnicode=true&characterEncoding=UTF-8”;
Connection conn=DriverManager.getConnection(url,“root”,“123456”);
PreparedStatement ps=conn.prepareStatement(“select * from book”);
ResultSet rs=ps.executeQuery();
Book book = new Book();
while(rs.next()){
// ��ӡ������Ʒ������
// String name=rs.getString(“name”);
// System.out.println(name);
book.setId(rs.getInt(“id”));
book.setBookName(rs.getString(“bookName”));
book.setPrice(rs.getInt(“price”));
book.setDescription(rs.getString(“description”));
System.out.println(book);
}
} catch (ClassNotFoundException e) {

		e.printStackTrace();
	} catch (SQLException e) {
		
		e.printStackTrace();
	}

}

}

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

public class Test {
public static void main(String[] args) {

    Book book = new Book();
    book.setId(15);
    book.setBookName("�����Ӣ��");
    book.setPrice(22);
    book.setDescription("Ӣ�����ô�");
//saveBook(book);
    
 // updateBook(book);
  //  findBookBybookName();

// deleteBookById(7);
findBookById(15);
// findBookByPrice(10);
}
private static void findBookBybookName() {
// TODO Auto-generated method stub

}
/*

• ʵ�������Ʒ�ķ���
  */
  public static void saveBook(Book book){
  Connection conn = DBUtil.getConnection();
  PreparedStatement pstmt;
  try {
  pstmt = conn.prepareStatement(“insert into book(id,bookName,price,description) values(?,?,?,?)”);
  pstmt.setInt(1, book.getId());
  pstmt.setString(2, book.getBookName());
  pstmt.setInt(3, book.getPrice());
  pstmt.setString(4, book.getDescription());
  int rs = pstmt.executeUpdate();
  System.out.println(rs);
  } catch (SQLException e) {

   e.printStackTrace();
  

  }

}
/*

• ʵ��ɾ����Ʒ�ķ���(����id)
  */
  public static void deleteBookById(int id){
  Connection conn = DBUtil.getConnection();
  PreparedStatement pstmt;
  try {
  pstmt = conn.prepareStatement(“delete from book where id=?”);
  pstmt.setInt(1, id);
  int rs = pstmt.executeUpdate();
  System.out.println(rs);
  } catch (SQLException e) {

   e.printStackTrace();
  

  }
  }
  /*

• ʵ���޸���Ʒ�ķ���(����id)
  */
  public static void updateBook(Book book){
  Connection conn =DBUtil.getConnection();
  PreparedStatement pstmt;
  try {
  pstmt = conn.prepareStatement(“update book set bookName=?,price=?, description=?where id=?”);
  pstmt.setString(1, book.getBookName());
  pstmt.setInt(2, book.getPrice());
  pstmt.setInt(3, book.getId());
  pstmt.setString(4,book.getDescription());
  int rs = pstmt.executeUpdate();
  System.out.println(rs);
  } catch (SQLException e) {

   e.printStackTrace();
  

  }

}
/*

• ʵ�ָ���id��ѯ��Ʒ�ķ���
  */
  public static Book findBookById(Integer id){
  Connection conn = DBUtil.getConnection();
  PreparedStatement pstmt;
  Book book = new Book();
  try {
  pstmt = conn.prepareStatement(“select * from book where id = ?”);
  pstmt.setInt(1, id);
  ResultSet rs = pstmt.executeQuery();
  while (rs.next()) {
  book.setId(rs.getInt(“id”));
  book.setBookName(rs.getString(“bookName”));
  book.setPrice(rs.getInt(“price”));
  book.setDescription(rs.getString(“description”));
  System.out.println(book);
  }
  } catch (SQLException e) {

   e.printStackTrace();
  

  }

  return book;
  }
  /*

• ʵ�ָ�����Ʒ����ѯ��Ʒ�ķ���
  */
  public static Book findProductByName(String bookName){
  Connection conn = DBUtil.getConnection();
  PreparedStatement pstmt;
  Book book = new Book();
  try {
  pstmt = conn.prepareStatement(“select * from book where bookName = ?”);
  pstmt.setString(1, bookName);
  ResultSet rs = pstmt.executeQuery();
  while (rs.next()) {
  book.setId(rs.getInt(“id”));
  book.setBookName(rs.getString(“bookName”));
  book.setPrice(rs.getInt(“price”));
  book.setDescription(rs.getString(“description”));
  System.out.println(book);
  }
  } catch (SQLException e) {

   e.printStackTrace();
  

  }

  return book;
  }
  /*

• ʵ�ָ��ݼ۸��ѯ��Ʒ�ķ���
  */
  public static List findBookByPrice(int price){
  Connection conn =DBUtil.getConnection();
  PreparedStatement pstmt;
  List list = new ArrayList();
  Book book = new Book();
  try {
  pstmt = conn.prepareStatement(“select * from book where price = ?”);
  pstmt.setInt(1, price);
  ResultSet rs = pstmt.executeQuery();

   while (rs.next()) {
   	book.setId(rs.getInt("id"));
      book.setBookName(rs.getString("bookName"));
      book.setPrice(rs.getInt("price"));
      book.setDescription(rs.getString("description"));
       list.add(book);
       System.out.println(book);
   }
  

  } catch (SQLException e) {

   e.printStackTrace();
  

  }

  return list;
  }
  }