Commits

Rudi Giacomini  committed c412f83

Changed save mode to prepared stat instead of direct query.
This allow to save and manage quotes in strings

  • Participants
  • Parent commits dbcb958

Comments (0)

Files changed (11)

File src/nyagua/DB.java

         }
     }
     
+   /**
+     * Allow accessing current connection
+     * 
+     * @return 
+     */
+    public  static Connection getConn(){
+       return DB.conn;
+   }
+    
     /**
      * Close the global connection to db
      */

File src/nyagua/Ny.java

     tank.setHeight(LocUtil.delocalizeDouble(aquariumHeightTextField.getText()));
     tank.setVolume(LocUtil.delocalizeDouble(aquariumWaterVolTextField.getText()));
     tank.setTankVolume(LocUtil.delocalizeDouble(aquariumTankVolTextField.getText()));
+    tank.setOpen(aquariumOCCheckBox.isSelected());
     tank.setBottom(aquariumBottomTextField.getText());
     tank.setType(aquariumTypeTextField.getText());
     tank.setStartDate(LocUtil.delocalizeDate(aquariumStartDateTextField.getDate()));

File src/nyagua/data/Aquarium.java

 
 import java.awt.image.BufferedImage;
 import java.io.IOException;
+import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.Date;
                 tank.endDate = LocUtil.localizeDate(rs.getString("End_date")); // NOI18N
                 tank.newStartDate = LocUtil.localizeAsDate(rs.getString("Start_date")); // NOI18N
                 tank.newEndDate = LocUtil.localizeAsDate(rs.getString("End_date")); // NOI18N
