Teach Me How To program: database connectivity - Database Connectivity Java MySql

Java Database Connectivity

In java JDBC is an API (Application programming Interface). Which is used to connect the different range of databases with your application. So, to connect any database with your application you need to install JDBC-API which is freely available at official site of MySQL here below is the link you can easily download.Database Connectivity Java MySql

JDBC-API URL:

Once you download this API then go to eclipse select your project from project explorer tab click on the properties. Let see below images step by step

Frist Step: I create New Project with name “Database Concepts” It will look like that:

database connectivity - Database Connectivity Java(JDBC)

Next Step:I right click on your project and select properties

Next Step:a box will open then then click on ‘Java Build Path’ then click on ‘Add External JARs.’

Next Step:Then browse your JDBC-API Jar after browse select the jar and click on Open

Next Step:note that your JDBC jar is successfully added in project no click on Ok button

Now your project will be looks like that

Notice that reference directory is created and inside that directory you will find JDBC connector.

For this tutorials I will be using MySQL database. Before move further first thing you have to great knowledge about SQL (Structured Query Language). SQL is used to communicate with different databases or in simple words SQL language is a native language for all type of databases. After that again come to our topic which is MySQL Database.Database Connectivity Java MySql

MySQL Database

MySQL is an open source Relational Database Management System (RDBMS) which used SQL to interact with the database. It is freely available go through the below link to download the MySQL database.Database Connectivity Java MySql

MySQL Database URL:

After that you have to use have to install any Database Management System (DBMS) like SQLYOG or MySQL workbench software. This software’s provide you an interface where you can play with your MySQL database (Note: this software’s are optional I just suggest you because of better understanding because MySQL itself don’t having a professional environment so, if you want to use MySQL database without any DBMS then you have to use CMD prompt).

There is also another option for easy and better understanding of MySQL with DBMS, you have to download and install XAMPP or WAMP server these both servers already having MySQL and DBMS inside it. So, you no need to install individual all required things. As for my concerned I will be using XAMPP server in this tutorials. To Install XAMPP or WAMP sever than go through the following links.Database Connectivity Java MySql

XAMPP Link:

WAMP Link:

So I’m preferring you to install XAMPP or WAMP server in your local machine rather than install individually MySQL database and SQLYOG or Workbench BDMS in your local machine. Once you successfully install XAMPP or WAMP server in your local machine then a shortcut of respected server will be created on your desktop, as I installed XAMPP server on my local machine so I double click on XAMPP server control panel shortcut it will look like this:Database Connectivity Java MySql

Click on Start button of MySQL and Apache

To access XAMPP or WAMP DBMS (Database management system) then open your browser and type localhost/phpmyadmin

URL:

Output

This is the MySQL DBMS environment. Now to create the database for your application, the XAMPP provide the two ways to create database for your application.

o Through XAMPP server

o Through SQL

Through XAMPP server

This is very easy and pretty to use. Click on database from menu, let’s see an image below

Next step: Write database name as you want and click on create button

Next Step: Once you click on create button then your database will be located at side so. from side bar click on the database which you created now, as my concerned I had created demo database

Next Step: after click on database you will see below layout, now you notice that I marked at four places from left side it shows “No table found in database”, second create table, so you have to write table

name in input box, next It say how many columns you want to create in your database so just give total no columns in input box and in last click on “GO” button

Next Step:You will see following layout and, now fill the information of your four columns, as for my concerned please read image carefully you will understand it well

Next Step: after click on save button your database table will successfully create as you see on left side of your computer screen it will display the name of table as you created.

Next Step:click on that table name you will see the following in environment

Now before moving to the next section lets we discuss about database table and its columns

o Table is a collection of columns

o Every column has a unique name, datatype, length etc.

o As SQL suggest that when you create any table in database then at least one column having a feature of PRIMARY KEY

o PRIMARY KEY means a database table column which identify all records uniquely that column is must contain a PRIMARY KEY.

o The NOT NULL modifier definition specifies that the field cannot accept a NULL value.

