说起jdbc,我相信很多人都不陌生,在最开始的web项目中,我们常常用它来连接数据库执行sql语句,下面是一个连接mysql的例子:
/**
* 定义数据库连接辅助类
*/
public class DBhelper {
private static final String DRIVERNAME = "com.mysql.cj.jdbc.Driver";
private static final String URL = "jdbc:mysql://127.0.0.1:3306/test";
private static final String USER = "root";
private static final String PASSWORD = "123456";
private Connection conn = null;
private Statement st = null;
private PreparedStatement ppst = null;
private ResultSet rs = null;
/*加载驱动*/
static {
try {
Class.forName(DRIVERNAME).newInstance();
} catch (Exception e) {
e.printStackTrace();
System.out.println("加载驱动失败");
}
}
/*获取数据库连接*/
public Connection getConn(){
try {
conn = DriverManager.getConnection(URL,USER,PASSWORD);
} catch (SQLException throwables) {
throwables.printStackTrace();
System.out.println("获取数据库连接失败");
}
return conn;
}
/*释放数据库连接*/
public void releaseConn(){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
if(ppst!=null){
try {
ppst.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}
测试一下:
public class TestJdbc {
private Connection conn = null;
private Statement st = null;
private PreparedStatement ppst = null;
private ResultSet rs = null;
private List<Object> selectUser(User user){
List<Object> list = new ArrayList<>();
DBhelper dBhelper = new DBhelper();
conn = dBhelper.getConn();
String sql = "select * from blog_user where login_num="+user.getLoginNum()+" and password ="+user.getPassword();
try {
st = conn.createStatement();
rs = st.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while (rs.next()){
Map map = new HashMap();
for (int i = 1;i <= columnCount;i++){
map.put(rsmd.getColumnLabel(i),rs.getObject(i));
}
list.add(map);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
dBhelper.releaseConn();
}
return list;
}
public static void main(String[] args) {
TestJdbc testJdbc = new TestJdbc();
User user = new User();
user.setLoginNum(123456);
user.setPassword("123456");
List list = testJdbc.selectUser(user);
}
}
debug一下:
然而在实际开发中,我们常常用到的框架是mybatis,其实mybatis就是对jdbc的封装,在我们以后的开发中我们可能会遇到关于持久层的各种问题,我们理解了jdbc的原理,那么mybatis又有何难?
依然以mysql为例,首先,我们来看数据库驱动Driver的加载过程:
在上面的贴出的代码中,我们可以看到一个静态代码块,利用class.forName()加载这个驱动,如下图:
private static final String DRIVERNAME = "com.mysql.cj.jdbc.Driver";
/*加载驱动*/
static {
try {
Class.forName(DRIVERNAME).newInstance();
} catch (Exception e) {
e.printStackTrace();
System.out.println("加载驱动失败");
}
}
我们点进这个驱动,发现它继承了一个父类,实现了一个接口,里面有一个方法----注册驱动, 如下图:
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
public Driver() throws SQLException {
}
static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
}
我们先来看父接口 Driver,如下图:
这是一个顶层接口(由于太长,注释删掉一部分),简单理解一下注释,我们就会明白,这是一个所有的驱动必须实现的方法,也就是说,这是一个java连接数据库的一个接口,一个规范,数据库有很多种,因此数据库驱动也分很多种,但是不管你是那种数据库驱动,必须都要实现这个接口,遵循这个规范,才能连接数据库,无疑给我们带来了很大的方便,更换数据库就代表着更换驱动,而所有驱动都实现了这个接口,那我们只需要在利用反射加载驱动的class.forName()方法中注明需要加载的驱动就ok了,这样就可以适配所有的数据库.
/**
* The interface that every driver class must implement.
* <P>The Java SQL framework allows for multiple database drivers.
*
* <P>Each driver should supply a class that implements
* the Driver interface.
*
* <P>The DriverManager will try to load as many drivers as it can
* find and then for any given connection request, it will ask each
* driver in turn to try to connect to the target URL.
*
* <P>It is strongly recommended that each Driver class should be
* small and standalone so that the Driver class can be loaded and
* queried without bringing in vast quantities of supporting code.
*
* <P>When a Driver class is loaded, it should create an instance of
* itself and register it with the DriverManager. This means that a
* user can load and register a driver by calling:
* <p>
* {@code Class.forName("foo.bah.Driver")}
*/
public interface Driver {
/**
* Attempts to make a database connection to the given URL.
* The driver should return "null" if it realizes it is the wrong kind
* of driver to connect to the given URL. This will be common, as when
* the JDBC driver manager is asked to connect to a given URL it passes
* the URL to each loaded driver in turn.
*
*/
Connection connect(String url, java.util.Properties info)
throws SQLException;
/**
* Retrieves whether the driver thinks that it can open a connection
* to the given URL. Typically drivers will return <code>true</code> if they
* understand the sub-protocol specified in the URL and <code>false</code> if
* they do not.
*/
boolean acceptsURL(String url) throws SQLException;
DriverPropertyInfo[] getPropertyInfo(String url, java.util.Properties info)
throws SQLException;
int getMajorVersion();
int getMinorVersion();
boolean jdbcCompliant();
public Logger getParentLogger() throws SQLFeatureNotSupportedException;
}
再看NonRegisteringDriver类
它实现了Driver,实际上,它是mysql驱动的一部分,里面的一些方法是关于连接mysql数据库的一些配置细节,根据一些连接属性创建一个真正连接数据库的网络通道
public class NonRegisteringDriver implements Driver {
public static String getOSName() {
return Constants.OS_NAME;
}
public static String getPlatform() {
return Constants.OS_ARCH;
}
static int getMajorVersionInternal() {
return StringUtils.safeIntParse("8");
}
static int getMinorVersionInternal() {
return StringUtils.safeIntParse("0");
}
public NonRegisteringDriver() throws SQLException {
}
//接收url,验证url的合法性
public boolean acceptsURL(String url) throws SQLException {
try {
return ConnectionUrl.acceptsUrl(url);
} catch (CJException var3) {
throw SQLExceptionsMapping.translateException(var3);
}
}
public Connection connect(String url, Properties info) throws SQLException {
try {
try {
if (!ConnectionUrl.acceptsUrl(url)) {
return null;
} else {
//负载均衡式访问
ConnectionUrl conStr = ConnectionUrl.getConnectionUrlInstance(url, info);
switch(conStr.getType()) {
case SINGLE_CONNECTION:
return ConnectionImpl.getInstance(conStr.getMainHost());
case FAILOVER_CONNECTION:
case FAILOVER_DNS_SRV_CONNECTION:
return FailoverConnectionProxy.createProxyInstance(conStr);
case LOADBALANCE_CONNECTION:
case LOADBALANCE_DNS_SRV_CONNECTION:
return LoadBalancedConnectionProxy.createProxyInstance(conStr);
case REPLICATION_CONNECTION:
case REPLICATION_DNS_SRV_CONNECTION:
return ReplicationConnectionProxy.createProxyInstance(conStr);
default:
return null;
}
}
} catch (UnsupportedConnectionStringException var5) {
return null;
} catch (CJException var6) {
throw (UnableToConnectException)ExceptionFactory.createException(UnableToConnectException.class, Messages.getString("NonRegisteringDriver.17", new Object[]{var6.toString()}), var6);
}
} catch (CJException var7) {
throw SQLExceptionsMapping.translateException(var7);
}
}
public int getMajorVersion() {
return getMajorVersionInternal();
}
public int getMinorVersion() {
return getMinorVersionInternal();
}
public DriverPropertyInfo[] getPropertyInfo(String url, Properties info) throws SQLException {
try {
String host = "";
String port = "";
String database = "";
String user = "";
String password = "";
if (!StringUtils.isNullOrEmpty(url)) {
ConnectionUrl connStr = ConnectionUrl.getConnectionUrlInstance(url, info);
if (connStr.getType() == Type.SINGLE_CONNECTION) {
HostInfo hostInfo = connStr.getMainHost();
info = hostInfo.exposeAsProperties();
}
}
if (info != null) {
host = info.getProperty(PropertyKey.HOST.getKeyName());
port = info.getProperty(PropertyKey.PORT.getKeyName());
database = info.getProperty(PropertyKey.DBNAME.getKeyName());
user = info.getProperty(PropertyKey.USER.getKeyName());
password = info.getProperty(PropertyKey.PASSWORD.getKeyName());
}
DriverPropertyInfo hostProp = new DriverPropertyInfo(PropertyKey.HOST.getKeyName(), host);
hostProp.required = true;
hostProp.description = Messages.getString("NonRegisteringDriver.3");
DriverPropertyInfo portProp = new DriverPropertyInfo(PropertyKey.PORT.getKeyName(), port);
portProp.required = false;
portProp.description = Messages.getString("NonRegisteringDriver.7");
DriverPropertyInfo dbProp = new DriverPropertyInfo(PropertyKey.DBNAME.getKeyName(), database);
dbProp.required = false;
dbProp.description = Messages.getString("NonRegisteringDriver.10");
DriverPropertyInfo userProp = new DriverPropertyInfo(PropertyKey.USER.getKeyName(), user);
userProp.required = true;
userProp.description = Messages.getString("NonRegisteringDriver.13");
DriverPropertyInfo passwordProp = new DriverPropertyInfo(PropertyKey.PASSWORD.getKeyName(), password);
passwordProp.required = true;
passwordProp.description = Messages.getString("NonRegisteringDriver.16");
JdbcPropertySet propSet = new JdbcPropertySetImpl();
propSet.initializeProperties(info);
List<DriverPropertyInfo> driverPropInfo = propSet.exposeAsDriverPropertyInfo();
DriverPropertyInfo[] dpi = new DriverPropertyInfo[5 + driverPropInfo.size()];
dpi[0] = hostProp;
dpi[1] = portProp;
dpi[2] = dbProp;
dpi[3] = userProp;
dpi[4] = passwordProp;
System.arraycopy(driverPropInfo.toArray(new DriverPropertyInfo[0]), 0, dpi, 5, driverPropInfo.size());
return dpi;
} catch (CJException var17) {
throw SQLExceptionsMapping.translateException(var17);
}
}
public boolean jdbcCompliant() {
return false;
}
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
throw new SQLFeatureNotSupportedException();
}
static {
try {
Class.forName(AbandonedConnectionCleanupThread.class.getName());
} catch (ClassNotFoundException var1) {
}
}
}
然后就是 DriverManager.registerDriver( new Driver() ) 这个注册驱动的方法,它是将自己传给DriverManager,我们点开这个方法:
这里将Driver封装进DriverInfo类中,添加在DriverManager的静态List中,便于DriverManager管理驱动
/**
class DriverManager
*/
private final static CopyOnWriteArrayList<DriverInfo> registeredDrivers = new CopyOnWriteArrayList<>();
public static synchronized void registerDriver(java.sql.Driver driver)
throws SQLException {
registerDriver(driver, null);
}
public static synchronized void registerDriver(java.sql.Driver driver,
DriverAction da)
throws SQLException {
/* Register the driver if it has not already been added to our list */
if(driver != null) {
registeredDrivers.addIfAbsent(new DriverInfo(driver, da));
} else {
// This is for compatibility with the original DriverManager
throw new NullPointerException();
}
println("registerDriver: " + driver);
}
再点开: registeredDrivers.addIfAbsent(new DriverInfo(driver, da))
方法名大概是说 如果缺席(没有)就添加
类里面有一个array,看注释,这是一个放置驱动类的临时数组,只能通过getArray和setArray获取和设置
而 indexOf(e, snapshot, 0, snapshot.length) >= 0 ? false :addIfAbsent(e, snapshot)
我分析了一下,是返回 参数e 在 数组snapshot 的下标,这里的 e 就是 上面的new DriverInfo(driver, da), snapshot 为上面提到的 array
当e为null,返回snapshot中null的下标
如果snapshot中没有e,则返回 -1
也就是说其实是判断snapshot中有没有e,没有的话,就调用方法添加
再看下面的添加方法,就是把 Driver 放进 array 中,相当于把驱动注册进DriverManager中,至于这里为什么是一个数组?假如我们一个系统同时连接两种或者多种数据库,那我们就需要多个驱动,因此这里是一个数组,当我们需要连接哪种数据库的时候,就可以从这里取出对应的驱动去获取连接
/**类: class CopyOnWriteArrayList<E>
implements List<E>, RandomAccess, Cloneable,Serializable
*/
/** The array, accessed only via getArray/setArray. */
private transient volatile Object[] array;
private static int indexOf(Object o, Object[] elements,
int index, int fence) {
if (o == null) {
for (int i = index; i < fence; i++)
if (elements[i] == null)
return i;
} else {
for (int i = index; i < fence; i++)
if (o.equals(elements[i]))
return i;
}
return -1;
}
public boolean addIfAbsent(E e) {
Object[] snapshot = getArray();
return indexOf(e, snapshot, 0, snapshot.length) >= 0 ? false :
addIfAbsent(e, snapshot);
}
private boolean addIfAbsent(E e, Object[] snapshot) {
final ReentrantLock lock = this.lock;
lock.lock();
try {
Object[] current = getArray();
int len = current.length;
if (snapshot != current) {
// Optimize for lost race to another addXXX operation
int common = Math.min(snapshot.length, len);
for (int i = 0; i < common; i++)
if (current[i] != snapshot[i] && eq(e, current[i]))
return false;
if (indexOf(e, current, common, len) >= 0)
return false;
}
Object[] newElements = Arrays.copyOf(current, len + 1);
newElements[len] = e;
setArray(newElements);
return true;
} finally {
lock.unlock();
}
}
我们再看如何获取连接Connection,DriverManager遍历其中的所有的驱动,然后获取该驱动的连接,这种方法或许有点笨,但是可以兼容所有的数据库驱动,而这里真正连接数据库的操作Connection con = aDriver.driver.connect(url, info);调用的是NonRegisteringDriver中的connect()方法,返回一个Connection实例
// Worker method called by the public getConnection() methods.
private static Connection getConnection(
String url, java.util.Properties info, Class<?> caller) throws SQLException {
ClassLoader callerCL = caller != null ? caller.getClassLoader() : null;
synchronized(DriverManager.class) {
// synchronize loading of the correct classloader.
if (callerCL == null) {
callerCL = Thread.currentThread().getContextClassLoader();
}
}
if(url == null) {
throw new SQLException("The url cannot be null", "08001");
}
println("DriverManager.getConnection(\"" + url + "\")");
SQLException reason = null;
for(DriverInfo aDriver : registeredDrivers) {
if(isDriverAllowed(aDriver.driver, callerCL)) {
try {
println(" trying " + aDriver.driver.getClass().getName());
Connection con = aDriver.driver.connect(url, info);
if (con != null) {
println("getConnection returning " + aDriver.driver.getClass().getName());
return (con);
}
} catch (SQLException ex) {
if (reason == null) {
reason = ex;
}
}
} else {
println(" skipping: " + aDriver.getClass().getName());
}
}
if (reason != null) {
println("getConnection failed: " + reason);
throw reason;
}
println("getConnection: no suitable driver found for "+ url);
throw new SQLException("No suitable driver found for "+ url, "08001");
}
再来看返回的连接 Connection,这也是一个接口,定义了一些数据库连接都要有的方法会用到的方法,我们看一下它的继承类图,如下:
package java.sql;
import java.util.Properties;
import java.util.concurrent.Executor;
/**
* <P>A connection (session) with a specific
* database. SQL statements are executed and results are returned
* within the context of a connection.
* <pre>
* java.util.Map map = con.getTypeMap();
* map.put("mySchemaName.ATHLETES", Class.forName("Athletes"));
* con.setTypeMap(map);
* </pre>
*
* @see DriverManager#getConnection
* @see Statement
* @see ResultSet
* @see DatabaseMetaData
*/
public interface Connection extends Wrapper, AutoCloseable {
//所有的数据库连接都要有的方法
Statement createStatement() throws SQLException;
PreparedStatement prepareStatement(String sql)
throws SQLException;
CallableStatement prepareCall(String sql) throws SQLException;
String nativeSQL(String sql) throws SQLException;
void setAutoCommit(boolean autoCommit) throws SQLException;
boolean getAutoCommit() throws SQLException;
void commit() throws SQLException;
void rollback() throws SQLException;
void close() throws SQLException;
boolean isClosed() throws SQLException;
}
public interface MysqlConnection {
//略
//关于mysql连接的一些方法
}
public interface JdbcConnection extends Connection, MysqlConnection, TransactionEventHandler {
//略
}
public class ConnectionImpl implements JdbcConnection, SessionEventListener, Serializable {
//略
}
根据上面的分析,有如下图示:
实际上jdbc的这种设计是一种桥接模式
桥接模式的定义与特点
定义:
将抽象与实现分离,使它们可以独立变化。它是用组合/聚合关系代替继承关系来实现,从而降低了抽象和实现这两个可变维度的耦合度。例如针对一个图形,我们可以设计颜色和形状两个变化维度
优点:
由于抽象与实现分离,所以扩展能力强;实现细节对客户透明
缺点:
由于聚合关系建立在抽象层,要求开发者针对抽象化进行设计与编程,这增加了系统的理解与设计难度
Jdbc有两个维度: 驱动Driver 与 连接Connection