[Servlet & amp; JSP] connecting to the database using JDBC

[Servlet & JSP] connecting to the database using JDBC

JDBC (Java DataBase Connectivity) is a solution for running SQL. developers use JDBC standard interfaces, and DataBase vendors implement interfaces, in this way, developers do not need to be exposed to the differences between underlying database drivers.

Introduction to JDBC

When implementing the JDBC driver, the vendor can divide the driver into four types as follows:

JDBC-ODBC Bridge Driver.
Convert JDBC call to ODBC call Native API Driver
Converts a JDBC call to an API call in the native Linked Library. Feature: It depends on the platform and is the fastest driver among the four types. JDBC-Net Driver
To convert a JDBC call to a specific network protocol call, the objective is to perform protocol operations with a specific intermediate server or component of the remote database, and the middleware or component can then perform operations with the database. Features: the highest elasticity. Native Protocol Driver
The main function is to convert JDBC calls to specific network protocols. Therefore, the driver can be implemented using Java technology, and your drivers can be cross-platform, it also has good performance. This type of driver is usually used when the flexibility of the 3rd driver types is not required, the most common driver type for this type.

Write a simple JavaBean to test whether the database can be connected:

package club.chuxing;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.logging.Level;import java.util.logging.Logger;public class DbBean {    private String url;    private String username;    private String password;    public DbBean(){        try {            Class.forName("com.mysql.jdbc.Driver");        } catch (ClassNotFoundException e) {            Logger.getLogger(DbBean.class.getName()).log(Level.SEVERE, null, e);        }    }    public boolean isConnectedOk() {        boolean ok = false;        Connection conn = null;        try {            conn = DriverManager.getConnection(url, username, password);            if (!conn.isClosed()) {                ok = true;            }        } catch (SQLException e) {            Logger.getLogger(DbBean.class.getName()).log(Level.SEVERE, null, e);        } finally {            if (conn != null) {                try {                    conn.close();                } catch (SQLException e) {                    Logger.getLogger(DbBean.class.getName())                        .log(Level.SEVERE, null, e);                }            }        }        return ok;    }    public void setPassword(String password) {        this.password = password;    }    public void setUrl(String url) {        this.url = url;    }    public void setName(String username) {        this.username = username;    }}

Then you can call the isConnectedOK () method to check whether the connection is successful. For example, you can write a simple JSP page with the following code:

<% @ Page language = "java" contentType = "text/html; charset = UTF-8 "pageEncoding =" UTF-8 "%> <% @ taglib prefix =" c "uri =" http://java.sun.com/jsp/jstl/core "%>
  
   
    
     
      
              
       
       
         Connection successful
       <% --
       
         Connection successful
       -- %>
       
         Connection Failed
        
      
     
    
   
  

Connect using DataSource

In the Java EE environment, connection and other behavior specifications related to the database source will be obtained injavax.sql.DataSourceInterface, the object implementing the interface is responsible for how to obtain the Connection.

In order for the application to obtain system-related resource objects, it has nothing to do with the actual system configuration, physical machine location, and environment architecture, in a Java application, you can use JNDI (Java Naming Directory Interface) to obtain the required resource objects. You can obtain the DataSource instance as follows:

try {    Context initContext = new InitialContext();    Context envContext = (Context)initContext.lookup("java:/comp/env");    dataSource = (DataSource)envContext.lookup("jdbc/test");} catch(NamingException ex) {    //other process}

The environment data will be collected during the Context object creation process, and then the DataSource instance will be searched for and returned to the JNDI Server Based on the JNDI name jdbc/demo. In this program segment, you do not know the actual resource configuration, physical machine location, environment architecture, and other information, and the application will not be dependent on these information.

The previous DbBean class can be written as follows:

package club.chuxing;import java.sql.Connection;import java.sql.SQLException;import java.util.logging.Level;import java.util.logging.Logger;import javax.naming.Context;import javax.naming.InitialContext;import javax.naming.NamingException;import javax.sql.DataSource;public class DatabaseBean {    private DataSource dataSource;    public DatabaseBean(){        try {            Context initContext = new InitialContext();            Context envContext = (Context)initContext.lookup("java:/comp/env");            dataSource = (DataSource)envContext.lookup("jdbc/test");        } catch(NamingException ex) {            Logger.getLogger(DatabaseBean.class.getName())                .log(Level.SEVERE, null, ex);        }    }    public boolean isConnectedOK(){        boolean ok = false;        Connection conn = null;        try {            conn = dataSource.getConnection();            if (!conn.isClosed()) {                ok = true;            }        } catch(SQLException ex) {            Logger.getLogger(DatabaseBean.class.getName())                .log(Level.SEVERE, null, ex);        } finally {            if (conn != null) {                try {                    conn.close();                } catch(SQLException ex) {                    Logger.getLogger(DatabaseBean.class.getName())                        .log(Level.SEVERE, null, ex);                }            }        }        return ok;    }}

The above Code does not contain drivers, database usernames, passwords, and other information. These are all set by database administrators or server administrators. The only thing we need to know is the JNDI name jdbc/test, and tell the web Container, that is, in the web. set in xml.

Web. xml:

    
         
    
     jdbc/demo
          
    
     javax.sql.DataSource
          
    
     Container
          
    
     Shareable
      
   
  

The purpose of setting in web. xml is to enable the web container to provide the environment information required for the JNDI search. In this way, you do not need to set a lot of parameters when creating the Context object. Then you can write a simple JSP to use DatabaseBean.

<% @ Page language = "java" contentType = "text/html; charset = UTF-8 "pageEncoding =" UTF-8 "%> <% @ taglib prefix =" c "uri =" http://java.sun.com/jsp/jstl/core "%>
  
   
           
               
    
     
Connection successful
    <% --
    
     
Connection successful
    -- %>
    
     
Connection Failed
            
   
  

Java developers have completed their work. Now, if you are a server administrator, you are responsible for setting resources related to JNDI, but the setting method is not a standard part, but varies with the application server. Assuming that the application is deployed on Tomcat6, You can require the web application to include a context. xml file in the META-INF folder when it is encapsulated as a war file. For example:

The name attribute is used to set the JNDI name, the username and password are used to set the database user name and password, the driverClassName is used to set the driver class name, And the url is used to set the jdbc url. Other attributes are related to DBCP (Database Connection Pool), which is a Connection Pool mechanism built in Tomcat.

When the application is deployed, Tomcat will follow the context in the META-INF. to find the specified driver, you must place the JAR file of the driver in the lib directory of Tomcat, then Tomcat sets related resources for the JNDI name jdbc/demo.

It is insecure to use root to operate the database. The root account is used to perform full operations on the database, and the application sets users with appropriate permissions to operate the database.