jdbc 創建、刪除表范例

在本教程將演示如何在 jdbc 應用程序中創建 和 刪除表。

刪除表后，表中的所有內容都將丟失，所以必須在繼續刪除表之前作出明確的決定。

1. 創建表范例

復制以下示例代碼保存到文件：createtable.java中，然后編譯并運行如下:

//step 1. import required packages

import java.sql.*;

public class createtable {
    // jdbc driver name and database url
    static final string jdbc_driver = "com.mysql.jdbc.driver";  
    static final string db_url = "jdbc:mysql://localhost/jdbc_db";

    //  database credentials
    static final string user = "root";
    static final string pass = "123456";

    public static void main(string[] args) {
        connection conn = null;
        statement stmt = null;
        try{
            //step 2: register jdbc driver
            class.forname("com.mysql.jdbc.driver");

            //step 3: open a connection
            system.out.println("connecting to a selected database...");
            conn = drivermanager.getconnection(db_url, user, pass);
            system.out.println("connected database successfully...");

            //step 4: execute a query
            system.out.println("creating table in given database...");
            stmt = conn.createstatement();

            string sql = "create table student " +
                        "(id integer not null, " +
                        " first varchar(255), " + 
                        " last varchar(255), " + 
                        " age integer, " + 
                        " primary key ( id ))"; 

            stmt.executeupdate(sql);
            system.out.println("created table in given database...");
        }catch(sqlexception se){
            //handle errors for jdbc
            se.printstacktrace();
        }catch(exception e){
            //handle errors for class.forname
            e.printstacktrace();
        }finally{
            //finally block used to close resources
            try{
                if(stmt!=null)
                conn.close();
            }catch(sqlexception se){
            }// do nothing
            try{
                if(conn!=null)
                conn.close();
            }catch(sqlexception se){
                se.printstacktrace();
            }//end finally try
        }//end try
        system.out.println("goodbye!");
    }//end main
}   

編譯上面代碼，如下 -

f:\worksp\jdbc> javac -djava.ext.dirs=f:\worksp\jdbc\libs createtable.java

執行上面代碼，如下 -

f:\worksp\jdbc>java -djava.ext.dirs=f:\worksp\jdbc\libs createtable
connecting to a selected database...
thu jun 01 22:17:34 cst 2017 warn: establishing ssl connection without server's identity verification is not recommended. according to mysql 5.5.45+, 5.6.26+ and 5.7.6+ requirements ssl connection must be established by default if explicit option isn't set. for compliance with existing applications not using ssl the verifyservercertificate property is set to 'false'. you need either to explicitly disable ssl by setting usessl=false, or set usessl=true and provide truststore for server certificate verification.
connected database successfully...
creating table in given database...
created table in given database...
goodbye!

2. 刪除表范例

復制以下示例代碼保存到文件：droptable.java中，然后編譯并運行如下:

//step 1. import required packages
import java.sql.*;

public class droptable {
    // jdbc driver name and database url
    static final string jdbc_driver = "com.mysql.jdbc.driver";  
    static final string db_url = "jdbc:mysql://localhost/jdbc_db";

    //  database credentials
    static final string user = "root";
    static final string pass = "123456";

    public static void main(string[] args) {
        connection conn = null;
        statement stmt = null;
        try{
            //step 2: register jdbc driver
            class.forname("com.mysql.jdbc.driver");

            //step 3: open a connection
            system.out.println("connecting to a selected database...");
            conn = drivermanager.getconnection(db_url, user, pass);
            system.out.println("connected database successfully...");

            //step 4: execute a query
            system.out.println("deleting table in given database...");
            stmt = conn.createstatement();

            string sql = "drop table student ";

            stmt.executeupdate(sql);
            system.out.println("table  deleted in given database...");
        }catch(sqlexception se){
            //handle errors for jdbc
            se.printstacktrace();
        }catch(exception e){
            //handle errors for class.forname
            e.printstacktrace();
        }finally{
            //finally block used to close resources
            try{
                if(stmt!=null)
                conn.close();
            }catch(sqlexception se){
            }// do nothing
            try{
                if(conn!=null)
                conn.close();
            }catch(sqlexception se){
                se.printstacktrace();
            }//end finally try
        }//end try
        system.out.println("goodbye!");
    }//end main
}   

編譯上面代碼，如下 -

f:\worksp\jdbc> javac -djava.ext.dirs=f:\worksp\jdbc\libs droptable.java

執行上面代碼，如下 -

f:\worksp\jdbc>java -djava.ext.dirs=f:\worksp\jdbc\libs droptable

connecting to a selected database...
thu jun 01 22:39:01 cst 2017 warn: establishing ssl connection without server's identity verification is not recommended. according to mysql 5.5.45+, 5.6.26+ and 5.7.6+ requirements ssl connection must be established by default if explicit option isn't set. for compliance with existing applications not using ssl the verifyservercertificate property is set to 'false'. you need either to explicitly disable ssl by setting usessl=false, or set usessl=true and provide truststore for server certificate verification.
connected database successfully...
deleting table in given database...
table  deleted in given database...
goodbye!

在執行上面語句后，數據庫 jdbc_db 中的 student 表將會被成功地刪除了。