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");

    }

score 2 · Answer 1 · edited Sep 27 '17 at 20:35

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 '17 at 20:35

Graham

7,431
18
59
84

answered Apr 15 '17 at 10:31

Youcef LAIDANI

55,661
15
90
140

I agree, but here i need all the values in string variable first .I would we using prepared statement later. – Sneha P Apr 15 '17 at 10:35
so is there no way to assign NULL to variable string ans? – Sneha P Apr 15 '17 at 10:36
@SnehaP what purpose to use null in your id? – Youcef LAIDANI Apr 15 '17 at 10:40
what type of `id` int, varchar, or real or what? @SnehaP – Youcef LAIDANI Apr 15 '17 at 10:58

score 0 · Answer 2 · answered Apr 15 '17 at 10:27

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 '17 at 10:27

davidxxx

125,838
23
214
215

score 0 · Answer 3 · answered Apr 15 '17 at 10:29

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.

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

3 Answers3