转帖自 http://yk94wo.blog.sohu.com/146586645.html
做java开发这么久了,一直都在使用mysql,oracle的驱动,只了解使用
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(url,username,password);
却不知道驱动程序到底为我们做了些什么,最近闲来无事,好好学习一下。
mysql开源,很容易就获得了驱动的源码,oracle的下周研究吧~~呵呵
话不多说,先贴代码。
package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class DBHelper {
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost/ad?useUnicode=true&characterEncoding=GBK&jdbcCompliantTruncation=false",
"root", "root");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/*dao中的方法*/
public List<Adv> getAllAdvs() {
Connection conn = null;
ResultSet rs = null;
PreparedStatement stmt = null;
String sql = "select * from adv where id = ?";
List<Adv> advs = new ArrayList<Adv>();
conn = DBHelper.getConnection();
if (conn != null) {
try {
stmt = conn.prepareStatement(sql);
stmt.setInt(1, new Integer(1));
rs = stmt.executeQuery();
if (rs != null) {
while (rs.next()) {
Adv adv = new Adv();
adv.setId(rs.getLong(1));
adv.setName(rs.getString(2));
adv.setDesc(rs.getString(3));
adv.setPicUrl(rs.getString(4));
advs.add(adv);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return advs;
}
}
1.Class.forName("com.mysql.jdbc.Driver");
这句是使用当前类加载器去加载mysql的驱动类Driver,所有数据库厂商的数据库驱动类必须实现java.sql.Driver接口,mysql也不例外。
看到这里不尽奇怪,只是加载了驱动类,但DriverManager如何知道该驱动类呢?
查看Driver类:
public class Driver implements java.sql.Driver
{
//
// Register ourselves with the DriverManager
//
static
{
try {
java.sql.DriverManager.registerDriver(new Driver());
}
catch (java.sql.SQLException E) {
E.printStackTrace();
}
}
//其它无关部分省略
}
上面的红色字体揭开了答案,原来,在类加载的使用,静态块被调用,驱动类Driver向DriverManager注册了自己,所以以后就可以被使用到了,同时,我们应该注意到,这里加载类使用的是Class.forName("");方法,它默认加载类时会调用static{}代码块,而ClassLoader则默认不会,切记。
查看DriverManager.registerDriver(--)方法:
public static synchronized void registerDriver(java.sql.Driver driver)
throws SQLException {
if (!initialized) {
initialize();
}
DriverInfo di = new DriverInfo();
di.driver = driver;
di.driverClass = driver.getClass();
di.driverClassName = di.driverClass.getName();
// Not Required -- drivers.addElement(di);
writeDrivers.addElement(di);
println("registerDriver: " + di);
/* update the read copy of drivers vector */
readDrivers = (java.util.Vector) writeDrivers.clone();
}
wirteDrivers和readDrivers是Vector对像,用于存储封装好的driver类信息。
2.接下来DBHelper.java类中,
conn = DriverManager.getConnection("jdbc:mysql://localhost/ad?useUnicode=true&characterEncoding=GBK&jdbcCompliantTruncation=false", "root", "root");
那么connection到底是如何生成的?我们一步一步看,
DriverManager:
public static Connection getConnection(String url,
String user, String password) throws SQLException {
java.util.Properties info = new java.util.Properties();
// Gets the classloader of the code that called this method, may
// be null.
ClassLoader callerCL = DriverManager.getCallerClassLoader();
if (user != null) {
info.put("user", user);
}
if (password != null) {
info.put("password", password);
}
return (getConnection(url, info, callerCL));
}
总共有4个重载的getConnection()方法,最终都调用私有的
private static Connection getConnection(
String url, java.util.Properties info, ClassLoader callerCL) throws SQLException {
java.util.Vector drivers = null;
/*
* When callerCl is null, we should check the application's
* (which is invoking this class indirectly)
* classloader, so that the JDBC driver class outside rt.jar
* can be loaded from here.
*/
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 + "/")");
if (!initialized) {
initialize();
}
synchronized (DriverManager.class){
// use the readcopy of drivers
drivers = readDrivers;
}
// Walk through the loaded drivers attempting to make a connection.
// Remember the first exception that gets raised so we can reraise it.
SQLException reason = null;
for (int i = 0; i < drivers.size(); i++) {
DriverInfo di = (DriverInfo)drivers.elementAt(i);
// If the caller does not have permission to load the driver then
// skip it.
if ( getCallerClass(callerCL, di.driverClassName ) != di.driverClass ) {
println(" skipping: " + di);
continue;
}
try {
println(" trying " + di);
Connection result = di.driver.connect(url, info);
if (result != null) {
// Success!
println("getConnection returning " + di);
return (result);
}
} catch (SQLException ex) {
if (reason == null) {
reason = ex;
}
}
}
// if we got here nobody could connect.
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");
}
上面红色字体中有一段关于caller的注释,代码意思是如果DriverManager类的类加载器为空的话,就使用当前线程的类加载器。仔细想想,DriverManager在rt.jar包中,它是由JDK的启动类加载器加载的,而启动类加载器是C编写的,所以取得的都是空,再者,使用当前线程类加载器的话,那么交由程序编写者来保证能够加载驱动类。而不至于驱动器类无法加载。非常高明的手段~!
3.上面代码Connection result = di.driver.connect(url, info);可知,由Driver来生成Connection:
Driver:
public synchronized java.sql.Connection connect(String Url, Properties Info)
throws java.sql.SQLException
{
if ((_Props = parseURL(Url, Info)) == null) {
return null;
}
else {
return new Connection (host(), port(), _Props, database(), Url, this);
}
}
我们来看看Conenction的构造方法:
public Connection(String Host, int port, Properties Info, String Database,
String Url, Driver D) throws java.sql.SQLException
{
if (Driver.trace) {
Object[] Args = {Host, new Integer(port), Info,
Database, Url, D};
Debug.methodCall(this, "constructor", Args);
}
if (Host == null) {
_Host = "localhost";
}
else {
_Host = new String(Host);
}
_port = port;
if (Database == null) {
throw new SQLException("Malformed URL '" + Url + "'.", "S1000");
}
_Database = new String(Database);
_MyURL = new String(Url);
_MyDriver = D;
String U = Info.getProperty("user");
String P = Info.getProperty("password");
if (U == null || U.equals(""))
_User = "nobody";
else
_User = new String(U);
if (P == null)
_Password = "";
else
_Password = new String(P);
// Check for driver specific properties
if (Info.getProperty("autoReconnect") != null) {
_high_availability = Info.getProperty("autoReconnect").toUpperCase().equals("TRUE");
}
if (_high_availability) {
if (Info.getProperty("maxReconnects") != null) {
try {
int n = Integer.parseInt(Info.getProperty("maxReconnects"));
_max_reconnects = n;
}
catch (NumberFormatException NFE) {
throw new SQLException("Illegal parameter '" +
Info.getProperty("maxReconnects")
+"' for maxReconnects", "0S100");
}
}
if (Info.getProperty("initialTimeout") != null) {
try {
double n = Integer.parseInt(Info.getProperty("intialTimeout"));
_initial_timeout = n;
}
catch (NumberFormatException NFE) {
throw new SQLException("Illegal parameter '" +
Info.getProperty("initialTimeout")
+"' for initialTimeout", "0S100");
}
}
}
if (Info.getProperty("maxRows") != null) {
try {
int n = Integer.parseInt(Info.getProperty("maxRows"));
if (n == 0) {
n = -1;
} // adjust so that it will become MysqlDefs.MAX_ROWS
// in execSQL()
_max_rows = n;
}
catch (NumberFormatException NFE) {
throw new SQLException("Illegal parameter '" +
Info.getProperty("maxRows")
+"' for maxRows", "0S100");
}
}
if (Info.getProperty("useUnicode") != null) {
String UseUnicode = Info.getProperty("useUnicode").toUpperCase();
if (UseUnicode.startsWith("TRUE")) {
_do_unicode = true;
}
if (Info.getProperty("characterEncoding") != null) {
_Encoding = Info.getProperty("characterEncoding");
// Attempt to use the encoding, and bail out if it
// can't be used
try {
String TestString = "abc";
TestString.getBytes(_Encoding);
}
catch (UnsupportedEncodingException UE) {
throw new SQLException("Unsupported character encoding '" +
_Encoding + "'.", "0S100");
}
}
}
if (Driver.debug)
System.out.println("Connect: " + _User + " to " + _Database);
try {
_IO = new MysqlIO(Host, port);
_IO.init(_User, _Password);
_IO.sendCommand(MysqlDefs.INIT_DB, _Database, null);
_isClosed = false;
}
catch (java.sql.SQLException E) {
throw E;
}
catch (Exception E) {
E.printStackTrace();
throw new java.sql.SQLException("Cannot connect to MySQL server on " + _Host + ":" + _port + ". Is there a MySQL server running on the machine/port you are trying to connect to? (" + E.getClass().getName() + ")", "08S01");
}
}
我们查看MysqlIO的构造方法:
MysqlIO(String Host, int port) throws IOException, java.sql.SQLException
{
_port = port;
_Host = Host;
_Mysql_Conn = new Socket(_Host, _port);
_Mysql_Buf_Input = new BufferedInputStream(_Mysql_Conn.getInputStream());
_Mysql_Buf_Output = new BufferedOutputStream(_Mysql_Conn.getOutputStream());
_Mysql_Input = new DataInputStream(_Mysql_Buf_Input);
_Mysql_Output = new DataOutputStream(_Mysql_Buf_Output);
}
现在大家都应该清楚了,最终是创建了一个socket对象,来与DB Server交互。
3.在DBHelper.java中:PreparedStatement stmt=conn.prepareStatement(sql);
我们都知道,perpareStatement是用来预编译sql的,可以大幅提高sql执行效率,同时避免sql注入问题 。
那么它到底如何实现这一点的呢?
我们继续往下看,
Connection:
public java.sql.PreparedStatement prepareStatement(String Sql) throws java.sql.SQLException
{
if (Driver.trace) {
Object[] Args = {Sql};
Debug.methodCall(this, "prepareStatement", Args);
}
PreparedStatement PStmt = new org.gjt.mm.mysql.PreparedStatement(this, Sql, _Database);
if (Driver.trace) {
Debug.returnValue(this, "prepareStatement", PStmt);
}
return PStmt;
}
============================================================
PreparedStatement:
public class PreparedStatement extends org.gjt.mm.mysql.Statement
implements java.sql.PreparedStatement
{
private String _Sql = null;
private String[] _TemplateStrings = null;
private String[] _ParameterStrings = null;
private InputStream[] _ParameterStreams = null;
private boolean[] _IsStream = null;
private Connection _Conn = null;
private boolean _do_concat = false;
private boolean _has_limit_clause = false;
/**
* Constructor for the PreparedStatement class.
* Split the SQL statement into segments - separated by the arguments.
* When we rebuild the thing with the arguments, we can substitute the
* args and join the whole thing together.
*
* @param conn the instanatiating connection
* @param sql the SQL statement with ? for IN markers
* @exception java.sql.SQLException if something bad occurs
*/
public PreparedStatement(Connection Conn, String Sql, String Catalog) throws java.sql.SQLException
{
super(Conn, Catalog);
if (Sql.indexOf("||") != -1) {
_do_concat = true;
}
_has_limit_clause = (Sql.toUpperCase().indexOf("LIMIT") != -1);
Vector V = new Vector();
boolean inQuotes = false;
int lastParmEnd = 0, i;
_Sql = Sql;
_Conn = Conn;
for (i = 0; i < _Sql.length(); ++i) {
int c = _Sql.charAt(i);
if (c == '/'')
inQuotes = !inQuotes;
if (c == '?' && !inQuotes)
{
V.addElement(_Sql.substring (lastParmEnd, i));
lastParmEnd = i + 1;
}
}
V.addElement(_Sql.substring (lastParmEnd, _Sql.length()));
_TemplateStrings = new String[V.size()];
_ParameterStrings = new String[V.size() - 1];
_ParameterStreams = new InputStream[V.size() - 1];
_IsStream = new boolean[V.size() - 1];
clearParameters();
for (i = 0 ; i < _TemplateStrings.length; ++i) {
_TemplateStrings[i] = (String)V.elementAt(i);
}
for (int j = 0; j < _ParameterStrings.length; j++) {
_IsStream[j] = false;
}
}
..............
}
注意PreparedStatement的四个成员变量,他们是现在客户端预编译的关键,注意,这里是客户端预编译。
org.gjt.mm.mysql中并没有提供接口用于使用真正意义上的服务器端预编译。所以执行效率并和Statement差不多。
我们一般使用 PreparedStatement的sql语句入下:
select * from adv where id = ?
通过对?的定位,找出那些非字符?,即不在''中的?号,来分隔sql语句,得到sql语句数组,放在_TemplateStrings中。
当我们调用setXXX(int index, XXX xxx);时,实际上是将参数值放到_ParameterStrings中,如果是类似于流和非基本类型对象的值,则放入_ParameterStreams中,并在_IsStream中标记。
private String[] _TemplateStrings = null; //
private String[] _ParameterStrings = null;
private InputStream[] _ParameterStreams = null;
private boolean[] _IsStream = null;
当我们执行
ResultSet rs = stmt.executeQuery();时
实际上是将这些拼装起来,重新生成完整的sql语句。发送到服务器端。
再次说明,org.gjt.mm.mysql并没有实现提供接口用于使用真正的服务器端sql预编译。
但是在后来的mysql官方驱动类中,已经实现了,我将在下一篇中详述,其实主要是生成的sql格式和命令不一样,就是说在发送给DB服务器的命令中明确指定需要预编译。
这时,我们还有一个问题,就是PreparedStatement如何防止sql注入的?
很简单,
PreparedStatement:
public void setString(int parameterIndex, String X) throws java.sql.SQLException
{
// if the passed string is null, then set this column to null
if(X == null) {
set(parameterIndex, "null");
}
else {
StringBuffer B = new StringBuffer();
int i;
B.append('/'');
for (i = 0 ; i < X.length() ; ++i) {
char c = X.charAt(i);
if (c == '//' || c == '/'' || c == '"') {
B.append((char)'//');
}
B.append(c);
}
B.append('/'');
set(parameterIndex, B.toString());
}
}
也就是在传进来的string 的前后强制加上了 " ' "号,明确表明这是一个string变量,也就避免了sql注入。
今天就到此为止了,下次再详细分析mysql官方驱动如何实现真正预编译的,以及分析Oracle驱动的实现.