derby Lab JDBC

Steps to add derby.jar in intelliJ

Derby.jar

Add jar file in “lib” folder

“derby-10.13.1. jar”

Configure of Database

Goto “File”  Menu

Goto “ Project Structure”

Goto “ Project Settings” -> Select “ Modules”

Then Select “Dependencies” Tab

Then Click on ( + ) on right side to add “ Jar or directories” Option & “derby.jar”

Apply -| Click on Ok.

1.  CreateTable.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class CreateTable {

    public static void main(String[] args) throws Exception {

        Connection con = DriverManager.getConnection("jdbc:derby:D:\\Notes\\TU\\bca 6 th sem\\db\\testdb;create=true");
        //System.out.println("Connected to Derby Database!");
        Statement st = con.createStatement();
        st.executeUpdate("create table publishers( publisher_id char(6),name char(30), url char(80) )");
        System.out.println("table created");
        st.close();
        con.close();


    }

}

 

2.  AddTable.java

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;



public class AddTable {



    public static void main(String[] args) throws Exception {



        Connection con = DriverManager.getConnection("jdbc:derby:D:\\Notes\\TU\\bca 6 th sem\\db\\testdb;create=true");

        //System.out.println("Connected to Derby Database!");



        PreparedStatement ps = con.prepareStatement("insert into Publishers(publisher_id, name, url) values (?,?,?)");

        ps.setString(1, "0201");

        ps.setString(2, "Addison-wesleey");

        ps.setString(3, "www.aw-bc.com");



        ps.executeUpdate();

        ps.close();





        System.out.println("table inserted");



        con.close();





    }



}

 

3.  ListTablePublishers.java

 

import java.sql.Connection;

import java.sql.DriverManager;



import javax.sql.rowset.CachedRowSet;

import javax.sql.rowset.RowSetFactory;

import javax.sql.rowset.RowSetProvider;



public class ListTablePublishers {



    public static void main(String[] args)  throws Exception{

        // TODO Auto-generated method stub



        RowSetFactory factory = RowSetProvider.newFactory();

        CachedRowSet crs = factory.createCachedRowSet();



        String url = "jdbc:derby:D:\\Notes\\TU\\bca 6 th sem\\db\\testdb";

        crs.setUrl(url);

        crs.setCommand("select * from Publishers");

        crs.execute();

        while(crs.next())

            System.out.println(crs.getString("url"));



        crs.close();





    }



}

 

 

4.  TestConnection.java

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

 

public class TestConnection {

 

       public static void main(String[] args) throws SQLException {

             

              Connection conn = DriverManager.getConnection("jdbc:derby:D:\\Notes\\TU\\bca 6 th sem\\db\\testdb;create=true");

              System.out.println("Connected to Derby Database!");

             

              Statement stmt = conn.createStatement();

            

              //Creating a table in Derby database

             String query = "CREATE TABLE Books( "

                + "Title CHAR(60), "

                + "ISBN CHAR(13), "

                + "Publisher_Id CHAR(6), "

                + "Price DECIMAL(10,2))";

             stmt.execute(query);

             stmt.close();

          

             System.out.println("Table created");    

              conn.close();

       }

 

}

 

5.  CreateGreatingTable.java

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;



public class CreateGreatingTable {



    public static void main(String[] args) throws SQLException {

        // TODO Auto-generated method stub

        Connection conn = DriverManager.getConnection("jdbc:derby:D:\\Notes\\TU\\bca 6 th sem\\db\\testdb;create=true");

        System.out.println("Connected to Derby Database!");





        Statement stmt = conn.createStatement();

        //Creating a table in Derby database

      

      

      String query = "CREATE TABLE Greetings(Message CHAR(20))";

         stmt.execute(query);

         

          query = "INSERT INTO Greetings VALUES('Hello, World')";

            stmt.execute(query);

         stmt.close();

         System.out.println("Table created");

       

       

        ResultSet rs = stmt.executeQuery("Select * from Greetings");

        System.out.println("Contents of the table Greetings table:");

        while(rs.next()) {

            System.out.print(rs.getString("Message"));

            System.out.println();

        }



        conn.close();

    }



}