LoginSignup
1
0

More than 1 year has passed since last update.

@Mobu_Kyoto

JavaでSQLite3に接続し、データの一覧を表示する

Posted at

　新たなPCにVisualStudio2022をインストールし、それでSQLite3を扱おうと思いました。しかし良く解らず、結局またJavaに戻ってやってみました。以前作ったものなのでどこのサイトを参考にしたのか覚えていません。悪しからず。「DB Browser(SQLCipher)」など便利なものがあるので、自作する必要があるかは疑問ですが。
　また再度SQLite3をインストールしEclipseから使えるようにするために、このサイトが役に立ちました。
https://itsakura.com/java-sqlite-select

１．初期画面です。Select DB Fileボタンでファイルチューザーを呼び出し選びます。
sqlite00.jpg
２．データベースを開くとテーブルの一覧が現れるので、テーブルのどれかを選びます。
sqlite01.jpg
３．それでデータ構造も見られます。ここでは何も選ぶ必要はなく、データを読込みます。
sqlite03.jpg
sqlite04.jpg
４．これ以上の機能は実装していないので、引き続き他のデータベースを選んでもエラーを起こします。一度終了して再度立ち上げ、他のデータベースを読込んでみます。
sqlite06.jpg
違うデータベースでも、テーブル内のフィールドの順番・構造が同じであれば表示できます。手抜きですね。
　以下がソースです。

SQLite00.java
import java.awt.Color;
import java.awt.Component;
import java.awt.Container;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import javax.swing.AbstractAction;
import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.border.BevelBorder;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.DefaultTableColumnModel;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumn;

public class SQLite00 extends JFrame{
	private static final long serialVersionUID = 1L;
	SQLite00 MainFrame;
	public static void main(String[] args) {
		JFrame MainFrame=new SQLite00("Target DataBase = ?");
		MainFrame.setBounds(4, 0, 1200, 766);
		MainFrame.setDefaultCloseOperation(EXIT_ON_CLOSE);	
		MainFrame.setVisible(true);
	}
	
	private	int[] colWP= {24,64,90,50,60,24};//6 Data in PRAGMA info 
	private int[] colWD= {54,54,250,70,90,90,90,250,100,100};
	
	static String DBName= "";
	final static String preamble="jdbc:sqlite:";
	static String URL   = "";
	String TargetDB="";
	final String sqlTableList = "select name from sqlite_master where type='table'";
	String TargetDBTable= "";
	String sqlP="";         
	
	JPanel GP;
	JLabel lbl0,lbl1;
	JTable TableListTable,PragmaTable, DataTable;
	DefaultTableModel ListTableModel,PragmaTableModel;
	SortableTableModel	  DataTableModel;
	DefaultTableColumnModel CmodelP,DataColumnModel;
	String[] PragmaColumnNames= {"cid","name","type","notnull","dflt_value","pk"};		
	
	String[] DataColumnNames= 	{"RowNo.","1","2","3","4","5","6","7"};
	String[] ColumClassType= {"","1","2","3","4","5","6","7"};
	JScrollPane spL,spP,spD;
	JButton btnSelectDB,btnSelectTable,btnDataReadIn;
	TableColumn column = null;

