Accessing MySQL on NetBeans using JDBC, Part II: Perform SQL Operations
Java, NetBeans January 16th, 2008From Part I, I have only established a connection with local MySQL. Next I’ll show how to retrieve and modify data on remote MySQL.
MySQL Connection using in this part
Suppose that I have MySQL running remotely on IP:192.168.1.101 with default port (3306) and I want to connect to Northwind database with username is ‘root’ and password is ‘123456’. The connection string will be
String connectionUrl = "jdbc:mysql://192.168.1.101:3306/Northwind?" + "user=root&password=123456";
Retrieve data from a database
To get some data, I need to execute query on the MySQL and get the result back to me. First, I create stmt (Statement object) and execute query in SQL language. Then I store the result on ResultSet object and iterative show the result on the output window.
Statement stmt = null; ResultSet rs = null; //SQL query command String SQL = "SELECT * FROM Products"; stmt = con.createStatement(); rs = stmt.executeQuery(SQL); while (rs.next()) { System.out.println(rs.getString("ProductName") + " : " + rs.getString("UnitPrice")); }
Code Explanation:
- Statement objects allow you to execute basic SQL queries and retrieve the results through the ResultSet class.
- In while-loop, iterative in the ResultSet object to show result in console (ProductName and UnitPrice columns in Products table) on output window.
The example result will be similar to below.
Note: I have imported only 4 records from Products table in Northwind database.

Update data on database
To insert, update and delete records on SQL Server, you can use the code from retrieve data from database and simply change SQL command and also modify some code a little bit. On update, I must use executeUpdate(�?SQL�?) method on statement object instead executeQuery(“SQL�?) and the return value will be rows affected instead of a record set.
Example
INSERT command
// SQL insert command String strSQL = "INSERT INTO Products (ProductName,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder," + "ReOrderLevel,Discontinued) VALUES ('MyProduct','10 Kg.',1234.0000,100,50,30,0)"; int rowsEffected = stmt.executeUpdate(strSQL); System.out.println(rowsEffected + " rows effected");
UPDATE command
// SQL update command String strSQL = "UPDATE Products SET UnitPrice = 900, UnitsInStock = 55, UnitsOnOrder = 5 WHERE ProductName = 'MyProduct'"; int rowsEffected = stmt.executeUpdate(strSQL); System.out.println(rowsEffected + " rows effected");
DELETE command
// SQL delete command String strSQL = "DELETE FROM Products WHERE ProductName = 'MyProduct'"); int rowsEffected = stmt.executeUpdate(strSQL); System.out.println(rowsEffected + " rows effected");
Summary
You can download source code example testMySQL.java (Right-click on the link and select Save target As…).
But you have to change connection string to match your environment. The example code will connect to Northwind database and try to retrieve records, insert a new record, update the record and delete the record from Products table. The result is below.

Related post
- Accessing SQL Server on NetBeans using JDBC, Part II: Perform SQL Operations From Part I, I have only established a connection with local SQL Server. Next I’ll show how to retrieve and...
- Accessing MS Access 2007 on NetBeans 6.5 using JDBC, Part 4: Perform SQL Operations This article is one of the series of Accessing Access 2007 on NetBeans 6.5 using JDBC. You can see the...
- Accessing MySQL on VB.NET using MySQL Connector/Net, Part VII: Perform SQL Operations Perform SQL Operations From the previous part, I have successfully connect to world database on MySQL Server from VB.NET. Next,...
- Accessing MySQL on NetBeans using JDBC, Part I: Create a connection Introduction This tutorial show you how to use NetBeans to connect MySQL by using MySQL Connector/J, MySQL AB’s JDBC Driver...
- Accessing SQL Server on NetBeans using JDBC, Part I: Create a connection Introduction This tutorial show you how to use NetBeans to connect SQL Server (2000 and 2005) by using Microsoft SQL...
Related posts:




