KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > examples > db


1 package examples.db;
2
3 import java.sql.Connection;
4 import java.sql.ResultSet;
5
6 import jodd.bean.BeanUtil;
7 import jodd.db.SqlUtil;
8 import jodd.db.pool.CoreConnectionPool;
9 import jodd.db.profile.SqlUtilProfiler;
10
11 public class db {
12
13     public static void main(String args[]) throws Exception {
14
15         System.out.println("\n---db connectivity example---------------------------------------------");
16
17         System.out.println("pool initialization...");
18         CoreConnectionPool pool = new CoreConnectionPool();
19         pool.setDriver("org.hsqldb.jdbcDriver");
20         pool.setUrl("jdbc:hsqldb:exampledb");
21         pool.setUser("sa");
22         pool.setPassword("");
23         pool.init();
24         System.out.println("pool ok");
25
26
27         System.out.println("\n---create insert example-----------------------------------------------");
28
29         // initial data
30 SqlUtil dbsql = new SqlUtil(pool);
31         dbsql.setStaticSql("create table JODD_GIRLS (" +
32                            "GIRL_ID integer not null," +
33                            "USER_NAME varchar(20) not null," +
34                            "SPECIALITY varchar(60) null," +
35                            "primary key (girl_id)" +
36                            ")");
37         System.out.println("create table: " + dbsql.executeUpdate());
38         dbsql.setStaticSql("insert into JODD_GIRLS values(1, 'Anna', 'seducive')");
39         System.out.println("insert #1: " + dbsql.executeUpdate());
40         dbsql.setStaticSql("insert into JODD_GIRLS values(2, 'Marie', 'spy')");
41         System.out.println("insert #2: " + dbsql.executeUpdate());
42         dbsql.setStaticSql("insert into JODD_GIRLS values(3, 'Monica', 'hi-tech')");
43         System.out.println("insert #3: " + dbsql.executeUpdate());
44         dbsql.close();
45
46
47         System.out.println("\n---sqlutil and profiler example----------------------------------------");
48         SqlUtilProfiler.handler = new SimpleProfilerHandler();
49         dbsql = new SqlUtilProfiler(pool);
50         SqlUtil.setDebugMode(true);
51         dbsql.setSql("select * from JODD_GIRLS where GIRL_ID = ?");
52         dbsql.setInt(1, 3);
53         System.out.println("sql query prepared:");
54         System.out.println(dbsql.toString());
55         ResultSet rs = dbsql.executeQuery();
56         System.out.println("query results:");
57         while (rs.next()) {
58             System.out.println(rs.getInt(1));
59             System.out.println(rs.getString(2));
60             System.out.println(rs.getString(2));
61         }
62         dbsql.close();
63
64         System.out.println("\n---sqlutil and connection example--------------------------------------");
65         Connection conn = pool.getConnection();
66         SqlUtil dbsqlc = new SqlUtil(conn);
67         dbsqlc.setStaticSql("select count(*) from JODD_GIRLS");
68         rs = dbsqlc.executeQuery();
69         if (rs.next()) {
70             System.out.println("result: " + rs.getInt(1));
71         }
72         dbsqlc.close();
73         // dbsqlc2.closeAll(); // DO NOT CALL closeAll() HERE, SINCE IT WILL CLOSE THE CONNECTION
74 // AND CONNECTION HAS TO BE RETURNED TO THE POOL INSTEAD
75 pool.freeConnection(conn);
76         
77
78         System.out.println("\n---bean util example---------------------------------------------------");
79         dbsql.setStaticSql("select * from JODD_GIRLS where USER_NAME like 'M%'");
80         System.out.println("static sql query:");
81         System.out.println(dbsql.toString());
82         rs = dbsql.executeQuery();
83         System.out.println("query results:");
84         while (rs.next()) {
85             GirlEntityBean girl = new GirlEntityBean();
86             BeanUtil.load(girl, rs);
87             System.out.println(BeanUtil.toString(girl));
88         }
89         dbsql.close();
90         dbsql.closeAll();
91
92
93         System.out.println("\n---transaction example-------------------------------------------------");
94         SqlUtil dbsql1 = new SqlUtil(pool);
95         SqlUtil dbsql2 = new SqlUtil(pool);
96
97         dbsql1.setAutoCommit(false);
98         dbsql1.setStaticSql("insert into JODD_GIRLS values(4, 'Jeniffer', 'soft and sensual')");
99         System.out.println("auto-commit #1: " + dbsql1.getAutoCommit());
100         dbsql1.executeUpdate();
101         dbsql1.close();
102
103         dbsql2.setStaticSql("select count(*) from JODD_GIRLS");
104         System.out.println("auto-commit #2: " + dbsql2.getAutoCommit());
105         rs = dbsql2.executeQuery();
106         if (rs.next()) {
107             System.out.println("count before rollback (READ_UNCOMMITTED isolation level): " + rs.getInt(1));
108         }
109         
110         // HSQLDB supports transactions at the READ_UNCOMMITTED level, also known
111 // as level 0 transaction isolation. This means that during the lifetime of
112 // a transaction, other connections to the database can see the changes made
113 // to the data
114
115         dbsql1.rollback();
116
117         //dbsql2.close(); // tip
118 //dbsql2.setStaticSql("select count(*) from JODD_GIRLS"); // tip
119 rs = dbsql2.executeQuery();
120         if (rs.next()) {
121             System.out.println("count after rollback: " + rs.getInt(1));
122         }
123         dbsql2.close();
124
125         System.out.println("close all");
126         dbsql1.closeAll();
127         dbsql2.closeAll();
128         
129         pool.close();
130     }
131 }
132
Popular Tags