5801
Comment:
|
5876
|
Deletions are marked like this. | Additions are marked like this. |
Line 12: | Line 12: |
Posted on the Jython-users mailing list by Alfonso Reyes, September 2007 | |
Line 16: | Line 16: |
["RSMD.py"] a database utility I found in IBM. [[BR]] | ["RSMD.py"] is a database utility I found in IBM. [[BR]] |
Apache Derby Example(s)
back to OtherExamples
-- Posted on the Jython-users mailing list by Alfonso Reyes, September 2007
This Derby example doesn't need the use of zxJDBC. I was looking to complete the exercises in the book "Python programming with Java libraries" but couldn't find anywhere a site to download InstantDB. Trying Apache Derby was quite a pleasant experience. I am attaching files here.
["RSMD.py"] is a database utility I found in IBM. BR SimpleApp.java the original Java demo
If you follow the instructions in this tutorial everything will go smooth. [http://db.apache.org/derby/papers/DerbyTut/install_software.html Derby Tutorial]. (note: there is also a lot of info on their wiki)
1 """ Derby Class SimpleApp.py
2 Add derby.jar to your CLASSPATH
3 Ported by: Alfonso Reyes, September 2007
4
5 """
6 from RSMD import printRSMD, RSMD # nice methods I downloaded from IBM site
7 from java.sql import Connection;
8 from java.sql import DriverManager;
9 from java.sql import ResultSet;
10 from java.sql import SQLException;
11 from java.sql import Statement;
12
13 from java.util import Properties;
14 from java.lang import Class
15
16 class SimpleApp:
17 def __init__(self):
18 # the default framework is embedded
19 self.framework = "embedded";
20 self.driver = "org.apache.derby.jdbc.EmbeddedDriver";
21 self.protocol = "jdbc:derby:";
22 self.username = "user1";
23 self.password = "user1";
24
25 def go(self):
26 # parse the arguments to determine which framework is desired
27 #parseArguments(args);
28 """ The driver is installed by loading its class.
29 In an embedded environment, this will start up Derby, since it is not already running.
30
31 """
32 ds = None;
33 conn = None;
34 props = Properties();
35 props.put("user", self.username);
36 props.put("password", self.password);
37 print props
38
39 # check for J2ME specification - J2ME must use a DataSource further on */
40 javaspec = props.getProperty( "java.specification.name" );
41
42 """
43 The connection specifies create=true in the url to cause
44 the database to be created. To remove the database,
45 remove the directory derbyDB and its contents.
46 The directory derbyDB will be created under
47 the directory that the system property
48 derby.system.home points to, or the current
49 directory if derby.system.home is not set.
50
51 """
52 Class.forName(self.driver).newInstance();
53 print "Loaded the appropriate driver."
54
55 database = "derbyDB5" # put here the name for your database
56 dbStr = self.protocol + database + ";create=true"
57 print dbStr
58 conn = DriverManager.getConnection(dbStr, props);
59 print "Connected to and created database derbyDB"
60
61 conn.setAutoCommit(False);
62
63 """ Creating a statement lets us issue commands against
64 the connection.
65
66 """
67 s = conn.createStatement();
68
69 # We create a table, add a few rows, and update one.
70 s.execute("create table derbyDB(num int, addr varchar(40))");
71 print "Created table derbyDB"
72 s.execute("insert into derbyDB values (1956,'Webster St.')");
73 print "Inserted 1956 Webster"
74 s.execute("insert into derbyDB values (1910,'Union St.')");
75 print "Inserted 1910 Union"
76 s.execute("insert into derbyDB values (1,'Wandering Oak')");
77 print "Inserted 1 Wandering Oak"
78
79 s.execute(
80 "update derbyDB set num=180, addr='Grand Ave.' where num=1956");
81 print "Updated 1956 Webster to 180 Grand"
82
83 s.execute(
84 "update derbyDB set num=300, addr='Lakeshore Ave.' where num=180");
85 print "Updated 180 Grand to 300 Lakeshore"
86
87 # We select the rows and verify the results.
88 rs = s.executeQuery("SELECT num, addr FROM derbyDB ORDER BY num");
89
90 print "Verified the rows"
91 stmt = conn.createStatement()
92 Query = 'SELECT * FROM derbyDB'
93 rs = stmt.executeQuery( Query )
94 rsmd = RSMD( rs )
95 printRSMD( rsmd, Query )
96
97 rowCount = 0
98 while ( rs.next() ) :
99 rowCount += 1
100 row = ( rs.getInt( 1 ), rs.getString( 2 ) )
101 print row
102
103 stmt.close() # close stmt connection
104 s.execute("drop table derbyDB");
105 print"Dropped table derbyDB"
106
107 # We release the result and statement resources.
108 rs.close();
109 s.close();
110 print "Closed result set and statements"
111
112 # We end the transaction and the connection.
113 conn.commit();
114 conn.close();
115 print "Committed transaction and closed connection"
116
117 """ In embedded mode, an application should shut down Derby.
118 If the application fails to shut down Derby explicitly,
119 the Derby does not perform a checkpoint when the JVM shuts down, which means
120 that the next connection will be slower.
121 Explicitly shutting down Derby with the URL is preferred.
122 This style of shutdown will always throw an "exception".
123
124 """
125 gotSQLExc = False;
126 try:
127 DriverManager.getConnection("jdbc:derby:;shutdown=true")
128 except SQLException:
129 print "Catching exceptions"
130 gotSQLExc = True;
131
132 if (not gotSQLExc):
133 print "Database did not shut down normally"
134 else:
135 print "Database shut down normally"
136 print("SimpleApp finished");
137
138 if __name__ == '__main__':
139 SimpleApp().go();