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();
}
}
