/*
 * JDBCOperation.java
 *
 * Created on 2006年8月12日, 上午10:41
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package database.command;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *主要是一些常用的數據庫操作，利用的jtds驅動程序
 * @author 幽夢還鄉
 */
public class JDBCOperation {
    
    /** Creates a new instance of JDBCOperation */
    public JDBCOperation() {
        
    }
    /*
     *獲得數據庫的連接
     *dirverName:數據庫驅動程序的名稱
     *JDBCURL：服務器路徑
     *username：數據庫的用戶名
     *password：密碼
     */
    public static Connection connection(String driverName,String JDBCURL,
            String userName,String password) 
            throws ClassNotFoundException, SQLException{
        Connection con = null;
        Class.forName(driverName);              
        con = java.sql.DriverManager.getConnection(JDBCURL,                        
                userName,password);  
       return con;
    }
    /*
     *添加一個表,用于字符串類
     *SQL：一個sql語句，創建表格以及添加一些比那兩
     *con:與數據庫連接的變量
     */
    public static void addTable(String SQL, Connection con) throws SQLException{
          
        Statement stmt = con.createStatement();     
        stmt.execute(SQL);
        stmt.close();
    }
  
    /*
     *刪除一個表格
     *tabelName:要刪除的表格的名稱
     */
    public static void deleteTable(String tableName,Connection con) throws SQLException{             
        Statement stmt = con.createStatement();
        stmt.execute("drop table " + tableName);
        stmt.close();
    }
    /*
     *向數據庫中插入數據
     */
    public static String insertValue(String tableName,String username,String password,String trueName,
            String country,String fav,String tel,Connection con) throws SQLException{
        String sql="insert into "+tableName+" values("+ "\'"+username+"\'"+",\'"+password
                +"\'"+",\'"+trueName+"\'"+",\'"+country+"\'"+",\'"+fav+"\'"+",\'"+tel+"\'"+")";
        Statement stmt=con.createStatement();
        stmt.execute(sql);
        return "success";
        
    }
/*
 *查找用戶的ID
 */
   public static boolean search_Mem(String ID,String password ,Connection con) throws SQLException{
       boolean isRegister = false;
       String sql = "select username,password from member where username =  " + "\'"+ ID +"\'"
               +"and password="+"\'"+password+"\'";
       //String sqltest = "select * from Userinformation";
       Statement stmt = con.createStatement();
       ResultSet rst = stmt.executeQuery(sql);
      if(rst.next()){        
           isRegister = true;
       }else{                       
           isRegister = false;                   
       }
       return isRegister;
   }
   public static boolean search_Mem(String ID,Connection con) throws SQLException{
         boolean isRegister = false;
       String sql = "select username,password from member where username =  " + "\'"+ ID +"\'";
       //String sqltest = "select * from Userinformation";
       Statement stmt = con.createStatement();
       ResultSet rst = stmt.executeQuery(sql);      
       if(rst.next()){        
           isRegister = true;
       }else{                       
           isRegister = false;                   
       }
       return isRegister;
   }
   public static boolean search_Admin(String ID,String password,Connection con) throws SQLException{
       boolean isAdmin = false;
       String sql = "select username,password from admin where username ="+"\'"+ID +"\'"
               +"and password="+"\'"+password+"\'";
       Statement stmt = con.createStatement();
       ResultSet rst = stmt.executeQuery(sql);       
       if(rst.next()){
           isAdmin = true;
           //System.out.println(rst.getString("password"));
       }else{
           isAdmin = false;
       }
       return isAdmin;
   }
   /*
    *用來返回數據據庫中注冊用戶的密碼
    *并且編成字符數組來返回
    */
   public static String get_Mem_Password(String username,String tableName,
          Connection con) throws SQLException{          
       String result=null;
       String sql = "select password from "+tableName+" where username =" +"\'"+username 
               +"\'";
       Statement stmt=con.createStatement();
       ResultSet rst=stmt.executeQuery(sql);
       if(rst.next()){            
           result=rst.getString("password");
       }
       return result;
   }
   public static void main(String args[]){
        Connection con;
        try {
            con = JDBCOperation.connection("net.sourceforge.jtds.jdbc.Driver", 
                    "jdbc:jtds:sqlserver://127.0.0.1:1433/gamvantalk",
                    "sa", "123");
            System.out.println(JDBCOperation.insertValue("member","ccc","456","eee","中國","123","789",con));
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
      
   }
}