How to Perform a Record Search in a Java Application

Searching for records in a Java application is a common database operation involving JDBC (Java Database Connectivity) classes. The tasks needed for the search (or any other database-related task) are the same as for non-Java platforms: connect to a database, run a query on that database, then loop through the resulting record set.

Instructions

Things Youll Need:

• Database-creation application (e.g. MS Access)
• IDE (integrated development environment)
• JDK (Java Development Kit) from java.sun.com

1. Step 1

   Create a test database and table. Open up Microsoft Access and select File>Blank database. (You can use other database-creation applications; the instructions are generally the same.)
   
   Create a table with the following fields: "title," a text field, "director," another text field, and "year_released," an int field.

2. Step 2

   Enter the following to create two rows of data for the table. Row one has "star wars," "lucas," and 1977, corresponding to the title, director and year_released fields you just created. Row two has "excalibur," "boorman," and 1980.
   
   Choose File>Save As to initiate saving the database. Enter "movies" when prompted for the name of the table, and enter "mydb" for the name of the database. Accept the .accdb file extension.

3. Step 3

   Create a Data Source Name (DSN) from the database you just created, so the Java JDBC-ODBC driver can talk to it: Click Start, enter "ODBC," then click the Data Sources (ODBC) icon when it appears.
   
   On the Data Source Administrators User DSN tab, click Add, then choose the Microsoft Access Driver with extension *.accdb from the list that appears. In the Microsoft Access Setup window, enter "mydb" for the Data Source Name, then press Select.
   
   Navigate to the mydb.accdb database file you created earlier, and click OK to accept your selections and back out of the dialog boxes. Close the Data Source Administrator.

4. Step 4

   Create a new Java project using your preferred IDE (integrated development environment). (NetBeans will be used for the remaining steps, which will generally apply to other IDEs like Eclipses or Aptanas.)
   
   Enter "mydb" for the project name and allow the IDE to create a Main class file.

5. Step 5

   Enter the following program code in the code window:
   
   //////////////////////////////////////////////////////////////////
   package mydb;
   
   import java.sql.*;
   
   public class Main {
   
   public static void main(String[] args) throws Exception {
   //get a connection from driver manager
   //create a blank query
   //execute an sql stmt on that query
   Connection cxn = DriverManager.getConnection("jdbc:odbc:mydb");
   Statement sm = cxn.createStatement();
   ResultSet records = sm.executeQuery("SELECT * from movies where year_released=1977");
   String title="";
   String director="";
   int year=0;
   while (records.next()) {
   title = records.getString("title");
   director = records.getString("director");
   year = records.getInt("year_released");
   System.out.printf("title:%s, director:%s, year:%d%n", title, director, year);
   }
   records.close();
   }
   }
   
   //////////////////////////////////////////////////////////////////

6. Step 6

   Build and run the program: select Run>Run File, and notice the results of the query appear in the output window.

Related Post: