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

Tutorial 1 : Creating a JDBC connection with Swing

Tutorial 1: Step by Step Database Connection with SWING

Step 1: Create a class name MySQLConnector

Step 2: Add the dependencies

import java.sql.*; // for SQL

import javax.swing.*; // for Design

Step 3: // Object for database connection

    Connection conn = null;

Step 4:// Create a class for Connect

    public Connection ConnectDatabase(){

   

        try{

  

           }catch(Exception ex){

            JOptionPane.showMessageDialog(null,ex);

        }

}

Step 5:    Inside try catch block

                 STEP 2: Register JDBC driver

                This will load the MySQL driver, each DB has its own driver

            System.out.println("Register JDBC driver");

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

Step 6-An optional go to the main class and define the variables to be used

   // JDBC driver name and database URL

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

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

   

   // Database credentials

   static final String USER = "root";

   static final String PASS = "";

//STEP7: Open a connection

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

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

      conn = DriverManager.getConnection("jdbc:mysql://localhost/studenttest","root","");

      return conn;

        }

//Step 8 Catch block

catch(Exception ex){

            JOptionPane.showMessageDialog(null,ex);

            return null;

        }

Step9: Create a Table Staff for login

·         Staff_ID

·         NAME

·         AGE

·         USERNAME

·         PASSWORD

Step 10: Create a New JFRAME Form

·         name it Login_form

·         Add two labels username and password

·         Add a text field

·         Add a Password Field

·         Add a button

Change the variables name

·         username : txt_username

·         password : txt_password

·         button : btn_login

Step11: Put in Panel

Select all the label, field and button

·         right click and enclose in panel

·         Go to properties of panel and select title border

//Step: 12 import the following librarys for Login Form

import java.awt.*;

import java.awt.event.WindowEvent;

import java.sql.*;

import javax.swing.*;

//Step 13: Declare the connection and connector variables for Login Form

    Connection conn = null;

    PreparedStatement pst = null;

    ResultSet rs = null;

 //Step 14 A Closing Event with a Function is created to close the form

    public void close(){

        WindowEvent winClosingEvent = new WindowEvent(this,WindowEvent.WINDOW_CLOSING);

        Toolkit.getDefaultToolkit().getSystemEventQueue().postEvent(winClosingEvent);

    }

Step 15: Create a form open event

// event for window opened

    // to do it right click on outside panel then event,-> windowEvent -> windowopened

    private void formWindowOpened(java.awt.event.WindowEvent evt) {                                 

        // TODO add your handling code here:

    }

Step 16: // Step 16: event for window opened

·         // to do it right click on outside panel then event,-> windowEvent -> windowopened

    private void formWindowOpened(java.awt.event.WindowEvent evt) {                                 

        // for the connection the MYSQL

        conn = MySQLConnector.ConnectDatabase();

    }         

Step 17:

·         GO to design

·         Then on button

·         Right Click -> Events -> Mouse -> buttonCLikced

 //when the login button is pressed

    private void btn_loginMouseClicked(java.awt.event.MouseEvent evt) {                                      

        // TODO add your handling code here:

    }

 //Step 18: Write SQL Statements

        String sql= "select * from staff where username=? and password=?";

       

 //Step 19: Try Catch Blocks

        try{

           

        }catch(Exception esql){

           

        }

//////////

catch(Exception esql){

            //step20: print exception

            JOptionPane.showMessageDialog(null, esql);

        }

  // Step 21: Create the Statement object

//The createStatement() method of Connection interface is used to create statement.

 // The object of statement is responsible to execute queries with the database.

            pst =conn.prepareStatement(sql);

 //Step 22: Get the Variable names from entered data

            pst.setString(1,txt_username.getText());

            pst.setString(2,txt_password.getText());

 //Step 23:  // Result set get the result of the SQL query

     //Execute the query : The executeQuery() method of Statement interface

      //is used to execute queries to the database.

       //This method returns the object of ResultSet that can be used to get all the records of a table.

      

            rs = pst.executeQuery();

//Step 24:  Verification of results Retrieved

            if(rs.next()){ // there is some record received only in case of valid username and password

                JOptionPane.showMessageDialog(null,"You Have ENTERED Correct Information");

                close();

            }

//Step 25: else{ // step 25: else portion if the username is not valid

                JOptionPane.showMessageDialog(null,"You Have ENTERED In-Correct Information");

            }

//Step 26: //default close information to dispose

·         Go to design -> main screen -> Properties

·         DeafultCloseOperation from Close to Dispose to see the screen staying there

// Step 27 : Add the sql library

·         Go to library on left pane

·         Select add library

·         then add my-sql jar

//Step 28: Create a new JFrameFrom for showing Staff info

·         rightclick -> New -> JFrameForm and name it as staffinfo

// Step 29 Add this to code to show the staff pane

                Staff_Info s_info = new Staff_Info();

                s_info.setVisible(true);

//Step 30: See Main Function

//Step 31: Right Click Run the file

·         enter correct and incorrect information check the command line

//Step 32: go to staffinfo jframe and add the table from the design pane

·         //then rename by

·         right click on table-> change variable name

·         then enter the name   tbl_staffinfo

//Step 33: Table connector driver rs2xml.jar

www.filesdownload.info/file/69Se66yD/rs2xml.html

//Step 34: Add it to project

go to library -> then properties -> add jar/folder and select rs2xml.jar

//step 35: Go to Staff_info.java

// Step 36: Add the Header files

import java.sql.*;

import javax.swing.*;

import net.proteanit.sql.DbUtils;

 //Step 37:  Define the Connection Statements

    Connection conn = null;

    ResultSet rs = null;

    PreparedStatement pst = null;

 // Step 38: the connection statement

        conn = MySQLConnector.ConnectDatabase();

//Step 39: create a population method

   

    private void Update_table(){

        try{

       

        }catch(Exception ex){

            JOptionPane.showMessageDialog(null, ex);

        }

    }

   //step 40:  select queries

            String sql = "select * from staff";

            pst= conn.prepareStatement(sql);

            rs= pst.executeQuery();

    //Step 41: now use the variable name for the table given when the Jtable was dragged

            tbl_staffinfo.setModel(DbUtils.resultSetToTableModel(rs));

//Step 42: Call the Populate_table()method

        Populate_table();