1. Use JDBC to directly operate the database in the business layer – the simplest and most direct operation

1) Database URL,username,password write dead in the code

     Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
     String url="jdbc:oracle:thin:@localhost:1521:orcl";
     String user="scott";
     String password="tiger";
     Connection conn= DriverManager.getConnection(url,user,password);  
     Statement stmt=conn.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
     String sql="select * from test";
     ResultSet rs=stmt.executeQuery(sql);
Copy the code

2) Use Facade and Command mode and DBUtil class to encapsulate JDBC operations; The database URL,username, and password can be placed in a configuration file (XML, Properties, INI, etc.). This method is widely used in small programs.

DAO(Data Accessor Object) mode – loosely coupled DAO = Data + Accessor + Domain Object

For example, User class – Domain Object (Javabean)

The UserDAO class – Accessor, which provides methods getUser(int ID), save(User User), contains JDBC operations. These two classes are used in the business logic to complete data operations.

Using the Factory pattern facilitates migration between different database connections.

3. Database resource management mode

  • 3.1 Database connection Pool technology

Resource reuse, avoid frequent creation and release of connections, resulting in a large amount of performance overhead. Faster system response;

By implementing JDBC’s partial resource object interfaces (Connection, Statement, ResultSet), you can use the Decorator design pattern to generate three logical resource objects: PooledConnection, PooledStatement, and PooledResultSet.

A simple database connection pool implementation:

public class ConnectionPool { private static Vector pools; private final int POOL_MAXSIZE = 25; /** * get database connection * if there is a connection available in the current pool, return the last one in the pool; */ public synchronized Connection getConnection() {Connection conn = null; if (pools == null) { pools = new Vector(); } // If (pools.isEmpty()) {conn = createConnection(); } else { int last_idx = pools.size() - 1; conn = (Connection) pools.get(last_idx); pools.remove(last_idx); } return conn; } /** * Put used database connections back into the pool * If the number of connections in the pool exceeds the threshold, close the connection. */ public synchronized void releaseConnection(Connection conn) {if (pools.size() >= POOL_MAXSIZE) try { conn.close(); } catch (SQLException e) {// TODO automatically generate catch block e.printStackTrace(); } else pools.add(conn); } public static Connection createConnection() { Connection conn = null; try { Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); String url = "jdbc:oracle:thin:@localhost:1521:orcl"; String user = "scott"; String password = "tiger"; conn = DriverManager.getConnection(url, user, password); } catch (InstantiationException e) {// TODO automatically generate catch block e.printStackTrace(); } catch (IllegalAccessException e) {// TODO automatically generate catch block e.printStackTrace(); } catch (ClassNotFoundException e) {// TODO automatically generates a catch block e.printStackTrace(); } catch (SQLException e) {// TODO automatically generate catch block e.printStackTrace(); }// Return conn; // Return conn; }}Copy the code

Note: With the Connection obtained by getConnection(), programmers are used to calling conn.close() to close the database Connection, so the above database Connection mechanism is useless. How is releaseConnection() called when the conn.close() method method is called? That’s the key. Here, we use Proxy mode and the Java reflection mechanism.

public synchronized Connection getConnection() { Connection conn = null; if (pools == null) { pools = new Vector(); } if (pools.isEmpty()) { conn = createConnection(); } else { int last_idx = pools.size() - 1; conn = (Connection) pools.get(last_idx); pools.remove(last_idx); } ConnectionHandler handler=new ConnectionHandler(this); return handler.bind(con); } public class ConnectionHandler implements InvocationHandler { private Connection conn; private ConnectionPool pool; public ConnectionHandler(ConnectionPool pool){ this.pool=pool; } // Add a Java development exchange sample: Connection * @param conn * @return */ public Connection bind(Connection conn){ this.conn=conn; Connection proxyConn=(Connection)Proxy.newProxyInstance( conn.getClass().getClassLoader(), conn.getClass().getInterfaces(),this); return proxyConn; } / * (not Javadoc) * @ see Java lang. Reflect. InvocationHandler# invoke (java.lang.object, Java. Lang. Reflect the Method, Java.lang. Object[]) */// Add Java development exchange Public Object Invoke (Object Proxy, Method Method, Object[] args) throws Throwable {// Object obj=null; if("close".equals(method.getName())){ this.pool.releaseConnection(this.conn); } else{ obj=method.invoke(this.conn, args); } return obj; }}Copy the code

In a real project, you do not need to design the database connection pooling mechanism from scratch. Mature open source projects such as C3P0, DBCP,Proxool, etc provide good implementations. Apache DBCP is recommended. The basic example is as follows:

DataSource ds = null; Try {initCtx = new InitialContext(); Context envCtx = (Context) initCtx.lookup("java:comp/env"); ds = (DataSource)envCtx.lookup("jdbc/myoracle"); if(ds! =null){ out.println("Connection is OK!" ); Connection cn=ds.getConnection(); if(cn! =null){ out.println("cn is Ok!" ); Statement stmt = cn.createStatement(); ResultSet rst = stmt.executeQuery("select * from BOOK"); out.println("<p>rst is Ok!" + rst.next()); while(rst.next()){ out.println("<P>BOOK_CODE:" + rst.getString(1)); } cn.close(); }else{ out.println("rst Fail!" ); } } else out.println("Fail!" ); }catch(Exception ne){ out.println(ne); }Copy the code
  • 3.2 the Statement Pool

Normal precompiled code:

String strSQL= "select name from items where id=?" ; PreparedStatement ps=conn.prepareStatement(strSQL); Ps. SetString (1, "2"); ResultSet rs=ps.executeQuery(); // Join the Java Development Exchange jun sample: 756584822 to blow water chatCopy the code

However, a PreparedStatement is associated with a particular Connection, and once the Connection is closed, the associated PreparedStatement is also closed. To create a PreparedStatement buffer pool, you can use an SQL statement in the Invoke method to determine if there are still instances available in the pool.

  • Persistence layer design and O/R Mapping technology

Hibernate was taken over by Jboss in 2003. By mapping Java POJO objects to the table of the database, Hibernate adopts XML/Javareflection technology. 3.0 provides support for stored procedures and handwritten SQL, and the HQL language itself. Files required for development:

Hibernate configuration file: hibernate-cfg. XML or Hibernate-. properties Hibernate mapping file: a.hobm. XML POJO source file: A. ava Export Table Relationships between tables: a. From Java Objects to HBM files: xDoclet B. From HBM files to Java Objects: Hibernate Extension C. D. From HBM file to database: SchemaExport // Add Java development exchangeCopy the code
  • 2) Iatis: suitable for the transformation of legacy systems and the reuse of existing databases, with strong flexibility 3) Apache OJB: the advantage lies in the full support of standards 4) EJB: Suitable for clustered servers, the performance is not as bad as some people complain.

