dabacon.java

java 写的一个地址薄管理工具

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.sxy.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.Vector;

/**
 *
 * @author Arthur
 */
public class DabaCon {

    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;
    private String url;
    //private Addbook addbook;
    public void condata() {
        try {


            String path = DabaCon.class.getResource("").toExternalForm();
            path = path.substring(6);
            path = path + "AddressBook.mdb";
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + path;
            conn = DriverManager.getConnection(url);

           /* String name = "root";
            String password = "root";
            Class.forName("com.mysql.jdbc.Driver");
            String driver = "jdbc:mysql://localhost:3306/addbook";
            setConn(DriverManager.getConnection(driver, name, password));*/
        } catch (SQLException ex) {
            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public boolean isexist(String name) {
        try {
            boolean flag = false;
            //conn.setAutoCommit(false);
            ps = conn.prepareStatement("select * from usertable where username=?");
            ps.setString(1, name);
            rs = ps.executeQuery();
            //conn.commit();
            while (rs.next()) {
                flag = true;
            }
            return flag;
        } catch (SQLException ex) {
            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public Vector selcetbyGroup(String owner, String group) {
        try {
            //conn.setAutoCommit(false);
            Vector v = new Vector();
            ps = conn.prepareStatement("SELECT Aname,Aemail,hphone,Aadd,Agroup FROM addbook WHERE Agroup=? AND owner=?");
            ps.setString(1, group);
            ps.setString(2, owner);
            rs = ps.executeQuery();
            //conn.commit();
            while (rs.next()) {
                Vector ve = new Vector();
                ve.addElement(rs.getString(1));
                ve.addElement(rs.getString(2));
                ve.addElement(rs.getString(3));
                ve.addElement(rs.getString(4));
                ve.addElement(rs.getString(5));
                v.addElement(ve);
            }
            return v;
        } catch (SQLException ex) {
            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
    public Vector selectbyname(String name,String owner){
        try {
            Vector v = new Vector();
            //conn.setAutoCommit(false);
            ps = conn.prepareStatement("SELECT * FROM addbook WHERE Aname=? AND owner=? ");
            ps.setString(1, name);
            ps.setString(2, owner);
            rs=ps.executeQuery();
            //conn.commit();
            while(rs.next()){
                Vector ve=new Vector();
                ve.addElement(rs.getObject("Aname"));
                ve.addElement(rs.getObject("Aemail"));
                ve.addElement(rs.getObject("hphone"));
                ve.addElement(rs.getObject("Aadd"));
                ve.addElement(rs.getObject("Agroup"));
                v.addElement(ve);
            }
            return v;
        } catch (SQLException ex) {
            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
    public Vector selectbyphone(String phone,String owner){
        try {
            Vector v = new Vector();
            //conn.setAutoCommit(false);
            ps=conn.prepareStatement("SELECT * FROM addbook WHERE hphone=? AND owner=?");
            ps.setString(1, phone);
            ps.setString(2, owner);
            rs=ps.executeQuery();
            //conn.commit();
            while(rs.next()){
                Vector ve=new Vector();
                ve.addElement(rs.getObject("Aname"));
                ve.addElement(rs.getObject("Aemail"));
                ve.addElement(rs.getObject("hphone"));
                ve.addElement(rs.getObject("Aadd"));
                ve.addElement(rs.getObject("Agroup"));
                v.addElement(ve);
            }
            
            return v;
        } catch (SQLException ex) {
            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
    public boolean addUser(person p) {
        boolean flag = false;
        try {
            conn.setAutoCommit(false);
            ps = conn.prepareStatement("insert into usertable (username,password) VALUES (?,?) ");
            ps.setString(1, p.getName());
            ps.setString(2, p.getPassword());
            flag = ps.execute();
            conn.commit();
            return flag;
        } catch (SQLException ex) {
            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

    }

    public boolean login(String name, String password) {
        try {
            //conn.setAutoCommit(false);
            boolean flag = false;
            ps = conn.prepareStatement("SELECT * FROM usertable WHERE username=? and password=?");
            ps.setString(1, name);
            ps.setString(2, password);
            rs = ps.executeQuery();
            //conn.commit();
            while (rs.next()) {
                flag = true;
            }
            return flag;
        } catch (SQLException ex) {
            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public Vector select(String owner) {
        try {
           // conn.setAutoCommit(false);
            Vector a = new Vector();
            ps = conn.prepareStatement("SELECT * FROM addbook WHERE owner=?");
            ps.setString(1, owner);
            rs = ps.executeQuery();
            //conn.commit();
            while (rs.next()) {
                Vector b = new Vector();
                b.addElement(rs.getObject(3));
                b.addElement(rs.getObject(9));
                b.addElement(rs.getObject(8));

                b.addElement(rs.getObject(6));
                b.addElement(rs.getObject(11));
                a.addElement(b);
            }
            return a;
        } catch (Exception ex) {
            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public Addbook selectAll(String name, String Agroup) {
        try {
            Addbook ab = new Addbook();
            //conn.setAutoCommit(false);
            ps = conn.prepareStatement("SELECT * FROM addbook where Aname=?  AND owner=?");
            ps.setString(1, name);
            ps.setString(2, Agroup);


            rs = ps.executeQuery();
            //conn.commit();
            while (rs.next()) {
                ab.setOwner(rs.getString("owner"));
                ab.setAadd(rs.getString("Aadd"));
                ab.setAage(rs.getString("Aage"));
                ab.setAemail(rs.getString("Aemail"));
                ab.setAgroup(rs.getString("Agroup"));
                ab.setAhphone(rs.getString("Ahphone"));
                ab.setAname(rs.getString("Aname"));
                ab.setAother(rs.getString("Aother"));
                ab.setAqq(rs.getString("Aqq"));
                ab.setAsex(rs.getString("Asex"));
                ab.setHphone(rs.getString("Hphone"));
                ab.setId(rs.getInt("Id"));
            }
            return ab;
        } catch (SQLException ex) {
            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public boolean addgroup(String owner, String group) {
        try {
            conn.setAutoCommit(false);
            boolean flag = false;
            ps = conn.prepareStatement("INSERT INTO groupname (owner,groupname) VALUES (?,?)");
            ps.setString(1, owner);
            ps.setString(2, group);
            int i = ps.executeUpdate();
            conn.commit();
            if (i != 0) {
                flag = true;
            }
            return flag;
        } catch (SQLException ex) {
            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public boolean removegroup(String owner, String groupname) {
        try {
            boolean flag = false;
            conn.setAutoCommit(false);
            ps = conn.prepareStatement("DELETE FROM groupname WHERE owner=? AND groupname=?");
            ps.setString(1, owner);
            ps.setString(2, groupname);
            int i = ps.executeUpdate();
            conn.commit();
            if (i != 0) {
                flag = true;

            }

            return flag;
        } catch (SQLException ex) {
            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public Vector selectgroup(String owner) {
        try {
            //conn.setAutoCommit(false);
            Vector v = new Vector();
            ps = conn.prepareStatement("SELECT * FROM groupname WHERE owner=? ");
            ps.setString(1, owner);
            rs = ps.executeQuery();
            //conn.commit();
            while (rs.next()) {
                v.addElement(rs.getString(2));
            }
            return v;
        } catch (SQLException ex) {
            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public boolean insert(Addbook a) {
        try {
            conn.setAutoCommit(false);
            ps = conn.prepareStatement("insert into addbook (owner,Aname,Aage,Asex,Aadd,Ahphone,hphone,Aemail,Aqq,Agroup,Aother) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
            ps.setString(1, a.getOwner());
            ps.setString(2, a.getAname());
            ps.setString(3, a.getAage());
            ps.setString(4, a.getAsex());
            ps.setString(5, a.getAadd());
            ps.setString(6, a.getAhphone());
            ps.setString(7, a.getHphone());

            ps.setString(8, a.getAemail());
            ps.setString(9, a.getAqq());
            ps.setString(10, a.getAgroup());
            ps.setString(11, a.getAother());
            int num = ps.executeUpdate();
            conn.commit();
            if (num != 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {

            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public boolean removeByName(String name, String owner) {
        try {
            conn.setAutoCommit(false);
            ps = conn.prepareStatement("DELETE FROM addbook WHERE Aname=? and owner=?");
            ps.setString(1, name);
            ps.setString(2, owner);
            int i = ps.executeUpdate();
            conn.commit();
            boolean flag = false;
            if (i != 0) {
                flag = true;
            }
            return flag;
        } catch (SQLException ex) {
            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public boolean update(Addbook a, int id) {
        try {
            conn.setAutoCommit(false);
            ps = conn.prepareStatement("update  addbook set owner=?,Aname=?,Aage=?,Asex=?,Aadd=?,Ahphone=?,hphone=?,Aemail=?,Aqq=?,Agroup=?,Aother=? WHERE ID=? ");
            ps.setString(1, a.getOwner());
            ps.setString(2, a.getAname());
            ps.setString(3, a.getAage());
            ps.setString(4, a.getAsex());
            ps.setString(5, a.getAadd());
            ps.setString(6, a.getAhphone());
            ps.setString(7, a.getHphone());
            ps.setString(8, a.getAemail());
            ps.setString(9, a.getAqq());
            ps.setString(10, a.getAgroup());
            ps.setString(11, a.getAother());
            ps.setInt(12, id);
            int i = ps.executeUpdate();
            boolean flag = false;
            if (i != 0) {
                flag = true;
            }
            conn.commit();
            //int i=ps.executeUpdate();
            return flag;
        } catch (SQLException ex) {
            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    /* public static void main(String args[]) {
    new DabaCon().condata();
    }*/

    public void close() {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DabaCon.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public Connection getConn() {
        return conn;
    }

    public void setConn(Connection conn) {
        this.conn = conn;
    }

    public PreparedStatement getPs() {
        return ps;
    }

    public void setPs(PreparedStatement ps) {
        this.ps = ps;
    }

    public ResultSet getRs() {
        return rs;
    }

    public void setRs(ResultSet rs) {
        this.rs = rs;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }
}