4
\$\begingroup\$

the following is my code for the Java class connection to mysql server, i need to see if there is any suggestion to make it better.

this class not making the connection, it just control transactions, selecting, updating & deleting.

package database;

import java.sql.*;
import java.util.*;
import core.*;

public class CruesDataAccessor {

   private PreparedStatement preparedStatement;
   private PreparedStatement updateStatement;
   private PreparedStatement insertStatement;
   private PreparedStatement deleteStatement;
   private ResultSet dataResultSet;
   private Connection connection;
   private Vector<String> columensDataTypes;
   private Vector<String> columensNames;
   private Vector<Object> queryDataVtr;
   private Vector<Object> filteredColNames;
   private int count = 0;
   private Vector<Integer> colsWidth;
   private String tablename;

   // the constructor
   public CruesDataAccessor(Connection conn) {
       connection = conn;
       try {
       } catch (Exception e) {
           myError(e, "1");
       }
   }

   // setting connection
   public void setConnection(Connection conn) {
       connection = conn;
       try {
       } catch (Exception e) {
           myError(e, "2");
       }
   }

   // used to get the data based on a mysql statement
   public void clearMe(){
       columensDataTypes.removeAllElements();
       columensNames.removeAllElements();
       queryDataVtr.removeAllElements();
       filteredColNames.removeAllElements();
       colsWidth.removeAllElements();
   }
   public Vector<?> getData(String sqlStmnt) {
       // remove illegal characters from the mysql statement
       sqlStmnt = Declare.replaceIllegalChars(sqlStmnt);
       //sqlStmnt = sqlStmnt.toLowerCase();

       // used to fill the names of the columns
       filteredColNames = new Vector<Object>();
       queryDataVtr = new Vector<Object>();
       columensNames = new Vector<String>();

       // used to fill column widths
       colsWidth = new Vector<Integer>();
       try {
           count = 0;
           // a prepare a MySQL statement
           preparedStatement = connection.prepareStatement(sqlStmnt);
           // execute the query and get the result
           dataResultSet = preparedStatement.executeQuery(sqlStmnt);
           // get the types of the data
           ResultSetMetaData resultSetMetaData = dataResultSet.getMetaData();
           count = resultSetMetaData.getColumnCount();
           columensDataTypes = new Vector<String>();
           // parse the data and fill the necessary variables
           for (int i = 0; i < count; i++) {
               columensNames.addElement(resultSetMetaData.getColumnName(i + 1));
               setTableName(resultSetMetaData.getTableName(1));
               setFilteredColumnNames(resultSetMetaData.getColumnName(i + 1));
               setColsWidth(resultSetMetaData.getColumnName(i + 1).length());
               String dataType = resultSetMetaData.getColumnTypeName(i + 1);

               if (dataType.indexOf("String") != -1) {
                   columensDataTypes.add("string");
               } else if (dataType.indexOf("Date") != -1 ||
                          dataType.indexOf("Timestamp") != -1) {
                   columensDataTypes.add("date");
               } else if ((dataType.indexOf("BigDecimal") != -1) ||
                          (dataType.indexOf("Integer") != -1) ||
                          (dataType.indexOf("Long") != -1)) {
                   columensDataTypes.add("number");
               } else {
                   columensDataTypes.add("string");
               }
           }

           // filling the information in a vector
           Vector<String> recordVtr;
           while (dataResultSet.next()) {
               recordVtr = new Vector<String>();
               for (int i = 0; i < count; i++) {
                   try {
                       recordVtr.addElement(dataResultSet.getString(i + 1));
                   } catch (SQLException ex1) {
                       recordVtr.addElement("");
                   }
               }
               queryDataVtr.add(recordVtr);
           }

           // closing
           try {
               dataResultSet.close();
               dataResultSet = null;
           } catch (Exception exx) {
               myError(exx, "3");
               dataResultSet = null;
           }
       } catch (Exception exx) {
           try {
               dataResultSet.close();
           } catch (Exception ex) {
           }
           myError(exx, "4");
           dataResultSet = null;
       }

       preparedStatement = null; //
       return queryDataVtr;
   }

   // get the column names in a vector
   public Vector<String> getColumnNames() {
       return columensNames;
   }

   // filter the column names
   public void setFilteredColumnNames(String str) {
       boolean found = false;
       for (int i = 0; i < str.length(); i++) {
           if (str.charAt(i) == '_') {
               filteredColNames.add(str.substring(i + 1, str.length()));
               found = true;
               break;
           }
       }
       if (!found) {
           filteredColNames.add(str);
       }
   }

   // get the types of the column fields
   public Vector<String> getDataTypes() {
       return this.columensDataTypes;
   }

   // get the filtered column names
   public Vector<?> getfilteredColumNames() {
       return filteredColNames;
   }

   // get the column counts
   public int getColumnCount() {
       return count;
   }

   // set default column widths
   public void setColsWidth(int width) {
       colsWidth.add(new Integer(100));
   }

   // get column widths
   public Vector<Integer> getColumnsWidth() {
       return colsWidth;
   }

