package skydev.modules.data;
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);
}
}
}
