DB programming with Java

In this post, we will learn how to interact with Database using Java programming language. We are going to cover these:

• Installing MySQL
• Using MySQL
• Combining Java and MySQL
• Using MySQL in Netbeans
• Creating an App

Have Java installed on your machine.

Next install Python as MySQL server components might require. So, it is recommended to install Python first. (www.python.org)

Install MySQL Community Server(http://dev.mysql.com/downloads/). 

After that download, MySQL connector for Java. Download, platform independent, ZIP archive. (http://dev.mysql.com/downloads/connector/J)

Extract the ZIP file to directory like D:\connectorJ and add the complete path to the library file to CLASSPATH (like, D:\connectorJ\mysql-connector-java-5.1.30\ )




Setting up a Database:

Go to MySQL command line client with appropriate password and you should get the mysql> prompt.

To create a database(CREATE DATABASE):

mysql> create database library;

This command will create the database named 'library'. To use that library, use the following command:

mysql> use library;

Now the 'library' database is opened and you can use it for creating tables, etc.

To create tables in the Database (CREATE TABLE):

mysql> create table book (
    -> isbn varchar(13) primary key,
    -> title varchar(50),
    -> author varchar(50),
    -> price float(11));

Now the table is created.

Insert data into table (INSERT):

mysql> insert into book values ('0764508814', 'Java 2 Database Programming', 'Raghu Dayala', 24.99);

mysql> insert into book values ('9780471269236', 'MySQL and Java Programming', 'Mark Mathews', 34.99);

Querying data from table (SELECT):

mysql> select * from book;

mysql> select title, author from book;

mysql> select isbn, price from book;

mysql> select * from book where price > 25;

Update data already in table (UPDATE):

mysql> update book
    -> set price = 26.99
    -> where title = 'MySQL and Java Programming';

Remove records from table (DELETE) :

Before that, lets enter one more row in to the table.

mysql> insert into book values('9780596009205', 'Head First Java', 'Kathy Sierra', 45.99); 

Now let's delete this row from table:

mysql> delete from book where title = 'Head First Java';

To drop a table:

mysql> drop table book;


Exercises Using MySQL:

mysql> create table books (
    -> isbn varchar(13),
    -> title varchar(50),
    -> author varchar(50),
    -> price float(11),
    -> publisher varchar(50));

mysql> create table publishers (
    -> id varchar(3),
    -> name varchar(50),
    -> city varchar(50));

mysql> insert into books values ('1234567890', 'Example Book', 'Raghu', 49.99, 'Apress');
mysql> insert into books values('9876543210', 'New Book', 'ABC', 32.99, 'Prentice Hall');
mysql> insert into books values('4567890123', 'Great Java Book', 'XYZ', 25.99, 'Mc Graw Hill');


mysql> insert into publishers values ('001', 'Apress', 'New York City');
mysql> insert into publishers values('002', 'Prentice Hall', 'California');
mysql> insert into publishers values('003', 'Mc Graw Hill', 'Washington');

Note:

Please make sure you have added the path to the driver mysql-connector-java-5.1.30-bin.jar to the CLASSPATH.

However,  IDEs and app servers ignore it.

The right way to do it depends on how you're using it:

1. If you're running inside an IDE like Eclipse or IntelliJ, you have to add the JAR to a library.
2. IF you're running in a command shell, use the -p option for javac.exe when you compile and java.exe when you run.
3. If you're using it in a web app, you can start by putting it in the WEB-INF/lib directory of your WAR file. If you're using a servlet/JSP engine like Tomcat 6, put it in the Tomcat /lib directory.

Simplest way to check if things are all fine:

Test Connection to MySQL using Java:
 
public class TestMySQLConnection {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            System.out.println("Connected to MySQL");
        }
        catch (Exception e) {
            System.out.println("Cannot connected to MySQL");
        }
    }
}

Connecting to DB using Java:

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package helloworld;

/**
 *
 * @author rdayala
 */

import java.sql.*;

public class ConnectDB {
    Connection connection;
   public ConnectDB() {
      try {
         Class.forName("com.mysql.jdbc.Driver").newInstance();
      }
      catch (Exception e) {
         System.out.println("Could not load driver.");
      }
   }

   public void connectToDB() {
      try {
         connection = DriverManager.getConnection(
                                    "jdbc:mysql://localhost/library", "raghu", "chotu123");
         System.out.println("Connected to MySQL database.");
      }
      catch (Exception e) {
         System.out.println("Cannot connect to database.");
      }
   }

   public static void main(String[] args) {
      ConnectDB conn = new ConnectDB();
      conn.connectToDB();
   }
}

Executing the SQL query within Java code:


public void execSQL()
   {
       try {
           Statement stmt = connection.createStatement();
           ResultSet rs = stmt.executeQuery("select * from books;");
          
           while(rs.next()) {             
               System.out.println(rs.getString(1) + "  " + rs.getString(2));
           }
       }
       catch(Exception e)
       {
           System.out.println("Error executing the query");
       }
   }

Updating table data within Java code:

public void updateSQL()
   {
       try {
           Statement stmt = connection.createStatement();
           String update = "update books set title = 'Java: How to Program'";
           update += " where isbn = '9876543210'";
           int success = stmt.executeUpdate(update);
                     
       }
       catch(Exception e)
       {
           System.out.println("Error executing the query");
       }
   }

Inserting Data into table from within Java code:

public void execSQL() {
      try {
         Statement stmt = connection.createStatement();
         BufferedReader input = new BufferedReader(new InputStreamReader(System.in));
         System.out.print("Enter the isbn: ");
         String isbn = input.readLine();
         System.out.print("Enter the title: ");
         String title = input.readLine();
         System.out.print("Enter the author: ");
         String author = input.readLine();
         System.out.print("Enter the price: ");
         String p = input.readLine();
         double price = Double.parseDouble(p);
         System.out.print("Enter the publisher: ");
         String pub = input.readLine();
         String insert = "Insert into books values (" + "'" + isbn + "','" + title +
                         "','" + author + "'," + price + ",'" + pub + "')";
         System.out.println(insert);
         int inserted = stmt.executeUpdate(insert); // returns 1 for success, 0 for failure
         if (inserted > 0) {
            System.out.println("Successfully inserted " + inserted + " row.");
         }
      }
      catch (Exception e) {
         System.out.println("Error executing SQL");
      }
   }


Deleting Data from Table from within Java code: 

public void execSQL() {
      try {
         Statement stmt = connection.createStatement();
         BufferedReader input = new BufferedReader(new InputStreamReader(System.in));
         System.out.println("Enter a title: ");
         String title = input.readLine();
         String delete = "delete from books where title = '" + title + "'";
         System.out.println(delete);
         int deleted = stmt.executeUpdate(delete); // returns 1 for success, 0 for failure
         if (deleted > 0) {
            System.out.println("Successfully deleted " + deleted + " row.");
         }
      }
      catch (Exception e) {
         System.out.println("Error executing SQL");
      }
   }


 Accessing Oracle DB from Java:

// FLOW:
// Load Driver --> DriverManager.getConnection --> createStatement -->
// Execute Query --> ResultSet

import java.sql.*;

public class DatabaseAccessExample {
   
    // STEP 1: JDBC driver name and database URL
    static final String JDBC_DRIVER = "oracle.jdbc.OracleDriver"; 
    static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
   
    //  Database credentials
   static final String USER = "raghu";
   static final String PASS = "raghu";
  
   public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try
        {
                      
           //STEP 2: Register JDBC driver. The below statements loads the driver class 
           // 'oracle.jdbc.OracleDriver' to be available for DriverManager instance
           Class.forName(JDBC_DRIVER);
           //STEP 3: Open a connection
           System.out.println("Connecting to database...");
           conn = DriverManager.getConnection(DB_URL,USER,PASS);
           //STEP 4: Execute a query
           System.out.println("Creating statement...");
           stmt = conn.createStatement();
           String sql;
           sql = "SELECT emp_id, emp_name, join_date FROM Employee";
           ResultSet rs = stmt.executeQuery(sql);
           //STEP 5: Extract data from result set
           while(rs.next())
           {
              //Retrieve by column name
              int id  = rs.getInt("emp_id");      
              String first = rs.getString("emp_name");
              Date joindate = rs.getDate("join_date");

              //Display values
              System.out.print("Emp ID: " + id);        
              System.out.print(", Name: " + first);
              if(joindate != null)
                 System.out.println(", Join Date: " + joindate.toString());
              else
                  System.out.println(", Join Date: " + null);
           }
          
           //STEP 6: Clean-up environment
           rs.close();
           stmt.close();
           conn.close();
        }
        catch(SQLException se)
        {
           //Handle errors for JDBC
           se.printStackTrace();
        }
        catch(Exception e)
        {
           //Handle errors for Class.forName
           e.printStackTrace();
        }
        finally
        {
                //finally block used to close resources
                try{
                   if(stmt!=null)
                      stmt.close();
                }catch(SQLException se2){
                }// nothing we can do
                try{
                   if(conn!=null)
                      conn.close();
                }catch(SQLException se){
                   se.printStackTrace();
                }//end finally try
        }//end try
       
        System.out.println("Goodbye!");
       
    }//end main
}