Database connectivity with java 🤯, using mysql…

Hey guys hope you’ll are doing great 😄,I’m back in the arena with a brand new topic , So connecting your java application to database has always been a hassle but don’t you worry, in this article i’m going to divide the process into small steps and carry on explaining it , in the end you’ll have a fully functional database connected java program . so before moving on make sure you load up some coffee cause it’s java 🙃 , tighten you seat belt and here we go 🏎…

We do have some Prerequisites :
1) Basic understanding of Java, Object oriented programming, DBMS
2) MYSQL installed and functional on your local machine ,It’s easy you can just follow this tutorial here .make sure that you take a note of username (by default it is root) and password while installing mysql.
3) Java 1.8 installed on your machine and Eclipse IDE

1. Run your MYSQL server locally : So the first step is to run your mysql server locally on your machine using command in your terminal or command prompt :
   then you’ll be prompted with a password , enter the password for the root user that you configured during installation of mysql

$: mysql -u root -p

2. Create a new java project in eclipse : Create a new project in eclipse and make sure to select java 1.8 version while creating it , after the project is created in the left file navigation pane select project folder -> the src folder create a java file call it whatever you want that will be your main class obviously .I’ll call it Demo.java

3. Coding time: Now is the time we get our hands dirty with the technology , let’s create our class, shall we ?! , first line will import everything from sql package ,this package will contain all our DriverManager, Statement, Connection classes whatsoever (more about them shortly)

import java.sql.* ;
public class Demo {
 public static void main(String args[]) {
   // all our database code will go here
 }
}

3.1 Getting the driver : now in order to connect with the database we’ll need to have a driver , driver is the one who will talk or connect with the database .
in this case i’ll be using the mysql driver which is a .jar file , here is the link to download it (driver or the connector are the words used interchangeably) .
Once you have downloaded the driver or connector we have to add it to our eclipse project .
RightClick on the project folder -> Build path -> Configure Build Path -> Under libraries tab there is Add external JARs option… click it -> At right bottom of the tab click Apply and close . now you have added the connector to our project

3.2 Registering the driver and loading it : now we need to load and register that driver into out code just add this line inside our main function

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

.forName function will load the provided class in our program which is our driver indeed , if you take a look inside the .jdbc.Driver class it has a static block which also register’s the driver using DriverManager class which has a registerDriver method , so adding above line will load the driver but simultaneously also register it

3.3 Creating a connection : now that we have our driver class loaded we have to tell it to create a connection with mysql database (make sure you have mysql installed and running as i have prompted earlier). Connection interface is used to create a connection , now we cant directly create an object of an interface(hope you recall java basics 😜) so we gonna use DriverManager class it has a method called as getConnection which takes in 3 arguments they are url, username, password
url: we need a database url ,because at the end of the day a database is running on a local server on a machine ,default url is ‘ jdbc:mysql://localhost:3306/your — database — name?autoReconnect=true&useSSL=false ‘
username: the one you created while installing mysql , default is root .
password: the one you had set while installing mysql database
?autoReconnect=true&useSSL=false : this query in the url is just some extra config that says don’t use SSL you don’t have to worry about it .

//Demo.java 
import java.sql.* ;
public class Demo {
 public static void main(String args[]) throws Exception {
  Class.forName("com.mysql.jdbc.Driver"); /*as explained in stage  3.2*/
  String url = "jdbc:mysql://localhost:3306/demo?   autoReconnect=true&useSSL=false" ; /* demo is the name of the  database */
  String uname = "root" ;
  String pass = "default";
  Connection con = DriverManager.getConnection(url,uname,pass);
 }
}

3.4 Creating a Statement : This interface is used to execute the Sql statements , once made a database connection we need a Statement to execute our queries , so let’s get this Statement object . Connection object has a method createStatement() which gives us back a Statement Object
``` Statement st = con.createStatement(); ``` add this line to our main method so our code now should look like below .

//Demo.java
import java.sql.* ;
public class Demo {
public static void main(String args[]) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/demo?autoReconnect=true&useSSL=false" ; 
String uname = "root" ;
String pass = "default";
Connection con = DriverManager.getConnection(url,uname,pass);
Statement st = con.createStatement();
}
}

3.5 Let’s create a database : now i am going to create a database using a GUI that is mysql workbench its free to download if you have completely followed the resource video in reference(mentioned in top prerequisite section) you’ll have it already installed
It’s pretty easy you just have to create a schema and a table , I’ll create a database with name demo and a table with name people which will have columns name :varchar(200), bio : varchar(200) , id : INT primary key ,
I’ll also add three dummy entires

Select the table and execute below queries to create dummy entries

use demo; 

insert into people values (1,'Yuvraj','I am a CS major');
insert into people values (2,'Mark','I run FB');
insert into people values (3,'Elon','I run Tesla');

Creating dummy records

3.6 Executing query in our java program :now that we have our database created with some dummy data in it ,lets execute the query , The Statement object has a few methods on it like execute() : which returns a Boolean based on if query was executed or not , and executeQuery() which returns ResultSet object which contains our records (records of select statements etc)

//Demo.java
import java.sql.* ;
public class Demo {
public static void main(String args[]) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/demo?autoReconnect=true&useSSL=false" ; 
String uname = "root" ;
String pass = "default";
Connection con = DriverManager.getConnection(url,uname,pass);
Statement st = con.createStatement();
String query = "select name from people where id = 2";
ResultSet rs = st.executeQuery(query);
rs.next();
String name = rs.getString("name");
System.out.println(name);
st.close(); //close statement
con.close(); //close db connection
}
}

This is our final entire code (your file should look like this),
the query string variable is our normal sql query , next up we have ``` ResultSet rs = st.executeQuery(query); ``` which as discussed executeQuery is a Statement interface method which executes the query and stores it into a ResultSet object

rs.next() : by default in select query the pointer is at 0th position or just one position back for example if you want 2nd record the pointer will be at 1st so you just have to call the next() method of ResultSet .

rs.getString(“column-name”) : just pass the column name that you have to fetch

.close(): this method is called on Statement and Connection , this will close each of them , not compulsory to close but this will save up some resources for us (follow good practices 😉)
Now just press the run button (which is a green play button on the top 7th from left side precisely ▶️ ) , press Ok if you are being prompted something by eclipse

Output

As you can see java has fetched the data according to your needs , now the program structure remains the same for any other query , you just have to change the query string , try other queries like inserting , creating ,altering etc . play with the code . let me know if you have any issues , Meanwhile also be sure to check out Oracle docs as they have immense information .

Now.. i don’t intent to convert this article into a book so i may have to stop here 😄, hope you enjoyed the article and got to learn something new ,I know you are craving to hit that like button (which looks like hand claps👏) so be sure to do so , writing such articles takes a lot of time and efforts, I’d appreciate a Follow on Twitter, Medium, Insta checkout my personal website for more info, until then Ciao 👋…