Re: Connecting to MySQL with JBuilder?

[joe byers]

I have tried to connect using the datamanager class wizard and Jbuilder defaults to their custom jbdc. I can not figure out how to get the mysql jdbc driver in the class paths for the datamanager class, but I have written my own connection class to a mysql database. You have to add the mysql jar to the project and then import the needed classes.

I placed the code below. This is not "grade A" coding because I am just learning JAVA myself, but it works for my project.

The calling method passes a textpanel object to most mehtods including the connection initiation methods so I can log progress of my sql queries. I am sure the the try catch blocks could be improved.

I would like to note, that I have looked at netbeans IDE. Mostly jbuilder and netbeans are the same but jbuilder's form designer wizards are better than netbeans. Netbeans automatic form code is more readable than jbuilder's. The wizard was more important to me.

Good luck.
joe


package pjmsummary;

/**
* <p>Title: </p>
*
* <p>Description: </p>
*
* <p>Copyright: Copyright (c) 2005</p>
*
* <p>Company: </p>
*
* @author not attributable
* @version 1.0
*/
import java.sql.*;

public class MyConn {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
public Connection conn = null;
private String sqldb;
private String svr = "SERVER";
public MyConn() {
try {
jbInit();
}
catch (Exception ex) {
ex.printStackTrace();
}
}

public void setconnection(java.awt.TextArea obj) {
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection("jdbc:mysql://" + svr + ":3306/" +
sqldb + "?user=USER");
// Do something with the Connection
obj.append(" \n" + "SQL connected: \n");
// System.out.println("SQL connected: " );
// conn.close();
// conn=null;
}
catch (ClassNotFoundException ex) {
obj.append("Driver not found\n");
}
catch (SQLException ex) {
// handle any errors
obj.append("SQLException: " + ex.getMessage() + " \n");
obj.append("SQLState: " + ex.getSQLState() + " \n");
obj.append("VendorError: " + ex.getErrorCode() + " \n");

}
}

/**
* nodeselect
*/
public void nodeselect(java.awt.TextArea obj, String firstdate, String lastdate,
int zones, boolean lmpload) {
/* load the lmprices_t into a temp file for the analysis periods*/
String sqlstr, prtstr;
String fd[], so, si;
ResultSet nodes = null, lmpso = null, lmpsi = null;
Statement stmt, nodestmt;
int result;
int cset = 34;
int row_lmpso, row_lmpsi;
restart(obj,firstdate,lastdate,lmpload);
try {
stmt = conn.createStatement();
nodestmt = conn.createStatement();
switch (zones) {
case 0:
nodes = nodestmt.executeQuery("select * from " + sqldb +
".nodecounts;");
break;
case 1:
nodes = nodestmt.executeQuery("select * from " + sqldb +
".nodeacross;");
break;
case 2:
nodes = nodestmt.executeQuery("select * from " + sqldb + ".nodeeq;");
break;
}

while (nodes.next()) {
si = nodes.getString("zone_sink");
so = nodes.getString("zone_source");
sqlstr = "insert into " + sqldb +
".paths select source, sink from PJM.nodal_paths where zone_source=";
sqlstr += "'" + nodes.getString("zone_source") + "'";
sqlstr += " and zone_sink=" + "'" + nodes.getString("zone_sink") +
"'" + ";";
prtstr = "Source Zone:" + so + " - Sink Zone:"
+ si + " - Processed\n";
result = stmt.executeUpdate(sqlstr);
calclmpdaily(obj, firstdate, lastdate, so, si);
fd = firstdate.split("-");
calclmp(obj, fd[1], fd[0]);
obj.append(prtstr);
stmt.addBatch("delete from " + sqldb + ".paths;");
stmt.addBatch("delete from " + sqldb + ".lmp_daily;");
stmt.executeBatch();
}
/* Free resultset */
nodes.close();
nodes = null;
stmt.close();
stmt = null;
nodestmt.close();
nodestmt = null;
}
catch (SQLException ex) {
// handle any errors
obj.append("SQLException: " + ex.getMessage() + " \n");
obj.append("SQLState: " + ex.getSQLState() + " \n");
obj.append("VendorError: " + ex.getErrorCode() + " \n");
}
}

/**
* restart
*/
public void restart(java.awt.TextArea obj,String firstdate, String lastdate,
boolean lmpload) {
Statement stmt;
String sqlstr;
if (!lmpload) {
try {
stmt = conn.createStatement();
stmt.addBatch("drop table if exists lmpt");
stmt.addBatch("create table " + sqldb +
".lmpt like PJM.lmprices_t");
sqlstr = "insert into " + sqldb +
".lmpt select * from PJM.lmprices_t where ";
sqlstr += "date between '" + firstdate + "' and '" + lastdate +
"'";
stmt.addBatch(sqlstr);
stmt.executeBatch();
}
catch (SQLException ex) {
// handle any errors
obj.append("SQLException: " + ex.getMessage() + " \n");
obj.append("SQLState: " + ex.getSQLState() + " \n");
obj.append("VendorError: " + ex.getErrorCode() + " \n");
}
}
}
/**
* calclmpdaily
*/
public int calclmpdaily(java.awt.TextArea obj, String firstdate, String lastdate,
String so, String si) {
int result;
String sqlstr, prtstr;
Statement stmt;
/* code removed */
}

