15th Article-java JDBC Programming

15th article-java JDBC Programming

• every sentence : For the brave, try to be a new way of life

• Beginner's experience : But for rough and muddy, can be seen in the ordinary heart, it is very difficult

• ( Author: jeep/711) [Java Notes | time: 2017-05-08| JAVA JDBC Programming]

1.JDBC Overview

• By using the JDBC Api,java program, it is very convenient to operate the various mainstream databases, which is the great charm of the Java language

• Because of the cross-platform nature of the Java language, programs written using the JDBC API can be implemented across databases and across platforms with excellent portability

• The program uses the JDBC API to connect the different databases in a uniform way, then executes the standard SQL statements through the statement object, and can obtain the results of the SQL statements accessing the database

2.JDBC Driver Type

• 1.JDBC Drive: Called the Jdbc–odbc Bridge, this driver is the first JDBC driver to be implemented, primarily to promote JDBC quickly. This driver maps the JDBC API to the ODBC API. JDBC-ODBC also needs to be driven, and this drive is provided by sun Company to implement

• 2.JDBC driver: Directly maps the JDBC API to a database-specific client API. This driver contains local code for a specific database that is used to access a specific database of clients

• 3.JDBC Drive: JDBC Access with three-tier architecture. Mainly used in the applet stage to access the database through applets

• 4.JDBC Drive: Pure Java, interacting directly with a DB instance. This driver is intelligent, and it knows the underlying protocol used by the database, which is the most popular JDBC driver currently

3. Install MySQL Database

• (1) Download MySQL installation file

• (2) After starting the installation of MySQL, click the "Next" button in the dialog box that appears

• (3) Click the "Next" button in the dialog box and a dialog box will appear that selects the installation components and the installation path.

• (4) Click the "Next" button in the dialog box to start installing the MySQL database system, the system also requires the MySQL database to be configured after successful installation.

• (5) When configuring the database, be careful to select the appropriate character set and set the password

Common commands for 4.MySQL

1.-Query database: Show databases;2.-Delete the database: Drop the database name; 3.-into the database: Use database name 4.-display all data that created the library name: Show tables;5.-output table name: Desc Table name 6. SQL statements Four parts 7.-data definition language (DDL): Drop, create, alter8.-Data Manipulation Language (DML): Insert, UPDATE, delete9.-data Query Language (DQL): Select, from, where, Group by10.-Data Control Language (DCL): Grant, REVOKE, COMMIT, rollback

5.JDBC Common interfaces and classes

• DriverManager

• Connection

• Statement

• PreparedStatement

• ResultSet

6.JDBC Programming Steps

• (1) Load Database driver

• (2) Get a database connection via DriverManager

• (3) Creating statement objects from connection objects

• (4) Execute the SQL statement using statement. All statement have the following three ways to execute an SQL statement

• (5) Operation result set. If the SQL statement executed is a query statement, the result of the execution returns a ResultSet object that holds the results of the SQL statement query. The program can remove the query results by manipulating the ResultSet object.

7.JDBC How SQL statements are executed

• Executing DDL and DML statements using executeupdate

• Executing SQL statements using the Execute method

• Executing a query statement using the ExecuteQuery method

8. Database Connection Pool

The solution to the database connection pool is that when the application starts, the system proactively establishes sufficient database connections and makes these connections a pool of connections
Each time the application requests a database connection, it does not need to reopen the connection, but instead removes the existing connection from the pool, and when it is finished, the database connection is not closed and the connection is returned directly to the connection pool. Through the use of connection pooling, the program will greatly improve the operational efficiency

Common parameters for database connection pooling are as follows:

• Number of initial connections to the database

• Connection pool maximum number of connections

• The minimum number of connections to the connection pool

• Each increased capacity of the connection pool

9. Two sources of open source data

• DBCP: An open source connection pool implementation under the Apache Software Foundation, which relies on another open source system under the organization: Common-pool

• C3P0: Data source performance is better, Hibernate recommends using this connection pool
  C3P0 connection pooling automatically cleans up connection that are no longer in use, and automatically cleans statement and resultset

10.JDBC Connection Driver Instance

