First JDBC Program

Prerequisite:

1. Install any database(in my case it is mysql)
2. Configure java build path and add external jar of mysqlWe have already created student_detail table in mysql

table-data
Now we will fetch that data using Java Program

Necessary step to work with JDBC
  1. Load driver
  2.  get the db connection via driver
  3.  issue sql query via connection
  4. process the result return by sql query
  5. Close all JDBC object
Java program to fetch data from mysql database
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
package com.spy.javaforlearn;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
import com.mysql.jdbc.Driver;
 
public class Myfirstjdbcprog 
{
 
	public static void main(String[] args)
	{
 
		Connection con=null;
		Statement stmt=null;
		ResultSet rs=null;
 
 
		try
		{
 
			/*
			 * 1. load the driver
			 */
 
		     Driver rf1 = new Driver();
 
		     DriverManager.registerDriver(rf1);
 
 
		     /*
		      * 2. get the db connection via driver
		      * 
		      * dburl-protocol:subprotocol:subname
                      * Protocol: JDBC
                      * Subprotocol:mysql( it's depends on database)
		      * subname consist:
		      * --hostname
		      * --port number;
		      * --db name
		      * --user & password		      
		     */
 
 
 
 
	String url="jdbc:mysql://localhost:3306/test?user=root&password=root";
        con=DriverManager.getConnection(url);
 
 
		/*
		 * 
		 * 3. issues sql query via connection
		 * 
		 */
 
 
		String query="select *from student_detail";
		stmt=con.createStatement();
		rs=stmt.executeQuery(query);
 
 
		/*
		 * 
		 *4. process the result return by sql query
                 *
		 */
 
 
		while(rs.next())
		  {
			String id=rs.getString("user_id");
			String f_name=rs.getString("f_name");
			String l_name=rs.getString("l_name");
 
 
			System.out.println("user_id:"+id);
			System.out.println("first name:"+f_name);
			System.out.println("last name:"+l_name);
 
			System.out.println("*******************");
 
		   }
 
		}
 
		catch(SQLException e)
		{
			e.printStackTrace();
		}
 
		/*
                 *  5. Close all JDBC object in Finally block
                 */
		finally
		{
			try
			{
			if(con!=null)
			   {
			    con.close();
			   }
			if(stmt!=null)
			  {
			  stmt.close();
			  }
 
			if(rs!=null)
			  {
			    rs.close();
			  }
		     }
 
			catch(SQLException e)
 
			   {
				e.printStackTrace();
			   }
 
			}			
		}	
	}
 
output:
 
user_id:1
first name:bhavesh
last name:lakhani
*******************
user_id:2
first name:vivel
last name:patel
*******************
user_id:3
first name:abhilash1
last name:shah
*******************
getconnection method:
getc2
There are Three overloaded Version of
getconnection(url) Methodgetc

 

Exa.

1. getconnection(String url)

String url="jdbc:mysql://localhost:3306/test?user=root&password=root";
con=DriverManager.getConnection(url);

2. getconnection(String url,properties info)

String dburl="jdbc:mysql://localhost:3306/test";
FileReader f=new FileReader("E:\\myjdbc.properties");
Properties prop=new Properties();
prop.load(f);
con=DriverManager.getConnection(dburl,prop);

3. getconnectin(String url,String user,String password)

String url="jdbc:mysql://localhost:3306/test";
con=DriverManager.getConnection(url,root,root);
  • Most Efficient way to getconnection using getconnection(String url,properties info) Method
  • In this method we provide user name and password from our property file so it is secure.
  • You can change User without changing into source program.

JDBC program using Properties file

JDBC program using Properties file
  • In our Previous topic we provided database username and password from our
    java program and that is not secure.
  • Now we will provide database username and password from Properties file.
  • Extension of Properties file is .properties and it contain data in
    name=value pair.
  •  I have created Properties file like belowproperties-file
java program that read username and password from properties file
and fetch data from database.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
package com.spy.javaforlearn;
 
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
 
import  com.mysql.jdbc.Driver;
 
public class JdbcusingProperty
{
	public static void main(String[] args)
	{
 
		Connection con=null;
		Statement stmt=null;
		ResultSet rs=null;
		try
		{
 
		//1.Load the Driver
 
		java.sql.Driver driverref=new Driver();
 
		DriverManager.registerDriver(driverref);
 
		//get the db connection via driver
 
 
		String dburl="jdbc:mysql://localhost:3306/test";
 
		FileReader f=new FileReader("E:\\myjdbc.properties");
 
		Properties prop=new Properties();
		prop.load(f);
 
		con=DriverManager.getConnection(dburl,prop);
 
		//issue sql query
 
		String query="Select * from Student_detail";
		stmt=con.createStatement();
		rs=stmt.executeQuery(query);
 
		//process result return by mysql query
 
		while(rs.next())
		{
			String id=rs.getString("user_id");
			String f_name=rs.getString("f_name");
			String l_name=rs.getString("l_name");
 
 
			System.out.println("user_id:"+id);
			System.out.println("first name:"+f_name);
			System.out.println("last name:"+l_name);
 
			System.out.println("*******************");
 
	         }
 
 
	  }
		catch(Exception e)
		 {
			e.printStackTrace();
		 }
 
		finally
		{
			try
			{
 
			  if(con!=null)
 
			   {
			     con.close();
 
			   }
 
	 	      if(stmt!=null)
		      {
		     	stmt.close();
		      }
 
		      if(rs!=null)
		      {
			     rs.close();
		      }
 
		    }
 
	        catch(Exception e)
		  {
		      System.out.println("Exception occur");   
		  }
 
	 	 }
	 }				
}
 
 
output:
 
user_id:1
first name:bhavesh
last name:lakhani
*******************
user_id:2
first name:vivel
last name:patel
*******************
user_id:3
first name:abhilash1
last name:shah
*******************