In Java, we can connect to our database(MySQL) with JDBC(Java Database Connectivity) through the Java code. JDBC is one of the standard APIs for database connectivity, using it we can easily run our query, statement, and also fetch data from the database.
Prerequisite to understand Java Database Connectivity with MySQL:-
1. You have MySQL on your System.
2. You have JDK on your System.
3. To set up the connectivity user should have MySQL Connector to the Java (JAR file), the ‘JAR’ file must be in classpath while compiling and running the code of JDBC.
Steps to download MySQL Connector:
- Search for MySQL community downloads.
- Then, go to the Connector/J.
- Then, select the Operating System platform-independent.
- Then, download the zip file Platform Independent (Architecture Independent), ZIP Archive.
- Then, extract the zip file.
- Get the mysql-connector-java-8.0.20.jar file from the folder.
Setting up Database Connectivity with MySQL using JDBC code
Users have to follow the following steps:-
1. Users have to create a database in MySQL (for example let the name of the database be ‘mydb’ ).
2. create a table in that database.
Example:
create table designation
(
code int primary key auto_increment,
title char(35) not null unique
);
this is MySQL code for creating a table.
3. Now, we want to access the data of this table using Java database connectivity.
- create a directory in your main drive (named gfg).
- now, inside gfg created two more directories one named as ‘src’ and the other ‘lib’.
- put the MySQL connector java jar file in the lib folder.
4. we will write connectivity code in the src folder, To write connectivity code user must know the following information:
- Driver class:- The driver class for connectivity of MySQL database “com.mysql.cj.jdbc.Driver”, after the driver has been registered, we can obtain a Connection instance that is connected to a particular database by calling DriverManager.getConnection():, in this method, we need to pass URL for connection and name and password of the database.
- URL for Connection:- The connection URL for the mysql database is jdbc:mysql://localhost:3306/mydb (‘mydb’ is the name of database).
Specify to the DriverManager which JDBC drivers to try to make Connections use below line.
Class.forName("com.mysql.cj.jdbc.Driver");
To get connection object use below line :-
Connection connection=DriverManager.getConnection("URL in string","username","password");
To get more clarification follow the connectivity code below.
5. In this src code, we will set up the connection and get all the data from the table. we have created the ‘check.java‘ file in the src folder.
Java
import java.sql.*;public class GFG { public static void main(String arg[]) { Connection connection = null; try { // below two lines are used for connectivity. Class.forName("com.mysql.cj.jdbc.Driver"); connection = DriverManager.getConnection( "mydbuser", "mydbuser"); // mydb is database // mydbuser is name of database // mydbuser is password of database Statement statement; statement = connection.createStatement(); ResultSet resultSet; resultSet = statement.executeQuery( "select * from designation"); int code; String title; while (resultSet.next()) { code = resultSet.getInt("code"); title = resultSet.getString("title").trim(); System.out.println("Code : " + code + " Title : " + title); } resultSet.close(); statement.close(); connection.close(); } catch (Exception exception) { System.out.println(exception); } } // function ends} // class ends |
Output :
Note:-
- To run the above code first create a table in your MySQL database and add some data manually.
- To compile the above code use “javac -classpath ..\lib\mysql-connector-java-8.0.20.jar;. Check.java“.
- To run the above code use “java -classpath ..\lib\mysql-connector-java-8.0.20.jar;. Check“.
