db
Product: xTier™/LWC 2.3 Whitepaper: n/a	support@fitechlabs.com  Download  Buy
Depends: log  tx  jndi  Related: cache  tx
xTier™ Navigator: cache cluster config email i18n ioc info jmx jndi security log marshal objpool os fs tx uidgen workflow jobs db startup jms grid

Description
'db' service is an easy-to-use and powerful tool for working with databases. It implements many well-known and often practiced JDBC usage patterns. Note that the goal of this service is not to replace JDBC API but to provide a complementary implementation for some frequently used JDBC design patterns. Main features of the 'db' service include but not limited to:

• Build-in support for major databases.
• Automation of common database operations.
• IoC-based XML configuration.
• Simplified JDBC resources management.
• Convinient SQL errors classification.
• JDBC 3.0 features support.
• Support for clean separation of SQL code from Java.

 Top

Configuration
'db' service acts as a registry for supported databases and data sources. Supported databases (and their error codes) can be defined in service XML configuration file only. Data sources can be either added at runtime or specified in XML configuration. Data sources specified in configuration are created automatically at the moment of first usage and can be accessed at runtime by their names. Service also acts as a factory and as a container for the database sessions. Once session is created, it is registered in the service and can later be retrived by the name. Application developers can separate all SQL code from source code and 'predefine' it in XML configuration. Predefined SQL statements can be retrieved at runtime by their names.

'db' service is configured via pre-defined xtier_db.xml configuration file. This file follows standard xTier™ service configuration pattern that can be demonstrated by the following complete example of db configuration:

1		<xtier-db>
2		<!--
3		RDBMS error codes mappings. Note that
4		this mapping is moved out of any region
5		as it belongs not to region but to the
6		service as a whole.
7		-->
8		<supported-dbs>
9		<!--
10		Error codes mapping for Oracle.
11		-->
12		<db name="oracle9i">
13		<err-codes>
14		<bad-sql>
15		900, 903, 904, 917, 936, 942,
16		17006
17		</bad-sql>
18		<data-integrity>
19		1, 1400, 1722, 2291
20		</data-integrity>
21		<opt-lock-failure></opt-lock-failure>
22		<too-many-rows>1422</too-many-rows>
23		<no-data-found>1403</no-data-found>
24		<db-not-avail></db-not-avail>
25		</err-codes>
26		</db>
27
28		<!--
29		Error codes mapping for IBM DB2.
30		-->
31		<db name="db2">
32		<err-codes>
33		<bad-sql>
34		-204, -206, -301, -408
35		</bad-sql>
36		<data-integrity>
37		-803
38		</data-integrity>
39		</err-codes>
40		</db>
41		</supported-dbs>
42
43		<!-- Examples region. -->
44		<region name="examples">
45		<!--
46		IoC-based data source.
47		Change URL argument according your
48		database settings.
49		-->
50		<ds name="ora-ioc-ds" db-type="oracle9i">
51		<ioc-ds>
52		<ioc uid="oracle-xa-ds" policy="new">
53		<java class="oracle.jdbc.xa.client.
54		OracleXADataSource">
55		<call method="setURL">
56		<arg type="string">
57		jdbc:oracle:thin:username/
58		password@server-name:
59		port:sid
60		</arg>
61		</call>
62		</java>
63		</ioc>
64		</ioc-ds>
65		<config
66		log-warns="true"
67		log-stmts="true"
68		raise-warns="true"
69		username="test"
70		password="test"
71		/>
72		</ds>
73
74		<!--
75		This insert statement expects parameter as
76		either java.util.Map or a JavaBean with keys
77		"id","firstname" and "lastname" in the map,
78		or public methods "getId()", "getFirstname()"
79		and "getLastname()" in JavaBean correspondently.
80		Note that method matching is NOT case sensitive.
81
82		Note that if values in the map or values of
83		JavaBean properites are 'null', they will be
84		substituted by SQL 'NULL'.
85		-->
86		<insert name="insert-user">
87		<sql>
88		<![CDATA[
89		insert into
90		users_table(id, firstname, lastname)
91		values
92		(
93		?{id},
94		?{firstname},
95		?{lastname}
96		)
97
98		]]>
99		</sql>
100		</insert>
101
102		<!--
103		This select statement expects parameter as either
104		java.util.Map or a JavaBean with key "id" in the
105		map, or public methods "getId()" in JavaBean
106		correspondently. Note that method matching is NOT
107		case sensitive.
108
109		Note that if values in the map or values of JavaBean
110		properites are null, they will be substituted by
111		SQL 'NULL'.
112
113		Result will be returned as JavaBean 'com.fitechlabs.
114		xtier.examples.services.db.DbUserObjectExample' class
115		instance. This class must contain "setId()",
116		"setFirstname()" and "setLastname()" methods.
117		-->
118		<select name="get-user">
119		<sql>
120		<![CDATA[
121		select
122		id,
123		firstname,
124		lastname
125		from
126		users_table
127		where
128		firstname like ?{firstname}
129		]]>
130		</sql>
131
132		<ioc-result>
133		<ioc uid="user.ioc" policy="new">
134		<java class="com.fitechlabs.xtier.examples.
135		services.db.DbUserObjectExample"/>
136		</ioc>
137		</ioc-result>
138		</select>
139		</region>
140		</xtier-db>

