[MySQL 05] Use Java to operate MySQL (create database)

JDBC connection database:

1. Load the JDBC driver
2. Provide the URL of the JDBC connection
3. Create a database connection
4. Create a Statement
5 , Execute SQL statements
6. Process results
7. Close the JDBC object

1. Load the JDBC driver:

Before connecting to the database, you must first load the database you want to connect to The database is driven to the JVM (Java Virtual Machine). After it is successfully loaded through the static method

 //加载数据库驱动总共有三种方式：
 try{   
    //①  
    Class.forName("com.mysql.jdbc.Driver") ;   
    //②    com.mysql.jdbc.Driver driver = new com.mysql.jdbc.Driver();
    //③
    new com.mysql.jdbc.driver();

 }catch(ClassNotFoundException e){   
    System.out.println("找不到驱动程序类 ，加载驱动失败！");   
    e.printStackTrace() ;   
 }

of the java.lang.Class class, an instance of the Driver class will be registered in the DriverManager class.

2. Provide the URL for JDBC connection

• The connection URL defines the protocol, sub-protocol, and data source identification when connecting to the database.

• MYSQL JDBC URL writing method: jdbc:mysql://host name: port number/database name? user = username & password = password & useUnicode = true & characterEncoding = UTF8

 String url = "jdbc:mysql://localhost:3306/samp_db?" + "user=root&password=mysql&useUnicode=true&characterEncoding=UTF8";

3. Create a database connection

•To connect to the database, you need to request java.sql .DriverManager requests and obtains a Connection object, which represents a database connection.
•Use DriverManager’s getConnectin(String url, String username, String password) method to pass in the specified number to be connected

  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 To execute a SQL statement, you must obtain a java.sql.Statement instance. Statement instances are divided into the following three types:
1. Execute static SQL statements. Usually implemented through Statement instances.
2. Execute dynamic SQL statements. Usually implemented through a PreparedStatement instance.
3. Execute the database stored procedure. Usually implemented through a CallableStatement instance.

 Statement stmt = con.createStatement() ;  

    PreparedStatement pstmt = con.prepareStatement(sql) ; 

    CallableStatement cstmt =   con.prepareCall("{CALL demoSp(? , ?)}") ;

5. Execute SQL statements

The Statement interface provides three methods for executing SQL statements: executeQuery, executeUpdate and execute
1. ResultSet executeQuery(String sqlString): Execute the SQL statement that queries the database and return a result set (ResultSet) object.
2. int executeUpdate(String sqlString): used to execute INSERT, UPDATE or DELETE statements and SQL DDL statements, such as: CREATE TABLE and DROP TABLE, etc.
3. execute(sqlString): used to execute multiple returns Statements with result sets, multiple update counts, or a combination of both.

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

6. Processing results

Two situations:
1. Executing the update returns the number of records affected by this operation.
2. The result returned by executing the query is a ResultSet object.
• ResultSet contains all rows that match the conditions in the SQL statement, and it provides access to the data in these rows through a set of get methods.
• Use the access method of the ResultSet object to obtain data:

  while(rs.next()){   
         String name = rs.getString("name") ;   
         String pass = rs.getString(1) ; // 此方法比较高效   
  }   
    （列是从左到右编号的，并且从列1开始）

7. Close the JDBC object

After the operation is completed, all JDBC objects used must be closed to release For JDBC resources, the closing order is opposite to the declaration order:
1. Close the record set
2. Close the statement
3. Close the connection object

 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() ;   
    }   
 }

Complete program:

package org.Mysql;
import java.sql.Connection;import java.sql.DriverManager;
import java.sql.ResultSet;import java.sql.SQLException;
import java.sql.Statement;public class mysqlDemo {

    public static void main(String[] args) throws Exception {
        //使用connection进行数据库的连接，创建一个connection对象，用于数据库的连接
        Connection conn = null;
        String sql;
        // MySQL的JDBC URL编写方式：jdbc:mysql://主机名称(localhost)：连接端口(3306)/数据库的名称?参数=值
        // 指定数据库的用户名和密码
        // 避免中文乱码要指定useUnicode和characterEncoding
        // 执行数据库操作之前要在数据库管理系统上创建一个数据库，名字自己定
        // 下面语句之前就要先创建samp_db数据库
        String url = "jdbc:mysql://localhost:3306/samp_db?"
                + "user=root&password=mysql&useUnicode=true&characterEncoding=UTF8";

        try {
            // 之所以要使用下面这条语句，是因为要使用MySQL的驱动，所以我们要把它驱动起来，
            // 可以通过Class.forName把它加载进去，也可以通过初始化来驱动起来，下面三种形式都可以
            // 动态加载mysql驱动
            Class.forName("com.mysql.jdbc.Driver");
            // or:
            // com.mysql.jdbc.Driver driver = new com.mysql.jdbc.Driver();
            // or：
            // new com.mysql.jdbc.Driver();

            System.out.println("成功加载MySQL驱动程序");
            // 一个Connection代表一个数据库连接
            conn = DriverManager.getConnection(url);

            // Statement里面带有很多方法，比如executeUpdate可以实现插入，更新和删除等
            Statement stmt = conn.createStatement();
            sql = "create table student(NO char(20),name varchar(20),primary key(NO))";
            int result = stmt.executeUpdate(sql);// executeUpdate语句会返回一个受影响的行数，如果返回-1就没有成功
            if (result != -1) {
                System.out.println("创建数据表成功");
                sql = "insert into student(NO,name) values('2012001','陶伟基')";
                result = stmt.executeUpdate(sql);
                sql = "insert into student(NO,name) values('2012002','周小俊')";
                result = stmt.executeUpdate(sql);
                sql = "select * from student";
                ResultSet rs = stmt.executeQuery(sql);// executeQuery会返回结果的集合，否则返回空值
                System.out.println("学号\t姓名");
                while (rs.next()) {
                    System.out
                            .println(rs.getString(1) + "\t" + rs.getString(2));// 入如果返回的是int类型可以用getInt()
                }
            }
        } catch (SQLException e) {
            System.out.println("MySQL操作错误");
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            conn.close();
        }

    }

}

The above is the content of [MySQL 05] using Java to operate MySQL (create database). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!