-
Notifications
You must be signed in to change notification settings - Fork 2k
Expand file tree
/
Copy pathjdbc.gs
More file actions
229 lines (205 loc) · 6.51 KB
/
jdbc.gs
File metadata and controls
229 lines (205 loc) · 6.51 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
/**
* Copyright Google LLC
*
* Licensed under the Apache License, Version 2.0 (the 'License');
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* https://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an 'AS IS' BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
/**
* Replace the variables in this block with real values.
* You can find the "Instance connection name" in the Google Cloud
* Platform Console, on the instance Overview page.
*/
const connectionName = "Instance_connection_name";
const rootPwd = "root_password";
const user = "user_name";
const userPwd = "user_password";
const db = "database_name";
const root = "root";
const instanceUrl = `jdbc:google:mysql://${connectionName}`;
const dbUrl = `${instanceUrl}/${db}`;
// [START apps_script_jdbc_create]
/**
* Create a new database within a Cloud SQL instance.
*/
function createDatabase() {
try {
const conn = Jdbc.getCloudSqlConnection(instanceUrl, root, rootPwd);
conn.createStatement().execute(`CREATE DATABASE ${db}`);
} catch (err) {
// TODO(developer) - Handle exception from the API
console.log("Failed with an error %s", err.message);
}
}
/**
* Create a new user for your database with full privileges.
*/
function createUser() {
try {
const conn = Jdbc.getCloudSqlConnection(dbUrl, root, rootPwd);
const stmt = conn.prepareStatement("CREATE USER ? IDENTIFIED BY ?");
stmt.setString(1, user);
stmt.setString(2, userPwd);
stmt.execute();
conn.createStatement().execute(`GRANT ALL ON \`%\`.* TO ${user}`);
} catch (err) {
// TODO(developer) - Handle exception from the API
console.log("Failed with an error %s", err.message);
}
}
/**
* Create a new table in the database.
*/
function createTable() {
try {
const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
conn
.createStatement()
.execute(
"CREATE TABLE entries " +
"(guestName VARCHAR(255), content VARCHAR(255), " +
"entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));",
);
} catch (err) {
// TODO(developer) - Handle exception from the API
console.log("Failed with an error %s", err.message);
}
}
// [END apps_script_jdbc_create]
// [START apps_script_jdbc_write]
/**
* Write one row of data to a table.
*/
function writeOneRecord() {
try {
const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
const stmt = conn.prepareStatement(
"INSERT INTO entries " + "(guestName, content) values (?, ?)",
);
stmt.setString(1, "First Guest");
stmt.setString(2, "Hello, world");
stmt.execute();
} catch (err) {
// TODO(developer) - Handle exception from the API
console.log("Failed with an error %s", err.message);
}
}
/**
* Write 500 rows of data to a table in a single batch.
*/
function writeManyRecords() {
try {
const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
conn.setAutoCommit(false);
const start = new Date();
const stmt = conn.prepareStatement(
"INSERT INTO entries " + "(guestName, content) values (?, ?)",
);
for (let i = 0; i < 500; i++) {
stmt.setString(1, `Name ${i}`);
stmt.setString(2, `Hello, world ${i}`);
stmt.addBatch();
}
const batch = stmt.executeBatch();
conn.commit();
conn.close();
const end = new Date();
console.log("Time elapsed: %sms for %s rows.", end - start, batch.length);
} catch (err) {
// TODO(developer) - Handle exception from the API
console.log("Failed with an error %s", err.message);
}
}
/**
* Write 500 rows of data to a table in a single batch.
* Recommended for faster writes
*/
function writeManyRecordsUsingExecuteBatch() {
try {
const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
conn.setAutoCommit(false);
const start = new Date();
const stmt = conn.prepareStatement(
"INSERT INTO entries " + "(guestName, content) values (?, ?)",
);
const params = [];
for (let i = 0; i < 500; i++) {
params.push([`Name ${i}`, `Hello, world ${i}`]);
}
const batch = stmt.executeBatch(params);
conn.commit();
conn.close();
const end = new Date();
console.log("Time elapsed: %sms for %s rows.", end - start, batch.length);
} catch (err) {
// TODO(developer) - Handle exception from the API
console.log("Failed with an error %s", err.message);
}
}
// [END apps_script_jdbc_write]
// [START apps_script_jdbc_read]
/**
* Read up to 1000 rows of data from the table and log them.
*/
function readFromTable() {
try {
const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
const start = new Date();
const stmt = conn.createStatement();
stmt.setMaxRows(1000);
const results = stmt.executeQuery("SELECT * FROM entries");
const numCols = results.getMetaData().getColumnCount();
while (results.next()) {
let rowString = "";
for (let col = 0; col < numCols; col++) {
rowString += `${results.getString(col + 1)}\t`;
}
console.log(rowString);
}
results.close();
stmt.close();
const end = new Date();
console.log("Time elapsed: %sms", end - start);
} catch (err) {
// TODO(developer) - Handle exception from the API
console.log("Failed with an error %s", err.message);
}
}
/**
* Read up to 1000 rows of data from the table and log them.
* Recommended for faster reads
*/
function readFromTableUsingGetRows() {
try {
const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
const start = new Date();
const stmt = conn.createStatement();
stmt.setMaxRows(1000);
const results = stmt.executeQuery("SELECT * FROM entries");
const numCols = results.getMetaData().getColumnCount();
const getRowArgs = [];
for (let col = 0; col < numCols; col++) {
getRowArgs.push(`getString(${col + 1})`);
}
const rows = results.getRows(getRowArgs.join(","));
for (let i = 0; i < rows.length; i++) {
console.log(rows[i].join("\t"));
}
results.close();
stmt.close();
const end = new Date();
console.log("Time elapsed: %sms", end - start);
} catch (err) {
// TODO(developer) - Handle exception from the API
console.log("Failed with an error %s", err.message);
}
}
// [END apps_script_jdbc_read]