JDBC MySQL Functions

Overview

The JDBC module is used to operate MySQL, Oracle, and other databases
The JDBC module uses the jdbc prefix, e.g. jdbc.init()

jdbc.init Initialize JDBC Connection

Initialize JDBC connection
@param jdbcDriver JDBC driver; for MySQL use: com.mysql.jdbc.Driver
@param dbUrl JDBC connection URL, e.g. jdbc:mysql://{ip}:{port}/{db}?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
@param user database username
@param password database password
@return {bool} true on success, false on failure

1
function main() {
2
    // MySQL address ip:port/database name
3
    let mysqlUrl = "jdbc:mysql://192.168.0.3:3306/test?characterEncoding=utf8&autoReconnect=true"
4
    let inited = jdbc.init("com.mysql.cj.jdbc.Driver", mysqlUrl, "root", "root123456");
5
    logd("inited " + inited);
6
    let conn = jdbc.connect()
7
    logd("connect " + conn);
8
    if (!conn) {
9
        logd(jdbc.getLastError());
10
        exit()
11
    }
12
    // Query statement
13
    let q = "Select * from table1 where id=1"
14
    let qur = jdbc.query(q)
15
    logd(qur);
16
    jdbc.connectionClose()
17
}
18

19
main();

jdbc.getLastError Get Last Error

Get the most recent error
@return {string} error string; null means no error

1
function main() {
2
    // MySQL address ip:port/database name
3
    let mysqlUrl = "jdbc:mysql://192.168.0.3:3306/test?characterEncoding=utf8&autoReconnect=true"
4
    let inited = jdbc.init("com.mysql.cj.jdbc.Driver", mysqlUrl, "root", "root123456");
5
    logd("inited " + inited);
6
    let conn = jdbc.connect()
7
    logd("connect " + conn);
8
    if (!conn) {
9
        logd(jdbc.getLastError());
10
        exit()
11
    }
12
    // Query statement
13
    let q = "Select * from table1 where id=1"
14
    let qur = jdbc.query(q)
15
    logd(qur);
16
    jdbc.connectionClose()
17
}
18

19
main();

jdbc.connect Connect to Database

Connect to the database; call after init()
@return {bool} true on success, false on failure

1
function main() {
2
    // MySQL address ip:port/database name
3
    let mysqlUrl = "jdbc:mysql://192.168.0.3:3306/test?characterEncoding=utf8&autoReconnect=true"
4
    let inited = jdbc.init("com.mysql.cj.jdbc.Driver", mysqlUrl, "root", "root123456");
5
    logd("inited " + inited);
6
    // Connect to database
7
    let conn = jdbc.connect()
8
    logd("connect " + conn);
9
    if (!conn) {
10
        logd(jdbc.getLastError());
11
        exit()
12
    }
13
    // Query statement
14
    let q = "Select * from table1 where id=1"
15
    let qur = jdbc.query(q)
16
    logd(qur);
17
    jdbc.connectionClose()
18
}
19

20
main();

jdbc.query Query Data

Query data
@param sql SQL statement
@return JSON | data collection object

1
function main() {
2
    // MySQL address ip:port/database name
3
    let mysqlUrl = "jdbc:mysql://192.168.0.3:3306/test?characterEncoding=utf8&autoReconnect=true"
4
    let inited = jdbc.init("com.mysql.cj.jdbc.Driver", mysqlUrl, "root", "root123456");
5
    logd("inited " + inited);
6
    let conn = jdbc.connect()
7
    logd("connect " + conn);
8
    if (!conn) {
9
        logd(jdbc.getLastError());
10
        exit()
11
    }
12
    // Query statement
13
    let q = "select * from table1 where id=1"
14
    let qur = jdbc.query(q)
15
    logd(qur);
16
    jdbc.connectionClose()
17
}
18

19
main();

jdbc.createPreparedStatement Create Prepared Statement

Create a prepared SQL statement
@param sql prepared statement SQL
@return {bool} true on success, false on failure

