dbasemanager.java

利用Java实现的神经网络工具箱

/*
 * DbaseManager.java	1.0  29/10/2004
 *
 * NeuralNetworkToolkit
 * Copyright (C) 2004 Universidade de Brasília
 *
 * This file is part of NeuralNetworkToolkit.
 *
 * NeuralNetworkToolkit is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * NeuralNetworkToolkit is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with NeuralNetworkToolkit; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA - 02111-1307 - USA.
 */

package neuralnetworktoolkit.datamanager.databasemanager;

import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Locale;
import java.util.Properties;
import java.util.ResourceBundle;

import neuralnetworktoolkit.NeuralModel;
import neuralnetworktoolkit.modelstorage.ModelSerializer;
import neuralnetworktoolkit.modelstorage.ModelSerializerException;

/**
 * 
 * @author <a href="mailto:[e-mail]">Hugo Iver V. Gonçalves</a>
 * @author <a href="mailto:[e-mail]">Rodrigo C. M. Coimbra</a>
 * 
 */
public class DbaseManager {
	
	private ResourceBundle messages;
	private ResourceBundle bdInfo;
	
	/**
	 * The JDBC driver.
	 */
	private String driverName;
	
	/**
	 * The database connection.
	 */
	private static Connection connection;

	private String queriesFile;
			
	public DbaseManager(String file) {
		this.bdInfo = ResourceBundle.getBundle("neuralnetworktoolkit.datamanager.databasemanager.Db", new Locale(file));
		System.out.println("Teste: " + bdInfo.getString("driver"));
		this.driverName = bdInfo.getString("driver");
		this.messages = ResourceBundle.getBundle("neuralnetworktoolkit.datamanager.databasemanager.resources.Messages");
	}
	
	/**
	 *  @param url Database url.
	 */
	public void connectDatabase(String url) {
		
		try {
			
			Class.forName (driverName).newInstance ();
			connection = DriverManager.getConnection (url);
			System.out.println ("Database connection established"); 
			
        } catch (InstantiationException e1) {
			System.out.println("InstantiationException - Não achei o driver");
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			System.out.println("IllegalAccessException - Não achei o driver");
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			System.out.println("ClassNotFoundException - Não achei o driver");
			e1.printStackTrace();
		} catch (SQLException e2) {
			System.out.println("SQLException - Erro no banco");
			e2.printStackTrace();
		}
		
	} // connectDatabase()
	
	/**
	 * @param url Database url.
	 * 
	 * @param userName User name.
	 * 
	 * @param password User's password.
	 */
	public void connectDatabase(String url, String userName, String password) {
		
		try {
			
			Class.forName (driverName).newInstance ();
			connection = DriverManager.getConnection (url, userName, password);
			        
        } catch (InstantiationException e1) {
			System.out.println("InstantiationException - Não achei o driver");
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			System.out.println("IllegalAccessException - Não achei o driver");
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			System.out.println("ClassNotFoundException - Não achei o driver");
			e1.printStackTrace();
		} catch (SQLException e2) {
			System.out.println("SQLException - Erro no banco");
			e2.printStackTrace();
		}
		
	} // connectDatabase()
	
