javaee oracle

package com.goldsoft.dao.example; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import com.goldsoft.bean.example.Student; import com.goldsoft.util.DBManager; public class StudentDAO { // public int add(Student student){ // PreparedStatement pstmt=null;//ctrl+shift+o // try{ // pstmt=DBManager.getConnection().prepareStatement( // "insert into example_student_tab(gradeid,studentid,name,birthday,sex,height,description)"+ // "values(example_student_seq.nextavl,?,?,?,?,?,?)"); // pstmt.setInt(1,student.getGradeid()); // pstmt.setInt(2,student.getStudentid()); // pstmt.setString(3,student.getName()); // pstmt.setString(4,student.getBirthday()); // pstmt.setInt(5,student.getSex()); // //pstmt.setString(6,String.parseString(student.getHeight())); // pstmt.setString(7,student.getDescription()); // }catch(Exception e){ // e.printStackTrace(); // }finally{ // DBManager.clearup(pstmt); // } // // return 0; // } private static final String SQL_STUDENT_ADD= "insert into example_student_tab(gradeid,studentid,name,birthday,sex,height,description)" +"values(?,example_student_seq.nextval,?,to_date(?,'yyyy-MM-dd'),?,?,?)"; public int add(Student student){ PreparedStatement pstmt=null; try{ pstmt=DBManager.getConnection().prepareStatement(SQL_STUDENT_ADD); pstmt.setInt(1,student.getGradeid()); pstmt.setString(2,student.getName()); pstmt.setString(3,student.getBirthday()); pstmt.setInt(4,student.getSex()); pstmt.setFloat(5,student.getHeight()); pstmt.setString(6,student.getDescription()); int x=pstmt.executeUpdate(); return x; }catch(Exception e){ e.printStackTrace(); }finally{ DBManager.clearup(pstmt); } return 0; } private static final String SQL_STUDENT_UPDATE= "update example_student_tab set gradeid=?,name=?,birthday=to_date(?,'yyyy-mm-dd'),sex=?,height=?,description=? where studentid=?"; public int update(Student student){ PreparedStatement pstmt=null; try{ pstmt=DBManager.getConnection().prepareStatement(SQL_STUDENT_UPDATE); pstmt.setInt(1,student.getGradeid()); pstmt.setString(2,student.getName()); pstmt.setString(3,student.getBirthday()); pstmt.setInt(4,student.getSex()); pstmt.setFloat(5,student.getHeight()); pstmt.setString(6,student.getDescription()); pstmt.setInt(7,student.getStudentid()); int x=pstmt.executeUpdate(); return x; }catch(Exception e){ e.printStackTrace(); }finally{ DBManager.clearup(pstmt); } return 0; } private static final String SQL_STUDENT_DELETE= "delete from example_student_tab where studentid=?"; public int delete(int studentid){ PreparedStatement pstmt=null; try{ pstmt=DBManager.getConnection().prepareStatement(SQL_STUDENT_DELETE); pstmt.setInt(1,studentid); int x=pstmt.executeUpdate(); return 0; }catch(Exception e){ e.printStackTrace(); }finally{ DBManager.clearup(pstmt); } return 0; } private static final String SQL_GET_STUDENT= "select s.*,to_char(s.birthday,'yyyy-mm-dd') birthday1 from example_student_tab s where studentid=?"; public Student get(int studentid){ PreparedStatement pstmt=null; ResultSet rs=null; try{ pstmt=DBManager.getConnection().prepareStatement(SQL_GET_STUDENT); pstmt.setInt(1,studentid); rs=pstmt.executeQuery(); if(rs.next()){ Student st=new Student(); st.setGradeid(rs.getInt("gradeid")); st.setStudentid(rs.getInt("studentid")); st.setName(rs.getString("name")); st.setBirthday(rs.getString("birthday1")); st.setSex(rs.getInt("sex")); st.setHeight(rs.getFloat("height")); st.setDescription(rs.getString("description")); return st; } }catch(Exception e){ e.printStackTrace(); }finally{ DBManager.clearup(pstmt); } return null; } private static final String SQL_GET_STUDENT_LIST="select g.name gname, s.*,to_char(birthday,'yyyy-MM-dd') birthday1 from example_grade_tab g,example_student_tab s where g.gradeid=s.gradeid"; public List<Map<String,String>> getList(){ PreparedStatement pstmt=null; ResultSet rs=null; List<Map<String,String>> list=new ArrayList<Map<String,String>>(); try{ pstmt=DBManager.getConnection().prepareStatement(SQL_GET_STUDENT_LIST); rs=pstmt.executeQuery(); Map<String,String> map=null; while(rs.next()){ map=new HashMap<String,String>(); map.put("gname",rs.getString("gname")); map.put("studentid", String.valueOf(rs.getInt("studentid"))); map.put("name",rs.getString("name")); map.put("birthday",rs.getString("birthday1")); map.put("sex",rs.getInt("sex")==0?"男":"女"); map.put("height",String.valueOf(rs.getFloat("height"))); map.put("description",rs.getString("description")); list.add(map); } } catch(Exception e){ e.printStackTrace(); }finally{ DBManager.clearup(pstmt,rs); } return list; } private static final String SQL_GET_STUDENT_LIST_BY_PAGE = "select * from (select a.*, rownum r from ("+ "select g.name gname, s.*,to_char(birthday,'yyyy-MM-dd') birthday1 from example_grade_tab g,example_student_tab s where g.gradeid=s.gradeid"+ ") a where rownum <=?) where r >=?"; public List<Map<String,String>> getListByPage(int fromIndex,int toIndex){ PreparedStatement pstmt=null; ResultSet rs=null; List<Map<String,String>> list=new ArrayList<Map<String,String>>();//内存图？ try{ pstmt=DBManager.getConnection().prepareStatement(SQL_GET_STUDENT_LIST_BY_PAGE); pstmt.setInt(1,toIndex);//当前页末条记录索引 pstmt.setInt(2,fromIndex);//当前页首条记录索引 rs=pstmt.executeQuery(); Map<String,String> map=null; while(rs.next()){ map=new HashMap<String,String>(); map.put("gname",rs.getString("gname")); map.put("studentid", String.valueOf(rs.getInt("studentid"))); map.put("name",rs.getString("name")); map.put("birthday",rs.getString("birthday1")); map.put("sex",rs.getInt("sex")==0?"男":"女"); map.put("height",String.valueOf(rs.getFloat("height"))); map.put("description",rs.getString("description")); list.add(map); } }catch(Exception e){ e.printStackTrace(); }finally{ DBManager.clearup(pstmt,rs); } return list; } private static final String SQL_GET_STUDENT_COUNT= "select count(1) c from example_student_tab"; //通用应为："select count(1) c from example_grade_tab a,example_student_tab b where a.grade" public int getRecordCount(){ PreparedStatement pstmt=null; ResultSet rs=null; try{ pstmt=DBManager.getConnection().prepareStatement(SQL_GET_STUDENT_COUNT); rs=pstmt.executeQuery(); rs.next(); int c=rs.getInt("c"); return c; }catch(Exception e){ e.printStackTrace(); }finally{ DBManager.clearup(pstmt,rs); } return 0; } }