April 23rd, 2008 at 10:24 am
Thanks a lot! That is exactly what I needed to get jump-started on Java & MySQL.
June 3rd, 2008 at 6:57 am
Very good.
You have no idea how hard it is to find a simple explanation like this.
June 11th, 2008 at 11:00 pm
can i ask u question
why i cannot build because of the con.
The result of the compiler is above
symbol : variable con
location: class test.test
stmt = con.createStatement();
1 error
BUILD FAILED (total time: 0 seconds)
who can help me !?
June 12th, 2008 at 8:46 am
I think that you haven’t completed part1: create a connection yet. You need to create a connection to MySQL before performing any query. Try to read part1 first.
June 15th, 2008 at 9:07 pm
Thanks a lot. i had solved my problem
June 19th, 2008 at 3:32 am
Thank you for this tutorial. Very usefull indeed. I have a problem with this line:
stmt = con.createStatement();
when I write it in my Netbeans IDE, it gets red underlined, the error message says: “Cannot find symbol”
Any idea why?
NB: on the following line:
Connection con = DriverManager.getConnection(connectionUrl);
The line is underlined grey saying that the con variable is not being used. That might be the source of the problem…
I import the followings:
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Connection;
and use IDE 6.1 and MySQL 5.1, connector 5.1.6
June 20th, 2008 at 10:31 pm
Hi, Arnaud
Normally, when you see this error message, it’ll tell which symbol. For instance,
cannot find symbol
symbol : variable aaa
location: class Main
This means that you haven’t declare a variable ‘aaa’ before using it.
According to your problem, have you already declare all of variables? Try check stmt and connectionUrl. In my example above, I have declare stmt = null and connectionUrl = “jdbc:mysql…..”.
June 23rd, 2008 at 9:01 pm
@Arnaud: You have to put the code of this part inside the scope of the “try” command from Part I.
If you place the code outside, then the compiler doesn’t see the declaration for the Connection con.
June 24th, 2008 at 9:17 pm
Thanks Dory, that was the answer to my problem. As I said very new to java programming.
Sorry that was very basic.
Thank you both for your answers.
By the way anybody knows where I could find a tutorial to then bind this query (INNER JOIN through more than 1 table) to a JTable?
The NetBeans tutorials only shows how to bind a simple table to a JTable, which is not very usefull as most of my tables contains ID’s we are of no meaning to the interface use.
Thank you
July 18th, 2008 at 1:31 pm
It’s great!!! I was trying to find out a place to start JAVA MYSQL connection and found no where. All the writers assume that only professionals have the right to read their articles.
Thanks a lot for the simple but great and effective article.
October 9th, 2008 at 12:39 pm
thnks a lot
it worked for me
October 25th, 2008 at 7:59 am
Nice Job.
Obrigado!!!
October 28th, 2008 at 8:09 pm
How to insert, update and delete if we use has map method?
Can you explain about it???
October 28th, 2008 at 8:59 pm
Hi, inD_05
What do you means “map method”?
November 20th, 2008 at 5:48 am
Hi, I have followed your examples (parts I & II) and it compiles fine, BUT when invoking:
DriverManager.getConnection(url, user, pass);
it returns an error that i cant resolve by now:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
Last packet sent to the server was 0 ms ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
…
can you enlighten me here? THANKS!
November 20th, 2008 at 6:35 am
solved it
some how the “:port/” part of the conn string was causing troubles.
November 22nd, 2008 at 8:06 pm
Hi! Thanks for the great tutorial. All I need to know now is how I show the resultset on grid in Java using NetBeans
May 3rd, 2009 at 12:26 am
thanks so very mach:
muy buen tutorial.
great tutorial.
June 1st, 2009 at 7:22 pm
Cheers, Dude,
you’ve saved me loads of time!
All the best,
to
November 24th, 2009 at 9:44 am
thank’s a lot
the world needs people like you
It helps in a simple way
December 1st, 2009 at 6:20 am
Efectivamente,
muchas gracias por la demostración.
December 10th, 2009 at 11:50 am
Thanks a lot. the tutorials helped me to start my journey with Java. Expecting a lot from you genius
December 17th, 2009 at 8:20 pm
I have a table named “Ciudades” with the following structure:
cod_ciudad = int
nom_ciudad = varchar
cod_provincia = varchar
I want to retrieve data from the table with the following code but instead I get nothing.
(the connection has already been created and opened)
sSentencia = m_cConexion.createStatement();
rsResultado = sSentencia.executeQuery(“SELECT * FROM Ciudades ” +
“WHERE cod_ciudad >= ” + Integer.toString(iCodigoDesde) +
” AND cod_ciudad <= " + Integer.toString(iCodigoHasta));
What am I doing wrong?
Thanks in advance.
January 6th, 2010 at 10:42 am
Hi, Veronica
Is there any error message? First, you should try to test if the SQL query’s syntax is correct by print out the query to console window and copy it to run on the SQL Server.
February 25th, 2010 at 1:45 pm
I followed ur instruction step by step.. it successfully build but fail to run..
SQL Exception: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure.
The last packet sent successfully to the server was 0ms ago. The driver has not received any packets from the server.
Why is this so? What should I check in this case?
Java Beginner
February 26th, 2010 at 10:06 am
Hi, Sitti
Check if the MySQL is accepting the connection from your application. Or if there is any firewall blocking.
February 26th, 2010 at 4:39 pm
Hi linglom,
Nothing change when I disable the firewall… How do i check for if MySQL is accepting the connection?? Is my connection string correct?? =)
try
{
Class.forName(“com.mysql.jdbc.Driver”);
String connectionUrl = “jdbc:mysql://localhost/Northwinds?;”;
Connection con = DriverManager.getConnection(connectionUrl);
}
catch (SQLException e)
{
System.out.println(“SQL Exception: “+ e.toString());
}
catch (ClassNotFoundException cE)
{
System.out.println(“Class Not Found Exception: “+ cE.toString());
}
March 2nd, 2010 at 9:07 am
Hi, Sitti
First, I thought that your MySQL is on remote machine. So if it on the local machine, I suggest you to change the connection string “localhost” to your real ip address of the machine.
Also, have you forgot username and password on the connection string?
March 3rd, 2010 at 9:57 pm
hey the records are getting inserted in table properly,but when i am trying to retrieve a single row i am getting a blank page. when i retrieve da whole table it is retrieving… but for one particular row it is not….
my query is : “select * from stu_detail where std_rollno=+ num +”
( String num=request.getParameter(“stdrollnumber”);
when i enter student roll number i am supposed to get student name,father’s name,address and contact no in the same page inside the textboxes.
March 4th, 2010 at 10:10 pm
Hi, Halima
I suggest you print out your query on output window and copy it to run on SQL Server directly to see if there is any error or not.
March 5th, 2010 at 2:05 pm
Thank linglom.. great help… I’m able to start working on it.. ♥
March 6th, 2010 at 9:07 pm
hey i tried in other system ,its working perfectly but i dont know wat’s da error….
plz help me with other query to retrieve a record , whatever user selects…
March 8th, 2010 at 5:37 pm
hey i retrieved all da records from the database in an table(i hav 5 columns i.e,- rollnumber,name,father’s name,address,ph).I have made da rollnumber as an link, so when i click on one particular rollnumber i should get all da columns of dat record in an table so dat i can edit and update it using servlet…
How to do it….???
March 23rd, 2010 at 1:14 am
Hello Friends Feels so good to be at a place of my interest where a hope that someone can help:
I am using netbeans for my project and mysql database to create a hash table for my web search engine project
the program runs fine the Problem: is Where does the actual physical data of the mysql table is stored.
I can locate the .frm files of the table but there needs to be an .Myd file also where does it get stored??
////////////please help anyone////////////
March 31st, 2010 at 3:36 pm
Linglom, you really did well with the way you explained this. I was able to even apply it to my JApplet in Netbeans and the part where I had to add MySQL Driver to the library made the difference.
I’d thought of using this information for my Java web development in Netbeans, but although the applet was running without any error, I kept getting a “ClassNotFoundException” error message.
What I did was to use the <jsp:plugin … to insert the applet
Please, give your counsel. Thank you.
March 31st, 2010 at 3:43 pm
…
Applet compiled and was executed without any error, but when I used the tag to insert it into the web page, a “ClassNotFoundException” error message was generated and the part where the statement:
==>Class.forName(“com.mysql.jdbc.Driver”);<==
was made never got executed.
—
Just explaining better… Thank you.
March 31st, 2010 at 4:02 pm
…
Wow! This is cool
April 15th, 2010 at 3:07 am
THnX a LOT MAn…..REalLY HelPFUl ……..
GOd BlESS YoU..
April 15th, 2010 at 3:07 am
THnX a LOT MAn…..REalLY HelPFUl ……..
May 27th, 2010 at 11:09 pm
great!
Thanks for the info.
It is simple and superb!
June 17th, 2010 at 3:20 pm
Thanks a lot for this. It was simple highly useful.
July 16th, 2010 at 7:14 pm
thank youuuuuuuuuuu….soooooooooooooo much…..there was great difficulty in update query…..you solved the problem…