When we do the information system, we must visit the database. I have recently taken one project. The project team decided to use Java to write, I am responsible for the design and encoding of the data layer, in order to improve the reuse of the code and improve the development efficiency of the code. We develop a universal database connection and complete basic operation class libraries. Personally think that this class has a certain value when doing MIS systems, so summarizes it, introduce it to everyone.
Connecting the factory to implement 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 * @ returnnection () {THIS.CONNECTION.CLOSE (); // attempt to close the connection} Finally {this.connection = null; // Release connection try {class.forname (this.drivername); // load driver //drivermanager.registerdriver (Driver );Try {this.connection = drivermanager.getConnection (this.URL , this.pansword);} catch (sqlexception e) {throw e;}} finally {return this.connection; // Returns newly established connection}}} public string getUsername () {Return Username;} 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 (ST ring 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) {}}}}}} (connection == null) {// No settings Create a connection getNewConnection ();} return connection;} public Connection getConnection (String userName, String password) throwsSQLException {this.setUserName (userName); this.setPassword (password); return getConnection ();} public PrintWriter getLogWriter () {return null;
} Public void setLogWriter (PrintWriter printWriter) {} public void setLoginTimeout (int int0) {} public int getLoginTimeout () {return 0;}} realize connection SQLServer connection factory, where because our project uses SQLServer2000 thus achieved only 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 location: If "localhost" * @Param port SQL server running port number, if you use default value 1433, passing a negative number to * @param databaseName database name * @param userName the user name * @param password the password * / public SqlServerConnectionFactory (String host, int port, String databaseName, String userName, String password) {this.setHost (host); this.Setport (port); this.setDatabaseName (THISSETUSETUSERNAME (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");} PUBL IC void setOSt (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" connections to the database connection factory package skydev.modules.data; public class JdbcOdbcConnectionFactory extends ConnectionFactory {private final static String driveName = "sun.jdbc.odbc.JdbcOdbcDriver"; private String odbcName; public JdbcOdbcConnectionFactory () {super.setDriverName (driveName);} / *** Odbc specified data source connection database server * @param odbcName * / public JdbcOdbcConnectionFactory ( String odbcName) {super.setDriverName (driveName); setOdbcName (odbcName);} public void setOdbcName (String odbcName) {this.odbcName = odbcName; this.setUrl ( "jdbc: odbc:" odbcName);}}
package skydev.modules.data; import java.sql *;. import java.sql.PreparedStatement; import javax.sql.DataSource; 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 (connectionFactory); this.dataSource = connectionFactory; // ConnectionFactory DataSource interface implements} / * Sql *** execute the query @param sql statement to be executed * @return return a result set of query, the query else return null * / public ResultSet getResultSet (String sql) {try {this.resultSet = statement.executeQuery (sql); // internal pointer reserved} catch (SQLException e) {e.printStackTrace (); this.resultSet = null; } finally {return this.ResultSet;}} / *** Get external specified RESLTSET's ResultSetMetadata data * @Param ResultSet ResultSet * @RE turn failed to return null * / public ResultSetMetaData getResultSetMetaData (ResultSet resultSet) {ResultSetMetaData resultSetMetaData = null; try {resultSetMetaData = resultSet.getMetaData ();} catch (SQLException e) {e.printStackTrace (); resultSetMetaData = null;} finally {return ResultSetMetadata;}} / *** Get the results of the RESULTSETADATA data for the last set or returned, * Compared to the partial call: 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 stored procedure stored procedure names * @ return * / public ResultSet Execute (String spName) {/ / SQL query this database a 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;} / *** Setting the database connection factory, you must call the method before all the operations of this class, * Set the database connection factory. * @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 () {return statement;} public javax.sql.DataSource getDataSource () {return dataSource;}} database access project-specific base class
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);} The database access class in the database layer in the project is derived from the DatabaseObject class, which only needs to set data in one place. Connect, there is no need to involve specific connection code involving 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 the specific implementation of this article, only one or two modules are used.
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. Do not use the SELECT statement directly to retrieve data, do the maximum flexibility and maintainability of database layer code. . Once you find that you need to modify the code in the database, you only need 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); @Useridcs.execute();if (cs.getint (1) == 1) {// password qualified Return true;}}} catch (sqlexception ex) {return false;} catch (Exception E) {Return False;}}} is just a little experience in learning and work, The purpose of writing makes it to communicate with you, and I hope that everyone will make valuable comments in order to make the function of the module more perfect.
