MySQL Python 化

      在〈MySQL Python 化〉中尚無留言

底下代碼，可以將 Java Python 化，更方便、更直覺的操控 MySQL。請新增 mysql 目錄，將所有關於 mysql 的操作都被在 mysql 目錄中。

mysql.java

mysql.java 完整代碼如下

package net.ddns.mahaljsp.mysql;
import java.sql.*;

public final class mysql {
    public static PyConnection connect(
            String host,String user, String password, String database
    )throws SQLException{
        /*
        //Java web 才需加入
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
        }
        catch (ClassNotFoundException ex){
            System.out.println(ex);
        }
        */
        return new PyConnection(host, user, password, database);
    }
}

Column.java

Column.java 為欄位自訂類別。

package net.ddns.mahaljsp.mysql;
public final class Column {
    public String name;
    public int type;
    public Column(String name, int type){
        this.name=name;
        this.type=type;
    }    
}

PyConnection.java

PyConnection 為連線類別。

package net.ddns.mahaljsp.mysql;
import java.sql.*;
public final class PyConnection implements AutoCloseable{
    Connection conn;
    public PyConnection(
            String host, String user, String password, String database
    )throws SQLException{
        //characterEncoding=UTF-8 最好是寫上，因為有的 driver 預設不是 utf-8
        String connStr="jdbc:mysql://%s/%s?useSSL=true&serverTimezone=Asia/Taipei&characterEncoding=UTF-8"
                .formatted(host, database);
        conn=DriverManager.getConnection(connStr,user,password);
        conn.setAutoCommit(false);
    }
    public Cursor cursor()throws SQLException{
        return new Cursor(conn);
    }
    @Override
    public void close()throws SQLException{
        if (conn != null && !conn.isClosed()) {
            conn.close();
            conn = null;
        }
    }
    public void commit()throws SQLException{
        conn.commit();
    }
    public void rollback() throws SQLException {
        conn.rollback();
    }        
}

Cursor.java

Cursor.java 為命令類別。

package net.ddns.mahaljsp.mysql;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public final class Cursor implements AutoCloseable {
    private Connection conn;
    private ResultSet rs;   
    PreparedStatement pstmt;
    public Cursor(Connection conn){
        this.conn=conn;
    }
    public void execute(String cmd)throws SQLException{
        close(); // 關掉舊 rs
        pstmt = conn.prepareStatement(cmd);
        if (pstmt.execute()) {
            rs = pstmt.getResultSet();
        } else {
            rs = null;
        }
    }
    public void executemany(String cmd, List<List<Object>>datas)throws SQLException{
        try(var ps = conn.prepareStatement(cmd)){
            int count = ps.getParameterMetaData().getParameterCount();
            for(var row:datas){
                if (row.size()!=count)throw new SQLException("Parameter count mismatch");
                for(int i=0;i<count;i++){
                    ps.setObject(i+1, row.get(i));
                }
                ps.addBatch();
            }
            ps.executeBatch();
            ps.clearBatch();
        }
    }
    public List<List<Object>> fetchall()throws SQLException{
        if (rs == null) {
            throw new SQLException("No ResultSet to fetch");
        }
        try{
            var meta=rs.getMetaData();
            int count=meta.getColumnCount();
            List<List<Object>> datas=new ArrayList<>();
            while(rs.next()){
                List<Object> item=new ArrayList<>();
                for (int i=1;i<=count;i++){
                    item.add(rs.getObject(i));
                }
                datas.add(item);
            }
            return datas;
        } finally {
            rs.close();
            rs = null;
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }            
        }        
    }    
    public List<String> getColumns(String table)throws SQLException{
        try (PreparedStatement ps =
                conn.prepareStatement("select * from %s limit 0, 1".formatted(table));
                ResultSet r = ps.executeQuery()) {
            //pstmt=conn.prepareStatement("select * from %s limit 0, 1".formatted(table));
            ResultSetMetaData meta=r.getMetaData();
            List<String> data=new ArrayList<>();
            for(int i=1;i<=meta.getColumnCount();i++){
                data.add(meta.getColumnLabel(i));
            }
            return data;
        }
    }    
    public List<Column> getColumnsDetail(String table)throws SQLException{
        try (PreparedStatement ps =
                conn.prepareStatement("select * from %s limit 0, 1".formatted(table));
                ResultSet r = ps.executeQuery()) {
            ResultSetMetaData meta=r.getMetaData();
            List<Column> data=new ArrayList<>();
            for(int i=1;i<=meta.getColumnCount();i++){
                data.add(new Column(meta.getColumnLabel(i), meta.getColumnType(i)));
            }
            return data;            
        }
    }

    public String getDDL(String table)throws SQLException{
        if (!table.matches("[A-Za-z0-9_\\u4e00-\\u9fa5]+")) {
            throw new SQLException("Invalid table name");
        }        
        this.execute("show create table `%s`".formatted(table));        
        var rows = fetchall();
        if (rows.isEmpty() || rows.get(0).size() < 2) {
            throw new SQLException("Failed to get DDL for table " + table);
        }
        String ddl=rows.get(0).get(1).toString();
        ddl = ddl.replaceAll(
                //i : ignore case，大小寫不分（engine、ENGINE 都吃）
                //s : dotall，讓 . 可以匹配 換行字元
                //.* : 後面所有的字
                "(?is)ENGINE=.*",
                "ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        );
        return ddl;
    }
    @Override
    public void close()throws SQLException{
        if (rs != null) {
            rs.close();
            rs = null;
        }
        if(pstmt !=null){
            pstmt.close();
            pstmt=null;
        }
    }    
}

