1.JDBC基础
JDBC(Java DataBase Connectivity)是给java提供连接数据的的工具包,它有编写的类和接口组成。它的基本类图如下:
它本质是:维护了一个client 与远程的(数据库)服务的TCP连接。
JDBC API : 提供了java应用程序到驱动管理器DriverManage的连接。
JDBC驱动程序API: 这支持驱动管理器DriverManager到数据库厂商提供的驱动程序的连接。
在基础的使用中,主要涉及到4个类的使用,为了方便下面省略包名,直接写类名:
1. 1Driver
初始化数据库驱动,以执行驱动的静态块代码
mysql的驱使语句为“ Class.forName("com.mysql.jdbc.Driver")”;架包:Mysql-Connect-java
Oracle的驱使语句为“Class.forName(“oracle.jdbc.driver.OracleDriver")”;架包:ojdbc6.JAR
1.2Connection
根据输入的url,user,password。 通过DriverManager 获得不同数据库的连接。
Connection 的结构图:
Connection的方法:
1.2.1事务:
在数据库中我们使用事务,成对出现的: savepoint ,rollback ;
比如:实际这个语句只会导入一条数据在表中,因为在commit之前,我进行的rollback,在savepoint 后面的执行的语句会被取消掉。
同样的对应到Connection中,先savepoint方法再进行rollback。
declare
startDate date;
excuteTime number :=1;
begin
startDate :=sysdate-1;loop
insert into ag ( DD) values (startDate);
if(excuteTime=1)then
savepoint pointName ;
end if;
excuteTime:= excuteTime+1;
if(excuteTime>2) then
rollback to pointName;
exit;
end if;
end loop;
commit;
end;
在Connection中,首先创建出的Connection 是默认事务自动提交的,如果想要做事务处理需要使用
setAutoCommit()修改是否自动提交,
savaPoint()设置点,
rollback(SavePoint)回滚,
rollback()全部回滚
setTransactionIsolation() 设置事务处理的隔离性,一般默认为可重复读。
同时注意rollback() 恢复掉所有的savePoint节点。但是对于"select t.* from table t where t.id = 1 for update " 的行锁依然会存在,只有commit才会停止。
代码:
package JDBCUtil;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
/** @author nieshijuan
* @createTime 2021年9月17日
* @Description
*/
public class ConnectionUtil {
private static ConnectionUtil connectionUtil ;
private ConnectionUtil(){
}
static{
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 获取是咧
* @description
* @return DriverManager
* @createPeople nieshijuan
* @throw @return
*/
public static synchronized ConnectionUtil getInstance(){
if(connectionUtil==null){
connectionUtil = new ConnectionUtil();
}
return connectionUtil;
}
/**
* 获取连接
* @description
* @return Connection
* @createPeople nieshijuan
* @throw @return
* @throw @throws Exception
*/
public Connection getConnection() throws Exception{
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "user", "password");
return con;
}
public static void main(String[] args){
ConnectionUtil util = ConnectionUtil.getInstance();
try {
Connection con = util.getConnection();
con.setAutoCommit(false);
Statement updateStatement = con.createStatement();
updateStatement.executeUpdate("update test t set t.name = '33' ");
Savepoint point = con.setSavepoint("firstpoint");
con.rollback(point);
con.commit();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
具体源码解释如下:
/**
* Sets this connection's auto-commit mode to the given state.
* If a connection is in auto-commit mode, then all its SQL
* statements will be executed and committed as individual
* transactions. Otherwise, its SQL statements are grouped into
* transactions that are terminated by a call to either
* the method <code>commit</code> or the method <code>rollback</code>.
* By default, new connections are in auto-commit
* mode.
* <P>
* The commit occurs when the statement completes. The time when the statement
* completes depends on the type of SQL Statement:
* <ul>
* <li>For DML statements, such as Insert, Update or Delete, and DDL statements,
* the statement is complete as soon as it has finished executing.
* <li>For Select statements, the statement is complete when the associated result
* set is closed.
* <li>For <code>CallableStatement</code> objects or for statements that return
* multiple results, the statement is complete
* when all of the associated result sets have been closed, and all update
* counts and output parameters have been retrieved.
*</ul>
* <P>
* <B>NOTE:</B> If this method is called during a transaction and the
* auto-commit mode is changed, the transaction is committed. If
* <code>setAutoCommit</code> is called and the auto-commit mode is
* not changed, the call is a no-op.
*
* @param autoCommit <code>true</code> to enable auto-commit mode;
* <code>false</code> to disable it
* @exception SQLException if a database access error occurs,
* setAutoCommit(true) is called while participating in a distributed transaction,
* or this method is called on a closed connection
* @see #getAutoCommit
*/
void setAutoCommit(boolean autoCommit) throws SQLException;
具体实现解见: MySQL驱动阅读------Connection连接的建立,基于JDBC-----5.1.26 - 无心流泪 - 博客园
3.Statement,以及PreStatement : 使用从此接口创建的对象将SQL语句提交到数据库。除了存储过程之外,一切派生类还接受参数。
4.ResultSet用于存储从sql查询到的结果集。
JDBC 关闭顺序: resultset,prestatement,statement,connection。
具体的一个小demo: 因为手打,没有在ecplise中,具体exception要自己加进去。
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, name, password);
String SQL = "select t.* from test";
Statement statment = conn.createStatement(SQL);
ResultSet result = statement.executeQuery();
result.close();
statement.close();
conn.close();