dbconnect.java

小型宠物医院网站

package DB;

import java.sql.Connection;
import java.sql.DriverManager;
import Bean.*;
import java.util.ArrayList;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DBConnect {
    public DBConnect() {
    }

    public Connection getCon() {
        Connection con = null;
        try {
//            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
//            con = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;databaseName=Pets","sa","");
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            con=DriverManager.getConnection("jdbc:odbc:test0");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return con;
    }

    //用户操作
    public ArrayList getUsers() { //得到所有用户
        ArrayList ar = new ArrayList();
        PreparedStatement ps = null;
        Connection con = null;
        ResultSet rs = null;
        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_UserDis}");
            rs = ps.executeQuery();
            userBean users = null;
            while (rs.next()) {
                users = new userBean();
                users.setUserId(rs.getInt(1));
                users.setUserName(rs.getString(2));
                users.setPassWord(rs.getString(3));
                users.setClassMessage(rs.getString(4));
                ar.add(users);

            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return ar;
    }

    public int deleteUsers(int userId) { //删除用户
        int i = 5;
        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_deleteUsers(?)}");
            ps.setInt(1, userId);
            i = ps.executeUpdate();
        } catch (Exception ex) {
         //   ex.printStackTrace();
        }
        return i;
    }

   public boolean checkUser(String username,String password){//检验登录
       boolean flag=false;
       Connection con=null;
       PreparedStatement ps=null;
       ResultSet rs=null;
       try {
           con=this.getCon();
           ps=con.prepareStatement("select * from users where userName=? and passWord=?");
           ps.setString(1,username);
           ps.setString(2,password);
           rs=ps.executeQuery();
           if (rs.next()) {
               flag=true;
           }
       } catch (Exception ex) {
           flag=false;
       }
       return flag;
   }
    public int addUsers(String username, String psd) { //增加用户
        Connection con = null;
        PreparedStatement ps = null;
        int i = 5;
        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_AddUser(?,?)}");
            ps.setString(1, username);
            ps.setString(2, psd);
            i = ps.executeUpdate();
        } catch (Exception ex) {
            //ex.printStackTrace();
        }
        return i;
    }

    //宠物主人操作
    public ArrayList getOwners() { //查询所有主人
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList ar = new ArrayList();
        try {
            OwnersBean owners = null;
            con = this.getCon();
            ps = con.prepareCall("{call proc_OwnerDis}");
            rs = ps.executeQuery();
            while (rs.next()) {
                owners = new OwnersBean();
                owners.setOwnerId(rs.getInt(1));
                owners.setOwnerName(rs.getString(2));
                owners.setOwnerPhone(rs.getString(3));
                owners.setOwnerImage(rs.getString(4));
                ar.add(owners);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return ar;
    }

    public ArrayList getOwnersByName(String ownername) { //按姓名查询主人
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList ar = new ArrayList();
        try {
            OwnersBean owners = null;
            con = this.getCon();
            ps = con.prepareCall("{call proc_OwnerDis(?)}");
            ps.setString(1, ownername);
            rs = ps.executeQuery();
            while (rs.next()) {
                owners = new OwnersBean();
                owners.setOwnerId(rs.getInt(1));
                owners.setOwnerName(rs.getString(2));
                owners.setOwnerPhone(rs.getString(3));
                owners.setOwnerImage(rs.getString(4));
                ar.add(owners);
            }
        } catch (Exception ex) {
          //  ex.printStackTrace();
        }
        return ar;
    }

    public OwnersBean getOwnersById(int ownerId) { //按id查询主人
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList ar = new ArrayList();
        OwnersBean owners = null;
        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_OwnerById(?)}");
            ps.setInt(1, ownerId);
            rs = ps.executeQuery();
            while (rs.next()) {
                owners = new OwnersBean();
                owners.setOwnerId(rs.getInt(1));
                owners.setOwnerName(rs.getString(2));
                owners.setOwnerPhone(rs.getString(3));
                owners.setOwnerImage(rs.getString(4));

            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return owners;
    }

    public int addOwners(String ownername, String ownerphone, String ownerimage) { //增加主人
        int i = 5;
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_AddOwner(?,?,?)}");
            ps.setString(1, ownername);
            ps.setString(2, ownerphone);
            ps.setString(3, ownerimage);
            i = ps.executeUpdate();
        } catch (Exception ex) {

        }
        return i;
    }


    public int updateOwners(int ownerId, String ownerphone,String ownerimage) throws Exception { //修改主人
        int i = 5;
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_UpdateOwner(?,?,?)}");
            ps.setInt(1, ownerId);
            ps.setString(2, ownerphone);
            ps.setString(3,ownerimage);
            i = ps.executeUpdate();
        } catch (Exception ex) {
          throw ex;
        }
        return i;
    }

    public int deleteOwners(int ownerId) { //删除主人
        int i = 5;
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_DeleteOwner(?)}");
            ps.setInt(1, ownerId);
            i = ps.executeUpdate();
        } catch (Exception ex) {
          //  ex.printStackTrace();
        }
        return i;
    }

    //宠物类别操作
    public ArrayList getType() { //得到所有宠物类别
        ArrayList ar = new ArrayList();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        petTypeBean type = null;
        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_TypeDis}");
            rs = ps.executeQuery();
            while (rs.next()) {
                type = new petTypeBean();
                type.setTypeId(rs.getInt(1));
                type.setTypeName(rs.getString(2));
                type.setTypeMessage(rs.getString(3));
                ar.add(type);
            }
        } catch (Exception ex) {
          //  ex.printStackTrace();
        }
        return ar;
    }

    public petTypeBean getTypeById(int typeid) { //按编号得到宠物类别
    //    ArrayList ar = new ArrayList();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        petTypeBean type = new petTypeBean();
        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_TypeById(?)}");
            ps.setInt(1,typeid);
            rs = ps.executeQuery();
            if (rs.next()) {
                type = new petTypeBean();
                type.setTypeId(rs.getInt(1));
                type.setTypeName(rs.getString(2));
                type.setTypeMessage(rs.getString(3));
            }
        } catch (Exception ex) {
           // ex.printStackTrace();
        }
        return type;
    }


    public int deleteType(int typeId) { //删除类别
        int i = 5;
        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_DeleteType(?)}");
            ps.setInt(1, typeId);
            i = ps.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return i;

    }

    public int addType(String typename, String typemessage) { //增加类别
        int i = 5;
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_AddType(?,?)}");
            ps.setString(1, typename);
            ps.setString(2, typemessage);
            i = ps.executeUpdate();
        } catch (Exception ex) {
        //    ex.printStackTrace();
        }
        return i;
    }

    //兽医专业操作
    public ArrayList getAllSpec() { //得到所有专业
        ArrayList ar = new ArrayList();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_spDis}");
            rs = ps.executeQuery();
            docSpBean spec;
            while (rs.next()) {
                spec = new docSpBean();
                spec.setSpId(rs.getInt(1));
                spec.setSpName(rs.getString(2));
                spec.setSpMessage(rs.getString(3));
                ar.add(spec);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return ar;
    }


    //兽医操作
    public int updateDoc(int docid, String docphone, String docmessage,
                         String docimage) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        int i = 5;
        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_UpdateDoc(?,?,?,?)}");
            ps.setInt(1, docid);
            ps.setString(2, docphone);
            ps.setString(3, docmessage);
            ps.setString(4, docimage);
            i = ps.executeUpdate();
        } catch (Exception ex) {
           throw ex;
        }
        return i;
    }

    public int updateDocs(int docid, String docphone, String docmessage)throws Exception { //更新兽医
        Connection con = null;
        PreparedStatement ps = null;
        int i = 5;
        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_UpdateDoc(?,?,?)}");
            ps.setInt(1, docid);
            ps.setString(2, docphone);
            ps.setString(3, docmessage);
            i = ps.executeUpdate();
        } catch (Exception ex) {
           throw ex;
        }
        return i;
    }

    public int updateDocSpec(int docid, int spid) { //更新兽医专业
        Connection con = null;
        PreparedStatement ps = null;
        int i = 5;
        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_UpdatedocSpec(?,?)}");
            ps.setInt(1, docid);
            ps.setInt(2, spid);
            i = ps.executeUpdate();
        } catch (Exception ex) {

        }
        return i;

    }

    public void deleteDoc(int docid) throws Exception { //删除兽医
        Connection con = null;
        PreparedStatement ps = null;
        int i = 5;
        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_DeleteDoc(?)}");
            ps.setInt(1, docid);
            i = ps.executeUpdate();
        } catch (Exception ex) {
             throw ex;
        }

    }

    public ArrayList getAllDoc() { //得到所有兽医
        ArrayList ar = new ArrayList();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = this.getCon();
            ps = con.prepareCall("{call proc_DocDis}");
            rs = ps.executeQuery();
            DoctorBean doctor;
            while (rs.next()) {
                doctor = new DoctorBean();
                doctor.setDocId(rs.getInt(1));
                String docname = rs.getString(2);
                doctor.setDocName(docname);
                doctor.setDocPhone(rs.getString(3));
                doctor.setDocSpId(this.getSpecid(docname));
                doctor.setDocMessage(rs.getString(4));
                doctor.setDocImage(rs.getString(5));
                ar.add(doctor);