'db' configuration region consists of 3 sections. First section defines supported databases and contains error codes mapping information. This section is common for all regions and corresponds to <supported-dbs> tag and every supported database if defined by <db> tag. <db> XML tag has the required attribute name and includes tags <err-codes> which defines error codes mapping information. Follwing types of SQL errors are supported by corresponding tags:

• <bad-sql>
• <data-integrity>
• <opt-lock-failure>
• <too-many-rows>
• <no-data-found>
• <db-not-avail>

Next section of configuration file is specific for configuration region and contains definitions of data sources. Data source must have name unique in the configuration region (attribute name). Data source must also have type (attribute db-type which references a name of supported databases (see first section of configuration file). Data source itself can be defined via JNDI or using xTier™ IoC technique.

Optional data source parameters:

• log-warns - true if SQL warnings will be logged while working with this data source.
• log-stmts - true if SQL statements will be logged while working with this data source.
• raise-warns - true if DbException should be raised on any SQLWarning while working with this data source.
• username - User name.
• password - User's password.

Last section of configuration file is also region-specific and contains definitions of SQL statements. There are 5 types of SQL statements and therefore 5 tags to predefine them:

• <select>
• <insert>
• <delete>
• <update>
• <call>

Due to JDBC 3 features in INSERT statements optional attributes <autogen-key-columns> or <autogen-key-indexes> can be specified to retrieve values generated by the database. In <autogen-key-columns> developers can specify comma delimited list of column names that must be returned. In <autogen-key-columns> developers can specify comma delimited list of column indexes. Note that both attributes cannot be specified for the same statement.

For SELECT statements developers can define type of Java object that will hold one row of query results. This can be done using <ioc-result> tag or <result-class> optional attribute. In <result-class> attribute full Java class name should be specified. <ioc-result> tag allows to define IoC-based object. Note that in both cases result object should be a valid JavaBeans object and should have setter methods according to column names returned by query (note that method name matching is NOT case sensitive).

For specifics of IoC and IoC-based configuration see IocService.
For common rules of xTier™ service configuration see XtierKernel.
For detailed information about XML configuration see xtier_db.dtd at ${XTIER_ROOT}/config/dtd folder.

 Top

Examples
Usage of 'db' service follows the standard pattern of using xTier™ service: you need to obtain an instance of xTier™ kernel that serves as a service registry. Once you have xTier™ kernel you can get an instance of any service, in our case the db service. Once the service instance is obtained the service API can be used. Following example illustrates these basic steps:

1		// Get the instance of xTier kernel.
2		XtierKernel xtier = XtierKernel.getInstance();
3
4		// Get the instance of 'db' service.
5		db = XtierKernel.getInstance().db();
6
7		DbSession session = db.createSession
8		("oracleDsExample");
9
10		// Insert statement.
11		DbSqlParams params = new DbArraySqlParams(
12		new Object[] {
13		new Integer(1),
14		"John",
15		"Smith"
16		}
17		);
18
19		session.insert(
20		"INSERT INTO users_table(id, firstname," +
21		" lastname) VALUES(?, ?, ?)",
22		params);
23
24		params = new DbArraySqlParams(
25		new Object[] {
26		new Integer(1)
27		}
28		);
29
30		DbRow row = session.select(
31		"select * from users_table where id = ?", params);
32
33		System.out.println(
34		"Select result [" +
35		"id=" + row.getInt("id") +
36		", firstname=" + row.getString("firstname") +
37		", lastname=" + row.getString("lastname") +
38		']');
39
40		// Statement handler.
41		session.exec(new DbStatementHandler() {
42		public void execute(Statement stmt)
43		throws SQLException {
44		stmt.execute("INSERT INTO users_table(id," +
45		" firstname, lastname) VALUES(2, 'Bob', 'James')");
46		}
47		});
48
49		// Predefined Insert statement.
50		Map params = new HashMap();
51
52		params.put("id", new Integer(3));
53		params.put("firstname", "Nick");
54		params.put("lastname", "Johns");
55
56		session.psqlInsert("insert-user", params);
57
58		session.close();

 Top