dao.java

基于Java技术实现的minipacs系统,可以进行诊断信息登记, 嵌入控件查看DICOM 影像和统计分析等功能.

/*
 * DAO.java
 *
 * Created on 21. oktober 2005, 01:21
 *
 * To change this template, choose Tools | Options and locate the template under
 * the Source Creation and Management node. Right-click the template and choose
 * Open. You can then make changes to the template in the Source Editor.
 */

package backup.server; 

import backup.model.*;
import java.util.*;
import java.sql.Connection;
import java.sql.Timestamp;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.DriverManager;

/**
 *
 * @author Thomas
 */
public class Dao {
    Connection connection = null;
    
    /**
     * CONSTRUCTOR - Creates a new instance of Dao 
     */
    public Dao(String dbServerName, String dbServerPort, String dbName, String dbUsername, String dbPassword) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        loadPostgreSqlDriver();
        connectToDb(dbServerName, dbServerPort, dbName, dbUsername, dbPassword);
    }
    
    /**
     * METHOD - Install driver to PACS-database
     */	
    private void loadPostgreSqlDriver() throws ClassNotFoundException, InstantiationException, IllegalAccessException {
        Class.forName("org.postgresql.Driver").newInstance();
    }

    /**
     * METHOD - Open connectien to PACS-database
     */	
    private void connectToDb(String dbServerName, String dbServerPort, String dbName, String dbUsername, String dbPassword) throws SQLException {
        String mySqlConnect = "jdbc:postgresql://"+dbServerName+":"+dbServerPort+"/"+dbName;
        connection = DriverManager.getConnection(mySqlConnect, dbUsername, dbPassword);
    }

    /**
     * METHOD - Close connection to PACS-database!
     */	
    public void closeConnection() {
        try { connection.close(); }
        catch(SQLException e) {	e.printStackTrace(); }
    }


    /**
     * METHOD - Uniform exception handler for all DB-queries
     */	
    public void excHandle(ResultSet rs, Statement stmt) {
        if (rs != null) {
            try { rs.close(); }
            catch (SQLException e) { 
                e.printStackTrace();
            }
            rs = null;
        }
        else if (stmt != null) {
            try { stmt.close(); }
            catch (SQLException e) {
                e.printStackTrace();
            }
            stmt = null;
        }
    }
    
    
    /**
     * METHOD - SELECT: Findes studies to be backed up
     *          Studies that holds images all older than 1 hour.
     *          Studies that has not been backed up yet.
     */	
    public Vector<Study> getStudiesToBackup() {
        String query = "SELECT temp1.stuinsuid" +
                        " FROM(" +
                           " SELECT stuinsuid" +
                             " FROM img_studylevel" +
                           " EXCEPT" +
                             " SELECT stuinsuid" +
                               " FROM img_imagelevel" +
                               " JOIN img_serieslevel" +
                               " ON img_imagelevel.serinsuid = img_serieslevel.serinsuid" +
                             " WHERE img_imagelevel.insertdatetime > (NOW() - INTERVAL '1 HOUR')" +
                        " ) AS temp1" +
                        " JOIN(" +
                           " SELECT DISTINCT ON (img_serieslevel.stuinsuid) stuinsuid" +
                             " FROM img_imagelevel" +
                             " JOIN img_serieslevel" +
                             " ON img_imagelevel.serinsuid = img_serieslevel.serinsuid" +
                           " EXCEPT" +
                             " SELECT study_unique_id" +
                               " FROM img_backup_register" +
                        " ) AS temp2" +
                      " ON temp1.stuinsuid = temp2.stuinsuid;";

        String studyUniqueId;
        Vector<Study> result = new Vector<Study>();

        ResultSet rs = null;
        Statement stmt = null;

        try {
            stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            while (rs.next()){
                studyUniqueId = rs.getString("stuinsuid");
                result.addElement(new Study(studyUniqueId));
            }
            result.trimToSize();
        }
        catch (SQLException e) { e.printStackTrace(); }

        finally { excHandle(rs, stmt); }
        
        for(int i=0; i<result.size(); i++) {
            this.getStudyId(result.elementAt(i));
            this.getStudyPath(result.elementAt(i));
            this.getStudySize(result.elementAt(i));
            this.getStudyTime(result.elementAt(i));
        }

        return result;
    }

    /**
     * METHOD - SELECT: Find ID of study
     */	    
    public void getStudyId(Study study) {
        String studyUniqueId = study.getStudyUniqueId();
        String query = "SELECT stuinsuid, stuid" +
                        " FROM img_studylevel" +
                      " WHERE stuinsuid = '" + studyUniqueId + "';";

        String studyId;

        ResultSet rs = null;
        Statement stmt = null;

        try {
            stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            while (rs.next()){
                studyId = rs.getString("stuid");
                study.setStudyId(studyId);
            }
        }
        catch (SQLException e) { e.printStackTrace(); }

        finally { excHandle(rs, stmt); }
    }
    
    
    /**
     * METHOD - SELECT: Find size of study
     */	
    public void getStudySize(Study study) {
        String studyUniqueId = study.getStudyUniqueId();
        String query = "SELECT stuinsuid, SUM(size_bytes) AS study_size" +
                         " FROM (SELECT stuinsuid, img_imagelevel.size_bytes" +
                                 " FROM img_imagelevel" +
                                 " JOIN img_serieslevel" +
                                 " ON img_imagelevel.serinsuid = img_serieslevel.serinsuid" +
                               " WHERE stuinsuid = '" + studyUniqueId + "')" +
                               " AS serie_image" +
                         " GROUP BY stuinsuid;";

        int studySize;

        ResultSet rs = null;
        Statement stmt = null;

        try {
            stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            while (rs.next()){
                studySize = rs.getInt("study_size");
                study.setStudySize(studySize);
            }
        }
        catch (SQLException e) { e.printStackTrace(); }

        finally { excHandle(rs, stmt); }
    }
    
    
    /**
     * METHOD - SELECT: Findes path of study
     */	    
    public void getStudyPath(Study study) {
        String studyUniqueId = study.getStudyUniqueId();
        String query = "SELECT stuinsuid, path_estudo" +
                        " FROM img_studylevel" +
                      " WHERE stuinsuid = '" + studyUniqueId + "';";

        String studyPath;

        ResultSet rs = null;
        Statement stmt = null;

        try {
            stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            while (rs.next()){
                studyPath = rs.getString("path_estudo");
                study.setStudyPath(studyPath);
            }
        }
        catch (SQLException e) { e.printStackTrace(); }

        finally { excHandle(rs, stmt); }
    }
    
    
    /**
     * METHOD - SELECT: Findes timestamp of study
     */	    
    public void getStudyTime(Study study) {
        String studyUniqueId = study.getStudyUniqueId();
        String query = "SELECT stuinsuid, insertdatetime" +
                        " FROM img_studylevel" +
                      " WHERE stuinsuid = '" + studyUniqueId + "';";

        long studyTime;
        Timestamp timestamp;

        ResultSet rs = null;
        Statement stmt = null;

        try {
            stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            while (rs.next()){
                timestamp = rs.getTimestamp("insertdatetime");