[零基础学JAVA]Java SE实战开发-37.MIS信息管理系统实战开发[JDBC](1)

package org.michael.demo.dao;    
import java.util.Set;    
import org.michael.demo.vo.Person;    
// 定义具体的数据的操作方法    
public  interface PersonDAO {    
         /**    
         * 插入数据的操作    
         *    
         * @param person    
         *                        插入的是一个人员信息    
         * @return 操作成功与否的提示    
         * @throws Exception    
         *                         如果有错误，则把错误抛给调用处处理    
         */    
         public  boolean doCreate(Person person)  throws Exception;    
         /**    
         * 更新数据操作    
         *    
         * @param person    
         *                        更新的具体信息    
         * @return 更新成功与否的提示    
         * @throws Exception    
         *                         如果有错误，则把错误抛出    
         */    
         public  boolean doUpdate(Person person)  throws Exception;    
         /**    
         * 按id删除信息    
         *    
         * @param id    
         *                        人员的编号    
         * @return 删除与否的提示    
         * @throws Exception    
         *                         如果有错误，则在调用处处理    
         */    
         public  boolean doDelete(String id)  throws Exception;    
         /**    
         * 因为查询是多个，所以要返回Set集合    
         *    
         * @return 全部的查询结果，一个Set中包含了多个Person对象    
         * @throws Exception    
         */    
         public Set<Person> findAll()  throws Exception;    
         /**    
         * 按id进行查询    
         *    
         * @param id    
         *                        人员的编号    
         * @return 具体的人员信息    
         * @throws Exception    
         */    
         public Person findById(String id)  throws Exception;    
         /**    
         * 按关键字进行查询    
         *    
         * @param keyWord    
         *                        输入的关键字    
         * @return 返回一组信息    
         * @throws Exception    
         */    
         public Set<Person> findByLike(String keyWord)  throws Exception;    
}

