Mercurial > hg > monetdb-java

comparison example/OnClientExample.java @ 558:ebf65f416da9 onclient

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Many improvements to the example code
author Joeri van Ruth <joeri.van.ruth@monetdbsolutions.com>
date Thu, 16 Sep 2021 15:12:49 +0200 (2021-09-16)
parents 87feb93330a6
children 6cb395daa7d1
comparison
equal deleted inserted replaced
557:ce2b616ed22e 558:ebf65f416da9
22 22
23 public static void main(String[] args) { 23 public static void main(String[] args) {
24 int status; 24 int status;
25 try { 25 try {
26 // Ideally this would not be hardcoded.. 26 // Ideally this would not be hardcoded..
27 final String dbUrl = "jdbc:monetdb://localhost:55000/banana"; 27 final String dbUrl = "jdbc:monetdb://localhost:50000/demo";
28 final String userName = "monetdb";
29 final String password = "monetdb";
28 final String uploadDir = "/home/jvr/mydata"; 30 final String uploadDir = "/home/jvr/mydata";
29 final boolean filesAreUtf8 = false; 31 final boolean filesAreUtf8 = false;
30 final String userName = "monetdb"; 32 String[] queries = {
31 final String password = "monetdb"; 33 "DROP TABLE IF EXISTS mytable",
34 "CREATE TABLE mytable(i INT, t TEXT)",
35 "COPY INTO mytable FROM 'generated.csv' ON CLIENT",
36 "COPY 20 RECORDS OFFSET 5 INTO mytable FROM 'generated.csv' ON CLIENT",
37 "COPY INTO mytable FROM 'nonexistentfilethatdoesnotexist.csv' ON CLIENT",
38 "SELECT COUNT(*) FROM mytable",
39 };
32 40
33 status = run(dbUrl, userName, password, uploadDir, filesAreUtf8); 41 status = run(dbUrl, userName, password, uploadDir, filesAreUtf8, queries);
34 42
35 } catch (Exception e) { 43 } catch (Exception e) {
36 status = 1; 44 status = 1;
37 e.printStackTrace(); 45 e.printStackTrace();
38 } 46 }
39 System.exit(status); 47 System.exit(status);
40 } 48 }
41 49
42 private static int run(String dbUrl, String userName, String password, String uploadDir, boolean filesAreUtf8) throws ClassNotFoundException, SQLException { 50 private static int run(String dbUrl, String userName, String password, String uploadDir, boolean filesAreUtf8, String[] queries) throws ClassNotFoundException, SQLException {
43 int status = 0; 51 int status = 0;
44 52
45 // Connect 53 // Connect
46 Class.forName("org.monetdb.jdbc.MonetDriver"); 54 Class.forName("org.monetdb.jdbc.MonetDriver");
47 Connection conn = DriverManager.getConnection(dbUrl, userName, password); 55 Connection conn = DriverManager.getConnection(dbUrl, userName, password);
48 56
49 // Register upload handler 57 // Register upload handler
50 MyUploader handler = new MyUploader(uploadDir, filesAreUtf8); 58 MyUploader handler = new MyUploader(uploadDir, filesAreUtf8);
51 conn.unwrap(MonetConnection.class).setUploadHandler(handler); 59 conn.unwrap(MonetConnection.class).setUploadHandler(handler);
52 60
53 // Run some SQL statements involving ON CLIENT
54 String[] queries = {
55 "DROP TABLE IF EXISTS bar",
56 "CREATE TABLE bar(i INT, t TEXT)",
57 "COPY INTO bar FROM 'generated.csv' ON CLIENT",
58 "COPY INTO bar FROM 'file.csv' ON CLIENT",
59 // following statement will run even if file.csv does not exist
60 "SELECT COUNT(*) FROM bar",
61 };
62 Statement stmt = conn.createStatement(); 61 Statement stmt = conn.createStatement();
63 for (String q : queries) { 62 for (String q : queries) {
64 System.out.println(q); 63 System.out.println(q);
65 try { 64 try {
66 stmt.execute(q); 65 boolean hasResultSet = stmt.execute(q);
67 ResultSet rs = stmt.getResultSet(); 66 if (hasResultSet) {
68 if (rs == null) { 67 ResultSet rs = stmt.getResultSet();
69 System.out.printf(" OK, %d rows updated%n", stmt.getUpdateCount());
70 } else {
71 long count = 0; 68 long count = 0;
72 while (rs.next()) { 69 while (rs.next()) {
73 count++; 70 count++;
74 } 71 }
75 System.out.printf(" OK, returned %d rows%n", count); 72 System.out.printf(" OK, returned %d rows%n", count);
73 } else {
74 System.out.printf(" OK, updated %d rows%n", stmt.getUpdateCount());
76 } 75 }
77 } catch (SQLNonTransientException e) { 76 } catch (SQLNonTransientException e) {
78 throw e; 77 throw e;
79 } catch (SQLException e) { 78 } catch (SQLException e) {
80 System.out.println(" => SQL ERROR " + e.getMessage()); 79 System.out.println(" => SQL ERROR " + e.getMessage());
81 status = 1; 80 status = 1;
82 } 81 }
82
83 } 83 }
84 84
85 return status; 85 return status;
86 } 86 }
87 87
88 88
89 private static class MyUploader implements UploadHandler { 89 private static class MyUploader implements UploadHandler {
90 private final Path uploadDir; 90 private final Path uploadDir;
91 private final boolean filesAreUtf8; 91 private final boolean filesAreUtf8;
92 private boolean stopUploading = false;
92 93
93 public MyUploader(String uploadDir, boolean filesAreUtf8) { 94 public MyUploader(String uploadDir, boolean filesAreUtf8) {
94 this.uploadDir = FileSystems.getDefault().getPath(uploadDir).normalize(); 95 this.uploadDir = FileSystems.getDefault().getPath(uploadDir).normalize();
95 this.filesAreUtf8 = filesAreUtf8; 96 this.filesAreUtf8 = filesAreUtf8;
96 } 97 }
97 98
98 @Override 99 @Override
100 public void uploadCancelled() {
101 System.out.println(" CANCELLATION CALLBACK: server cancelled the upload");
102 stopUploading = true;
103 }
104
105 @Override
99 public void handleUpload(MonetConnection.Upload handle, String name, boolean textMode, long linesToSkip) throws IOException { 106 public void handleUpload(MonetConnection.Upload handle, String name, boolean textMode, long linesToSkip) throws IOException {
100
101 // COPY OFFSET line numbers are 1-based but 0 is also allowed.
102 // Compute the number of lines to skip
103 107
104 // We can upload data read from the file system but also make up our own data 108 // We can upload data read from the file system but also make up our own data
105 if (name.equals("generated.csv")) { 109 if (name.equals("generated.csv")) {
106 uploadGenerated(handle, linesToSkip); 110 uploadGeneratedData(handle, linesToSkip);
107 return; 111 return;
108 } 112 }
109 113
110 // Validate the path, demonstrating two ways of dealing with errors 114 // Validate the path, demonstrating two ways of dealing with errors
111 Path path = securityCheck(name); 115 Path path = securityCheck(name);
112 if (path == null || !Files.exists(path)) { 116 if (path == null || !Files.exists(path)) {
113 // This makes the COPY command fail but keeps the connection alive. 117 // This makes the COPY command fail but keeps the connection
114 // Can only be used if we haven't sent any data yet 118 // alive. Can only be used if we haven't sent any data yet
115 handle.sendError("Invalid path"); 119 handle.sendError("Invalid path");
116 return; 120 return;
117 } 121 }
118 if (!Files.isReadable(path)) { 122 if (!Files.isReadable(path)) {
119 // As opposed to handle.sendError(), throwing an IOException ends the whole connection. 123 // As opposed to handle.sendError(), we can throw an IOException
124 // at any time. Unfortunately, the file upload protocol does not
125 // provide a way to indicate to the server that the data sent so
126 // far is incomplete, so for the time being throwing an
127 // IOException from {@handleUpload} terminates the connection.
120 throw new IOException("Unreadable: " + path); 128 throw new IOException("Unreadable: " + path);
121 } 129 }
122 130
123 boolean binary = !textMode; 131 boolean binary = !textMode;
124 if (binary) { 132 if (binary) {
125 uploadBinary(handle, path); 133 uploadAsBinary(handle, path);
126 } else if (linesToSkip == 0 && filesAreUtf8) { 134 } else if (linesToSkip == 0 && filesAreUtf8) {
127 // Avoid unnecessary character set conversions by pretending it's binary 135 // Avoid unnecessary UTF-8 -> Java String -> UTF-8 conversions
128 uploadBinary(handle, path); 136 // by pretending the data is binary.
137 uploadAsBinary(handle, path);
129 } else { 138 } else {
130 // Charset and skip handling really necessary 139 // Charset and skip handling really necessary
131 uploadTextFile(handle, path, linesToSkip); 140 uploadAsText(handle, path, linesToSkip);
132 } 141 }
133 } 142 }
134 143
135 private Path securityCheck(String name) { 144 private Path securityCheck(String name) {
136 Path p = uploadDir.resolve(name).normalize(); 145 Path p = uploadDir.resolve(name).normalize();
139 } else { 148 } else {
140 return null; 149 return null;
141 } 150 }
142 } 151 }
143 152
144 private void uploadGenerated(MonetConnection.Upload handle, long toSkip) throws IOException { 153 private void uploadGeneratedData(MonetConnection.Upload handle, long toSkip) throws IOException {
154 // Set the chunk size to a tiny amount so we can demonstrate
155 // cancellation handling. The default chunk size is one megabyte.
156 // DO NOT DO THIS IN PRODUCTION!
157 handle.setChunkSize(50);
158
159 // Make up some data and upload it.
145 PrintStream stream = handle.getStream(); 160 PrintStream stream = handle.getStream();
146 for (long i = toSkip + 1; i <= 100; i++) { 161 long n = 100;
162 System.out.printf(" HANDLER: uploading %d generated lines, numbered %d to %d%n", n - toSkip, toSkip +1, n);
163 long i;
164 for (i = toSkip + 1; i <= n; i++) {
165 if (stopUploading) {
166 System.out.printf(" HANDLER: at line %d we noticed the server asked us to stop sending%n", i);
167 break;
168 }
147 stream.printf("%d|the number is %d%n", i, i); 169 stream.printf("%d|the number is %d%n", i, i);
148 } 170 }
171 System.out.println(" HANDLER: done uploading");
149 stream.close(); 172 stream.close();
150 } 173 }
151 174
152 private void uploadTextFile(MonetConnection.Upload handle, Path path, long toSkip) throws IOException { 175 private void uploadAsText(MonetConnection.Upload handle, Path path, long toSkip) throws IOException {
153 BufferedReader reader = Files.newBufferedReader(path);// Converts from system encoding to Java text 176 BufferedReader reader = Files.newBufferedReader(path);// Converts from system encoding to Java text
154 for (long i = 0; i < toSkip; i++) { 177 for (long i = 0; i < toSkip; i++) {
155 reader.readLine(); 178 reader.readLine();
156 } 179 }
157 handle.uploadFrom(reader); // Converts from Java text to UTF-8 as required by MonetDB 180 handle.uploadFrom(reader); // Converts from Java text to UTF-8 as required by MonetDB
158 } 181 }
159 182
160 private void uploadBinary(MonetConnection.Upload handle, Path path) throws IOException { 183 private void uploadAsBinary(MonetConnection.Upload handle, Path path) throws IOException {
161 // No charset conversion whatsoever.. 184 // No charset conversion whatsoever..
162 // Use this for binary data or when you are certain the file is UTF-8 encoded. 185 // Use this for binary data or when you are certain the file is UTF-8 encoded.
163 InputStream stream = Files.newInputStream(path); 186 InputStream stream = Files.newInputStream(path);
164 handle.uploadFrom(stream); 187 handle.uploadFrom(stream);
165 } 188 }