dataaccess.java

基于MVC模式的学生选课系统。实现学生信息注册、学生选课和管理员的课程信息维护操作

package dao;
import bean.LessonBean;
import java.sql.*;
import java.util.*;

public class DataAccess {

        private String className="com.mysql.jdbc.Driver";
        private String serverURL="jdbc:mysql://localhost/registersystem";
        private String userName="root";
        private String password="ruan0405";
        private Connection con=null;

        public DataAccess(){

        }

        public void insert(String id,String name,String email){

                try{
                	    Class.forName(className).newInstance();
                        con=DriverManager.getConnection(serverURL, userName, password);
                        PreparedStatement sql = con.prepareStatement("insert into student values(?,?,?)");
                        sql.setString(1, id);
                        sql.setString(2, name);
                        sql.setString(3, email);

                        sql.executeUpdate();
                        sql.close();
                        con.close();
                }catch(SQLException ex){

                        System.out.println(ex.getMessage());
                }

                catch(Exception ex){

                        System.out.println(ex.getMessage());
                }
        }

        public boolean search(String number){

                boolean flag=false;

                try{
                	    Class.forName(className).newInstance();
                        con=DriverManager.getConnection(serverURL, userName, password);
                        Statement sqlCMD=con.createStatement();
                        ResultSet result=sqlCMD.executeQuery("select sid from student");

                        while(result.next()){

                                if(number.equals(result.getString("sid"))){

                                        flag=true;
                                        return flag;
                                }
                        }
                        result.close();
                        sqlCMD.close();
                        con.close();
                }catch(SQLException ex){

                        System.out.println(ex.getMessage());
                }

                catch(Exception ex){

                        System.out.println(ex.getMessage());
                }

                return flag;
        }

        public Vector<LessonBean> showCourse(){
        		
        		LessonBean lesson = new LessonBean();
                Vector<LessonBean> vector = new Vector<LessonBean>();

                try{
                	    Class.forName(className).newInstance();
                	    con=DriverManager.getConnection(serverURL, userName, password);
                        Statement sqlCMD=con.createStatement();
                        ResultSet result=sqlCMD.executeQuery("select lid,lname from lesson");


                        while(result.next()){
                        	
                        	lesson.setLid(result.getString("lid"));
                        	lesson.setLname(result.getString("lname"));
                            vector.add(lesson);              
                            //Test data
                            //System.out.println(lesson.getLid()+lesson.getLname());
                                
                        }
                        result.close();
                        sqlCMD.close();
                        con.close();
                }catch(SQLException ex){

                        System.out.println(ex.getMessage());
                }

                catch(Exception ex){

                        System.out.println(ex.getMessage());
                }

                return vector;
        }

        public void insertRegist(String sid,String lid,int score){

                try{
                		Class.forName(className).newInstance();
                		con=DriverManager.getConnection(serverURL, userName, password);
                        PreparedStatement sql = con.prepareStatement("insert into register values(?,?,?)");
                        sql.setString(1, sid);
                        sql.setString(2, lid);
                        sql.setInt(3, score);

                        sql.executeUpdate();
                        sql.close();
                        con.close();
                }catch(SQLException ex){

                        System.out.println(ex.getMessage());
                }

                catch(Exception ex){

                        System.out.println(ex.getMessage());
                }
        }
        
        public void insertLesson(String lid,String lname){

            try{
            		Class.forName(className).newInstance();
            		con=DriverManager.getConnection(serverURL, userName, password);
                    PreparedStatement sql = con.prepareStatement("insert into lesson values(?,?)");
                    sql.setString(1, lid);
                    sql.setString(2, lname);

                    sql.executeUpdate();
                    sql.close();
                    con.close();
            }catch(SQLException ex){

                    System.out.println(ex.getMessage());
            }

            catch(Exception ex){

                    System.out.println(ex.getMessage());
            }
    }

}