package org.michael.demo.dao.impl;    
import java.sql.PreparedStatement;    
import java.sql.ResultSet;    
import java.util.Set;    
import java.util.TreeSet;    
import org.michael.demo.dao.PersonDAO;    
import org.michael.demo.dbc.DataBaseConnection;    
import org.michael.demo.vo.Person;    
import org.michael.demo.vo.Student;    
import org.michael.demo.vo.Worker;    
public  class PersonDAOImplJDBC  implements PersonDAO {    
         private DataBaseConnection dbc =  null;    
         private String type =  null;    
         // 查询操作分为两点，要么是查询工人，要么是查询学生    
         public PersonDAOImplJDBC(String type) {    
                 this();    
                 this.type = type;    
        }    
         public PersonDAOImplJDBC() {    
        }    
         public  boolean doCreate(Person person)  throws Exception {    
                 this.dbc =  new DataBaseConnection();    
                 boolean flag =  false;    
                 try {    
                        PreparedStatement pstmt =  null;    
                         // 如果传入的是学生，则type为0，如果传入的是工人，则type为1    
                        String sql =  null;    
                         float temp = 0.0f;    
                         if (person  instanceof Student) {    
                                Student stu = (Student) person;    
                                temp = stu.getScore();    
                                sql =  "INSERT INTO tperson(id,name,age,score,type) VALUES (?,?,?,?,0)";    
                        }    
                         if (person  instanceof Worker) {    
                                Worker wor = (Worker) person;    
                                temp = wor.getSalary();    
                                sql =  "INSERT INTO tperson(id,name,age,salary,type) VALUES (?,?,?,?,1)";    
                        }    
                        pstmt =  this.dbc.getConnection().prepareStatement(sql);    
                        pstmt.setString(1, person.getId());    
                        pstmt.setString(2, person.getName());    
                        pstmt.setInt(3, person.getAge());    
                        pstmt.setFloat(4, temp);    
                        pstmt.executeUpdate();    
                        pstmt.close();    
                        flag =  true;    
                }  catch (Exception e) {    
                         throw e;    
                }  finally {    
                         // 最终无论是否发生异常，都必须关闭数据库    
                         this.dbc.close();    
                }    
                 return flag;    
        }    
         public  boolean doDelete(String id)  throws Exception {    
                 this.dbc =  new DataBaseConnection();    
                 boolean flag =  false;    
                 try {    
                        PreparedStatement pstmt =  null;    
                         // 如果传入的是学生，则type为0，如果传入的是工人，则type为1    
                        String sql =  null;    
                        sql =  "DELETE FROM tperson WHERE id=?";    
                        pstmt =  this.dbc.getConnection().prepareStatement(sql);    
                        pstmt.setString(1, id);    
                        pstmt.executeUpdate();    
                        pstmt.close();    
                        flag =  true;    
                }  catch (Exception e) {    
                         throw e;    
                }  finally {    
                         // 最终无论是否发生异常，都必须关闭数据库    
                         this.dbc.close();    
                }    
                 return flag;    
        }    
         public  boolean doUpdate(Person person)  throws Exception {    
                 this.dbc =  new DataBaseConnection();    
                 boolean flag =  false;    
                 try {    
                        PreparedStatement pstmt =  null;    
                         // 如果传入的是学生，则type为0，如果传入的是工人，则type为1    
                        String sql =  null;    
                         float temp = 0.0f;    
                         if (person  instanceof Student) {    
                                Student stu = (Student) person;    
                                temp = stu.getScore();    
                                sql =  "UPDATE tperson SET name=?,age=?,score=? WHERE id=?";    
                        }    
                         if (person  instanceof Worker) {    
                                Worker wor = (Worker) person;    
                                temp = wor.getSalary();    
                                sql =  "UPDATE tperson SET name=?,age=?,salary=? WHERE id=?";    
                        }    
                        pstmt =  this.dbc.getConnection().prepareStatement(sql);    
                        pstmt.setString(1, person.getName());    
                        pstmt.setInt(2, person.getAge());    
                        pstmt.setFloat(3, temp);    
                        pstmt.setString(4, person.getId());    
                        pstmt.executeUpdate();    
                        pstmt.close();    
                        flag =  true;    
                }  catch (Exception e) {    
                         throw e;    
                }  finally {    
                         // 最终无论是否发生异常，都必须关闭数据库    
                         this.dbc.close();    
                }    
                 return flag;    
        }    
         public Set<Person> findAll()  throws Exception {    
                 this.dbc =  new DataBaseConnection();    
                Set<Person> newSet =  new TreeSet<Person>();    
                String sql =  null;    
                 if ( "0".equals( this.type)) {    
                        sql =  "SELECT id,name,age,score FROM tperson WHERE type=?";    
                }    
                 if ( "1".equals( this.type)) {    
                        sql =  "SELECT id,name,age,salary FROM tperson WHERE type=?";    
                }    
                PreparedStatement pstmt =  null;    
                 try {    
                        pstmt =  this.dbc.getConnection().prepareStatement(sql);    
                        pstmt.setString(1,  this.type);    
                        ResultSet rs = pstmt.executeQuery();    
                         while (rs.next()) {    
                                 // 要进行判断，判断是实例化Student还是实例化Worker    
                                 if ( "0".equals( this.type)) {    
                                        newSet.add( new Student(rs.getString(1), rs.getString(2), rs    
                                                        .getInt(3), rs.getFloat(4)));    
                                }    
                                 if ( "1".equals( this.type)) {    
                                        newSet.add( new Worker(rs.getString(1), rs.getString(2), rs    
                                                        .getInt(3), rs.getFloat(4)));    
                                }    
                        }    
                        rs.close();    
                        pstmt.close();    
                }  catch (Exception e) {    
                         throw e;    
                }  finally {    
                         this.dbc.close();    
                }    
                 return newSet;    
        }    
         public Person findById(String id)  throws Exception {    
                 this.dbc =  new DataBaseConnection();    
                Person person =  null;    
                String sql =  null;    
                 if ( "0".equals( this.type)) {    
                        sql =  "SELECT id,name,age,score FROM tperson WHERE id=?";    
                }    
                 if ( "1".equals( this.type)) {    
                        sql =  "SELECT id,name,age,salary FROM tperson WHERE id=?";    
                }    
                PreparedStatement pstmt =  null;    
                 try {    
                        pstmt =  this.dbc.getConnection().prepareStatement(sql);    
                        pstmt.setString(1, id);    
                        ResultSet rs = pstmt.executeQuery();    
                         while (rs.next()) {    
                                 // 要进行判断，判断是实例化Student还是实例化Worker    
                                 if ( "0".equals( this.type)) {    
                                        person =  new Student(rs.getString(1), rs.getString(2), rs    
                                                        .getInt(3), rs.getFloat(4));    
                                }    
                                 if ( "1".equals( this.type)) {    
                                        person =  new Worker(rs.getString(1), rs.getString(2), rs    
                                                        .getInt(3), rs.getFloat(4));    
                                }    
                        }    
                        rs.close();    
                        pstmt.close();    
                }  catch (Exception e) {    
                         throw e;    
                }  finally {    
                         this.dbc.close();    
                }    
                 return person;    
        }    
         public Set<Person> findByLike(String keyWord)  throws Exception {    
                 this.dbc =  new DataBaseConnection();    
                Set<Person> newSet =  new TreeSet<Person>();    
                String sql =  null;    
                sql =  "SELECT id,name,age,score FROM tperson WHERE type=? AND name LIKE ?";    
                PreparedStatement pstmt =  null;    
                 try {    
                        pstmt =  this.dbc.getConnection().prepareStatement(sql);    
                        pstmt.setString(1,  this.type);    
                        pstmt.setString(2,  "%" + keyWord +  "%");    
                        ResultSet rs = pstmt.executeQuery();    
                         while (rs.next()) {    
                                 // 要进行判断，判断是实例化Student还是实例化Worker    
                                 if ( "0".equals( this.type)) {    
                                        newSet.add( new Student(rs.getString(1), rs.getString(2), rs    
                                                        .getInt(3), rs.getFloat(4)));    
                                }    
                                 if ( "1".equals( this.type)) {    
                                        newSet.add( new Worker(rs.getString(1), rs.getString(2), rs    
                                                        .getInt(3), rs.getFloat(4)));    
                                }    
                        }    
                        rs.close();    
                        pstmt.close();    
                }  catch (Exception e) {    
                         throw e;    
                }  finally {    
                         this.dbc.close();    
                }    
                 return newSet;    
        }    
}

