CODEWITHSUNDEEP
javamysqljdbc
joe
joe

Reputation: 1

Inserting records into mysql using java

package Simple;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;


public class CheckJdbc {

  private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
  private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/db";

  private static final String DB_USER = "root";
  private static final String DB_PASSWORD = "root";

  private static int RECORD_COUNT = 1;
  static final String DATEFORMAT = "yyyy-MM-dd HH:mm:ss";
  static final String DATE_FORMAT = "yyyy-MM-dd";
  static final String TIME_FORMAT = "HH:mm:ss";
  private static final int ADD_MINUTES = 2;
  static final String FromDate = "2016-01-01 00:00:00";

  @SuppressWarnings("unused")
public static void main(String[] args) throws Exception {

    List<String> records = new ArrayList<String>();
    StringBuffer record = new StringBuffer();
    DateFormat d_f = new SimpleDateFormat(DATE_FORMAT);
    @SuppressWarnings("unused")
    DateFormat tf = new SimpleDateFormat(TIME_FORMAT);
    Calendar cal = Calendar.getInstance();
    cal.setTime(d_f.parse(FromDate));
   // record.append("\t");
    for (int i = 1; i <= RECORD_COUNT; i++) {
        records = new ArrayList<String>(RECORD_COUNT);
      }
      for (int j = 0; j < 5; j++) {

          int a2 = 230 + j % 15; // 230 - 244 by 1
          String wString = Integer.toString(a2);
          String a = String.valueOf(a2);
          record.append(a+" ");            
          double b2 = 1.3 + j % 17 ; // 1.3 - 2.9 by 0.1 
          String aString = Double.toString(b2);
          String b = String.valueOf(b2);
          record.append(b+" ");  
          double c2 = 0.01 + j % 49 * 0.01; // 0.01 - 0.49 by 0.01
          String bString = Double.toString(c2);
          String c = String.valueOf(c2);
          record.append(c+" ");         

      record.append((d_f.format(cal.getTime()))+" "+tf.format(cal.getTime())+" ");

        record.delete(0, record.length());
        record.append(a + "," + b + "," + c );
        record.append("\t\t");

        record.append("\n");
        cal.add(Calendar.MINUTE, ADD_MINUTES);
        records.add(record.toString());

    try {
        String insertTableSQL = "INSERT INTO cmd"
                + "(a, b, c) " + "VALUES"
                + "("+record.toString()+")";
            System.out.println("insertTableSQL - " + insertTableSQL);     
            Statement.executeUpdate(insertTableSQL);

      insertRecordIntodb();
      Connection dbConnection = null;
      Statement statement = null;
      dbConnection = getDBConnection();
      statement = dbConnection.createStatement();
      System.out.println(insertTableSQL);
      // execute insert SQL stetement
      statement.executeUpdate(insertTableSQL);
      System.out.println(insertTableSQL);
      System.out.println("Record is inserted into Db table!");
    } catch (SQLException e) {
      System.out.println(e.getMessage());
    } finally {
    }
  }
    }

  private static void insertRecordIntodb() {

  }

  private static Connection getDBConnection() {
    Connection dbConnection = null;
    try {
      Class.forName(DB_DRIVER);
    } catch (ClassNotFoundException e) {
      System.out.println(e.getMessage());
    }
    try {
      dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
      return dbConnection;
    } catch (SQLException e) {
      System.out.println(e.getMessage());
    }
    return dbConnection;
  }
}

I am trying to insert data into mysql db i am getting some of values but there is exception saying that :Field 'd_f' doesn't have a default value.Getting following output in stack trace:->

INSERT INTO cmd(a, b, c) VALUES(233,4.3,0.040)
    Field 'd_f' doesn't have a default value

Is My Insert statement wrong or format?

Upvotes: 0

Views: 280

Answers (4)

Abubakkar
Abubakkar

Reputation: 15664

As mentioned in you comment, d_f is a column in your table.

So I think, it is being defined as Not-Nullable in your table without a default value.

Two possible solutions :

  1. If you want to keep that field as optional while doing an insert, make it nullable in your table
  2. If you want it to be not null, give it a default value (either at code or database level) .

Upvotes: 2

Chit Khine
Chit Khine

Reputation: 850

Your query might have a problem. Why don't u try by creating a prepared statement instead as below.

    `String a = "somevalue";
String b = "somevalue";

String c = "somevalue";

String insertTableSQL = "INSERT INTO cmd"
                    + "(a, b, c) " + "VALUES"
                    + "(?, ?, ?)";
                System.out.println("insertTableSQL - " + insertTableSQL);    
 Statement.executeUpdate(insertTableSQL,a,b,c); `

Upvotes: 0

gshri1986
gshri1986

Reputation: 68

It seems like 'd_f' is a column in your table, which is not nullable,

There are three possible ways to resolve this issue

  1. Make that column nullable
  2. Provide some default value to that column e.g current timestamp
  3. Put some value for that column while inserting records

Upvotes: 0

e4c5
e4c5

Reputation: 53774

It's exactly what the error says. you have a field called d_f in your table but you are not inserting anything into that column with this statement produced by your code.

INSERT INTO cmd(a, b, c) VALUES(233,4.3,0.040)

Since a default value has not been specied for that column the database doesn't know what to do and returns this error.

You have two options, alter your table to create a default value or pass in some default value as follows

INSERT INTO cmd(a, b, c, d_f) VALUES(233,4.3,0.040,'some default')

A third option is to modify the table to allow nulls as suggested by @chris569 and as @tim-biegeleisen points out, it is indeed better to do this at the database level rather than in your java code.

Upvotes: 4

Related Questions