Develop a reusable database connection framework

When developing the application of the database, although JDBC is provided to us many conveniences, it still repeats some work when we develop different applications, such as writing a database connection, although not much, but constantly Repeating is really wasting time, and also adds complexity of debugging; there is a little, we should try to use the configuration data source, the author used mysql, when used in Chinese Applied, Mysql data source has no way to solve Chinese Question, on the other hand, do not want to create a connection every time. Based on the two small obstacles encountered above, I have written a small frame to implement the database usage method, and can create a data connection pool. The implementation of these work is no need to program again. In web applications, we only need to add a DBCONFIG.PROPERTIES for a web-INF folder, simply set a few parameters, and add a listner element in the web.xml file. All JDBC supported database types can be set to the database through these two files. With this foundation, developers can only consider business programming. For example, what kind of query and data operations are to be implemented, and developers only need to define methods on these services. This improves the development efficiency. Of course, if you use the ORM frame in some large applications, this framework will not be used. This small framework is only applicable to the application of small and medium "working in local languages ​​of relational databases." Steps to use: 1. Configure the setting example of dbconfig.properties dbconfig.properties as follows:

URL = JDBC: mysql: // localhost: 3306 / Article? Useunicode = true & characterencoding = GB2312User = dbuserPassword = 227711drivername = com.mysql.jdbc.drivermaxConnections = 10

The URL parameter specifies the URL of the desired database connection. User parameters are the username Password parameter connected to the database is the connection user's password drivername parameter is the drive class of the database type used. MaxConnections can set the maximum number of connections. Second, configure web.xml:

In Web.xml, you can: bbmyth.util.Database.DataSourceProviderServlet Do not change < Listener> Everything in it. Third, use the API of this application package to write business programs

Make sure Bbmyth.util.Database This application package is in your classpath, or directly into the package IMPORT.

Finally, define your own database operation bean. Note that this bean wants to inherit the bbmyth.util.database.dbManager class, which provides four database operation variables in this DBManager class: Connection Con: is a connection class, the framework has provided ready-made connections, developers can be in the program The statement is connected to the database: con = getConnection (); statement smt: is the variable of the query statement class, can be used directly; PreparedState psmt: is the variable of the precompiled query statement class, can be used directly; ResultSet RS: is the result set Variables can be used directly. After each connection is used, no matter whether it is compiled or the precompiled statement and whether there is a returning result set can close them directly to close them, it is not recommended to use their own close () method. Fourth, apply the USEBean tab into the JSP to apply the service JavaBean, then set one of its parameters with the setProperty tag. Such as: "/> ArticleDB is the ID of your own useBean definition. Other don't change. 5. Commissioning and running! In this way, you can automatically create the required connection pool when you give the parameters. Release the connection when the application exits. Use MySQL in the configuration data source because the parameters of Chinese coding are always encoded, but here, can be added directly to the URL. There is only the convenience of the database connection and solve the problem of Chinese connections. Working principle: Working principle is very simple: a class implements the interface of ServletContextListener, which can realize event monitoring of web applications. I read the parameters of the configuration file when I am started, and then connect the database to a bean according to the parameter, and store the BEAN as a variable in the JSP Application range, the entire application It can be used. Next, writing business methods is to use the API inside the package. When using this connection pool instance in the JSP page, use the usebaen and setproperty tags to use the connection pool variables in ServletContext as their own business bean. This allows you to work with the ready-made connection pool. In addition, inherit the DBManager class when the user writes the business bean. This class has defined some of the necessary variables and implemented connections. Users can directly start writing from business logic methods. Just know how to start, how to end. Below is the source code for the application package: a total of three class files, DataSourceProviderServlet .java generates a connection to the CONNECTIONPROVIDER.JAVA when the application is started. DBManager is convenient for programming. / ************************************************** ***************** / 1, DataSourceProviderServlet .javaPackage bbmyth.util.Database;

import javax.servlet.http.HttpServlet; import javax.servlet.ServletContextListener; import javax.servlet.ServletContextEvent; import javax.servlet.ServletContext; import java.util *;. import java.io *;. import javax.sql *. Import java.sql. *;

