Java Database Connectivity | JDBC

okay my exact recording software is all set the video jdbc in Twin fact I have it's been a long time I've worked on jdbc uh maybe five to six years maybe the last time I've used it is to make a video why this video oh that's weird because from from a long time I'm using spring and hibernate for database and things were going good but then I thought it's important for us to understand what happens behind the scene right understanding jdpc is very important now question is why you need jdbc here now think about this all your data is getting stored in the database right and as a user a normal user when they want the data they can't simply talk to database directly right you can they cannot simply say hey database I want a data for a user whose roll number is one zero one database has no idea what you're talking about so basically you have to speak the database language and the language which your database or dbms understands is SQL so basically you have to talk to your database with the help of SQL language now the thing is not everyone knows SQL of course as a programmer it's your job to know SQL but not a normal person they are busy with their life they are busy with doing accounting they're busy with building Rockets they're busy with all all the other things it's your job as a programmer to help them to connect with database and that's where you build applications right now this application can be written in any language let's say Java c-sharp PHP Ruby or the JavaScript doesn't matter so when you build this application you allow your user to interact with it and then this application will interact with the database now how do you connect your application with your database now that depends upon the language which we use example if you're using Java and then you have a database to connect these two we have to use something called jdbc which is Java database connectivity okay now how do we use it see the first of all when you talk about jdbc with the help of jdbc you can connect your Java and database right but then how will you connect with and is it applicable for only one type of dbms or you can connect with any dbms because in this world we have thousands of databases or dbms out of which few are famous exam and many more we are going to use postgresia because in this machine I have postgres installed doesn't matter which rdbms which you use everything will work in fact most of them supports jdbc now how Java knows how to connect with all these dbms and that's where the fun thing comes into picture so all these different dbms they have their own library to connect with Java okay so if you talk about MySQL they have their own Library if you talk about postgres they have their own Library what you need in your application is the library okay so the first thing you need is the library and when you have that Library what next now that's where you have to follow certain steps it's like a recipe right if you want to cook something you follow certain steps in the same way if you want to connect your Java application with database you have to follow seven steps in fact six steps okay so before we talk about these steps let's also take an example which I've taken way back which is calling someone so let's say if you want to know the information about something let's say you are at home and you want to know what is happening in your office so what you will do is you will talk you will call to your colleague not for the gossip but to get some information so you will call them and say Hey you know I want the information about uh what is going on with the project now you can't simply think in your mind and then your colleague will know of course you have to use certain devices right so the first thing you need is a phone so you will take a phone and then you will make sure that you have a connection because most of the places we don't have Network where the mobile network says we are available everywhere no they are not but let's say the first thing you need is a network or the Wi-Fi connection if you want to do a WhatsApp call so two things first you need a phone then you need a connection next you have to dial of course right you can't simply look at your phone and then you will assume that it will it will get connected to your friend you have to dial the number then there will be ringing so they there will be a ring and then your friend will pick up the call of course if they're free and they want to talk to you once they pick up the call you will say something right that's the information which you want of course after the greetings and hello hi what's going on all the stuff they're done you will you will ask for a specific information and then your the your friend will give you some information now you have to process the information of course with your with some context with some previous knowledge you will understand what he's trying to say and then everything done you will simply close the connection or if you want you can throw your phone okay don't do that but those are the steps in the same way if you want to connect your Java application with database let's follow these seven steps or six steps so the first one is import the packages okay now in Java if you want to use certain things you have to import the package remember when you talk about the library so basically in this you have to import the packages where you have all the classes interfaces for jdbc next step is to load the driver now this is almost same as checking if you have a network so loading the driver now this driver will be provided by the actual rdbms so for different dbms we have different drivers now with jdbc 4.0 which is the version of jdbc which came I think in Java 6. loading the driver is optional now the next step is you have to register the driver okay which is whatever driver you have you have to register next you have to create a connection that's why you have to dial the number right so next step is to actually create a statement so before when you say something you think about it right that's creating a statement now once you thought about it say that's your execute this statement and then if everything goes good you will get a response so basically you have to say process the response and then after doing all those things it's important to close the connection otherwise you will get a huge bill that's why I close the connection okay so with those seven steps let's try to implement it now how do we implement it the first thing you need is a IDE oh in fact before IDE let me open the dbms now we are using postgres here so I will open postgres taking some time okay and then uh this is PG admin basically when you install postgres it will also give you PG admin now based on which dbms you have there are different ways of opening it now if you want to try out uh postgres it's very simple just go to Google search for postgres download and then common steps it will ask you for the password set a password set a difficult password for your database and that's why in my machine I have a password which is zero zero zero zero difficult to guess okay and okay that's it I'm into my postgres now we can create our own database if you want so you can right click here and you can create a new database but what I've done is already have a database in fact in one of the video we have used product table so we are going to use the same table so that is available in the telescope database so the database name is telescope inside that if I go to schema and if I scroll down you can see we have a tables here if I say view all those we already have some data here if you can see yeah so you can see we have all this data here I want to fetch this data in my application how do I do that quite simple if I go back here uh okay we don't have application so I will open my IntelliJ and let's create a new project so again you can use any IDE which you want but I'm using IntelliJ here one of the thing with integer is you can actually add external Java files easily so let me create a project and I will say this is for videos okay uh the location download doesn't matter because I'm going to delete this project and then I want this as a Java application and then the build system is IntelliJ the jdk which I'm using is 17 but anything above 8 should work click on create you will get your project you can see I got my project here now in this project let me create a file or the class basically and let's name this as demo so this is where we are going to write our code all the code basically but then this will not work without the jar file if you can see in the external libraries we only have one which is your jvm which is your Java library but what we also want is the drive of for postgres now where you can get it you can simply do a Google search so you can search for postgres driver for jdbc and see if you can find that maybe we can also get from the official website I never tried it and as I mentioned it's been a long time I've worked on this but it says it is a pure Java driver so it should work click on download and okay so based on the version as I mentioned you anything which is above eight or eight about it should work so and the jdpc version it is have is 4.2 so the first two steps are optional click on download and you can see it will give you a jar file this is the jar file I was talking about so this is where we have to add the Java file how do you add the Java file it's very simple go to file uh go to Project structure and this is where you will see libraries folder or Library section just click on libraries click on this Plus and say new project library for Java yes and now you can basically select the jar file which you have downloaded click on OK and done you can see this will be a part of this external Java file this is important okay because in this you have all the required classes okay let's go back to demo and this is our main file so in this particular video the idea is how do you connect your Java with database right and if you can even write a single query to go every database our job is done so what we are going to do is we'll find a simple SQL query and of course we have to fire the SQL query so I will say string SQL so we have to file the query now if you know SQL it's very easy so you can say simply select select star or maybe I can just use one record I want to say select let's go to PG admin I want to select a name with the ID let's say eight so I want to print Apple keyboard okay so I want a name from product table where the ID is equal to eight simple SQL query so what it will return you is a name simple string and that's what we want okay now this is the query we have to fire but then this will not work on I mean of with only SQL query will not work you have to follow the seven steps remember the seven step which we have talked about so the first thing you will do is they load the driver again the loading and registering is optional you can directly jump into connection so you can use something called connection interface now this belongs to java.sql okay so this is a special package where you can find all the SQL interfaces and classes so it's a connection con equal to now how do you create an object here basically connection itself is interface right so you can't simply create an object of it so you can use another class which is called a driver manager which has a method called get connection so using this get connection method you can create the connection so you will get this object okay so that's why you are basically trying to connect with your database okay but then with this how will my Java application will know which dbm has to connect what is the username what is a password so of course we have to specify the dbms name which is postgres we have to mention your database name which is telescope you have to mention your username which is postgres in this case and the password which is zero zero zero zero zero zero zero four times how do I mention that so if you can see get connection so if I cut this part control space okay it's giving the default one let's try once again so when I say get connection there is another method which takes three parameters so first is the URL second is the username and third is the password so to specify the URL the username and the password so you have to specify these three things that's it okay but then uh we have to create this variables right so I can just go back here and say string URL so we'll mention this then we have to mention the username username for this postgres is postgres and string of course this is something which will be given by the postgres when you do the setup and the password is four times zero and you're done but yeah we have to complete the URL now how the URL looks like you just have to mention three things first jdbc because your Java application will be connected with jdbc jdbc is connected to the postgres SQL and post with SQL is connected with a database which is the telescope so database name is right so if I show you here the database name is telesco and done uh not exactly the thing is the postgres works on the network so if you want to connect your Java with postgres you have to mention the network address so here I can say colon localhost colon slash telescope so basically we have to mention things first URL which is your application got connects to jdbc colon postgres SQL colon slash slash local over slash teresco because it's in the same address also if you have to mention the port number now this port number changes as per the dbms for Mass ql it is 3306 postgres it is 5432 and depend upon your different dbms you have different port numbers and you will get to know when you when you install the postgres or any dbms it will mention the port number on which you can connect with it so you can just remember that remember that okay get connection is giving you error it simply says it might to an exception so of course I can write try catch which you should do but since I am teaching you this I want to make the code bit clean for you to understand what I will do is I will simply close the SQL exception in fact I can simply say those exception our job is done not a good idea you should use try cats but here just to make the code look good I'm using this so we are done with the connection as well what's the next step now we have to think about the statement oh if you want to talk to your dbms the statement which I'm talking about there are three options you have option of using a statement as an interface so we have statement interface which is from java.sql package we also got a prepared statement and then we can also use callable statements so you can say callable statement okay three options but here you can you can use normal statement to achieve that okay also statement St equal to con dot create statement now basically we can use callable statement for procedures we can use statement of repair statement difference between these two I will let you know in some time but yeah you can use those things now once you got your statement and we also got SQL it's time to execute okay so it's something like you called your friend or your colleague you know what you want to say now it's time to say it okay now how do you how will you say it's very simple St the statement has a method called execute query so you can use execute query so basically if you want to for the select query you can use this execute query if you want to fire the insert update you can use execute update which will make some changes in the database so we can use SQL query here and then in this you just have to pass the query it's so simple so execute query says I will execute you just give me the query and you just pass the query but then when you say something your friend will also say something now when you execute this query what you will get in return so what I will do is I will just copy this go back to postgres and here I want to execute this so how do I get the SQL prompt it's here I think you need a query tool okay so that's my query and I want to run this query you can see when you run this query you will get Apple keyboard that's what you wanted but if you look at this output are you getting only Apple keyboard or are you getting the structure where you also have a column name the thing is if you're thinking that this SQL query will give you a type string then you're wrong if I click on execute query it returns a result set imagine it result set as a table format in the Java code so basically you have to accept data with the help of X result set equal to I mean result RS equal to St dot execute query and then once you got this result set basically this has a data now okay trust me this has a data but the problem is if this is how you get the data your pointer is not pointing to Apple keyboard example if you go back here and say hey these are set get to the data because when you want to get something the most common method is always get you can also mention what type of data you're getting are you getting a integer type data are you getting the string type data we know we are getting string type data and the number of columns so the column number is very important here you can say one because you only got one column but then if you try to save this in a string let's say name do you think this would work I mean I know it will not work but let's say if it works what should be the output of course it should print let's print name and let's see what happens sometimes you learn by mistakes right so let's make some mistakes and right leg run okay so you can see we got an error it says result set not positioned properly perhaps you need to call next so what is happening is when you're saying get me this string you are expecting that there will be only one record and you can fetch it first of all there can be many records multiple rows and also where your pointer is pointing when you have when you when you're saying get string so by default the pointer is here we want to move the pointer to the Apple keyboard so before you use it you can simply say RS dot next and that's it now you shifted your pointer to the first record and now I've done this you got your output so that's your jdbc okay and you got the output as Apple keyboard but is this code complete first of all we made a lot of uh bad practices don't close exception you have to handle the exception don't don't write username password in the code write it somewhere else and statement is not a good idea but we'll talk about how do we solve that but the most important thing which we forgot is to close the connection remember it's like calling someone and not cutting the call so close the connection let's try to understand what can go wrong with statement now when you say statement basically we are filing a query right but what if the eighth here is coming from the user of course right user don't always want Apple keyboard maybe user want to specify a ID based on which you want to fetch what if this 8 is dynamic now the ID is dynamic in that case you need to specif you need to ask the user to enter the number and then user will enter but how will you replace it that's one question and what if a user is typing some another query in terms of in case of value so it's always better to instead of using statement we can use prepared statement there's a concept of SQL injection I don't know how many of you know it you can just Google it later so basically in the in the text field where the user has to enter the number a user can enter the query itself okay so to avoid SQL injection we should use prepared statement we are not going to do that in this video but just want to let you know that there is a concept available which is required statement where uh you a user if if I if you ask a user to enter the number and if user is not entering a number entering something else it will not accept so you are safe there okay so yeah that's how we use jdbc and it's important to know something what happens behind the scene anyway when you start using spring with orm you don't write all the steps you simply Say Hey I want an object or I want data spring framework will say take it so yeah that's it from this video where we talked about jdbc and see you in the next video goodbye

Channel: Telusko

Views: 228,032

Keywords: telusko, navin, reddy, tutorial, Java, Blockchain

Id: 7v2OnUti2eM

Length: 20min 34sec (1234 seconds)

Published: Tue Sep 12 2023