1
function main() {
2
    // MySQL address ip:port/database name
3
    let mysqlUrl = "jdbc:mysql://192.168.0.3:3306/test?characterEncoding=utf8&autoReconnect=true"
4
    let inited = jdbc.init("com.mysql.cj.jdbc.Driver", mysqlUrl, "root", "root123456");
5
    logd("inited " + inited);
6

7
    let conn = jdbc.connect()
8
    logd("connect " + conn);
9
    if (!conn) {
10
        logd(jdbc.getLastError());
11
        exit()
12
    }
13

14
    // Query statement
15
    let q = "select * from table1 where id=? or uname=?"
16
    // Create a query
17
    let qur = jdbc.createPreparedStatement(q)
18
    if (qur) {
19
        // Set the first indexed parameter
20
        jdbc.psqlSetInt(1, 1)
21
        // Set the second indexed parameter
22
        jdbc.psqlSetString(2, 'test')
23

24
    }
25
    // Prepared statement query
26
    let data = jdbc.psqlQuery()
27
    logd(data);
28

29
    // Close prepared statement
30
    jdbc.psqlClose()
31

32
    // Insert data
33
    q = "insert table1(`uname`,`ucontent`,`create_time`)values(?,?,?);"
34
    qur = jdbc.createPreparedStatement(q)
35
    if (qur) {
36
        // Set the first indexed parameter
37
        jdbc.psqlSetString(1, "我是名称")
38
        // Set the second indexed parameter
39
        jdbc.psqlSetString(2, '我是内容')
40
        // Set timestamp
41
        jdbc.psqlSetTimestamp(3, "yyyy-MM-dd hh:mm:ss", "2020-10-02 12:02:11")
42
    }
43
    rowcount = jdbc.psqlExecuteUpdate();
44
    logi("Insert affected row count -" + rowcount);
45
    if (rowcount <= 0) {
46
        loge("Insert error: " + jdbc.getLastError())
47
    }
48
    jdbc.connectionClose()
49
}
50

51
main();

jdbc.psqlQuery Execute Prepared Statement

Execute the previously created prepared statement
@return {string} JSON string

1
function main() {
2
    // MySQL address ip:port/database name
3
    let mysqlUrl = "jdbc:mysql://192.168.0.3:3306/test?characterEncoding=utf8&autoReconnect=true"
4
    let inited = jdbc.init("com.mysql.cj.jdbc.Driver", mysqlUrl, "root", "root123456");
5
    logd("inited " + inited);
6

7
    let conn = jdbc.connect()
8
    logd("connect " + conn);
9
    if (!conn) {
10
        logd(jdbc.getLastError());
11
        exit()
12
    }
13

14
    // Query statement
15
    let q = "select * from table1 where id=? or uname=?"
16
    // Create a query
17
    let qur = jdbc.createPreparedStatement(q)
18
    if (qur) {
19
        // Set the first indexed parameter
20
        jdbc.psqlSetInt(1, 1)
21
        // Set the second indexed parameter
22
        jdbc.psqlSetString(2, 'test')
23

24
    }
25
    // Prepared statement query
26
    let data = jdbc.psqlQuery()
27
    logd(data);
28

29
    // Close prepared statement
30
    jdbc.psqlClose()
31
    // Insert data
32
    q = "insert table1(`uname`,`ucontent`,`create_time`)values(?,?,?);"
33
    qur = jdbc.createPreparedStatement(q)
34
    if (qur) {
35
        // Set the first indexed parameter
36
        jdbc.psqlSetString(1, "我是名称")
37
        // Set the second indexed parameter
38
        jdbc.psqlSetString(2, '我是内容')
39
        // Set timestamp
40
        jdbc.psqlSetTimestamp(3, "yyyy-MM-dd hh:mm:ss", "2020-10-02 12:02:11")
41
    }
42
    rowcount = jdbc.psqlExecuteUpdate();
43
    logi("Insert affected row count -" + rowcount);
44
    if (rowcount <= 0) {
45
        loge("Insert error: " + jdbc.getLastError())
46
    }
47
    jdbc.connectionClose()
48
}
49

50
main();

