JDBC Test Program
— print (last updated: Nov 8, 2009) — print

Select font size:
Download the TestJDBC.ziparchive. In order to run the project we assume that MySQL has been installed so that the test database is accessible to the user guest with no password.

MySQL Driver

The MySQL/Connector J driver file is included in the NetBeans distribution as one of the libraries, so there is no need to install it separately. NetBeans will package this driver JAR file along with the project JAR file to produce a truly distributable executable in the project's dist folder.

Importing into NetBeans

The structure of the TestJDBC archive is intended to fit into the NetBeans framework, where the source code all resides in the src subfolder. Unfortunately, there appears to be no way to import this zip archive directly and so the importation of this and all subsequent projects requires a somewhat convoluted approach.

NetBeans Installation/Execution

  1. Extract the zip file TestJDBC.zip as the folder TestJDBC (and move if necessary) into the default NetBeans application folder. For Windows this folder is 
    My Documents\NetBeansProjects\
    for Linux it is 
    ~/NetBeansProjects/
  2. Open a New Project and select Java Project with Existing Sources, click Next. Give it the name TestJDBC (it must be the name of the project folder) and click Next.
  3. In the Existing Sources window, click the Add Folder... button. It should open to the TestJDBC folder with the src folder visible. Select src. Then OK and, back in previous window, Finish.
  4. Add the MySQL driver JAR file as a project library:
  5. Then run by selecting Run Run Main Project. The first time you will be asked to identify the main class, which is correctly determined to be testjdbc.Main.

Database/table settings

The assumption we're making about the database settings are these: 
host:     localhost
database: test
user:     guest
password: <NONE>
table:    books
If these settings are not already created, do so from the shell as follows: 
mysql -u root               (add -p if password-protected root)
mysql> create user guest@localhost;
mysql> create database test;
mysql> grant all on test.* to guest@localhost;
mysql> quit;
If you want to have a password for guest, the replacement command would be: 
mysql> create user guest@localhost identified by 'some password';

Properties files for database/table settings

If you want to use different settings, you must edit the following two Java properties files which refer to the above settings: 
models.db/database.properties
models/books.properties

Source Files






models/db/database.properties




url=jdbc:mysql://localhost/test
user=guest
password=








models/db/Database.java




package models.db;

import java.sql.*;
import java.util.*;

public class Database {
  private String url;
  private String user;
  private String password;

  private Connection cx = null;

  public Database() {
    ResourceBundle props = ResourceBundle.getBundle("models.db.database");
    url = props.getString("url");
    user = props.getString("user");
    password = props.getString("password");
  }
  
  public Connection connect() throws Exception {
    if (cx == null || cx.isClosed()) {
      cx = DriverManager.getConnection(url, user, password);
    }
    return cx;
  }
}








models/Book.java




package models;

public class Book {
  public int id;
  public String title;
  public String type;
  public int qty;
  
  // This constructor is not public, it's intendeded to only
  // be used by the Books class.
  Book(int id, String title, String type, int qty) {
    this.id = id;
    this.title = title;
    this.type = type;
    this.qty = qty;
  }

  public Book(String title, String type, int qty) {
    this.title = title;
    this.type = type;
    this.qty = qty;
  }

  @Override
  public String toString() {
    return "[" + id + ", " + title + ", " + type + ", " + qty + "]";
  }
}








models/books.properties




table_def=id INTEGER AUTO_INCREMENT NOT NULL\
,title VARCHAR(80) NOT NULL\
,type ENUM('paper','cloth')\
,qty INT\
,PRIMARY KEY(id)\
,UNIQUE(title)








models/Books.java




package models;

import models.db.*;
import java.sql.*;
import java.util.*;

public class Books {
  // assume the table name is "books", the lower-case of the class name
  private String table_name = getClass().getSimpleName().toLowerCase();
  private String table_def;
  private Database db = new Database();

  public Books() throws Exception {
    ResourceBundle props = ResourceBundle.getBundle("models.books");
    table_def = props.getString("table_def");
  }
  
  public void create() throws Exception {
    Connection cx = db.connect();

    String sql_op = "DROP TABLE IF EXISTS " + table_name;

    Statement st = cx.createStatement();
    
    st.executeUpdate(sql_op);

    sql_op = "CREATE TABLE " + table_name + "(" + table_def + ")";

    st.executeUpdate(sql_op);
  }

  public void insert(List<Book> books) throws Exception {
    Connection cx = db.connect();

    String sql_op = "INSERT INTO " + table_name
      + "(title,type,qty)"   + " VALUES (?, ?, ?)";

    PreparedStatement st = cx.prepareStatement(sql_op);

    for (Book book: books) {
      st.setString(1, book.title);
      st.setString(2, book.type);
      st.setInt(3, book.qty);
      st.executeUpdate();
    }
  }

  public List<Book> fetchAll() throws Exception {
    Connection cx = db.connect();

    String sql_op = "SELECT * FROM " + table_name;

    Statement st = cx.createStatement();

    ResultSet rs = st.executeQuery(sql_op);

    List<Book> L = new LinkedList<Book>();
    while (rs.next()) {
      int id  = rs.getInt("id");
      String title = rs.getString("title");
      String type  = rs.getString("type");
      int qty      = rs.getInt("qty");
      L.add( new Book(id,title,type,qty) );
    }
    return L;
  }
}








testjdbc/Main.java




package testjdbc;

import models.*;
import java.util.*;

public class Main {

  public static void main(String[] args) {
    try {
      Books books = new Books();

      System.out.println("****** create");
      books.create();

      System.out.println("****** populate");
      Book[] data = new Book[]{
        new Book("Java in a Nutshell", "paper", 20),
        new Book("Programming 'Perl'", "paper", 14),
        new Book("Multimedia Systems", "cloth", 35),
        new Book("Data Structures in Java", "cloth", 27),
        new Book("Java Foundation Classes", "paper", 51)
      };
      books.insert( Arrays.asList(data) );

      System.out.println("****** dump");
      List<Book> L = books.fetchAll();
      for (Book book : L) {
        System.out.println("retrieve: " + book);
      }
    } catch (Exception x) {
      x.printStackTrace();
    }
  }
}

Database Validation

See the database table in Navicat

Fire up Navicat. For basic instructions see the Navicat section in either of the MySQL on Windows or MySQL on Unbuntu documents. Open the root connection. Open the test database. Open the Tables and open (double-click) the books table.

NetBeans' built-in tools for manipulating the database

NetBeans also provides an alternative to the mysql command-line interpreter. It already includes a version of the MySQL Connector/J driver and has built-in capabilites to manipulate a MySQL database, presumably through JDBC calls. Here's how to use it:
  1. Open the Services window.
  2. Open up the Databases, then Drivers indexes, right-click on the MySQL driver to select Connect Using... from the popup.
  3. In the window (Basic Setting), with the Field Entry radio button selected, fill in these fields: 
    Host:       localhost
Database:   test
User Name:  guest
    Click OK.
  4. Afterwards, a new "jdbc:mysql..." database entry should appear. Open it up to reveal entries for Tables, Views, Procedures.
  5. Open up Tables to see the books table.
  6. Right-click on the books table and select View Data.
You can run queries on the books table by right-clicking and selecting Design Query to obtain a simple "query designer" user interface.

© Robert M. Kline