Generate MySQL INSERT Statements Programmatically

In this example, you will learn how to generate MySQL INSERT statements programmatically, eliminating the need for manual string concatenation. This approach ensures your DML statements are constructed accurately and efficiently, reducing the risk of errors.

Java Code Listing

package com.northconcepts.datapipeline.examples.cookbook;

import com.northconcepts.datapipeline.sql.mysql.Insert;

public class GenerateMySqlInsertStatementsProgrammatically {


    public static void main(String[] args) {
        Insert insert = new Insert("users")
                .add("id", 1)
                .add("name", "'John Doe'")
                .add("dateOfBirth", "'1990-01-01'")
                .add("email", "'johndoe@gmail.com'")
                .add("password_hash", "'#ag3!axx123111@'")
                .nextRow()
                .add("id", 2)
                .add("name", "'Alice Bob'")
                .add("dateOfBirth", "'1991-02-03'")
                .add("email", "'alicebob@gmail.com'")
                .add("password_hash", "'#ag3!axx123111@'")
                .nextRow()
                .add("id", 3)
                .add("name", "'Charlie Brown'")
                .add("dateOfBirth", "'1992-03-04'")
                .add("email", "'charliebrown@gmail.com'")
                .add("password_hash", "'#ag3!axx123111@'")
                ;

        insert.setPretty(true); // pretty print the generated SQL

        System.out.println(insert.getSqlFragment());
    }
}

Code Walkthrough

1. Insert instance is used to create INSERT statements for users table.
2. The add method is used to specify column-value pairs for the row to be inserted. Each add method call specifies a column name (as a string) and its corresponding value.
3. The nextRow method is called to signify that the current row of data is complete, and the following add method calls will pertain to a new row. This allows multiple data rows to be added in a single SQL INSERT statement.
4. .setPretty(true) is used to enhance the look of the output, it creates new lines where necessary which improves the readability of the output.
5. Finally, getSqlFragment() generates the actual SQL INSERT statement based on the data provided. The generated SQL string is then printed to the console.

Console Output

INSERT INTO `users` (`id`, `name`, `dateOfBirth`, `email`, `password_hash`)
VALUES (1, 'John Doe', '1990-01-01', 'johndoe@gmail.com', '#ag3!axx123111@'),
       (2, 'Alice Bob', '1991-02-03', 'alicebob@gmail.com', '#ag3!axx123111@'),
       (3, 'Charlie Brown', '1992-03-04', 'charliebrown@gmail.com', '#ag3!axx123111@')