JAVA入门简单的增删改查(Servlet+JSP)一
是一个图书的增删改查
JDBC的连接
packagecom.zhouxiaobo.demo.jdbc.util;
importjava.sql.Connection;
publicinterfaceDBUtil{
publicConnectionopenConn();
publicvoidcloseConn();
}
重写
packagecom.zhouxiaobo.demo.jdbc.util;
importjava.sql.Connection;
importjava.sql.DriverManager;
publicclassDefaultDBUtilimplementsDBUtil{
publicstaticStringurl="jdbc:mysql://localhost:3306/d";
publicstaticStringusername="root";
publicstaticStringpassword="root";
publicstaticStringdrive="com.mysql.jdbc.Driver";
publicConnectionconn=null;
@Override
publicConnectionopenConn()/*throwsRuntimeException*/{
try{
Class.forName(drive);
conn=DriverManager.getConnection(url,username,password);
returnconn;
}catch(Exceptione){
//thrownewRuntimeException(e);
e.getMessage();
returnnull;
}
}
@Override
publicvoidcloseConn()throwsRuntimeException{
try{
conn.close();
}catch(Exceptions){
thrownewRuntimeException(s);
}
}
}
Bean层
packagecom.zhouxiaobo.demo.jdbc.bean;
publicclassBook{
publicStringgetSn(){
returnsn;
}
publicvoidsetSn(Stringsn){
this.sn=sn;
}
publicStringgetName(){
returnname;
}
publicvoidsetName(Stringname){
this.name=name;
}
privateStringsn;
privateStringname;
}
BookLend层
packagecom.zhouxiaobo.demo.jdbc.bean;
publicclassBookLend{
publicBookgetBook(){
returnbook;
}
publicvoidsetBook(Bookbook){
this.book=book;
}
publicStudentgetStudent(){
returnstudent;
}
publicvoidsetStudent(Studentstudent){
this.student=student;
}
publicintgetNumber(){
returnnumber;
}
publicvoidsetNumber(intnumber){
this.number=number;
}
privateBookbook;
privateStudentstudent;
privateintnumber;
}
BookStore层
packagecom.zhouxiaobo.demo.jdbc.bean;
publicclassBookStore{
publicBookgetBook(){
returnbook;
}
publicvoidsetBook(Bookbook){
this.book=book;
}
publicintgetTotalNumber(){
returntotalNumber;
}
publicvoidsetTotalNumber(inttotalNumber){
this.totalNumber=totalNumber;
}
privateBookbook;
privateinttotalNumber;
}
Student层
packagecom.zhouxiaobo.demo.jdbc.bean;
publicclassStudent{
publicStringgetId(){
returnid;
}
publicvoidsetId(Stringid){
this.id=id;
}
publicStringgetName(){
returnname;
}
publicvoidsetName(Stringname){
this.name=name;
}
privateStringid;
privateStringname;
}
DAO层
packagecom.zhouxiaobo.demo.jdbc.dao;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.util.ArrayList;
importjava.util.List;
importcom.zhouxiaobo.demo.jdbc.bean.Book;
publicclassBookDao{
publicList<Book>findbysn(Stringsn,Connectionconn){
Stringsql="select*frombookwheresn=?";
List<Book>books=newArrayList<Book>();
try{
PreparedStatementps=conn.prepareStatement(sql);
ps.setString(1,sn);
ResultSetrs=ps.executeQuery();
while(rs.next()){
Bookbook=newBook();
book.setSn(rs.getString(1));
book.setName(rs.getString(2));
books.add(book);
}
returnbooks;
}catch(SQLExceptions){
s.printStackTrace();
returnnull;
}
}
publicvoidinsert(Stringsn,Stringname,Connectionconn){
Stringsql="insertbookvalues(?,?)";
try{
introw=0;
PreparedStatementps=conn.prepareStatement(sql);
ps.setString(1,sn);
ps.setString(2,name);
row=ps.executeUpdate();
if(row>0){
System.out.println("success");
}
}catch(SQLExceptions){
s.printStackTrace();
}
}
publicvoidupdate(Stringsn,Stringname,Connectionconn){
Stringsql="updatebooksetname=?wheresn=?";
try{
introw=0;
PreparedStatementps=conn.prepareStatement(sql);
ps.setString(1,name);
ps.setString(2,sn);
row=ps.executeUpdate();
if(row>0){
System.out.println("Success!");
}
}catch(SQLExceptions){
s.printStackTrace();
}
}
publicList<Book>getquery(Connectionconn){
Stringsql="select*frombook";
List<Book>books=newArrayList<Book>();
try{
PreparedStatementps=conn.prepareStatement(sql);
ResultSetrs=ps.executeQuery();
while(rs.next()){
Bookbook=newBook();
book.setSn(rs.getString(1));
book.setName(rs.getString(2));
books.add(book);
}
returnbooks;
}catch(SQLExceptione){
//TODO:handleexception
e.printStackTrace();
returnnull;
}
}
publicvoiddelete(Stringsn,Connectionconn){
Stringsql="deletefrombookwheresn=?";
try{
PreparedStatementps=conn.prepareStatement(sql);
ps.setString(1,sn);
introw=0;
row=ps.executeUpdate();
if(row>0){
System.out.println("Success!");
}
}catch(SQLExceptione){
//TODO:handleexception
e.printStackTrace();
}
}
}
packagecom.zhouxiaobo.demo.jdbc.dao;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.SQLException;
publicclassBookLendDao{
publicvoidinsert(Stringsn,Stringid,intnumber,Connectionconn){
Stringsql="insertbooklendvalues(?,?,?)";
try{
introw=0;
PreparedStatementps=conn.prepareStatement(sql);
ps.setString(1,sn);
ps.setString(2,id);
ps.setInt(3,number);
row=ps.executeUpdate();
if(row>0){
System.out.println("success");
}
}catch(SQLExceptions){
s.printStackTrace();
}
}
publicvoiddelete(Stringsn,Connectionconn){
Stringsql="deletefrombooklendwheresn=?";
try{
PreparedStatementps=conn.prepareStatement(sql);
ps.setString(1,sn);
introw=0;
row=ps.executeUpdate();
if(row>0){
System.out.println("success");
}
}catch(SQLExceptions){
s.printStackTrace();
}
}
}
packagecom.zhouxiaobo.demo.jdbc.dao;
importjava.awt.List;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.util.ArrayList;
importjava.util.LinkedList;
importcom.zhouxiaobo.demo.jdbc.bean.Book;
importcom.zhouxiaobo.demo.jdbc.bean.BookStore;
publicclassBookStoreDao{
publicvoidupdate(Stringsn,inttotalNumber,Connectionconn){
Stringsql="updatebookstoresettotalNumber=?wheresn=?";
try{
introw=0;
PreparedStatementps=conn.prepareStatement(sql);
ps.setInt(1,totalNumber);
ps.setString(2,sn);
row=ps.executeUpdate();
if(row>0){
System.out.println("���³ɹ���");
}
}catch(SQLExceptions){
s.printStackTrace();
}
}
publicintgetbooknumberbysn(Stringsn,Connectionconn){
Stringsql="select*frombookstorewheresn=?";
try{
PreparedStatementps=conn.prepareStatement(sql);
ResultSetrs=ps.executeQuery();
if(rs.next()){
Strings1=rs.getNString(3);
System.out.println("�Ȿ��һ����"+s1+"��");
inta=Integer.parseInt(s1);
returna;
}
}catch(SQLExceptions){
s.printStackTrace();
}
return0;
}
publicvoidinsert(Bookbook,inttotalNumber,Connectionconn){
Stringsql="insertbookstorevalue(?,?,?)";
BookStorebookStore=newBookStore();
try{
introw=0;
PreparedStatementps=conn.prepareStatement(sql);
ps.setString(1,bookStore.getBook().getSn());
ps.setString(2,bookStore.getBook().getName());
ps.setInt(3,bookStore.getTotalNumber());
row=ps.executeUpdate();
if(row>1){
System.out.println("����ɹ���");
}
}catch(SQLExceptions){
s.printStackTrace();
}
}
publicvoiddelete(Stringsn,Connectionconn){
Stringsql="deletefrombookstorewheresn=?";
BookStorebookStore=newBookStore();
try{
PreparedStatementps=conn.prepareStatement(sql);
ps.setString(1,bookStore.getBook().getSn());
introw=0;
row=ps.executeUpdate();
if(row>0){
System.out.println("ɾ��ɹ���");
}
}catch(SQLExceptions){
s.printStackTrace();
}finally{
}
}
publicvoidfindbysn(Stringsn,Connectionconn){
Stringsql="select*frombookstorewheresn=?";
BookStorebookStore=newBookStore();
try{
PreparedStatementps=conn.prepareStatement(sql);
ps.setString(1,bookStore.getBook().getSn());
ResultSetrs=ps.executeQuery();
Strings1=rs.getString(1);
Strings2=rs.getString(2);
Strings3=rs.getString(3);
System.out.println(s1+"\t"+s2+"\t"+s3);
}catch(SQLExceptiona){
a.printStackTrace();
}
}
/*publicList<BookStore>getquery(Connectionconn){
Stringsql="select*frombookstore";
try{
PreparedStatementps=conn.prepareStatement(sql);
ResultSetrs=ps.executeQuery();
List<BookStore>bookList=newArrayList<BookStore>();
while(rs.next()){
BookStorebookStore=newBookStore();
//bookStore.setBook()
}
}catch(SQLExceptions){
s.printStackTrace();
}
}*/
}
三个有点相似看一个就好
Service层
接口
packagecom.zhouxiaobo.demo.jdbc.service;
importjava.sql.Connection;
importjava.util.List;
importcom.zhouxiaobo.demo.jdbc.bean.Book;
importcom.zhouxiaobo.demo.jdbc.bean.BookLend;
importcom.zhouxiaobo.demo.jdbc.bean.Student;
publicinterfaceBussinessService{
publicvoidlendBook(Studentstudent,Bookbook,intnumber);
publicvoidreturnbook(BookLendbookLend);
publicList<Book>showallbooks();
publicvoidsetConn(Connectionconn);
publicvoidupdatebook(Stringsn,Stringname,Connectionconn);
publicvoiddeletebook(Stringsn,Connectionconn);
publicStringinsertbook(Stringsn,Stringname,Connectionconn);
publicList<Book>findbysn(Stringsn,Connectionconn);
}
重写
packagecom.zhouxiaobo.demo.jdbc.service;
importjava.sql.Connection;
importjava.sql.SQLException;
importjava.util.ArrayList;
importjava.util.List;
importcom.zhouxiaobo.demo.jdbc.bean.Book;
importcom.zhouxiaobo.demo.jdbc.bean.BookLend;
importcom.zhouxiaobo.demo.jdbc.bean.Student;
importcom.zhouxiaobo.demo.jdbc.dao.BookDao;
importcom.zhouxiaobo.demo.jdbc.dao.BookLendDao;
importcom.zhouxiaobo.demo.jdbc.dao.BookStoreDao;
importcom.zhouxiaobo.demo.jdbc.exception.DemoJDBCException;
publicclassBussinessServiceImplimplementsBussinessService{
privateBookLendDaobooklendDao;
privateBookStoreDaobookStoreDao;
privateConnectionconn;
publicBussinessServiceImpl(){
}
publicBussinessServiceImpl(Connectionconn){
this.conn=conn;
}
publicvoidsetConn(Connectionconn){
this.conn=conn;
}
@Override
publicvoidlendBook(Studentstudent,Bookbook,intnumber){
booklendDao=newBookLendDao();
bookStoreDao=newBookStoreDao();
try{
conn.setAutoCommit(false);
//TODO:��lendBookȥ�����������
booklendDao.insert(book.getSn(),student.getId(),number,conn);
//��ȡ�����ж��ٱ������
inttotal=bookStoreDao.getbooknumberbysn(book.getSn(),conn);
intcurrentNumber=total-number;
bookStoreDao.update(book.getSn(),currentNumber,conn);
//TODO:��bookStoreDaoȥ�۳�ͼ��
conn.commit();
}catch(Exceptione){
try{
conn.rollback();
}catch(SQLExceptione1){
//TODOAuto-generatedcatchblock
e1.printStackTrace();
}
thrownewDemoJDBCException("xzzxz",e);
}finally{
try{
conn.close();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
}
}
publicvoidreturnbook(BookLendbookLend){
//ɾ����ļ�¼
booklendDao=newBookLendDao();
bookStoreDao=newBookStoreDao();
try{
conn.setAutoCommit(false);
booklendDao.delete(bookLend.getBook().getSn(),conn);
//ɾ����ļ�¼
intnumber=bookStoreDao.getbooknumberbysn(bookLend.getBook().getSn(),conn);
inttotalNumber=number+bookLend.getNumber();
bookStoreDao.update(bookLend.getBook().getSn(),totalNumber,conn);
conn.commit();
}catch(Exceptionx){
try{
conn.rollback();
}catch(SQLExceptione){
e.printStackTrace();
}
}
}
publicList<Book>showallbooks(){
BookDaobookDao=newBookDao();
List<Book>result=null;
try{
conn.setAutoCommit(false);
returnbookDao.getquery(conn);
}catch(SQLExceptione){
//TODO:handleexception
try{
conn.rollback();
}catch(SQLExceptione1){
//TODOAuto-generatedcatchblock
e1.printStackTrace();
}
e.printStackTrace();
}
returnnull;
}
publicvoidupdatebook(Stringsn,Stringname,Connectionconn){
BookDaobookDao=newBookDao();
bookDao.update(sn,name,conn);
}
publicvoiddeletebook(Stringsn,Connectionconn){
BookDaobookDao=newBookDao();
try{
conn.setAutoCommit(false);
bookDao.delete(sn,conn);
conn.commit();
}catch(SQLExceptionx){
x.printStackTrace();
try{
conn.rollback();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
}
}
publicStringinsertbook(Stringsn,Stringname,Connectionconn){
BookDaobookDao=newBookDao();
List<Book>books=newArrayList<Book>();
books=bookDao.findbysn(sn,conn);
if(books.size()==0){
try{
conn.setAutoCommit(false);
bookDao.insert(sn,name,conn);
conn.commit();
return"1";
}catch(SQLExceptions){
s.printStackTrace();
try{
conn.rollback();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
return"1";
}
}else{
returnsn;
}
}
publicList<Book>findbysn(Stringsn,Connectionconn){
BookDaobookDao=newBookDao();
List<Book>result=null;
try{
conn.setAutoCommit(false);
returnbookDao.findbysn(sn,conn);
}catch(Exceptione){
//TODO:handleexception
try{
conn.rollback();
}catch(SQLExceptione1){
//TODOAuto-generatedcatchblock
e1.printStackTrace();
}
e.printStackTrace();
returnnull;
}
}
}
Action层
packagecom.zhouxiaobo.demo;
importjava.io.IOException;
importjava.io.PrintWriter;
importjava.sql.Connection;
importjava.util.Collection;
importjava.util.List;
importjavax.servlet.RequestDispatcher;
importjavax.servlet.ServletException;
importjavax.servlet.http.HttpServlet;
importjavax.servlet.http.HttpServletRequest;
importjavax.servlet.http.HttpServletResponse;
importcom.zhouxiaobo.demo.jdbc.bean.Book;
importcom.zhouxiaobo.demo.jdbc.service.BussinessService;
importcom.zhouxiaobo.demo.jdbc.service.BussinessServiceImpl;
importcom.zhouxiaobo.demo.jdbc.util.DBUtil;
importcom.zhouxiaobo.demo.jdbc.util.DefaultDBUtil;
/**
*ServletimplementationclassFindAllBook
*/
publicclassFindAllBookextendsHttpServlet{
privatestaticfinallongserialVersionUID=1L;
/**
*Defaultconstructor.
*/
publicFindAllBook(){
//TODOAuto-generatedconstructorstub
}
/**
*@seeHttpServlet#doGet(HttpServletRequestrequest,HttpServletResponseresponse)
*/
protectedvoiddoGet(HttpServletRequestrequest,HttpServletResponseresponse)throwsServletException,IOException{
//TODOAuto-generatedmethodstub
DBUtildbUtil=newDefaultDBUtil();
try{
Connectionconn=dbUtil.openConn();
BussinessServicebusinessService=newBussinessServiceImpl();
businessService.setConn(conn);
List<Book>BookStore=businessService.showallbooks();
request.setAttribute("book",BookStore);
request.getRequestDispatcher("/booklist.jsp").forward(request,response);
}finally{
dbUtil.closeConn();
}
}
/**
*@seeHttpServlet#doPost(HttpServletRequestrequest,HttpServletResponseresponse)
*/
protectedvoiddoPost(HttpServletRequestrequest,HttpServletResponseresponse)throwsServletException,IOException{
//TODOAuto-generatedmethodstub
}
}
JSP
界面
<%@pagelanguage="java"contentType="text/html;charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPEhtmlPUBLIC"-//W3C//DTDHTML4.01Transitional//EN""http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<metahttp-equiv="Content-Type"content="text/html;charset=UTF-8">
<title>Inserttitlehere</title>
</head>
<body>
<h1>图书管理系统</h1>
<palign="center">
</p>
<td><ahref="/LIB/Addbook.jsp">添加图书</a></td>
<td><ahref="/LIB/findAllBook">查看所有图书</a></td>
<td><ahref="/LIB/editBookServlet">更改图书</a></td>
<td><ahref="/LIB/Find.jsp">按条件查找</a></td>
</body>
</html>
显示所有图书
<%@pageimport="java.util.List"%>
<%@pageimport="com.zhouxiaobo.demo.jdbc.bean.Book"%>
<%@tagliburi="http://java.sun.com/jsp/jstl/core"prefix="c"%>
<%@pagelanguage="java"contentType="text/html;charset=UTF-8"
pageEncoding="UTF-8"%>
<tableborder="1"cellspacing="0"cellpadding="0width="100%"style="align:center;">
<trbgcolor="AAAAAA"style="font-weight:bold;">
<th>编号</th><th>书名</th><th><thwidth="0"colspan="2"></th>
<c:forEachvar="bk"items="${book}"varStatus="status">
<tr>
<c:iftest="${status.count%2==0}">
<trbgcolor="CCC000"style="font-weight:bold;">
</c:if>
<c:iftest="${status.count%2==1}">
<trbgcolor="FFFFOO"style="font-weight:bold;">
</c:if>
<td>${bk.sn}</td>
<td>${bk.name}</td>
<td><ahref="/LIB/deleteBookServlet?sn=${bk.sn}">删除</a>|<ahref="/LIB/editBookServlet?sn=${bk.sn}">更改</a></td>
</c:forEach>
<c:iftest="${!emptyflag}">
ISBN${flag}isdeleted!
</c:if>
<c:iftest="${!emptyflag1}">
ISBN${flag1}ishaved!
</c:if>
</tr>
</table>