	SQLite00(String title){
		super(title);
		MainFrame= this;
		btnSelectDB = new JButton("Select DB File");
		btnSelectDB.addActionListener(new ActionListenerDBSelect(DBName));
		lbl0= new JLabel("URL=");
		lbl1= new JLabel();
		lbl1.setOpaque(true);
		lbl1.setBackground(Color.white);
		
		Container GP=new JPanel();
		GP=MainFrame.getContentPane();
		GP.setLayout(null);
		
		spP=new JScrollPane(PragmaTable);

		ListTableModel = new DefaultTableModel();
		ListTableModel.addColumn("Tables in the DataBase");
		TableListTable = new JTable(ListTableModel);
		spL = new JScrollPane(TableListTable);
		
		btnSelectTable= new JButton("テーブルを選択");
		btnSelectTable.addActionListener(new ActionListenerTableSelect());
		
		PragmaTable = new JTable();
		PragmaTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
		PragmaTableModel = new DefaultTableModel();
		PragmaTable.setModel(PragmaTableModel);
		for(int i=0;i<6;i++) PragmaTableModel.addColumn(PragmaColumnNames[i]);
		spP=new JScrollPane(PragmaTable);
		btnDataReadIn= new JButton("データ読み込み	");
		btnDataReadIn.addActionListener(new ActionListenerDataReadIn());
		
		DataTable= new JTable();
		DataTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
		DataTable.getTableHeader().setReorderingAllowed(false);//prohibit column exchanging
		spD=new JScrollPane(DataTable);
		spD.setBorder(new BevelBorder(BevelBorder.LOWERED, Color.white, Color.black));
		
		GP.add(btnSelectDB);
		GP.add(lbl0);
		GP.add(lbl1);
		GP.add(spL);
		GP.add(btnSelectTable);
		GP.add(btnDataReadIn);
		GP.add(spP);
		GP.add(spD);
		btnSelectDB.setBounds(2, 10, 120, 26);
		lbl0.setBounds(140, 10, 30, 26);
		lbl1.setBounds(170, 10, 450, 26);
		spL.setBounds(2, 100, 200, 120);
		btnSelectTable.setBounds(20, 230, 150, 32);
		spP.setBounds(2, 290,200, 151);
		btnDataReadIn.setBounds(20,450,150,32);
		spD.setBounds(270, 50, 900, 660);
	}
	
//------------------------------------------------
	class ActionListenerDBSelect extends AbstractAction{
		private static final long serialVersionUID = 1L;
		ActionListenerDBSelect(String text){super(text);}
		@Override
		public void actionPerformed(ActionEvent ae) {
			File directory = null;
			JFileChooser DBChooser= new JFileChooser(directory);
            DBChooser.setFileSelectionMode(JFileChooser.FILES_ONLY);	
			DBChooser.setFileFilter(new DBFilter());
			int ret=DBChooser.showOpenDialog(MainFrame);
			File file=DBChooser.getSelectedFile();
			if(ret != JFileChooser.APPROVE_OPTION || file==null) return;
			DBName=file.toString();
			TargetDB=file.getPath();
			URL=preamble+TargetDB;
			lbl1.setText(URL);
			MainFrame.setTitle("Taget DataBase = "+TargetDB);
			
			try {
				Class.forName("org.sqlite.JDBC");
			}catch(Exception er) {
				System.out.println("org.sqlite.JDBC"+" was missed");			
			}
			try {
				Connection con = DriverManager.getConnection(URL);
				Statement stmt = con.createStatement();
				ResultSet rs = stmt.executeQuery(sqlTableList);
				while (rs.next()) {
					Object str=rs.getString(1);
					if(str.equals("sqlite_sequence")) {
						System.out.println(str+" was rejected");			
					}else{ 
						ListTableModel.addRow(new Object[] {str});
						System.out.println(str+" was received");			
					}
				}
				stmt.close();
				con.close();
			} catch (Exception e) {
				System.out.println(sqlTableList+" failed");
			}
		}
	}
		
	private class ActionListenerTableSelect implements ActionListener {
		@Override
		public void actionPerformed(ActionEvent ae) {
			int row=-1;
			row=TableListTable.getSelectedRow();
			if(row<0) {
				 JOptionPane.showMessageDialog(TableListTable,"Table not selected");
				return;				
			}
			TargetDBTable=TableListTable.getValueAt(row,0).toString();
			if(TargetDBTable=="")return;
			sqlP= "PRAGMA table_info('"+ TargetDBTable +"')";//important
			try {
				Connection con = DriverManager.getConnection(URL);
				Statement stmt = con.createStatement();
				ResultSet rs = stmt.executeQuery(sqlP);
				while (rs.next()) {
					PragmaTableModel.addRow(new Object[] {
									rs.getString(1),rs.getString(2),rs.getString(3),
									rs.getString(4),rs.getString(5),rs.getString(6)});
				}
				stmt.close();
				con.close();
				CmodelP = (DefaultTableColumnModel) PragmaTable.getColumnModel();
				for (int i = 0 ; i < PragmaTable.getColumnCount() ; i++){
					column = CmodelP.getColumn(i);
					column.setMaxWidth(colWP[i]);
					column.setMinWidth(colWP[i]);
					column.setPreferredWidth(colWP[i]);
				}
				PragmaTable.setModel(PragmaTableModel);
			} catch (Exception e) {
				System.out.println(sqlP+" failed");
			}
		}
	}
	
