A basic case of using JDBC to connect to a MySQL database

JDBC Concept (from Baidu Encyclopedia)

JDBC (Java Database Connectivity,java connection) is a Java API for executing SQL statements that provides unified access to a variety of relational databases, consisting of a set of classes and interfaces written in the Java language. JDBC provides a benchmark to build more advanced tools and interfaces that enable database developers to write database applications.

Combining the Java language and JDBC allows programmers to write different applications for different platforms, just by writing the program to run on any platform, which is also the Java language's "write once, run everywhere" advantage.

JDBC is an API for Java programmers and is an interface model for service providers that implement database connections. As a standard interface for program development, API,JDBC provides a standard method for database vendors and third-party middleware vendors to connect with databases.

In short, JDBC can do three things: establish a connection to the database, send a statement that operates the database, and process the results.

JDBC-related architectures

where JDBC Works:

JDBCAPI:

JDBC-related URLs:

Steps to build JDBC:

Attention:

1) Before testing the data program, be sure to remember to open the MySQL service on the computer where the database is located. Otherwise wasted time is limitless.

2) Be sure to remember to import the MySQL jar package.

3) It is best to set the Connection object to singleton mode to prevent memory crashes when the database accesses are too large.

4) Use the forname () method of the class object, either in a static method or in a static block.

5) Remember the relevant database operation after frees up memory and cleans up the environment.

6) PreparedStatement inherits from statement, so statement's related methods, PreparedStatement can be called. At the same time PreparedStatement is very powerful, recommended to use it more.

7) to place the related memory-freed statement in the finally in the Try...catch statement to prevent the statement execution in the try block from failing and the associated memory not being freed.

Show case

 PackageCom.java_jdbc;Importjava.sql.Connection;ImportJava.sql.DriverManager;Importjava.sql.PreparedStatement;ImportJava.sql.ResultSet;Importjava.sql.SQLException;Importjava.sql.Statement; Public classJdbc_test {Private StaticString driver= "Com.mysql.jdbc.Driver";//Database-driven//the URL address of the connection database    Private StaticString url= "Jdbc:mysql://localhost:3306/hellojdbc?useunicode=true&characterencoding=utf-8"; Private StaticString username= "root";//database connection user name    Private StaticString password= "123456";//Database connection Password        Private StaticConnection conn=NULL;//Database Connection Object    Private StaticStatement stat=NULL;//Statement Statements Object    Private StaticResultSet rs=NULL;//result Data Set    Private StaticPreparedStatement pst=NULL;//Pre-compiled statements//load the driver using static blocks    Static {        Try {            //Call the static forname () method of the class object to load the database driver classClass.forName (Driver); } Catch(ClassNotFoundException e) {e.printstacktrace (); }            }        //To return a database connection object using singleton mode     Public StaticConnection getconnection ()throwssqlexception{if(conn==NULL) {Conn=drivermanager.getconnection (URL, username, password); returnConn; }        returnConn; }             //Test database connection is normal     Public Static voidIsconnect ()throwssqlexception{Connection con=getconnection (); if(con!=NULL) System.out.println ("Database connection OK"); ElseSystem.out.println ("Database connection Failed"); }            //querying all information in all database tables     Public Static voidQuerytoall () {Try{stat=conn.createstatement ();//Statement object by connection Get statementRs=stat.executequery ("SELECT * from roster");//Get result set information             while(Rs.next ()) {System.out.println ("ID:" +rs.getint ("id") + ", Name:" +rs.getstring ("name") + ", Age:" +rs.getint ("Age")); }        } Catch(SQLException e) {e.printstacktrace (); }finally{clean (); }                    }            //find information in a database by name     Public Static voidqueryforname (String name) {Try{PST= Conn.preparestatement ("SELECT * from roster where name=?");//get pre-compiled statementsPst.setstring (1, name);//Set Precompiled statement parametersRs=pst.executequery ();//execute a precompiled statement to get the result data set             while(Rs.next ()) {System.out.println ("ID:" +rs.getint ("id") + ", Name:" +rs.getstring ("name") + ", Age:" +rs.getint ("Age")); }        } Catch(SQLException e) {e.printstacktrace (); }finally{clean (); }            }            //increase data information in the database     Public Static voidInsert () {Try{String SQL= "INSERT into roster (Id,name,age) VALUES (' 4 ', ' Zhangsan ', ' 14 ')"; Stat=conn.createstatement (); intCount=stat.executeupdate (SQL);//to perform an update operation on a statement statement objectSystem.out.println ("Insert operation succeeded, insert Information Bar number is" +count); } Catch(SQLException e) {e.printstacktrace (); }finally{clean (); }            }        //modifying data information in a database     Public Static voidAlter () {Try{String SQL= "Update roster set age=12 where name= ' Zhangsan '"; Stat=conn.createstatement (); intCount = stat.executeupdate (SQL);//to perform an update operation on a statement statement objectSYSTEM.OUT.PRINTLN ("Modify operation succeeded, modify Information Bar number to" +count); } Catch(SQLException e) {e.printstacktrace (); }finally{clean (); }            }        //Delete data information from a database     Public Static voidDelete () {Try{String SQL= "Delete from roster where name= ' Zhangsan '"; Stat=conn.createstatement (); intCount=stat.executeupdate (SQL);//to perform an update operation on a statement statement objectSystem.out.println ("Delete operation succeeded, delete Information Bar number is" +count); } Catch(SQLException e) {e.printstacktrace (); }finally{clean (); }                    }        //free up memory, clean up the environment     Public Static voidClean () {Try {            if(rs!=NULL) Rs.close (); if(pst!=NULL) Pst.close (); if(stat!=NULL) Stat.close (); //You cannot turn off the connection connection object in singleton mode, or you cannot perform other database operations after a database operation has completed//if (conn!=null)//conn.close ();//                                            } Catch(SQLException e) {e.printstacktrace (); }            }             Public Static voidMain (string[] args)throwsSQLException {isconnect (); Queryforname ("Bob"); System.out.println ("--------------------------------------");        Querytoall (); System.out.println ("--------------------------------------");        Insert (); System.out.println ("--------------------------------------");        Querytoall (); System.out.println ("--------------------------------------");        Alter (); System.out.println ("--------------------------------------");        Querytoall (); System.out.println ("--------------------------------------");        Delete (); System.out.println ("--------------------------------------");    Querytoall (); }}

Execution Result:

Reference blog:

Https://www.cnblogs.com/erbing/p/5805727.html

Https://www.cnblogs.com/wuziyue/p/4827295.html

A basic case of using JDBC to connect to a MySQL database