Select 應用

Select 的應用。

package filepath;

import net.ddns.mahaljsp.mysql.mysql;
import net.ddns.mahaljsp.os;
import java.sql.*;

public class Select {
    public static void main(String[] args)throws SQLException {
        String table="台灣股市";
        os.utf8();
        try(var conn=mysql.connect("mahaljsp.ddns.net", G.user,G.password, "cloud");
            var cursor=conn.cursor();){
            cursor.execute("select * from %s".formatted(table));
            var rs=cursor.fetchall();
            for(var r:rs){
                System.out.println(r);
            }
        }
    }
}

欄位取得

底下可以取得任何資料表的欄位 List。

package filepath;
import java.sql.*;
import net.ddns.mahaljsp.mysql.mysql;
import net.ddns.mahaljsp.os;
public class MysqlTest {
    public static void main(String[] args) {
        os.utf8();
        try (var conn=mysql.connect(G.host, G.user, G.password, "django");
            var cursor=conn.cursor();){
            String cmd="select * from history";
            cursor.execute(cmd);
            var columns=cursor.getColumns("history");
            System.out.println(columns);
        } catch (SQLException ex) {
            System.out.println(ex);
            //ex.printStackTrace();
        }
    }
}


結果:
[id, ip, eventDay, eventTime, page, userAccount, country, city, lng, lat]

Copy 資料表

底下可以讀取資料表 DDL 及內容，再於另一台建立資料表及 copy 資料表內容。

package filepath;

import net.ddns.mahaljsp.mysql.mysql;
import java.sql.*;
import java.util.List;
import net.ddns.mahaljsp.os;

public class CopyTable {
    public static void main(String[] args) {
        os.utf8();
        String table="台灣股市";
        int count=0;
        String ddl=null;
        String columns=null;
        List<List<Object>> rs=null;
        try(var conn=mysql.connect(G.host, G.user, G.password, "cloud");
            var cursor=conn.cursor();){
            
            //取得欄位，不包含 id
            List<String> tmp=cursor.getColumns(table);
            tmp.remove(0);
            count=tmp.size();
            String s=tmp.toString();
            columns=s.substring(1, s.length()-1);
            
            ddl=cursor.getDDL(table);
            
            //取得原始資料
            String cmd="select %s from %s".formatted(columns, table);
            cursor.execute(cmd);
            rs=cursor.fetchall();
        }
        catch(SQLException e){}
        if(rs!=null){
            //寫入新的資料表
            try(var conn=mysql.connect("localhost", G.user, G.password, "cloud");
                var cursor=conn.cursor();){
                cursor.execute("drop table if exists %s".formatted(table));            
                cursor.execute(ddl);
                //cursor.execute("truncate table %s".formatted(table));
                var cmd="insert into %s (%s) values (%s)"
                        .formatted(
                                table, 
                                columns, 
                                //"?,".repeat(count).substring(0,count*2-1)
                                //底下把字串集合(List、Set 等)用分隔符連起來。
                                String.join(",", Collections.nCopies(count, "?"))
                        );
                cursor.executemany(cmd, rs);
                conn.commit();
            }
            catch(SQLException e){
                System.out.println(e);
            }
        }
    }
}

DDL

底下可以讀取任一資料表的 DDL。

package filepath;

import net.ddns.mahaljsp.mysql.mysql;
import java.sql.*;
import net.ddns.mahaljsp.os;
public class DDL {
    public static void main(String[] args) throws SQLException{
        String table="台灣股市";
        os.utf8();
        try(var conn=mysql.connect("mahaljsp.ddns.net", G.user,G.password, "cloud");
            var cursor=conn.cursor();){
            String ddl=cursor.getDDL(table);
            System.out.println(ddl);
            conn.close();
            /*
            conn=mysql.connect("localhost", "thomas","Mahal$0507", "cloud");
            cursor=conn.cursor();
            cursor.execute("drop table if exists %s".formatted(table));
            cursor.execute(ddl);
            conn.close();
            */  
        }
    }
}