jdbc.psqlSetString Set String Parameter

Set a string parameter on the prepared statement
@param index parameter index
@param input string value
@return {bool} true on success, false on failure

1
function main() {
2
    // MySQL address ip:port/database name
3
    let mysqlUrl = "jdbc:mysql://192.168.0.3:3306/test?characterEncoding=utf8&autoReconnect=true"
4
    let inited = jdbc.init("com.mysql.cj.jdbc.Driver", mysqlUrl, "root", "root123456");
5
    logd("inited " + inited);
6

7
    let conn = jdbc.connect()
8
    logd("connect " + conn);
9
    if (!conn) {
10
        logd(jdbc.getLastError());
11
        exit()
12
    }
13

14
    // Query statement
15
    let q = "select * from table1 where id=? or uname=?"
16
    // Create a query
17
    let qur = jdbc.createPreparedStatement(q)
18
    if (qur) {
19
        // Set the first indexed parameter
20
        jdbc.psqlSetInt(1, 1)
21
        // Set the second indexed parameter
22
        jdbc.psqlSetString(2, 'test')
23

24
    }
25
    // Prepared statement query
26
    let data = jdbc.psqlQuery()
27
    logd(data);
28

29
    // Close prepared statement
30
    jdbc.psqlClose()
31

32
    // Insert data
33
    q = "insert table1(`uname`,`ucontent`,`create_time`)values(?,?,?);"
34
    qur = jdbc.createPreparedStatement(q)
35
    if (qur) {
36
        // Set the first indexed parameter
37
        jdbc.psqlSetString(1, "我是名称")
38
        // Set the second indexed parameter
39
        jdbc.psqlSetString(2, '我是内容')
40
        // Set timestamp
41
        jdbc.psqlSetTimestamp(3, "yyyy-MM-dd hh:mm:ss", "2020-10-02 12:02:11")
42
    }
43
    rowcount = jdbc.psqlExecuteUpdate();
44
    logi("Insert affected row count -" + rowcount);
45
    if (rowcount <= 0) {
46
        loge("Insert error: " + jdbc.getLastError())
47
    }
48
    jdbc.connectionClose()
49
}
50

51
main();

jdbc.psqlSetLong Set long Parameter

Set a long parameter on the prepared statement
@param index parameter index
@param input long value
@return {bool} true on success, false on failure

1
function main() {
2
    jdbc.psqlSetLong(1, 1000);
3
}
4

5
main();

jdbc.psqlSetInt Set int Parameter

@param index parameter index
@param input int value
@return {bool} true on success, false on failure

1
function main() {
2
    // MySQL address ip:port/database name
3
    let mysqlUrl = "jdbc:mysql://192.168.0.3:3306/test?characterEncoding=utf8&autoReconnect=true"
4
    let inited = jdbc.init("com.mysql.cj.jdbc.Driver", mysqlUrl, "root", "root123456");
5
    logd("inited " + inited);
6

7
    let conn = jdbc.connect()
8
    logd("connect " + conn);
9
    if (!conn) {
10
        logd(jdbc.getLastError());
11
        exit()
12
    }
13

14
    // Query statement
15
    let q = "select * from table1 where id=? or uname=?"
16
    // Create a query
17
    let qur = jdbc.createPreparedStatement(q)
18
    if (qur) {
19
        // Set the first indexed parameter
20
        jdbc.psqlSetInt(1, 1)
21
        // Set the second indexed parameter
22
        jdbc.psqlSetString(2, 'test')
23

24
    }
25
    // Prepared statement query
26
    let data = jdbc.psqlQuery()
27
    logd(data);
28

29
    // Close prepared statement
30
    jdbc.psqlClose()
31

32
    // Insert data
33
    q = "insert table1(`uname`,`ucontent`,`create_time`)values(?,?,?);"
34
    qur = jdbc.createPreparedStatement(q)
35
    if (qur) {
36
        // Set the first indexed parameter
37
        jdbc.psqlSetString(1, "我是名称")
38
        // Set the second indexed parameter
39
        jdbc.psqlSetString(2, '我是内容')
40
        // Set timestamp
41
        jdbc.psqlSetTimestamp(3, "yyyy-MM-dd hh:mm:ss", "2020-10-02 12:02:11")
42
    }
43
    rowcount = jdbc.psqlExecuteUpdate();
44
    logi("Insert affected row count -" + rowcount);
45
    if (rowcount <= 0) {
46
        loge("Insert error: " + jdbc.getLastError())
47
    }
48
    jdbc.connectionClose()
49
}
50

