SQL error of missing database (no such table: users)
Не понимаю как исправить эту ошибку. Вроде таблица создана, запрос не кривой
package org.example;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import java.awt.*;
import java.sql.*;
public class Main {
public static String _url_ = "jdbc:sqlite:users.db";
static void main() throws ClassNotFoundException {
JFrame frame = new JFrame("Users");
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.setSize(700, 400);
frame.setLocationRelativeTo(null);
// Input panel
JPanel input = new JPanel(new FlowLayout(FlowLayout.LEFT));
JTextField tfName = new JTextField(12);
JTextField tfEmail = new JTextField(12);
JButton btnAdd = new JButton("Add");
JButton btnUpdate = new JButton("Update");
JButton btnDelete = new JButton("Delete");
input.add(new JLabel("Name"));
input.add(tfName);
input.add(new JLabel("Email"));
input.add(tfEmail);
input.add(btnAdd);
input.add(btnUpdate);
input.add(btnDelete);
DefaultTableModel model = new DefaultTableModel(new Object[]{"id","name","email"}, 0) {
public boolean isCellEditable(int r, int c) { return false; }
};
JTable table = new JTable(model);
table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
JScrollPane scroll = new JScrollPane(table);
frame.setLayout(new FlowLayout());
frame.add(input, BorderLayout.NORTH);
frame.add(scroll, BorderLayout.CENTER);
frame.setVisible(true);
// Load data
loadData(model);
table.getSelectionModel().addListSelectionListener(e -> {
if (!e.getValueIsAdjusting() && table.getSelectedRow() != -1) {
int r = table.getSelectedRow();
tfName.setText(model.getValueAt(r,1).toString());
tfEmail.setText(model.getValueAt(r,2).toString());
}
});
btnAdd.addActionListener(ae -> {
String name = tfName.getText().trim();
String email = tfEmail.getText().trim();
if (name.isEmpty() || email.isEmpty()) {
JOptionPane.showMessageDialog(frame, "Fill fields", "Warning", JOptionPane.WARNING_MESSAGE);
return;
}
new SwingWorker<Integer, Void>() {
protected Integer doInBackground() throws Exception {
String createSql = "CREATE TABLE IF NOT EXISTS users (" +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"name TEXT NOT NULL, " +
"email TEXT UNIQUE NOT NULL" +
")";
String sql = "INSERT INTO users (name,email) VALUES('John', '[email protected]')";
try (Connection conn = DriverManager.getConnection(_url_);
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
Statement statement = conn.createStatement();
statement.execute(createSql);
ps.setString(1, name);
ps.setString(2, email);
int cnt = ps.executeUpdate();
if (cnt==0) throw new SQLException("Insert failed");
try (ResultSet keys = ps.getGeneratedKeys()) {
if (keys.next()) return keys.getInt(1);
}
return -1;
}
}
protected void done() {
try {
int id = get();
if (id>0) model.addRow(new Object[]{id,name,email});
tfName.setText(""); tfEmail.setText("");
} catch (Exception ex) {
JOptionPane.showMessageDialog(frame, "Error: "+ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
}
}
}.execute();
});
btnUpdate.addActionListener(ae -> {
int sel = table.getSelectedRow();
if (sel==-1) { JOptionPane.showMessageDialog(frame, "Select row"); return; }
int id = (int) model.getValueAt(sel,0);
String name = tfName.getText().trim();
String email = tfEmail.getText().trim();
if (name.isEmpty() || email.isEmpty()) { JOptionPane.showMessageDialog(frame, "Fill fields"); return; }
new SwingWorker<Void, Void>() {
protected Void doInBackground() throws Exception {
String sql = "UPDATE users SET name= '1', email=? WHERE id= '1' ";
try (Connection conn = DriverManager.getConnection(_url_);
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, name);
ps.setString(2, email);
ps.setInt(3, id);
ps.executeUpdate();
}
return null;
}
protected void done() {
model.setValueAt(name, sel, 1);
model.setValueAt(email, sel, 2);
}
}.execute();
});
btnDelete.addActionListener(ae -> {
int sel = table.getSelectedRow();
if (sel==-1) { JOptionPane.showMessageDialog(frame, "Select row"); return; }
int id = (int) model.getValueAt(sel,0);
int c = JOptionPane.showConfirmDialog(frame, "Delete selected?", "Confirm", JOptionPane.YES_NO_OPTION);
if (c!=JOptionPane.YES_OPTION) return;
new SwingWorker<Void, Void>() {
protected Void doInBackground() throws Exception {
String sql = "DELETE FROM users WHERE id=?";
try (Connection conn = DriverManager.getConnection(_url_);
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
ps.executeUpdate();
}
return null;
}
protected void done() {
model.removeRow(sel);
tfName.setText(""); tfEmail.setText("");
}
}.execute();
});
}
private static void loadData(DefaultTableModel model) {
new SwingWorker<Void, Object[]>() {
protected Void doInBackground() throws Exception {
String sql = "SELECT FROM users";
try (Connection conn = DriverManager.getConnection(_url_);
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql)) {
while (rs.next()) {
publish(new Object[]{rs.getInt("id"), rs.getString("name"), rs.getString("email")});
}
}
return null;
}
protected void process(java.util.List<Object[]> chunks) {
for (Object[] row : chunks) model.addRow(row);
}
}.execute();
}
}