Implementation of database application system with Java

Data | Database When we do information systems, we have to access the database, I recently took over a project, the project team decided to write in Java, I am responsible for the design and coding of the data layer, in order to improve the reuse of code and improve project development efficiency. We have developed a common database connection and the completion of basic operations of the class library, personally think that this class in the MIS system still has a certain value, so summed up, introduced to everyone.
Connect the factory, realize 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 (); Attempt to close 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.username,
This.password);
}
catch (SQLException e) {
Throw e;
}
}
finally {
return this.connection; Returns a newly established 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) {//No connection is set to create a connection
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 int0) {
}

public int getlogintimeout () {
return 0;
}
}

Implement a connection factory to connect to SQL Server, which is only implemented sqlserverconnectionfactory because our project uses SQLServer2000.

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 name where the host database resides: such as "localhost"
* @param port number that the port SQL Server runs, and if you use the default value of 1433, pass in a negative
* @param databaseName database name
* @param userName User Name
* @param password 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 () + ";D atabasename=" +
Databasename.trim ());
Super.seturl ("Jdbc:microsoft:sqlserver://localhost:1433;databasename=demo");
}

public void Sethost (String host) {
Processing host Names
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 the front "//"
}
index = Host.indexof ("//", 0);
if (index >= 0) {
try {
throw new Exception ("SQL Server host name 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;
}
}

Connect factory using "Sun.jdbc.odbc.JdbcOdbcDriver" to connect to a database
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);
}

/**
* Connect to the database server with the specified ODBC data source
* @param odbcname
*/
Public jdbcodbcconnectionfactory (String odbcname) {
Super.setdrivername (DriveName);
Setodbcname (Odbcname);
}

public void Setodbcname (String odbcname) {
This.odbcname = Odbcname;
This.seturl ("JDBC:ODBC:" + odbcname);
}
}
Data base Operation class, which connects the database using the connection factory.

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 implements the DataSource interface
}

/**
* Execute Query
* @param SQL SQL statement to execute
* @return Returns the result set of the query, the query fails to return NULL
*/
Public ResultSet Getresultset (String sql) {
try {
This.resultset = statement.executequery (sql); Keep internal pointers
}
catch (SQLException e) {
E.printstacktrace ();
This.resultset = null;
}
finally {
return this.resultset;
}
}

/**
* Get ResultSetMetaData data for external specified resltset
* @param resultSet to get the ResultSet
* @return 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;
}
}

/**
* Gets the last set or returned resultset resultmetadata data.
* For example, call the: Getresultset (SQL) method, and then call the Getresultsetmetadata method
* The corresponding ResultSetMetaData data can be obtained.
* @return
*/
Public ResultSetMetaData Getresultsetmetadata () {
Return This.getresultsetmetadata (This.resultset);
}

/**
* Execute Stored Procedure
* @param spname Stored procedure name
* @return
*/
Public ResultSet Execute (String spname) {
Execute a SQL query against this database
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;
}

/**
* Set up the database connection factory, you must call the method before all operations of this class.
* Set up the database connection factory.
* @param connectionfactory Database connection factory ConnectionFactory class objects 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 base class for specific projects

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 classes in the database tier in the project derive from the Databaseobject class, so that you need to set up data connections in one place, and no specific connection code involving database access is required elsewhere.

For example, the user class is specifically responsible for the permissions control of the Users group, so you can connect and access the database simply by simple code. The implementation here is not related to this article, only one or two modules for example.

public class User extends DBObject {
Public User () {
Subclasses can also override the way the base class is accessed, which is useful in stand-alone mode.
Super (new Sqlserverconnectionfactory ("localhost", 1433, "Theschool", "sa", ""));
Super ();//Call the base class's database access code.
}
/*
In order to improve customer maintenance in the information system, we generally use stored procedures to return and modify data, in the database layer code does not use

The SELECT statement retrieves the data directly and achieves the maximum flexibility and maintainability of the database layer code. Once you have found that you need to modify the

Code that only needs to be modified at the beginning of the village process that can be.
Here's how Java uses SQL Server storeprocedure.
Parameters for stored procedures use the "?" Instead, the following code has a certain representativeness, stored procedures have input parameters, output parameters.
The basic functions of a stored procedure are: to detect whether UserID and Encpassword are consistent with database storage, and return userid if

To return-1.
*/
Test that the encrypted password stored in the database is consistent with the encrypted password that the user passed in.
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;
}
else {
return false;
}
}
catch (SQLException ex) {
return false;
}
catch (Exception e) {
return false;
}
}
}

The above is only I in the study and the work of a little experience, write out the purpose so as to communicate with you, the wrong place I hope everyone put forward valuable advice, in order to make the function of the module to do a little more perfect.