   // used to update the database with an SQL statement
   public synchronized boolean UpdateDB(String sql) {
       sql = Declare.replaceIllegalChars(sql);
       //sql = sql.toLowerCase();
       int result = 0;
       try {
           updateStatement = connection.prepareStatement(sql);
           result = updateStatement.executeUpdate();
           updateStatement = null; //
       } catch (SQLException sexx) {
           myError(sexx, "5");
           Declare.exCounter += 1;
      //     sexx.printStackTrace();
           System.out.println("Erro: "+sexx);
       } catch (Exception exx) {
           System.out.println("Erro: "+exx);
          // myError(exx, "6");
       }
       if (result == 1) {
           return true;
       } else {
           return false;
       }
   }

   // used to insert in the database with an SQL statement
   public synchronized boolean InsertDB(String sql) {
       sql = Declare.replaceIllegalChars(sql);
       int result = 0;
       try {
           insertStatement = connection.prepareStatement(sql);
           result = insertStatement.executeUpdate();
           insertStatement = null; //
       } catch (SQLException sexx) {
           System.out.println("Erro: "+sexx);
           myError(sexx, "7");
       } catch (Exception exx) {
           myError(exx, "8");
           System.out.println("Erro: "+exx);
       }
       if (result == 1) {
           return true;
       } else {
           return false;
       }
   }

   // used to delete from the database with an SQL statement
   public synchronized boolean DeleteDB(String sql) {
       int result = 0;
       try {
           deleteStatement = connection.prepareStatement(sql);
           result = deleteStatement.executeUpdate();
           deleteStatement = null; //
       } catch (Exception exx) {
           myError(exx, "9");
           System.out.println("Erro: "+exx);
       }
       if (result == 1) {
           return true;
       } else {
           return false;
       }
   }

   public void setTableName(String tablename) {
       this.tablename = tablename;
   }

   public String getTableName() {
       return this.tablename;
   }

   // used to lock the table
   public synchronized boolean lockTable(String sql) {
       //sql = sql.toLowerCase();
       Statement stmt = null;
       try {
           stmt = connection.createStatement();
           stmt.executeUpdate(sql);
           stmt = null; //
       } catch (SQLException sexx) {
           myError(sexx, "14");
           return false;
       } catch (Exception exx) {
           myError(exx, "15");
           return false;
       }
       return true;
   }

   // used to unlock the table
   public synchronized boolean unlockTable(String sql) {
       //sql = sql.toLowerCase();
       Statement stmt = null;
       try {
           stmt = connection.createStatement();
           stmt.executeUpdate(sql);
           stmt = null; //
       } catch (SQLException sexx) {
           myError(sexx, "16");
           return false;
       } catch (Exception exx) {
           myError(exx, "17");
           return false;
       }
       return true;
   }

   // use to start aut commit mode
   public synchronized boolean initAutoCommit() {
       try {
           if(connection.getAutoCommit()){
               System.out.println("SetAutoCommit = false");
              connection.setAutoCommit(false);
           }
       } catch (SQLException sexx) {
           myError(sexx, "18");
           return false;
       } catch (Exception exx) {
           myError(exx, "19");
           return false;
       }
       return true;
   }

   // use to begin the transaction
   public synchronized boolean beginTransaction() {
       Statement stmt = null;
       Declare.exCounter = 0;
       try {
           stmt = connection.createStatement();
           stmt.executeUpdate("START TRANSACTION;");
           System.out.println("START TRANSACTION");
           stmt = null; //
       } catch (SQLException sexx) {
           myError(sexx, "20");
           return false;
       } catch (Exception exx) {
           myError(exx, "21");
           return false;
       }
       return true;
   }

   // used to end the transaction
   public synchronized boolean endTransaction() {
       try {
           connection.commit();
           System.out.println("Transaction Commited");
       } catch (SQLException sexx) {
          // myError(sexx, "22");
           System.out.println(sexx);
           return false;
       } catch (Exception exx) {
           myError(exx, "23");
           return false;
       }

       return true;
   }

   // used to rollback the transaction
   public synchronized boolean rollBack() {
       try {
           connection.rollback();
           System.out.println("Transaction Rolled Back");
       } catch (SQLException sexx) {
           myError(sexx, "24");
           return false;
       } catch (Exception exx) {
           myError(exx, "25");
           return false;
       }
       return true;
   }

   // used to modify the language
   public synchronized boolean modifyLanguage(String sql) {
       Statement stmt = null;
       try {
           stmt = connection.createStatement();
           stmt.executeUpdate(sql);
           stmt = null; //
       } catch (SQLException sexx) {
           myError(sexx, "26");
           return false;
       } catch (Exception exx) {
           myError(exx, "27");
           return false;
       }
       return true;
   }

   // a general error routine
   private void myError(Exception c, String no) {
       Declare.exCounter+= 1;
       c.printStackTrace();
   }
}
\$\endgroup\$
1
\$\begingroup\$

scanned your code, good to go, just one thing-- by Convention Java methods start with lower case. So, the method "UpdateDB()" better suit updateDb() or updateDatabase()

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.