JDBC纵览

使用JDBC逻辑上的基本步骤：" connect to the database, create a statement and execute the query, and look at the result set."

Class DriverManager

public static Connection getConnection (String url, Properties info) throws SQLException

Attempts to establish a connection to the given database URL. The DriverManager attempts to select an appropriate driver from the set of registered JDBC drivers.

Parameters:

url - a database url of the form jdbc:subprotocol :subname

info - a list of arbitrary string tag/value pairs as connection arguments; normally at least a "user" and "password" property should be included

Returns:

a Connection to the URL

Throws:

SQLException - if a database access error occurs

Interface Connection

A connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection.

A Connection object's database is able to provide information describing its tables, its supported SQL grammar, its stored procedures, the capabilities of this connection, and so on. This information is obtained with the getMetaData method.

Interface Statement

The object used for executing a static SQL statement and returning the results it produces.

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

Interface PreparedStatement

It is subinterface of Statement. An object that represents a precompiled SQL statement.

A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

Statement vs. PreparedStatement

If you want to execute a Statement object many times, it normally reduces execution time to use a PreparedStatement object instead.

The main feature of a PreparedStatement object is that, unlike a Statement object, it is given an SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first.

Although PreparedStatement objects can be used for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it.

Interface ResultSet

A table of data representing a database result set, which is usually generated by executing a statement that queries the database.

［4］是一个非常好的Get Start，建议新手一定要看看。

最后附上一个完整的例子，大家可以复习一下上面提到的内容，例子来自［5］，使用了sqlite嵌入式数据库。

import java.sql.*; public class Test_create_select { public static void main(String[] args) throws Exception { Class.forName("org.sqlite.JDBC"); Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db"); Statement stat = conn.createStatement(); stat.executeUpdate("drop table if exists people;"); stat.executeUpdate("create table people (name, occupation);"); PreparedStatement prep = conn.prepareStatement( "insert into people values (?, ?);"); prep.setString(1, "Gandhi"); prep.setString(2, "politics"); prep.addBatch(); prep.setString(1, "Turing"); prep.setString(2, "computers"); prep.addBatch(); prep.setString(1, "Wittgenstein"); prep.setString(2, "smartypants"); prep.addBatch(); conn.setAutoCommit(false); prep.executeBatch(); conn.setAutoCommit(true); ResultSet rs = stat.executeQuery("select * from people;"); while (rs.next()) { System.out.println("name = " + rs.getString("name")); System.out.println("job = " + rs.getString("occupation")); } rs.close(); conn.close(); } }

参考

［1］Getting Started with JDBC API

［2］JDBC 4.0

［3］http://java.sun.com/products/jdbc/overview.html

［4］http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html

［5］http://www.zentus.com/sqlitejdbc/