CODEWITHSUNDEEP
javamysqlsqljdbc
Skib Rs
Skib Rs

Reputation: 141

Data writing error

I'm inserting some data into a mySql database. For some unknown reason the data is not being inserted into my table. This worked fine with SQLITE. Here is my code:

try {

            Class.forName("com.mysql.jdbc.Driver");

              Connection conn = DriverManager.getConnection(url, username, password);



            String query1 = "insert into Project (uniqueid,name,address,startingdate,estcompletion,engname) values(?,?,?,?,?,?)";
            String query2 = "insert into EngineerData (UniqueId,Name,Password) values(?,?,?)";
            String query3 = "insert into " +tableName +" (UniqueId,Category,Item,EstimatedQuantity,Unit,UnitPrice,TotalPrice,TotalSpent) values(?,?,?,?,?,?,?,?)";
            String query4 = "insert into ProjectImages (uniqueId, Path) values(?,?)";

            PreparedStatement pst1=  conn.prepareStatement(query1);
            PreparedStatement pst2 = conn.prepareStatement(query2);
            PreparedStatement pst3 = conn.prepareStatement(query3);
            PreparedStatement pst4 = conn.prepareStatement(query4);

            pst1.setInt(1, uniqueID);
            pst1.setString(2, projectName.getText());
            pst1.setString(3, address.getText());
            pst1.setString(4, day1.getText()+"/"+month1.getText()+"/"+year1.getText());
            pst1.setString(5, day2.getText()+"/"+month2.getText()+"/"+year2.getText());
            pst1.setString(6, engineerName.getText());

            pst2.setInt(1, uniqueID);
            pst2.setString(2, engineerName.getText());
            pst2.setString(3, engineerPassword.getText());

            try{
            for (int j = 0; j < table.getRowCount(); j++ ){

                pst3.setInt(1, uniqueID);
                pst3.setString(2, table.getValueAt(j, 0).toString());
                pst3.setString(3, table.getValueAt(j, 1).toString());
                pst3.setString(4, table.getValueAt(j, 2).toString());
                pst3.setString(5, table.getValueAt(j, 3).toString());
                pst3.setString(6, table.getValueAt(j, 4).toString());
                pst3.setString(7, table.getValueAt(j, 5).toString());

                pst3.setDouble(8, 0.0);
                pst3.execute();

            }}catch(Exception e3){
                /*JOptionPane.showMessageDialog(null, e3);
                 * 
                 */
            }

            pst4.setInt(1, uniqueID);
            pst4.setString(2, null);

            pst1.execute();
            pst2.execute();
            pst4.execute();

            System.out.println(pst1.toString());
            System.out.println(pst2.toString());

            pst1.close();
            pst2.close();
            pst3.close();
            pst4.close();



            conn.close();  

            }

Upvotes: 2

Views: 77

Answers (2)

m c
m c

Reputation: 1104

Without the stack trace it might be difficult to get where this is not working. It might be a INDEX constraint exception, a null pointer exception, ... But, I'd suggest to format differently the date, instead of

day1.getText()+"/"+month1.getText()+"/"+year1.getText()

I'd use

year1.getText()+"-"+month1.getText()+"-"+day1.getText()

(check the MySQL documentation and especially the STR_TO_DATE() function, but you probably don't need it).

And also there's room to improve your code (following Rajesh's comment):

  • While probably you're using Java 7 or above, I'd add the try-with statement for the connection;
  • Catching Exception is generally bad practice, I'd go for a fine-grain try-catch with SQLException, ...
  • I can't see any transaction strategy in place. I'd be worried while some of the pst?.execute() works fine and another doesn't, leaving your database in an inconsistent state.

Upvotes: 0

Rajesh
Rajesh

Reputation: 2155

You should leave out auto generated column "uniqueID" and rewrite your code. Ex.

String query4 = "insert into ProjectImages (Path) values(?)";

Another approach - passing null value for auto generated column. Ex.

String query4 = "insert into ProjectImages (uniqueId, Path) values(null,?)";

Upvotes: 2

Related Questions