Reading from mySQL JDBC updating

[speakon]

Hello,

I need to create a JDBC that connects to a mySQL database, retrieves the data from a table and displays it into a JTable.

I have done this successfully, but have ran into a problem. The initial values I entered into the database (for testing) are permament. If I change the database values, save, close mySQL and re-execute the code it still displays the first set of values.

I reloaded mySQL to make sure it wasn't a problem with the database, but sure enough it shows the newly entered values.

Could someone please take a look at my code and show me where I have gone wrong? It's wrecking my head!

Thanks

package JDBC;


import java.sql.*;
import java.util.*;
import java.awt.*;
import javax.swing.*;


public class JDBCNew extends JPanel {

    public Connection con = null;
    public Statement stmt = null;
    public ResultSet rs = null;


    public JDBCNew() {

        try{

            String url = "jdbc:mysql://localhost:3306/myrecords";
            String login = "root";
            String password = "jdbc";



            con = DriverManager.getConnection(url, login, password);
            stmt = con.createStatement();

            ResultSet result = stmt.executeQuery("SELECT * FROM employees");
            ResultSetMetaData md = result.getMetaData();
            int columnCount = md.getColumnCount();

            Vector columns = new Vector(columnCount);


            for(int i=1; i<=columnCount; i++)
            columns.add(md.getColumnName(i));

            Vector data = new Vector();
            Vector row;


            while(result.next())
            {
            row = new Vector(columnCount);
            for(int i=1; i<=columnCount; i++)
            {
            row.add(result.getString(i));
            }
            data.add(row);
            }



            JTable table = new JTable(data, columns);
            table.setPreferredScrollableViewportSize(new Dimension(300, 400));  
            table.setFillsViewportHeight(true);  
           // table.validate();
            this.add(table);

        }catch(SQLException sqle){
            System.err.println(sqle);
        }


    }

    public static void main(String[] args) {

        JFrame frame = new JFrame("Employee Records");
        JScrollPane scrollPane = new JScrollPane(new JDBCNew());


        frame.getContentPane().add(scrollPane);
        frame.setSize(400, 500);
        frame.setVisible(true);


    }

}

[JamesCherrill]

There's no code there that can update the database, so how exactly are you trying to update the values?

[speakon]

I was under the impression it would just read the current values from the database, not have to be updated regardless.

Could you point me in the right direction documentation wise to implement updating functions?

[JamesCherrill]

Normally you would use a Listener to respond to changes in the table's data, and feed those into SQL statements to update the database. The details depend on exactly how your overall systeem is put together, but you will find many relevant examples on the web.

[speakon]

I am really struggling here, trying to find literature that's in line with my JDBC structure.

Would it be possible for you to link me to some websites I would find useful? This is going way over my head!

[1stDAN]

if you post the attributes of your table employees, I could show you the way how to update a row. You may tell me which data you want to update for example. Consider, table employees must have a primary key.

By the way, why are you trying to work with the meta data of your result set?

-- 1stDAN

[speakon]

employeeID int PK
Forename VARCHAR(45)
Surname VARCHAR(45)
Telephone INT

My problem is;

I need to prove that editting the mySQL information will transfer over to the JDBC application.

I entered values into the mySQL, executed it once.
changed the value in mySQL, re-executed after saving and the OLD values still appear over and over, regardless of what I change/save etc.

[1stDAN]

Ok, I hope I've understood you correctly. So this is not a matter of updating your MySQL database from within a Java program using JDBC.

When you are on MySQL's workbench, you may enter a SQL update like this one:

update employees set Forename = 'Jack' where employeeID = .... ;

Supposing your MySQL is NOT configured to autocommit mode ON, you have to commit explicitely by entering:

commit;

try this and tell me what happend.

[speakon]

Hello, I tried

commit;

in mySQL, executed it and re-launched my Java code but still having the same issue.

You understood correctly.

[speakon]

Does anyone have anything new to add?

Thank you.

[1stDAN]

Hi speakon,

now I am at my wit's end.

I've just created your table employee on my MySQL database (running on older ubuntu), inserted some rows, commited and selected them as follows:

