I have a code that connects to 2 databases(one is MySQL and other is Sybase). It retrives data from sybase and store it in mysql, then does some operation on then data and filters it. Filtered data is send as an sms to list of people stored in one of the tables in MySQL database. this cycle is repeated every 5 mins. Below is my Code:
package my.withgui;
import java.sql.*;
import java.io.IOException;
import java.net.URL;
import java.net.URLConnection;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.ScheduledFuture;
import static java.util.concurrent.TimeUnit.*;
public class gui extends javax.swing.JFrame {
private final ScheduledExecutorService scheduler =
Executors.newScheduledThreadPool(1);
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/alarm";
static final String JDBC_DRIVER_SECOND = "net.sourceforge.jtds.jdbc.Driver";
static final String DB_URL_SECOND = "jdbc:jtds:sybase://10.159.252.29:4100/fmdb";
static final String USER = "root";
static final String PASS = "Itarcy123";
static final String USER_SECOND = "sa";
static final String PASS_SECOND = "Changeme_123";
String a;
String b;
String c;
String d;
String str;
String keep;
int counter = 0;
String f;
String g;
String h;
int a1, a2,a3,a4;
String a5;
Connection conn = null;
Connection conn_2 = null;
PreparedStatement stmt = null;
PreparedStatement stmt1 = null;
PreparedStatement stmt2 = null;
PreparedStatement stmt3 = null;
PreparedStatement stmt4 = null;
PreparedStatement stmt5 = null;
PreparedStatement stmt6 = null;
PreparedStatement stmt7 = null;
PreparedStatement stmt8 = null;
PreparedStatement stmt9 = null;
public gui() {
initComponents();
}
public void beepForAnHour() {
final Runnable beeper = new Runnable() {
public void run() {
try{
String sql = "delete from newtable";
stmt5 = conn.prepareStatement(sql);
stmt5.executeUpdate(sql);
sql = "delete from sms21";
stmt6 = conn.prepareStatement(sql);
stmt6.executeUpdate(sql);
sql = "delete from sms23";
stmt3 = conn.prepareStatement(sql);
stmt3.executeUpdate(sql);
sql = "delete from sms1";
stmt7 = conn.prepareStatement(sql);
stmt7.executeUpdate(sql);
sql = "delete from sms2";
stmt8 = conn.prepareStatement(sql);
stmt8.executeUpdate(sql);
Statement stmt_1= conn_2.createStatement();
//change needed
String sql_1 = "select distinct tbl_alm_log_2000000000.Csn, tbl_alm_log_2000000000.IsCleared, tbl_alm_log_2000000000.Id,tbl_alm_log_2000000000.NEType, tbl_alm_log_2000000000.OccurTime, tbl_alm_log_2000000000.hostIP, tbl_alm_log_2000000000.ExtendInfo From fmdb.dbo.tbl_alm_log_2000000000 Where IsCleared = 0";
ResultSet rs = stmt_1.executeQuery(sql_1);
String sql_2 = "insert into newtable (CSN, IsCleared, Id, NEType, OccurTime, hostIP) values(?,?,?,?,?,?)";
conn.setAutoCommit(false);
PreparedStatement ps = conn.prepareStatement(sql_2);
final int batchSize = 1000;
int count = 0;
while (rs.next()){
a = rs.getString(3); //Id
if(a.equals("301"))
{
str = rs.getString(7);
keep = str.substring(str.indexOf("neIP=")+5,str.indexOf(", neBackupIP"));
ps.setString(1, rs.getString(1)); //csn
ps.setString(2, rs.getString(2)); //iscleared
ps.setString(3, rs.getString(3));//id
ps.setString(4, rs.getString(4));//netype
ps.setString(5, rs.getString(5));//occurtime
ps.setString(6, keep);//hostip
}else
{
ps.setString(1, rs.getString(1)); //csn
ps.setString(2, rs.getString(2)); //iscleared
ps.setString(3, rs.getString(3));//id
ps.setString(4, rs.getString(4));//netype
ps.setString(5, rs.getString(5));//occurtime
ps.setString(6, rs.getString(6));//hostip
}
ps.addBatch();
if(++count % batchSize == 0) {
ps.executeBatch();
}
}
ps.executeBatch(); // insert remaining records
conn.commit();
ps.close();
System.out.println("done1");
Statement stmt_3 = conn.createStatement();
sql = "select newtable.csn, newtable.Id, newtable.hostIP, newtable.OccurTime from newtable join list on newtable.Id = list.NEFDNId";
ResultSet rs_1 = stmt_3.executeQuery(sql);
Statement stmt_4 = conn.createStatement();
sql = "select list.Index_Name from list join newtable on newtable.Id = list.NEFDNId";
ResultSet rs_2 = stmt_4.executeQuery(sql);
sql_2 = "Insert into sms1 (Csn, Id, hostIP, alarm, OccurTime) values(?,?,?,?,?)";
conn.setAutoCommit(false);
PreparedStatement ps_1 = conn.prepareStatement(sql_2);
final int batchSize_1 = 1000;
int count_1 = 0;
while(rs_1.next()&&rs_2.next())
{
ps_1.setString(1, rs_1.getString(1));
ps_1.setString(2, rs_1.getString(2));
ps_1.setString(3, rs_1.getString(3));
ps_1.setString(4, rs_2.getString(1));
ps_1.setString(5, rs_1.getString(4));
ps_1.addBatch();
if(++count_1 % batchSize_1 == 0) {
ps_1.executeBatch();
}
}
ps_1.executeBatch(); // insert remaining records
conn.commit();
ps_1.close();
System.out.println("done2");
/////////////////////////////////////////////////////////////////////////
Statement stmt_5 = conn.createStatement();
sql = "Select sitelist.Site, sitelist.OAM_IP, sitelist.SMS_Infra, sitelist.SMS_Numbers_Telecom from sitelist join newtable on newtable.hostIP = sitelist.OAM_IP";
ResultSet rs_3 = stmt_5.executeQuery(sql);
sql_2 = "Insert into sms2 (Site, OAM_IP, SMS_Infra,SMS_Numbers_Telecom) values(?,?,?,?)";
conn.setAutoCommit(false);
PreparedStatement ps_2 = conn.prepareStatement(sql_2);
final int batchSize_2 = 1000;
int count_2 = 0;
while(rs_3.next())
{
ps_2.setString(1, rs_3.getString(1));
ps_2.setString(2, rs_3.getString(2));
ps_2.setString(3, rs_3.getString(3));
ps_2.setString(4, rs_3.getString(4));
ps_2.addBatch();
if(++count_2 % batchSize_2 == 0) {
ps_2.executeBatch();
}
// System.out.println(a+" "+b+" "+c);
}
ps_2.executeBatch(); // insert remaining records
conn.commit();
ps_2.close();
System.out.println("done3");
///////////////////////////////
Statement stmt_6 = conn.createStatement();
sql = "select distinct sms1.Csn, sms1.OccurTime, sms2.Site, sms2.OAM_IP, sms2.SMS_Infra, sms2.SMS_Numbers_Telecom, sms1.Id, sms1.alarm from sms2, sms1 where sms2.OAM_IP = sms1.hostIP";
ResultSet rs_4 = stmt_6.executeQuery(sql);
//////////////////// making current table ;
sql_2 = "Insert into sms21 (csn, OccurTime, Site, OAM_IP, SMS_Infra,SMS_Numbers_Telecom, alarmID, alarmName, overdue) values(?,?,?,?,?,?,?,?,?)";
conn.setAutoCommit(false);
PreparedStatement ps_3 = conn.prepareStatement(sql_2);
final int batchSize_3 = 1000;
int count_3 = 0;
while(rs_4.next())
{
a = rs_4.getString(3); //site name
b = rs_4.getString(6); //mobile no
c = rs_4.getString(8); // alarm name
ps_3.setString(1, rs_4.getString(1));
ps_3.setString(2, rs_4.getString(2));
ps_3.setString(3, rs_4.getString(3));
ps_3.setString(4, rs_4.getString(4));
ps_3.setString(5, rs_4.getString(5));
ps_3.setString(6, rs_4.getString(6));
ps_3.setString(7, rs_4.getString(7));
ps_3.setString(8, rs_4.getString(8));
ps_3.setString(9, "1");
ps_3.addBatch();
if(++count_3 % batchSize_3 == 0) {
ps_3.executeBatch();
}
}
ps_3.executeBatch(); // insert remaining records
conn.commit();
ps_3.close();
// System.out.println("all sms send");
System.out.println("done4");
////////////////////////////////////////////////////////////////////////// changes from here
Statement stmt_7 = conn.createStatement();
sql = "select sms21.csn, sms21.OccurTime, sms21.Site, sms21.OAM_IP, sms21.SMS_Infra, sms21.SMS_Numbers_Telecom, sms21.alarmID, sms21.alarmName, coalesce(sms22.overdue,sms21.overdue) as overdue from sms21 left join sms22 on(sms22.csn = sms21.csn)";
ResultSet rs_5 = stmt_7.executeQuery(sql);
sql_2 = "Insert into sms23 (csn, OccurTime, Site, OAM_IP, SMS_Infra,SMS_Numbers_Telecom, alarmID, alarmName, overdue) values(?,?,?,?,?,?,?,?,?)";
conn.setAutoCommit(false);
PreparedStatement ps_4 = conn.prepareStatement(sql_2);
final int batchSize_4 = 1000;
int count_4 = 0;
while(rs_5.next())
{
ps_4.setString(1, rs_5.getString(1));
ps_4.setString(2, rs_5.getString(2));
ps_4.setString(3, rs_5.getString(3));
ps_4.setString(4, rs_5.getString(4));
ps_4.setString(5, rs_5.getString(5));
ps_4.setString(6, rs_5.getString(6));
ps_4.setString(7, rs_5.getString(7));
ps_4.setString(8, rs_5.getString(8));
ps_4.setString(9, rs_5.getString(9));
ps_4.addBatch();
if(++count_4 % batchSize_4 == 0) {
ps_4.executeBatch();
}
}
ps_4.executeBatch(); // insert remaining records
conn.commit();
ps_4.close();
System.out.println("done!!");
conn.setAutoCommit(true);
System.out.println("done5");
//////sending alarm sms
Statement stmt_8 = conn.createStatement();
sql = "select OccurTime, Site, SMS_Infra, SMS_Numbers_Telecom, alarmName, overdue, alarmID from sms23";
ResultSet rs_6 = stmt_8.executeQuery(sql);
while(rs_6.next())
{
a = rs_6.getString(1); //time
b = rs_6.getString(2); //site
c = rs_6.getString(3); //sms_infra
d = rs_6.getString(4); //sms no
f = rs_6.getString(5); //alarm name
g = rs_6.getString(6); // overdue
h = rs_6.getString(7); //perungudi alarm mains fail
// System.out.println(g);
a1 = Integer.valueOf(a) % 86400;
a2 = a1/3600;
a3 = a1 % 3600;
a4 = a3/60;
a5 = Integer.toString(a2)+"Hrs "+Integer.toString(a4)+" Mins";
if (g.equals("1"))
{
//alarm in site at time
// f+" in "+b+" at "+a5
try{
String z = "\u2193 "+f+" in "+b+" at "+a5;
String encodedURL = java.net.URLEncoder.encode(z, "utf-8");
String link;
if(b.equals("CBE_MSC_Coimbatore_Idea_Crit")||b.equals("CHN_Perungudi_BSC_Idea"))
{
link = "sms link";
}
else
{
link = "sms link";
}
URL myURL = new URL(link);
URLConnection myURLConnection = myURL.openConnection();
myURLConnection.connect();
myURLConnection.getInputStream();
// System.out.println("restoration send");
}catch(IOException e)
{
e.printStackTrace();
}
}
// overdue
}
System.out.println("done6");
Statement stmt_9 = conn.createStatement();
sql = "select * from sms22 where sms22.csn not in (select sms21.csn from sms21 group by sms21.csn)";
ResultSet rs_7 = stmt_9.executeQuery(sql);
while(rs_7.next())
{
d = rs_7.getString(2); //time
a = rs_7.getString(3);
b = rs_7.getString(6);
c = rs_7.getString(8);
a1 = Integer.valueOf(d) % 86400;
a2 = a1/3600;
a3 = a1 % 3600;
a4 = a3/60;
a5 = Integer.toString(a2)+"Hrs "+Integer.toString(a4)+" Mins";
try{
d = "\u2191 "+c+" is cleared in "+a;
String encodedURL = java.net.URLEncoder.encode(d, "utf-8");
String link1;
if(a.equals("CBE_MSC_Coimbatore_Idea_Crit")||a.equals("CHN_Perungudi_BSC_Idea"))
{
link1 = "sms link";
}
else
{
link1 = "sms link";
}
URL myURL = new URL(link1);
URLConnection myURLConnection = myURL.openConnection();
myURLConnection.connect();
myURLConnection.getInputStream();
//System.out.println("restoration send");
}catch(IOException e)
{
e.printStackTrace();
}
}
sql = "delete from sms22";
stmt1 = conn.prepareStatement(sql);
stmt1.executeUpdate(sql);
System.out.println("done!!");
sql = "Insert into sms22 select * from sms23";
stmt2 = conn.prepareStatement(sql);
stmt2.executeUpdate(sql);
System.out.println("done!!");
sql = "delete from sms23";
stmt3 = conn.prepareStatement(sql);
stmt3.executeUpdate(sql);
sql = "update sms22 set overdue = overdue+1";
stmt4 = conn.prepareStatement(sql);
stmt4.executeUpdate(sql);
sql = "delete from newtable";
stmt5 = conn.prepareStatement(sql);
stmt5.executeUpdate(sql);
sql = "delete from sms21";
stmt6 = conn.prepareStatement(sql);
stmt6.executeUpdate(sql);
sql = "delete from sms1";
stmt7 = conn.prepareStatement(sql);
stmt7.executeUpdate(sql);
sql = "delete from sms2";
stmt8 = conn.prepareStatement(sql);
stmt8.executeUpdate(sql);
System.out.println("done!!");
}catch(SQLException se){
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}
System.out.println("beep"); }
};
final ScheduledFuture<?> beeperHandle =
scheduler.scheduleAtFixedRate(beeper, 5, 5, MINUTES);
/*scheduler.schedule(new Runnable() {
public void run() { beeperHandle.cancel(true); }
},24*60, MINUTES);*/
}
/**
* This method is called from within the constructor to initialize the form.
* WARNING: Do NOT modify this code. The content of this method is always
* regenerated by the Form Editor.
*/
@SuppressWarnings("unchecked")
// <editor-fold defaultstate="collapsed" desc="Generated Code">
private void initComponents() {
jButton1 = new javax.swing.JButton();
jButton2 = new javax.swing.JButton();
setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
jButton1.setText("START");
jButton1.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
jButton1ActionPerformed(evt);
}
});
jButton2.setText("STOP");
jButton2.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
jButton2ActionPerformed(evt);
}
});
javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
getContentPane().setLayout(layout);
layout.setHorizontalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addGap(154, 154, 154)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
.addComponent(jButton1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
.addComponent(jButton2, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
.addContainerGap(181, Short.MAX_VALUE))
);
layout.setVerticalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addGap(85, 85, 85)
.addComponent(jButton1)
.addGap(67, 67, 67)
.addComponent(jButton2)
.addContainerGap(102, Short.MAX_VALUE))
);
pack();
}// </editor-fold>
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
try{
Class.forName(JDBC_DRIVER);
System.out.println("connecting to database mysql");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("connected to database successfully");
Class.forName(JDBC_DRIVER_SECOND);
System.out.println("connecting to database SYBASE");
conn_2 = DriverManager.getConnection(DB_URL_SECOND, USER_SECOND, PASS_SECOND);
System.out.println("connected to database successfully");
}catch(SQLException se){
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}
beepForAnHour();
}
private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {
System.exit(0);
}
try {
for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
if ("Nimbus".equals(info.getName())) {
javax.swing.UIManager.setLookAndFeel(info.getClassName());
break;
}
}
} catch (ClassNotFoundException ex) {
java.util.logging.Logger.getLogger(gui.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (InstantiationException ex) {
java.util.logging.Logger.getLogger(gui.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (IllegalAccessException ex) {
java.util.logging.Logger.getLogger(gui.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (javax.swing.UnsupportedLookAndFeelException ex) {
java.util.logging.Logger.getLogger(gui.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
}
//</editor-fold>
/* Create and display the form */
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new gui().setVisible(true);
}
});
}
// Variables declaration - do not modify
private javax.swing.JButton jButton1;
private javax.swing.JButton jButton2;
// End of variables declaration
}
I am executing SQL queries through java as my gole is to automate my process ie sending SMS alerts to relevant people.One time i got Java heap space error(but only one time), now netbeans is just getting struck in between. I am new to java language and would like suggestions to improve my code. I have remove some code to keep it short, so might have intrduced some syntax error.
System that i am using has following specifications: Pentium dual core 2GB ram windows 7
