JDBC基础以及例子

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();