import ballerina/jdbc;
import ballerina/io;
import ballerina/mysql;

endpoint mysql:Client testDB {
    host: "localhost",
    port: 3306,
    name: "customerdb",
    username: "root",
    password: "123",
    poolOptions: { maximumPoolSize: 5 },
    dbOptions: { useSSL: false }
};

public type CustomerRecord record {
    int id,
    string name,
};


function main(string... args) {
    // We first create connect to the database and create a database table
    var ret = testDB->update("CREATE TABLE IF NOT EXISTS Customer (ID INT, NAME VARCHAR(30))");

    match ret {
        int retInt => io:println("Table creation status: " + retInt);
        error err => io:println(err.message);
    }

   // Now let's obtain a 'proxy' table to the table we just created. Here you need to pass the name of the actual table,
   // and the Ballerina record type that a row of the database table could be mapped with.
    var proxyRet = testDB->getProxyTable("Customer", CustomerRecord);

    table<CustomerRecord> tbProxy;
    match proxyRet {
        table tbReturned => tbProxy = tbReturned;
        error err => io:println("Proxy table retrieval failed: " + err.message);
    }

    // Now we create a record and insert it to the database table through the proxy table we obtained.
    CustomerRecord cr1 = { id: 1, name: "Tom" };
    CustomerRecord cr2 = { id: 2, name: "Will" };
    _ = tbProxy.add(cr1);
    _ = tbProxy.add(cr2);

    // Now let's iterate the database table through our proxy table.
    foreach entry in tbProxy {
        io:println(entry.id + "|" + entry.name);
    }

    testDB.stop();
}