Mercurial > hg > monetdb-java

comparison example/MJDBCTest.java @ 0:a5a898f6886c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Copy of MonetDB java directory changeset e6e32756ad31.
author Sjoerd Mullender <sjoerd@acm.org>
date Wed, 21 Sep 2016 09:34:48 +0200 (2016-09-21)
parents
children b9b35ca2eec2
comparison
equal deleted inserted replaced
-1:000000000000 0:a5a898f6886c
1 /*
2 * This Source Code Form is subject to the terms of the Mozilla Public
3 * License, v. 2.0. If a copy of the MPL was not distributed with this
4 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
5 *
6 * Copyright 1997 - July 2008 CWI, August 2008 - 2016 MonetDB B.V.
7 */
8
9 import java.sql.*;
10
11 /**
12 * This example assumes there exists tables a and b filled with some data.
13 * On these tables some queries are executed and the JDBC driver is tested
14 * on it's accuracy and robustness against 'users'.
15 *
16 * @author Fabian Groffen
17 */
18 public class MJDBCTest {
19 public static void main(String[] args) throws Exception {
20 // make sure the driver is loaded
21 Class.forName("nl.cwi.monetdb.jdbc.MonetDriver");
22 // turn on debugging (disabled)
23 //nl.cwi.monetdb.jdbc.MonetConnection.setDebug(true);
24 Connection con = DriverManager.getConnection("jdbc:monetdb://localhost/notused", "monetdb", "monetdb");
25 Statement st = con.createStatement();
26 ResultSet rs;
27
28 rs = st.executeQuery("SELECT a.var1, COUNT(b.id) as total FROM a, b WHERE a.var1 = b.id AND a.var1 = 'andb' GROUP BY a.var1 ORDER BY a.var1, total;");
29 // get meta data and print columns with their type
30 ResultSetMetaData md = rs.getMetaData();
31 for (int i = 1; i <= md.getColumnCount(); i++) {
32 System.out.print(md.getColumnName(i) + ":" +
33 md.getColumnTypeName(i) + "\t");
34 }
35 System.out.println("");
36 // print the data: only the first 5 rows, while there probably are
37 // a lot more. This shouldn't cause any problems afterwards since the
38 // result should get properly discarded on the next query
39 for (int i = 0; rs.next() && i < 5; i++) {
40 for (int j = 1; j <= md.getColumnCount(); j++) {
41 System.out.print(rs.getString(j) + "\t");
42 }
43 System.out.println("");
44 }
45
46 // tell the driver to only return 5 rows, it can optimize on this
47 // value, and will not fetch any more than 5 rows.
48 st.setMaxRows(5);
49 // we ask the database for 22 rows, while we set the JDBC driver to
50 // 5 rows, this shouldn't be a problem at all...
51 rs = st.executeQuery("select * from a limit 22");
52 // read till the driver says there are no rows left
53 for (int i = 0; rs.next(); i++) {
54 System.out.print("[" + rs.getString("var1") + "]");
55 System.out.print("[" + rs.getString("var2") + "]");
56 System.out.print("[" + rs.getInt("var3") + "]");
57 System.out.println("[" + rs.getString("var4") + "]");
58 }
59
60 // this close is not needed, should be done by next execute(Query) call
61 // however if there can be some time between this point and the next
62 // execute call, it is from a resource perspective better to close it.
63 //rs.close();
64
65 // unset the row limit; 0 means as much as the database sends us
66 st.setMaxRows(0);
67 // we only ask 10 rows
68 rs = st.executeQuery("select * from b limit 10;");
69 // and simply print them
70 while (rs.next()) {
71 System.out.print(rs.getInt("rowid") + ", ");
72 System.out.print(rs.getString("id") + ", ");
73 System.out.print(rs.getInt("var1") + ", ");
74 System.out.print(rs.getInt("var2") + ", ");
75 System.out.print(rs.getString("var3") + ", ");
76 System.out.println(rs.getString("var4"));
77 }
78
79 // this close is not needed, as the Statement will close the last
80 // ResultSet around when it's closed
81 // again, if that can take some time, it's nicer to close immediately
82 // the reason why these closes are commented out here, is to test if
83 // the driver really cleans up it's mess like it should
84 //rs.close();
85
86 // perform a ResultSet-less query (with no trailing ; since that should
87 // be possible as well and is JDBC standard)
88 // Note that this method should return the number of updated rows. This
89 // method however always returns -1, since Monet currently doesn't
90 // support returning the affected rows.
91 st.executeUpdate("delete from a where var1 = 'zzzz'");
92
93 // closing the connection should take care of closing all generated
94 // statements from it...
95 // Don't forget to do it yourself if the connection is reused or much
96 // longer alive, since the Statement object contains a lot of things
97 // you probably want to reclaim if you don't need them anymore.
98 //st.close();
99 con.close();
100 }
101 }