# JDBC概述
# 什么是JDBC
- JDBC，java database connectivity, 是java语言中，用来规范客户端程序，如何访问数据库的应用程序接口;
# 主要用途
- 与数据库建立连接
- 发送SQL语句
- 处理查询结果
# JDBC原生编程
# 加载客户端驱动
class Main{
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
# 获取连接
class Main{
public Connection getConnection(){
Connection connection=null;
try {
connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mytest", "root", "chenkaihai");
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
}
# 操作数据库
class Main{
public void statementSql(Connection connection){
if(connection==null){
return;
}
Statement statement= null;
try {
statement = connection.createStatement();
boolean dmlResult= statement.execute("delete my_table where id = 'mykey' ");
System.out.println("dml结果:"+dmlResult);
ResultSet resultSet = statement.executeQuery("select * from my_table");
while (resultSet.next()){
System.out.println("dql结果:");
System.out.println(resultSet.getString(1));
System.out.println(resultSet.getString(2));
System.out.println(resultSet.getString(3));
//....
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
# dql语句进行orm转换
- 从ResultSet获取数据，并转成java POJO对象.
# java数据库编程
# 数据库事务控制
public interface Connection extends Wrapper, AutoCloseable {
void setAutoCommit(boolean autoCommit) throws SQLException;
void setReadOnly(boolean readOnly) throws SQLException;
void setTransactionIsolation(int level) throws SQLException;
//省略其它抽象...
}
# spring项目设置隔离级别
- 事务隔离 可以是 session级别的
public enum Isolation {
DEFAULT(-1),
READ_UNCOMMITTED(1),
READ_COMMITTED(2),
REPEATABLE_READ(4),
SERIALIZABLE(8);
private final int value;
private Isolation(int value) {
this.value = value;
}
public int value() {
return this.value;
}
}
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface Transactional {
@AliasFor("transactionManager")
String value() default "";
@AliasFor("value")
String transactionManager() default "";
Propagation propagation() default Propagation.REQUIRED;
Isolation isolation() default Isolation.DEFAULT;
int timeout() default -1;
boolean readOnly() default false;
Class<? extends Throwable>[] rollbackFor() default {};
String[] rollbackForClassName() default {};
Class<? extends Throwable>[] noRollbackFor() default {};
String[] noRollbackForClassName() default {};
}
# 事务传播行为
需要注意，事务传播行为是spring中的概念，属于客户端范畴。
事务隔离级别，属于服务端范畴
允许事务
public enum Propagation {
REQUIRED(0), //有就加入，没有就新建
MANDATORY(2), //必须有，不然报错
REQUIRES_NEW(3), //不管有没有都新建事务
NESTED(6); //有就嵌套事务执行，没有就跟REQUIRED一样。 嵌套事务外层会影响内层，内层不会影响外层
private final int value;
private Propagation(int value) {
this.value = value;
}
public int value() {
return this.value;
}
}
- 不允许事务
public enum Propagation {
SUPPORTS(1), //有就加入，没有就算了
NOT_SUPPORTED(4), //不支持事务，有就挂起
NEVER(5); //不支持事务，不然报错
private final int value;
private Propagation(int value) {
this.value = value;
}
public int value() {
return this.value;
}
}
# JDBC底层理论
# 连接
package java.sql;
public interface Driver {
Connection connect(String url, java.util.Properties info);
}
package com.mysql.cj.jdbc;
public class NonRegisteringDriver implements java.sql.Driver {
public java.sql.Connection connect(String url, Properties info) throws SQLException {
//xxx: 省略其它抽象...(负载之类的)
return com.mysql.cj.jdbc.ConnectionImpl.getInstance(conStr.getMainHost());
}
}
package java.sql;
public interface Connection extends Wrapper, AutoCloseable {
Statement createStatement();
void setAutoCommit(boolean autoCommit);
void commit();
void rollback();
void close();
}
package com.mysql.cj.jdbc;
public class ConnectionImpl implements JdbcConnection, SessionEventListener, Serializable {
private NativeSession session = null;
public ConnectionImpl(HostInfo hostInfo) throws SQLException {
createNewIO(false);
//xxx:省略其它抽象...
}
public void createNewIO(boolean isForReconnect) {
synchronized (getConnectionMutex()) {
//xxx:省略其它抽象,如是否重试的判断
connectOneTryOnly(isForReconnect);
}
}
private void connectOneTryOnly(boolean isForReconnect) throws SQLException {
this.session.connect(this.origHostInfo, this.user, this.password, this.database, getLoginTimeout(), c);
//xxx: 省略其它抽象...
}
}
public class NativeSession extends CoreSession implements Serializable {
public void connect(HostInfo hi, String user, String password, String database, int loginTimeout, TransactionEventHandler transactionManager)
throws IOException {
SocketConnection socketConnection = new NativeSocketConnection();
socketConnection.connect(this.hostInfo.getHost(), this.hostInfo.getPort(), this.propertySet, getExceptionInterceptor(), this.log, loginTimeout);
//xxx: 省略其它抽象...
}
public void quit() {
try{
((NativeProtocol) this.protocol).quit();
//xxx: 省略其它抽象，本质上是调用socket.shutdownInput(),会因为读取超时导致链路关闭
}finally{
this.socketConnection.forceClose();
//xxx: 主要动作包括 inputStream.close(),outputStream.close(),socket.close()
}
}
}
# 连接池
public class DruidDataSource extends DruidAbstractDataSource implements DruidDataSourceMBean, ManagedDataSource, Referenceable, Closeable, Cloneable, ConnectionPoolDataSource, MBeanRegistration {
// xxx:存储连接池中的连接
private volatile DruidConnectionHolder[] connections;
//xxx: 创建连接的线程
private CreateConnectionThread createConnectionThread;
//xxx: 销毁连接的线程
private DestroyConnectionThread destroyConnectionThread;
@Override
public DruidPooledConnection getConnection() throws SQLException {
return getConnection(maxWait);
}
public DruidPooledConnection getConnection(long maxWaitMillis) throws SQLException {
init();
//xxx: 省略其它抽象...
return getConnectionDirect(maxWaitMillis);
}
public void init() throws SQLException {
if (inited) {
return;
}
//xxx: 解析驱动，常见的数据库如mysql,oracle等都可以实现自动识别驱动
resolveDriver();
//xxx: 线程开始操作..
createAndStartCreatorThread();
createAndStartDestroyThread();
}
public DruidPooledConnection getConnectionDirect(long maxWaitMillis) throws SQLException {
poolableConnection = getConnectionInternal(maxWaitMillis);
//xxx: 省略其它抽象...
}
private DruidPooledConnection getConnectionInternal(long maxWait) throws SQLException {
//xxx: 有则直接返回，无则创建连接(参数允许的情况下)
PhysicalConnectionInfo pyConnInfo = DruidDataSource.this.createPhysicalConnection();
//xxx: 省略其它抽象...
}
public PhysicalConnectionInfo createPhysicalConnection() throws SQLException {
//xxx: 省略其它抽象...
conn = getDriver().connect(url, info);
}
}
public class CreateConnectionThread extends Thread {
public void run() {
//xxx: 省略其它抽象,通过 CountDownLatch 结合 Condition实现线程循环操作
connection = createPhysicalConnection();
}
}
public class DestroyConnectionThread extends Thread {
public void run() {
initedLatch.countDown();
Thread.sleep(1000);
//xxx: 省略其它抽象,通过线程休眠 实现线程机制
destroyTask.run();
}
}