mysql> use dan;

mysql> create table employee (
    -> employeeID integer not null,
    -> forename varchar(100),
    -> surname varchar(100),
    -> telephone varchar(15),
    -> primary key (employeeID)
    -> );

mysql> insert into employee (employeeID, forename, surname, telephone) values (1000, 'Dan', 'Brown', '030-252-43854');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into employee (employeeID, forename, surname, telephone) values (1001, 'Dani', 'Webs', '001-516-222-1700');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into employee (employeeID, forename, surname, telephone) values (1002, 'Danica', 'Padraige', '001-516-463-6800');
Query OK, 1 row affected, 1 warning (0.00 sec)

commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from employee;
+------------+----------+----------+-----------------+
| employeeID | forename | surname  | telephone       |
+------------+----------+----------+-----------------+
|       1000 | Dan      | Brown    | 030-252-43854   |
|       1001 | Dani     | Webs     | 001-516-222-170 |
|       1002 | Danica   | Padraige | 001-516-463-680 |
+------------+----------+----------+-----------------+
3 rows in set (0.00 sec)

Then I updated last row, commited changes made to database and exited mysql:

mysql> update employee set surname = 'Padraig' where employeeID = 1002;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

After reconnecting to MySQL the SQL select showes the changes correctly:

root@dan:/home/dan# mysql -udan -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 46
Server version: 5.1.62-0ubuntu0.10.04.1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use dan
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from employee;
+------------+----------+---------+-----------------+
| employeeID | forename | surname | telephone       |
+------------+----------+---------+-----------------+
|       1000 | Dan      | Brown   | 030-252-43854   |
|       1001 | Dani     | Webs    | 001-516-222-170 |
|       1002 | Danica   | Padraig | 001-516-463-680 |
+------------+----------+---------+-----------------+
3 rows in set (0.00 sec)

So what might be wrong with your database?

Have you got the rights for creating new tables on your database?

What ist the result of querying: select @@autocommit;

-- 1stDAN

[speakon]

Hello,

if I try:

insert into employee (employeeID, forename, surname, telephone) values (1000, 'Dan', 'Brown', '030-252-43854');

I get

"Error Code: 1146. Table 'myrecords.employee' doesn't exist

if I try:

select @@autocommit; I get
1row(s) returned.

Does this help?

Every time I execute my Java code it still displays values that are not saved to the database.

[1stDAN]

ok, try these (daniweb forced me to put all these tiny snippets into code...!):

1st
    instead of employee  ...
    try employees  ...
    (I usually use the singular form for table names, so instead of employees I use employee)


2nd
    If you do 
    select @@autocommit;
    you should get this
    +--------------+
    | @@autocommit |
    +--------------+
    |            1 |
    +--------------+
    1 row in set (0.00 sec)

or that

    +--------------+
    | @@autocommit |
    +--------------+
    |            0 |
    +--------------+
    1 row in set (0.00 sec)

So what did you exactly get?

To gain ground, pls post these:

1st,  the create statement of your table employees

    (like
      create table employee ( employeeID integer not null,
      forename varchar(100),
      surname varchar(100),
      telephone varchar(20),
      primary key (employeeID) );
    )

2nd, some insert statements

    (like
      insert into employee (employeeID, forename, surname, telephone) values (1001, 'Dani', 'Webs',    '001-516-222-1700');
    )

3rd, result of select statements from employees

    (like
    select * from employee;
    employeeID  forename    surname telephone
    1000    Dan Brown   030-252-43854
    1001    Dani    Webs    001-516-222-1700
    1002    Danica  Padraige    001-516-463-6800
    )

[speakon]

OK I've found a problem, when I do a select statement on the table, the old values appear?

But when I view the table through the GUI it shows the newly entered values?

I'm lost, it's saying two different things.

[speakon]

Used this:

UPDATE employees
SET Forename = 'John', Surname = 'blabla', Department = 'adw'
WHERE idEmployees = 1;

SELECT
idEmployees, Forename, Surname, Department FROM
employees;

Works perfectly now.

Thanks.