Mercurial > hg > monetdb-java

comparison example/OnClientExample.java @ 608:c462161504a3

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Extend example with Download functionality
author Joeri van Ruth <joeri.van.ruth@monetdbsolutions.com>
date Tue, 04 Jan 2022 14:42:37 +0100 (2022-01-04)
parents 6cb395daa7d1
children 6666a9c62460
comparison
equal deleted inserted replaced
607:69b0bcf5f62d 608:c462161504a3
5 * 5 *
6 * Copyright 1997 - July 2008 CWI, August 2008 - 2021 MonetDB B.V. 6 * Copyright 1997 - July 2008 CWI, August 2008 - 2021 MonetDB B.V.
7 */ 7 */
8 8
9 import org.monetdb.jdbc.MonetConnection; 9 import org.monetdb.jdbc.MonetConnection;
10 import org.monetdb.jdbc.MonetConnection.UploadHandler;
11 10
12 import java.io.BufferedReader; 11 import java.io.BufferedReader;
12 import java.io.BufferedWriter;
13 import java.io.IOException; 13 import java.io.IOException;
14 import java.io.InputStream; 14 import java.io.InputStream;
15 import java.io.InputStreamReader;
16 import java.io.OutputStream;
17 import java.io.OutputStreamWriter;
15 import java.io.PrintStream; 18 import java.io.PrintStream;
19 import java.nio.charset.Charset;
20 import java.nio.charset.StandardCharsets;
16 import java.nio.file.FileSystems; 21 import java.nio.file.FileSystems;
17 import java.nio.file.Files; 22 import java.nio.file.Files;
18 import java.nio.file.Path; 23 import java.nio.file.Path;
19 import java.sql.*; 24 import java.sql.*;
20 25
30 final String uploadDir = "/home/jvr/mydata"; 35 final String uploadDir = "/home/jvr/mydata";
31 final boolean filesAreUtf8 = false; 36 final boolean filesAreUtf8 = false;
32 String[] queries = { 37 String[] queries = {
33 "DROP TABLE IF EXISTS mytable", 38 "DROP TABLE IF EXISTS mytable",
34 "CREATE TABLE mytable(i INT, t TEXT)", 39 "CREATE TABLE mytable(i INT, t TEXT)",
40
41 // with this filename our handler will make up data by itself
35 "COPY INTO mytable FROM 'generated.csv' ON CLIENT", 42 "COPY INTO mytable FROM 'generated.csv' ON CLIENT",
43
44 // with this filename our handler will access the file system
45 "COPY INTO mytable FROM 'data.csv' ON CLIENT",
46
47 // with this statement, the server will ask the handler to stop halfway
36 "COPY 20 RECORDS OFFSET 5 INTO mytable FROM 'generated.csv' ON CLIENT", 48 "COPY 20 RECORDS OFFSET 5 INTO mytable FROM 'generated.csv' ON CLIENT",
49
50 // this demonstrates sending errors
37 "COPY INTO mytable FROM 'nonexistentfilethatdoesnotexist.csv' ON CLIENT", 51 "COPY INTO mytable FROM 'nonexistentfilethatdoesnotexist.csv' ON CLIENT",
52
53 // downloads but does not write to file, only counts the lines
54 "COPY SELECT * FROM mytable INTO 'justcount.csv' ON CLIENT",
55
56 // downloads to actual file
57 "COPY SELECT * FROM mytable INTO 'download.csv' ON CLIENT",
58
59 // demonstrate that the connection is still alive
38 "SELECT COUNT(*) FROM mytable", 60 "SELECT COUNT(*) FROM mytable",
39 }; 61 };
40 62
41 status = run(dbUrl, userName, password, uploadDir, filesAreUtf8, queries); 63 status = run(dbUrl, userName, password, uploadDir, filesAreUtf8, queries);
42 64
52 74
53 // Connect 75 // Connect
54 Class.forName("org.monetdb.jdbc.MonetDriver"); 76 Class.forName("org.monetdb.jdbc.MonetDriver");
55 Connection conn = DriverManager.getConnection(dbUrl, userName, password); 77 Connection conn = DriverManager.getConnection(dbUrl, userName, password);
56 78
57 // Register upload handler 79 // Register upload- and download handler
58 MyUploader handler = new MyUploader(uploadDir, filesAreUtf8); 80 MyHandler handler = new MyHandler(uploadDir, filesAreUtf8);
59 conn.unwrap(MonetConnection.class).setUploadHandler(handler); 81 MonetConnection monetConnection = conn.unwrap(MonetConnection.class);
82 monetConnection.setUploadHandler(handler);
83 monetConnection.setDownloadHandler(handler);
60 84
61 Statement stmt = conn.createStatement(); 85 Statement stmt = conn.createStatement();
62 for (String q : queries) { 86 for (String q : queries) {
63 System.out.println(q); 87 System.out.println(q);
64 try { 88 try {
84 108
85 return status; 109 return status;
86 } 110 }
87 111
88 112
89 private static class MyUploader implements UploadHandler { 113 private static class MyHandler implements MonetConnection.UploadHandler, MonetConnection.DownloadHandler {
90 private final Path uploadDir; 114 private final Path uploadDir;
91 private final boolean filesAreUtf8; 115 private final boolean filesAreUtf8;
92 private boolean stopUploading = false; 116 private boolean stopUploading = false;
93 117
94 public MyUploader(String uploadDir, boolean filesAreUtf8) { 118 public MyHandler(String uploadDir, boolean filesAreUtf8) {
95 this.uploadDir = FileSystems.getDefault().getPath(uploadDir).normalize(); 119 this.uploadDir = FileSystems.getDefault().getPath(uploadDir).normalize();
96 this.filesAreUtf8 = filesAreUtf8; 120 this.filesAreUtf8 = filesAreUtf8;
97 } 121 }
98 122
99 @Override 123 @Override
102 stopUploading = true; 126 stopUploading = true;
103 } 127 }
104 128
105 @Override 129 @Override
106 public void handleUpload(MonetConnection.Upload handle, String name, boolean textMode, long linesToSkip) throws IOException { 130 public void handleUpload(MonetConnection.Upload handle, String name, boolean textMode, long linesToSkip) throws IOException {
107
108 // We can upload data read from the file system but also make up our own data 131 // We can upload data read from the file system but also make up our own data
109 if (name.equals("generated.csv")) { 132 if (name.equals("generated.csv")) {
110 uploadGeneratedData(handle, linesToSkip); 133 uploadGeneratedData(handle, linesToSkip);
111 return; 134 } else {
112 } 135 uploadFileData(handle, name, textMode, linesToSkip);
113 136 }
114 // Validate the path, demonstrating two ways of dealing with errors 137 }
115 Path path = securityCheck(name); 138
116 if (path == null || !Files.exists(path)) { 139 @Override
117 // This makes the COPY command fail but keeps the connection 140 public void handleDownload(MonetConnection.Download handle, String name, boolean textMode) throws IOException {
118 // alive. Can only be used if we haven't sent any data yet 141 if (name.equals("justcount.csv")) {
119 handle.sendError("Invalid path"); 142 justCountLines(handle);
120 return; 143 } else {
121 } 144 downloadFileData(handle, name, textMode);
122 if (!Files.isReadable(path)) { 145 }
123 // As opposed to handle.sendError(), we can throw an IOException 146 }
124 // at any time. Unfortunately, the file upload protocol does not 147
125 // provide a way to indicate to the server that the data sent so 148 private Path securelyResolvePath(String name) {
126 // far is incomplete, so for the time being throwing an
127 // IOException from {@handleUpload} terminates the connection.
128 throw new IOException("Unreadable: " + path);
129 }
130
131 boolean binary = !textMode;
132 if (binary) {
133 uploadAsBinary(handle, path);
134 } else if (linesToSkip == 0 && filesAreUtf8) {
135 // Avoid unnecessary UTF-8 -> Java String -> UTF-8 conversions
136 // by pretending the data is binary.
137 uploadAsBinary(handle, path);
138 } else {
139 // Charset and skip handling really necessary
140 uploadAsText(handle, path, linesToSkip);
141 }
142 }
143
144 private Path securityCheck(String name) {
145 Path p = uploadDir.resolve(name).normalize(); 149 Path p = uploadDir.resolve(name).normalize();
146 if (p.startsWith(uploadDir)) { 150 if (p.startsWith(uploadDir)) {
147 return p; 151 return p;
148 } else { 152 } else {
149 return null; 153 return null;
150 } 154 }
151 } 155 }
152 156
153 private void uploadGeneratedData(MonetConnection.Upload handle, long toSkip) throws IOException { 157 private void uploadGeneratedData(MonetConnection.Upload handle, long toSkip) throws IOException {
154 // Set the chunk size to a tiny amount so we can demonstrate 158 // Set the chunk size to a tiny amount, so we can demonstrate
155 // cancellation handling. The default chunk size is one megabyte. 159 // cancellation handling. The default chunk size is one megabyte.
156 // DO NOT DO THIS IN PRODUCTION! 160 // DO NOT DO THIS IN PRODUCTION!
157 handle.setChunkSize(50); 161 handle.setChunkSize(50);
158 162
159 // Make up some data and upload it. 163 // Make up some data and upload it.
170 } 174 }
171 System.out.println(" HANDLER: done uploading"); 175 System.out.println(" HANDLER: done uploading");
172 stream.close(); 176 stream.close();
173 } 177 }
174 178
179 private void uploadFileData(MonetConnection.Upload handle, String name, boolean textMode, long linesToSkip) throws IOException {
180 // Validate the path, demonstrating two ways of dealing with errors
181 Path path = securelyResolvePath(name);
182 if (path == null || !Files.exists(path)) {
183 // This makes the COPY command fail but keeps the connection
184 // alive. Can only be used if we haven't sent any data yet
185 handle.sendError("Invalid path");
186 return;
187 }
188 if (!Files.isReadable(path)) {
189 // As opposed to handle.sendError(), we can throw an IOException
190 // at any time. Unfortunately, the file upload protocol does not
191 // provide a way to indicate to the server that the data sent so
192 // far is incomplete, so for the time being throwing an
193 // IOException from {@handleUpload} terminates the connection.
194 throw new IOException("Unreadable: " + path);
195 }
196
197 boolean binary = !textMode;
198 if (binary) {
199 uploadAsBinary(handle, path);
200 } else if (linesToSkip == 0 && filesAreUtf8) {
201 // Avoid unnecessary UTF-8 -> Java String -> UTF-8 conversions
202 // by pretending the data is binary.
203 uploadAsBinary(handle, path);
204 } else {
205 // Charset and skip handling are really necessary
206 uploadAsText(handle, path, linesToSkip);
207 }
208 }
209
175 private void uploadAsText(MonetConnection.Upload handle, Path path, long toSkip) throws IOException { 210 private void uploadAsText(MonetConnection.Upload handle, Path path, long toSkip) throws IOException {
176 BufferedReader reader = Files.newBufferedReader(path);// Converts from system encoding to Java text 211 BufferedReader reader = Files.newBufferedReader(path);// Converts from system encoding to Java text
177 for (long i = 0; i < toSkip; i++) { 212 for (long i = 0; i < toSkip; i++) {
178 reader.readLine(); 213 reader.readLine();
179 } 214 }
215 // This variant of uploadFrom takes a Reader
180 handle.uploadFrom(reader); // Converts from Java text to UTF-8 as required by MonetDB 216 handle.uploadFrom(reader); // Converts from Java text to UTF-8 as required by MonetDB
181 } 217 }
182 218
183 private void uploadAsBinary(MonetConnection.Upload handle, Path path) throws IOException { 219 private void uploadAsBinary(MonetConnection.Upload handle, Path path) throws IOException {
184 // No charset conversion whatsoever.. 220 // No charset conversion whatsoever..
185 // Use this for binary data or when you are certain the file is UTF-8 encoded. 221 // Use this for binary data or when you are certain the file is UTF-8 encoded.
186 InputStream stream = Files.newInputStream(path); 222 InputStream stream = Files.newInputStream(path);
223 // This variant of uploadFrom takes a Stream
187 handle.uploadFrom(stream); 224 handle.uploadFrom(stream);
188 } 225 }
226
227
228 private void justCountLines(MonetConnection.Download handle) throws IOException {
229 System.out.println(" HANDLER: not writing the download to file, just counting the lines");
230 InputStream stream = handle.getStream();
231 InputStreamReader reader = new InputStreamReader(stream, StandardCharsets.UTF_8); // MonetDB always sends UTF-8
232 BufferedReader bufreader = new BufferedReader(reader);
233 long count = 0;
234 while (bufreader.readLine() != null) {
235 count++;
236 }
237 System.out.println(" HANDLER: file had " + count + " lines");
238 }
239
240 private void downloadFileData(MonetConnection.Download handle, String name, boolean textMode) throws IOException {
241 Path path = securelyResolvePath(name);
242 if (path == null) {
243 handle.sendError("Illegal path");
244 return;
245 }
246
247 OutputStream stream = Files.newOutputStream(path);
248 if (!textMode || filesAreUtf8) {
249 handle.downloadTo(stream);
250 stream.close(); // do not forget this
251 } else {
252 OutputStreamWriter writer = new OutputStreamWriter(stream, Charset.defaultCharset()); // let system decide the encoding
253 BufferedWriter bufwriter = new BufferedWriter(writer);
254 handle.downloadTo(bufwriter);
255 bufwriter.close(); // do not forget this
256 }
257 }
258
189 } 259 }
190 } 260 }