数据库系统实现考试资料-汽院.docx

B/S架构大对象处理protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException request.setCharacterEncoding(UTF-8); Student student = new Student(); / Check that we have a file upload request boolean isMultipart = ServletFileUpload.isMultipartContent(request); if (!isMultipart) try throw new Exception(Form enctype Error); catch (Exception ex) Logger.getLogger(Display.class.getName().log(Level.SEVERE, null, ex); / Create a factory for disk-based file items DiskFileItemFactory factory = new DiskFileItemFactory(); / Set factory constraints-内存阈值 factory.setSizeThreshold(512 * 1024); /设置临时文件夹 ServletContext servletContext = this.getServletConfig().getServletContext(); File repository = (File) servletContext.getAttribute(javax.servlet.context.tempdir); factory.setRepository(repository); / Create a new file upload handler ServletFileUpload upload = new ServletFileUpload(factory); / Set overall request size constraint upload.setSizeMax(10 * 1024 * 1024); /10M try / Parse the request List items = upload.parseRequest(request); / Process the uploaded items Iterator iter = items.iterator(); while (iter.hasNext() FileItem item = iter.next(); if (item.isFormField() /非 file 字段 String name = item.getFieldName(); /form中字段名 String value = new String(item.getString().getBytes(iso-8859-1), UTF-8);/必须经过转码，否则会出现乱码现象 if (name.equals(sno) student.setSno(value); else if (name.equals(sname) student.setSname(value); else if (name.equals(sage) student.setSage(Integer.parseInt(value); else InputStream uploadedStream = item.getInputStream(); String fieldName = item.getFieldName(); /form中字段名 String fileName = item.getName(); /文件的名字，是否带路径与浏览器有关 / String contentType = item.getContentType(); /文件的类型 / boolean isInMemory = item.isInMemory(); long sizeInBytes = item.getSize(); /文件的大小 if (fileName != null & !.equals(fileName) & sizeInBytes != 0) /存入硬盘 String path = this.getServletConfig().getServletContext().getRealPath(/photo); File uploadDir = new File(path); if (!uploadDir.exists() uploadDir.mkdir(); int index = fileName.lastIndexOf(File.separator); if (index 0) fileName = fileName.substring(index + 1, fileName.length(); File fileToSave = new File(uploadDir, fileName); student.setSimage(fileToSave.getAbsolutePath(); try item.write(fileToSave); catch (Exception ex) Logger.getLogger(Display.class.getName().log(Level.SEVERE, null, ex); /如果选择了图片，uploadedStream.available() 0 ,否则没有选择图片，uploadedStream.available() = 0 student.setPhoto(uploadedStream); /存入新添加的学生记录/ DaoFactory.getStudentDao().insertStudentByStream(student);/ response.sendRedirect(request.getContextPath() + /Student/Index); uploadedStream.close(); catch (FileUploadException ex) /对发生的异常进行捕获，然后转发到输入页面，显示错误信息提示/ String errValue = 图片尺寸应小于1M，请重新选择正确的大小!;/ request.setAttribute(insertError, errValue);/ request.getRequestDispatcher(/Student/Insert).forward(request, response); response.setContentType(text/html;charset=UTF-8); PrintWriter out = response.getWriter(); try out.println(); out.println(); out.println(); out.println(Servlet Display); out.println(); out.println(); out.println(Servlet Display at + request.getContextPath() + ); out.println(学号： + student.getSno() + ); out.println(姓名： + student.getSname() + ); out.println(年龄： + student.getSage() + ); out.println(图片： + student.getSimage() + ); out.println(); out.println(); finally out.close(); 存储过程(查询，将结果保存在游标中) create or replace procedure getHealthIndexByhino(g_hino in varchar2,outcursor out mycursor) astype mycursor is ref cursor; begin open outcursor for select * from healthindex A join c_doctor B on A.Hino = B.Hino where A.Hino=g_hino.hino ;end; 触发器表1、行级触发器中关联名的意义触发语句:OLD:NEWINSERT未定义-所有字段均为null触发语句完成时，要插入的值UPDATE更新以前相应记录行的原始值触发语句完成时，要更新的值DELETE更新以前相应记录行的原始值未定义-所有字段均为null、同一个事件可触发多个触发器。同一个事件触发的不同触发器执行顺序为：语句(表)级BEFORE触发器行级BEFORE触发器触发语句行级AFTER触发器语句（表）级AFTER触发器create or replace trigger tr_scafter insert or delete or update on scdeclare cursor cur_sc is select sno,avg(grade) avggrade from sc group by sno;begin for temp in cur_sc loop update student set savggrade=temp.avggrade where sno=temp.sno; end loop;end;数据库连接程序public class DatabaseBean public static Connection getConnection() Connection conn = null; String driver = oracle.jdbc.driver.OracleDriver; String url = jdbc:oracle:thin:localhost:1521:xe; String user = SCOTT; String password = tiger; try Class.forName(driver); conn = DriverManager.getConnection(url, user, password); catch (ClassNotFoundException ex) Logger.getLogger(DatabaseBean.class.getName().log(Level.SEVERE, null, ex); catch (SQLException ex) Logger.getLogger(DatabaseBean.class.getName().log(Level.SEVERE, null, ex); return conn; public static void close(ResultSet rs, Statement st, Connection conn) try if (rs != null) rs.close(); if(st != null) st.close(); if(conn != null) conn.close(); catch (SQLException ex) Logger.getLogger(DatabaseBean.class.getName().log(Level.SEVERE, null, ex); tabelmodel表格模型的设计package com.mis.tablemodel;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import javax.swing.ImageIcon;import javax.swing.table.AbstractTableModel;import com.mis.model.Student;import com.mis.util.DAOFactory;public class StudentAbstractTableModel extends AbstractTableModel List rowData;List columnNames;public StudentAbstractTableModel() rowData = new ArrayList();columnNames = new ArrayList();try rowData = DAOFactory.getStudentDAO().getAllStudents(); catch (SQLException e) e.printStackTrace();setColumnNames();public StudentAbstractTableModel(String sno) rowData = new ArrayList();columnNames = new ArrayList();try Student stu = DAOFactory.getStudentDAO().getStudentBySno(sno);if(stu!=null)rowData.add(stu); catch (SQLException e) e.printStackTrace();setColumnNames();private void setColumnNames() columnNames.add(学号);columnNames.add(姓名);columnNames.add(性别);columnNames.add(年龄);columnNames.add(系部);columnNames.add(平均成绩);columnNames.add(照片);Overridepublic int getColumnCount() return columnNames.size();Overridepublic int getRowCount() return rowData.size();Overridepublic Object getValueAt(int rowIndex, int columnIndex) Student stu = rowData.get(rowIndex);if(stu!=null) switch (columnIndex) case 0:return stu.getSno();case 1:return stu.getSname();case 2:return stu.getSsex();case 3:return stu.getSage();case 4:return stu.getSdept();case 5:return stu.getSavgGrade();case 6:return stu.getPhoto();default:return null; else return null;Overridepublic Class getColumnClass(int columnIndex) switch (columnIndex) case 0:case 1:case 2:case 4:return String.class;case 3:return Integer.class;case 5:return Double.class;case 6:return ImageIcon.class;default:return Object.class;Overridepublic String getColumnName(int column) return columnNames.get(column);乐观锁与悲观锁w 悲观锁定：对并发冲突采取一种悲观的态度,认为相关业务的并发冲突度高，把本事务拟将更新的行在查询时就加锁，从而阻止其它事务更新这些行； 在SELECT语句后加上FOR UPDATE 也可以在select子句后用for update nowait 用在C/S架构w 乐观锁定：对并发冲突采取乐观的态度，认为事务并发冲突度不高，对拟将更新的记录在查询时不锁定，在真正更新时再作检查，检查通过后方锁定。 w 实施乐观锁的方法 更新前在应用中存储所要操作行的“前映像”，更新时对操作行的当前值与存储的旧记录进行比对，如果数据一致则表明没有并发冲突，就提交更新；否则则需要根据业务逻辑来作进一步处理。 Selec col1,col2 from table into old_col1,old_col2 where primary_key =:primary_key Update table Set col1 = :new_col1, col2 = :new_col2, . Where primary_key = :primary_key