Public Class DatasourceProviderServlet Implements ServletContextListener {Public Void ContextDestroyed (ServletContextEvent SCE) {/ * This method is called by the system when the application exits, at this time we turn all the connections! * / ((ConnectionProvider) (sce.getServletContext () getAttribute ( "CONNECTOR")).) Destroy ();.. Sce.getServletContext () removeAttribute ( "CONNECTOR");} public void contextInitialized (ServletContextEvent sce) {/ * This method is called at the application, then we can create a connection and save it in a container, stored in the app, and then handed over a JavaBean process. * / Properties ps = new Properties (); Vector connections = new Vector (); String url = null; String user = null; String passWord = null; int maxConnections; String DriverName = null; ServletContext context = sce.getServletContext (); Try {INPUTSTREAM INPUT = Context .getResourceSstream ("/ Web-INF / DBCONFIG.PROPERTIES"); ps.load (input); input.close (); URL = (String) ps.get ("URL"); user = Ps.get ("user"); password = (string) ps.get ("password"); drivername = (string) ps.get ("drivername"); maxconnections = integer.parseint ((String) Ps.get ("MaxConnections")). Trim (), 10); for (int i = 0; i

import java.util *;. import java.sql *;. import javax.sql *;. public class ConnectionProvider {private int maxConnections; private Vector freeConnections; private Vector nowConnections; public ConnectionProvider (Vector connections) {freeConnections = new Vector () ; nowConnections = new Vector (); freeConnections = connections; maxConnections = freeConnections.size ();} public Connection getConnection () {Connection temp = (Connection) freeConnections.firstElement (); freeConnections.remove (temp); nowConnections.add ( temp); return temp;} public void closeConnection (Connection con) {/ * do not use the method close () provid by Connection to close the connection beacuase if you do that, the connection will not return to the pool *!! / nowConnections.remove (con); freeConnections.add (con);} public void destroy () // can the container call this method {freeConnections.removeAllElements ();? nowConnections.removeAllElements ();}} three, DBManager.javapackage bbmyth.util.database;

Import java.sql. *; import javax.sql. *; import java.util. *;

public class DBManager {protected ConnectionProvider provider = null; protected Connection con = null; protected Statement smt = null; protected PreparedStatement psmt = null; protected ResultSet rs = null; public void setProvider (Object provider) {this.provider = (ConnectionProvider) provider ;} public Connection getConnection () throws SQLException // get acess to database {if (provider == null) throw new SQLException ( "missing the property 'provider' !!"); return provider.getConnection ();} public void closeAll () {Try {if (rs! = Null) rs.close (); if (SMT! = Null) smt.close (); if (psmt! = Null) psmt.close (); if (con! = Null );} catch (Exception E) {E.PrintStackTrace ();}}} / ************************************ ********************************************************* / The following is an example of an application: the application is an application of a document and an application for viewing. There are only three ways in business logic Javabean and these methods are to add and prepare according to your own business needs. There is another Javabean that represents the article.

A, ArticleDB.java package article; import java.sql *;. Import java.util *;. Import javax.naming *;. Import javax.sql *;. Import bbmyth.util.dataBase *;. Public class ArticleDB extends DBManager {public Article getArticleByTitle (String title) throws SQLException {Article article = new Article (); try {con = getConnection (); String sql = "select *" "from articles where title = '" title "'"; smt = con.createstatement (); rs = smt.executeQuery (SQL); rs.next (); article.kind = rs.getstring (1); article.author = rs.getstring (2); article.title = RS. GetString (3); article.date = rs.getstring (4); article.body = rs.getstring (5); article.checknum = rs.Getint (6); closeall ();} catch (e) {e) {e) {e .printStackTrace ();} return article;} public ArrayList executeQuery (String sql) throws SQLException {ArrayList list = new ArrayList (); try {con = this.getConnection (); smt = con.createStatement (); rs = smt. ExecuteQuery (SQL); while (rs.next ()) {article article = new articles (); article. Kind = rs.getstring (1); article.author = rs.getstring (2); article.title = rs.getstring (3); article.date = rs.getstring (4); article.body = rs.getstring ( 5); article.checknum = rs.getinT (6); list.add (article);} closeall ();} catch (sqlexception e) {E.PrintStackTrace ();} returnpidate;} public void executeUpdate (String SQL Throws Sqlexception {Try {Con = this.getConnection (); SMT = con.createstatement (); smt.executeUpdate (SMT.executeUpdate (SMT.ExecuteUpdate (SQL); closeall ();} catch (exception e) {E.PrintStackTrace ();}}} Article.javaPackage Article; import java.sql. *; Import java.util. *;

import javax.naming *;. import javax.sql *;. public class Article {String kind; String author; String title; String date; String body; int checknum; public Article () {} public Article (String kind, String author , String Title, String Date, String Body, Int Checknum) {this.kind = Kind; this.Author = Author; this.title = Title; this.date = Date; this.body = body; this.checknum = checknum; } / ********* Read field value ******** / public int getChecknum () {returnim;} public string getkind ()} public string;} public string; public String GetAuthor () {returni;} public string gettitle () {return this.title;} public string getDate () {return this.date;} public string getBody;} / *** ******* Set field value ********* / public void setkind (String Kind) {this.kind = kind;} public void setauthor (string author) {this.author = author;} Public void settitle (String Tit) Le) {this.title = Title;} public void setdate (string date) {this.date = date;} {this.body = body;} public void setChecknum (int checknum) {THIS. Checknum = checknum;}} Attached: "/> This way you can use your business bean from the JSP. Summary: This is a very simple appliance tool for small or small applications. Can reduce the processor's work in the database connection. Destroy business programs concentrate.