51
main();

jdbc.psqlSetFloat Set float Parameter

Set a float parameter on the prepared statement
@param index parameter index
@param input float value
@return {bool} true on success, false on failure

1
function main() {
2
    jdbc.psqlSetFloat(1, 1.0)
3
}
4

5
main();

jdbc.psqlSetBoolean Set boolean Parameter

Set a boolean parameter
@param index parameter index
@param input boolean value
@return {bool} true on success, false on failure

1
function main() {
2
    jdbc.psqlSetBoolean(1, true)
3
}
4

5
main();

jdbc.psqlSetDate Set Date Parameter

Set a date parameter on the prepared statement
@param index parameter index
@param dataFormat date format, e.g. yyyy-MM-dd
@param input date string
@return {bool} true on success, false on failure

1
function main() {
2
    // MySQL address ip:port/database name
3
    let mysqlUrl = "jdbc:mysql://192.168.0.3:3306/test?characterEncoding=utf8&autoReconnect=true"
4
    let inited = jdbc.init("com.mysql.cj.jdbc.Driver", mysqlUrl, "root", "root123456");
5
    logd("inited " + inited);
6

7
    let conn = jdbc.connect()
8
    logd("connect " + conn);
9
    if (!conn) {
10
        logd(jdbc.getLastError());
11
        exit()
12
    }
13

14
    // Query statement
15
    let q = "select * from table1 where id=? or uname=?"
16
    // Create a query
17
    let qur = jdbc.createPreparedStatement(q)
18
    if (qur) {
19
        // Set the first indexed parameter
20
        jdbc.psqlSetInt(1, 1)
21
        // Set the second indexed parameter
22
        jdbc.psqlSetString(2, 'test')
23

24
    }
25
    // Prepared statement query
26
    let data = jdbc.psqlQuery()
27
    logd(data);
28

29
    // Close prepared statement
30
    jdbc.psqlClose()
31

32
    // Insert data
33
    q = "insert table1(`uname`,`ucontent`,`create_time`)values(?,?,?);"
34
    qur = jdbc.createPreparedStatement(q)
35
    if (qur) {
36
        // Set the first indexed parameter
37
        jdbc.psqlSetString(1, "我是名称")
38
        // Set the second indexed parameter
39
        jdbc.psqlSetString(2, '我是内容')
40
        // Set date
41
        jdbc.psqlSetDate(3, "yyyy-MM-dd hh:mm:ss", "2020-10-02 12:02:11")
42
    }
43
    rowcount = jdbc.psqlExecuteUpdate();
44
    logi("Insert affected row count -" + rowcount);
45
    if (rowcount <= 0) {
46
        loge("Insert error: " + jdbc.getLastError())
47
    }
48
    jdbc.connectionClose()
49
}
50

51
main();

jdbc.psqlSetTimestamp Set Timestamp Parameter

Set a timestamp parameter on the prepared statement
@param index parameter index
@param dataFormat date format, e.g. yyyy-MM-dd
@param input date string
@return {bool} true on success, false on failure

