package telbook;

import java.sql.*;
import java.util.*;
import telbook.*;


public class SQL_jh {
    public SQL_jh() {
    }

    /**/
    public static Connection getconn() {
        Connection conn = null;
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection("jdbc:odbc:accp");
        } catch (Exception ax) {
            System.out.println("" + ax);
        }
        return conn;
    }

    /*插入電話號碼*/
    public static boolean addTel(String a, String b) {
        boolean ok = false;
        try {
            Connection con = getconn();
            String sql = "insert into TelBook(uname,utel) values(?,?)";
            PreparedStatement pre = con.prepareStatement(sql);
            pre.setString(1, a);
            pre.setString(2, b);
            //
            int c = pre.executeUpdate();
            if (c > 0) {
                ok = true;
            }
            pre.close();
            con.close();
        } catch (Exception bx) {
            System.out.println("" + bx);
        }
        return ok;
    }

    /*顯示電話信息（查詢）*/
    public static List selTel() {
        List list = null;
        try {
            Connection con = getconn();
            String sql = "select * from TelBook";
            Statement sta = con.createStatement();
            ResultSet res = sta.executeQuery(sql);
            //
            list = new ArrayList();
            while (res.next()) {
                ShiTi a = new ShiTi(); //實體類，放表
                a.setUid(res.getInt(1));
                a.setUname(res.getString(2));
                a.setUtel(res.getString(3));
                list.add(a);
            }
            res.close();
            sta.close();
            con.close();
        } catch (Exception cx) {
            System.out.println("" + cx);
        }
        return list;
    }

//    public static void main(String[] args) {
//        SQL_jh s = new SQL_jh();
//        ArrayList list = new ArrayList();
//        list = (ArrayList) s.selTel();
//        System.out.println(""+list);
//
//    }
    public static boolean deletetel(int a) {
        boolean ok = false;
        try {
            Connection con = getconn();
            PreparedStatement pre = con.prepareStatement(
                    "delete from TelBook where uid=?");
            pre.setInt(1, a);
            int po = pre.executeUpdate();
            if (po > 0) {
                ok = true;
            }
        } catch (Exception dx) {
            System.out.println("" + dx);
        }
        return ok;
    }

    /*按主鍵查詢*/
    public static List selectTel(int id) {
        List list = null;
        try {
            Connection con = getconn();
            String sql = "select * from TelBook where uid=?";
            PreparedStatement pre = con.prepareStatement(sql);
            pre.setInt(1, id);
            ResultSet rs = pre.executeQuery();
            list = new ArrayList();
            while (rs.next()) {
                ShiTi a = new ShiTi();
                a.setUid(rs.getInt(1));
                a.setUname(rs.getString(2));
                a.setUtel(rs.getString(3));
                list.add(a);
            }
        } catch (Exception ex) {
            System.out.println("" + ex);
        }
        return list;
    }

    public static ShiTi selectInfo(int id) {
        ShiTi shiti = null;
        Connection conn = getconn();
        try {
            String sql1 = "select * from TelBook where uid=?";
            PreparedStatement pree = conn.prepareStatement(sql1);
            pree.setInt(1, id);
            ResultSet re = pree.executeQuery();
            if (re.next()) {
                shiti = new ShiTi();
                shiti.setUid(re.getInt(1));
                shiti.setUname(re.getString(2));
                shiti.setUtel(re.getString(3));
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return shiti;
    }

    //修改方法
    public static boolean updateInfo(String name, String telephone, int id) {
        boolean f = false;
        try {
            Connection con = getconn();
            String strsql = "update TelBook set uname=?,utel=? where uid=?";
            PreparedStatement pre = con.prepareStatement(strsql);
            pre.setString(1, name);
            pre.setString(2, telephone);
            pre.setInt(3, id);
            int d = pre.executeUpdate();
            if (d > 0) {
                f = true;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return f;
    }
    /*刪除全部信息*/
    public static boolean DeleteAll(){
        boolean ok=false;
        try {
            Connection con=getconn();
            Statement sta=con.createStatement();
            int a=sta.executeUpdate("truncate table TelBook");
            if (a>0) {
                ok=true;
            }
        } catch (Exception fx) {
            System.out.println(""+fx);
        }
        return ok;
    }
}