o The AUTO_INCREMENT modifier tells MySQL to automatically generate a value for this field every time a new record is inserted into the table, by in crementing the previous value by 1. Only available for numeric feilds.PHP MySQL engine auto generate the PRIMARY KEY for new records, so that we distinguish all record easily

Database Connectivity Java My Sql

MySQL Data types

Field Type	Description
INT	A numeric type that can accept values in the range of -2147483648 to 2147483647
DECIMAL	A numeric type with support for floating-point or decimal numbers
CHAR	A string type with a maximum size of 255 characters and a fixed length
VARCHAR	A string type with a maximum size of 255 characters and a variable length
TEXT	A string type with a maximum size of 65535 characters
DATE	A date field in the YYYY-MM-DD format
TIME	A time field in the HH:MM:SS format
DATETIME	A combined date/time type in the YYYY-MM-DD HH:MM:SS format

Now we are moving towards second way to create a database. Go to PHP my admin clicks on SQL as display in image below.

Next Step: you will see below layout, now as in start I suggest you that before start this you have to complete now knowledge of database and SQL, here I will use some SQL to create database see image below SQL query “CREATE DATABASE demo”

Next Step: Once you click on create button then your database will be located at side so. from side bar click on the database which you created now, as my concerned I had created demo database

Next Step: after click on database you created, then again click on SQL again to create a table

Next Step: you can see SQL editor here you will write a code to create a table, as for me I’m writing this code see image below

Next Step:as you on left side of computer screen it will display you table name which you created recently

Next Step: click on table name

Database Connectivity Java MySql

Now I suggest you both ways for creating database and database table, now it’s up to you which way you like go through that way

Java Connecting MySQL database

To connecting your java application with MySQL database so need to required complete following these steps

Step-1

o First step you have to register the Driver class, for this you have to use following method with Class

o Class.forName(“com.mysql.jdbc.Driver”);

o This method will load all Drivers dynamically

Step-2

o Create the Connection class object and use the Driver Manager class with method getConnection(). This method will connect your application with MySQL database

o Connection con = DriverManager.getConnection(“host”, “username”, “password”);

o host: mysql:jdbc//localhost:3306/databaseName

o username: for MySQL database the username is “root”

o password: by default, the password is null but I set the password as “root”

Step-3

o Create the statement object or prepared Statement object. This statement or prepared statement is responsible for executing MySQL database queries Note: prepared statement interface is a new version of statement interface so I’m recommending you to use prepared statements rather than statements

o Statement statement = con.createStatement();

o PreparedStatementpreparedStatement = con.preparedStatement(“Database Query”);

Step-4

o Create the Result Set object. When we create the statement after that we have to execute the query, once we executed the query, then the executed query will return the result set. So, for this you have to create ResultSet object to store the database result

o ResultSet set = statement.executeQuery(“Database Query”); // for statement interface

o ResultSet set = preparedStatement.executeQuery(); // for prepared statement interface

Step-5

o This is last step and in this step you have to close the connection object.

Example with connection and insert the record into database

Open your browser and go to your database check that record is inserted or not

As you see on above image that the data which is inserted through Java program the same data is successfully inserted into MySQL database. Now same way I insert three more records in MySQL database now my database looks like that:

Now I want to get or retrieve these record in Java Program so let see an example below

Example with Retrieve the data from MySQL database

In Above example we select the records from database table using preparedStatement Interface this interface returns the resultSet so we create the object of resultSet and store the data in that object after that we start a loop to read the record one by one. In loop we as set.next() it means that in set object it has next value or not? If has then loop will be execute otherwise loop will terminate.

Now inside the loop we print the four columns of database table like

o Syntax: resultsetobject.getDatatype(“database column name”);

o Example: set.getInt(“emp_id”); or set.getString(“emp-name”);

Note:in database the ID columnis type of Integer so that’s why we used set.getInt() method. Same way the in database the name columns is type of varchar so that’s why we used set.getString() method.

Example with Update Record

Database Before Update