mysqldataaccess.java

在ECLIPSE开发环境下使用JAVA编写的通讯录

package com.globalmethod.addressbook;

import java.sql.*;

public class MySQLDataAccess
 implements AddressBookDataAccess
{

 private Connection connection;
 private PreparedStatement sqlFind;
 private PreparedStatement sqlPersonID;
 private PreparedStatement sqlInsertName;
 private PreparedStatement sqlInsertAddress;
 private PreparedStatement sqlInsertPhone;
 private PreparedStatement sqlInsertEmail;
 private PreparedStatement sqlUpdateName;
 private PreparedStatement sqlUpdateAddress;
 private PreparedStatement sqlUpdatePhone;
 private PreparedStatement sqlUpdateEmail;
 private PreparedStatement sqlDeleteName;
 private PreparedStatement sqlDeleteAddress;
 private PreparedStatement sqlDeletePhone;
 private PreparedStatement sqlDeleteEmail;

 public MySQLDataAccess()
     throws Exception
 {   
 	 // 连接
     connect();
     // 查询
     sqlFind = connection.prepareStatement("SELECT name.personID,name,addressID,address,birthday,phoneID, phone,mobile,qq,emailID, email,other FROM name, address, phone, email  WHERE name =? AND name.personID = address.personID AND name.personID = phone.personID AND name.personID = email.personID");
     sqlPersonID = connection.prepareStatement("SELECT max(personID) AS personID FROM name");
     // 插入     
     sqlInsertName = connection.prepareStatement("INSERT INTO name (name) VALUES ( ? )");
     sqlInsertAddress = connection.prepareStatement("INSERT INTO address ( personID, address,birthday ) VALUES ( ? ,?, ? )");
     sqlInsertPhone = connection.prepareStatement("INSERT INTO phone( personID, phone,mobile,qq,other) VALUES ( ? , ? , ? , ? , ? )");
     sqlInsertEmail = connection.prepareStatement("INSERT INTO email ( personID, email ) VALUES ( ? , ? )");
     // 更新
     sqlUpdateName = connection.prepareStatement("UPDATE name SET name = ? WHERE personID = ?");
     sqlUpdateAddress = connection.prepareStatement("UPDATE address SET address = ?,birthday=? WHERE addressID = ?");
     sqlUpdatePhone = connection.prepareStatement("UPDATE phone SET phone = ?,mobile=?,qq=? ,other=? WHERE phoneID = ?");
     sqlUpdateEmail = connection.prepareStatement("UPDATE email SET email = ? WHERE emailID = ?");
     // 删除
     sqlDeleteName = connection.prepareStatement("DELETE FROM name WHERE personID = ?");
     sqlDeleteAddress = connection.prepareStatement("DELETE FROM address WHERE personID = ?");
     sqlDeletePhone = connection.prepareStatement("DELETE FROM phone WHERE personID = ?");
     sqlDeleteEmail = connection.prepareStatement("DELETE FROM email WHERE personID = ?");
 }

 // 连接
 private void connect()
     throws Exception
 { 
     String driver = "org.gjt.mm.mysql.Driver";
     String url = "jdbc:mysql://localhost/addressbook?user=root&password=12345&useUnicode=true&characterEncoding=GB2312";
     Class.forName(driver).newInstance();  //加载数据库驱动
     connection = DriverManager.getConnection(url);  //连接数据库
     connection.setAutoCommit(false);
 }

 // 查找操作
 public AddressBookEntry findPerson(String Name)
 {
     try
     {
         sqlFind.setString(1, Name);
         ResultSet resultSet = sqlFind.executeQuery();
         if(!resultSet.next())
         {
             return null;
         } else
         {
             AddressBookEntry person = new AddressBookEntry(resultSet.getInt(1));
             person.setName(resultSet.getString(2));
             person.setAddressID(resultSet.getInt(3));
             person.setAddress(resultSet.getString(4));
             person.setBirth(resultSet.getString(5));
             person.setPhoneID(resultSet.getInt(6));
             person.setPhone(resultSet.getString(7));
             person.setMobile(resultSet.getString(8));
             person.setQq(resultSet.getString(9));
             person.setEmailID(resultSet.getInt(10));
             person.setEmail(resultSet.getString(11));
             person.setOther(resultSet.getString(12));
            
           return person;
         }
     }
     catch(SQLException sqlException)
     {
         return null;
     }
 }

 // 更新数据
 public boolean savePerson(AddressBookEntry person)
     throws DataAccessException
 {
     try
     {
     	 // 从name表中执行更新
         sqlUpdateName.setString(1, person.getName());
         sqlUpdateName.setInt(2, person.getPersonID());
         int result = sqlUpdateName.executeUpdate();
         if(result == 0)
         {
             connection.rollback();
             return false;
         }
         
         // 从address表中执行更新
         sqlUpdateAddress.setString(1, person.getAddress());
         sqlUpdateAddress.setString(2, person.getBirth());
         sqlUpdateAddress.setInt(3, person.getAddressID());
         result = sqlUpdateAddress.executeUpdate();
         if(result == 0)
         {
             connection.rollback();
             return false;
         }
         
         // 从phone表中执行更新
         sqlUpdatePhone.setString(1, person.getPhone());
         sqlUpdatePhone.setString(2, person.getMobile());
         sqlUpdatePhone.setString(3, person.getQq());
         sqlUpdatePhone.setString(4, person.getOther());
         sqlUpdatePhone.setInt(5, person.getPhoneID());
        result = sqlUpdatePhone.executeUpdate();
         if(result == 0)
         {
             connection.rollback();
             return false;
         }
         
         // 从email表中执行更新
         sqlUpdateEmail.setString(1, person.getEmail());
         sqlUpdateEmail.setInt(2, person.getEmailID());
         result = sqlUpdateEmail.executeUpdate();
         if(result == 0)
         {
             connection.rollback();
             return false;
         } else
         {
             connection.commit();
             return true;
         }
     }
     
     catch(SQLException sqlException) { }
     try
     {
         connection.rollback();
         return false;
     }
     catch(SQLException exception)
     {
         throw new DataAccessException(exception);
     }
 }

 // 插入联系人信息
 public boolean newPerson(AddressBookEntry person)
     throws DataAccessException
 {
     try
     {
         sqlInsertName.setString(1, person.getName());
         int result = sqlInsertName.executeUpdate();
         if(result == 0)
         {
             connection.rollback();
             return false;
         }
         ResultSet resultPersonID = sqlPersonID.executeQuery();
         if(resultPersonID.next())
         {
             int personID = resultPersonID.getInt(1);
             sqlInsertAddress.setInt(1, personID);
             sqlInsertAddress.setString(2, person.getAddress());
             sqlInsertAddress.setString(3,person.getBirth());
             result = sqlInsertAddress.executeUpdate();
             if(result == 0)
             {
                 connection.rollback();
                 return false;
             }
             sqlInsertPhone.setInt(1, personID);
             sqlInsertPhone.setString(2, person.getPhone());
             sqlInsertPhone.setString(3, person.getMobile());
             sqlInsertPhone.setString(4, person.getQq());
             sqlInsertPhone.setString(5, person.getOther());
             result = sqlInsertPhone.executeUpdate();
             if(result == 0)
             {
                 connection.rollback();
                 return false;
             }
             sqlInsertEmail.setInt(1, personID);
             sqlInsertEmail.setString(2, person.getEmail());
            
             result = sqlInsertEmail.executeUpdate();
             if(result == 0)
             {
                 connection.rollback();
                 return false;
             } else
             {
                 connection.commit();
                 return true;
             }
         } else
         {
             return false;
         }
     }
     catch(SQLException sqlException)
     {
         try
         {
             sqlException.printStackTrace();
             connection.rollback();
             return false;
         }
         catch(SQLException exception)
         {
             throw new DataAccessException(exception);
         }
     }
 }

 // 删除操作
 public boolean deletePerson(AddressBookEntry person)
     throws DataAccessException
 {
     try
     {
         sqlDeleteAddress.setInt(1, person.getPersonID());
         int result = sqlDeleteAddress.executeUpdate();
         if(result == 0)
         {
             connection.rollback();
             return false;
         }
         sqlDeletePhone.setInt(1, person.getPersonID());
         result = sqlDeletePhone.executeUpdate();
         if(result == 0)
         {
             connection.rollback();
             return false;
         }
         sqlDeleteEmail.setInt(1, person.getPersonID());
         result = sqlDeleteEmail.executeUpdate();
         if(result == 0)
         {
             connection.rollback();
             return false;
         }
         sqlDeleteName.setInt(1, person.getPersonID());
         result = sqlDeleteName.executeUpdate();
         if(result == 0)
         {
             connection.rollback();
             return false;
         } else
         {
             connection.commit();
             return true;
         }
     }
     catch(SQLException sqlException) { }
     try
     {
         connection.rollback();
         return false;
     }
     catch(SQLException exception)
     {
         throw new DataAccessException(exception);
     }
 }

 // 关闭与数据库的连接
 public void close()
 {
     try
     {
         sqlFind.close();
         sqlPersonID.close();
         sqlInsertName.close();
         sqlInsertAddress.close();
         sqlInsertPhone.close();
         sqlInsertEmail.close();
         sqlUpdateName.close();
         sqlUpdateAddress.close();
         sqlUpdatePhone.close();
         sqlUpdateEmail.close();
         sqlDeleteName.close();
         sqlDeleteAddress.close();
         sqlDeletePhone.close();
         sqlDeleteEmail.close();
         connection.close();
     }
     catch(SQLException sqlException)
     {
         sqlException.printStackTrace();
     }
 }

 protected void finalize()
 {
     close();
 }
}