底下代碼，可以將 Java Python 化，更方便、更直覺的操控 MySQL。

完整代碼

請先新增 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){}
}