使用Java将记录插入MySQL表 [英] Inserting records into a MySQL table using Java

问题描述

我在MySQL中用一个表创建了一个数据库:

I created a database with one table in MySQL:

CREATE DATABASE iac_enrollment_system;

USE iac_enrollment_system;

CREATE TABLE course(
    course_code CHAR(7),
    course_desc VARCHAR(255) NOT NULL,
    course_chair VARCHAR(255),
    PRIMARY KEY(course_code)
);

我尝试使用Java插入记录:

I tried to insert a record using Java:

// STEP 1: Import required packages
import java.sql.*;
import java.util.*;

public class SQLInsert {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
static final String DB_URL = "jdbc:mysql://localhost:3306/iac_enrollment_system";

// Database credentials
static final String USER = "root";
static final String PASS = "1234";

public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    Scanner scn = new Scanner(System.in);
    String course_code = null, course_desc = null, course_chair = null;

    try {
        // STEP 2: Register JDBC driver
        Class.forName("com.mysql.jdbc.Driver");

        // STEP 3: Open a connection
        System.out.print("\nConnecting to database...");
        conn = DriverManager.getConnection(DB_URL, USER, PASS);
        System.out.println(" SUCCESS!\n");

        // STEP 4: Ask for user input
        System.out.print("Enter course code: ");
        course_code = scn.nextLine();

        System.out.print("Enter course description: ");
        course_desc = scn.nextLine();

        System.out.print("Enter course chair: ");
        course_chair = scn.nextLine();

        // STEP 5: Excute query
        System.out.print("\nInserting records into table...");
        stmt = conn.createStatement();

        String sql = "INSERT INTO course " +
            "VALUES (course_code, course_desc, course_chair)";
        stmt.executeUpdate(sql);

        System.out.println(" SUCCESS!\n");

    } catch(SQLException se) {
        se.printStackTrace();
    } catch(Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if(stmt != null)
                conn.close();
        } catch(SQLException se) {
        }
        try {
            if(conn != null)
                conn.close();
        } catch(SQLException se) {
            se.printStackTrace();
        }
    }
    System.out.println("Thank you for your patronage!");
  }
}

输出似乎成功返回:

The output appears to return successfully:

但是当我从MySQL中选择时，插入的记录为空:

But when I select from MySQL, the inserted record is blank:

为什么要插入空白记录?

Why is it inserting a blank record?

推荐答案

不行(不适用于真实数据):

no that cannot work(not with real data):

String sql = "INSERT INTO course " +
        "VALUES (course_code, course_desc, course_chair)";
    stmt.executeUpdate(sql);

将其更改为:

String sql = "INSERT INTO course (course_code, course_desc, course_chair)" +
        "VALUES (?, ?, ?)";

使用该sql创建PreparedStatment并插入带有索引的值:

Create a PreparedStatment with that sql and insert the values with index:

PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, "Test");
preparedStatement.setString(2, "Test2");
preparedStatement.setString(3, "Test3");
preparedStatement.executeUpdate(); 

这篇关于使用Java将记录插入MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！