import ?java.sql. * ;

 /**
?*?使用ODBC的方法：<br>
?*?sun.jdbc.odbc.JdbcOdbcDriver<br>
?*?jdbc:odbc:?+?odbcName<br>
?*
?*?oracle.thin.Driver<br>
?*?qwe.sql.qweMySqlDriver<br>
?*?symantec.dbanywhere.Driver<br>
?*
?*?訪問MS?SQLServer的方法<br>
?*?driveName=com.microsoft.jdbc.sqlserver.SQLServerDriver;<br>
?*?url=jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=demo;<br>
?*?訪問MySQL的方法：<br>
?*?DBDriver=com.mysql.jdbc.Driver<br>
?*?URL=jdbc:mysql://localhost/demo<br>
? */

public ? abstract ? class ?AbstractConnectionFactory? {

?? private ?String?userName;
?? private ?String?password;
?? private ?String?driverName;
?? private ?String?url;
?? private ?java.sql.Connection?connection;
?? /**
???*?工廠方法，返回實際創建的連接對象
???*? @return
??? */

?? /**
???*?根據設置的連接參數創建一個新的連接實例
???*? @return
??? */
?? private ?Connection?getNewConnection()? {
???? try ? {
?????? this .connection.close();? // 試圖關閉連接
????}
???? finally ? {
?????? this .connection? = ? null ;? // 釋放連接
?????? try ? {
????????Class.forName( this .driverName);? // 加載驅動程序
???????? try ? {
?????????? this .connection? = ?DriverManager.getConnection( this .url,? this .userName,
?????????????? this .password);
????????}
???????? catch ?(SQLException?e)? {
?????????? throw ?e;
????????}
??????}
?????? finally ? {
???????? return ? this .connection;? // 返回新建立的連接
??????}
????}
??}

?? public ?String?getUserName()? {
???? return ?userName;
??}

?? public ? void ?setUserName(String?userName)? {
???? this .userName? = ?userName;
??}

?? public ?String?getPassword()? {
???? return ?password;
??}

?? public ? void ?setPassword(String?password)? {
???? this .password? = ?password;
??}

?? public ?String?getDriverName()? {
???? return ?driverName;
??}

?? public ? void ?setDriverName(String?driverName)? {
???? this .driverName? = ?driverName;
??}

?? public ?String?getUrl()? {
???? return ?url;
??}

?? public ? void ?setUrl(String?url)? {
???? this .url? = ?url;
??}

?? public ?java.sql.Connection?getConnection()? {
???? if ?(connection? != ? null )? {
?????? try ? {
???????? if ?(connection.isClosed())? {
??????????connection? = ? null ;
??????????getNewConnection();
????????}
??????}
?????? catch ?(SQLException?ex)? {
??????}
????}
???? if ?(connection? == ? null )? {? // 沒有設置連接則創建一個連接
??????getNewConnection();
????}

???? return ?connection;

??}

}
package ?skydev.modules.data;

 public ? class ?ConnectionFactory
