Tutorial 2: Step by Step Database Connection and Operations

Tutorial 2: Step by Step Database Connection and Operations

// Step 1: Add the SQL Library

import java.sql.*;

// Step2 : Add Jar file

·         right click library -> Properties -> Add Jar / Folder -> MySql connector.jar

//Step 3: JDBC driver name and database URL

   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; 

   static final String DB_URL = "jdbc:mysql://localhost/studenttest";

// Step 4:  Database credentials

   static final String USER = "root";

   static final String PASS = "";

// Step 5: Define Connection and Query Variables

        Connection conn = null;

         Statement stmt = null;

         ResultSet rs = null;

// Step 6: Try - Catch Block

        

         try{

            

         }catch(Exception ex){

//Step 7Handle errors for SQL and other exception

      ex.printStackTrace();

         }

  // STEP 8: Register JDBC driver

      Class.forName("com.mysql.jdbc.Driver");

 //STEP 9: Open a connection

      System.out.println("Connecting to database...");

      // connection statement

      conn = DriverManager.getConnection(DB_URL,USER,PASS);

 //STEP 10 : Creating a SQL Statement

      System.out.println("Creating SQL Statement...");

      stmt = conn.createStatement();

      String sql;

      sql = "SELECT id, name, age FROM staff";

// Step 11: Exectuting the SQL Query

      rs = stmt.executeQuery(sql);

 //STEP 12: Get the Data From RS

      while(rs.next()){ // while rs has the data

//Step 13: Retrieve the data column by column

         int id  = rs.getInt("id");

         int age = rs.getInt("age");

         String name = rs.getString("name");

       

//Step 14: Display the Results stored

         System.out.print("ID: " + id);

         System.out.print(", Name: " + name);

         System.out.println(", Age: " + age);

        

        

//STEP 15: Closing up all the connection and result set to clear the environment

                rs.close(); // result set

                stmt.close(); // sql query

                conn.close(); // connection

//Step 16: Error checks

·         //variable name worng

·         //putting closing statement inside checks

Step 17: Run the file

Step 18: java and SQL date time options

Step19: Insert Example

create a new java class InsertExample

// Step 20: Add the Sql Library

import java.sql.*;

public class InsertExample {

//Step 21: JDBC driver name and database URL

   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; 

   static final String DB_URL = "jdbc:mysql://localhost/studenttest";

  

// Step 22:  Database credentials

   static final String USER = "root";

   static final String PASS = "";

    public static void main(String[] args) {

       

    }

 

// Step 23: Define Connection and Query Variables

        Connection conn = null;

         Statement stmt = null;

         ResultSet rs = null;

        

// Step 24: Try - Catch Block

        

         try{

//STEP 25: Register JDBC driver

      Class.forName("com.mysql.jdbc.Driver");

     

 //STEP 26: Open a connection

      System.out.println("Connecting to database...");

 //Step 27:  connection statement

      conn = DriverManager.getConnection(DB_URL,USER,PASS);

            

//STEP 28 : Creating a SQL Statement

      System.out.println("Creating SQL Statement...");

      System.out.println("Inserting records into the table...");

      stmt = conn.createStatement();

     

// Step 29: Creating Insert Statement

      String sql = "INSERT INTO studentinfo " +

                   "VALUES (3, 'Amjad', 18, 'Jeddah')";

      stmt.executeUpdate(sql);

      sql = "INSERT INTO studentinfo " +

                   "VALUES (4, 'Nawaf', 25, 'Yanbu')";

      stmt.executeUpdate(sql);

     

      //just the print out

      System.out.println("Inserted records into the table...");

  

  //STEP 31: Closing up all the connection

                    stmt.close(); // sql query

                conn.close(); // connection 

                                }catch(Exception ex){

//Step 30: Handle errors for

                ex.printStackTrace();

         }   

     

             

         }catch(Exception ex){

Now create program for update, delete, whereas , like and sorting