Connect to MySQL Database

JDBC Connection Database

Create a program that connects to the database in JDBC with 7 steps:
1. Load the JDBC driver:
Before connecting to the database, first load the driver of the database you want to connect to the JVM (Java Virtual machine),
This is achieved through the static method forname (String className) of the Java.lang.Class class.
For example:

try{ //加载MySql的驱动类         Class.forName("com.mysql.jdbc.Driver");     }catch(ClassNotFoundException e)   {       System.out.println("找不到驱动程序类 ，加载驱动失败！");    e.printStackTrace();   }   

After a successful load, an instance of the driver class is registered in the DriverManager class.
2. Provide the URL of the JDBC connection

• The connection URL defines the protocol, sub-protocol, and data source identity when the database is connected.
• Writing form: Protocol: Sub-Protocol: Data source identification
• Protocol: Always start with JDBC in JDBC
• Sub-Protocol: A bridge-connected driver or database management system name.
• Data source identification: The tag locates the address of the database source and the connection port.

jdbc:mysql:       //localhost:3306/test?useUnicode=true&characterEncoding=gbk;  

useUnicode=true：表示使用Unicode字符集。
If Characterencoding is set to gb2312 or GBK, this parameter must be set to true &characterEncoding=gbk; character encoding.
3. Create a connection to the database

• To connect to a database, you need to request and obtain a connection object from Java.sql.DriverManager, which represents a connection to a database.
• The DriverManager getconnectin (string URL, string username, string password) method is used to pass in the path of the specified database to be connected, the user name of the database, and the password to obtain.

For example:

//连接MySql数据库，用户名和密码都是root   String url = "jdbc:mysql://localhost:3306/test" ;    String username = "root" ;   String password = "root" ;   try{       Connection con = DriverManager.getConnection(url , username , password ) ;        }catch(SQLException se){               System.out.println("数据库连接失败！");               se.printStackTrace() ;         }   

4. Create a statement

• To execute the SQL statement, you must obtain the Java.sql.Statement instance, which is divided into the following 3 statement instances
  Type of:
  
  1. Executes a static SQL statement. Typically implemented through statement instances.
  2. Executes a dynamic SQL statement. Typically implemented through PreparedStatement instances.
  3. Executes the database stored procedure. Typically implemented through CallableStatement instances.

The specific implementation method:

   Statement stmt = con.createStatement() ;      PreparedStatement pstmt = con.prepareStatement(sql) ;      CallableStatement cstmt = con.prepareCall("{CALL demoSp(? , ?)}") ;   

5. Execute SQL statements

• The statement interface provides three ways to execute SQL statements: ExecuteQuery, executeupdate, and execute
  
  1. ResultSet executeQuery (String sqlString): Executes the SQL statement that queries the database and returns a result set (ResultSet) object.
  2. int executeupdate (String sqlString): Used to perform INSERT, UPDATE, or
     Delete statements and SQL DDL statements, such as CREATE table and drop table
  3. Execute (sqlString): Used to perform a return of multiple result sets, multiple update counts, or a combination of the two
     Statement.

  ResultSet rs = stmt.executeQuery("SELECT * FROM ...") ;     int rows = stmt.executeUpdate("INSERT INTO ...") ; boolean flag = stmt.execute(String sql) ; 

6. Processing results

• Two cases:
  
  1. The update is performed to return the number of records affected by this operation.
  2. The result returned by the execution query is a ResultSet object.

• ResultSet contains all rows that conform to the conditions in the SQL statement, and it provides a set of get methods for these
  Access to the data in the row.
• Get data using the access method of the result set (ResultSet) object:

while(rs.next()){               String name = rs.getString("name") ;               String pass = rs.getString(1) ; // 此方法比较高效 } 

(columns are numbered from left to right and start with column 1)
7. Close the JDBC object
All JDBC objects used are closed after the operation is complete to release the JDBC resource, turn off order harmony
The opposite of the Ming Order:
1. Close record set
2. Closing the statement

      if(rs != null){   // 关闭记录集       try{           rs.close() ;       }catch(SQLException e){           e.printStackTrace() ;       }         }         if(stmt != null){   // 关闭声明       try{           stmt.close() ;       }catch(SQLException e){           e.printStackTrace() ;       }         }         if(conn != null){  // 关闭连接对象        try{           conn.close() ;        }catch(SQLException e){           e.printStackTrace() ;        }         }  

Connect to MySQL Database