1
edit
Changes
→JDBC
==Database==
===Database===
: Agile database practice [http://www.agiledata.org/essays/bestPractices.html]
* Terminology
: Collation @ [http://msdn.microsoft.com/en-us/library/ms143726(SQL.90).aspx#Locale_Defn msdn.microsoft.com]
: Cursors: Rather than executing a whole query at once, it is possible to set up a cursor that encapsulates the query, and then read the query result a few rows at a time. One reason for doing this is to avoid memory overrun when the result contains a large number of rows. [http://www.postgresql.org/docs/8.1/static/plpgsql-cursors.html]
: Relational database Management System[http://en.wikipedia.org/wiki/Relational_database_management_system ]
: Indexes & Hints
: Transactions
:: [http://ovir.icp.ac.ru/oracle/doc/server/doc/scn73/ch12.htm#toc094 Oracle Server Manual]
:Mapping Objects to Relational Databases @ [http://www.agiledata.org/essays/mappingObjects.html#MappingMetaData @ agiledata.org]: Converting Charsets [http://codex.wordpress.org/Converting_Database_Character_Sets]
===Persistence===
# JDBC Driver @ http://en.wikipedia.org/wiki/JDBC_driver
# Drivers table http://devapp.sun.com/product/jdbc/drivers
==JDBC==
To make sure that the Driver class passes through the class loader, you can do a lookup by class name, as shown in the Java code snippet in this example.
<code> try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException cnfe) {
System.err.println("Couldn't find driver class:");
cnfe.printStackTrace();
} </code>
Class.forName is a method that finds a class by name. In this case, you look for the Driver. This causes the class loader to search through the CLASSPATH and find a class by that name. If it finds it, the class loader will then read in the binary description of the class. If it does not find it, it will throw a ClassNotFoundException, in which case you can print out an error message to that effect. If you reach this state, you either haven't built the driver correctly, or the .jar file is not in your classpath.
Once you have registered the Driver class, you need to request a connection to a PostgreSQL database. To do this, you use a class called DriverManager. The DriverManager class is responsible for handling JDBC URLs, finding an appropriate driver, and then using that driver to provide a connection to the database.
JDBC URLs are of the following format, in three colon-delimited parts:
<code>jdbc:[drivertype]:[database]</code>
The first part, jdbc, is a constant. It represents that you are connecting to a JDBC data source. The second part, [drivertype], represents the kind of database you want to connect to. Use postgresql to connect to a PostgreSQL database. The third part is passed off to the driver, which finds the actual database. It takes on one of the following formats:
<code> databasename --- //hostname/databasename ------ //hostname:portnumber/databasename</code>
In the first case, the PostgreSQL database is running on the local machine, on the default port number. The databasename is the literal name of the database you wish to connect to. The second case is used for when you want to specify a hostname and a database. This also uses the default port number. The third case allows you to specify a port number as well. Even if you use the first type of URL, the JDBC connection will always be made via TCP/IP.
For the purposes of the examples from now on, this chapter will use the URL: jdbc:postgresql://localhost/booktown, meaning you are connecting to host localhost and database booktown. With that in mind, try to make a connection, using all you have learned so far. Example 12-2 shows a simple Java program that opens a JDBC connection to the booktown database. If you run the example yourself, be sure to replace the username and password with values that will work on your system.
:One issue is that JDBC does not do any client-side SQL parsing or syntax checking. SQL statements are passed off transparently to the database, whether or not they are valid. Therefore, if the SQL is valid on one vendor's database, but invalid on another vendor's database the implementation won't know until the actual connection is made and the SQL is sent across. Sun is attempting to deal with this problem, and there may be some provisions made to correct this, either in later versions of JDBC or in a different standard. Another issue is that each vendor has additional helper classes specific to that vendor. For instance, PostgreSQL has extensions for geometric data types. Other vendors won't support these extensions; they are specific to PostgreSQL. If you use such vendor-specific classes, your program will not work with another JDBC database, despite using the JDBC "standard."[http://www.commandprompt.com/ppbook/c20820]
: One advantage of the PostgreSQL JDBC driver is that it is a "Type 4" driver. This means that it is written in Pure Java, so it can be taken anywhere, and used anywhere as long as the platform it is used on has TCP/IP capabilities, because the driver only connects via TCP/IP.[http://www.commandprompt.com/ppbook/c20820]
:JDBC Driver 4 for PostgreSQL 9.This is the current version of the driver. Unless you have unusual requirements (running old applications or JVMs), this is the driver you should be using. It supports Postgresql 7.2 or newer and requires a 1.4 or newer JVM. It contains support for SSL and the javax.sql package. It comes in two flavors, JDBC3 and JDBC4. If you are using the 1.6 JVM, then you should use the JDBC4 version.
::JDBC3 Postgresql Driver, Version 9.0-801
::JDBC4 Postgresql Driver, Version 9.0-801 [http://jdbc.postgresql.org/download.html]
: Some info @ [http://wiki.postgresql.org/images/a/ac/Pg_8.1_J2EE_v1.0.pdf wiki.postgresql.org]
: Sample code for testing the connection to PostgreSQL [http://www.fankhausers.com/postgresql/jdbc/#driver_install] and [http://www.mkyong.com/java/how-do-connect-to-postgresql-with-jdbc-driver-java/]
:
==PostgreSQL and MySQL==
* Postgre
: Installation: [http://www.postgresql.org/download/]
: [http://www.postgresql.org/docs/9.0/static/index.html Tutorial]
: [http://www.postgresql.org/docs/7.1/static/jdbc-ext.html postgre connection]
: postgre gotchas [http://sql-info.de/postgresql/postgres-gotchas.html]
: Pattern Matching [http://pgsqld.active-venture.com/functions-matching.html
: Character set support -::The character set support in PostgreSQL allows you to store text in a variety of character sets (also called encodings), including single-byte character sets such as the ISO 8859 series and multiple-byte character sets such as EUC (Extended Unix Code), UTF-8, and Mule internal code. All supported character sets can be used transparently by clients, but a few are not supported for use within the server (that is, as a server-side encoding). The default character set is selected while initializing your PostgreSQL database cluster using initdb. It can be overridden when you create a database, so you can have multiple databases each with a different character set. An important restriction, however, is that each database's character set must be compatible with the database's LC_CTYPE (character classification) and LC_COLLATE (string sort order) locale settings. For C or POSIX locale, any character set is allowed, but for other locales there is only one character set that will work correctly. (On Windows, however, UTF-8 encoding can be used with any locale.) Note! Not all client APIs support all the listed character sets. For example, the PostgreSQL JDBC driver does not support MULE_INTERNAL, LATIN6, LATIN8, and LATIN10. [http://www.postgresql.org/docs/current/static/multibyte.html]
: BLOBs [http://www.postgresql.org/files/documentation/books/aw_pgsql/node96.html]
: [http://dev.mysql.com/doc/refman/5.0/en/tutorial.html Tutorial]
: [http://www.mysql.com/downloads/connector/j/ Mysql Connector]
: Retrieving AUTO_INCREMENT Column Values [http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-basic.html#connector-j-usagenotes-last-insert-id]
: LikeEscape:[http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like]
:: Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\\n”. To search for “\”, specify it as “\\\\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.
::Exception: At the end of the pattern string, backslash can be specified as “\\”. At the end of the string, backslash stands for itself because there is nothing following to escape. Suppose that a table contains the following values:
<code>
mysql> SELECT filename FROM t1;
filename : C: | C:\ | C:\Programs | C:\Programs\
</code>
::To test for values that end with backslash, you can match the values using either of the following patterns:
<code>
mysql> SELECT filename, filename LIKE '%\\' FROM t1;
::filename | filename LIKE '%\\' |
:: C: | 0 |
:: C:\ | 1 |
:: C:\Programs | 0 |
:: C:\Programs\ | 1 |
mysql> SELECT filename, filename LIKE '%\\\\' FROM t1;</code> ( Will have the same result as above)
*The current versions are MySQL 5.1 and PostgreSQL 8.4.
#PostgreSQL is a unified database server with a single storage engine. MySQL has two layers, an upper SQL layer and a set of storage engines. [http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL]
