java - 使用java将记录插入mysql

Question

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;
  }
} 

我正在尝试将数据插入到 mysql 数据库中，我得到了一些值，但有一个异常(exception)说:Field 'd_f' 没有默认值。在堆栈跟踪中获取以下输出:->

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

我的 Insert 语句是否有误或格式错误？

Answer 1

这正是错误所说的。您的表中有一个名为 d_f 的字段，但您没有使用代码生成的语句向该列插入任何内容。

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

由于没有为该列指定默认值，数据库不知道该怎么做并返回此错误。

您有两个选择，改变您的表以创建默认值或按如下方式传递一些默认值

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

第三个选项是按照@chris569 和@tim-biegeleisen 所建议的那样修改表以允许空值，在数据库级别而不是在您的 Java 代码中执行此操作确实更好。