db.java

Jodd是一个开源的公用Java基础类库

package examples.db;

import java.sql.Connection;
import java.sql.ResultSet;

import jodd.bean.BeanUtil;
import jodd.db.SqlUtil;
import jodd.db.pool.CoreConnectionPool;
import jodd.db.profile.SqlUtilProfiler;

public class db {

	public static void main(String args[]) throws Exception {

		System.out.println("\n---db connectivity example---------------------------------------------");

		System.out.println("pool initialization...");
		CoreConnectionPool pool = new CoreConnectionPool();
		pool.setDriver("org.hsqldb.jdbcDriver");
		pool.setUrl("jdbc:hsqldb:exampledb");
		pool.setUser("sa");
		pool.setPassword("");
		pool.init();
		System.out.println("pool ok");


		System.out.println("\n---create insert example-----------------------------------------------");

		// initial data
		SqlUtil dbsql = new SqlUtil(pool);
		dbsql.setStaticSql("create table JODD_GIRLS (" +
						   "GIRL_ID        integer not null," +
						   "USER_NAME      varchar(20) not null," +
						   "SPECIALITY     varchar(60) null," +
						   "primary key (girl_id)" +
						   ")");
		System.out.println("create table: " + dbsql.executeUpdate());
		dbsql.setStaticSql("insert into JODD_GIRLS values(1, 'Anna', 'seducive')");
		System.out.println("insert #1: " + dbsql.executeUpdate());
		dbsql.setStaticSql("insert into JODD_GIRLS values(2, 'Marie', 'spy')");
		System.out.println("insert #2: " + dbsql.executeUpdate());
		dbsql.setStaticSql("insert into JODD_GIRLS values(3, 'Monica', 'hi-tech')");
		System.out.println("insert #3: " + dbsql.executeUpdate());
		dbsql.close();


		System.out.println("\n---sqlutil and profiler example----------------------------------------");
		SqlUtilProfiler.handler = new SimpleProfilerHandler();		
		dbsql = new SqlUtilProfiler(pool);
		SqlUtil.setDebugMode(true);
		dbsql.setSql("select * from JODD_GIRLS where GIRL_ID = ?");
		dbsql.setInt(1, 3);
		System.out.println("sql query prepared:");
		System.out.println(dbsql.toString());
		ResultSet rs = dbsql.executeQuery();
		System.out.println("query results:");
		while (rs.next()) {
			System.out.println(rs.getInt(1));
			System.out.println(rs.getString(2));
			System.out.println(rs.getString(2));
		}
		dbsql.close();

		System.out.println("\n---sqlutil and connection example--------------------------------------");
		Connection conn = pool.getConnection();
		SqlUtil dbsqlc = new SqlUtil(conn);
		dbsqlc.setStaticSql("select count(*) from JODD_GIRLS");
		rs = dbsqlc.executeQuery();
		if (rs.next()) {
			System.out.println("result: " + rs.getInt(1));			
		}
		dbsqlc.close();
		// dbsqlc2.closeAll();		// DO NOT CALL closeAll() HERE, SINCE IT WILL CLOSE THE CONNECTION
									// AND CONNECTION HAS TO BE RETURNED TO THE POOL INSTEAD
		pool.freeConnection(conn);
		

		System.out.println("\n---bean util example---------------------------------------------------");
		dbsql.setStaticSql("select * from JODD_GIRLS where USER_NAME like 'M%'");
		System.out.println("static sql query:");
		System.out.println(dbsql.toString());
		rs = dbsql.executeQuery();
		System.out.println("query results:");
		while (rs.next()) {
			GirlEntityBean girl = new GirlEntityBean();
			BeanUtil.load(girl, rs);
			System.out.println(BeanUtil.toString(girl));
		}
		dbsql.close();
		dbsql.closeAll();


		System.out.println("\n---transaction example-------------------------------------------------");
		SqlUtil dbsql1 = new SqlUtil(pool);
		SqlUtil dbsql2 = new SqlUtil(pool);

		dbsql1.setAutoCommit(false);
		dbsql1.setStaticSql("insert into JODD_GIRLS values(4, 'Jeniffer', 'soft and sensual')");
		System.out.println("auto-commit #1: " + dbsql1.getAutoCommit());
		dbsql1.executeUpdate();
		dbsql1.close();

		dbsql2.setStaticSql("select count(*) from JODD_GIRLS");
		System.out.println("auto-commit #2: " + dbsql2.getAutoCommit());
		rs = dbsql2.executeQuery();
		if (rs.next()) {
			System.out.println("count before rollback (READ_UNCOMMITTED isolation level): " + rs.getInt(1));
		}
		
		// HSQLDB supports transactions at the READ_UNCOMMITTED level, also known
		// as level 0 transaction isolation.  This means that during the lifetime of
		// a transaction, other connections to the database can see the changes made
		// to the data

		dbsql1.rollback();

		//dbsql2.close();											// tip
		//dbsql2.setStaticSql("select count(*) from JODD_GIRLS");	// tip
		rs = dbsql2.executeQuery();
		if (rs.next()) {
			System.out.println("count after rollback: " + rs.getInt(1));
		}
		dbsql2.close();

		System.out.println("close all");
		dbsql1.closeAll();
		dbsql2.closeAll();
		
		pool.close();
	}
}