Use Java to access the SQLServer database in the information system ZENG Qingsong@sohu.comqicq: 36951970 2004-2-3
When we do an information system, we must access the database. I have recently taken one project, and the project team decided to use Java to write, I negative
Responsible for the design and encoding of the data, in order to improve the reuse of the code and improve the development efficiency of the project. We have developed a universal database
Connect and complete the basic operation class library, individuals think this class is still a certain value when doing MIS systems, so summarizes it, introduce it to large
Home.
Connection factory implements the DataSource interface package skydev.modules.data; import java.sql *;. Import javax.sql.DataSource; import java.io.PrintWriter; public class ConnectionFactory implements DataSource {private String userName; private String password; private String drivername; private string url; private java.sql.connection connection;
/ ** * Create a new connection instance based on the set connection parameters * @return * / private connection getNewconnection () {try {this.connection.close (); // Try to close the connection} finally {this.connection = NULL; // Release connection Try {class.forname (this.drivername); // load driver / divIverManager.registerDriver (Driver); try {this.connection = drivermanager.getconnection (this.URL, this.username, this.password );} Catch (sqlexception e) {throw e;}} finally {return this.connection; // Return newly established connection}}}}}
Public string getUsername () {return username;}
Public void setusername (string username) {this.username = usrname;}
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) {}}} == null) {// create a connection is not provided connected getNewConnection ();} return connection;} public connection getConnection (String userName, String password) throws SQLException {this.setUserName (userName); this.setPassword (password); Return getConnection ();
Public PrintWriter getLogwriter () {Return NULL;}
Public void setLogwriter (PrintWriter PrintWriter) {}
Public void setlogintimeout (int INT) {}
Public int getlogintimeout () {return 0;}}
Realize the connection factories connected to SQL Server, here is only implemented because of our projects using SQLServer2000
SQLServerConnectionFactory. package skydev.modules.data; public final class SqlServerConnectionFactory extends ConnectionFactory {private final String dbDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver"; private String host; // host private int port; // port private String databaseName; / / SQL database name
Public SQLServerConnectionFactory () {super.SetDrivername (dbdriver);
/ ** * * @Param Host Database The host name of the database: If the "localhost" * @Param port number running, if the default value is 1433, it will be introduced into a negative number * @PARAM DATABASENAME database name * @ param userName * @param password username 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: 1433; DatabaseName = DEMO ");
Public void setHost (string host) {// Host host name IF ((host == null) || (Host.equals (")) || (Host.equals (". ")) || (Host.equals) ("local"))) {host = "localhost";} int index = host.indexof ("//", 0); if (index == 0) {Host = host.substring (2); // remove Front "//"} index = host.indexof ("//", 0); if (index> = 0) {Try {throw new exception ("SQL Server hostname parameter error!");} Catch (Exception EX) {}} this.host = host;}
Public void setPort (int port) {/ ** * Default port 1433 * / if (port <0) {port = 1433;} this.port = port;
public void setDatabaseName (String databaseName) {this.databaseName = databaseName;}} using "sun.jdbc.odbc.JdbcOdbcDriver" connection factory database package skydev.modules.data; public class JdbcOdbcConnectionFactory extends ConnectionFactory {private final static String driveName = "sun.jdbc.odbc.jdbcodbcdriver"; private string odbcname;
Public JDBCODBCCONNECTIONFAACTORY () {Super.SetDrivername (drivename);
/ ** * Connect the database server * @Param odbcname * / public jdbcodbcconnectionFactory (SUPER.SETDRIVERNAME (DRIVENAME); setoDbcname (odbcname);
Public void setoDbcname (string odbcname) {this.odbcname = odbcname; this.SetURL ("JDBC: ODBC:" ODBCNAME);}}
The data basic operation class uses the connection factory to connect the database. Package skydev.modules.data; import java.sql. *; import java.sql.preparedStatement; import javax.sql.dataserce;
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; private javax.sql.DataSource dataSource; // = NEW statement ();
Public DatabaseObject () {Datasource = NULL; connection = null;
Public DatabaseObject (ConnectionFactory ConnectionFactory) {this.setConnectionFactory; this.datasource = connectionsFactory; // connectionfactory implements DataSource interface}
/ ** * Execute Query * @Param SQL To execute the SQL statement * @return returns the result set of queries, the query fails returns null * / public resultset getResultSet (String SQL) {Try {THIS.ResultSet = Statement.executeQuery (SQL) ; // Reserved internal pointer} catch (sqlexception e) {E.printStackTrace (); this.resultset = null;} finally}}} / ** * Get the outside to specify the ResultSetAdata data * @Param Resultset to obtain the ResultSet * @return else return null * / public ResultSetMetaData getResultSetMetaData (ResultSet resultSet) {ResultSetMetaData resultSetMetaData = null; try {resultSetMetaData = resultSet.getMetaData ();} catch (SQLException e) {e.printStackTrace (); resultSetMetaData = null;} finally {return results;}
/ ** * Get the RESULTSETADATA data for the last set or returned, * ratio called: getResultSet (SQL) method, then call the getResultSetMetadata method * You can get the corresponding ResultSetMetadata data. * @Return * / public resultsetMetadata getResultSetMetadata () {return this.getResultSetMetadata (this.Resultset);}
/ ** * * @param spName execute a stored procedure stored procedure name * @return * / public ResultSet Execute (String spName) {// this database to perform a SQL query ResultSet resultSet = null; try {// PreparedStatement stmt = (PreparedStatement ); resultset = statement.executeQuery (spname);} catch (exception e) {system.out.println ("Execute Error" E.getMessage ());} Return ResultSet;
/ ** * Set the database connection factory before all the operations of this class must call this method, * Set the database connection plant. * @Param ConnectionFactory Database Connection Factory ConnectionFactory class object and * derived class object. * / 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 () {returnitment;} public javax.sql.datasource getDataSource () {return DataSource;}}
Database access base class package skydev.modules.data specific items; 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);}}
Database Access classes in the database layer in the project are derived from the DatabaseObject class, so they only need to set data connection in one place.
The other places do not need specific connection code for database access. Such as: User class is responsible for the permission control of the USERS group, and only simple code can be connected and accessed. Here is specific
Nowhere is not related to this article, only one or two modules do an example. Public Class User Extends Dbobject {public user () {// Subclass can also overwrite the access method of the base class, useful when the stand-alone is adjusted. // Super (New SqlServerConnectionFactory ("LocalHost", 1433, "THESCHOOL", "SA", "")); super (); // invoke the database access code of the base class. } / * In order to improve customer maintenance, we generally use the stored procedure to return and modify the data in the database layer code.
SELECT statement directly retrieves data to achieve the greatest flexibility and maintainability of database layer code. Once you find that you need to modify the database
The code can only be modified to modify the year of the village. Here is the method of Java using SQLServer StoreProcedure. The parameter of the stored procedure uses "?" Instead, the following code has a certain representation, the stored procedure has input parameters, output parameters. The basic function of the stored procedure is: Detect whether UserID and Encpassword are consistent with the database store, return to userid if it is inconsistent, and -1. * /// Is the encrypted password stored in the database and the encrypted password incoming encryption. public boolean testPassword (int userID, byte [] encPassword) {Connection con = this.getConnection (); CallableStatement cs = null; try {cs = con.prepareCall ( "??? {= call sp_Accounts_TestPassword (,)}"); CS.setint (2, userid); cs.setbytes (3, encpassword); cs.registeroutparameter (1, types.integer); @ Userid cs.execute (); if (cs.getint (1) == 1 ) {// password qualified Return true;}}} catch (sqlexception ex) {return false;} catch (exception e) {returnaf false;}}} is just a little in learning and work The purpose of writing makes it to communicate with you, I hope that everyone will propose treasure.
Expensive comments in order to make the function of the module more perfect.
Welcome everyone to make valuable comments.
