Two ways to establish database connection using JDBC:
1. Use DriverManager in the code to get the database connection. This method is inefficient and its performance, reliability and stability gradually decrease as the number of users visits increases.
2. Use the method of configuring data sources to connect to the database. The essence of this method is to add a database connection pool based on the above method, which is highly efficient.
There is the following difference between connecting to a database using DriverManager in the code:
1) The data source connection pool is to connect to the database in the program. By querying a JNDI (Java Naming and Directory Interface) server, that is, calling the lookup() method of the Context interface to obtain the DataSource object, and then calling the getConnection() method of the DataSource object to establish a connection.
2) In order to reuse database connection objects, improve the response time to requests and the performance of the server, connection pooling technology is adopted. Connection pooling technology establishes multiple database connection objects in advance, and then saves the connection object to the connection pool. When the client request comes, a connection object is taken out from the pool to serve the customer. When the request is completed, the client program calls the close() method and puts the connection object back into the pool.
3) In the way of using DriverManager to obtain database connections in the code, the connection object obtained by the client program is a physical connection. Calling the close() method of the connection object will close the connection. Using the connection pooling technology, the connection object obtained by the client program is a handle to the physical connection in the connection pool. Calling the close() method of the connection object, the physical connection is not closed. The implementation of the data source only deletes the connection between the connection object in the client program and the connection object in the pool.
For testing convenience, you can create a USER table in the database (see mysql 5 as an example here):
CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(50) DEFAULT NULL, `password` varchar(50) DEFAULT NULL, `email` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`), );
Import the jar package of the driver of the database into the lib directory of tomcat (here, taking mysql5 as an example, the jar package used is: mysql-connector-java-5.0.8-bin.jar).
1. Use DriverManager in the code to get the database connection. This method is inefficient and its performance, reliability and stability gradually decrease as the number of users visits increases.
The Java code for oracle database connection is as follows:
import java.sql.Connection; import java.sql.DriverManager; /** * Get database connection*/ public class DBConnection { /** Oracle database connection URL*/ private final static String DB_URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; /** Oracle database connection driver*/ private final static String DB_DRIVER = "oracle.jdbc.driver.OracleDriver"; /** Database username*/ private final static String DB_USERNAME = "root"; /** Database password*/ private final static String DB_PASSWORD = "admin"; /** * Get database connection* @return */ public Connection getConnection(){ /** Declare the Connection connection object*/ Connection conn = null; try{ /** Use the Class.forName() method to automatically create an instance of this driver and automatically call DriverManager to register it */ Class.forName(DB_DRIVER); /** Get database connection through the DriverManager getConnection() method*/ conn = DriverManager.getConnection(DB_URL,DB_USERNAME,DB_PASSWORD); }catch(Exception ex){ ex.printStackTrace(); } return conn; } /** * Close the database connection* * @param connect */ public void closeConnection(Connection conn){ try{ if(conn!=null){ /** Determine that the current connection connection object is not closed, call the closing method*/ if(!conn.isClosed()){ conn.close(); } } } catch(Exception ex){ ex.printStackTrace(); } } }The JSP code for the mysql database connection is as follows:
<%@page import="java.sql.*, com.mysql.jdbc.Driver"%> <%@page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <html> <body> <% //com.mysql.jdbc.Driver Class.forName(Driver.class.getName()).newInstance(); String url = "jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF8"; String user = "root"; String password = "123"; Connection conn = DriverManager.getConnection(url, user, password); Statement stmt = conn.createStatement(); String sql = "select * from user"; ResultSet rs = stmt.executeQuery(sql); while(rs.next()) { out.print("<br />" + "====================" + "<br />"); out.print(rs.getLong("id") + " "); out.print(rs.getString("username") + " "); out.print(rs.getString("password") + " "); out.print(rs.getString("email") + " "); } %> </body> </html>2. Use the method of configuring data sources to connect to the database. The essence of this method is to add a database connection pool based on the above method, which is highly efficient.
1) The JSP code of the mysql database data source connection pool is as follows:
<%@page import="java.sql.*, javax.naming.*, javax.sql.DataSource"%> <%@page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <html> <body> <%Context initCtx = new InitialContext(); DataSource ds = (DataSource)initCtx.lookup("java:comp/env/jdbc/demoDB"); Connection conn = ds.getConnection(); Statement stmt = conn.createStatement(); String sql = "select * from user"; ResultSet rs = stmt.executeQuery(sql); while(rs.next()) { out.print("<br />" + "====================" + "<br />"); out.print(rs.getLong("id") + " "); out.print(rs.getString("username") + " "); out.print(rs.getString("password") + " "); out.print(rs.getString("email") + " "); } %> </body> </html>2) Add the following code to server.xml in the conf directory of tomcat:
<Context> <Resource name="jdbc/demoDB" auth="Container" type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/demo" username="root" password="123" maxActive="50" maxIdle="30" maxWait="10000" /> </Context>
3) Configure the following content under the root node of web.xml in the web project directory:
<resource-ref> <description>mysqlDB Connection</description> <res-ref-name>jdbc/demoDB</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref>
The connection pool configuration of the data source has been completed, but in order to improve the portability of the project, it is best to put the content of the second step above into the context.xml of the META-INF directory of the project (this file needs to be created by itself):
<?xml version="1.0" encoding="UTF-8"?> <Context> <Resource name="jdbc/demoDB" auth="Container" type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/demo" username="root" password="123" maxActive="50" maxIdle="30" maxWait="10000" /> </Context>
3. Database operation tool class when configuring the database connection pool of the data source
The code is as follows:
package db.utils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.text.DateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.naming.InitialContext; import javax.sql.DataSource; //import org.apache.log4j.Logger; /** * Database operation auxiliary class*/ public class DbUtils { //private static Logger logger = Logger.getLogger("DbUtils"); /** * This statement must be a SQL INSERT, UPDATE or DELETE statement* @param sql * @param paramList: parameter, corresponding to the placeholder in the SQL statement* @return * @throws Exception */ public int execute(String sql, List<Object> paramList) throws Exception { if(sql == null || sql.trim().equals("")) { //logger.info("parameter is valid!"); } Connection conn = null; PreparedStatement pstmt = null; int result = 0; try { conn = getConnection(); pstmt = DbUtils.getPreparedStatement(conn, sql); setPreparedStatementParam(pstmt, paramList); if(pstmt == null) { return -1; } result = pstmt.executeUpdate(); } catch (Exception e) { //logger.info(e.getMessage()); throw new Exception(e); } finally { closeStatement(pstmt); closeConn(conn); } return result; } /** * Convert the result set obtained by querying the database into a Map object* @param sql: query statement* @param paramList: parameter* @return */ public List<Map<String, Object>> getQueryList(String sql, List<Object> paramList) throws Exception { if(sql == null || sql.trim().equals("")) { //logger.info("parameter is valid!"); return null; } Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; List<Map<String, Object>> queryList = null; try { conn = getConnection(); pstmt = DbUtils.getPreparedStatement(conn, sql); setPreparedStatementParam(pstmt, paramList); if(pstmt == null) { return null; } rs = getResultSet(pstmt); queryList = getQueryList(rs); } catch (RuntimeException e) { //logger.info(e.getMessage()); System.out.println("parameter is valid!"); throw new Exception(e); } finally { closeResultSet(rs); closeStatement(pstmt); closeConn(conn); } return queryList; } private void setPreparedStatementParam(PreparedStatement pstmt, List<Object> paramList) throws Exception { if(pstmt == null || paramList == null || paramList.isEmpty()) { return; } DateFormat df = DateFormat.getDateTimeInstance(); for (int i = 0; i < paramList.size(); i++) { if(paramList.get(i) instanceof Integer) { int paramValue = ((Integer)paramList.get(i)).intValue(); pstmt.setInt(i+1, paramValue); } else if(paramList.get(i) instanceof Float) { float paramValue = ((Float)paramList.get(i)).floatValue(); pstmt.setFloat(i+1, paramValue); } else if(paramList.get(i) instanceof Double) { double paramValue = ((Double)paramList.get(i)).doubleValue(); pstmt.setDouble(i+1, paramValue); } else if(paramList.get(i) instanceof Date) { pstmt.setString(i+1, df.format((Date)paramList.get(i))); } else if(paramList.get(i) instanceof Long) { long paramValue = ((Long)paramList.get(i)).longValue(); pstmt.setLong(i+1, paramValue); } else if(paramList.get(i) instanceof Long) { long paramValue = ((Long)paramList.get(i)).longValue(); pstmt.setLong(i+1, paramValue); } else if(paramList.get(i) instanceof String) { pstmt.setString(i+1, (String)paramList.get(i)); } } return; } /** * Get database connection* @return * @throws Exception */ private Connection getConnection() throws Exception { InitialContext cxt = new InitialContext(); DataSource ds = (DataSource) cxt.lookup(jndiName); if ( ds == null ) { throw new Exception("Data source not found!"); } return ds.getConnection(); } private static PreparedStatement getPreparedStatement(Connection conn, String sql) throws Exception { if(conn == null || sql == null || sql.trim().equals("")) { return null; } PreparedStatement pstmt = conn.prepareStatement(sql.trim()); return pstmt; } /** * Get database query result set* @param pstmt * @return * @throws Exception */ private ResultSet getResultSet(PreparedStatement pstmt) throws Exception { if(pstmt == null) { return null; } ResultSet rs = pstmt.executeQuery(); return rs; } /** * @param rs * @return * @throws Exception */ private List<Map<String, Object>> getQueryList(ResultSet rs) throws Exception { if(rs == null) { return null; } ResultSetMetaData rsMetaData = rs.getMetaData(); int columnCount = rsMetaData.getColumnCount(); List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>(); while (rs.next()) { Map<String, Object> dataMap = new HashMap<String, Object>(); for (int i = 0; i < columnCount; i++) { dataMap.put(rsMetaData.getColumnName(i+1), rs.getObject(i+1)); } dataList.add(dataMap); } return dataList; } /** * Close the database connection* @param conn */ private void closeConn(Connection conn) { if(conn == null) { return; } try { conn.close(); } catch (SQLException e) { //logger.info(e.getMessage()); } } /** * Close* @param stmt */ private void closeStatement(Statement stmt) { if(stmt == null) { return; } try { stmt.close(); } catch (SQLException e) { //logger.info(e.getMessage()); } } /** * Close* @param rs */ private void closeResultSet(ResultSet rs) { if(rs == null) { return; } try { rs.close(); } catch (SQLException e) { //logger.info(e.getMessage()); } } private String jndiName = "java:/comp/env/jdbc/demoDB"; public void setJndiName(String jndiName) { this.jndiName = jndiName; } }Summary: Use the method of configuring data sources to connect to the database. This method is efficient and stable, so it is recommended to use it.
To view more Java syntax, you can follow: "Thinking in Java Chinese Manual", "JDK 1.7 Reference Manual Official English Version", "JDK 1.6 API java Chinese Reference Manual", "JDK 1.5 API java Chinese Reference Manual". I also hope that everyone will support Wulin.com more.