7th Chapter WEB07-JDBC

Mission today
? Using JDBC to complete a crud operation on a taxonomy table (JDBC Review)
? Using the DBCP,C3P0 connection pool to complete the operation of the base database (Extraction tool Class)
? Use metadata to extract a JDBC framework.
? Ability to use Dbutils to complete CRUD operations
Teaching navigation
Teaching objectives
Master the use of DBCP and C3P0 connection pools and master the principle of connection pooling.
Master the idea of using meta data to extract basic JDBC common methods.
Ability to use Dbutils to complete basic CRUD operations
Teaching methods
Case-driven approach
1.1 Previous lesson Content review:
Mysql:

• Overview of the database: refers to a file system that is accessed through SQL.
• Relational databases: The relationships between entities are stored in the database.
• Common relational database: Mysql,oracle,db2,sqlserver,sqlite,sybase
  MySQL usage:
• Install and Uninstall:
• Storage structure:
  • A computer installed MySQL server software, then this computer is called a MySQL database server. A software creates a database that has an entity class that creates a table corresponding to it, and the entity's instance object is usually mapped to it using the records in the table.
    SQL: Structured Query Language:
• Classification of SQL:
  • DDL: Data definition language. Create,alter,drop.
  • DML: Data manipulation language. Update,insert,delete
  • DCL: Data Control language. Grant,if,...
  • DQL: Data Query Language. Select
• SQL to database crud:
• SQL operations on table crud in the database
• SQL operations on records of tables in a database:
  • Update,insert,delete
  • Select ... From ... Where ... Group by ... Having ... Order by ...;
• Multi-table design for SQL:
  • One-to-many: the primary key of a party that creates a foreign key on a multiple side.
  • Many-to-many: Create an intermediate table with at least two fields as the foreign key to the primary key of many-to-many sides.
  • One-to-one: the only foreign key corresponds to the primary key.
• Multi-table queries for SQL:
  • Connection query:
    • Cross Connect:
    • Internal connection:
      • Explicit intra-connection: inner JOIN
      • Implicit Intra-connection:
    • External connection
      • Left outer connection: outer JOIN
      • Right outer connection: outer JOIN
  • Sub-query:
    1.2 Case one: using JDBC to complete CRUD operations: 1.2.1 Requirements:
    Use JDBC for CRUD operations on taxonomy management.
    1.2.2 Analysis:
    1.2.2.1 Technical Analysis:
    "Overview of JDBC"
    ? Jdbc:java database Connectivity The connection to the Java databases.
• is a unified set of interface Specifications (JDBC) provided by Sun Corporation. Each database manufacturer provides implementations.
  ? Drive: A bridge of communication between two hardware devices.
  "Development Steps for JDBC"
  ? Registration drive:
  ? Get Connected:
  ? Get the Execute SQL statement object:
  ? Release resources:
  1.2.2.2 Step Analysis:
  Step one: Create a Java project.
  "Step Two": Introduce the MySQL driver package.
  Step three: Write the code.
  Step four: Complete the crud operation:
  1.2.3 Code implementation:

Tool Class Extraction: public class Jdbcutils {/** * Registration-driven method */public static void Loaddriver () {try {class.forname ("Com.mysql.jdbc.Driver ");} catch (ClassNotFoundException e) {e.printstacktrace ();}} /** * Method of obtaining a connection */public static Connection getconnection () {Connection conn = null;try {loaddriver (); conn = Drivermanager.get Connection ("jdbc:mysql:///web_07", "root", "123");} catch (SQLException e) {e.printstacktrace ();} Return conn;}            /** * Method of releasing resources */public static void release (ResultSet rs,statement stmt,connection conn) {if (rs! = null) {try {        Rs.close ();        } catch (SQLException e) {e.printstacktrace ();        }//garbage collection reclaims objects as quickly as possible.    rs = null;        }if (stmt! = null) {try {stmt.close ();        } catch (SQLException e) {e.printstacktrace ();        }//garbage collection reclaims objects as quickly as possible.    stmt = null;        }IF (conn! = null) {try {conn.close ();    } catch (SQLException e) {e.printstacktrace ();    }//garbage collection reclaims objects as quickly as possible.    conn = null;         }}public static void Release (Statement stmt,connection conn) {if (stmt! = null) {try {stmt.close ();        } catch (SQLException e) {e.printstacktrace ();        }//garbage collection reclaims objects as quickly as possible.    stmt = null;        }IF (conn! = null) {try {conn.close ();        } catch (SQLException e) {e.printstacktrace ();        }//garbage collection reclaims objects as quickly as possible.    conn = null; The extract of the tool class with the property file: Defines a property file: public class Jdbcutils {private static final string driverclass;private static final string ur L;private static final string username;private static final string Password;static {properties Properties = null;//Read Property file : An object that uses properties in java. try{inputstream is = new FileInputStream ("Src/jdbc.properties");p roperties = new properties (); Properties.load (IS);} catch (Exception e) {e.printstacktrace ();} Driverclass = Properties.getproperty ("Driverclass"); url = properties.getproperty ("url"); UsernamE = Properties.getproperty ("username");p assword = properties.getproperty ("password");} /** * Registration-driven method */public static void Loaddriver () {try {class.forname (driverclass);} catch (ClassNotFoundException e) {E.pri Ntstacktrace ();}} /** * Method of obtaining a connection */public static Connection getconnection () {Connection conn = null;try {loaddriver (); conn = Drivermanager.get Connection (URL, username, password);} catch (SQLException e) {e.printstacktrace ();} Return conn;}            /** * Method of releasing resources */public static void release (ResultSet rs,statement stmt,connection conn) {if (rs! = null) {try {        Rs.close ();        } catch (SQLException e) {e.printstacktrace ();        }//garbage collection reclaims objects as quickly as possible.    rs = null;        }if (stmt! = null) {try {stmt.close ();        } catch (SQLException e) {e.printstacktrace ();        }//garbage collection reclaims objects as quickly as possible.    stmt = null;        }IF (conn! = null) {try {conn.close ();           } catch (SQLException e) {     E.printstacktrace ();        }//garbage collection reclaims objects as quickly as possible.    conn = null;         }}public static void Release (Statement stmt,connection conn) {if (stmt! = null) {try {stmt.close ();        } catch (SQLException e) {e.printstacktrace ();        }//garbage collection reclaims objects as quickly as possible.    stmt = null;        }IF (conn! = null) {try {conn.close ();        } catch (SQLException e) {e.printstacktrace ();        }//garbage collection reclaims objects as quickly as possible.    conn = null; }}}

See JDBCDemo2 class:
1.2.4 Summary:
1.2.4.1 JDBC API:
"Connection"
? To create an object that executes SQL:

? For transaction management:

"Statement"
? Execute SQL statement:

? Perform batch processing:

"ResultSet"
? To get the data in the result set:

• GetXXX (int idx);
  • Select Cname,cid from category;
• GetXXX (String name);
  ? By default: Next ();
• In normal cases, the result set can only be downward.
  1.3 Case Two: tool classes for using connection pooling to transform JDBC:
  1.3.1 Requirements:
  Traditional JDBC operations are not particularly good at destroying connected objects. It takes time to create and destroy connections every time. A program that can use connection pooling optimization.
• At the beginning of the program, you can create several connections to put the connection into the connection pool. When users use a connection, they can get it from the connection pool. After you run out of time, you can return the connection to the connection pool.
  1.3.2 Analysis:
  1.3.2.1 Technical Analysis:
  "Custom connection Pooling" (learn)
• Sun provides an interface to a pool of connections. (Javax.sql.DataSource).
• Define a connection pool: implement this interface.
• Use the list collection to hold multiple connected objects.
  "Code for custom Connection pool"

public class MyDataSource implements DataSource{// 创建一个List集合用于存放多个连接对象.private List<Connection> list = new ArrayList<Connection>();// 在程序开始的时候，初始化几个连接,将连接存放到list中.public MyDataSource() {// 初始化3个连接:for(int i=1;i<=3;i++){Connection conn = JDBCUtils.getConnection();list.add(conn);}}@Override// 获得连接的方法：public Connection getConnection() throws SQLException {if(list.size() <= 0){for(int i=1;i<=3;i++){Connection conn = JDBCUtils.getConnection();list.add(conn);}}Connection conn = list.remove(0);return conn;}// 归还连接的方法：public void addBack(Connection conn){list.add(conn);}...}

"Custom connection pool issues and how to resolve"
? Problem?
1. If you are using a custom connection pool, you will need to remember the APIs in the custom connection pool extra.
2. Can I use an interface-oriented programming method?
? Solve:
Do not provide additional API methods, you can solve the above two problems!!!
Can you also call the Close method of connection. Can enhance the connection Close method, the original destruction into return!!!
? How to enhance the Close method of connection:

• There are several ways to enhance a method in a Java class???
  • One way: the way inheritance is. Inheritance can only be used when
    • can control the construction of this class.
  • Two ways: Decorator mode. Both the
    • wrapper object and the wrapped object implement the same interface.
    The
    • wrapper object needs to get a reference to the wrapped object.
      * * * * * Disadvantages: If the interface method is more, enhance one of the methods. Other functions of the method require the original call.
  • Three ways: Dynamic proxy mode.
    • an Enhanced object implementation interface is available.
      "Inheritance and decorator case"
      /**
• inherited ways to enhance a method in a class:
  */
  Class man{
  public void Run () {
  Sys Tem.out.println ("Run ....");
  }
  }
  Class Superman extends man{
  public void Run () {
  //Super.run ();
  System.out.println ("Fly ....");
  }
  }
  /**
• enhancements to methods of the class using adorners
  */
  Interface waiter{
  public void Server ();
  }
  Class Waiteress implements waiter{br/> @Override
  System.out.println ("service ...");
  }

}
Class Waiteresswrapper implements waiter{
Private waiter waiter;
Public Waiteresswrapper (Waiter waiter) {
This.waiter = Waiter;
}

    @Override    public void server() {            System.out.println("微笑...");            // this.waiter.server();    }

}
"Use decorator mode to enhance connection's Close method"
public class MyConnection implements connection{
Private Connection Conn;
Private list<connection> List;
Public myconnection (Connection conn,list<connection> List) {
This.conn = conn;
This.list = List;br/>}
@Override
LIST.ADD (conn);
}
...
}
Getconnection method for connection pooling:
@Override
Public Connection getconnection () throws SQLException {
if (list.size () <= 0) {
for (int i=1;i<=3;i++) {
Connection conn = Jdbcutils.getconnection ();
LIST.ADD (conn);
}
}
Connection conn = list.remove (0);
MyConnection myconn = new MyConnection (conn, list);
return myconn;
}
"Common open Source database connection pool":
? DBCP:
DBCP (database connection pool), DB connection pool. is a Java Connection pool project on Apache and a connection pool component used by Tomcat. Use of DBCP alone requires 2 packages: Commons-dbcp.jar, Commons-pool.jar because establishing a database connection is a very time-consuming and resource-intensive behavior, the connection pool is pre-established with the database to make some connections, put in memory, the application needs to establish a database connection directly to the connection pool to apply for a line, run out and then put back.
? C3P0:
C3P0 is an open source JDBC connection pool that implements the data source and Jndi bindings, and supports the standard extensions of the JDBC3 specification and JDBC2. The open source projects that currently use it are hibernate,spring and so on.
? Tomcat built-in connection pool:
"Use of the DBCP connection pool"
First step: Introduce the jar package of the DBCP connection pool.
Step two: Write the DBCP code:

• To set parameters manually:

• Configuration file setup parameters:br/> "DBCP Connection Pool Usage"
  @Test
  < p="">

  • Manual mode:
    /
    public void Demo1 () {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    Basicdatasource DataSource = new Basicdatasource ();
    Datasource.setdriverclassname ("Com.mysql.jdbc.Driver");
    Datasource.seturl ("jdbc:mysql:///web_07");
    Datasource.setusername ("root");
    Datasource.setpassword ("123");
    try{
    Get Connected:
    conn = Datasource.getconnection ();
    Write sql:
    String sql = "Select from category";
    Precompiled sql:
    stmt = conn.preparestatement (sql);
    Execute SQL:
    rs = Stmt.executequery ();
    while (Rs.next ()) {
    System.out.println (Rs.getint ("CID") + "" "+rs.getstring (" cname ");
    }
    }catch (Exception e) {
    E.printstacktrace ();
    }finally{
    Jdbcutils.release (RS,STMT, conn);
    }
    }

    @Test
    /**

  • Configuration file Mode:
    */
    public void Demo2 () {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    Properties Properties = new properties ();

        try{            properties.load(new FileInputStream("src/dbcpconfig.properties"));            DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);            // 获得连接:            conn = dataSource.getConnection();            // 编写SQL:            String sql = "select * from category";            // 预编译SQL:            stmt = conn.prepareStatement(sql);            // 执行SQL:            rs = stmt.executeQuery();            while(rs.next()){                    System.out.println(rs.getInt("cid")+"   "+rs.getString("cname"));            }    }catch(Exception e){            e.printStackTrace();    }finally{            JDBCUtils.release(rs,stmt, conn);    }

    }
    "Use of the C3P0 connection pool"
    First step: Introduce the jar package of the C3P0 connection pool.
    Step Two: Write the code:

    • To set parameters manually:
    • Configuration file Setup parameters:
      "C3P0 Transformation Tool Class"
      public class JDBCUtils2 {
      private static final Combopooleddatasource data_source =new Combopooleddatasource ();
      /**
  • method for obtaining a connection
    */
    public static Connection getconnection () {
    Connection conn = null;
    try {
    conn = data_source.getconnection ();
    } catch (SQLException e) {
    //TODO auto-generated catch block
    E.pri Ntstacktrace ();
    }
    Return conn;
    }
    ...
    }
    1.4 case three: Manually extracting a Dbutils tool class:
    1.4.1 Requirements:
    Each time a JDBC curd operation is performed, a lot of the code is similar. You can not extract the tool class. Complete some common code?
    1.4.2 Analysis:
    1.4.2.1 Technical Analysis:
    "jdbc Metadata matadata" (understanding)-write code that is highly versatile.
    ? DatabaseMetaData: Gets the database connection information, obtains the database the table the information.
    • Get Database metadata: Connection GetMetaData ();
      ? Parametermetadata: Gets the number and type of arguments in SQL.
    • Get parameter metadata: PreparedStatement in Getparametermetadata ()
      ? ResultSetMetaData: Gets the column name and the type of the column in the result set.
    • Get result set metadata: Use of Getmeta () br/> "metadata in resultset"
      @Test
      

  • Database Metadata
    */
    public void Demo1 () {
    Connection conn = null;
    conn = Jdbcutils2.getconnection ();
    //Get database meta data:
    try {
    DatabaseMetaData metaData = Conn.getmetadata ();
    System.out.println ("Get Driver Name:" +metadata.getdrivername ());
    System.out.println ("Get Driver URL:" +metadata.geturl ());
    System.out.println ("Get User name:" +metadata.getusername ());

     //Get the primary key in the table: ResultSet rs = Metadata.getprimarykeys (null, NULL, "category");                    if (Rs.next ()) {String name = rs.getstring ("column_name");            SYSTEM.OUT.PRINTLN (name);    }} catch (SQLException e) {e.printstacktrace ();  

    }

    @Test
    /**

  • Parameter metadata:
    */
    public void Demo2 () {
    Connection conn = null;
    PreparedStatement stmt = null;
    try{
    conn = Jdbcutils2.getconnection ();
    String sql = "Update category set cname =?" WHERE cid =? ";
    stmt = conn.preparestatement (sql);
    Parametermetadata metaData = Stmt.getparametermetadata ();
    int count = Metadata.getparametercount ();
    System.out.println (count);
    }catch (Exception e) {

     }  

    }

    @Test
    /**

  • Result set meta data:
    /
    public void Demo3 () {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try{
    conn = Jdbcutils2.getconnection ();
    String sql = "Select from category";
    stmt = conn.preparestatement (sql);
    rs = Stmt.executequery ();

                ResultSetMetaData metaData = rs.getMetaData();            int count = metaData.getColumnCount();            for(int i = 1;i<=count ;i++){                    String name = metaData.getColumnName(i);                    String type = metaData.getColumnTypeName(i);                    System.out.println(name+type);            }    }catch(Exception e){    }

    }

7th Chapter WEB07-JDBC