|
|  |
| This FAQ covers only the basics of Postgresql JDBC. It is based on questions that regularly appear on the pgsql-jdbc mailing list from folks who are trying JDBC out for the first time. The FAQ is written in a narrative style, so if you're new and starting from scratch, just start reading at the beginning by following this link. If you have a pretty good idea of what you're doing, but have a particular problem, use the FAQ-style set of links below to go directly to a topic. This FAQ was updated 11/10/2003 to reflect. I believe all of the examples should work with PostgreSQL version 7.3.2 and the current JDBC driver. The questions:
Note:I'll try to assume no prior knowledge in the documentation that follows- I only assume that you're smart. If you get stuck because I'm assuming you already know how to do something, don't be embarrassed- just send me an E-Mail and I'll add the necessary information. The whole point here is to help you get started with no prior knowledge. (Like I was when I started.) Please send questions and suggestions to nick@fankhausers.com. Before you can start with JDBC you'll need to have PostgreSQL and Java already installed. I won't cover this process, as it is nicely documented elswhere. Here are links to a few good sources for installing PostgreSQL:
Install and setup of Java componentsGetting the driverFirst, you'll need to get the JDBC driver and classes. The simplest method is to get the .jar file, although compiling from source is also a fairly simple option. The .jar files may be found at: http://jdbc.postgresql.org/download.html Make sure you're getting the appropriate file for your version of PostgreSQL & Java. Most of the recent versions are simply in the .jar file format. Older versions may be in .tar or .tar.gz format. Since this is Java, there's no need to worry about your hardware or OS version. Installing the driverOnce you've downloaded the appropriate jarfile, put it in the directory of your choice. Most operating systems have a standard place for jarfiles to live and this is probably the most sensible place to put yours, but it can really live anywhere as long as it gets included in the classpath (more on classpaths below). If you've gotten a tar or tar.gz format, put it in the directory of your choice & unpack it using one of these commands: Setting the classpathNow make sure the jarfile is in your classpath. For example if the file you got is jdbc7.1-1.2.jar, and you put it in /usr/share/java, tack ":/usr/share/java/jdbc7.1-1.2.jar" onto the end of the classpath that you currently use. If you've never used jarfiles with java before, there are a couple of "hidden gotchas" that you should be aware of:
Configuring PostgreSQL for JDBCJDBC uses an IP socket connection to access your database even if the database lives on the same machine as the Java client. A Unix socket and an IP socket are not the same thing. **Confusion about this seems to be the most common source of problems for new JDBC folk**. Luckily, once you know this, configuration is easy. The PostgreSQL configuration for JDBC consists of two tasks- turning on the IP socket connection for the server, and authorizing access via the IP Socket. To turn on the IP Socket, you have two options:
To authorize connections to the IP socket, you must add a line to /etc/postgresql/pg_hba.conf. There are varying levels of security available. For the purpose of getting started, I suggest you get JDBC working using the lowest level of security, and then tighten the security up later. To allow an IP connection to all databases on the same computer from any user, add this line to pg_hba.conf: This will get you started. After everything is working well, you can play with the security settings. Complete information on authorization methods and levels in the pg_hba.conf file can be found at the postgresql interactive documentation for client-authentication Writing & running your first programThe java code below makes a connection & demonstrates a few bits of the JDBC API. In order to make this work, you'll need to have PostgreSQL running & accepting IP connections. You'll also need to create a test database to connect to in advance. When you run the test program, specify the same user that created the database to ensure that you have no access problems. HelloPostgresql.java/**
* A demo program to show how jdbc works with postgresql
* Nick Fankhauser 10/25/01
* nickf@ontko.com or nick@fankhausers.com
* This program may be freely copied and modified
* Please keep this header intact on unmodified versions
* The rest of the documentation that came with this demo program
* may be found at http://www.fankhausers.com/postgresql/jdbc
*/
import java.sql.*; // All we need for JDBC
import java.text.*;
import java.io.*;
public class HelloPostgresql
{
Connection db; // A connection to the database
Statement sql; // Our statement to run queries with
DatabaseMetaData dbmd; // This is basically info the driver delivers
// about the DB it just connected to. I use
// it to get the DB version to confirm the
// connection in this example.
public HelloPostgresql(String argv[])
throws ClassNotFoundException, SQLException
{
String database = argv[0];
String username = argv[1];
String password = argv[2];
Class.forName("org.postgresql.Driver"); //load the driver
db = DriverManager.getConnection("jdbc:postgresql:"+database,
username,
password); //connect to the db
dbmd = db.getMetaData(); //get MetaData to confirm connection
System.out.println("Connection to "+dbmd.getDatabaseProductName()+" "+
dbmd.getDatabaseProductVersion()+" successful./n");
sql = db.createStatement(); //create a statement that we can use later
String sqlText = "create table jdbc_demo (code int, text varchar(20))";
System.out.println("Executing this command: "+sqlText+"/n");
sql.executeUpdate(sqlText);
sqlText = "insert into jdbc_demo values (1,'One')";
System.out.println("Executing this command: "+sqlText+"/n");
sql.executeUpdate(sqlText);
sqlText = "insert into jdbc_demo values (3,'Four')";
System.out.println("Executing this command twice: "+sqlText+"/n");
sql.executeUpdate(sqlText);
sql.executeUpdate(sqlText);
sqlText = "update jdbc_demo set text = 'Three' where code = 3";
System.out.println("Executing this command: "+sqlText+"/n");
sql.executeUpdate(sqlText);
System.out.println (sql.getUpdateCount()+
" rows were update by this statement/n");
System.out.println("/n/nNow demostrating a prepared statement...");
sqlText = "insert into jdbc_demo values (?,?)";
System.out.println("The Statement looks like this: "+sqlText+"/n");
System.out.println("Looping three times filling in the fields.../n");
PreparedStatement ps = db.prepareStatement(sqlText);
for (int i=10;i<13;i++)
{
System.out.println(i+".../n");
ps.setInt(1,i); //set column one (code) to i
ps.setString(2,"HiHo"); //Column two gets a string
ps.executeUpdate();
}
ps.close();
System.out.println("Now executing the command: "+
"select * from jdbc_demo");
ResultSet results = sql.executeQuery("select * from jdbc_demo");
if (results != null)
{
while (results.next())
{
System.out.println("code = "+results.getInt("code")+
"; text = "+results.getString(2)+"/n");
}
}
results.close();
sqlText = "drop table jdbc_demo";
System.out.println("Executing this command: "+sqlText+"/n");
sql.executeUpdate(sqlText);
db.close();
}
public static void correctUsage()
{
System.out.println("/nIncorrect number of arguments./nUsage:/n "+
"java
/n");
System.exit(1);
}
public static void main (String args[])
{
if (args.length != 3) correctUsage();
try
{
HelloPostgresql demo = new HelloPostgresql(args);
}
catch (Exception ex)
{
System.out.println("***Exception:/n"+ex);
ex.printStackTrace();
}
}
}
If you have all of the java components installed and the JDBC jarfile is in your classpath, this should compile with no problem. If you get "unrecognized symbol" errors pointing to the JDBC classes, there is a problem in your classpath. Go Back to the classpath section and double-check everything. Run it with the following command:java HelloPostgresql <database name> <username> <password> for example: java HelloPostgresql test scott tiger An example session compiling and running this code as user nickf on a linux box looks like this: nickf@dev:~$ CLASSPATH=/usr/share/java/postgresql.jar:. nickf@dev:~$ export CLASSPATH nickf@dev:~$ javac HelloPostgresql.java nickf@dev:~$ createdb fred CREATE DATABASE nickf@dev:~$ java HelloPostgresql fred nickf password Connection to PostgreSQL 6.5.2 successful. Executing this command: create table jdbc_demo (code int, text varchar(20)) Executing this command: insert into jdbc_demo values (1,'One') Executing this command twice: insert into jdbc_demo values (3,'Four') Executing this command: update jdbc_demo set text = 'Three' where code = 3 2 rows were update by this statement Now demostrating a prepared statement... The Statement looks like this: insert into jdbc_demo values (?,?) Looping three times filling in the fields... 10... 11... 12... Now executing the command: select * from jdbc_demo code = 1; text = One code = 3; text = Three code = 3; text = Three code = 10; text = HiHo code = 11; text = HiHo code = 12; text = HiHo Executing this command: drop table jdbc_demo nickf@dev:~$ dropdb fred DROP DATABASE nickf@dev:~$ The "No suitable driver found" errorIf the compile went well, but you get a runtime error that says "No suitable driver found", it is likely that the URL passed to DriverManager.getConnection is malformed or otherwise incorrect. See the notes in the next section for a complete description of the URL format. Notes on the connection URLThe demo program above uses the simplest form of the URL in the DriverManager.getConnection method call. In this case, it only connects to the default port on the local computer. By modifying the URL you may easily specify both port and host. Here are the possible forms: "ClassNotFoundException" for the driverIf the compile went well, but you get a runtime error that says "ClassNotFoundException", one of two problems has occured:
File LocationsThe locations of the various files mentioned in this FAQ vary depending on the OS platform. Information for the Debian platform is at the bottom of this section. If other readers of this FAQ will send me the info for the platform they're working on, I'll post that informaiton as well. In the meantime, here's how to go about finding them... If you are on one of the flavors of Unix, you can use "find" to locate specific files. To search for a file starting at root & traversing all subdirectories use this command:find / -name <filename> -print For instance: find / -name pg_hba.conf -print This command often takes a while to execute and will usually result in a lot of lines saying "...Permission denied" as it attempts to scan directories that your user can't access, but buried among the "denied" messages, you'll find the file you're after. Running it as root eliminates the "Permission Denied" messages. If you are running on one of the MSWindows platforms, just use Start->Find->[Files or Folders] to look for the files. If you are seeking the .conf files, another method is to locate the data directory. The postgres user will have this path in an environment variable, so log in as root, and then enter these commands: I'm not certain how this works on the windows platform, but I'd guess an environment variable with the same name is used, so you'd have to go to a DOS prompt & issue the DOS equivalent command to display the variable.If you're a windows user, send me email with the answer & I'll post it here. File locations for Postgresql V7.1 on Debian 2.2
How do I get Tomcat and PostgreSQL JDBC to play together nicely?Take a look at the Tomcat & PostgreSQL JDBC FAQ Other resources on the web
This helped! how do I thank you? | |
961
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
