Mercurial > hg > monetdb-java

comparison 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
comparison
equal deleted inserted replaced
225:5ddb1f20d5d5 226:5ea126405bac
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 // make sure the driver is loaded 20 String MonetDB_JDBC_URL = "jdbc:monetdb://localhost:50000/demo"; // change host, port and databasename
21 Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); 21 Connection con;
22 // turn on debugging (disabled) 22 try {
23 //nl.cwi.monetdb.jdbc.MonetConnection.setDebug(true); 23 con = DriverManager.getConnection(MonetDB_JDBC_URL, "monetdb", "monetdb");
24 Connection con = DriverManager.getConnection("jdbc:monetdb://localhost/notused", "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
25 Statement st = con.createStatement(); 33 Statement st = con.createStatement();
26 ResultSet rs; 34 ResultSet rs;
27 35
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;"); 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);
29 // get meta data and print columns with their type 38 // get meta data and print columns with their type
30 ResultSetMetaData md = rs.getMetaData(); 39 ResultSetMetaData md = rs.getMetaData();
31 for (int i = 1; i <= md.getColumnCount(); i++) { 40 for (int i = 1; i <= md.getColumnCount(); i++) {
32 System.out.print(md.getColumnName(i) + ":" + 41 System.out.print(md.getColumnName(i) + ":" +
33 md.getColumnTypeName(i) + "\t"); 42 md.getColumnTypeName(i) + "\t");
47 // value, and will not fetch any more than 5 rows. 56 // value, and will not fetch any more than 5 rows.
48 st.setMaxRows(5); 57 st.setMaxRows(5);
49 // 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
50 // 5 rows, this shouldn't be a problem at all... 59 // 5 rows, this shouldn't be a problem at all...
51 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");
52 // read till the driver says there are no rows left 65 // read till the driver says there are no rows left
53 for (int i = 0; rs.next(); i++) { 66 for (int i = 0; rs.next(); i++) {
54 System.out.print("[" + rs.getString("var1") + "]"); 67 System.out.println(
55 System.out.print("[" + rs.getString("var2") + "]"); 68 "[" + rs.getString(var1_cnr) + "]" +
56 System.out.print("[" + rs.getInt("var3") + "]"); 69 "[" + rs.getString(var2_cnr) + "]" +
57 System.out.println("[" + rs.getString("var4") + "]"); 70 "[" + rs.getInt(var3_cnr) + "]" +
71 "[" + rs.getString(var4_cnr) + "]" );
58 } 72 }
59 73
60 // 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
61 // 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
62 // 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.
63 //rs.close(); 77 rs.close();
64 78
65 // 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
66 st.setMaxRows(0); 80 st.setMaxRows(0);
67 // we only ask 10 rows 81 // we only ask 10 rows
68 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");
69 // and simply print them 89 // and simply print them
70 while (rs.next()) { 90 while (rs.next()) {
71 System.out.print(rs.getInt("rowid") + ", "); 91 System.out.println(
72 System.out.print(rs.getString("id") + ", "); 92 rs.getInt(rowid_cnr) + ", " +
73 System.out.print(rs.getInt("var1") + ", "); 93 rs.getString(id_cnr) + ", " +
74 System.out.print(rs.getInt("var2") + ", "); 94 rs.getInt(var1_cnr) + ", " +
75 System.out.print(rs.getString("var3") + ", "); 95 rs.getInt(var2_cnr) + ", " +
76 System.out.println(rs.getString("var4")); 96 rs.getString(var3_cnr) + ", " +
97 rs.getString(var4_cnr) );
77 } 98 }
78 99
79 // 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
80 // ResultSet around when it's closed 101 // ResultSet around when it's closed
81 // 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
82 // 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
83 // the driver really cleans up it's mess like it should 104 // the driver really cleans up it's mess like it should
84 //rs.close(); 105 rs.close();
85 106
86 // perform a ResultSet-less query (with no trailing ; since that should 107 // perform a ResultSet-less query (with no trailing ; since that should
87 // be possible as well and is JDBC standard) 108 // be possible as well and is JDBC standard)
88 // 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
89 // method however always returns -1, since Monet currently doesn't 110 // method however always returns -1, since Monet currently doesn't
90 // support returning the affected rows. 111 // support returning the affected rows.
91 st.executeUpdate("delete from a where var1 = 'zzzz'"); 112 st.executeUpdate("delete from a where var1 = 'zzzz'");
92 113
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 114 // 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 115 // longer alive, since the Statement object contains a lot of things
97 // 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.
98 //st.close(); 117 st.close();
118 // closing the connection should take care of closing all generated
119 // statements from it...
99 con.close(); 120 con.close();
100 } 121 }
101 } 122 }