五分钟搞定JDBC数据库连接

JDBC(Java Database Connectivity)

API allows Java programs to connect to databases, Imports for the JDBC classes and interfaces from package java.sql.

Database access is the same for all database vendors The JVM uses a JDBC driver to translate generalized JDBC calls into vendor specific database calls

Starting with Java SE 8, the JDBC-ODBC Bridge will no longer be included with the JDK.

Popular JDBC database URL formats

MySQL jdbc:mysql://hostname:portNumber/databaseName ORACLE jdbc:oracle:thin:@hostname:portNumber:databaseName

Typical JDBC Programming Procedure

1. Load the database driver 2. Obtain a connection 4. Getting result sets (tables) from tables 5. Navigate through the result sets 6. Close the connection

Step 1：load database driver class

Class.forName("com.mysql.jdbc.Driver");

Step 2：connect to database

Connection con = DriverManager.getConnection( "jdbc:mysql://host[:port]/books", uid, password);

Step 3：Create a SQL statement object Statement sql = con.createStatement();

Step 4：Getting data from a table ResultSet rs = sql.executeQuery("SELECT * FROM authors");

Step 5：Use methods of ResultSet A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. next()

The next() method moves the cursor to the next row, and it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.

getString()、getBollean()、getLong()

Values can be retrieved using either the index number of the column or the name of the column (case insensitive)

eg: String s = rs.getString(“firstName”); = String s = rs.getString(2);

获得列名：

ResultSetMetaData metaData = resultSet.getMetaData(); int numberOfColumns = metaData.getColumnCount(); //index从1开始遍历 for(int i=1;i<numberOfColumns;i++){ metaData.getColumnName(i); }

Step 6：close the Statement and Connection

try{ ... } catch (SQLException se) { // 处理 JDBC 查询错误 se.printStackTrace(); } catch (ClassNotFoundException e) { // 处理 Class.forName 错误，找不到driver class e.printStackTrace(); } finally { // 关闭资源 try { resultSet.close(); statement.close(); connection.close(); }catch(Exception e){ e.printStackTrace(); } }