結果 :
CREATE TABLE `台灣股市` (
  `id` int NOT NULL AUTO_INCREMENT,
  `代號` varchar(10) COLLATE utf8mb3_unicode_ci NOT NULL,
  `日期` date NOT NULL,
  `開盤` double NOT NULL,
  `最高` double NOT NULL,
  `最低` double NOT NULL,
  `收盤` double NOT NULL,
  `成交量` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

舊版代碼

請先新增 mysql.java，輸入如下完整代碼

package net.ddns.mahaljsp;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.ArrayList;
import java.sql.*;
import java.util.List;

public final class mysql {
    public static PyConnection connect(
            String host,String user, String password, String database
    )throws SQLException{
        /*
        //Java web 才需加入
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
        }
        catch (ClassNotFoundException ex){
            System.out.println(ex);
        }
        */
        return new PyConnection(host, user, password, database);
    }
    public final static class PyConnection{
        Connection conn;
        private PyConnection(
                String host, String user, String password, String database
        )throws SQLException{
            String connStr="jdbc:mysql://%s/%s?useSSL=true&serverTimezone=Asia/Taipei"
                    .formatted(host, database);
            conn=DriverManager.getConnection(connStr,user,password);
            conn.setAutoCommit(false);
        }
        public Cursor cursor()throws SQLException{
            return new Cursor(conn);
        }
        public void close()throws SQLException{
            conn.close();
        }
        public void commit()throws SQLException{
            conn.commit();
        }
        public void rollback() throws SQLException {
            conn.rollback();
        }        
    }
    public final static class Cursor{
        Connection conn;
        private ResultSet rs;   
        private PreparedStatement ps;
        private Cursor(Connection conn){
            this.conn=conn;
        }
        public void execute(String cmd)throws SQLException{
            rs=null;
            ps = conn.prepareStatement(cmd);
            if (ps.execute())rs=ps.getResultSet();
        }
        public void close()throws SQLException{
            if (rs!=null)rs.close();
        }
        public void executemany(String cmd, List<List<Object>>datas)throws SQLException{
            ps = conn.prepareStatement(cmd);
            int count = ps.getParameterMetaData().getParameterCount();
            for(var row:datas){
                if (row.size()!=count)throw new SQLException("Parameter count mismatch");
                for(int i=0;i<count;i++){
                    ps.setObject(i+1, row.get(i));
                }
                ps.addBatch();
            }
            ps.executeBatch();
            ps.clearBatch();
        }
        public Map<String, Integer> columns()throws SQLException{
            ResultSetMetaData meta=rs.getMetaData();
            Map<String, Integer> datas=new LinkedHashMap<>();
            for(int i=1;i<=meta.getColumnCount();i++){
                datas.put(meta.getColumnLabel(i), meta.getColumnType(i));
            }
            return datas;
        }
        public List<List<Object>> fetchall()throws SQLException{
            var meta=rs.getMetaData();
            int count=meta.getColumnCount();
            List<List<Object>> datas=new ArrayList<>();
            while(rs.next()){
                List<Object> item=new ArrayList<>();
                for (int i=1;i<=count;i++){
                    item.add(rs.getObject(i));
                }
                datas.add(item);
            }
            rs=null;
            return datas;            
        }
    }     
}

查詢應用

主程式引用上面的 mysql，即可進行查詢

import java.sql.*;
import net.ddns.mahaljsp.mysql;
import net.ddns.mahaljsp.os;
public class MysqlTest {
    public static void main(String[] args) {
        os.utf8();
        var host="host";
        var user="帳號";
        var password="密碼";
        var database="資料庫";        
        try {
            var conn=mysql.connect(host, user, password, database);
            String cmd="select * from history";
            var cursor=conn.cursor();
            cursor.execute(cmd);
            var columns=cursor.columns();
            var rs=cursor.fetchall();
            conn.close();
            
            for(var r :rs){
                System.out.println(r);
            }
            for (var c : columns.keySet()){
                String type;
                switch(columns.get(c)){
                    case Types.INTEGER->type="Integer";
                    case Types.VARCHAR->type="String";
                    case Types.TIMESTAMP->type="Timestamp";
                    case Types.DATE->type="Date";
                    case Types.TIME->type="time";                    
                    case Types.DOUBLE->type="Double";
                    default->type="Other";
                }
                System.out.printf("%s : %d, %s\n",c,columns.get(c), type);
            }            
            
        } catch (SQLException ex) {
            System.out.println(ex);
        }
    }
}

新增應用

主程式引用上面的 mysql，即可快速新增多筆資料。

public static void main(String[] args) {
	try{
		var conn=mysql.connect("mahaljsp.ddns.net", "帳號", "密碼", "cloud");
		var cursor=conn.cursor();
		cursor.execute("select 日期, 買進, 賣出 from 台銀黃金");
		var rs=cursor.fetchall();
		for(var r:rs){
			System.out.println(r);
		}
		conn.close();
		
		conn=mysql.connect("localhost", "帳號", "密碼", "cloud");
		cursor=conn.cursor();
		cursor.execute("truncate table 台銀黃金");
		String cmd="insert into 台銀黃金 (日期, 買進, 賣出) values (?,?,?)";
		cursor.executemany(cmd, rs);
		conn.commit();
		conn.close();
	}
	catch(SQLException e){}
}

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *