数据库系统应用与开发实验五.docx

1、数据库系统应用与开发实验五实验 JDBC进阶（1）一、相关知识点 1、JDBC基本概念 2、JDBC数据增、删、改，事务控制等 3、OR映射二、实验目的： 理解Java连接数据库的基本概念。理解利用Statement对象、PreparedStatement对象进行增、删、改操作，理解事务的概念和JDBC编程方式。理解OR映射的基本概念三、实验内容：1、 对读者管理模块进行OR映射。第一步：将ReaderTypeDAO、ReaderDAO放入相应的包中，并完成补充完成其中的代码（要求进行纯粹的OR映射，不要涉及其他业务逻辑的判断）；第二步：改造ReaderManager类，使得该类不直接使用JD

2、BC，而通过调用上述两个DAO类实现相关业务逻辑。第三步：运行图书管理系统，进行各个功能的测试（读者类别管理、读者管理）【实验结果与分析】A、 写出DAO类中各个方法的代码。readerdaopublic List searchReader(String readerid, String readerName,int readerTypeId)throws BaseException List result=new ArrayList(); Connection conn=null; try conn=DBUtil.getConnection(); String sql; if(readerT

3、ypeId0) sql=select * from beanreader where readerid like %+readerid+% or readername like +readerName+; else sql=sql=select * from beanreader where readerid like %+readerid+% or readername like %+readerName+% or readertypeid =+readerTypeId+; java.sql.Statement st=conn.createStatement(); java.sql.Resu