-                tank.open = rs.getBoolean("o_c"); // NOI18N
+                tank.open=Boolean.parseBoolean(rs.getString("o_c"));
+                //tank.open = rs.getBoolean("o_c"); // NOI18N
                 tank.tankVolume = LocUtil.localizeDouble(rs.getString("Tank_vol")); // NOI18N
                 tank.bottom = rs.getString("Bottom"); // NOI18N
                // "id","Name","Deep","Width","Height","Water_vol","Type","Start_date","End_date","o_c"
     public static void save(Aquarium tank){
         int currID=tank.AqId;
 
-        String qry = "";
+        /*String qry = "";
         qry = "INSERT OR REPLACE INTO " + TABLE + " VALUES (";// NOI18N
         if (currID == 0) {
             qry = qry + "NULL,";    // NOI18N
         qry = qry + tank.startDate + "','" + tank.endDate + "','";// NOI18N
         qry = qry + String.valueOf(tank.open)  + "','";// NOI18N
         qry = qry + tank.tankVolume + "','" + tank.bottom +"'";// NOI18N
-        qry = qry + ");";// NOI18N
+        qry = qry + ");";// NOI18N*/
         DB.openConn();
-        DB.execQuery(qry);
+        try {
+            //Changes due to single quote problem
+            PreparedStatement prep=DB.getConn().prepareStatement(""// NOI18N
+                    + "INSERT OR REPLACE INTO " + TABLE + " VALUES (?, ?, ?, ?, ?, ?, ?, ?,"// NOI18N
+                    + "?, ?, ?, ?);");// NOI18N
+            if (currID == 0) {
+                prep.setString(1, null);
+            } else {                //the record is in update
+                prep.setString(1,  String.valueOf(currID));
+            }            
+            prep.setString(2,tank.name);
+            prep.setString(3,tank.depth);
+            prep.setString(4,tank.width);
+            prep.setString(5,tank.height);
+            prep.setString(6,tank.waterVolume);
+            prep.setString(7,tank.type);
+            prep.setString(8,tank.startDate);
+            prep.setString(9,tank.endDate);
+            prep.setString(10,String.valueOf(tank.open));
+            prep.setString(11,tank.tankVolume);
+            prep.setString(12,tank.bottom);            
+            prep.executeUpdate();            
+        } catch (SQLException ex) {
+            Logger.getLogger(Reading.class.getName()).log(Level.SEVERE, null, ex);
+        }        
+        //end changes
+        //DB.execQuery(qry);
         DB.closeConn();
         
         //try to save or update image

File src/nyagua/data/Device.java

 
 package nyagua.data;
 
+import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.logging.Level;
      */
     public void save(Device dev){
         int currID=dev.id;
-
+        /*
         String qry = "";
         qry = "INSERT OR REPLACE INTO " + TABLE + " VALUES (";// NOI18N
         if (currID == 0) {
         qry = qry + "'" + dev.name + "','" + dev.brand + "','";// NOI18N
         qry = qry + dev.wattage + "','" + dev.notes + "','";// NOI18N
         qry = qry + dev.onPeriod + "'," + Global.AqID;// NOI18N
-        qry = qry + ");";// NOI18N
+        qry = qry + ");";// NOI18N*/
         DB.openConn();
-        DB.execQuery(qry);
+        try {
+            //Changes due to single quote problem
+            PreparedStatement prep=DB.getConn().prepareStatement(""// NOI18N
+                    + "INSERT OR REPLACE INTO " + TABLE + " VALUES (?, ?, ?, ?, ?, ?, ?);");// NOI18N
+            if (currID == 0) {
+                prep.setString(1, null);
+            } else {                //the record is in update
+                prep.setString(1,  String.valueOf(currID));
+            }            
+            prep.setString(2,dev.name);
+            prep.setString(3,dev.brand);
+            prep.setString(4,dev.wattage);
+            prep.setString(5,dev.notes);
+            prep.setString(6,dev.onPeriod);
+            prep.setInt(7,Global.AqID);
+            prep.executeUpdate();            
+        } catch (SQLException ex) {
+            Logger.getLogger(Reading.class.getName()).log(Level.SEVERE, null, ex);
+        }        
+        //end changes
+        //DB.execQuery(qry);
         DB.closeConn();
     }
      

File src/nyagua/data/Expense.java

 
 package nyagua.data;
 
+import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.Date;
      */
     public void save(Expense exp){
         int currID=exp.id;
-
+        /*
         String qry = "";
         qry = "INSERT OR REPLACE INTO " + TABLE + " VALUES (";// NOI18N
         if (currID == 0) {
         qry = qry + "'" + exp.date + "','" + exp.item + "','";// NOI18N
         qry = qry + exp.price + "','" + exp.notes + "','";// NOI18N
         qry = qry + exp.shop + "'," + Global.AqID;// NOI18N
-        qry = qry + ");";// NOI18N
+        qry = qry + ");";// NOI18N*/
         DB.openConn();
-        DB.execQuery(qry);
+        try {
+            //Changes due to single quote problem
+            PreparedStatement prep=DB.getConn().prepareStatement(""// NOI18N
+                    + "INSERT OR REPLACE INTO " + TABLE + " VALUES (?, ?, ?, ?, ?, ?, ?);");// NOI18N
+            if (currID == 0) {
+                prep.setString(1, null);
+            } else {                //the record is in update
+                prep.setString(1,  String.valueOf(currID));
+            }            
+            prep.setString(2,exp.date);
+            prep.setString(3,exp.item);
+            prep.setString(4,exp.price);
+            prep.setString(5,exp.notes);
+            prep.setString(6,exp.shop);
+            prep.setInt(7,Global.AqID);
+            prep.executeUpdate();            
+        } catch (SQLException ex) {
+            Logger.getLogger(Reading.class.getName()).log(Level.SEVERE, null, ex);
+        }        
+        //end changes
+        //DB.execQuery(qry);
         DB.closeConn();
     }
 

File src/nyagua/data/Fish.java

 
 package nyagua.data;
 
+import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.Date;
      */
     public void save(Fish specimen){
         int currID=specimen.id;
-
+        /*
         String qry = "";
         qry = "INSERT OR REPLACE INTO " + TABLE + " VALUES (";// NOI18N
         if (currID == 0) {
         qry = qry + "'" + specimen.date + "','" + specimen.name + "','";// NOI18N
         qry = qry + specimen.males + "','" + specimen.females + "','";// NOI18N
         qry = qry + specimen.notes + "'," + Global.AqID;// NOI18N
-        qry = qry + ");";// NOI18N
+        qry = qry + ");";// NOI18N*/
         DB.openConn();
-        DB.execQuery(qry);
+        try {
+            //Changes due to single quote problem
+            PreparedStatement prep=DB.getConn().prepareStatement(""// NOI18N
+                    + "INSERT OR REPLACE INTO " + TABLE + " VALUES (?, ?, ?, ?, ?, ?, ?);");// NOI18N
+            if (currID == 0) {
+                prep.setString(1, null);
+            } else {                //the record is in update
+                prep.setString(1,  String.valueOf(currID));
+            }            
+            prep.setString(2,specimen.date);
+            prep.setString(3,specimen.name);
+            prep.setString(4,specimen.males);
+            prep.setString(5,specimen.females);
+            prep.setString(6,specimen.notes);
+            prep.setInt(7,Global.AqID);
+            prep.executeUpdate();            
+        } catch (SQLException ex) {
+            Logger.getLogger(Reading.class.getName()).log(Level.SEVERE, null, ex);
+        }        
+        //end changes
+        //DB.execQuery(qry);
         DB.closeConn();
     }
     //"id", "Date", "Name", "Males_qty", "Females_Qty", "Notes","AqID"

File src/nyagua/data/FishBase.java

 
 import java.awt.image.BufferedImage;
 import java.io.IOException;
+import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.logging.Level;
      */
     public static void save(FishBase specData){
         int currID=specData.id;
-
+        /*
         String qry = "";
         qry = "INSERT OR REPLACE INTO " + TABLE + " VALUES (";// NOI18N
         if (currID == 0) {
         qry = qry + specData.dhMin + "','" + specData.dhMax + "','";// NOI18N
         qry = qry + specData.tempMin + "','" + specData.tempMax + "','";// NOI18N
         qry = qry + specData.swimLevel + "'";// NOI18N
-        qry = qry + ");";// NOI18N
+        qry = qry + ");";// NOI18N*/
         DB.openConn();
-        DB.execQuery(qry);
+        try {
+            //Changes due to single quote problem
+            PreparedStatement prep=DB.getConn().prepareStatement(""// NOI18N
+                    + "INSERT OR REPLACE INTO " + TABLE + " VALUES (?, ?, ?, ?, ?, ?, ?, ?,"// NOI18N
+                    + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");// NOI18N
+            if (currID == 0) {
+                prep.setString(1, null);
+            } else {                //the record is in update
+                prep.setString(1,  String.valueOf(currID));
+            }            
+            prep.setString(2,specData.commonName);
+            prep.setString(3,specData.type);
+            prep.setString(4,specData.name);
+            prep.setString(5,specData.distribution);
+            prep.setString(6,specData.diagnosis);
+            prep.setString(7,specData.biology);
+            prep.setString(8,specData.maxSize);
+            prep.setString(9,specData.environment);
+            prep.setString(10,specData.climate);
+            prep.setString(11,specData.dangerous);
+            prep.setString(12,specData.phMin);
+            prep.setString(13,specData.phMax);
+            prep.setString(14,specData.dhMin);
+            prep.setString(15,specData.dhMax);
+            prep.setString(16,specData.tempMin);
+            prep.setString(17,specData.tempMax);  
+            prep.setString(18,specData.swimLevel);
+            prep.executeUpdate();            
+        } catch (SQLException ex) {
+            Logger.getLogger(Reading.class.getName()).log(Level.SEVERE, null, ex);
+        }        
+        //end changes
+        //DB.execQuery(qry);
         DB.closeConn();
 
         //try to save or update image

File src/nyagua/data/Maintenance.java

 
 package nyagua.data;
 
+import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.Date;
      */
     public void save(Maintenance event){
         int currID=event.id;
-
+        /*
         String qry = "";
         qry = "INSERT OR REPLACE INTO "+ TABLE + " VALUES (";// NOI18N
         if (currID == 0) {
         qry = qry + event.event + "','" + event.units + "','";// NOI18N
         qry = qry + event.notes + "','" + event.warnings + "',";// NOI18N
         qry = qry + Global.AqID;// NOI18N
-        qry = qry + ");";// NOI18N
+        qry = qry + ");";// NOI18N*/
         DB.openConn();
-        DB.execQuery(qry);
+        try {
+            //Changes due to single quote problem
+            PreparedStatement prep=DB.getConn().prepareStatement(""// NOI18N
+                    + "INSERT OR REPLACE INTO " + TABLE + " VALUES (?, ?, ?, ?, ?, ?, ?,?);");// NOI18N
+            if (currID == 0) {
+                prep.setString(1, null);
+            } else {                //the record is in update
+                prep.setString(1,  String.valueOf(currID));
+            }            
+            prep.setString(2,event.date);
+            prep.setString(3,event.time);
+            prep.setString(4,event.event);
+            prep.setString(5,event.units);
+            prep.setString(6,event.notes);
+            prep.setString(7,event.warnings);
+            prep.setInt(8,Global.AqID);
+            prep.executeUpdate();            
+        } catch (SQLException ex) {
+            Logger.getLogger(Reading.class.getName()).log(Level.SEVERE, null, ex);
+        }        
+        //end changes
+        //DB.execQuery(qry);
         DB.closeConn();
     }
 

File src/nyagua/data/Plant.java

 
 package nyagua.data;
 
+import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.Date;
      */
     public void save(Plant specimen){
         int currID=specimen.id;
-
+        /*
         String qry = "";
         qry = "INSERT OR REPLACE INTO " + TABLE + " VALUES (";// NOI18N
         if (currID == 0) {
         qry = qry + "'" + specimen.date + "','" + specimen.name + "','";// NOI18N
         qry = qry + specimen.quantity + "','" + specimen.initialStatus + "','";// NOI18N
         qry = qry + specimen.notes + "'," + Global.AqID;// NOI18N
-        qry = qry + ");";// NOI18N
+        qry = qry + ");";// NOI18N*/
         DB.openConn();
-        DB.execQuery(qry);
+        try {
+            //Changes due to single quote problem
+            PreparedStatement prep=DB.getConn().prepareStatement(""// NOI18N
+                    + "INSERT OR REPLACE INTO " + TABLE + " VALUES (?, ?, ?, ?, ?, ?, ?);");// NOI18N
+            if (currID == 0) {
+                prep.setString(1, null);
+            } else {                //the record is in update
+                prep.setString(1,  String.valueOf(currID));
+            }            
+            prep.setString(2,specimen.date);
+            prep.setString(3,specimen.name);
+            prep.setString(4,specimen.quantity);
+            prep.setString(5,specimen.initialStatus);
+            prep.setString(6,specimen.notes);
+            prep.setInt(7,Global.AqID);
+            prep.executeUpdate();            
+        } catch (SQLException ex) {
+            Logger.getLogger(Reading.class.getName()).log(Level.SEVERE, null, ex);
+        }        
+        //end changes
+        //DB.execQuery(qry);
         DB.closeConn();
     }
      

File src/nyagua/data/PlantBase.java

 
 import java.awt.image.BufferedImage;
 import java.io.IOException;
+import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.logging.Level;
      */
     public static void save(PlantBase specData){
         int currID=specData.id;
-
+        /*
         String qry = "";
         qry = "INSERT OR REPLACE INTO " + TABLE + " VALUES (";// NOI18N
         if (currID == 0) {
         qry = qry + specData.phMin + "','" + specData.phMax + "','";// NOI18N
         qry = qry + specData.dhMin + "','" + specData.dhMax + "','";// NOI18N
         qry = qry + specData.tempMin + "','" + specData.tempMax + "'";// NOI18N
-        qry = qry + ");";// NOI18N
+        qry = qry + ");";// NOI18N*/
         DB.openConn();
-        DB.execQuery(qry);
+        try {
+            //Changes due to single quote problem
+            PreparedStatement prep=DB.getConn().prepareStatement(""// NOI18N
+                    + "INSERT OR REPLACE INTO " + TABLE + " VALUES (?, ?, ?, ?, ?, ?,?,?,"// NOI18N
+                    + "?, ?, ?, ?, ?, ?,?);");// NOI18N
+            if (currID == 0) {
+                prep.setString(1, null);
+            } else {                //the record is in update
+                prep.setString(1,  String.valueOf(currID));
+            }            
+            prep.setString(2,specData.name);
+            prep.setString(3,specData.family);
+            prep.setString(4,specData.distribution);
+            prep.setString(5,specData.height);
+            prep.setString(6,specData.width);
+            prep.setString(7,specData.light);
+            prep.setString(8,specData.growth);
+            prep.setString(9,specData.demands);
+            prep.setString(10,specData.phMin);
+            prep.setString(11,specData.phMax);
+            prep.setString(12,specData.dhMin);
+            prep.setString(13,specData.dhMax);
+            prep.setString(14,specData.tempMin);
+            prep.setString(15,specData.tempMax);  
+            prep.executeUpdate();            
+        } catch (SQLException ex) {
+            Logger.getLogger(Reading.class.getName()).log(Level.SEVERE, null, ex);
+        }        
+        //end changes
+        //DB.execQuery(qry);
         DB.closeConn();
 
         //try to save or update image

File src/nyagua/data/Reading.java

 
 package nyagua.data;
 
+import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.Date;
      */
     public void save(Reading measure){
         int currID=measure.id;
-
-        String qry = "";
+        /*String qry = "";
         qry = "INSERT OR REPLACE INTO " + TABLE + " VALUES (";// NOI18N
         if (currID == 0) {
             qry = qry + "NULL,";    // NOI18N
         qry = qry + measure.co2 + "','" + measure.cond + "','";// NOI18N
         qry = qry + measure.ca + "','" + measure.mg + "','";// NOI18N
         qry = qry + measure.cu + "'," + Global.AqID;// NOI18N
-        qry = qry + ");";// NOI18N
+        qry = qry + ");";// NOI18N*/
+        //Changes due to single quote problem       
+        
+        //end changes        
         DB.openConn();
-        DB.execQuery(qry);
+        try {
+            //Changes due to single quote problem
+            PreparedStatement prep=DB.getConn().prepareStatement(""// NOI18N
+                    + "INSERT OR REPLACE INTO " + TABLE + " VALUES (?, ?, ?, ?, ?, ?,?,?,"// NOI18N
+                    + "?, ?, ?, ?, ?, ?,?,?,?);");// NOI18N
+            if (currID == 0) {
+                prep.setString(1, null);
+            } else {                //the record is in update
+                prep.setString(1,  String.valueOf(currID));
+            }            
+            prep.setString(2,measure.date);
+            prep.setString(3,measure.time);
+            prep.setString(4,measure.no2);
+            prep.setString(5,measure.no3);
+            prep.setString(6,measure.gh);
+            prep.setString(7,measure.kh);
+            prep.setString(8,measure.ph);
+            prep.setString(9,measure.temp);
+            prep.setString(10,measure.fe);
+            prep.setString(11,measure.nh);
+            prep.setString(12,measure.co2);
+            prep.setString(13,measure.cond);
+            prep.setString(14,measure.ca);
+            prep.setString(15,measure.mg);
+            prep.setString(16,measure.cu);
+            prep.setInt(17,Global.AqID);
+            prep.executeUpdate();            
+        } catch (SQLException ex) {
+            Logger.getLogger(Reading.class.getName()).log(Level.SEVERE, null, ex);
+        }        
+        //end changes
+       // DB.execQuery(qry);
         DB.closeConn();
     }