1
function main() {
2
    // MySQL address ip:port/database name
3
    let mysqlUrl = "jdbc:mysql://192.168.0.3:3306/test?characterEncoding=utf8&autoReconnect=true"
4
    let inited = jdbc.init("com.mysql.cj.jdbc.Driver", mysqlUrl, "root", "root123456");
5
    logd("inited " + inited);
6

7
    let conn = jdbc.connect()
8
    logd("connect " + conn);
9
    if (!conn) {
10
        logd(jdbc.getLastError());
11
        exit()
12
    }
13

14
    // Query statement
15
    let q = "select * from table1 where id=? or uname=?"
16
    // Create a query
17
    let qur = jdbc.createPreparedStatement(q)
18
    if (qur) {
19
        // Set the first indexed parameter
20
        jdbc.psqlSetInt(1, 1)
21
        // Set the second indexed parameter
22
        jdbc.psqlSetString(2, 'test')
23

24
    }
25
    // Prepared statement query
26
    let data = jdbc.psqlQuery()
27
    logd(data);
28

29
    // Close prepared statement
30
    jdbc.psqlClose()
31

32
    // Insert data
33
    q = "insert table1(`uname`,`ucontent`,`create_time`)values(?,?,?);"
34
    qur = jdbc.createPreparedStatement(q)
35
    if (qur) {
36
        // Set the first indexed parameter
37
        jdbc.psqlSetString(1, "我是名称")
38
        // Set the second indexed parameter
39
        jdbc.psqlSetString(2, '我是内容')
40
        // Set timestamp
41
        jdbc.psqlSetTimestamp(3, "yyyy-MM-dd hh:mm:ss", "2020-10-02 12:02:11")
42
    }
43
    rowcount = jdbc.psqlExecuteUpdate();
44
    logi("Insert affected row count -" + rowcount);
45
    if (rowcount <= 0) {
46
        loge("Insert error: " + jdbc.getLastError())
47
    }
48
    jdbc.psqlClose()
49
    jdbc.connectionClose()
50
}
51

52
main();

jdbc.psqlAddBatch Batch Submit

Add current parameters to batch submission
@return {bool} true on success, false on failure

1
function main() {
2
    // MySQL address ip:port/database name
3
    let mysqlUrl = "jdbc:mysql://192.168.0.3:3306/test?characterEncoding=utf8&autoReconnect=true"
4
    let inited = jdbc.init("com.mysql.cj.jdbc.Driver", mysqlUrl, "root", "root123456");
5
    logd("inited " + inited);
6

7
    let conn = jdbc.connect()
8
    logd("connect " + conn);
9
    if (!conn) {
10
        logd(jdbc.getLastError());
11
        exit()
12
    }
13

14

15
    // Insert data
16
    let q = "insert table1(`uname`,`ucontent`,`create_time`)values(?,?,?);"
17
    let qur = jdbc.createPreparedStatement(q)
18

19

20
    if (qur) {
21

22
        for (var i = 0; i < 10; i++) {
23
            // Set the first indexed parameter
24
            jdbc.psqlSetString(1, "我是名称")
25
            // Set the second indexed parameter
26
            jdbc.psqlSetString(2, '我是内容')
27
            // Set timestamp
28
            jdbc.psqlSetTimestamp(3, "yyyy-MM-dd hh:mm:ss", "2020-10-02 12:02:11")
29
            r = jdbc.psqlAddBatch()
30
            if (!r) {
31
                logd(jdbc.getLastError());
32
            }
33
        }
34
    }
35

36
    rowcount = jdbc.psqlExecuteUpdate();
37
    logi("Insert affected row count -" + rowcount);
38
    if (rowcount <= 0) {
39
        loge("Insert error: " + jdbc.getLastError())
40
    }
41
    jdbc.psqlClose()
42
    jdbc.connectionClose()
43

44
}
45

46
main();

jdbc.psqlExecuteUpdate Execute Update

Execute an update operation
@return {int} number of affected rows