Set a Properties object, so as to obtain a PersistenceManagerFactory JDO (equivalent to a DataSource in the JDBC connection pool), In turn, you get a PersistenceManager object (equivalent to a Connection object in JDBC) that you can then use to add, update, delete, and query objects. JDOQL is the query language of JDO; It is a bit like SQL, but with Java syntax.

  • Struts+Spring+Hibernate implementation scheme based on open source framework

Example: This is a 3-tier Web application that calls the DAO class back and forth through an Action to the business proxy. The following flowchart shows how MyUsers works. The numbers indicate the sequence of processes, from the Web layer (UserAction) to the middle layer (UserManager), to the data layer (UserDAO), and back. Spring is AOP, and UserManager and UserDAO are interfaces.

  • 1) Web layer (UserAction) : Call the interface method of the middle layer and inject UserManager as a property.

Using the popular Struts framework, although many people despise it, but this technology is widely used in the industry, can meet the basic function, can reduce the cost of training and learning.

  • 2) Middle layer (UserManager) : Inject UserDAO as a property, its implementation is mainly to call some methods of data layer interface; It is in transaction control. Spring framework implementation, IOC and AOP is synonymous with it, functional, a very good architecture.

  • 3) Data layer (UserDAO) : The implementation class inherits from the HibernateDaoSupport class, where you can call some methods of getHibernateTemplate() to perform specific data operations.

Using Hibernate for O/R mapping, we can see from various indications that Hibernate is the beta version of EJB3.0.