JDBC---数据库连接增删改查

 

到这里，我意识到这个系统MVC中的C放置在各个V中了，所以C就不介绍了，哈哈哈

接着总结DAO层，这个系统我采用的应该算是标准的DAO设计模式

还没有完全写完的dao包

DAO的结构图

 

DAO数据访问层的开发：

1.设计基类DAOBase类

提供标准的获得和关闭数据库连接的方法

package com.yinger.patientims.dao; 

import java.sql.Connection;
import java.sql.DriverManager;

/**
 * 数据库操作的基础类，它是其他的DAO类的父类
 * 定义了建立数据库连接和关闭数据库连接的方法
 */
public class DAOBase {
   private Connection connection;

   // 得到数据库连接
   public Connection getConnection() {
     try {
      Class.forName( "com.mysql.jdbc.Driver");
      connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/patientims", "root", "root");
    } catch (Exception e) {
      e.printStackTrace();
    }
     return connection;
  }

   // 关闭数据库连接
   public void closeConnnection() {
     try {
      connection.close();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
       if (connection != null) {
        connection = null;
      }
    }
  }

}

 

2.编写各个Model层对象对应的DAO类

例如 PatientDAO

注意一个方法 setOnePatient 方法，这个方法很有用的，其作用是将数据库中取出的一条记录放置到一个Patient对象上，所以该方法

最好是能够独立出来，以便重复使用！

package com.yinger.patientims.dao; 

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.yinger.patientims.model.Patient;
import com.yinger.patientims.util.DBUtil;

public class PatientDAO extends DAOBase {

