本文已参与「新人创作礼」活动，一起开启掘金创作之路。

关于JDBC

什么是JDBC

JDBC为使用关系型数据库提供了一套标准化接口

因为每一个数据库的底层实现原理都不一样, Oracle数据库有自己的原理, MySQL数据库也有自己的原理。 MS SqlServer数据库也有自己的原理。

JDBC标准化了

如何和数据库建立连接

如何初始化查询

创建查询的方法

储存查询结果的数据结构

注意不同数据库的SQL的语法没有统一

JDBC需要引用java.sql package

JDBC的组成

JDBC API : 一套完整的java api

JDBC Driver Manager: 实际和数据库交互的部分

JDBC API

Collection of interfaces and classes:

• DriverManager: Loads the driver • Driver: Creates a connection • Connection: Represents a connection • DatabaseMetaData: Information about the DB server • Statement: Executing queries • PreparedStatement: Precompiled and stored query • CallableStatment: Execute SQL stored procedures • ResultSet: Results of execution of queries • ResultSetMetaData:Meta data for ResultSet

JDBC的数据类型

创建JDBC的步骤

1. 加载驱动

try {
	Class.forName("oracle.jdbc.driver.OracleDriver"); 
	Class.forName("com.mysql.jdbc.Driver");
} catch { ClassNotFoundException cnfe) { 
	System.out.println("Error loading driver: " cnfe);
}

2. 定义链接URL

String host = "dbhost.yourcompany.com"; 
String dbName = "someName";
int port = 1234;
String oracleURL = "jdbc:oracle:thin:@" + host +":" + port + ":" + dbName; 
String mysqlURL = "jdbc:mysql://" + host +":" + port + "/" + dbName;

3. 创建链接

String username = "test"; 
String password = "test";
Connection connection = DriverManager.getConnection(oracleURL,username,password);

4. 创建Statement Object

Statement statement = connection.createStatement();
// discuss PreparedStatements later

5. 执行查询

String query = "SELECT col1, col2, col3 FROM sometable"; 
ResultSet resultSet = statement.executeQuery(query);

To modify the database, use executeUpdate(), supplying a string that uses UPDATE, INSERT, or DELETE

6. 处理结果

while(resultSet.next()) { 
	System.out.println(resultSet.getString(1) + " " +
	resultSet.getString(2) + " " + 
	resultSet.getString(3)); 
}

First column has index 1, not 0

ResultSet provides various getXxx methods that take a column index or name and returns the data

7.关闭链接

connection.close();

建立连接的代价很高, 所以直到所有数据库操作完成之后再关闭连接

常用属性

Result Set

Result Set包含SQL查询的结果 ----- 也就是根据rows和columns表示的一张表

ResultSet只能往前遍历(next)

常用的方法:

getMetaDataObject() 
//返回 ResultSetMetaData object

next()
//在ResultSet中移动到下一排
//如果成功则返回true, 失败则返回false
//第一次使用next()会将cursor移动到第一排

getWarnings()
//返回第一个SQL Warning 或者返回null如果没有warning

findColumn()
//根据对应的列的名字返回对应的数值

getXxx()
//Returns the value from the column specified by column name or column index as an Xxx Java type
//Legal getXxx types: double, byte, int, Date, String, float, short, long, Time, Object
//Be sure to handle the situation where getXxx returns a NULL

wasNull()
//To check if the last getXxx read was a SQL NULL

ResultSetMetaData

从 ResultSet 得到 ResultSetMetaData,

MetaDataResultSet 提供返回的ResultSet的具体信息

ResultSetMetaData 可以回答以下问题

result set中有多少row

指定列的名字

列名是否区分大小写

具体一列的数据类型

是否可以对一列进行查找

getColumnCount()
//返回result set中的columns总数
getColumnDisplaySize()
//Returns the maximum width of the specified column in characters
getColumnName()
//The getColumnName method returns the database name of the column
getColumnType()
//Returns the SQL type for the column to compare against types in java.sql.Type
isNullable()
//Indicates whether storing a NULL in the column is legal
//Compare the return value against ResultSet constants: columnNoNulls, columnNullable, columnNullableUnknown
isSearchable()
//Returns true or false if the column can be used in a WHERE clause
isReadOnly() / isWriteable()
//The isReadOnly method indicates if the column is definitely not writable
//The isWritable method indicates whether it is possible for a write to succeed

