I googled the whole day and no luck. I call getnPrintAllData()
method after pressing OK button. So the code is:
public class DatabaseSQLiteConnection {
Connection conn = null;
PreparedStatement statement = null;
ResultSet res = null;
public DatabaseSQLiteConnection(){
try{
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:test.sqlite");
statement = conn.prepareStatement("SELECT * from product_info;");
}
catch(Exception e){
e.printStackTrace();
}
}
public void getnPrintAllData(){
String name, supplier, id;
DefaultTableModel dtm = new DefaultTableModel();
Window gui = new Window(); //My JPanel class
try{
res = statement.executeQuery();
testResultSet(res);
ResultSetMetaData meta = res.getMetaData();
int numberOfColumns = meta.getColumnCount();
while (res.next())
{
Object [] rowData = new Object[numberOfColumns];
for (int i = 0; i < rowData.length; ++i)
{
rowData[i] = res.getObject(i+1);
}
dtm.addRow(rowData);
}
gui.jTable1.setModel(dtm);
dtm.fireTableDataChanged();
//////////////////////////
}
catch(Exception e){
System.err.println(e);
e.printStackTrace();
}
finally{
try{
res.close();
statement.close();
conn.close();
}
catch(Exception e){
e.printStackTrace();
}
}
}
public void testResultSet(ResultSet res){
try{
while(res.next()){
System.out.println("Product ID: "+ res.getInt("product_id"));
System.out.println("Product name: "+ res.getString("product_name"));
System.out.println("Supplier: "+ res.getString("supplier"));
}
}
catch(Exception e){
e.printStackTrace();
}
}
}
My testResultSet()
method is working properly.
Now, how to change my code so that it works, or what is the most simple code to make DefaultTableModel
from ResultSet
?
Thanks in advance.
Edit: I am reciving java.lang.IllegalStateException: SQLite JDBC: inconsistent internal state
error.
This question is related to
java
swing
sqlite
jtable
defaulttablemodel
With all factors put into consideration, with regard to code architecture using modular programming would work very well and with more simplicity in the code. Write a simple
getData() function @returns a 2D array of the data from the database.
pass this funtion to the constructor of the JTabel() constructor i.e
JTabel myTable = new JTable(getData(),columnsArray);
In this case the second argument: columnsArray is a single dimensional array that has the column names
String[] columns = {{"ID","DataOfBirth","Age","Grade","Marks","RegNumber"}};
pass the JTable object to a JScrollPane then you are done, right after adding the ScrollPane to the container ofcourse
JScrollPane scrollPane = new JScrollPane(myTable);
JFrame myFrame = new JFrame();
myFrame.add(scrollPane);
Here is a sample function getData() that queries the database for the data that later is passed on to the JTable
public String[][] getRecords() {
try (Connection conect = DriverManager.getConnection("jdbc:mysql://localhost:3306/online_students_registration", "root", "")) {
Statement stm = conect.createStatement();
String SELECT_QUERY = "SELECT COUNT(*) FROM medicalrecords ;";
ResultSet cursor = stm.executeQuery(SELECT_QUERY);
while (cursor.next()) {
rows = cursor.getInt("COUNT(*)");
System.out.println("Table will have " + rows + " Rows");
}
System.err.println("Contacts row count is obtained!!");
if (rows < 1) {
JOptionPane.showMessageDialog(null, "There is NO DATA");
//contactsRowsCount = 1;
//System.out.println("Table rows succefully reset to " + contactsRowsCount + " Rows");
dataValues = new String[1][8];
//dataValues[1][5] = "No Values";
for (int i = 0; i < 1; i++) {
for (int j = 0; j < 8; j++) {
if (j == 0) {
dataValues[i][j] = "No Details Available";
System.out.println("" + dataValues);
} else {
dataValues[i][j] = "...";
System.out.println("Contacts" + dataValues[i][j]);
}
}
}
System.out.println("Return statement is being executed on 0 rows ");
//return doctoredDataValues;
} else if (rows > 0) {
System.out.println("obtain contacts code is being run under " + rows + " Rows");
dataValues = new String[rows][8]; //declare array for contacts table data
System.out.println("[ Line 1584 ]The dataValues object for the JTable succefully set");
String SELECT_QUERY_CONTACT = "SELECT * FROM medicalrecords; ";
//OBTAIN CONTACTS FROM DB WITH REGARD TO CONTACT CATEGORY SPECIFIED
ResultSet contactsTableCursor = stm.executeQuery(SELECT_QUERY_CONTACT);
//use iterator-algorithm to insert values into the JTable
for (int i = 0; contactsTableCursor.next() && i < rows; i++) {
for (int j = 0; j < 8; j++) {
dataValues[i][j] = contactsTableCursor.getString(j + 1);
System.out.println("Contacts" + dataValues[i][j]);
}
}
}
JOptionPane.showMessageDialog(null, "Medical Details Added Succefully!!");
} catch (SQLException e) {
JOptionPane.showMessageDialog(null, "Unable to Obtain contacts:Server is Offline(LINE 1568)" + e.getMessage());
}
return dataValues;
}
I think this is the Easiest way to populate a table with ResultSet with a method like
FillTable(MyTable, "select * Customers;");
And a very simple method can be made as
public void FillTable(JTable table, String Query)
{
try
{
CreateConnection();
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery(Query);
//To remove previously added rows
while(table.getRowCount() > 0)
{
((DefaultTableModel) table.getModel()).removeRow(0);
}
int columns = rs.getMetaData().getColumnCount();
while(rs.next())
{
Object[] row = new Object[columns];
for (int i = 1; i <= columns; i++)
{
row[i - 1] = rs.getObject(i);
}
((DefaultTableModel) table.getModel()).insertRow(rs.getRow()-1,row);
}
rs.close();
stat.close();
conn.close();
}
catch(InstantiationException | IllegalAccessException | SQLException e)
{
}
}
You have to Download the Jar called rs2xml by click this link rs2xml.jar Then Add it to your project libraries then import
import net.proteanit.sql.DbUtils;
I prepare Only a method to do so as a sample
private void Update_table(){
try{
// fetch a connection
connection = your_connection_class_name_here.getInstance().getConnection();
if (connection != null) {
String sql="select name,description from sell_mode";
pst=connection.prepareStatement(sql);
rs=pst.executeQuery();
your_table_name_for_populating_your_Data.setModel(DbUtils.resultSetToTableModel(rs));
}
}
catch(IOException | SQLException | PropertyVetoException e){
JOptionPane.showMessageDialog(null,e);
}
finally {
if (rs!= null) try { rs.close(); } catch (SQLException e) {}
if (pst != null) try { pst.close(); } catch (SQLException e) {}
if (connection != null) try { connection.close(); } catch (SQLException e) {}
}
}
Hope this will help more
The JTable constructor accepts two arguments 2dimension Object Array for the data, and String Array for the column names.
eg:
import java.awt.BorderLayout;
import java.awt.Color;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
public class Test6 extends JFrame {
public Test6(){
this.setSize(300,300);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
Mpanel m = new Mpanel();
this.add(m,BorderLayout.CENTER);
}
class Mpanel extends JPanel {
JTable mTable;
private Object[][] cells = {{"Vivek",10.00},{"Vishal",20.00}};
private String[] columnNames = { "Planet", "Radius" };
JScrollPane mScroll;
public Mpanel(){
this.setSize(150,150);
this.setComponent();
}
public void setComponent(){
mTable = new JTable(cells,columnNames);
mTable.setAutoCreateRowSorter(true);
mScroll = new JScrollPane(mTable);
this.add(mScroll);
}
}
public static void main(String[] args){
new Test6().setVisible(true);
}
}
go here java tips weblog
then,put in your project : listtabelmodel.java and rowtablemodel.java add another class with this code:
enter code here
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package comp;
import java.awt.*;
import java.sql.*;
import java.util.*;
import javax.swing.*;
import static javax.swing.JFrame.EXIT_ON_CLOSE;
import javax.swing.table.*;
public class TableFromDatabase extends JPanel {
private Connection conexao = null;
public TableFromDatabase() {
Vector columnNames = new Vector();
Vector data = new Vector();
try {
// Connect to an Access Database
conexao = DriverManager.getConnection("jdbc:mysql://" + "localhost"
+ ":3306/yourdatabase", "root", "password");
// Read data from a table
String sql = "select * from tb_something";
Statement stmt = conexao.createStatement();
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData md = rs.getMetaData();
int columns = md.getColumnCount();
// Get column names
for (int i = 1; i <= columns; i++) {
columnNames.addElement(md.getColumnName(i));
}
// Get row data
while (rs.next()) {
Vector row = new Vector(columns);
for (int i = 1; i <= columns; i++) {
row.addElement(rs.getObject(i));
}
data.addElement(row);
}
rs.close();
stmt.close();
conexao.close();
} catch (Exception e) {
System.out.println(e);
}
// Create table with database data
JTable table = new JTable(data, columnNames) {
public Class getColumnClass(int column) {
for (int row = 0; row < getRowCount(); row++) {
Object o = getValueAt(row, column);
if (o != null) {
return o.getClass();
}
}
return Object.class;
}
};
JScrollPane scrollPane = new JScrollPane(table);
add(scrollPane);
JPanel buttonPanel = new JPanel();
add(buttonPanel, BorderLayout.SOUTH);
}
public static void main(String[] args) {
javax.swing.SwingUtilities.invokeLater(new Runnable() {
public void run() {
JFrame frame = new JFrame("any");
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
//Create and set up the content pane.
TableFromDatabase newContentPane = new TableFromDatabase();
newContentPane.setOpaque(true); //content panes must be opaque
frame.setContentPane(newContentPane);
//Display the window.
frame.pack();
frame.setVisible(true);
}
});
}
}
then drag this class to you jframe,and it's done
it's deprecated,but it works.........
Well I'm sure that this is the simplest way to populate JTable from ResultSet, without any external library. I have included comments in this method.
public void resultSetToTableModel(ResultSet rs, JTable table) throws SQLException{
//Create new table model
DefaultTableModel tableModel = new DefaultTableModel();
//Retrieve meta data from ResultSet
ResultSetMetaData metaData = rs.getMetaData();
//Get number of columns from meta data
int columnCount = metaData.getColumnCount();
//Get all column names from meta data and add columns to table model
for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++){
tableModel.addColumn(metaData.getColumnLabel(columnIndex));
}
//Create array of Objects with size of column count from meta data
Object[] row = new Object[columnCount];
//Scroll through result set
while (rs.next()){
//Get object from column with specific index of result set to array of objects
for (int i = 0; i < columnCount; i++){
row[i] = rs.getObject(i+1);
}
//Now add row to table model with that array of objects as an argument
tableModel.addRow(row);
}
//Now add that table model to your table and you are done :D
table.setModel(tableModel);
}
This is my approach:
String[] columnNames = {"id", "Nome", "Sobrenome","Email"};
List<Student> students = _repo.getAll();
Object[][] data = new Object[students.size()][4];
int index = 0;
for(Student s : students) {
data[index][0] = s.getId();
data[index][1] = s.getFirstName();
data[index][2] = s.getLastName();
data[index][3] = s.getEmail();
index++;
}
DefaultTableModel model = new DefaultTableModel(data, columnNames);
table = new JTable(model);
Class Row
will handle one row from your database.
Complete implementation of UpdateTask responsible for filling up UI.
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JTable;
import javax.swing.SwingWorker;
public class JTableUpdateTask extends SwingWorker<JTable, Row> {
JTable table = null;
ResultSet resultSet = null;
public JTableUpdateTask(JTable table, ResultSet rs) {
this.table = table;
this.resultSet = rs;
}
@Override
protected JTable doInBackground() throws Exception {
List<Row> rows = new ArrayList<Row>();
Object[] values = new Object[6];
while (resultSet.next()) {
values = new Object[6];
values[0] = resultSet.getString("id");
values[1] = resultSet.getString("student_name");
values[2] = resultSet.getString("street");
values[3] = resultSet.getString("city");
values[4] = resultSet.getString("state");
values[5] = resultSet.getString("zipcode");
Row row = new Row(values);
rows.add(row);
}
process(rows);
return this.table;
}
protected void process(List<Row> chunks) {
ResultSetTableModel tableModel = (this.table.getModel() instanceof ResultSetTableModel ? (ResultSetTableModel) this.table.getModel() : null);
if (tableModel == null) {
try {
tableModel = new ResultSetTableModel(this.resultSet.getMetaData(), chunks);
} catch (SQLException e) {
e.printStackTrace();
}
this.table.setModel(tableModel);
} else {
tableModel.getRows().addAll(chunks);
}
tableModel.fireTableDataChanged();
}
}
Table Model:
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.table.AbstractTableModel;
/**
* Simple wrapper around Object[] representing a row from the ResultSet.
*/
class Row {
private final Object[] values;
public Row(Object[] values) {
this.values = values;
}
public int getSize() {
return values.length;
}
public Object getValue(int i) {
return values[i];
}
}
// TableModel implementation that will be populated by SwingWorker.
public class ResultSetTableModel extends AbstractTableModel {
private final ResultSetMetaData rsmd;
private List<Row> rows;
public ResultSetTableModel(ResultSetMetaData rsmd, List<Row> rows) {
this.rsmd = rsmd;
if (rows != null) {
this.rows = rows;
} else {
this.rows = new ArrayList<Row>();
}
}
public int getRowCount() {
return rows.size();
}
public int getColumnCount() {
try {
return rsmd.getColumnCount();
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
public Object getValue(int row, int column) {
return rows.get(row).getValue(column);
}
public String getColumnName(int col) {
try {
return rsmd.getColumnName(col + 1);
} catch (SQLException e) {
e.printStackTrace();
}
return "";
}
public Class<?> getColumnClass(int col) {
String className = "";
try {
className = rsmd.getColumnClassName(col);
} catch (SQLException e) {
e.printStackTrace();
}
return className.getClass();
}
@Override
public Object getValueAt(int rowIndex, int columnIndex) {
if(rowIndex > rows.size()){
return null;
}
return rows.get(rowIndex).getValue(columnIndex);
}
public List<Row> getRows() {
return this.rows;
}
public void setRows(List<Row> rows) {
this.rows = rows;
}
}
Main Application which builds UI and does the database connection
import java.awt.BorderLayout;
import java.awt.Dimension;
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 javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JTable;
public class MainApp {
static Connection conn = null;
static void init(final ResultSet rs) {
JFrame frame = new JFrame();
frame.setLayout(new BorderLayout());
final JTable table = new JTable();
table.setPreferredSize(new Dimension(300,300));
table.setMinimumSize(new Dimension(300,300));
table.setMaximumSize(new Dimension(300,300));
frame.add(table, BorderLayout.CENTER);
JButton button = new JButton("Start Loading");
button.setPreferredSize(new Dimension(30,30));
button.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
JTableUpdateTask jTableUpdateTask = new JTableUpdateTask(table, rs);
jTableUpdateTask.execute();
}
});
frame.add(button, BorderLayout.SOUTH);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.pack();
frame.setVisible(true);
}
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/test";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url, userName, password);
PreparedStatement pstmt = conn.prepareStatement("Select id, student_name, street, city, state,zipcode from student");
ResultSet rs = pstmt.executeQuery();
init(rs);
} catch (Exception e) {
e.printStackTrace();
}
}
}
I think this is the Easiest way to populate/model a table with ResultSet.. Download and include rs2xml.jar Get rs2xml.jar in your libraries..
import net.proteanit.sql.DbUtils;
try
{
CreateConnection();
PreparedStatement st =conn.prepareStatement("Select * from ABC;");
ResultSet rs = st.executeQuery();
tblToBeFilled.setModel(DbUtils.resultSetToTableModel(rs));
conn.close();
}
catch(Exception ex)
{
JOptionPane.showMessageDialog(null, ex.toString());
}
Source: Stackoverflow.com