Is it possible to insert records to MySQL with different parameter (keys & values) each time using the same insert function in Java?

Question

I am trying to find out if it is possible to use the same insert function each time with new different parameters in java 8 & MySQL. I know it is possible in PHP as I have the PHP code below :

this is the array records:

$Customer_details=[
             'firstname' => $Firstname,
             'lastname'=> $lastname,
             'phone'  => $phone,
             'email'  => $email,
             'password'=> $hash

  ];

After created the class object which is $object2 this is the records:

$object2->insert($pdo,'customer',$Customer_details);

The following code is the PHP insert function code:

function insert( $pdo, $table, $record) {
$keys = array_keys( $record);
$values = implode( ', ' , $keys);
$valuesWithColon = implode( ', :' , $keys);
$query = 'INSERT INTO '  . $table . ' ('  . $values . ') VALUES (:'  . 
$valuesWithColon . ')' ;
$stmt = $pdo->prepare( $query);
$stmt->execute( $record);
return $stmt;
}

Can anybody just show me an example of the same thing in java, Please?

Answer 1

Yes, you can do that in much the same way you would achieve it with php, as @lexicore commented, a common pattern for parameterised sql queries in Java is to use PreparedStatements

Here is an example.. you would just need to wrap the code in that example in a function to make it reusable like your php example.. https://alvinalexander.com/java/java-mysql-insert-example-preparedstatement

public void insert(String fname, String lname, Date created, boolean isAdmin, int points) {
    String myDriver = "org.gjt.mm.mysql.Driver";
    String myUrl = "jdbc:mysql://localhost/test";
    Class.forName(myDriver);
    Connection conn = DriverManager.getConnection(myUrl, "root", "");

    // create a sql date object so we can use it in our INSERT statement
    Calendar calendar = Calendar.getInstance();
    java.sql.Date startDate = new java.sql.Date(calendar.getTime().getTime());

    // the mysql insert statement
    String query = " insert into users (first_name, last_name, date_created, is_admin, num_points)"
      + " values (?, ?, ?, ?, ?)";

    // create the mysql insert preparedstatement
    PreparedStatement preparedStmt = conn.prepareStatement(query);
    preparedStmt.setString (1, fname);
    preparedStmt.setString (2, lname);
    preparedStmt.setDate   (3, created);
    preparedStmt.setBoolean(4, isAdmin);
    preparedStmt.setInt    (5, points);

    // execute the preparedstatement
    preparedStmt.execute();

    conn.close();
}

Answer 2

I wrote this about a year ago, but I think this is what your looking for.

Ibelieve youwant to changeValuesto List<Object and use a switch statement with instanceOfto parse through that.

private String insert(String tableName, List<String> columns, List<String> values){
    StringBuilder stringBuilder = new StringBuilder("INSERT INTO " + tableName + " (");
    for(String column :columns){
        stringBuilder.append(column + ",");
    }
    stringBuilder.deleteCharAt(stringBuilder.lastIndexOf(","));
    stringBuilder.append(") VALUES (");
    for(String value : values){
        stringBuilder.append(value + ",");
    }
    stringBuilder.deleteCharAt(stringBuilder.lastIndexOf(","));
    stringBuilder.append(")");
    return stringBuilder.toString();
}

Of as previously mention use this and make the second loop:

for(int z =0; z < columns.size(); z++){
    stringBuilder.append("?,");
}

To use with PreparedStatements knowing what I know now, I would choose the latter.