Statement

SQL statements通过Statement object 发给数据库

不同类型的statement

Statement

执行简单的SQL语句

executeQuery()
//Executes the SQL query and returns the data in a table (ResultSet)
//The resulting table may be empty but never null
executeUpdate()
//Used to execute for INSERT, UPDATE, or DELETE SQL statements
//The return is the number of rows that were affected in the database
getMaxRows()/setMaxRows()
//Determines the number of rows a ResultSet may contain
//Unless explicitly set, the number of rows are unlimited (return value of 0)
getQueryTimeout()/setQueryTimeout()
//Specifies the amount of a time a driver will wait for a STATEMENT to complete before throwing a SQLException

PrepareStatement

执行带有参数的precompiled SQL statement

当需要执行多次执行相似的SQL语句时使用, 可以提高性能

包含execute(), executeQuery(), executeUpdate()

Connection connection = 
DriverManager.getConnection(url, user, password);
PreparedStatement statement = connection.prepareStatement("UPDATE employees " +
float[] newSalaries = getSalaries(); 
int[] employeeIDs = getIDs();
for(int i=0; i<employeeIDs.length; i++) { 
	statement.setFloat(1, newSalaries[i]); 
	statement.setInt(2, employeeIDs[i]);
	statement.executeUpdate();
}

CallableStatement

for executing a database stored procedure

SQLException

几乎每个JDBC method 都会抛出一个SQLException

如果多个错误发生, 它们会被chained together

SQL exceptions包含: Description of the error: getMessage()

The SQLState (Open Group SQL specification) identifying the exception: getSQLState

A vendor-specific integer error code:, getErrorCode

A chain to the next exception: getNextException

try {
... // JDBC statement.
} catch (SQLException sqle) { 
	while (sqle != null) {
		System.out.println("Message: " + sqle.getMessage());
		System.out.println("SQLState: " + sqle.getSQLState()); 
		System.out.println("Vendor Error: " + sqle.getErrorCode()); 
		sqle = sqle.getNextException();
	}
}

SQL Warnings

SQLWarnings are rare, but provide information about the database access warnings

ResultSet results = statement.executeQuery(someQuery); 
SQLWarning warning = statement.getWarnings();
while (warning != null) {
	System.out.println("Message: " + warning.getMessage()); 
	System.out.println("SQLState: " + warning.getSQLState()); 
	System.out.println("Vendor Error: " + warning.getErrorCode()); 
	warning = warning.getNextWarning();
}
while (results.next()) { 
	int value = rs.getInt(1);
	... // Call additonal methods on result set.
	SQLWarning warning = results.getWarnings(); 
	while (warning != null) {
		System.out.println("Message: " + warning.getMessage()); 
		System.out.println("SQLState: " + warning.getSQLState()); 
		System.out.println("Vendor Error: " + warning.getErrorCode()); 
		warning = warning.getNextWarning();
	}
}

Transaction

一般默认是每个SQL statement执行完, 修改会自动comnmitted to database

关闭auto-commit: connection.setAutoCommit(false)

Call commit to permanently record the changes to the database after executing a group of statements

Call rollback if an error occurs

getAutoCommit()/setAutoCommit()
//By default, a connection is set to auto-commit
// Retrieves or sets the auto-commit mode

commit()
//Force all changes since the last call to commit to become permanent
//Any database locks currently held by this Connection object are released

 rollback()
//Drops all changes since the previous call to commit
//Releases any database locks held by this Connection object

数据库JDBC --- Java Database Connectivity

关于JDBC

什么是JDBC

JDBC的组成

JDBC API

JDBC的数据类型

创建JDBC的步骤

常用属性

Result Set

ResultSetMetaData

Statement

SQLException

SQL Warnings

Transaction