4、ltSet rs=st.executeQuery(sql); while(rs.next() BeanReader a=new BeanReader(); a.setReaderid(rs.getString(1); a.setReaderName(rs.getString(2); a.setReaderTypeId(rs.getInt(3); a.setLendBookLimitted(rs.getInt(4); a.setCreateDate(rs.getDate(5); a.setCreatorUserId(rs.getString(6); a.setRemoveDate(rs.getD

5、ate(7); a.setRemoverUserId(rs.getString(8); a.setStopDate(rs.getDate(9); a.setStopUserId(rs.getString(10); result.add(a); catch (SQLException e) e.printStackTrace(); throw new DbException(e); finally if(conn!=null) try conn.close(); catch (SQLException e) / TODO Auto-generated catch block e.printSta

6、ckTrace(); return result; /* * 新增读者 * param r * throws BaseException */ public void createReader(BeanReader r) throws BaseException Connection conn=null; try conn=DBUtil.getConnection(); String sql; sql=insert into beanreader values(+r.getReaderid()+,+r.getReaderName()+); java.sql.Statement st=conn.

7、createStatement(); st.execute(sql); catch (SQLException e) e.printStackTrace(); throw new DbException(e); finally if(conn!=null) try conn.close(); catch (SQLException e) / TODO Auto-generated catch block e.printStackTrace(); /* * 修改读者信息 * param reader * throws BaseException */ public void modifyRead

8、er(BeanReader reader) throws BaseException Connection conn=null; try conn=DBUtil.getConnection(); String sql=update beanreader set readername=+reader.getReaderName()+,readertypeid=+reader.getReaderTypeId()+,lendbooklimitted=+reader.getLendBookLimitted()+,createdate=+reader.getCreateDate()+,CreatorUs

9、erId=+reader.getCreatorUserId()+,RemoveDate=+reader.getRemoveDate()+,RemoverUserId=+reader.getRemoverUserId()+,StopDate=+reader.getStopDate()+,StopUserId=+reader.getStopUserId()+; java.sql.Statement st=conn.createStatement(); st.execute(sql); catch (SQLException e) e.printStackTrace(); throw new DbE

10、xception(e); finally if(conn!=null) try conn.close(); catch (SQLException e) / TODO Auto-generated catch block e.printStackTrace(); public void deleteReader(BeanReader reader)throws BaseException Connection conn=null; try conn=DBUtil.getConnection(); String sql=delete from where readerid=+reader.get

11、Readerid()+; java.sql.Statement st=conn.createStatement(); st.execute(sql); catch (SQLException e) e.printStackTrace(); throw new DbException(e); finally if(conn!=null) try conn.close(); catch (SQLException e) / TODO Auto-generated catch block e.printStackTrace(); /* * * param readerid * return * th

12、rows DbException */ public BeanReader get(String readerid) throws DbException Connection conn=null; try conn=DBUtil.getConnection(); String sql=select * from beanreader where readerid=+readerid+; java.sql.Statement st=conn.createStatement(); java.sql.ResultSet rs=st.executeQuery(sql); if(rs.next() B

13、eanReader a=new BeanReader(); a.setReaderid(rs.getString(1); a.setReaderName(rs.getString(2); a.setReaderTypeId(rs.getInt(3); a.setLendBookLimitted(rs.getInt(4); a.setCreateDate(rs.getDate(5); a.setCreatorUserId(rs.getString(6); a.setRemoveDate(rs.getDate(7); a.setRemoverUserId(rs.getString(8); a.se

14、tStopDate(rs.getDate(9); a.setStopUserId(rs.getString(10); return a; catch (SQLException e) e.printStackTrace(); throw new DbException(e); finally if(conn!=null) try conn.close(); catch (SQLException e) / TODO Auto-generated catch block e.printStackTrace(); return null; Readertypedaopublic List qryR

15、eaderType(String readerTypeName)throws BaseException List result=new ArrayList(); Connection conn=null; try conn=DBUtil.getConnection(); String sql; sql=select * from beanreadertype where readerTypeName like %+readerTypeName+%; java.sql.Statement st=conn.createStatement(); java.sql.ResultSet rs=st.e

16、xecuteQuery(sql); while(rs.next() BeanReaderType a=new BeanReaderType(); a.setReaderTypeId(rs.getInt(1); a.setReaderTypeName(rs.getString(2); a.setLendBookLimitted(rs.getInt(3); result.add(a); catch (SQLException e) e.printStackTrace(); throw new DbException(e); finally if(conn!=null) try conn.close

17、(); catch (SQLException e) / TODO Auto-generated catch block e.printStackTrace(); return result; /* * 新增读者类别 * param rt * throws BaseException */ public void createReaderType(BeanReaderType rt) throws BaseException Connection conn=null; try conn=DBUtil.getConnection(); String sql; sql=insert into be

18、anreadertype values(+rt.getReaderTypeId()+,+rt.getReaderTypeName()+,+rt.getLendBookLimitted()+); java.sql.Statement st=conn.createStatement(); st.execute(sql); catch (SQLException e) e.printStackTrace(); throw new DbException(e); finally if(conn!=null) try conn.close(); catch (SQLException e) e.prin

19、tStackTrace(); /* * 修改读者类别信息 * param rt * throws BaseException */ public void modifyReaderType(BeanReaderType rt)throws BaseException Connection conn=null; try conn=DBUtil.getConnection(); String sql=update beanreadertype set ReaderTypeId=+rt.getReaderTypeId()+,ReaderTypeName=+rt.getReaderTypeName()

20、+,LendBookLimitted=+rt.getLendBookLimitted()+; java.sql.Statement st=conn.createStatement(); st.execute(sql); catch (SQLException e) e.printStackTrace(); throw new DbException(e); finally if(conn!=null) try conn.close(); catch (SQLException e) / TODO Auto-generated catch block e.printStackTrace(); p

21、ublic void deleteReaderType(BeanReaderType rt)throws BaseException Connection conn=null; try conn=DBUtil.getConnection(); String sql=delete from where readerid=+rt.getReaderTypeId()+; java.sql.Statement st=conn.createStatement(); st.execute(sql); catch (SQLException e) e.printStackTrace(); throw new

22、 DbException(e); finally if(conn!=null) try conn.close(); catch (SQLException e) / TODO Auto-generated catch block e.printStackTrace(); B、 给出改造后ReaderManager类的各个方法的代码。public class ReaderManager public List loadAllReaderType()throws BaseException List result=new ReaderTypeDAO().qryReaderType(); retur

23、n result; public void createReaderType(BeanReaderType rt) throws BaseException if(rt.getReaderTypeName()=null | .equals(rt.getReaderTypeName() | rt.getReaderTypeName().length()20) throw new BusinessException(读者类别名称必须是1-20个字); if(rt.getLendBookLimitted()100) throw new BusinessException(借阅图书数量必须在0-100

24、之间); ReaderTypeDAO r=new ReaderTypeDAO(); r.createReaderType(rt); public void modifyReaderType(BeanReaderType rt)throws BaseException if(rt.getReaderTypeId()=0) throw new BusinessException(读者类别ID必须是大于0的整数); ReaderTypeDAO r=new ReaderTypeDAO(); r.modifyReaderType(rt); public void deleteReaderType(int

25、 id)throws BaseException if(id=0) throw new BusinessException(读者类别ID必须是大于0的整数); ReaderTypeDAO r=new ReaderTypeDAO(); BeanReaderType b=new BeanReaderType(); b.setReaderTypeId(id); r.modifyReaderType(b); public List searchReader(String keyword,int readerTypeId)throws BaseException List result=new Arra

26、yList(); ReaderDAO r=new ReaderDAO(); r.searchReader(keyword, keyword, readerTypeId); return result; public void createReader(BeanReader r) throws BaseException if(r.getReaderTypeId()20) throw new BusinessException(读者证号必须是1-20个字); if(r.getReaderName()=null | .equals(r.getReaderName() | r.getReaderName().length()20) throw new BusinessException(读者姓名必须是1-20个字); ReaderDAO rt=new ReaderDAO(); rt.createReader(r); public void renameReader(String id,String name) throws BaseException if(id=