???? extends ?AbstractConnectionFactory? {

?? public ?ConnectionFactory()? {
??}

?

}
package ?skydev.modules.data;

 import ?java.sql. * ;
 import ?java.sql.PreparedStatement;

 public ? abstract ? class ?DatabaseObject? {

?? protected ?Connection?connection? = ? null ;
?? protected ?ResultSet?resultSet? = ? null ;
?? protected ?ResultSetMetaData?resultSetMetaData? = ? null ;
?? private ?ConnectionFactory?connectionFactory? = ? null ;
?? private ?java.sql.Statement?statement = null ; // =new?Statement();

?? public ?DatabaseObject()? {
??}

?? public ?DatabaseObject(ConnectionFactory?connectionFactory)? {
???? this .setConnectionFactory(connectionFactory);
??}

?? /**
???*?執行查詢
???*? @param ?sql?要執行的Sql語句
???*? @return 　返回查詢的結果集?,查詢失敗返回null
??? */
?? public ?ResultSet?getResultSet(String?sql)? {
???? // ?Statement?stmt?=?null;
???? try ? {
?????? // ??stmt?=?connection.createStatement();
?????? this .resultSet? = ?statement.executeQuery(sql);? // 保留內部指針
????}
???? catch ?(SQLException?e)? {
??????e.printStackTrace();
?????? this .resultSet? = ? null ;
????}
???? finally ? {
?????? return ? this .resultSet;
????}
??}

?? /**
???*?獲取外部指定ResltSet的ResultSetMetaData數據
???*? @param ?resultSet?要獲取的ResultSet
???*? @return ?失敗返回null
??? */
?? public ?ResultSetMetaData?getResultSetMetaData(ResultSet?resultSet)? {
????ResultSetMetaData?resultSetMetaData? = ? null ;
???? try ? {
??????resultSetMetaData? = ?resultSet.getMetaData();
????}
???? catch ?(SQLException?e)? {
??????e.printStackTrace();
??????resultSetMetaData? = ? null ;
????}
???? finally ? {
?????? return ?resultSetMetaData;
????}
??}

?? /**
???*?獲取最近一次設置或者返回的ResultSet的ResultMetaData數據，
???*?比方說調用了：getResultSet(sql)方法，然后調用getResultSetMetaData方法
???*?可以獲得相應的ResultSetMetaData數據。
???*? @return
??? */
?? public ?ResultSetMetaData?getResultSetMetaData()? {
???? return ? this .getResultSetMetaData( this .resultSet);
??}

?? /**
???*?執行存儲過程
???*? @param ?spName?存儲過程名稱
???*? @return
??? */
?? public ?ResultSet?Execute(String?spName)? {
???? // 對此數據庫執行一個?SQL?查詢
????ResultSet?resultSet? = ? null ;
???? try ? {
?????? // ?PreparedStatement?stmt?=?(PreparedStatement)?connection.createStatement();
??????resultSet? = ?statement.executeQuery(spName);
????}
???? catch ?(Exception?e)? {
??????System.out.println(execute?error? +
?????????????????????????e.getMessage());
????}
???? return ?resultSet;
??}

?? /**
???*?設置數據庫連接工廠，對此類的所有操作之前，必須調用該方法，
???*?設置數據庫連接工廠。
???*? @param ?connectionFactory?數據庫連接工廠ConnectionFactory?類對象以及
???*?派生類對象。
??? */
?? public ? void ?setConnectionFactory(ConnectionFactory?connectionFactory)? {
???? this .connectionFactory? = ?connectionFactory;
????connection? = ?connectionFactory.getConnection();
???? try ? {
??????statement? = ?connection.createStatement();
????}
???? catch ?(SQLException?ex)? {
??????System.err.println(ex);
????}

??}

?? public ?Connection?getConnection()? {
???? return ?connection;
??}

?? public ?java.sql.Statement?getStatement()? {
???? return ?statement;
??}

}
package ?skydev.modules.data;


 public ? class ?DbObject
???? extends ?DatabaseObject? {
? // ?private?final?static?String?driveName?=?sun.jdbc.obdc.JdbcOdbcDriver;

?? public ?DbObject()? {
???? super ( new ?SqlServerConnectionFactory(localhost,? 1433 ,?TheSchool,?sa,
?????????????????????????????????????????));
??}

?? public ?DbObject(ConnectionFactory?connectionFactory)? {
???? super (connectionFactory);
??}
}
package ?skydev.modules.data;

 public ? final ? class ?SqlServerConnectionFactory
