Help: Java database manipulation using NetBeans


T

tobleron

Hi, I'm using Netbeans IDE to create a form and want to insert data
from the swing component into the database. I'm using MySQL. I want to
insert the data by clicking the submit button. But I'm confusing how
to make the correct code. Here is my code. I know this still has a lot
of errors. Please give me advise. Thx before.

/*
* ECGTerminalSetup.java
*/

package ecgterminal;

import org.jdesktop.application.Action;
import java.sql.*;


public class ECGTerminalSetup extends javax.swing.JDialog {

//DB Configuration

public static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
public static final String DATABASE_URL = "jdbc:mysql://localhost:3306/
dicom";
public Connection myConn;

public ECGTerminalSetup(java.awt.Frame parent) {
super(parent);
initComponents();
getRootPane().setDefaultButton(closeButton);
}

@Action public void closeAboutBox() {
setVisible(false);
}

/** This method is called from within the constructor to
* initialize the form.
* WARNING: Do NOT modify this code. The content of this method is
* always regenerated by the Form Editor.
*/
// <editor-fold defaultstate="collapsed" desc="Generated
Code">
private void initComponents() {

closeButton = new javax.swing.JButton();
jLabel1 = new javax.swing.JLabel();
jLabel2 = new javax.swing.JLabel();
jLabel3 = new javax.swing.JLabel();
terminalIdTxt = new javax.swing.JTextField();
terminalNameTxt = new javax.swing.JTextField();
insNameTxt = new javax.swing.JTextField();
jLabel4 = new javax.swing.JLabel();
insAddr1Txt = new javax.swing.JTextField();
submitButton = new javax.swing.JButton();
jLabel5 = new javax.swing.JLabel();
insAddr2Txt = new javax.swing.JTextField();


setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);
org.jdesktop.application.ResourceMap resourceMap =
org.jdesktop.application.Application.getInstance(ecgterminal.ECGTerminalApp.class).getContext().getResourceMap(ECGTerminalSetup.class);
setTitle(resourceMap.getString("title")); // NOI18N
setModal(true);
setName("aboutBox"); // NOI18N
setResizable(false);

javax.swing.ActionMap actionMap =
org.jdesktop.application.Application.getInstance(ecgterminal.ECGTerminalApp.class).getContext().getActionMap(ECGTerminalSetup.class,
this);
closeButton.setAction(actionMap.get("closeAboutBox")); //
NOI18N

closeButton.setText(resourceMap.getString("closeButton.text")); //
NOI18N
closeButton.setName("closeButton"); // NOI18N

jLabel1.setFont(resourceMap.getFont("jLabel1.font")); //
NOI18N
jLabel1.setText(resourceMap.getString("jLabel1.text")); //
NOI18N
jLabel1.setName("jLabel1"); // NOI18N

jLabel2.setText(resourceMap.getString("jLabel2.text")); //
NOI18N
jLabel2.setName("jLabel2"); // NOI18N

jLabel3.setText(resourceMap.getString("jLabel3.text")); //
NOI18N
jLabel3.setName("jLabel3"); // NOI18N

terminalIdTxt.setName("terminalIdTxt"); // NOI18N

terminalNameTxt.setName("terminalNameTxt"); // NOI18N

insNameTxt.setName("insNameTxt"); // NOI18N

jLabel4.setText(resourceMap.getString("jLabel4.text")); //
NOI18N
jLabel4.setName("jLabel4"); // NOI18N

insAddr1Txt.setName("insAddr1Txt"); // NOI18N

submitButton.setAction(actionMap.get("addTerminal")); //
NOI18N
submitButton.setName("submitButton"); // NOI18N

jLabel5.setText(resourceMap.getString("jLabel5.text")); //
NOI18N
jLabel5.setName("jLabel5"); // NOI18N

insAddr2Txt.setName("insAddr2Txt"); // NOI18N

javax.swing.GroupLayout layout = new
javax.swing.GroupLayout(getContentPane());
getContentPane().setLayout(layout);
layout.setHorizontalGroup(

layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addContainerGap()
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addComponent(jLabel1)
.addGroup(layout.createSequentialGroup()
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addComponent(jLabel4)
.addComponent(jLabel2)
.addComponent(jLabel3))
.addGap(49, 49, 49)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
.addComponent(insNameTxt,
javax.swing.GroupLayout.DEFAULT_SIZE, 265, Short.MAX_VALUE)
.addGroup(layout.createSequentialGroup()
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING,
false)
.addComponent(terminalNameTxt,
javax.swing.GroupLayout.Alignment.LEADING)
.addComponent(terminalIdTxt,
javax.swing.GroupLayout.Alignment.LEADING,
javax.swing.GroupLayout.DEFAULT_SIZE, 139, Short.MAX_VALUE))
.addGap(126, 126, 126))))
.addGroup(layout.createSequentialGroup()
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
.addComponent(submitButton)
.addComponent(jLabel5))
.addGap(39, 39, 39)
.addComponent(insAddr1Txt,
javax.swing.GroupLayout.DEFAULT_SIZE, 263, Short.MAX_VALUE))))
.addGroup(layout.createSequentialGroup()
.addGap(140, 140, 140)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addComponent(closeButton)
.addComponent(insAddr2Txt,
javax.swing.GroupLayout.DEFAULT_SIZE, 263, Short.MAX_VALUE))))
.addContainerGap())
);
layout.setVerticalGroup(

layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addContainerGap()
.addComponent(jLabel1)
.addGap(18, 18, 18)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
.addComponent(jLabel2)
.addComponent(terminalIdTxt,
javax.swing.GroupLayout.PREFERRED_SIZE,
javax.swing.GroupLayout.DEFAULT_SIZE,
javax.swing.GroupLayout.PREFERRED_SIZE))
.addGap(18, 18, 18)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
.addComponent(jLabel3)
.addComponent(terminalNameTxt,
javax.swing.GroupLayout.PREFERRED_SIZE,
javax.swing.GroupLayout.DEFAULT_SIZE,
javax.swing.GroupLayout.PREFERRED_SIZE))
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addGap(26, 26, 26)
.addComponent(jLabel4))
.addGroup(layout.createSequentialGroup()
.addGap(18, 18, 18)
.addComponent(insNameTxt,
javax.swing.GroupLayout.PREFERRED_SIZE,
javax.swing.GroupLayout.DEFAULT_SIZE,
javax.swing.GroupLayout.PREFERRED_SIZE)))
.addGap(21, 21, 21)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
.addComponent(insAddr1Txt,
javax.swing.GroupLayout.PREFERRED_SIZE,
javax.swing.GroupLayout.DEFAULT_SIZE,
javax.swing.GroupLayout.PREFERRED_SIZE)
.addComponent(jLabel5))
.addGap(18, 18, 18)
.addComponent(insAddr2Txt,
javax.swing.GroupLayout.PREFERRED_SIZE,
javax.swing.GroupLayout.DEFAULT_SIZE,
javax.swing.GroupLayout.PREFERRED_SIZE)
.addGap(18, 18, 18)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
.addComponent(closeButton)
.addComponent(submitButton))
.addContainerGap(20, Short.MAX_VALUE))
);

