jdbc code to retrieve data from mysql database

                     In this post, we will see a Java Database Connectivity program (JDBC) for selecting records or retrieving data from the table in 'mysql' database.

               Here 'sdldatabase' is the database name. User name is 'sdl' and password is 'sdlpassword'. This program is for retrieving records (tuples) from table 'student' which has four fields (columns).
               Following are the SQL queries implemented on SQL prompt followed by JDBC program for retrieving data. Go through them.

SQL queries implemented on mysql prompt:

parag@parag-Inspiron-N4010:~$ mysql -u root -p Enter password:  mysql> create user 'sdl'@'localhost' identified by 'sdlpassword'; Query OK, 0 rows affected (0.00 sec) mysql> grant all privileges on *.* to 'sdl'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> create database sdldatabase; Query OK, 1 row affected (0.00 sec) mysql> use sdldatabase; Database changed mysql> create table student     -> (     -> rollno int(5),     -> class varchar(10),     -> college varchar(10),     -> city varchar(10)     -> ); Query OK, 0 rows affected (0.06 sec) mysql> insert into student values(3101,'te','pict','pune'); Query OK, 1 row affected (0.03 sec) mysql> insert into student values(4301,'be','gcoe','amravati'); Query OK, 1 row affected (0.05 sec) mysql> select * from student; +--------+-------+---------+----------+ | rollno | class | college | city     | +--------+-------+---------+----------+ |   3101 | te    | pict    | pune     | |   4301 | be    | gcoe    | amravati | +--------+-------+---------+----------+ 2 rows in set (0.00 sec)

                

JDBC Program (jdbc.java):

import java.sql.*;

class jdbc
{
public static void main(String args[]) throws Exception
{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/sdldatabase","sdl","sdlpassword");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("select * from student");
while(rs.next())
{
System.out.print(rs.getString(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.print(rs.getString(3)+" ");
System.out.print(rs.getString(4)+" ");

System.out.println("\n");
}

rs.close();
st.close();
con.close();

}

} 

Note:

We have to download mysql-connector.jar file. We can download it from the link http://www.java2s.com/Code/Jar/m/Downloadmysqlconnectorjar.htm. Extract zip file and save it at location /usr/share/java.                                    To run jdbc program on terminal in Linux, you need to edit .bashrc file. You will get .bashrc file in following location /home/[user name]/.bashrc e.g.  parag@parag-Inspiron-N4010:~$ gedit /home/parag/.bashrc Then add following line in .bashrc file: export CLASSPATH=$CLASSPATH:/usr/share/java/mysql-connector.jar Save it and restart the system.

Output:

parag@parag-Inspiron-N4010:~/Desktop/prog$ javac jdbc.java parag@parag-Inspiron-N4010:~/Desktop/prog$ java jdbc 3101 te pict pune  4301 be gcoe amravati

                  
                      Check jdbc code for inserting data into mysql database in this post: http://www.comrevo.com/2017/07/jdbc-code-to-insert-data-into-mysql-database.html

Check Other Posts on Database