package org.michael.demo.dbc;    
import java.sql.Connection;    
import java.sql.DriverManager;    
import java.sql.SQLException;    
//数据库的连接操作类    
public  class DataBaseConnection {    
         private  static  final String DBDRIVER =  "org.gjt.mm.mysql.Driver";    
         private  static  final String DBURL =  "jdbc:mysql://localhost:3306/51cto";    
        private static final String DBUSER = "root";    
        private static final String DBPASSWORD = "mysqladmin";    
        private Connection conn = null;    
        public DataBaseConnection() {    
                try {    
                        Class.forName(DBDRIVER);    
                } catch (ClassNotFoundException e) {    
                        // TODO Auto-generated catch block    
                        e.printStackTrace();    
                }    
                try {    
                        this.conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD);    
                } catch (SQLException e) {    
                        // TODO Auto-generated catch block    
                        e.printStackTrace();    
                }    
        }    
        public Connection getConnection() {    
                return this.conn;    
        }    
        public void close(){    
                try {    
                        this.conn.close() ;    
                } catch (SQLException e) {    
                        // TODO Auto-generated catch block    
                        e.printStackTrace();    
                }    
        }    
}

DROP  TABLE tperson ;    
CREATE  TABLE tperson    
(    
        id         VARCHAR(50)         PRIMARY  KEY  NOT  NULL ,    
         name         VARCHAR(50)         NOT  NULL ,    
        age         INT                 NOT  NULL ,    
        score         FLOAT ,    
        salary         FLOAT ,    
        type         INT     NOT  NULL  DEFAULT 0    
) ;    
-- 如果type的值为0，则表示学生，如果type的值为1则表示工人。

package org.michael.demo.factory;    
import org.michael.demo.dao.PersonDAO;    
import org.michael.demo.proxy.PersonDAOProxyJDBC;    
public  class DAOFactory {    
         public  static PersonDAO getPersonDAOInstance(String path) {    
                 return  new PersonDAOProxyJDBC(path);    
        }    
}

package org.michael.demo.io;    
import java.io.File;    
import java.io.FileInputStream;    
import java.io.FileOutputStream;    
import java.io.ObjectInputStream;    
import java.io.ObjectOutputStream;    
import java.util.TreeSet;    
import org.michael.demo.vo.Person;    
public  class FileOperate {    
         private File file =  null;    
         // 在够方法处必须指定保存文件的名字    
         public FileOperate(String path) {    
                 this.file =  new File(path);    
                 if (! this.file.exists()) {    
                         // 如果内容不存在，则认为是第一次操作，需要初始化    
                         try {    
                                 this.save( new TreeSet<Person>()) ;    
                        }  catch (Exception e) {    
                                e.printStackTrace();    
                        }    
                }    
        }    
         // 把对象保存在文件之中    
         public  void save(Object obj)  throws Exception {    
                ObjectOutputStream out =  null;    
                 try {    
                        out =  new ObjectOutputStream( new FileOutputStream( this.file));    
                         // 写入对象    
                        out.writeObject(obj);    
                }  catch (Exception e) {    
                         throw e;    
                }  finally {    
                         try {    
                                out.close();    
                        }  catch (Exception e) {    
                        }    
                }    
        }    
         // 把对象从文件之中读进来    
         public Object load()  throws Exception {    
                Object obj =  null;    
                ObjectInputStream input =  null;    
                 try {    
                        input =  new ObjectInputStream( new FileInputStream( this.file));    
                        obj = input.readObject();    
                }  catch (Exception e) {    
                         throw e;    
                }  finally {    
                         try {    
                                input.close();    
                        }  catch (Exception e) {    
                        }    
                }    
                 return obj;    
        }    
}

package org.michael.demo.main;    
import org.michael.demo.menu.Menu;    
public  class Main {    
         public  static  void main(String[] args) {    
                 new Menu();    
        }    
}