Mercurial > hg > monetdb-java

comparison example/OnClientExample.java @ 536:daf6a3b828f9 onclient

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add onclient.txt and refer to it from release.txt Also move OnClientExample.java to directory examples/
author Joeri van Ruth <joeri.van.ruth@monetdbsolutions.com>
date Mon, 30 Aug 2021 15:52:04 +0200 (2021-08-30)
parents src/main/java/OnClientExample.java@130bb2e18d3f
children 31df6a12fd41
comparison
equal deleted inserted replaced
535:c9d88af06d35 536:daf6a3b828f9
1 import org.monetdb.jdbc.MonetConnection;
2 import org.monetdb.jdbc.MonetUploadHandler;
3
4 import java.io.BufferedReader;
5 import java.io.IOException;
6 import java.io.InputStream;
7 import java.io.PrintStream;
8 import java.nio.file.FileSystems;
9 import java.nio.file.Files;
10 import java.nio.file.Path;
11 import java.sql.*;
12
13 public class OnClientExample {
14
15 public static void main(String[] args) {
16 int status = 0;
17 try {
18 // Ideally this would not be hardcoded..
19 final String dbUrl = "jdbc:monetdb://localhost:55000/banana";
20 final String uploadDir = "/home/jvr/mydata";
21 final boolean filesAreUtf8 = false;
22 final String userName = "monetdb";
23 final String password = "monetdb";
24
25 status = run(dbUrl, userName, password, uploadDir, filesAreUtf8);
26
27 } catch (Exception e) {
28 status = 1;
29 e.printStackTrace();
30 }
31 System.exit(status);
32 }
33
34 private static int run(String dbUrl, String userName, String password, String uploadDir, boolean filesAreUtf8) throws ClassNotFoundException, SQLException {
35 int status = 0;
36
37 // Connect
38 Class.forName("org.monetdb.jdbc.MonetDriver");
39 Connection conn = DriverManager.getConnection(dbUrl, userName, password);
40
41 // Register upload handler
42 MyUploader handler = new MyUploader(uploadDir, filesAreUtf8);
43 conn.unwrap(MonetConnection.class).setUploadHandler(handler);
44
45 // Run some SQL statements involving ON CLIENT
46 String[] queries = {
47 "DROP TABLE IF EXISTS bar",
48 "CREATE TABLE bar(i INT, t TEXT)",
49 "COPY INTO bar FROM 'generated.csv' ON CLIENT",
50 "COPY INTO bar FROM 'file.csv' ON CLIENT",
51 // following statement will run even if file.csv does not exist
52 "SELECT COUNT(*) FROM bar",
53 };
54 Statement stmt = conn.createStatement();
55 for (String q : queries) {
56 System.out.println(q);
57 try {
58 stmt.execute(q);
59 ResultSet rs = stmt.getResultSet();
60 if (rs == null) {
61 System.out.printf(" OK, %d rows updated%n", stmt.getUpdateCount());
62 } else {
63 long count = 0;
64 while (rs.next()) {
65 count++;
66 }
67 System.out.printf(" OK, returned %d rows%n", count);
68 }
69 } catch (SQLNonTransientException e) {
70 throw e;
71 } catch (SQLException e) {
72 System.out.println(" => SQL ERROR " + e.getMessage());
73 status = 1;
74 }
75 }
76
77 return status;
78 }
79
80
81 private static class MyUploader implements MonetUploadHandler {
82 private final Path uploadDir;
83 private final boolean filesAreUtf8;
84
85 public MyUploader(String uploadDir, boolean filesAreUtf8) {
86 this.uploadDir = FileSystems.getDefault().getPath(uploadDir).normalize();
87 this.filesAreUtf8 = filesAreUtf8;
88 }
89
90 @Override
91 public void handleUpload(MonetConnection.Upload handle, String name, boolean textMode, int offset) throws IOException {
92
93 // COPY OFFSET line numbers are 1-based but 0 is also allowed.
94 // Compute the number of lines to skip
95 long toSkip = offset <= 1 ? 0 : offset - 1;
96
97 // We can upload data read from the file system but also make up our own data
98 if (name.equals("generated.csv")) {
99 uploadGenerated(handle, toSkip);
100 return;
101 }
102
103 // Validate the path, demonstrating two ways of dealing with errors
104 Path path = securityCheck(name);
105 if (path == null || !Files.exists(path)) {
106 // This makes the COPY command fail but keeps the connection alive.
107 // Can only be used if we haven't sent any data yet
108 handle.sendError("Invalid path");
109 return;
110 }
111 if (!Files.isReadable(path)) {
112 // As opposed to handle.sendError(), throwing an IOException ends the whole connection.
113 throw new IOException("Unreadable: " + path);
114 }
115
116 boolean binary = !textMode;
117 if (binary) {
118 uploadBinary(handle, path);
119 } else if (toSkip == 0 && filesAreUtf8) {
120 // Avoid unnecessary character set conversions by pretending it's binary
121 uploadBinary(handle, path);
122 } else {
123 // Charset and skip handling really necessary
124 uploadTextFile(handle, path, toSkip);
125 }
126 }
127
128 private Path securityCheck(String name) {
129 Path p = uploadDir.resolve(name).normalize();
130 if (p.startsWith(uploadDir)) {
131 return p;
132 } else {
133 return null;
134 }
135 }
136
137 private void uploadGenerated(MonetConnection.Upload handle, long toSkip) throws IOException {
138 PrintStream stream = handle.getStream();
139 for (long i = toSkip + 1; i <= 100; i++) {
140 stream.printf("%d|the number is %d%n", i, i);
141 }
142 stream.close();
143 }
144
145 private void uploadTextFile(MonetConnection.Upload handle, Path path, long toSkip) throws IOException {
146 BufferedReader reader = Files.newBufferedReader(path);// Converts from system encoding to Java text
147 for (long i = 0; i < toSkip; i++) {
148 reader.readLine();
149 }
150 handle.uploadFrom(reader); // Converts from Java text to UTF-8 as required by MonetDB
151 }
152
153 private void uploadBinary(MonetConnection.Upload handle, Path path) throws IOException {
154 // No charset conversion whatsoever..
155 // Use this for binary data or when you are certain the file is UTF-8 encoded.
156 InputStream stream = Files.newInputStream(path);
157 handle.uploadFrom(stream);
158 }
159 }
160 }