How to assign NULL value to a string and then insert it in mysql database

Question

Here I'am calculating the average of one column If the avg comes to be 0.0, then i would want to assign the string variable as NULL else the avg value itself.

These values are getting stored in mysql db Now my problem is that when the avg does come 0.0, string NULL gets stored but i want the default NULL value to get stored in it.

How can i assign NULL (and not string NULL) to variable ans??

private void btnAdd1ActionPerformed(java.awt.event.ActionEvent evt) {                                        
    // TODO add your handling code here:
     Connection conn = null;
            Statement st = null;          
            ResultSet rs = null;

        try{

  conn = DriverManager.getConnection("jdbc:mysql://localhost/check","root","");
    st = conn.createStatement();

    String sql4 =("SELECT AVG(unit4) as num FROM lo where unit4 IS NOT NULL");
         PreparedStatement pstmt3 = conn.prepareStatement(sql4);
         ResultSet rs4 = pstmt3.executeQuery();
     rs4.next();
     double a3 = rs4.getDouble("num");
     double b3 = Math.round(a3*10);
     double res5 = b3/10;
     rs4.next();
     avg1.setText(String.valueOf(res5));
     String a1 =avg1.getText();
     String ans ;

    if(a1.equals("0.0")){
        ans = null;
    }else{
        ans = a1;
    }

    String query = "INSERT INTO chk(id) VALUES ('"+ans+"')";
    executeSQlQuery(query, "Inserted");

    }

Graham · Accepted Answer · 2017-09-27 20:35:19Z

2

You can use setNull() and to avoid any syntax error or SQL Injection you have to use PreparedStatement instead :

PreparedStatement ps = connection.prepareStatement("INSERT INTO chk(id) VALUES (?)");
ps.setNull(1, java.sql.Types.VARCHAR);
ps.executeUpdate();

Note

Something wired in your program, why you are using rs4.next(); what if your result is empty, i think you need something like this instead :

if (rs4.next()) {//<<<--------------------
    double a3 = rs4.getDouble("num");
    //-----------^^-----------------
}

edited Sep 27, 2017 at 20:35

Graham

7,86020 gold badges67 silver badges92 bronze badges

answered Apr 15, 2017 at 10:31

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sneha P Over a year ago

I agree, but here i need all the values in string variable first .I would we using prepared statement later.

Sneha P Over a year ago

so is there no way to assign NULL to variable string ans?

Youcef LAIDANI Over a year ago

@SnehaP what purpose to use null in your id?

Youcef LAIDANI Over a year ago

what type of id int, varchar, or real or what? @SnehaP

davidxxx · Accepted Answer · 2017-04-15 10:27:42Z

0

You have not enclose ans between quotes to handle the NULL case.
Otherwise, indeed you insert "NULL" String :

String query = "INSERT INTO chk(id) VALUES ('"+ans+"')";

You can try :

String query = "INSERT INTO chk(id) VALUES ("+ ans +")";

answered Apr 15, 2017 at 10:27

davidxxx

132k23 gold badges231 silver badges228 bronze badges

Comments

Kayaman · Accepted Answer · 2017-04-15 10:29:51Z

0

Your code is pretty suboptimal, since you're not even using a PreparedStatement. Since your query contains the '' markers, it will always insert a string in the database. In the case of null you'll just be inserting 'null' which isn't what you want.

Now, this is not a good solution, but it's the quickest for you. Modify the query to "INSERT INTO chk(id) VALUES ("+ans+")", and add the following before executing the query:

if(ans != null)
    ans = "'" + ans + "'";

Remember, this is bad code. It's not the standard way. It has security issues. Learn to use a PreparedStatement.

answered Apr 15, 2017 at 10:29

Kayaman

73.8k6 gold badges91 silver badges128 bronze badges

Collectives™ on Stack Overflow

How to assign NULL value to a string and then insert it in mysql database

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related