JDBCThere are three versions of JDBC 1, 2 and 3. Most SQLs (Standard Query Languages) use level 2 and few now support level 3. Level three allows things such as discovering what keys were automatically generated by INSERT.
You can learn more about ODBC by reading the Microsoft ODBC documentation. This will give you improved ODBC configuration utilities for the Control Panel.
JDBC is just a wrapper to let you feed SQL requests to the server. To be kind, the interface is less than elegant. It comes from gluing ODBC onto SQL, then hurriedly grafting Java/JDBC onto ODBC. JDBC does not in the least look like database interface designed for Java.
JDBC can be used to access a database on the same machine, or a server on a LAN (Local Area Network) or on a server across the Internet.
JDBC has nothing to say about the format of the packets sent across the net. It is also silent on how the work is divided between client and server. This means that clients must use a JDBC proprietary library matching the server’s protocols. This library may be written in pure Java or partly in native code.
Since JDBC is so similar to ODBC, it is expedient to use a bridge to convert JDBC calls into ODBC calls and exploit existing ODBC drivers, standard protocols and database interfaces. However, this extra layer extracts a performance penalty.
Because the format of the packets are not specified, JDBC drivers are free to do clever things like compress, buffer and encrypt.
 |
recommend book⇒JDBC API Tutorial and Reference, third edition | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| by | Maydene Fisher, Jon Ellis, Jonathan Bruce | 978-0-321-17384-3 | paperback | |||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| publisher | Prentice Hall | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| published | 2003-06-21 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| This is Oracle’s official book on JDBC. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Greyed out stores probably do not have the item in stock. Try looking for it with a bookfinder. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
recommend book⇒Database Programming with JDBC and Java | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| by | George Reese | 978-1-56592-616-5 | paperback | |||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| birth | 1969-02-17 age:49 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| publisher | O’Reilly  | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| published | 2000-01-15 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Covers JDBC 2 and RMI. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Greyed out stores probably do not have the item in stock. Try looking for it with a bookfinder. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
recommend book⇒Visual Developer Java Database Programming with JDBC, second edition: The Essentials for Developing Databases for Internet and Intranet Applications | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| by | Pratik Patel and Karl Moss | 978-1-57610-159-9 | paperback | |||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| publisher | Coriolis | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| published | 1997-07-22 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Greyed out stores probably do not have the item in stock. Try looking for it with a bookfinder. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
recommend book⇒Java Database Programming with JDBC: Discover the Essentials for Developing Databases for Internet and Intranet Applications | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| by | Pratik Patel and Karl Moss | 978-1-57610-056-1 | paperback | |||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| publisher | Coriolis | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| published | 1996-09-13 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| If you are looking for an example for building a JDBC driver from the ground up, this the book you want. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Greyed out stores probably do not have the item in stock. Try looking for it with a bookfinder. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| JDBC data types | |
|---|---|
| JDBC Type | Equivalent Java Type |
| ARRAY | java.sql.Array |
| BIGINT | long |
| BINARY | byte[] |
| BIT | Boolean |
| BLOB | java.sql.Blob |
| BOOLEAN | Boolean |
| CHAR | String |
| CLOB | java.sql.Clob |
| DATALINK | java.net.URL |
| DATE | java.sql.Date |
| DECIMAL | java.math.BigDecimal |
| DISTINCT | mapping of underlying type |
| DOUBLE | double |
| FLOAT | double |
| INTEGER | int |
| JAVA_OBJECT | underlying Java class |
| LONGNVARCHAR | String |
| LONGVARBINARY | byte[] |
| LONGVARCHAR | String |
| NCHAR | String |
| NCLOB | java.sql.NClob |
| NUMERIC | java.math.BigDecimal |
| NVARCHAR | String |
| REAL | float |
| REF | java.sql.Ref |
| ROWID | java.sql.RowId |
| SMALLINT | short |
| SQLXML | java.sql.SQLXML |
| STRUCT | java.sql.Struct |
| TIME | java.sql.Time |
| TIMESTAMP | java.sql.Timestamp |
| TINYINT | byte |
| VARBINARY | byte[] |
| VARCHAR | String |
| JDBC Literals | ||
|---|---|---|
| Type | Syntax | Notes |
| date | {d 'yyyy-mm-dd'} | e.g. {d 'yyyy-mm-dd'} |
| time | {t 'hh:mm:ss'} | e.g. {t '23:59:59'}. The seconds can have up to 6 decimal places. |
| timestamp | {ts 'yyyy-mm-dd hh:mm:ss'} | Can also have fractional seconds. {ts '2007-12-31-59.123456'} |
| String | Jim’s dog or 'Jim said I faked every orgasm.' |
String literals are delimited by quotation marks or apostrophes. If a string is delimited by quotation marks, it may contain apostrophes; if a string is delimited by apostrophes, it may contain quotation marks. SQL92 only specifies using single quotes for delimiting string, with two single quotes to indicate embedded quotes. Double-quotes are reserved for quoting names. This is not true for all SQL DBMS (DataBase Management System) vendors, though. If you have an apostrophe in a String delimited by an apostrophes, you need to double it. It gets rather mind boggling because you have the outer Java String which uses Java-style escapes and inside the string are SQL literals part of an SQL expression which use SQL conventions. |
| int | 10 +10 -10 | Optional sign |
| long | 10 +10 10 | SQL does not make a distinction between short, int and long in literals. |
| decimal | 10.4 +10.4 -10.4 | Optional sign |
| double | 10.4E6 +10.4E6 -10.4E6 | Optional sign |
| binary | B'010' | defines an int using binary. SQL 92 syntax. |
| hex | X'7FAB' | defines an int using hex. SQL 92 syntax. |
| Boolean | TRUE FALSE UNKNOWN NULL | UNKNOWN is not universally supported. |
| JDBC Standard functions | |
|---|---|
| Function | Notes |
| Numeric Functions | |
| {fn ABS(number)} | absolute value |
| {fn ACOS(float)} | arccos giving radians |
| {fn ASIN(float)} | arcsin giving radians |
| {fn ATAN(float)} | arctan giving radians |
| {fn ATAN2(float1, float2)} | accurate arctan of float1/float2 giving radians |
| {fn CEILING(number) } | next highest integer |
| {fn COS(float)} | cosine of radians |
| {fn COT(float) } | cotangent of radians |
| {fn DEGREES(number)} | convert radians to degrees |
| {fn EXP(float) } | exponential, e to the float |
| {fn FLOOR(number) } | next lower integer |
| {fn LOG(float) } | base e logarithm |
| {fn LOG10(float)} | base 10 logarithm |
| {fn MOD(integer1, integer2)} | remainder, like Java %, not a true modulus function. The result is negative only if integer1 is negative. |
| {fn PI()} | π |
| {fn POWER(number, power)} | number to the power |
| {fn RADIANS(number) } | converts degrees to radians |
| {fn RAND() } | float random number 0..1 |
| {fn RAND(integer) } | set seed for random number generator |
| {fn ROUND(number places)} | round to given number of decimal places. |
| {fn SIGN(number)} | signum: +1= positive 0=zero -1=negative |
| {fn SIN(float) } | sine of radians |
| {fn SQRT(float)} | square root |
| {fn TAN(float)} | tangent of radians |
| {fn TRUNCATE(number places) } | truncate to number of places |
| String Functions | |
| {fn ASCII (string)} | convert a single-character string to the corresponding ASCII (American Standard Code for Information Interchange) int, which will be 0..255. |
| {fn CHAR(code)} | char corresponding to ASCII code. The code must be in the range 0..255.. |
| {fn CHAR_LENGTH(string) | String length in chars. c.f. LENGTH |
| {fn CONCAT(string1,string2)} | Concatenate two strings |
| {fn DIFFERENCE(string1, string2)} | returns an integer that is the difference between two SOUNDEX string expressions. The return value is in the range of 0..4. |
| {fn INSERT(string1, start, length, string2)} | Insert string2 into a slot in string 1, leaving string1 and string2 themselves intact. |
| {fn LCASE(string) | Lower case |
| {fn LEFT(string, count)} | The leftmost count chars of a string |
| {fn LENGTH(string) | String length in chars, excluding trailing blanks. cf CHAR_LENGTH |
| {fn LOCATE(string1, string2, start)} | 1-based index of string2 in string1. The third argument is optional. |
| {fn LTRIM(string) | Trim spaces from the left end of the string, leaving the original intact. |
| {fn REPEAT(string, count)} | concatenate the string count times to itself so there are count copies in total. |
| {fn REPLACE(string1, string2, string3)} | replaces all occurrences of string2 in string1 with string3. The original strings are left intact. |
| {fn RIGHT(string, count)} | Rightmost count chars of a string. |
| {fn RTRIM(string)} | trim spaces from the right end leaving the original string intact. |
| {fn SOUNDEX(string)} | Generate a soundex from the string. |
| {fn SPACE(count)} | Generate a string count spaces long. |
| {fn SUBSTRING(string, start, length)} | Substring of string, 1 based |
| {fn UCASE(string) } | Upper case, leaving original intact |
| Time and Date Functions | |
| {fn CURDATE()} | Current date |
| {fn CURTIME()} | Current time |
| {fn DAYNAME(date)} | Day of week given date. The actual string you get back depends on the SQL engine. |
| {fn DAYOFMONTH(date)} | Day of month given date. Returns an integer in the range of 1..31. |
| {fn DAYOFWEEK(date)} | Day of week as a number given the date. Returns an integer in the range of 1..7. A value of 1 represents the Monday. |
| {fn DAYOFYEAR(date)} | Day of year, given date. Returns integer in the range of 1..366. |
| {fn HOUR(time)} | hour, (0..23) given time |
| {fn MINUTE(time)} | minute, (00..59) given time |
| {fn MONTH(time)} | month, (1..12) given time |
| {fn MONTHNAME(date)} | month name given date. The exact string you get depends on the SQL engine. |
| {fn NOW()} | Current timestamp |
| {fn QUARTER(date)} | Quarter given date. An integer 1..4. 1 represents January 1 through March 31 |
| {fn SECOND(time)} | minute, (00..59) given time |
| {fn TIMESTAMPADD(interval, count, timestamp)} | Add count intervals (in seconds, with 6 decimal places) to a timestamp to get another timestamp. The interval parameter is specified as SQL_TSI_YEAR, SQL_TSI_DAY, SQL_TSI_SECOND |
| {fn TIMESTAMPDIFF(interval, timestamp1, timestamp2)} | Difference in two timestamps in seconds, with 6 decimal places (i.e. to the microsecond) The interval parameter is specified as SQL_TSI_YEAR, SQL_TSI_DAY, SQL_TSI_SECOND. |
| {fn WEEK(date)} | Week number of year, 1..53, week 1 is the first week with any days in this year. |
| {fn YEAR(date) } | Year given date |
| System Functions | |
| {fn DATABASE()} | name of the database |
| {fn IFNULL(expression, replacement)} | result is normally expression, but if expression is null, the result is the replacement. |
| {fn USER() } | Who is accessing the SQL database. |
| Conversion Functions | |
| {fn CONVERT(value, SQLtype) } | type may be SQL_BIGINT, SQL_BINARY, SQL_BIT, SQL_CHAR, SQL_DATE, SQL_DECIMAL, SQL_DOUBLE, SQL_FLOAT, SQL_INTEGER, SQL_LONGVARBINARY, SQL_LONGVARCHAR, SQL_REAL, SQL_SMALLINT, SQL_TIME, SQL_TIMESTAMP, SQL_TINYINT, SQL_VARBINARY and SQL_VARCHAR |
In some JDBC drivers, you cannot nest {fn-style functions. You must use ordinary SQL expressions or variables as arguments.
The preferred way to connect to an SQL database is to use DataSources. This way your application program needs know only the name of the database. It need not know the userid, password, URL (Uniform Resource Locator), JDBC driver name etc. You define the details of each connection using XML (extensible Markup Language) in a configuration file for your servlet womb. Your application code then uses ugly but simple code
In theory you can store binary data in SQL databases using BLOBs. However, I found the implementation so flawed I gave up and decided it made more sense to store the binary data as Base64u-armoured text instead.
The fundamental problem is that SQL works by exchanging ASCII sentence with the server. Binary data must be embedded in these sentences surrounded in quotes, with accidental quotes doubled. SQL is not designed to deal with streams. It was an afterthought. You must use the methods of PreparedStatement to compose them.
Problems include:
confirmnumber INT NOT NULLPRIMARY KEYAUTO_INCREMENT,When you insert records into the database, you leave that field NULL. You can retrieve
SELECT LAST_INSERT_ID();
If you wanted to have a client application access two different SQL servers, your client would have to load two versions of the JDBC library interface routines. JDBC handler classes automatically register themselves, so there is no logical problem with multiple JDBC drivers, just a performance hit. Middleware products like dbAnywhere come to the rescue. The client loads a single version of the JDBC library that either speaks several proprietary protocols, or that talks to a server which in turn talks to the databases in their native proprietary protocols.
Middleware products can also help with buffering output from the server.
Since there is no such thing as a standard JDBC library, every website will require you to download a set of JDBC drivers just to browse its files, even with middleware.
If the client JDBC drivers are written in pure Java (such as those for dbAnywhere), they can be downloaded on the fly by any passing Java-enabled web browser, without violating Applet security. If they are native classes, they have to be manually downloaded and installed. Ideally you want JDBC drivers thin — lightweight so they don’t take much time to load.
There are four types of JDBC drivers.
The JDBC driver scans your queries for the { character to pre-process the generic JDBC syntax into the native SQL syntax. Other than that, it just passes your queries on as text SQL sentences to the server.
JDBC looks like a rush job to get an SQL interface out the door. With more time, what might replace it?
SQL vendors are hopeless about using standard names or representations for common business objects like dates, times, timestamps, 8, 16, 32 and 64-bit integers, zip codes, postal codes, states, provinces and phone numbers.
Most likely we will have to wait for SQL standards groups to upgrade the embedded interface specifications.
I have heard there is something called ODMG (Object Database Management Group) binding which will let you get at a database in a more object oriented way. JDBC is not really a suitable interface for application programmers. It is meant to be hidden inside something more programmer-friendly.
Also IBM (International Business Machines), Tandem and Oracle are working on a programmer-friendly SQL interface called JSQL (Java Standard Query Language) that calls for automatic mapping of SQL types to Java objects, thereby producing bridges between the two languages, in addition to delineating methods for checking at application compile-time to make sure that SQL and Java types match.
Oracle demonstrated JSQL at the Colorado Summit. It is a preprocessor that generates Java code. It even clumsier to use that embedded SQL was in FØRTRAN two decades ago. It is somewhat better than JDBC, but still far cry from being able creating components that are dbAware, that do their reads/writes more or less invisibly. We have a long way to go.
You need to presume some brat will decompile your Applet and use that knowledge to create a substitute Applet that causes as much havoc as possible. If your Applet has direct access to JDBC, the brat’s substitute can snoop or pillage the database to its heart’s content.
You thus need two layers of security, in the Applet and in the server. The proper way to handle it is your Applet communicates via a socket or CGI GET/POST [UTF-8 or binary] to a Servlet, that does the JDBC calls. The Servlet can request reams of data via JDBC and intelligently summarise them for the Applet, thus cutting down on traffic.
On the other paw, if this is for pure intranet use, having the Applet do its own JDBC saves the work of writing Servlets, defining packet formats etc. You can write and maintain your code much more quickly. You gain flexibility and give up only a tiny bit of speed, since the LAN is relatively quick.
 |
This page is posted |
http://mindprod.com/jgloss/jdbc.html |
Optional Replicator mirror
|
J:\mindprod\jgloss\jdbc.html | |
 |
Please read the feedback from other visitors,
or send your own feedback about the site. Contact Roedy. Please feel free to link to this page without explicit permission. | |
| Canadian
Mind
Products
IP:[65.110.21.43] Your face IP:[18.97.14.91] |
| |
| Feedback |
You are visitor number | |
