Category Archives: Java MySQL

4: More on Statements: Queries and Updates

There are two main ways of manipulating your database: executeQuery and executeUpdate

executeQuery returns a resultSet and can be used to search your database.

executeUpdate returns an integer. It can be used to to INSERT, UPDATE or DELETE files. The number that it returns is the number of records updated. 0 means no records were updated.

1 executeQuery

As you’ve already see, the following finds all the records in table Book

ResultSet rs = statement.executeQuery("SELECT * FROM BOOK");

The following code finds all the books by ‘Joe Bloggs’

ResultSet rs = statement.executeQuery("SELECT * FROM BOOK WHERE author = 'Joe Bloggs' ");

2 executeUpdate

The following code will add a record to a table Book. It assumes that any other fields in the table have default values set.

statement.executeUpdate("INSERT INTO BOOK(title,Author,isFiction) VALUES ('Spot', 'Eric Hill',TRUE)");

This code changes the author of ‘Spot’ to ‘Joe Bloggs’

statement.executeUpdate("UPDATE BOOK SET Author = 'Joe Bloggs' WHERE title ='Spot' ");

This code deletes all the books with the title ‘Spot’

statement.executeUpdate("DELETE FROM BOOK WHERE title = 'Spot'");

See Also:

3: Using Statements

You now have a connection to your database: you’ll want to do something with it. Java allows you to prepare SQL statements and pass them to the database.

  • First, create a Statement attached to your connection. This is a confusing term as it will be an empty statement- you’ll add the actual query later on.
1:  Statement statement = conn.createStatement();
  • Now you have created the statement you can use it to run a query. Here’s an example.
1:  statement.executeQuery("SELECT * FROM BOOK");

Note that the statement is written in SQL. If you don’t understand it, go back and revise on sqlzoo.net now.

Your code should look something like the following. It should run without errors, but it won’t print anything out.

 1:  Connection conn;
 2:  
 3:  try
 4:  {
 5:  Class.forName("com.mysql.jdbc.Driver").newInstance(); //load driver
 6:  conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/library","root", "");
 7:  //establish connection to database, user, password
 8:  
 9:  Statement statement = conn.createStatement();
10:  statement.executeQuery("SELECT * FROM BOOK"); 
11:  
12:  }
13:  catch(Exception e)
14:  {
15:  System.err.println(e.getMessage());
16:  }
17:

Important: Remember to import the java.mysql.* libraries, not the com.mysql.* ones.

  • Now you have (hopefully) run the query, you need to do something with it. You need to create a ResultSet object to hold the results
1:  ResultSet rs = statement.executeQuery("SELECT * FROM BOOK");
  1. Now you can use a while loop to retrieve data from the ResultSet as follows
1:  while(rs.next())
2:  {
3:  System.out.println("Title: " + rs.getString("title") + "Author: " + rs.getString("author"));
4:  }

rs.next() is an interesting method, you need to understand how it works.

When the result set is first made, a pointer is set to before the first record. Calling rs.next() checks to see if the next record exists. If it does, it moves the pointer to that record and returns true. You can now retrieve data from that record as shown.

TIP: A common mistake is to try and retrieve data from the result set without calling rs.next() first!

2: Using JDBC to connect to MySQL

Before you begin, make sure that the MySQL database you are trying to connect to is up and running!

  • You need to add a JAR to your netbeans project. Right Click on the libraries node in the projects window and select Add JAR/Folder

The connector can found in your netbeans folder. Here is a typical filepath: C:Program Files | NetBeans 6.8 | ide 12 | modules | ext . The current version is mysql-connector-java-5.1.6-bin. You can also download the connector from MySQL

  • In a suitable Java class, add the code to load the driver
1:  try 
2:  {
3:          Class.forName("com.mysql.jdbc.Driver").newInstance();
4:  } 
5:  catch(Exception e) 
6:  {
7:          System.err.println(e.getMessage());
8:  }
  • Run the code. If everything is okay it should build and run, although nothing will happen. You have succesfully loaded a JDBC driver. (There are many JDBC drivers, suitable for connecting to to different databases. com.mysql.jdbc.Driver is (obviously) a mysql driver).
  • Now make a connection to the database
1:  Connection con = DriverManager.getConnection("jdbc:mysql://lamp1:3306/lampy", "lampy", "lampeter");

This connects to a database lampy, running on the lamp1 server. The username is lampy, the password lampeter. Note the port number 3306, usual for a MySQL database

1:  Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/library","root", "");

The above connects to a database library running on XAMPP. The username is root, the password empty, the default on XAMPP

Important: import the java.sql.Connection library, not the com.mySQL one.

  • If you have done all the above correctly, your program should run with no errors. You now need to learn how to read to and from the database.

1: Checklist – Before you start

This tutorial assumes you are using Netbeans and a MySQL database.

You can use a LAMP server or an XAMPP server to host the database, there are plenty of instructions elsewhere on how to do this.

Set up a table on the database with the following structure

ID
Surname varchar(50)
Forename varchar(50)
Sex varchar(1)
Date of Birth Date

Here’s the MySQL for the table

CREATE TABLE IF NOT EXISTS `tblPupil` (
  `ID` int(11) NOT NULL auto_increment,
  `Surname` varchar(50) NOT NULL,
  `Forename` varchar(50) NOT NULL,
  `Sex` varchar(1) NOT NULL,
  `DOB` date NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

If you need a little practice on SQL, try http://sqlzoo.net/ At the very least, do Section 1 Select and Section 2a More Practice with Select