Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Accessing the Database with JDBC

In this article you will see a fun mode to access the database in java with JDBC.

Mr. JDBC, How do you Access the Database?

Who am I?

My name is java Database Connectivity also known as JDBC and I provide a standard library for accessing relational databases

JDBC Drivers

JDBC consists of two parts:

  • JDBC API is a purely Java-based API
  • JDBC Driver Manager which communes with vendor-specific drivers that carries out the real communication with the database.
JDBC Explanation

Fundamental Steps in using JDBC:

  • Load the driver: This is not required in Java 6, so Java 6 needs only 6 steps.
  • Define the Connection URL
  • Establish the Connection
  • Create a Statement object
  • Execute a query
  • Process the results
  • Close the connection

Step 1: Let us LOAD THE DRIVER

Loading of a Driver is not required in java SE 6.0 and later (JDBC 4.0 and later), here the driver is automatically loaded. If we talk about Java 5 and earlier, here we need to load the driver class only that has a static initialization block that makes an instance and registers it with the Driver manager.

try {
Class.forName("com.mysql.jdbc.Driver");
Class.forName("oracle.jdbc.driver.OracleDriver");
} 
catch (ClassNotFoundException cnfe)
{
System.out.println("Error loading driver: " cnfe);
}

Step 2: Time to DEFINE THE CONNECTION URL

In order to define the connection URL with the Remote databases, find below the details for the same :

Format is “jdbc:vendorName:…”

  • Address contains hostname, port, and database name
  • Exact details given by supplier of JDBC driver

Connection with the Embedded Derby database details is as follows:

- The “Java DB” (i.e., Apache Derby) is bundled with Java 6 and can be used for a database embedded in the same Java VM that runs the app server.

- Format is “jdbc:derby:databaseName”

Here is the illustration for the above described points:

String host = "dbhost.myorganization.com";
String dbName = "someName";
int port = 1234;
String mySqlUrl = "jdbc:mysql//" + host + ":" + port +
"/" + dbName;
String embeddedDerbyUrl = "jdbc:derby" + dbName;

Step 3: We are ready to establish the Connection now

Below lists the details to get the main connection:

Properties userInfo = new Properties();
userInfo.put("user", "jay_debesee");
userInfo.put("password", "secret");
Connection connection = DriverManager.getConnection(mySqlUrl, userInfo);

Alternatively, you can look up info about the database:

DatabaseMetaData dbMetaData =connection.getMetaData();
String productName =dbMetaData.getDatabaseProductName();
System.out.println("Database: " + productName);
String productVersion = dbMetaData.getDatabaseProductVersion();
System.out.println("Version: " + productVersion);

Step 4: Are you ready to make a Statement? Go and read the below statement then.

A statement is ideally made use of send queries or commands. Statement types that we use are Statement, Prepared Statement, and Callable Statement, for example:

Statement statement =connection.createStatement();

Step 5: Execute a Query

  • Idea
  • - statement.executeQuery("SELECT … FROM …");

  • This version returns a ResultSet
  • - statement.executeUpdate("UPDATE …");

    - statement.executeUpdate("INSERT …");

    - statement.executeUpdate("DELETE…");

    - statement.execute("CREATE TABLE…");

    - statement.execute("DROP TABLE …");

    Example:

    String query ="SELECT col1, col2, col3 FROM sometable";
    ResultSet resultSet = statement.executeQuery(query);

    Step 6: See below how we Process the Result

    We describer here some of the Essential Result Set methods to process the result successfully:

    • resultSet.next()
    • - Goes to the next row. In case there is no next row, it returns false.

    • resultSet.getString("columnName")
    • -Returns value of column with designated name in current row, as a String and also getInt, getDouble, getBlob, etc.

    • resultSet.getString(columnIndex)

      - Returns value of designated column. First index is 1 (ala SQL), not 0 (ala Java).

    • resultSet.beforeFirst()
    • - Moves cursor before first row, as it was at the start.

    • resultSet.absolute(rowNum)
    • -Moves cursor to given row (starting with 1) and also last and after Last.

    Step 7: Time to say good bye, Let us close the Connection now

    Close the database connection when totally done. On the other hand, if you want to open a new connection, that will be very expensive on contrary to sending queries on the existing connections, hence it is recommended to postpone the closing of database connection as long as possible.

    Example:

    connection.close();


I am a software developer from India with hands on experience on java, html for over 5 years.

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login