1
function main() {
2
    // MySQL address ip:port/database name
3
    let mysqlUrl = "jdbc:mysql://192.168.0.3:3306/test?characterEncoding=utf8&autoReconnect=true"
4
    let inited = jdbc.init("com.mysql.cj.jdbc.Driver", mysqlUrl, "root", "root123456");
5
    logd("inited " + inited);
6

7
    let conn = jdbc.connect()
8
    logd("connect " + conn);
9
    if (!conn) {
10
        logd(jdbc.getLastError());
11
        exit()
12
    }
13

14
    // Update data
15
    let q = "update table1 set uname=?,ucontent=?,create_time=? where id=?;"
16
    let qur = jdbc.createPreparedStatement(q)
17
    if (qur) {
18
        // Set the first indexed parameter
19
        jdbc.psqlSetString(1, "我是名称")
20
        // Set the second indexed parameter
21
        jdbc.psqlSetString(2, '我是内容')
22
        // Set timestamp
23
        jdbc.psqlSetTimestamp(3, "yyyy-MM-dd hh:mm:ss", "2020-10-02 12:02:11")
24
        // Set id
25
        jdbc.psqlSetInt(4, 1)
26
    }
27
    let rowcount = jdbc.psqlExecuteUpdate();
28
    logi("Insert affected row count -" + rowcount);
29
    if (rowcount <= 0) {
30
        loge("Insert error: " + jdbc.getLastError())
31
    }
32
    // Insert data
33
    q = "insert table1(`uname`,`ucontent`,`create_time`)values(?,?,?);"
34
    qur = jdbc.createPreparedStatement(q)
35
    if (qur) {
36
        // Set the first indexed parameter
37
        jdbc.psqlSetString(1, "我是名称")
38
        // Set the second indexed parameter
39
        jdbc.psqlSetString(2, '我是内容')
40
        // Set timestamp
41
        jdbc.psqlSetTimestamp(3, "yyyy-MM-dd hh:mm:ss", "2020-10-02 12:02:11")
42
    }
43
    rowcount = jdbc.psqlExecuteUpdate();
44
    logi("Insert affected row count -" + rowcount);
45
    if (rowcount <= 0) {
46
        loge("Insert error: " + jdbc.getLastError())
47
    }
48
    jdbc.psqlClose()
49
    jdbc.connectionClose()
50
}
51

52
main();

jdbc.psqlClose Close Prepared Statement

Close the prepared statement
@return {bool} true on success, false on failure

1
function main() {
2
    jdbc.psqlClose()
3
}
4

5
main();

jdbc.connectionClose Close Database Connection

Close the database connection
@return {bool} true on success, false on failure

1
function main() {
2
    // MySQL address ip:port/database name
3
    let mysqlUrl = "jdbc:mysql://192.168.0.3:3306/test?characterEncoding=utf8&autoReconnect=true"
4
    let inited = jdbc.init("com.mysql.cj.jdbc.Driver", mysqlUrl, "root", "root123456");
5
    logd("inited " + inited);
6

7
    let conn = jdbc.connect()
8
    logd("connect " + conn);
9
    if (!conn) {
10
        logd(jdbc.getLastError());
11
        exit()
12
    }
13

14
    // Query statement
15
    let q = "select * from table1 where id=? or uname=?"
16
    // Create a query
17
    let qur = jdbc.createPreparedStatement(q)
18
    if (qur) {
19
        // Set the first indexed parameter
20
        jdbc.psqlSetInt(1, 1)
21
        // Set the second indexed parameter
22
        jdbc.psqlSetString(2, 'test')
23

24
    }
25
    // Prepared statement query
26
    let data = jdbc.psqlQuery()
27
    logd(data);
28

29
    // Close prepared statement
30
    jdbc.psqlClose()
31

32
    // Insert data
33
    q = "insert table1(`uname`,`ucontent`,`create_time`)values(?,?,?);"
34
    qur = jdbc.createPreparedStatement(q)
35
    if (qur) {
36
        // Set the first indexed parameter
37
        jdbc.psqlSetString(1, "我是名称")
38
        // Set the second indexed parameter
39
        jdbc.psqlSetString(2, '我是内容')
40
        // Set timestamp
41
        jdbc.psqlSetTimestamp(3, "yyyy-MM-dd hh:mm:ss", "2020-10-02 12:02:11")
42
    }
43
    rowcount = jdbc.psqlExecuteUpdate();
44
    logi("Insert affected row count -" + rowcount);
45
    if (rowcount <= 0) {
46
        loge("Insert error: " + jdbc.getLastError())
47
    }
48
    jdbc.psqlClose()
49
    jdbc.connectionClose()
50
}
51

52
main();