???? extends ?ConnectionFactory? {
?? private ? final ?String?dbDriver? =
??????com.microsoft.jdbc.sqlserver.SQLServerDriver;
?? private ?String?host;
?? private ? int ?port;
?? private ?String?databaseName;

?? public ?SqlServerConnectionFactory()? {
???? super .setDriverName(dbDriver);
??}

?? /**
???*
???*? @param ?host?數據庫所在的主機名：如localhost
???*? @param ?port?SQL服務器運行的端口號，如果使用缺省值?1433，傳入一個負數即可
???*? @param ?databaseName?數據庫名稱
???*? @param ?userName?用戶名
???*? @param ?password?口令
??? */
?? public ?SqlServerConnectionFactory(String?host,
???????????????????????????????????? int ?port,
????????????????????????????????????String?databaseName,
????????????????????????????????????String?userName,
????????????????????????????????????String?password)? {
???? this .setHost(host);
???? this .setPort(port);
???? this .setDatabaseName(databaseName);
???? this .setUserName(userName);
???? this .setPassword(password);

????init();
??}

?? private ? void ?init()? {
???? super .setDriverName(dbDriver);
???? super .setUrl(jdbc:microsoft:sqlserver: // ?+?host.trim()?+?:?+
????????????????? new ?Integer(port).toString()? + ?;DatabaseName = ? +
?????????????????databaseName.trim());
???? // super.setUrl(jdbc:microsoft:sqlserver: // localhost:1433;DatabaseName=demo);
??}

?? public ? void ?setHost(String?host)? {
???? // 處理主機名稱
???? if ?(?(host? == ? null )? || ?(host.equals())? || ?(host.equals(.))? ||
????????(host.equals(local)))? {
??????host? = ?localhost;
????}

???? int ?index? = ?host.indexOf( // ,?0);
???? if ?(index? == ? 0 )? {
??????host? = ?host.substring( 2 );? // 去掉前面的 //
 ????}

????index? = ?host.indexOf( // ,?0);
???? if ?(index? >= ? 0 )? {
?????? try ? {
???????? throw ? new ?Exception(SQL?Server主機名參數錯誤！);
??????}
?????? catch ?(Exception?ex)? {
??????}
????}

???? this .host? = ?host;
??}

?? public ? void ?setPort( int ?port)? {
???? /**
?????*?缺省端口1433
????? */
???? if ?(port? < ? 0 )? {
??????port? = ? 1433 ;
????}

???? this .port? = ?port;
??}

?? public ? void ?setDatabaseName(String?databaseName)? {
???? this .databaseName? = ?databaseName;
??}

}

?

 package ?skydev.modules.data;

 import ?junit.framework. * ;
 import ?java.sql. * ;

 public ? class ?TestSqlServerConnectionFactory
???? extends ?TestCase? {
?? private ?SqlServerConnectionFactory?sqlServerConnectionFactory? = ? null ;

?? protected ? void ?setUp()? throws ?Exception? {
???? super .setUp();
???? /** @todo?verify?the?constructors */
????sqlServerConnectionFactory? = ? new ?SqlServerConnectionFactory();
??}

?? protected ? void ?tearDown()? throws ?Exception? {
????sqlServerConnectionFactory? = ? null ;
???? super .tearDown();
??}

?? public ? void ?testEmpty()? {
???? // ???assertTrue(objCon.connectDatabase());
????assertEquals(sqlServerConnectionFactory.getDriverName(),
?????????????????com.microsoft.jdbc.sqlserver.SQLServerDriver);
??}

?? public ? void ?testDB1()? {

????DbObject?DbO? = ? new ?DbObject( new ?SqlServerConnectionFactory(localhost,
???????? 1433 ,?demo,?sa,?));
????Connection?con? = ?DbO.getConnection();
????CallableStatement?pstmt? = ? null ;
????System.out.println(TestDB1());
???? /* ?try?{
???????pstmt?=?con.prepareCall({call?sp_getStudentById(?)});
???????pstmt.setInt(1,?1);
?????} */
???? try ? {
??????pstmt? = ?con.prepareCall( {call?sp_getStudentByName( ? )} );
??????pstmt.setString( 1 ,?Tom);
????}

???? catch ?(SQLException?ex1)? {
??????System.out.println(ex1);
????}
???? catch ?(Exception?ex)? {
??????System.out.println(ex);
????}

????ResultSet?results? = ? null ;
????ResultSetMetaData?resultMetaData? = ? null ;
???? try ? {
?????? // ?results?=?DbO.getResultSet(sp_getStudentByName);
??????results? = ?pstmt.executeQuery();
??????resultMetaData? = ?DbO.getResultSetMetaData(results);
?????? int ?cols? = ?resultMetaData.getColumnCount();
??????String?resultRow? = ?\n字段\n;
?????? for ?( int ?i? = ? 1 ;?i? <= ?cols;?i ++ )? {
????????resultRow? += ?resultMetaData.getColumnName(i)? + ?;;
??????}
??????System.out.println(resultRow);
?????? while ?(results.next())? {
????????resultRow? = ?\n內容\n;
???????? for ?( int ?i? = ? 1 ;?i? <= ?cols;?i ++ )? {
?????????? try ? {
????????????resultRow? += ?results.getString(i)? + ?;;
??????????}
?????????? catch ?(NullPointerException?e)? {
????????????System.out.println(e.getMessage());
??????????}
????????}
????????System.out.println(resultRow);
??????}
????}
???? catch ?(SQLException?ex)? {
????}
??}

?? public ? void ?testDB2()? {
????DbObject?DbO? = ? new ?DbObject( new ?SqlServerConnectionFactory(localhost,
???????? 1433 ,?demo,?sa,?));
???? // DbO.setConnectionFactory(new?SqlServerConnectionFactory());
???? // ?Connection?con?=?DbO.getConnection();
????System.out.println(TestDB2());
????ResultSet?results? = ? null ;
????ResultSetMetaData?resultMetaData? = ? null ;
???? try ? {
??????results? = ?DbO.getResultSet(select? * ?from??Persons;);
??????resultMetaData? = ?DbO.getResultSetMetaData();
?????? int ?cols? = ?resultMetaData.getColumnCount();
??????String?resultRow? = ?\n字段\n;
?????? for ?( int ?i? = ? 1 ;?i? <= ?cols;?i ++ )? {
????????resultRow? += ?resultMetaData.getColumnName(i)? + ?;;
??????}
??????System.out.println(resultRow);
?????? while ?(results.next())? {
????????resultRow? = ?\n內容\n;
???????? for ?( int ?i? = ? 1 ;?i? <= ?cols;?i ++ )? {
?????????? try ? {
????????????resultRow? += ?results.getString(i)? + ?;;
??????????}
?????????? catch ?(NullPointerException?e)? {
????????????System.out.println(e.getMessage());
??????????}
????????}
????????System.out.println(resultRow);
??????}
????}
???? catch ?(SQLException?ex)? {
????}
??}

?? public ? void ?testDB3()? {

????DbObject?DbO? = ? new ?DbObject( new ?SqlServerConnectionFactory(localhost,
???????? 1433 ,?demo,?sa,?));
????Connection?con? = ?DbO.getConnection();
????CallableStatement?pstmt? = ? null ;
????System.out.println(TestDB3());
???? try ? {
??????pstmt? = ?con.prepareCall( { ?= call?sp_insertStudent( ? , ? , ? )} );
??????pstmt.setString( 2 ,?zengqingsong);
??????pstmt.setInt( 3 ,? 22 );

??????pstmt.registerOutParameter( 4 ,?Types.INTEGER);
??????pstmt.registerOutParameter( 1 ,?Types.INTEGER);
?????? int ?ret? = ?pstmt.executeUpdate();? // 執行影響的行數

?????? int ?ret2? = ?pstmt.getInt( 1 );? // 返回參數（輸出參數）
?????? int ?id? = ?pstmt.getInt( 4 );? // 輸出參數
??????System.out.println(ret);
??????System.out.println(ret2);
??????System.out.println(id);
????}

???? catch ?(SQLException?ex1)? {
??????System.out.println(ex1);
????}
???? catch ?(Exception?ex)? {
??????System.out.println(ex);
????}

??}

}