图书综合管理系统增删查改jspservlet基础优质项目BaseDao.docx

这是图书管理系统课后作业 增删查改 BaseDao
jsp提交到servlet

BaseDao.java 数据库操作通用类

package combook.dao;

import java.sql.*;

import com.dao.util.ConfigManager;

import common.*;
public class BaseDao {//数据库操作通用类
protected Connection conn;
/**定义静态变量接收加载驱动*/
private static String driver;
/**定义静态变量得到连接*/
private static String url;
private static String userName;//数据库用户名
private static String password;//数据库密码
//静态Connection 对象
static{
driver=ConfigManager.getInstance().getString("driver");
url=ConfigManager.getInstance().getString("url");
userName=ConfigManager.getInstance().getString("userName");
password=ConfigManager.getInstance().getString("password");
}
protected ResultSet rs;
protected Statement stmt;
protected PreparedStatement ps;
//获取链接方法
public boolean getConnection(){

if(conn!=null){//判定连接对象是否为空
return false;
}
try {
Class.forName(driver);//加载驱动
//依据url，用户名和密码获取链接 获取是connection对象
conn=DriverManager.getConnection(url,userName,password);
} catch (ClassNotFoundException e) {

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

e.printStackTrace();
}

return true;
}
//增删改方法 子类能够调用这个方法 帮助实施sql语句
public int execute(String sql,Object[]params){
try {
ps=conn.prepareStatement(sql);//预编译传过来sql语句
if(params!=null){//假如子类传过来参数不为空话 遍历循环 判定使参数兼容
for (int i = 0; i < params.length; i++) {//遍历参数数组
Object obj=params[i];//参数是Object类型所以Object先接收 然后判定
if(obj instanceof String){
ps.setString((i+1), (String)obj);//假如该参数是String类型 就强转设置 数组该位置元素是String类型
} else if(obj instanceof Integer){
ps.setInt((i+1), (Integer)obj);
}else if(obj==null){//这么判定一下因为有图片传入 判定不了类型
obj="";//赋值为空
ps.setString((i+1), (String)obj);//默认设置为String类型
}
}
}//实施sql
int count=ps.executeUpdate();
return count;
} catch (SQLException e) {

e.printStackTrace();
return 0;
}

}
//获取结果集方法
public ResultSet excuteSql(String sql,Object[]params){

try {
rs=ps.executeQuery(sql);
for (int i = 0; i < params.length; i++) {
if(params[i] instanceof String){
ps.setString((i+1), (String)params[i]);
}else if(params[i] instanceof Integer){
ps.setInt((i+1), (Integer)params[i]);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}

public boolean closeResource(){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}

if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}

if(conn!=null){
try {
conn.close();
conn=null;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}

return true;
}

//方法重载 假如需要关闭多个资源就选择哪个方法
public void closeResource(Connection conn){
if(conn==null){
try {
conn.close();
} catch (SQLException e) {

e.printStackTrace();
}
}
}
}
Dao层接口 --BookDao.java
package combook.dao;

import java.util.List;

import combook.dto.Book;
import common.dto.PageDto;

public interface BookDao {
public List<Book> getBooksList(PageDto pagedto)throws Exception;//取得图书集合方法--全部信息
//增加一本图书信息
public int insertBooks(Book book)throws Exception;
//依据图书编号删除新闻
public int delete(int bid)throws Exception;
//修改信息 --传入是图书对象
public int update(Book book)throws Exception;
//依据图书编号查找该图书

//依据图书编号查找该图书
public Book query(int bid) throws Exception;

public List<Book> query(String bookname) throws Exception;

//取得总统计方法
public int getAllRowCount() throws Exception;

}

BookImpl.java 实现类 实现了接口 继承了BaseDao.java
package combook.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import combook.dao.BaseDao;
import combook.dao.BookDao;
import combook.dto.Book;
import common.dto.PageDto;

public class BookImpl extends BaseDao implements BookDao{

//1、查询全部信息 返回一个 带有泛型集合
public List<Book> getNewsList() throws Exception {
//定义一个集合 集合是对应要返回出去 泛型集合
List<Book> bookList=new ArrayList<Book>();
super.getConnection();//取得连接 经过父类方法

conn.prepareStatement("select * from news");
rs=ps.executeQuery();//返回结果集
while(rs.next()){//循环结果集中数据判定结果集中是否还有下一个内容
Book book=new Book();//创建一个实例对象
//把结果集中图书信息封装到图书对象中去
book.setBid(rs.getInt("bid"));
book.setBookName(rs.getString("bookName"));
book.setImage(rs.getString("image"));
book.setPrice(Double.parseDouble(rs.getString("b_price")));
book.setStock(rs.getInt("stock"));
//将图书对象添加到集合中去 因为集合是带有图书泛型所以不需要强转 直接添加图书对象
bookList.add(book);
}
//调用父类方法释放资源
super.closeResource();

return bookList;
}

//取得数据库中全部图书信息
public List<Book> getBooksList(PageDto pagedto) throws Exception {
//新建一个带有book泛型集合
List<Book> bookList=new ArrayList<Book>();

super.getConnection();//第一步取得和数据库连接
int start=pagedto.getStartIndex();//分页查询开始
int end =pagedto.getEndIndex();//分页查询结束
//写分页查询sql语句
String sql="select * from (select b.*,rownum rn from books b) where rn>=? and rn<?";
ps=conn.prepareStatement(sql);//预编译sql语句
ps.setInt(1,start);//设置第一个参数值
ps.setInt(2,end);//设置第二个参数值
//实施查询语句
rs=ps.executeQuery();

//循环结果集数据
while(rs.next()){
//实例化一个图书对象
Book book =new Book();
//把结果集中图书信息封装到图书对象中去
book.setBid(rs.getInt("bid"));
book.setBookName(rs.getString("bookName"));
book.setImage(rs.getString("image"));
book.setPrice(Double.parseDouble(rs.getString("b_price")));
book.setStock(rs.getInt("stock"));
//将图书对象添加到集合中去 因为集合是带有图书泛型所以不需要强转 直接添加图书对象
bookList.add(book);
}

super.closeResource();
return bookList;
}
//向表中插入新书方法 传入是图书对象 返回影响行数
public int insertBooks(Book book) throws Exception {
super.getConnection();//第一步取得和数据库连接
//新建一个参数数组 填充占位符
Object params[]=new Object[5];
params[0]=book.getBid();
params[1]=book.getBookName();
params[2]=book.getPrice();
params[3]=book.getImage();
params[4]=book.getStock();
String sql="insert into book values(?,?,?,?,?)";
//预编译sql语句
ps=conn.prepareStatement(sql);
//实施更新数据库sql语句操作
int count=super.execute(sql, params);
super.closeResource();
return count;
}
//删除图书方法 依据穿过来图书id
public int delete(int bid) throws Exception {
super.getConnection();//第一步取得和数据库连接
//填充占位符
Object params[]=new Object[1];
params[0]=bid;
String sql="delete from book where bid=?";//删除sql语句
//预编译sql语句
ps=conn.prepareStatement(sql);
//调用父类实施sql
int count=super.execute(sql, params);
//释放资源
super.closeResource();
//返回受影响行数
return count;
}
//修改图书方法 传入图书对象
public int update(Book book) throws Exception {
super.getConnection();//第一步取得和数据库连接
//填充占位符
Object params[]=new Object[4];
params[0]=book.getBookName();
params[1]=book.getPrice();
params[2]=book.getImage();
params[3]=book.getStock();
String sql="update book set bookname=?,b_price=?,image=?,stock=?";
ps=conn.prepareStatement(sql);
int count=super.execute(sql, params);
super.closeResource();
return count;
}
public Book query(int bid) throws Exception{
super.getConnection();//第一步取得和数据库连接
Book book=new Book();
//填充占位符
Object params[]=new Object[1];
params[0]=bid;
String sql="select * from books where bid=?";
conn.prepareStatement(sql);
rs=super.excuteSql(sql, params);
if(rs.next()){
//把结果集中图书信息封装到图书对象中去
book.setBid(rs.getInt("bid"));
book.setBookName(rs.getString("bookName"));
book.setImage(rs.getString("image"));
book.setPrice(rs.getDouble("b_price"));
book.setStock(rs.getInt("stock"));
}
super.closeResource();
return book;

}
public List<Book> query(String bookname) throws Exception {
super.getConnection();//第一步取得和数据库连接
//新建一个带有book泛型集合
List<Book> bookList=new ArrayList<Book>();

//填充占位符
//Object params[]=new Object[1];
//params[0]=bookname;
String sql="select * from books where bookname like ?";
ps=conn.prepareStatement(sql);
ps.setString(1, "%"+bookname+"%");
//rs=super.excuteSql(sql, params);
rs=ps.executeQuery();
while(rs.next()){
Book book=new Book();
//把结果集中图书信息封装到图书对象中去
book.setBid(rs.getInt("bid"));
book.setBookName(rs.getString("bookName"));
book.setImage(rs.getString("image"));
book.setPrice(rs.getDouble("b_price"));
book.setStock(rs.getInt("stock"));
bookList.add(book);
}
super.closeResource();
return bookList;
}

public int getAllRowCount() throws Exception {
super.getConnection();//取得连接
int count=0;
//查找到全部统计
String sql="select count(*) from books";
ps=conn.prepareStatement(sql);//预编译sql语句
rs=ps.executeQuery();//返回一个结果集
if(rs.next()){
count=rs.getInt(1);//取得结果集第一条
}

super.closeResource();
return count;
}

}

用户表 UserInfoDao.java 接口

package combook.dao;

import java.util.List;

import combook.dto.Book;
import combook.dto.UserInfo;
import common.dto.PageDto;

public interface UserInfoDao {
//传入一个用户对象 判定这个对象是否在数据库中存在
public int queryObject(UserInfo user) throws Exception;
//依据用户名查找该数据库表中有没有反复用用户
public int query(String userName) throws Exception;
//增加一个用户信息 --传入一个用户对象
public int insertUserInfo(UserInfo user)throws Exception;
//修改信息 --传入是用户对象
public int update(UserInfo user)throws Exception;

}
UserInfoDaoImpl实现类

package combook.dao.impl;

import java.sql.SQLException;
import java.util.List;

import combook.dao.BaseDao;
import combook.dao.UserInfoDao;
import combook.dto.Book;
import combook.dto.UserInfo;
import common.dto.PageDto;

public class UserInfoDaoImpl extends BaseDao implements UserInfoDao {

//向用户表中添加一个新用户信息
public int insertUserInfo(UserInfo user) throws Exception {
super.getConnection();//第一步取得和数据库连接
//定义数组 用于填充占位符
Object params[]=new Object[3];
params[0]=user.getUserName();
params[1]=user.getPassword();
params[2]=user.getEmail();
//定义一个sql语句向表中插入数据
String sql="insert into userInfo values(?,?,?)";
//预编译sql语句
//ps=conn.prepareStatement(sql);
//ps.setString(1, x)
//调用父类实施sql操作
int count=super.execute(sql, params);
super.closeResource();
return count;
}

public int update(UserInfo user) throws Exception {

return 0;
}
//传入一个用户名判定这个用户名是否存在 返回一个查找统计
public int query(String userName) throws Exception {
super.getConnection();//第一步取得和数据库连接
int count=0;

String sql="select count(*) from userInfo where userName=?";
try {

//预编译sql
ps=conn.prepareStatement(sql);
//用ps填充占位符
ps.setString(1, userName);
rs=ps.executeQuery();//实施sql返回一个结果集
//找到结果集第一条数据
if(rs.next()){
count=rs.getInt(1);
}

} catch (SQLException e1) {

e1.printStackTrace();
}//释放资源
super.closeResource();
return count;
}

public int queryObject(UserInfo user) throws Exception {
super.getConnection();//第一步取得和数据库连接
String sql="select count(*) from userInfo where userName=? and passwords=?";
//预编译sql
ps=conn.prepareStatement(sql);
//填充占位符
ps.setString(1, user.getUserName());
ps.setString(2, user.getPassword());
//实施sql语句
rs=ps.executeQuery();
int count=0;
//找到结果集第一条数据
if(rs.next()){
count=rs.getInt(1);
}
super.closeResource();
return count;
}

}

数据库当中表对应实体类 DTO层

package combook.dto;
/***图书类**/
public class Book {
private int bid;//图书编号
private String bookName;//图书名字
private double price;//图书价格
private String image;//图书图片
private int stock;//库存
public int getBid() {
return bid;
}
public void setBid(int bid) {
this.bid = bid;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public String getImage() {
return image;
}
public void setImage(String image) {
this.image = image;
}
public int getStock() {
return stock;
}
public void setStock(int stock) {
this.stock = stock;
}

}

用户表实体类
package combook.dto;
/**用户类*/
public class UserInfo {
private String userName;//用户名
private String password;//密码
private String email;//邮箱

public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}

}

注册servlet提交地址
package combook.servlet;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import combook.dao.BaseDao;
import combook.dao.UserInfoDao;
import combook.dao.impl.UserInfoDaoImpl;
import combook.dto.UserInfo;
import combook.service.UserInfoService;
import combook.service.impl.UserInfoServiceImpl;

public class RegisterServlet extends HttpServlet{
/**
*
*/
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException{
//在地址栏直接敲代码会调用doGet
System.out.println("进入doGet方法");
//调用doPost方法 实现代码重用省掉代码
try {
doPost(request,response);
} catch (Exception e) {
e.printStackTrace();
}
}

protected void doPost(HttpServletRequest request,HttpServletResponse response){

System.out.println("进入doPost方法");
try {//取得表单提交过来用户名
try {
request.setCharacterEncoding("utf-8");
} catch (UnsupportedEncodingException e1) {
e1.printStackTrace();
}//设置编码格式处理汉字乱码
response.setCharacterEncoding("utf-8");
String userName=request.getParameter("userName");
//实例化一个用户表数据层实例
UserInfoDao userInfo=new UserInfoDaoImpl();
//调用依据传入提交过来用户名查询取得该用户是否存在 返回一个int类型
int count=userInfo.query(userName);
if(count>0){//说明该用户存在-跳到错误页面-返回重新注册
response.sendRedirect("./jbook/registererror.jsp");
}else if(count==0){//说明能够进入下一步 --把新注册用户存到数据库中用户表中去
//实例化用户表信息
UserInfo user=new UserInfo();
//封装表单提交过来表单数据
//将提交过来表单元素值赋值给用户表对象
Strin