1.package cn.jdbc;2.import java.sql.connection;3.import java.sql.drivermanager;4.import  java.sql.sqlexception;5./**6. *  create initialization and get 7. */8.public class myjdbc {9.   private static connection cot = null;//declaration 10.  //static code block 11.   static{12.      try {13.           class.forname ("Com.mysql.jdbc.Driver");14.           //database name, user name, password 15.          cot =  Drivermanager.getconnection ("Jdbc:mysql://localhost:3306/sqltestdb", "root", "root");16.           if (!cot.isclosed ()) {17.               SYSTEM.OUT.PRINTLN ("Data connection succeeded");18.           }19.      } catch  (classnotfoundexception e)  {20.           e.printstacktrace (); 21.      }catch ( Sqlexception e) {22.          e.printstacktrace (); 23.       }24.  }25.  public static Connection  Getinstace () {26.      return cot;//returns the object 27.  }28.}

1.package cn.jdbc;2.import java.sql.connection;3.import java.sql.resultset;4.import  java.sql.sqlexception;5.import java.sql.statement;6.import java.text.dateformat;7.import  java.text.parseexception;8.import java.text.simpledateformat;9.import java.util.date;10.import  Com.mysql.jdbc.preparedstatement;11./**12. * java connect the JDBC driver 13. *  @author  jeep-71114.  *15. */16.public class JdBc {17.   @SuppressWarnings ("unused") 18.   public static void main (String[] args)  throws SQLException,  parseexception {        19.           connection cot = myjdbc.getinstace ();//Connection 20.           statement st = cot.createstatement ();//Create Statement class object, To execute the SQL statement 21.          String sql =  "select * from emp";//SQL statement to execute 22.           resultset rs = st.executequery (SQL);// The ResultSet class, used to hold the get result set 23.          system.out.println ("------- ----------------------------------------");24.           System.out.println ("------------------execution results are as follows------------------");25.           system.out.println ("-----------------------------------------------");26.           system.out.println ("ID" + "\ T" + "name" + "\ T" + "title" + "\ T" + "\ T period" + "\ T" + " Funds "); 27.          system.out.println ("---------------------- -------------------------");28.          /**29.            *  Query 30.           */ 31.          string job = null;//Job 32.           string names = null;//name 33.           STRING EMPON = NULL;//ID, 34.           string hiredate = null;//period 35.           string sal = null;//Fund 36.           while (Rs.next ()) {37.               job = rs.getstring ("job");//Get the field titles in the table the St column data 38.               names = rs.getstring ("ename");//Get Name Data 39 .              empon = rs.getstring ("Empon");//Get ID number 40.               hiredate  =  rs.getstring ("HireDate");//Acquisition time period 41.               sal = rs.getstring ("Sal");//Get Money 42.               system.out.println (empon+ "\ t" +names+ "\ T" +job+ "\ T" +hiredate+ "\ T" +sal) ;//Output result 43.          /**44.            *  Add Data  45.            */46.          preparedstatement pl;47.           ResultSet res;48.           //preprocessing adds data with two parameters--"?" 49.          pl =  (PreparedStatement)   Cot.preparestatement ("insert into emp  (empon,ename,job,hiredate,sal)" +50.            "VALUES (?,?,?,?,?)"); 51.          pl.setint (1, 3211);//Set the parameter to 1,id to 321252.           pl.setstring (2,  "Wang Gang");//Set parameter is 2,name for Wang Gang 53.           pl.setstring (3,  "President");//Set parameter 3, position for President 54.           DateFormat df2 = new  SimpleDateFormat ("Yyy-mm-dd"); 55.          date mydate2  = df2.parse ("2010-09-13"); 56.          pl.setdate (4, new java.sql.date (Mydate2.gettime ())); 57.  &Nbsp;       pl.setfloat (5,  (float) 2000.3);58.           pl.executeupdate ();//Implement Update 59.           /**60.           *  Update Data 61.            */62.           //preparedstatement gx;63.          / /preprocessing update (Modify) data, change the Sal of Wang Gang to 5000.0064.          pl =  ( PreparedStatement)  cot.preparestatement ("update emp set sal = ? where  ename = ? "); 65.          pl.setfloat (1, (float)  5000.00);66.           pl.setstring (2,  "Wang Gang"); 67.    &Nbsp;     pl.executeupdate ();//Implement Modification 68.           /**69.           *  Delete Data 70.            */71.           //preparedstatement sc;72.          / /preprocessing Delete data 73.          pl =  (PreparedStatement)  cot.preparestatement ("DELETE FROM EMP WHERE SAL > ?"); 74.          pl.setfloat (1, 4500);75.           pl.executeupdate ();//Implement Delete 76.           pl.close ();//Close 77.          }78.           rs.close ();//close 79.          cot.close ();//Close 80.       } catch  (classnotfoundexception e)  {81.           SYSTEM.OUT.PRINTLN ("Database driven exception");//Database driver class exception handling 82.           e.printstacktrace (); 83.      }  catch  (sqlexception e)  {84.           SYSTEM.OUT.PRINTLN ("Database connection Failed");//Database connection failure exception handling 85.           E.printstacktrace ();86.      } catch  (Exception e)  {87.           e.printstacktrace ();88.       }finally{89.          system.out.println (" Database data successfully obtained! "); 90.    &Nbsp; }91.  }92.} 

Beginner (JAVA JDBC Programming advanced stage) Difficulty: ★★★★★ hope that each article can provide readers with help and promotion, which is the original intention of every author Thank you for your reading welcome your message and suggestions

• Facebook:jeep SevenEleven

• Twitter: @JEEP7ll

• Sina official Weibo: @JEEP-711

• GitHub Blog: Https://github.com/jeep711/jeep711.github.io

• Blog garden:http://www.cnblogs.com/jeep711/

• w3c/blog:http://www.w3cschool.cn/jeep711blog/

• csdn/blog:http://blog.csdn.net/jeep911

• 51cto/blog:http://jeep711.blog.51cto.com/

• Code Cloud: Http://git.oschina.net/JEEP711/jeep711.github.io

• Email: [email protected],[email protected]

• 
  

This article is from the "JEEP711" blog, make sure to keep this source http://jeep711.blog.51cto.com/12970787/1955816

15th-java JDBC Programming