6.JDBC Simple Learning (using idea)

First, prepare the work, install the database (mysql,oracle, etc.)

Slightly

Two

1) Open idea and create a new project

2) Create a new directory under the newly created project to hold the jar packages needed to connect to the MySQL database. Name is libs. Then paste in the "Mysql-connector-java-5.1.42.jar" package.

3) Select the project, use the shortcut key Control+shift+alt+s, select modules in the left window, select Dependencies in the right window, click the "+" sign in the picture, add the jar package, and then click Apply.

4) Here, basically has been created successfully, followed by the Code section.

ImportJava.sql.*;/** * @authorVi * @create 2018-07-28 * Test Database*/ Public classJdbctest {//MySQL driver package name    Private Static FinalString driver_name = "Com.mysql.jdbc.Driver"; //Database Connection Address    Private Static FinalString URL = "Jdbc:mysql://127.0.0.1:3306/shopmanagement"; //User name    Private Static FinalString user_name = "root"; //Password    Private Static FinalString PASSWORD = "root";  Public Static voidMain (string[] args) {Connection conn=NULL; Try {            //load the MySQL driver classClass.forName (driver_name); //Get database connectionconn =drivermanager.getconnection (Url,user_name,password); //MySQL Query statementString sql = "SELECT * FROM Demo"; PreparedStatement PRST=conn.preparestatement (SQL); //result setResultSet rs =Prst.executequery ();  while(Rs.next ()) {System.out.println ("User name:" +rs.getstring ("name") + "----Password:" +rs.getstring ("password"));            } rs.close ();        Prst.close (); } Catch(Exception e) {e.printstacktrace (); } finally {            if(Conn! =NULL) {                Try{conn.close (); } Catch(SQLException e) {e.printstacktrace (); }            }        }    }}

Test results:

Third, the JDBC development steps

1. Importing JAR Packages
2. Load Driver
   Class.forName ("Com.mysql.jdbc.Driver"); Fixed notation

3. Get database connection
   Connection Connection = drivermanager.getconnection (Url,user,password);
   URL format:
   Master Protocol: Sub-Protocol://HOST: Port/Database name
   Jdbc:mysql://Localhost:3306/mydatabase
   User: Database username
   Password: Connect database password

   DriverManager class: Managing the JDBC Driver service class, the main function is to get the connection object

4. Creating statement objects from connection
   Connection class: Represents a database connection, you must first obtain a database connection to access the database
5. Use the statement object to perform operations such as SQL statements, crud, and so on, returning a ResultSet object

6. ResultSet operation Result set
   Next () Method: Used to determine if there is a next record. If there is a return of true, and if the cursor is moved down one line, false is not returned.

   GetXxx () Method: Gets the column data in the record to which the current cursor is pointing. such as: GetInt (), getString (), GetDate (), getdouble (), etc.

7. Recycling Resources

Iv. Introduction to the corresponding API for JDBC.

(1) Define the class of the record

classPerson {PrivateString ID; PrivateString name; PrivateString sex; PrivateString age;  PublicPerson (String name,string sex,string age) { This. Name =name;  This. Sex =sex;  This. Age =Age ;  This. ID =NULL;//default    }     PublicString GetName () {returnname; }     PublicString Getsex () {returnsex; }     PublicString getage () {returnAge ; }     Public voidsetName (String name) { This. Name =name; }     Public voidsetsex (String sex) { This. Sex =sex; }     Public voidsetage (String age) { This. Age =Age ; }}

2) Get the connection

    Private StaticConnection getconnection () {String driver= "Com.mysql.jdbc.Driver"; String URL= "Jdbc:mysql://localhost:3306/test"; String User= "Root"; String Password= "Root"; Connection Conn=NULL; Try{class.forname (driver); //load the corresponding driverconn =drivermanager.getconnection (Url,user,password); } Catch(ClassNotFoundException e) {e.printstacktrace (); } Catch(SQLException e) {e.printstacktrace (); }        returnConn; }

3) Insert

    Private Static intInsert (person person) {Connection conn=getconnection (); inti = 0; String SQL= "INSERT into person (Name,sex,source) VALUES (?,?,?)"; PreparedStatement pstmt=NULL; Try{pstmt=(PreparedStatement) conn.preparestatement (SQL); Pstmt.setstring (1, Person.getname ()); Pstmt.setstring (2, Person.getsex ()); Pstmt.setstring (3, Person.getfrom ()); I=pstmt.executeupdate (); } Catch(SQLException e) {e.printstacktrace (); }        returni; }

4) Update

    PrivateStaticintUpdate (person person) {Connection conn=getconnection (); inti = 0; String SQL= "Update person set source= '" +person.getfrom () + "' Where Name= '" +person.getname () + "'";        PreparedStatement pstmt; Try{pstmt=(PreparedStatement) conn.preparestatement (SQL); I=pstmt.executeupdate (); System.out.println ("Result:" +i);        Pstmt.close (); } Catch(SQLException e) {e.printstacktrace (); } finally {            if(Conn! =NULL) {                Try{conn.close (); } Catch(SQLException e) {e.printstacktrace (); }            }        }        returni; }

5) Select

PrivateStaticvoidGetAll () {Connection conn=getconnection (); String SQL= "SELECT * FROM Person"; PreparedStatement pstmt=NULL; Try{pstmt=(PreparedStatement) conn.preparestatement (SQL); ResultSet RS=Pstmt.executequery (); intCol =rs.getmetadata (). getColumnCount (); System.out.println ("=========================================");  while(Rs.next ()) { for(inti = 1; I <= col; i++) {System.out.print (rs.getstring (1) + "\ T"); if((i==2) && (rs.getstring (i). Length () < 8) {System.out.print ("\ T");            }} System.out.println (); } System.out.println ("=========================================");        Pstmt.close (); } Catch(SQLException e) {e.printstacktrace (); } finally {            if(Conn! =NULL) {                Try{conn.close (); } Catch(SQLException e) {e.printstacktrace (); }            }        }    }

6) Delete

    Private Static intDelete (String name) {Connection conn=getconnection (); inti = 0; String SQL= "Delete from person where name= '" +name+ "'"; PreparedStatement pstmt=NULL; Try{pstmt=(PreparedStatement) conn.preparestatement (SQL); I=pstmt.executeupdate (); System.out.print ("Deleted:" +i+ "bar Data"); } Catch(SQLException e) {e.printstacktrace (); } finally {            if(Conn! =NULL) {                Try{conn.close (); } Catch(SQLException e) {e.printstacktrace (); }            }        }        returni; }

Test:

  Public Static voidMain (string[] args) {//Add Data//Person p1 = new Person ("Edward Allick", "Male", "Alchemist of Steel");//Person p2 = new Person ("Wen Li Lockebell", "female", "Alchemist of Steel");////System.out.println (Insert (p1));//System.out.println (Insert (p2)); //Modify//Person p = new person ("Cherry Blossom Road", "male", "slam Dunk Master");//Update (p); //DeleteSystem.out.println (Delete ("Edward Allick")); //EnquiryGetAll (); }

Test results:

Code Analysis

In the process of the above-mentioned database, it can be found that the common part, namely the general process:

(1) Create connection object, SQL query command string;

(2) The connection object is passed into the SQL query command to obtain the PreparedStatement object;

(3) executeupdate () or Executequrey () to the PreparedStatement object to obtain the results;

(4) Close the PreparedStatement object and the connection object successively.

As you can see, when using JDBC, the most common dealings are the two classes of connection, PreparedStatement, and the ResultSet class in select

6.JDBC Simple Learning (using idea)