	private class ActionListenerDataReadIn implements ActionListener {
		@Override
		public void actionPerformed(ActionEvent ae) {
			if(PragmaTable.getRowCount()==0){
				JOptionPane.showMessageDialog(PragmaTable,"Data not exist"); return;}
	       
			Vector<String> names = new Vector<>();
			Vector<String> types = new Vector<>();
			names.add(" ");
			types.add(" ");
			for(int i=0; i<PragmaTable.getRowCount();i++) {
				names.add(PragmaTable.getValueAt(i, 1).toString());
				types.add(PragmaTable.getValueAt(i, 2).toString());
			}
			
			DataTableModel= new SortableTableModel(DataTable, names, 0, colWD,types);
			try {
				Connection con = DriverManager.getConnection(URL);
				Statement stmt = con.createStatement();
				String sql0 = "select * from '"+ TargetDBTable + "'";
				ResultSet rs = stmt.executeQuery(sql0);
				while (rs.next()) {
			        Vector<Object> vec = new Vector<>();				
					vec.add(" ");
					for( int i=1; i<=PragmaTable.getRowCount();i++) {
						if(types.elementAt(i).equals("INTEGER")) vec.add(rs.getInt(i));
						else 							  vec.add(rs.getString(i));
					}
					DataTableModel.addRow(vec);
				}
				stmt.close();
				con.close();
				
				DataTable.setModel(DataTableModel);
				DataColumnModel = (DefaultTableColumnModel)DataTable.getColumnModel();
				column = null;
				for (int i = 0 ; i < DataColumnModel.getColumnCount() ; i++){
					column = DataColumnModel.getColumn(i);
					column.setPreferredWidth(colWD[i]);
					if(i==0) {
						column.setCellRenderer(new Column0Renderer());
					}else if(types.elementAt(i)=="INTEGER") 
						column.setCellRenderer(new IntegerRenderer());
				}		
			} catch (Exception e) {
				System.out.println("DataTable err "+e.getMessage());
			}
		}
	}
	
	class TableModel1 extends DefaultTableModel{ // correctly soting in class
		private static final long serialVersionUID = 1L;
		TableModel1(Vector<String> names, int rowNum)
		{
			super(names, rowNum);
		}
		
		@SuppressWarnings({ "unchecked", "rawtypes" })
		public Class getColumnClass(int col) {
			if(getValueAt(0, col)==null)return null;
			return getValueAt(0, col).getClass(); // First row decides class
		}	
	}
	
	class ColumnFloatRenderer extends DefaultTableCellRenderer {
		private static final long serialVersionUID = 1L;
		public Component getTableCellRendererComponent(
				JTable table,Object value,boolean isSelected,boolean hasFocus,int row,int column)
		{
			String str=value.toString();
			if(str =="100") {
				//do nothing
			}else if(str.length()==5) {
				str=str+"0";
			}else if(str.length()==4) {
				str=str+"00";
			}else if(str.length()==3) {
				str=str+".000";
			}
			setValue(str);
			setHorizontalAlignment(RIGHT);
			return (this);
		}
	}
	
	class IntegerRenderer extends DefaultTableCellRenderer {
		private static final long serialVersionUID = 1L;
		public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected,
				boolean hasFocus, int row, int column) 
		{
			if(value == null) {	setText(""); return(this);}
			setValue(value);
			setHorizontalAlignment(RIGHT);
			return this;
		}
	}
}
SortableTableModel.java
import java.awt.Point;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Vector;

import javax.swing.JTable;
import javax.swing.table.DefaultTableColumnModel;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.JTableHeader;
import javax.swing.table.TableColumn;

public class SortableTableModel extends DefaultTableModel {
	private static final long serialVersionUID = 1L;
	
	JTable Ttable;
	JTableHeader header;
	Vector<String> TcolumnNames;
	Vector<String> Ttypes;
	int[] colW;
	public SortableTableModel(JTable table, Vector<String> columnNames,
			int rowCount, int[] colW, Vector<String> types) {
		super( columnNames, rowCount);
		Ttable=table;
		Ttypes=types;
		header=table.getTableHeader();
		header.setReorderingAllowed(false);
		header.addMouseListener((MouseListener) new HeaderMouseAdapter() );
		TcolumnNames=columnNames;
		this.colW=colW;
	}


