利用 JDBC 撈取資料庫內容

利用 JDBC 撈取資料庫的方式：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionDemo {
    public static void main(String[] args)
                  throws ClassNotFoundException{
        
        Class.forName("com.mysql.jdbc.Driver");
        
        String url = "jdbc:mysql://localhost:3306/directory"; 
        String dbaccount = "directory";
        String dbpassword = "a123456";
        
        try(Connection conn = DriverManager.getConnection(url,
                 dbaccount, dbpassword);
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql)){
                          
          //列出資料表欄位資料
            int numCols = rs.getMetaData().getColumnCount();
            String[] colsName = new String[numCols];
            String[] colsType = new String[numCols];
            for (int i = 0; i < numCols; i++){
                colsName[i] = rs.getMetaData().getColumnName(i+1);
                colsType[i] = rs.getMetaData().getColumnTypeName(i+1);
            }
            System.out.println("Numbers of columns returned:  "
                               + numCols);
            System.out.println("Column names/types returned: ");
             for (int i = 0; i < numCols; i++){
                 System.out.println( colsName[i] + " : " + colsType[i]);
             }
             
             //列出資料表所裝填的內容
            System.out.println(colsName[0] +"\t\t" + colsName[1] +
                              "\t" +colsName[2]);
            while (rs.next()){
               String rsID = rs.getString(colsName[0]);
               String rsUserName = rs.getString(colsName[1]);
               String rsEmail = rs.getString(colsName[2]);
               System.out.println(rsID + "\t\t" + rsUserName +
                                                "\t\t"+rsEmail);

        } catch (SQLException ex) {
           System.out.println("資料庫連結失敗....");
        }
    }
}

JDBC 連結資料庫

利用 JDBC 與資料庫連結：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionDemo {
    public static void main(String[] args)
                  throws ClassNotFoundException{
        
        Class.forName("com.mysql.jdbc.Driver");
        
        String url = "jdbc:mysql://localhost:3306/directory"; 
        String dbaccount = "directory";
        String dbpassword = "a123456";
        
        try(Connection conn = DriverManager.getConnection(url,
                 dbaccount, dbpassword)){
           
            if (!conn.isClosed()){
                System.out.println("資料庫連結成功....");
            }
            
        } catch (SQLException ex) {
           System.out.println("資料庫連結失敗....");
        }
    }
}

MySQL 的範例資料庫 directory，可以利用下列語法建立起來：

CREATE DATABASE  IF NOT EXISTS `directory`;
USE `directory`;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(100) NOT NULL,
  `userEmail` varchar(100) NOT NULL,
  PRIMARY KEY (`no`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES (1,'test','test@localhost'),
       (2,'hello','hello@localhost.domain'),(3,'world','world@test.123');
UNLOCK TABLES;