Mercurial > hg > monetdb-java

annotate example/MJDBCTest.java @ 226:5ea126405bac

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Update java examples code
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 03 May 2018 18:44:55 +0200 (2018-05-03)
parents c38d4eaf5479
children 51e886d7f3b4 529b92d09fc6
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
0
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
1 /*
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
2 * This Source Code Form is subject to the terms of the Mozilla Public
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
3 * License, v. 2.0. If a copy of the MPL was not distributed with this
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
4 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
5 *
200
c38d4eaf5479 It's 2018 now.
Sjoerd Mullender <sjoerd@acm.org>
parents: 90
diff changeset
6 * Copyright 1997 - July 2008 CWI, August 2008 - 2018 MonetDB B.V.
0
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
7 */
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
8
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
9 import java.sql.*;
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
10
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
11 /**
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
12 * This example assumes there exists tables a and b filled with some data.
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
13 * On these tables some queries are executed and the JDBC driver is tested
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
14 * on it's accuracy and robustness against 'users'.
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
15 *
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
16 * @author Fabian Groffen
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
17 */
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
18 public class MJDBCTest {
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
19 public static void main(String[] args) throws Exception {
226
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
20 String MonetDB_JDBC_URL = "jdbc:monetdb://localhost:50000/demo"; // change host, port and databasename
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
21 Connection con;
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
22 try {
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
23 con = DriverManager.getConnection(MonetDB_JDBC_URL, "monetdb", "monetdb");
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
24 } catch (SQLException e) {
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
25 System.err.println("Failed to connect to MonetDB server! Message: " + e.getMessage());
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
26 }
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
27
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
28 if (con == null) {
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
29 System.err.println("Failed to create a connection object!");
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
30 return;
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
31 }
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
32
0
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
33 Statement st = con.createStatement();
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
34 ResultSet rs;
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
35
226
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
36 String sql = "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;";
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
37 rs = st.executeQuery(sql);
0
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
38 // get meta data and print columns with their type
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
39 ResultSetMetaData md = rs.getMetaData();
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
40 for (int i = 1; i <= md.getColumnCount(); i++) {
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
41 System.out.print(md.getColumnName(i) + ":" +
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
42 md.getColumnTypeName(i) + "\t");
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
43 }
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
44 System.out.println("");
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
45 // print the data: only the first 5 rows, while there probably are
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
46 // a lot more. This shouldn't cause any problems afterwards since the
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
47 // result should get properly discarded on the next query
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
48 for (int i = 0; rs.next() && i < 5; i++) {
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
49 for (int j = 1; j <= md.getColumnCount(); j++) {
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
50 System.out.print(rs.getString(j) + "\t");
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
51 }
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
52 System.out.println("");
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
53 }
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
54
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
55 // tell the driver to only return 5 rows, it can optimize on this
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
56 // value, and will not fetch any more than 5 rows.
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
57 st.setMaxRows(5);
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
58 // we ask the database for 22 rows, while we set the JDBC driver to
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
59 // 5 rows, this shouldn't be a problem at all...
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
60 rs = st.executeQuery("select * from a limit 22");
226
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
61 int var1_cnr = rs.findColumn("var1");
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
62 int var2_cnr = rs.findColumn("var2");
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
63 int var3_cnr = rs.findColumn("var3");
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
64 int var4_cnr = rs.findColumn("var4");
0
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
65 // read till the driver says there are no rows left
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
66 for (int i = 0; rs.next(); i++) {
226
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
67 System.out.println(
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
68 "[" + rs.getString(var1_cnr) + "]" +
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
69 "[" + rs.getString(var2_cnr) + "]" +
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
70 "[" + rs.getInt(var3_cnr) + "]" +
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
71 "[" + rs.getString(var4_cnr) + "]" );
0
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
72 }
226
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
73
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
74 // this rs.close is not needed, should be done by next execute(Query) call
0
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
75 // however if there can be some time between this point and the next
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
76 // execute call, it is from a resource perspective better to close it.
226
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
77 rs.close();
0
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
78
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
79 // unset the row limit; 0 means as much as the database sends us
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
80 st.setMaxRows(0);
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
81 // we only ask 10 rows
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
82 rs = st.executeQuery("select * from b limit 10;");
226
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
83 int rowid_cnr = rs.findColumn("rowid");
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
84 int id_cnr = rs.findColumn("id");
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
85 var1_cnr = rs.findColumn("var1");
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
86 var2_cnr = rs.findColumn("var2");
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
87 var3_cnr = rs.findColumn("var3");
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
88 var4_cnr = rs.findColumn("var4");
0
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
89 // and simply print them
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
90 while (rs.next()) {
226
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
91 System.out.println(
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
92 rs.getInt(rowid_cnr) + ", " +
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
93 rs.getString(id_cnr) + ", " +
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
94 rs.getInt(var1_cnr) + ", " +
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
95 rs.getInt(var2_cnr) + ", " +
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
96 rs.getString(var3_cnr) + ", " +
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
97 rs.getString(var4_cnr) );
0
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
98 }
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
99
226
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
100 // this close is not required, as the Statement will close the last
0
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
101 // ResultSet around when it's closed
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
102 // again, if that can take some time, it's nicer to close immediately
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
103 // the reason why these closes are commented out here, is to test if
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
104 // the driver really cleans up it's mess like it should
226
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
105 rs.close();
0
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
106
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
107 // perform a ResultSet-less query (with no trailing ; since that should
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
108 // be possible as well and is JDBC standard)
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
109 // Note that this method should return the number of updated rows. This
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
110 // method however always returns -1, since Monet currently doesn't
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
111 // support returning the affected rows.
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
112 st.executeUpdate("delete from a where var1 = 'zzzz'");
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
113
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
114 // Don't forget to do it yourself if the connection is reused or much
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
115 // longer alive, since the Statement object contains a lot of things
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
116 // you probably want to reclaim if you don't need them anymore.
226
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
117 st.close();
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
118 // closing the connection should take care of closing all generated
5ea126405bac Update java examples code
Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
parents: 200
diff changeset
119 // statements from it...
0
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
120 con.close();
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
121 }
a5a898f6886c Copy of MonetDB java directory changeset e6e32756ad31.
Sjoerd Mullender <sjoerd@acm.org>
parents:
diff changeset
122 }