   // 得到所有住院的病人的信息
   // SELECT
   // p.id,p.name,p.age,p.sex,p.address,p.logtime,p.phone,d.name,b.sickbedno,r.sickroomno
   // FROM t_patient p,t_department d,t_sickbed b,t_sickroom r
   // WHERE p.sickbed_id=b.id and b.sickroom_id=r.id and r.department_id=d.id
   public List <Patient > getPatientInfoList() {
    Patient patient;
    List <Patient > list = new ArrayList <Patient >();
     // 注意：这里有两个name，要使用到别名
    String sql = "SELECT p.id,p.name as pname,p.age,p.sex,p.address,p.logtime,p.phone,d.name as dname,b.sickbedno,r.sickroomno "
         + "FROM t_patient p,t_department d,t_sickbed b,t_sickroom r "
         + "WHERE p.sickbed_id=b.id and b.sickroom_id=r.id and r.department_id=d.id";

 

     String sql="SELECT * FROM  t_table"   //查询整个表
    Connection connection = null;
     // 在finally中使用它是它必须要初始化
     // 还有，它一定要放在外面，这样在try外面（finally）才可以被访问到
     // 还有，一定要有finally！关闭数据库连接是很重要的！
     try {
      connection = getConnection();
      PreparedStatement pStatement = connection.prepareStatement(sql);
      ResultSet resultSet = pStatement.executeQuery();
       while (resultSet.next()) {
        patient = setOnePatient(resultSet);
        list.add(patient);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
       try {
        connection.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
     return list;
  }

   // 将数据库中的一条patient记录关联到一个Patient对象中
   // 并且它不处理异常，抛出由上层处理
   private Patient setOnePatient(ResultSet resultSet) throws Exception {
    Patient patient = new Patient();
     if (resultSet.getLong( "id") != 0) {
      patient.setId(resultSet.getLong( "id"));
    }
     if (resultSet.getString( "pname") != null) {
      patient.setName(resultSet.getString( "pname"));
    }
     if (resultSet.getString( "sex") != null) {
      patient.setSex(resultSet.getString( "sex"));
    }
     if (resultSet.getString( "phone") != null) {
      patient.setPhone(resultSet.getString( "phone"));
    }
     if (resultSet.getString( "address") != null) {
      patient.setAddress(resultSet.getString( "address"));
    }
     if (resultSet.getInt( "age") != 0) {
      patient.setAge(resultSet.getInt( "age"));
    }
     if (resultSet.getDate( "logtime") != null) {
      patient.setLogtime(resultSet.getDate( "logtime"));
    }
     if (resultSet.getString( "dname") != null) {
       // 注意这一步！这里容易发生空指针异常！在一个Patient对象中Department等属性并没有被初始化！
       // 所以要在类中new出各个对象，对于SickRoom等等同理
      patient.getDepartment().setName(resultSet.getString( "dname"));
    }
     if (resultSet.getInt( "sickbedno") != 0) {
      patient.getSickbed().setSickBedNo(resultSet.getInt( "sickbedno"));
    }
     if (resultSet.getInt( "sickroomno") != 0) {
      patient.getSickroom().setSickRoomNo(resultSet.getInt( "sickroomno"));
    }
     return patient;
  }

   // 删除病人信息
   public boolean deletePatient(Patient patient) {
    Long id = patient.getId();
    Connection connection = null;
    String sql = "delete from t_patient where id=  "+ id + "    ";
     try {
      connection = getConnection();
      PreparedStatement pStatement = connection.prepareStatement(sql);
       int res = pStatement.executeUpdate();
       if (res > 0) {
         return true; // 注意：这里虽然是return了，但是这个方法真正返回之前还是要执行finally
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
       try {
        connection.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
     return false;
  }

   // 添加病人住院信息
   public boolean insertPatient(Patient patient) {
    Connection connection = null;
     // INSERT INTO
     // t_patient(name,sex,age,phone,logtime,address,sickbed_id) VALUES
     // ('patient"+i+"','女',37,'1533535354','2011-10-10','TianJin',"+id+")
    String sql = "INSERT INTO t_patient(name,sex,age,phone,logtime,address,sickbed_id) VALUES ('" + patient.getName() + "','" + patient.getSex() + "',"
         + patient.getAge() + ",'" + patient.getPhone() + "','" + DBUtil.simpleDateFormat.format(patient.getLogtime()) + "','" + patient.getAddress() + "',"
         + patient.getSickbed().getId() + ")";   // 字符串用'"+ patient.getName() + "'，整形用"+ patient.getAge() + "

             //patient.getLogtime().toLocaleString() 已经不推荐使用了
     //注意：时间是有点特别地是使用 DateFormater进行format一下！
     try {
      connection = getConnection();
      PreparedStatement pStatement = connection.prepareStatement(sql);
       int res = pStatement.executeUpdate();
       if (res > 0) {
         return true; // 注意：这里虽然是return了，但是这个方法真正返回之前还是要执行finally
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
       try {
        connection.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
     return false;
  }

   // 修改病人住院信息
   public boolean updatePatient(Patient patient) {
    Connection connection = null;
     //UPDATE t_patient as p set p.name='yyh' , p.address='ghalsdhgl' , p.age=24 , p.phone='523659365', p.sex='female'
     // where id=6
    StringBuffer sql = new StringBuffer( "UPDATE t_patient as p ");
    sql.append( " set p.name='" +patient.getName() + "' ");
    sql.append( ", p.address='" +patient.getAddress() + "' ");
    sql.append( " , p.age=" +patient.getAge() + " ");
    sql.append( " , p.phone='" +patient.getPhone() + "' ");
    sql.append( ", p.sex='" +patient.getSex() + "' ");
    sql.append( " ,p.sickbed_id= " +patient.getSickbed().getId() + " ");
    sql.append( " where id=" +patient.getId());
     try {
      connection = getConnection();
      PreparedStatement pStatement = connection.prepareStatement(sql.toString());
       int res = pStatement.executeUpdate();
       if (res > 0) {
         return true; 
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
       try {
        connection.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
     return false;
  }

}

 

 

再比如：DepartmentDAO类

 

package com.yinger.patientims.dao; 

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.yinger.patientims.model.Department;

public class DepartmentDAO extends DAOBase{

//  private List departmentList = new ArrayList();
//  private Department department;

   // 得到所有的科室
   public List <Department > getDepartmentList(){
    Department department;
    List <Department > list = new ArrayList <Department >();
    Connection connection = null;
    String sql = "select id,name from t_department";
     try {
      connection = getConnection();
      PreparedStatement pStatement = connection.prepareStatement(sql);
      ResultSet resultSet = pStatement.executeQuery();
       while (resultSet.next()) {
        department = setOneDepartment(resultSet);
        list.add(department);
      }
      pStatement.close();
    } catch (Exception e) {
      e.printStackTrace();
    } finally{
       try {
        connection.close(); // Quick Fix : Ctrl + 1
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
     return list;
  }

   // 从数据库中取出一条记录并保存到一个Department中
   private Department setOneDepartment(ResultSet resultSet) throws Exception {
    Department department = new Department();
     if(resultSet.getLong( "id") != 0){
      department.setId(resultSet.getLong( "id"));
    }
     if(resultSet.getString( "name") !=null){
      department.setName(resultSet.getString( "name"));
    }
     return department;
  }

}

 

3.总结：

（1）注意DAO类中每个操作的语句结构，一定要捕捉异常进行处理，一定要有finally，并在其中关闭连接

（2）注意数据库中取出来的数据的类型和Model的该属性的类型

（3）注意方法的返回值，是否要返回值？

（4）注意sql语句的构造，如果多次使用字符串的合并建议使用高效率的StringBuilder，还有构造时也要注意是否需要''，这个最好是

先测试，然后编写代码！

 

 

参考： http://www.cnblogs.com/yinger/archive/2011/11/19/2255660.html