	public class HeaderMouseAdapter extends MouseAdapter {
		int clickCount=0;
		@Override
		public void mousePressed(MouseEvent e) {
			Point mousePoint=new Point(e.getX(), e.getY());
			int selectedheaderColumn= header.columnAtPoint(mousePoint);
			DefaultTableColumnModel Cmodel = (DefaultTableColumnModel) Ttable.getColumnModel();
			TableColumn column = null;
			if(selectedheaderColumn!=0) {
				clickCount++;
				boolean reverseOrder= false;
				if((clickCount %2)==0) reverseOrder=true;
				sort(selectedheaderColumn, reverseOrder);
			}
			for (int i = 0 ; i < Cmodel.getColumnCount() ; i++){
				column = Cmodel.getColumn(i);
				column.setMaxWidth(colW[i]);
				column.setMinWidth(colW[i]);
				column.setPreferredWidth(colW[i]);
				if(i==0) column.setCellRenderer(new Column0Renderer());			
			}
		}
	}

	@SuppressWarnings("unchecked")
	/*
	 * class Column0Renderer extends DefaultTableCellRenderer { private static final
	 * long serialVersionUID = 1L; public Component
	 * getTableCellRendererComponent(JTable table, Object value, boolean isSelected,
	 * boolean hasFocus, int row, int column) {
	 * setBackground(table.getTableHeader().getBackground()); setValue(row + 1);
	 * setBorder(new BevelBorder(BevelBorder.RAISED)); return (this);
	 * setHorizontalAlignment(RIGHT); } }
	 * 
	 */	
	void sort( int selectedHeaderColumn, boolean reverseOrder) {
		@SuppressWarnings("rawtypes")
		Vector vec=getDataVector();
		Object[] array= vec.toArray();
		Arrays.sort(array, new Compare(selectedHeaderColumn,  reverseOrder));
		vec=DefaultTableModel.convertToVector(array);
		setDataVector(vec, TcolumnNames);
	}
	
	class Compare implements Comparator<Object>{
		int sortKey;
		boolean reverseOrder;
		Compare(int sortKey, boolean reverseOrder){
			this.sortKey=sortKey;
			this.reverseOrder=reverseOrder;
		}
		@SuppressWarnings({ "rawtypes", "unchecked" })
		@Override
		public int compare(Object o1, Object o2) {
			Vector v1= (Vector)o1;
			Vector v2= (Vector)o2;
			Object data1=v1.get(sortKey);
			Object data2=v2.get(sortKey);
			if(Ttypes.elementAt(sortKey).equals("INTEGER")) {
				Integer c1= Integer.valueOf(data1.toString());
				Integer c2= Integer.valueOf(data2.toString());
				int ans= c1.compareTo(c2);
				if(reverseOrder) ans *= -1;
				return ans;
			}else if(data1 instanceof Comparable && data2 instanceof Comparable) {
				Comparable c1=(Comparable)data1;
				Comparable c2=(Comparable)data2;
				int ans= c1.compareTo(c2);
				if(reverseOrder) ans *= -1;
				return ans;
			}else {
				return 0;
			}
		}
		public boolean equals(Object obj) {
			return equals(obj);
		}
	}
}
DBFilter.java
import java.io.File;
import javax.swing.filechooser.FileFilter;

public class DBFilter extends FileFilter{

	public boolean accept(File f){
		if (f.isDirectory()) return true;

		String ext = getExtension(f);
		if (ext != null){
			if (ext.equals("db") || ext.equals("sqlite3")) return true;
			else return false;
		}
		return false;
	}
	
	public String getDescription(){
		return "データベースファイル";
	}

	private String getExtension(File f){
		String ext = null;
		String filename = f.getName();
		int dotIndex = filename.lastIndexOf('.');

		if ((dotIndex > 0) && (dotIndex < filename.length() - 1)){
			ext = filename.substring(dotIndex + 1).toLowerCase();
		}
		return ext;
	}
}
Column0Renderer.java
import java.awt.Component;
import javax.swing.JTable;
import javax.swing.border.BevelBorder;
import javax.swing.table.DefaultTableCellRenderer;

public class Column0Renderer extends DefaultTableCellRenderer {
	private static final long serialVersionUID = 1L;
	public Component getTableCellRendererComponent(
			JTable table,Object value,boolean isSelected,boolean hasFocus,int row,int column)
	{
		setBackground(table.getTableHeader().getBackground());
		setValue(row + 1);
		setBorder(new BevelBorder(BevelBorder.RAISED));
		setHorizontalAlignment(RIGHT);
		return (this);
	}
}

　以上、おかしな点もあるかと思いますが何か参考になれば幸いです。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0