	/**
	 * Disconnects the database.
	 */
	public void disconnectDatabase() {
		
		if (connection != null) {
			
			try {
				connection.close ();
				System.out.println("Database connection closed!");
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
					
		}
		
	} // disconnectDatabase()
	
	/**
	 * @return Returns the existent tables' names in a String array.
	 */
	public String[] getTableNames() {
		String[] result = null;
		
		try {
			
			DatabaseMetaData data = connection.getMetaData();
			String[] tableTypes = {"TABLE"};
			ResultSet rs = data.getTables(null, null, null, tableTypes);
			rs.last();
			if (rs.getRow() > 0) {
				result = new String[rs.getRow()];
				rs.beforeFirst();
				int index = 0;
				while ( rs.next() ) {
					result[index] = rs.getString("TABLE_NAME");
					index++;
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
				
		return result;
	} // getTableNames()
	
	/**
	 * @param tableName The table which column names one wants to know.
	 * 
	 * @return The names of the existent columns in a String array.
	 */
	public String[] getTableColumnNames(String tableName) {
		String[] result = null;
		
		try {
			DatabaseMetaData data = connection.getMetaData();
			ResultSet rs = data.getColumns(null, null, tableName, null);
			rs.last();
			if (rs.getRow() > 0) {
				result = new String[rs.getRow()];
				rs.beforeFirst();
				int index = 0;
				while ( rs.next() ) {
					result[index] = rs.getString("COLUMN_NAME");
					index++;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	} // getTableColumnNames()
	
	/**
	 * @param tableName The name of the table from which one wants to load the data.
	 * 
	 * @param columnNames The names of table columns form which one wants to load the data.
	 * 
	 * @return The corresponding data.
	 */
	public double[][] loadData(String tableName, String[] columnNames) {
		double[][] result = null;
		
		if (connection != null) {
			
			try {
				Statement stm = connection.createStatement();
				String columns = columnNames[0];
				for (int i = 1; i < columnNames.length; i++) {
					columns = columns +","+columnNames[i];
				}
				String query = "SELECT "+columns +" FROM " + tableName;
				//System.out.println(query);
                ResultSet resultSet = stm.executeQuery(query);
				resultSet.last();
				//System.out.println("rs size: " + resultSet.getRow());
				if (resultSet.getRow() > 0) {
					result = new double[resultSet.getRow()][columnNames.length];
					resultSet.beforeFirst();
					int i = 0;
					while ( resultSet.next() ) {
						
						for (int j = 0; j < columnNames.length; j++) {
							result[i][j] = resultSet.getDouble(columnNames[j]);
						}
						i++;
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		
		
		return result;
	} // loadData()
	
	/**
	 * @param tableName The table where one wants to save the neural model.
	 * 
	 * @param modelName The name of the model.
	 * 
	 * @param nm The neural model one wants to save.
	 */
	public void saveNeuralModel(String tableName, String modelName, NeuralModel nm) {
		
		if (connection != null) {
			
			try {
				
				boolean tableExists = false;
				
				String[] tableNames = getTableNames();
				for (int i = 0; i < tableNames.length; i++) {
					if ( tableName.compareTo(tableNames[i]) == 0 ) {
						tableExists = true;
					}
				}
				
				if (!tableExists) {
					System.out.println("Tabela não existe!");
					String creationQuery = "CREATE TABLE " + tableName + " ("+"name "+bdInfo.getString("string")+" not null, network " +bdInfo.getString("blob")+ " not null, error "+bdInfo.getString("double")+" not null, architecture "+bdInfo.getString("string")+" not null, date "+bdInfo.getString("date")+" not null, time "+bdInfo.getString("time")+" not null"+", primary key(name, date, time))";
					System.out.println("Query: "+ creationQuery);
					Statement stmt = connection.createStatement();
					stmt.executeUpdate(creationQuery);
					
				} 
				
				System.out.println("Inserindo-----------------------!");
				byte[] binaryArray;
				try {
					binaryArray = ModelSerializer.serializeModelToByteArray(nm);
					ByteArrayInputStream binaryData = new ByteArrayInputStream(binaryArray);
					String insertionQuery = "INSERT INTO " +tableName+ "(name,network,error,architecture,date,time) VALUES(?,?,?,?,?,?)";
					PreparedStatement ps = connection.prepareStatement(insertionQuery);
					ps.setString(1, modelName);
					ps.setBytes(2, binaryArray);
					ps.setDouble(3, nm.getStatisticalResults().getError());
					ps.setString(4, nm.getNetworkArchitecture()+"MLP");
					ps.setDate(5, new Date(nm.getDate().getTime()));
					ps.setTime(6, new Time(nm.getDate().getTime()));
					ps.executeUpdate();
					System.out.println(insertionQuery);
				
				} catch (ModelSerializerException e) {
					e.printStackTrace();
				}
							
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		
	} // saveNeuralModel()
	
	/**
	 * @param tableName The name of table one wants to explore.
	 * 
	 * @param name The neural model name.
	 * 
	 * @param upperError The upper limit of the neural model training error.
	 * 
	 * @param architecture The respective neural network architecture.
	 * 
	 * @return A String[][] tha contains in each row the information of the
	 * 		of the satisfying neural models. 
	 */
	public String[][] getExistitentModels(String tableName, String name, double upperError, String architecture) {
		String[][] result = null;
		
		if (connection != null) {
			
			boolean tableExists = false;
			String id, arch, error;
			String[] tableNames = getTableNames();
			for (int i = 0; i < tableNames.length; i++) {
				if ( tableName.compareTo(tableNames[i]) == 0 ) {
					tableExists = true;
				}
			}
			
			if (tableExists) {
							
				try {
					if ( name == null ) {
						id = "";
					} else {
						id = "name = ? ";
					}
					if ( (upperError==0) ) {
						error = "";
						upperError = Double.MAX_VALUE;
					} else {
						error = "and error < ?";
					}
					if ( architecture == null ) {
						arch = "";
					} else {
						arch = " and architecture=?";
					}
					System.out.println("Tabela existe!");
					String selectQuery = "SELECT name,error,architecture,date,time FROM " + tableName + " WHERE "+id+error+arch;
					//System.out.println("Query: "+ selectQuery);
					PreparedStatement ps = connection.prepareStatement(selectQuery);
					int parameter;
					if ( id.compareTo("")!=0 ) {
						parameter = 1;
						ps.setString(parameter, name);
					}
					if ( arch.compareTo("")!=0 ) {
						if ( id.compareTo("")==0 ) {
							parameter = 1;
						} else {
							parameter = 2;
						}
						ps.setString(parameter, architecture);
					}
					if ( error.compareTo("")!=0 ) {
						parameter = 3;
						if ( (id.compareTo("")==0)&&(arch.compareTo("")==0) ) {
							parameter = 1;
						} else if ( (id.compareTo("")==0)||(arch.compareTo("")==0) ) {
							parameter =2;
						}
						ps.setDouble(parameter, upperError);
					}
					ResultSet rs = ps.executeQuery();
					
					rs.last();
					if (rs.getRow() > 0) {
						result = new String[rs.getRow()][5];
						rs.beforeFirst();
						int index = 0;
						while ( rs.next() ) {
							result[index][0] = rs.getString("name");
							Double instantError = new Double(rs.getDouble("error"));
							result[index][1] = instantError.toString();
							result[index][2] = rs.getString("architecture");
							result[index][3] = rs.getDate("date").toString();
							result[index][4] = rs.getTime("time").toString();
							index++;
						}
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
			}
			
		}
		
		return result;
	} // getExistitentModels()
	
	/**
	 * @param tableName The table where the model is stored.
	 * 
	 * @param modelKey The primary key, each field contains a string containing:
	 * 		Model name;<br>
	 * 		Date in SQL format;<br>
	 * 		Time in SQL format.
	 * 
	 * @return The specified neural model.
	 */
	public NeuralModel retrieveModel(String tableName, String[] modelKey) {
		NeuralModel result = null;
		
		if (connection!=null) {
			
			boolean tableExists = false;
			String id, arch, error;
			String[] tableNames = getTableNames();
			for (int i = 0; i < tableNames.length; i++) {
				if ( tableName.compareTo(tableNames[i]) == 0 ) {
					tableExists = true;
				}
			}
			
			if (tableExists) {
			
				try {
					System.out.println("Tabela existe!");
					String selectQuery = "SELECT network FROM " + tableName + " WHERE name=? and date=? and time=?";
					//System.out.println("Query: "+ selectQuery);
					PreparedStatement ps = connection.prepareStatement(selectQuery);
					ps.setString(1, modelKey[0]);
					ps.setDate(2, Date.valueOf(modelKey[1]));
					ps.setTime(3, Time.valueOf(modelKey[2]));
					ResultSet rs = ps.executeQuery();
					if (rs.next()) {
						byte[] binaryArray= rs.getBytes("network");
						try {
							result = ModelSerializer.retrieveSerializedModel(binaryArray);
						} catch (ModelSerializerException e) {
							e.printStackTrace();
						}
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
			}
			
		}
		
		return result;
	} // retrieveModel()
	
	/**
	 * @return Returns the driverName.
	 */
	public String getDriverName() {
		return driverName;
	}
	
	/**
	 * @param driverName The driverName to set.
	 */
	public void setDriverName(String driverName) {
		this.driverName = driverName;
	}
			
	/**
	 * @param queriesFile The queriesFile to set.
	 */
	public void setQueriesFile(String queriesFile) {
		this.queriesFile = queriesFile;
	}
}