Mercurial > hg > monetdb-java

comparison example/MJDBCTest.java @ 228:51e886d7f3b4 embedded

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge with default
author Pedro Ferreira <pedro.ferreira@monetdbsolutions.com>
date Tue, 08 May 2018 19:04:17 +0200 (2018-05-08)
parents 5b13ccaba741 5ea126405bac
children 4face9f42efc
comparison
equal deleted inserted replaced
227:e938bb2c587f 228:51e886d7f3b4
15 * 15 *
16 * @author Fabian Groffen 16 * @author Fabian Groffen
17 */ 17 */
18 public class MJDBCTest { 18 public class MJDBCTest {
19 public static void main(String[] args) throws Exception { 19 public static void main(String[] args) throws Exception {
20 //Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); 20 String MonetDB_JDBC_URL = "jdbc:monetdb://localhost:50000/demo"; // change host, port and databasename
21 // turn on debugging (disabled) 21 Connection con;
22 //nl.cwi.monetdb.jdbc.MonetConnection.setDebug(true); 22 try {
23 Connection con = DriverManager.getConnection("jdbc:monetdb://localhost/notused", "monetdb", "monetdb"); 23 con = DriverManager.getConnection(MonetDB_JDBC_URL, "monetdb", "monetdb");
24 } catch (SQLException e) {
25 System.err.println("Failed to connect to MonetDB server! Message: " + e.getMessage());
26 }
27
28 if (con == null) {
29 System.err.println("Failed to create a connection object!");
30 return;
31 }
32
24 Statement st = con.createStatement(); 33 Statement st = con.createStatement();
25 ResultSet rs; 34 ResultSet rs;
26 35
27 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;"); 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;";
37 rs = st.executeQuery(sql);
28 // get meta data and print columns with their type 38 // get meta data and print columns with their type
29 ResultSetMetaData md = rs.getMetaData(); 39 ResultSetMetaData md = rs.getMetaData();
30 for (int i = 1; i <= md.getColumnCount(); i++) { 40 for (int i = 1; i <= md.getColumnCount(); i++) {
31 System.out.print(md.getColumnName(i) + ":" + 41 System.out.print(md.getColumnName(i) + ":" +
32 md.getColumnTypeName(i) + "\t"); 42 md.getColumnTypeName(i) + "\t");
46 // value, and will not fetch any more than 5 rows. 56 // value, and will not fetch any more than 5 rows.
47 st.setMaxRows(5); 57 st.setMaxRows(5);
48 // we ask the database for 22 rows, while we set the JDBC driver to 58 // we ask the database for 22 rows, while we set the JDBC driver to
49 // 5 rows, this shouldn't be a problem at all... 59 // 5 rows, this shouldn't be a problem at all...
50 rs = st.executeQuery("select * from a limit 22"); 60 rs = st.executeQuery("select * from a limit 22");
61 int var1_cnr = rs.findColumn("var1");
62 int var2_cnr = rs.findColumn("var2");
63 int var3_cnr = rs.findColumn("var3");
64 int var4_cnr = rs.findColumn("var4");
51 // read till the driver says there are no rows left 65 // read till the driver says there are no rows left
52 for (int i = 0; rs.next(); i++) { 66 for (int i = 0; rs.next(); i++) {
53 System.out.print("[" + rs.getString("var1") + "]"); 67 System.out.println(
54 System.out.print("[" + rs.getString("var2") + "]"); 68 "[" + rs.getString(var1_cnr) + "]" +
55 System.out.print("[" + rs.getInt("var3") + "]"); 69 "[" + rs.getString(var2_cnr) + "]" +
56 System.out.println("[" + rs.getString("var4") + "]"); 70 "[" + rs.getInt(var3_cnr) + "]" +
71 "[" + rs.getString(var4_cnr) + "]" );
57 } 72 }
58 73
59 // this close is not needed, should be done by next execute(Query) call 74 // this rs.close is not needed, should be done by next execute(Query) call
60 // however if there can be some time between this point and the next 75 // however if there can be some time between this point and the next
61 // execute call, it is from a resource perspective better to close it. 76 // execute call, it is from a resource perspective better to close it.
62 //rs.close(); 77 rs.close();
63 78
64 // unset the row limit; 0 means as much as the database sends us 79 // unset the row limit; 0 means as much as the database sends us
65 st.setMaxRows(0); 80 st.setMaxRows(0);
66 // we only ask 10 rows 81 // we only ask 10 rows
67 rs = st.executeQuery("select * from b limit 10;"); 82 rs = st.executeQuery("select * from b limit 10;");
83 int rowid_cnr = rs.findColumn("rowid");
84 int id_cnr = rs.findColumn("id");
85 var1_cnr = rs.findColumn("var1");
86 var2_cnr = rs.findColumn("var2");
87 var3_cnr = rs.findColumn("var3");
88 var4_cnr = rs.findColumn("var4");
68 // and simply print them 89 // and simply print them
69 while (rs.next()) { 90 while (rs.next()) {
70 System.out.print(rs.getInt("rowid") + ", "); 91 System.out.println(
71 System.out.print(rs.getString("id") + ", "); 92 rs.getInt(rowid_cnr) + ", " +
72 System.out.print(rs.getInt("var1") + ", "); 93 rs.getString(id_cnr) + ", " +
73 System.out.print(rs.getInt("var2") + ", "); 94 rs.getInt(var1_cnr) + ", " +
74 System.out.print(rs.getString("var3") + ", "); 95 rs.getInt(var2_cnr) + ", " +
75 System.out.println(rs.getString("var4")); 96 rs.getString(var3_cnr) + ", " +
97 rs.getString(var4_cnr) );
76 } 98 }
77 99
78 // this close is not needed, as the Statement will close the last 100 // this close is not required, as the Statement will close the last
79 // ResultSet around when it's closed 101 // ResultSet around when it's closed
80 // again, if that can take some time, it's nicer to close immediately 102 // again, if that can take some time, it's nicer to close immediately
81 // the reason why these closes are commented out here, is to test if 103 // the reason why these closes are commented out here, is to test if
82 // the driver really cleans up it's mess like it should 104 // the driver really cleans up it's mess like it should
83 //rs.close(); 105 rs.close();
84 106
85 // perform a ResultSet-less query (with no trailing ; since that should 107 // perform a ResultSet-less query (with no trailing ; since that should
86 // be possible as well and is JDBC standard) 108 // be possible as well and is JDBC standard)
87 // Note that this method should return the number of updated rows. This 109 // Note that this method should return the number of updated rows. This
88 // method however always returns -1, since Monet currently doesn't 110 // method however always returns -1, since Monet currently doesn't
89 // support returning the affected rows. 111 // support returning the affected rows.
90 st.executeUpdate("delete from a where var1 = 'zzzz'"); 112 st.executeUpdate("delete from a where var1 = 'zzzz'");
91 113
92 // closing the connection should take care of closing all generated
93 // statements from it...
94 // Don't forget to do it yourself if the connection is reused or much 114 // Don't forget to do it yourself if the connection is reused or much
95 // longer alive, since the Statement object contains a lot of things 115 // longer alive, since the Statement object contains a lot of things
96 // you probably want to reclaim if you don't need them anymore. 116 // you probably want to reclaim if you don't need them anymore.
97 //st.close(); 117 st.close();
118 // closing the connection should take care of closing all generated
119 // statements from it...
98 con.close(); 120 con.close();
99 } 121 }
100 } 122 }