Inserting records into a MySQL table using Java

Question

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

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:

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

Why is it inserting a blank record?

Apennines

You are not using course_*** variables in query. – Largo

You aren't setting any values on the insert statement. Check out using a preparedStatement with bind variables rather than making up a "string" insert statement. This will insert the data and will stop you getting SQL injection issues. – Clear

Answer 1

no that cannot work(not with real data):

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

change it to:

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

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

Zita

Hi, I tried changing the part of the code into this. But it is returning cannot find symbol for variable prepareStatement. – Apennines

of couse you have to create it before using. – Zita

PreparedStatement preparedStatement = conn.prepareStatement(sql); – Zita

Hi, sorry if I am new to this. I have changed the code. Here is my pastebin. It is throwing a Can not issue data manipulation statements with executeQuery(). – Apennines

Answer 2

this can also be done like this if you don't want to use prepared statements.

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

Why it didnt insert value is because you were not providing values, but you were providing names of variables that you have used.

Uttasta

You should always use preparedStatements. This type of code is bad practice and will lead to SQL injection issues. – Clear

Aayush, I disagree. You shouldn't learn bad habits as they will stick. Learn to use preparedStatements from the beginning and you will write better code. – Clear

I am not asking to learn this thing and use it, I mentioned it because its a feature and one should know about this plus this solves his answer too. – Uttasta

I personally don't use this anymore, I use ORM framework like hibernate. – Uttasta

I have opted to use the PreparedStatement approach. Still, your explanation on why it didn't insert values was helpful. Upvote for that. Thank you. – Apennines

Answer 3

This should work for any table, instead of hard-coding the columns.

//Source details
    String sourceUrl = "jdbc:oracle:thin:@//server:1521/db";
    String sourceUserName = "src";
    String sourcePassword = "***";

    // Destination details
    String destinationUserName = "dest";
    String destinationPassword = "***";
    String destinationUrl = "jdbc:mysql://server:3306/db";

    Connection srcConnection = getSourceConnection(sourceUrl, sourceUserName, sourcePassword);
    Connection destConnection = getDestinationConnection(destinationUrl, destinationUserName, destinationPassword);

    PreparedStatement sourceStatement = srcConnection.prepareStatement("SELECT *  FROM src_table ");
    ResultSet rs = sourceStatement.executeQuery();
    rs.setFetchSize(1000); // not needed


    ResultSetMetaData meta = rs.getMetaData();



    List<String> columns = new ArrayList<>();
    for (int i = 1; i <= meta.getColumnCount(); i++)
        columns.add(meta.getColumnName(i));

    try (PreparedStatement destStatement = destConnection.prepareStatement(
            "INSERT INTO dest_table ("
                    + columns.stream().collect(Collectors.joining(", "))
                    + ") VALUES ("
                    + columns.stream().map(c -> "?").collect(Collectors.joining(", "))
                    + ")"
            )
    )
    {
        int count = 0;
        while (rs.next()) {
            for (int i = 1; i <= meta.getColumnCount(); i++) {
                destStatement.setObject(i, rs.getObject(i));
            }
            
            destStatement.addBatch();
            count++;
        }
        destStatement.executeBatch(); // you will see all the rows in dest once this statement is executed
        System.out.println("done " + count);

    }

Newsworthy

Answer 4

There is a mistake in your insert statement chage it to below and try : String sql = "insert into table_name values ('" + Col1 +"','" + Col2 + "','" + Col3 + "')";

Oglethorpe

While this also solves the problem, I have opted to use the PreparedStatement approach. Thank you for your help. – Apennines