databaseoperate.java~23~

可以对名片进行增删改查,用户管理包括增删改查等,对名片查询时可以模糊查询

package card;

import java.util.ArrayList;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;

/**
 * <p>Title: Card</p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2007</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
public class DatabaseOperate {
    cardmodel cm;
    typemodel tm;
    allmodel am;
    public DatabaseOperate() {
    }

    /**
     * 功能：从数据库中查询出所有满足条件的记录
     * @param sql2 String
     * @return ArrayList
     */
    public ArrayList selectall(String sql2) {
        ArrayList al = new ArrayList();
        Connection conn;
        Statement stm;
        ResultSet rs;
        String sql = "select carid,typetable.tpid,tpname,frdname,sex,birthday,depart,power,homephone,officephone,mobil,address,number,email from cardtable,typetable where cardtable.tpid=typetable.tpid " +
                     sql2;
        try {
            conn = new dbconn().getconnection();
            stm = conn.createStatement();
            rs = stm.executeQuery(sql);
            while (rs.next()) {
                cm = new cardmodel();
                tm = new typemodel();
                am = new allmodel();
                cm.setCarid(rs.getInt("carid"));
                tm.setTyid(rs.getInt(2));
                tm.setTyname(rs.getString("tpname"));
                cm.setFrdname(rs.getString("frdname"));
                cm.setSex(rs.getString("sex"));
                cm.setBirthday(rs.getString("birthday"));
                cm.setDepart(rs.getString("depart"));
                cm.setPower(rs.getString("power"));
                cm.setHomephone(rs.getString("homephone"));
                cm.setOfficephone(rs.getString("officephone"));
                cm.setMobil(rs.getString("mobil"));
                cm.setAddress(rs.getString("address"));
                cm.setNumber(rs.getString("number"));
                cm.setEmail(rs.getString("email"));
                am.setall(cm, tm);
                al.add(am);
            }
            rs.close();
            stm.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return al;
    }

    /**
     * 功能：删除指定cardid的记录
     * @param cardid String
     * @return boolean
     */
    public boolean delInfo(String cardid) {
        boolean flag = true;
        Connection conn;
        PreparedStatement pstm;
        String sql = "delete from cardtable where carid=(?)";
        try {
            conn = new dbconn().getconnection();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, cardid);
            int intre = pstm.executeUpdate();
            if (intre == 0) {
                flag = false;
            }
            pstm.close();
            conn.close();
        } catch (Exception ex) {
            flag = false;
            ex.printStackTrace();
        }
        return flag;
    }

    /**
     * 类别的增删改
     * @param tm typemodel
     * @return boolean
     */
    public boolean typeinsert(typemodel tm) {
        boolean flag = false;
        Connection db = new dbconn().getconnection();
        PreparedStatement ps = null;
        String sql = "insert into typetable(tpname) values (?)";
        try {
            ps = db.prepareStatement(sql);
            ps.setString(1, tm.getTyname());
            int n = ps.executeUpdate();
            if (n != 0) {
                flag = true;
            }
            ps.close();
            db.close();
        } catch (Exception ex) {
            System.out.println(ex);
            return false;
        }
        return flag;
    }

    public boolean typeupdate(typemodel tm) {
        boolean flag = false;
        Connection db = new dbconn().getconnection();
        PreparedStatement ps = null;
        String sql = "update typetable set tpname=? where tpid=?";
        try {
            ps = db.prepareStatement(sql);
            ps.setString(1, tm.getTyname());
            ps.setInt(2, tm.getTyid());
            int n = ps.executeUpdate();
            if (n != 0) {
                flag = true;
            }
            ps.close();
            db.close();
        } catch (Exception ex) {
            System.out.println(ex);
            return false;
        }
        return flag;
    }

    public boolean typedelete(typemodel tm) {
        boolean flag = false;
        Connection db = new dbconn().getconnection();
        PreparedStatement ps = null;
        String sql = "delete typetable where tpid=?";
        try {
            ps = db.prepareStatement(sql);
            ps.setInt(1, tm.getTyid());
            int n = ps.executeUpdate();
            if (n != 0) {
                flag = true;
            }
            ps.close();
            db.close();
        } catch (Exception ex) {
            System.out.println(ex);
            return false;
        }
        return flag;
    }

    /**
     * card类的增修sql语句
     */
    public cardmodel cardselect(cardmodel cd) {
        int car = cd.getCarid();
        cardmodel carmodel = new cardmodel();
        Connection db = new dbconn().getconnection();
        Statement ps = null;
        String sql = "select tpid,frdname,sex,birthday,depart,power,homephone,officephone,mobil,address,number,email from cardtable where carid=" +
                     car + "";
        try {
            ps = db.createStatement();
            ResultSet res = ps.executeQuery(sql);
            while (res.next()) {
                carmodel.setTpid(res.getInt(1));
                carmodel.setFrdname(res.getString(2));
                carmodel.setSex(res.getString(3));
                carmodel.setBirthday(res.getString(4));
                carmodel.setDepart(res.getString(5));
                carmodel.setPower(res.getString(6));
                carmodel.setHomephone(res.getString(7));
                carmodel.setOfficephone(res.getString(8));
                carmodel.setMobil(res.getString(9));
                carmodel.setAddress(res.getString(10));
                carmodel.setNumber(res.getString(11));
                carmodel.setEmail(res.getString(12));
            }
            res.close();
            ps.close();
            db.close();
        } catch (Exception ex) {
            System.out.println(ex);
            System.out.println("获得名片信息时出错");
        }
        return carmodel;
    }

     public boolean cardupdate(allmodel am) {
        boolean flag = false;
        Connection db = new dbconn().getconnection();
        PreparedStatement ps = null;

         cardmodel cm=am.getcm();
         typemodel tm=am.gettym();
            String name="select tyid from typetable where tyname='"+tm.getTyname()+"'";
      try {
           Connection db2 = new dbconn().getconnection();
           Statement sss=db2.createStatement();
           ResultSet ress=sss.executeQuery(name);
           while(ress.next()){
               cm.setTpid(ress.getInt(1));
           }
           ress.close();
           sss.close();
           db2.close();
      } catch (Exception ex) {

      }
        String sql = "update cardtable set tpid=?,frdname=?,sex=?,birthday=?,depart=?,power=?,homephone=?,officephone=?,mobil=?,address=?,number=?,email=? where carid=?";

        try {
            ps = db.prepareStatement(sql);
            ps.setInt(1, cm.getTpid());
            ps.setString(2, cm.getFrdname());
            ps.setString(3, cm.getSex());
            ps.setString(4, cm.getBirthday());
            ps.setString(5, cm.getDepart());
            ps.setString(6, cm.getPower());
            ps.setString(7, cm.getHomephone());
            ps.setString(8, cm.getOfficephone());
            ps.setString(9, cm.getMobil());
            ps.setString(10, cm.getAddress());
            ps.setString(11, cm.getNumber());
            ps.setString(12, cm.getEmail());
            ps.setInt(13, cm.getCarid());
            int n = ps.executeUpdate();
            if (n != 0) {
                flag = true;
            }
            ps.close();
            db.close();
        } catch (Exception ex) {
            System.out.println(ex);
            System.out.println("更新名片信息时出错");
        }
        return flag;
    }

    /**
     * 功能：增加名片
     * @param cd cardmodel
     * @return boolean
     */
    public boolean cardinsert(cardmodel cd) {
        boolean flag = false;
        Connection db = new dbconn().getconnection();
        PreparedStatement ps = null;
        String sql = "insert into cardtable(carid,tpid,frdname,sex,birthday,depart,power,homephone,officephone,mobil,address,number,email) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";

        try {
            ps = db.prepareStatement(sql);
            ps.setInt(1, cd.getCarid());
            ps.setInt(2, cd.getTpid());
            ps.setString(3, cd.getFrdname());
            ps.setString(4, cd.getSex());
            ps.setString(5, cd.getBirthday());
            ps.setString(6, cd.getDepart());
            ps.setString(7, cd.getPower());
            ps.setString(8, cd.getHomephone());
            ps.setString(9, cd.getOfficephone());
            ps.setString(10, cd.getMobil());
            ps.setString(11, cd.getAddress());
            ps.setString(12, cd.getNumber());
            ps.setString(13, cd.getEmail());
            int n = ps.executeUpdate();
            if (n != 0) {
                flag = true;
            }
            ps.close();
            db.close();
        } catch (Exception ex) {
            System.out.println(ex);
            System.out.println("创建名片信息时出错");
        }
        return flag;
    }

    public int checkLoginInfo(usermodel um) {
        int ctn = 100;
        Connection conn = new dbconn().getconnection();
        String sql = "select username,password from usertable where username='" +
                     um.getUsername() + "'";
        try {
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery(sql);
            boolean b = rs.next();
            if (b) {
                if (um.getPassword().equals(rs.getString(2).trim())) {
                    ctn = 0; //正确
                } else {
                    ctn = 1; //密码不正确
                }
            } else {
                ctn = 2; //无用户名
            }
            rs.close();
            s.close();
            conn.close();
        } catch (Exception ex) {
            System.out.println("sss" + ex.getMessage());
        }
        return ctn;
    }

    public boolean updateUserPassword(usermodel um) { //修改用户密码
        boolean b = false;
        Connection conn = new dbconn().getconnection();
        String sql = "update usertable set password='" + um.getPassword() +
                     "' where username='" + um.getUsername() + "'";
        try {
            Statement s = conn.createStatement();
            int e = s.executeUpdate(sql);
            if (e != 0) {
                b = true;
            }
            conn.commit();
            s.close();
            conn.close();
        } catch (Exception ex) {
            System.out.println("updateUserPassword:" + ex.getMessage());
        }
        return b;
    }

    public ArrayList getBirthdayArr() {
        Connection conn = new dbconn().getconnection();
        ArrayList birthday = new ArrayList();
        String sql = "select birthday,frdname from cardtable";
        try {
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery(sql);

            while (rs.next()) {
                birthday.add(rs.getString(1).trim());
                birthday.add(rs.getString(2).trim());
            }
            s.close();
            conn.close();
        } catch (Exception ex) {
            System.out.println("updateUserPassword:" + ex.getMessage());
        }
        return birthday;
    }

    public ArrayList selectType() {
        ArrayList arr = new ArrayList();
        Connection conn = new dbconn().getconnection();
        String sql = "select tpid,tpname from typetable";
        try {
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery(sql);
            while (rs.next()) {
                typemodel tm = new typemodel();
                tm.setTyid(rs.getInt(1));
                tm.setTyname(rs.getString(2));
                arr.add(tm);
            }
            s.close();
            conn.close();
        } catch (Exception ex) {
            System.out.println("updateUserPassword:" + ex.getMessage());
        }
        return arr;
    }
}