/**
* calclmp
*/
public void calclmp(java.awt.TextArea obj, String month, String year) {
int result;
String sqlstr;
Statement stmt;
/* code removed
}

public void updatemonth(java.awt.TextArea obj, String mon) {
/* update the processed month to master lmp db */
int result;
String sqlstr = null;
Statement stmt;
try {
stmt = conn.createStatement();
obj.append("Update monthly tables\n");
result = stmt.executeUpdate(
"alter table pjmwork.tmp_month_hr drop column month;");
sqlstr = "insert into PJM.lmp_mon" + mon.trim() + " select * from " +
sqldb +
".tmp_month_hr;";
result = stmt.executeUpdate(sqlstr);
stmt.close();
stmt = null;
}
catch (SQLException ex) {
// handle any errors
obj.append("SQLException: " + ex.getMessage() + " \n");
obj.append("SQLState: " + ex.getSQLState() + " \n");
obj.append("VendorError: " + ex.getErrorCode() + " \n");
}
}

/**
* resetmain
*/
public void resetmain(java.awt.TextArea obj) {
/* reset current month files */
String sqlstr;
Statement stmt;
int result;
try {
stmt = conn.createStatement();
sqlstr = "drop table if exists " + sqldb + ".lmp_daily, " + sqldb +
".t6, " + sqldb + ".tmp_month_hr, ";
sqlstr += sqldb + ".lmpt, " + sqldb + ".master_source_sink, " +
sqldb + ".paths," + sqldb + ".nodeeq,";
sqlstr += sqldb + ".nodecounts," + sqldb + ".nodeacross;";
result = stmt.executeUpdate(sqlstr);
stmt.addBatch("create table " + sqldb +
".lmp_daily like PJM.pjm_lmp_daily;");
stmt.addBatch("alter table " + sqldb +
".lmp_daily drop column lmp_mean, drop column _freq_;");
stmt.addBatch("alter table " + sqldb +
".lmp_daily change column lmp_sum lmp real;");
stmt.addBatch("alter table " + sqldb +
".lmp_daily drop column lmp_stddev;");
stmt.addBatch("create table " + sqldb +
".master_source_sink like PJM.master_source_sink;");
stmt.addBatch("create table " + sqldb +
".tmp_month_hr like PJM.pjm_lmp_month_hr;");
stmt.executeBatch();
stmt.addBatch("insert into " + sqldb +
".master_source_sink select * from PJM.master_source_sink;");
stmt.addBatch("create table " + sqldb +
".nodecounts like PJM.nodecounts;");
stmt.addBatch("insert into " + sqldb +
".nodecounts select * from PJM.nodecounts;");
stmt.addBatch("create table " + sqldb +
".nodeacross like PJM.nodeacross;");
stmt.addBatch("insert into " + sqldb +
".nodeacross select * from PJM.nodeacross;");
stmt.addBatch("create table " + sqldb + ".nodeeq like PJM.nodeeq;");
stmt.addBatch("insert into " + sqldb +
".nodeeq select * from PJM.nodeeq;");
stmt.addBatch("create table " + sqldb + ".paths like PJM.paths;");
stmt.executeBatch();
}
catch (SQLException ex) {
// handle any errors
obj.append("SQLException: " + ex.getMessage() + " \n");
obj.append("SQLState: " + ex.getSQLState() + " \n");
obj.append("VendorError: " + ex.getErrorCode() + " \n");
}
}

public void resetmonth(java.awt.TextArea obj) {
/* reset current month files */
String sqlstr;
Statement stmt;
int result;
obj.append("Clean up monthly tables\n");
resetmain(obj);
try {
stmt = conn.createStatement();
/* need to remove for participant summaries*/
sqlstr = "alter table " + sqldb + ".tmp_month_hr ";
sqlstr += "drop column lmp_option_long_sum, drop column lmp_option_short_sum, drop column lmp_option_long_mean, drop column lmp_option_short_mean, ";
sqlstr += "drop column lmp_option_long_max, drop column lmp_option_long_min, drop column lmp_option_long_stddev, ";
sqlstr += "drop column lmp_option_short_max, drop column lmp_option_short_min, drop column lmp_option_short_stddev;";
result = stmt.executeUpdate(sqlstr);
}
catch (SQLException ex) {
// handle any errors
obj.append("SQLException: " + ex.getMessage() + " \n");
obj.append("SQLState: " + ex.getSQLState() + " \n");
obj.append("VendorError: " + ex.getErrorCode() + " \n");
}
obj.append("Done with Clean up monthly tables\n");
}

/**
* resetsummary
*/
public void resetsummary(java.awt.TextArea obj) {
resetmain(obj);
}

public void setSqldb(String sqldb) {
this.sqldb = sqldb;
}

public void setSvr(String svr) {
this.svr = svr;
}

public String getSqldb() {
return sqldb;
}

public String getSvr() {
return svr;
}

/* public static void main(String[] args) {
MyConn myconn = new MyConn();
}*/
public void finalize(java.awt.TextArea obj) {
try {
conn.close();
conn = null;
}
catch (SQLException ex) {
// handle any errors
obj.append("SQLException: " + ex.getMessage() + " \n");
obj.append("SQLState: " + ex.getSQLState() + " \n");
obj.append("VendorError: " + ex.getErrorCode() + " \n");
}
}

private void jbInit() throws Exception {
}
}