20241121(四)
:::success
[TOC]
:::
<span style=color:orange>本文可能存在偏誤或不確定的部分為此色</span>

---
### <p style="color: #00CCDD;">Table、Model</p>
#### <p style="color: #DA8359;">屬性、介面、方法</p>
javax.swing.JTable extends JComponent

#### <p style="color: #DA8359;">視窗頁面</p>

import java.awt.BorderLayout;
import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;

import tw.Issac.apis.FoodTable;

public class MyFood extends JFrame{
	// 宣告將使用到的物件
	private FoodTable foodTable;
	private JButton del;
	
	public MyFood() {
		// 視窗介面
		setLayout(new BorderLayout());
		foodTable = new FoodTable();
		JScrollPane jsp = new JScrollPane(foodTable);
		add(jsp, BorderLayout.CENTER);
		
		// 按鈕介面
		JPanel top = new JPanel(new FlowLayout());
		del = new JButton("Delete");
		top.add(del);
		add(top, BorderLayout.NORTH);
		
		// 視窗規格
		setSize(640, 480);
		setVisible(true);
		setDefaultCloseOperation(EXIT_ON_CLOSE);
		
		initEvent();
	}
	
	// 程式執行指令
	private void initEvent() {
		del.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent e) {
				foodTable.delRow();
			}
		});
	}
	
	// 程式進入點，new出本程式物件給其他兩隻使用
	public static void main(String[] args) {
		new MyFood();
	}
}

---

#### <p style="color: #DA8359;">自訂類別api</p>

import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

public class FoodTable extends JTable{
	private FoodModel model;
	private FoodDB db;
	
	public FoodTable() {
		try {
			db = new FoodDB();
			db.query();
		}catch(Exception e) {
			System.out.println(e);
		}
		model = new FoodModel();
		setModel(model);
		model.setColumnIdentifiers(db.getFieldNames());
	}
	
	public void delRow() {
//		System.out.println(getSelectedRow());	
		//確認一下刪掉的是哪一列，可以看到這裡是從0匴起，而DB是從1算起，需要特別注意
		db.delData(getSelectedRow());
		repaint();
	}
	
	private class FoodModel extends DefaultTableModel{
		@Override
		public int getRowCount() {
			return db.getRows();
		}
		@Override
		public int getColumnCount() {
			return db.getCols();
		}
		@Override
		public Object getValueAt(int row, int column) {
			return db.getData(row, column);
		}
		@Override
		public boolean isCellEditable(int row, int column) {
			return column != 0;
		}
		@Override
		public void setValueAt(Object aValue, int row, int column) {
			db.updateData((String)aValue, row, column);
		}
	}
}

---

#### <p style="color: #DA8359;">串接DB與資料呈現</p>
使用的資料是前一個筆記中導入的[opendata](https://hackmd.io/V0wW2wTlSDyYH2EqXN-HHA#opendata-%E5%B0%8E%E5%85%A5-database)

import java.sql.*;
import java.util.Properties;

public class FoodDB {
	private final static String URL = "jdbc:mysql://localhost/issac";
	private final static String USER = "root";
	private final static String PASSWORD = "";
	private final static String SQL_QUERY = "SELECT id 編號, name 商家名稱, addr 地址, tel 電話 from food";

	private Connection conn;
	private ResultSet rs;
	private String[] fieldNames;

	public FoodDB() throws Exception {
		Properties prop = new Properties();
		prop.put("user", USER);
		prop.put("password", PASSWORD);
		conn = DriverManager.getConnection(URL, prop);
	}

	public void query() throws Exception {
		query(SQL_QUERY);
	}

	public void query(String sql) throws Exception {
		Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
		rs = stmt.executeQuery(sql);

		ResultSetMetaData rsmd = rs.getMetaData();
		fieldNames = new String[rsmd.getColumnCount()];
		for (int i = 0; i < fieldNames.length; i++) {
			fieldNames[i] = rsmd.getColumnLabel(i + 1);
		}
	}

	public int getRows() {
		try {
			rs.last();
			return rs.getRow();
		} catch (Exception e) {
			return 0;
		}
	}

	public int getCols() {
		return fieldNames.length;
	}

	// row, col => 0-base
	public String getData(int row, int col) {
		try {
			rs.absolute(row + 1);
			return rs.getString(fieldNames[col]);
		} catch (Exception e) {
			return "ERROR";
		}
	}

	public String[] getFieldNames() {
		return fieldNames;
	}

	// 0-base
	public void delData(int row) {
		try {
			rs.absolute(row + 1);
			rs.deleteRow();
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	public void updateData(String newData, int row, int col) {
		try {
			rs.absolute(row + 1);
			rs.updateString(col + 1, newData);
			rs.updateRow();
		} catch (Exception e) {
			System.out.println(e);
		}
	}
}