pack();
}// </editor-fold>

//This method is used to connect to the database
public void init() {
try {
Class.forName(DRIVER_NAME);
//Again, you'll have to change these to suit your own config.
myConn =
DriverManager.getConnection(DATABASE_URL,"dicom","");
} catch (SQLException ex) {
ex.printStackTrace();
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
}
}

//This method is to be called only after init() in order to get
the database connection
public Connection getMyConnection() {
return myConn;
}

//Closes the SQL statement once you're done retrieving data
public void Close(java.sql.Statement stmt) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}

//Shuts down the connection
public void CloseConnection() {
if (myConn != null) {
try {
myConn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}

//This next method represents the code of your application
//and is used to insert or retrieve data from your database
public static void main(String[] args) throws SQLException {

//Define a few variables that you'll need later
//This is obviously a contrived variable. You asked to
retrieve database data
//based on information gathered from a GUI. Therefore, get
the information
//from the appropriate control in the GUI. For a text box,
you might do it
//like this: txtbxCriteria.getText();
//String txtbxCriteria = "Second String";
//Open a database connection, and define required SQL
statements and result sets
ECGTerminalSetup myConn = new ECGTerminalSetup();
myConn.init();
Connection con = myConn.getMyConnection();

//Set up your SQL statements
//Next, to insert data.
String sqlInsert = "insert into TABLENAME (INTVAR2, STRVAR1)
values (?, ?)";


//After your data have been retrieved successfully, close
everything
con.close();
myConn.CloseConnection();
}

// Variables declaration - do not modify
private javax.swing.JButton closeButton;
private javax.swing.JTextField insAddr1Txt;
private javax.swing.JTextField insAddr2Txt;
private javax.swing.JTextField insNameTxt;
private javax.swing.JLabel jLabel1;
private javax.swing.JLabel jLabel2;
private javax.swing.JLabel jLabel3;
private javax.swing.JLabel jLabel4;
private javax.swing.JLabel jLabel5;
private javax.swing.JButton submitButton;
private javax.swing.JTextField terminalIdTxt;
private javax.swing.JTextField terminalNameTxt;
// End of variables declaration

}
 
Ad

Advertisements

J

John B. Matthews

tobleron said:
Hi, I'm using Netbeans IDE to create a form and want to insert data
from the swing component into the database. I'm using MySQL. I want to
insert the data by clicking the submit button. But I'm confusing how
to make the correct code. Here is my code. I know this still has a lot
of errors. Please give me advise. Thx before.

Among other things, your code never defines the name of the table and
columns to use. First, see if you can connect from java, then see if you
can insert a row in a table and view the result. Numerous examples may
be found here:

<http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html>

<code>
import java.sql.*;

public class User {

public static void main(String args[]) {

String url = "jdbc:mysql://localhost:3306/mysql";
Connection con;
Statement stmt;

try {
Class.forName("com.mysql.jdbc.Driver");
} catch(java.lang.ClassNotFoundException e) {
System.err.println(e);
}

try {

con = DriverManager.getConnection(url, "name", "secret");
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
"select user, host from user where user != ''");

while (rs.next()) {
String s1 = rs.getString(1);
String s2 = rs.getString(2);
System.out.println(s1 + "@" + s2);
}

stmt.close();
con.close();

} catch(SQLException e) {
System.err.println(e);
}
}
}
</code>
 
