java圣经sqlite3数据库

package b;

import java.awt.Color;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import javax.swing.JFrame;
import javax.swing.JMenu;
import javax.swing.JMenuBar;
import javax.swing.JMenuItem;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
import javax.swing.JTextPane;
import javax.swing.plaf.FontUIResource;
import javax.swing.text.Style;
import javax.swing.text.StyleConstants;
import javax.swing.text.StyledDocument;

public class Main extends JFrame implements ActionListener {
	ArrayList<String> als = select();
	String[] lei = { "律法书", "历史书", "诗歌·智慧书", "先知书", "四福音", "教会历史", "书信", "对约翰的启示" };
	JMenuBar jmb = new JMenuBar();
	JMenu[] jm = new JMenu[lei.length];
	JMenuItem[] jmi = new JMenuItem[als.size()];
	JTextPane jtp = new JTextPane();

	JScrollPane jsp = new JScrollPane(jtp);
	FontUIResource fuir = new FontUIResource("微软雅黑", Font.BOLD, 88);
	StyledDocument sd = jtp.getStyledDocument();
	Style sty = jtp.addStyle(null, null);

	public Main() {
		for (int i = 0; i < lei.length; i++) {
			jm[i] = new JMenu(lei[i]);
			jmb.add(jm[i]);
		}
		for (int i = 0; i < 5; i++) {
			jmi[i] = new JMenuItem(als.get(i));
			jmi[i].addActionListener(this);
			jmi[i].setBackground(Color.RED);
			jm[0].add(jmi[i]);
		}
		for (int i = 5; i < 17; i++) {
			jmi[i] = new JMenuItem(als.get(i));
			jmi[i].addActionListener(this);
			jmi[i].setBackground(Color.ORANGE);
			jm[1].add(jmi[i]);
		}
		for (int i = 17; i < 22; i++) {
			jmi[i] = new JMenuItem(als.get(i));
			jmi[i].addActionListener(this);
			jmi[i].setBackground(Color.YELLOW);
			jm[2].add(jmi[i]);
		}
		for (int i = 22; i < 39; i++) {
			jmi[i] = new JMenuItem(als.get(i));
			jmi[i].addActionListener(this);
			jmi[i].setBackground(new Color(146, 208, 80));
			jm[3].add(jmi[i]);
		}
		for (int i = 39; i < 43; i++) {
			jmi[i] = new JMenuItem(als.get(i));
			jmi[i].addActionListener(this);
			jmi[i].setBackground(new Color(0, 176, 80));
			jm[4].add(jmi[i]);
		}
		for (int i = 43; i < 44; i++) {
			jmi[i] = new JMenuItem(als.get(i));
			jmi[i].addActionListener(this);
			jmi[i].setBackground(new Color(0, 176, 240));
			jm[5].add(jmi[i]);
		}
		for (int i = 44; i < 65; i++) {
			jmi[i] = new JMenuItem(als.get(i));
			jmi[i].addActionListener(this);
			jmi[i].setBackground(new Color(0, 112, 192));
			jm[6].add(jmi[i]);
		}
		for (int i = 65; i < 66; i++) {
			jmi[i] = new JMenuItem(als.get(i));
			jmi[i].addActionListener(this);
			jmi[i].setBackground(Color.MAGENTA);
			jm[7].add(jmi[i]);
		}

		jtp.setEditable(false);
		jtp.setBackground(Color.BLACK);
		jtp.setFont(fuir);
		jtp.setText("请选择菜单中的卷,再输入要查询的章节数字,中间用空格隔开.例如查询创世记第1章第1节则先选择律法书菜单里面的创世记,接下来在弹出的提示框中输入1 1(注:1 1之间中间用空格隔开)\n");

		this.setJMenuBar(jmb);
		this.add(jsp);
		this.setSize(1000, 700);
		this.setTitle("圣经投屏");
	}

	public static void main(String[] args) {
		Main m = new Main();
		m.setExtendedState(MAXIMIZED_BOTH);
		m.setDefaultCloseOperation(EXIT_ON_CLOSE);
		m.setVisible(true);

	}

	public static ArrayList<String> select() {
		ArrayList<String> als = new ArrayList<String>();
		try {
			Class.forName("org.sqlite.JDBC");
			try (Connection conn = DriverManager.getConnection("jdbc:sqlite:hb.sqlite3")) {
				try (PreparedStatement ps = conn
						.prepareStatement("select distinct juan from t_hb order by rowid asc")) {
					try (ResultSet rs = ps.executeQuery()) {
						while (rs.next()) {
							als.add(rs.getString("juan"));
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return als;
	}

	public static ArrayList<HashMap<String, String>> select(String juan, String zhang, String jie) {
		ArrayList<HashMap<String, String>> alhmss = new ArrayList<HashMap<String, String>>();
		try {
			Class.forName("org.sqlite.JDBC");
			try (Connection conn = DriverManager.getConnection("jdbc:sqlite:hb.sqlite3")) {
				try (PreparedStatement ps = conn.prepareStatement(
						"select juan, zhang, jie, jingwen from t_hb where juan = ? and zhang = ? and jie between ? and 200")) {
					ps.setString(1, juan);
					ps.setString(2, zhang);
					ps.setString(3, jie);
					try (ResultSet rs = ps.executeQuery()) {
						while (rs.next()) {
							HashMap<String, String> hmss = new HashMap<String, String>();
							hmss.put("juan", rs.getString("juan"));
							hmss.put("zhang", rs.getString("zhang"));
							hmss.put("jie", rs.getString("jie"));
							hmss.put("jingwen", rs.getString("jingwen"));
							alhmss.add(hmss);
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return alhmss;
	}

	@Override
	public void actionPerformed(ActionEvent ae) {
		String s = ae.getActionCommand();
		String shuru = JOptionPane.showInputDialog("请输入要查询的章节(例如查找所在卷1章1节到最后1节则输入:1 1(中间用空格隔开，不用输入结束的章节)):", "1 1")
				.trim();
		if (shuru == null) {
			return;
		}
		if (shuru.equals("")) {
			return;
		}
		String[] zhangjie = shuru.split(" ");
		if (!zhangjie[0].trim().matches("^[0-9]*$") || !zhangjie[1].trim().matches("^[0-9]*$")) {
			return;
		}
		ArrayList<HashMap<String, String>> alhmss = this.select(s, zhangjie[0], zhangjie[1]);
		jtp.setText("");

		for (int i = 0; i < alhmss.size(); i++) {
			String zhang = alhmss.get(i).get("zhang");
			String jie = alhmss.get(i).get("jie");
			String jingwen = alhmss.get(i).get("jingwen");
			String zhangjiejingwen = zhang + ":" + jie + jingwen + "\n";
			if (i % 3 == 0) {
				StyleConstants.setForeground(sty, Color.CYAN);
			} else if (i % 3 == 1) {
				StyleConstants.setForeground(sty, Color.MAGENTA);
			} else if (i % 3 == 2) {
				StyleConstants.setForeground(sty, Color.YELLOW);
			} else {
				StyleConstants.setForeground(sty, Color.WHITE);
			}
			try {
				sd.insertString(sd.getLength(), zhangjiejingwen, sty);
			} catch (Exception e) {
				e.printStackTrace();
			}

			this.jtp.setCaretPosition(0);
		}

	}
}