T

tobleron

 tobleron said:
Hi, I'm using Netbeans IDE to create a form and want to insert data
from the swing component into the database. I'm using MySQL. I want to
insert the data by clicking the submit button. But I'm confusing how
to make the correct code. Here is my code. I know this still has a lot
of errors. Please give me advise. Thx before.

Among other things, your code never defines the name of the table and
columns to use. First, see if you can connect from java, then see if you
can insert a row in a table and view the result. Numerous examples may
be found here:

<http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html>

<code>
import java.sql.*;

public class User {

  public static void main(String args[]) {

    String url = "jdbc:mysql://localhost:3306/mysql";
    Connection con;
    Statement stmt;

    try {
      Class.forName("com.mysql.jdbc.Driver");  
    } catch(java.lang.ClassNotFoundException e) {
      System.err.println(e);
    }

    try {

      con = DriverManager.getConnection(url, "name", "secret");
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(
        "select user, host from user where user != ''");

      while (rs.next()) {
        String s1 = rs.getString(1);
        String s2 = rs.getString(2);
        System.out.println(s1 + "@" + s2);
      }

      stmt.close();
      con.close();

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

</code>

Hi, I already tried your sugestion, but I found result :

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
java.sql.SQLException: No suitable driver found for jdbc:mysql://
localhost:3306/dicom

But in the service window, I already run the connection to MySQL and
get no problem when executing SQL statement using "Execute
Command...". What happened ? FYI, I add an action into the
submitButton, so it should be send the data to MySQL server when the
button was clicked. Here is my code :

package ecgterminal;

import org.jdesktop.application.Action;
import java.sql.*;


public class ECGTerminalSetup extends javax.swing.JDialog {

public ECGTerminalSetup(java.awt.Frame parent) {
super(parent);
initComponents();
getRootPane().setDefaultButton(closeButton);
}

@Action public void closeAboutBox() {
setVisible(false);
}

@Action public void setup() {
String url = "jdbc:mysql://localhost:3306/dicom";
Connection con;
Statement stmt;

try {
Class.forName("com.mysql.jdbc.Driver");
} catch(java.lang.ClassNotFoundException e) {
System.err.println(e);
}

try {
con = DriverManager.getConnection(url, "dicom", "");
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
"INSERT INTO ecgterminal
(terminalid,terminalname,insname,insaddr1,insaddr2) VALUES ('"+
terminalIdTxt +"','"+ terminalNameTxt +"','"+ insNameTxt +"','"+
insAddr1Txt +"','"+ insAddr2Txt +"')");


stmt.close();
con.close();

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

// Variables declaration - do not modify
private javax.swing.JButton closeButton;
private javax.swing.JTextField insAddr1Txt;
private javax.swing.JTextField insAddr2Txt;
private javax.swing.JTextField insNameTxt;
private javax.swing.JLabel jLabel1;
private javax.swing.JLabel jLabel2;
private javax.swing.JLabel jLabel3;
private javax.swing.JLabel jLabel4;
private javax.swing.JLabel jLabel5;
private javax.swing.JButton submitButton;
private javax.swing.JTextField terminalIdTxt;
private javax.swing.JTextField terminalNameTxt;
// End of variables declaration

}
 
J

John B. Matthews

tobleron said:
Among other things, your code never defines the name of the table and
columns to use. First, see if you can connect from java, then see if you
can insert a row in a table and view the result. Numerous examples may
be found here:

<http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html>
[...]
Hi, I already tried your sugestion, but I found result :

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
java.sql.SQLException: No suitable driver found for jdbc:mysql://
localhost:3306/dicom

Nothing will work until you solve this part of the problem. The jar
containing com.mysql.jdbc.Driver must be accessible to your program when
it runs. You can add it to the classpath from the command line, add it
your to project's properties, or check the NetBeans' Library Manager.
See "Setting the Runtime Classpath" in Help.
But in the service window, I already run the connection to MySQL and
get no problem when executing SQL statement using "Execute
Command...". What happened?

Do you mean the "Services" window in NetBeans? I would expect this uses
the MySQL driver that is included with NetBeans. Mine's inside the
NetBeans folder:

/Applications/NetBeans.app/Contents/Resources/NetBeans/ide9/modules/ext/m
ysql-connector-java-5.1.5-bin.jar

You can add a new driver by clicking on the Driver folder in the
Services window.
FYI, I add an action into the submitButton, so it should be send the
data to MySQL server when the button was clicked. Here is my code :

I cannot test your code, as it is incomplete.
 
T

tobleron

I already fix the classpath, and it seems looked OK for the DB
connection. But when the data sent to the DB, the netbeans response
with this error message :

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for
column 'terminalid' at row 1

The "terminalid" was varchar(10). And the data to be written just
"ECG-2" which is less than 10 character. Why it shown error message
"data too long" ??
 
J

John B. Matthews

tobleron said:
I fix[ed] the classpath, and it seems [to be] OK for the DB
connection.
Excellent.

But when the data [is] sent to the DB, [NetBeans] respond with this
error message:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for
column 'terminalid' at row 1

The "terminalid" was varchar(10), and the data to be written [is]
"ECG-2", which is less than 10 character. Why [does it show the] error
message "data too long"?


Either terminalid isn't varchar(10) or the inserted string is longer
than you think. I can reproduce your error with the following table:

mysql> show columns from tobleron;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| terminalid | varchar(10) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+

and the following SQL statement:

"insert into tobleron(terminalid) values ('ECG-2.12345')"

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for
column 'terminalid' at row 1

Of course, it works correctly for the value 'ECG-2'. If this isn't
helpful, we need to see your table definition and SQl statement.
 
Ad

Advertisements

T

tobleron

 tobleron said:
I fix[ed] the classpath, and it seems [to be] OK for the DB
connection.
Excellent.

But when the data [is] sent to the DB, [NetBeans] respond with this
error message:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for
column 'terminalid' at row 1
The "terminalid" was varchar(10), and the data to be written [is]
"ECG-2", which is less than 10 character. Why [does it show the] error
message "data too long"?

Either terminalid isn't varchar(10) or the inserted string is longer
than you think. I can reproduce your error with the following table:

mysql> show columns from tobleron;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| terminalid | varchar(10) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

and the following SQL statement:

"insert into tobleron(terminalid) values ('ECG-2.12345')"

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for
column 'terminalid' at row 1

Of course, it works correctly for the value 'ECG-2'. If this isn't
helpful, we need to see your table definition and SQl statement.


Thank you for correcting my poor english :)

Yes, it works if I used "Execute commands..." menu in the "services"
window. But if I used script in my code, it doesn't work. It still
shows "com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too
long for column 'terminalid' at row 1" even i just used "ECG-2" for
the terminalid's data. In other side, there is a set of data already
fits the row 1. The next data should be occupies row 2, right ? But
still showed "Data too long for column 'terminalid' at row 1" even it
is already occupied. Here is my code :


package ecgterminal;

import org.jdesktop.application.Action;
import java.sql.*;


public class ECGTerminalSetup extends javax.swing.JDialog {

public ECGTerminalSetup(java.awt.Frame parent) {
super(parent);
initComponents();
getRootPane().setDefaultButton(closeButton);
}

@Action public void closeAboutBox() {
setVisible(false);
}

@Action public void setup() {
String url = "jdbc:mysql://localhost:3306/dicom";
Connection con;
Statement stmt;

try {
Class.forName("com.mysql.jdbc.Driver");
} catch(java.lang.ClassNotFoundException e) {
System.err.println(e);
}

try {
con = DriverManager.getConnection(url, "dicom", "");
stmt = con.createStatement();
String sql = "INSERT INTO ecgterminal
(terminalid,terminalname,insname,insaddr1,insaddr2) VALUES ('"+
terminalIdTxt +"','"+ terminalNameTxt +"','"+ insNameTxt +"','"+
insAddr1Txt +"','"+ insAddr2Txt +"')";
stmt.execute(sql);
/*while (rs.next()) {
String s1 = rs.getString(1);
String s2 = rs.getString(2);
System.out.println(s1 + "@" + s2);
}*/

stmt.close();
con.close();

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

private javax.swing.JButton closeButton;
private javax.swing.JTextField insAddr1Txt;
private javax.swing.JTextField insAddr2Txt;
private javax.swing.JTextField insNameTxt;
private javax.swing.JLabel jLabel1;
private javax.swing.JLabel jLabel2;
private javax.swing.JLabel jLabel3;
private javax.swing.JLabel jLabel4;
private javax.swing.JLabel jLabel5;
private javax.swing.JButton submitButton;
private javax.swing.JTextField terminalIdTxt;
private javax.swing.JTextField terminalNameTxt;
// End of variables declaration

}


The table structure is :

name : ecgterminal

a4identity : int, auto increment, no null, key --> this is for
identity only
terminalid : varchar(10), no null, key
terminalname : varchar(10), no null
insname : varchar(30), no null
insaddr1 : varchar(30), no null
insaddr2 : varchar(30), no null
 
T

tobleron

Even when I changed the to "terminalid varchar(30)" and I submitted
"123" for the data, it still shows :

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for
column 'terminalid' at row 1

It's so confusing... varchar(30) should be enough for "123".
 
J

John B. Matthews

tobleron said:
Thank you for correcting my poor english :)

Thank you for a collegial discussion. I appreciate your understanding
that my intent was to foster communications.
Just found from the Net, the error probably caused by a bug in MySQL
Server. See detail at : http://bugs.mysql.com/bug.php?id=18908
Currently I'm using version 5.0.51a. I think the best way is waiting
for the new version of MySQL.

Interesting. As suggested in the discussion there, the problem does not
appear on Mac OS X using MySQL 5.0.41 with utf-8. You might execute

SHOW VARIABLES LIKE 'character_sets_dir';

to see if there's a suitable charset available. Sorry I couldn't be more
help.
 
T

tobleron

Hi, today I found a trick to handle this issue from
http://forums.mysql.com/read.php?39,15869,17969#msg-17969. I tested it
in my java code, and it works. But the data that sent into the table
is not the value that I wrote :

a4identity : 1
terminalid : javax.swin
terminalname : javax.swin
insname : javax.swing.JTextField[insName
insaddr1 : javax.swing.JTextField[insAddr
insaddr2 : javax.swing.JTextField[insAddr

Please advise.
 
Ad

Advertisements

T

tobleron

Hi, the problem has been solved. I just used namecompenent.getText()
to send the value into the database. Thank you for helping. Thread
closed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top