Steps to connect a Java Application to Database
The following 5 steps are the basic steps involve in connecting a Java
application with Database using JDBC.
1. Register the Driver
2. Create a Connection
3. Create SQL Statement
4. Execute SQL Statement
5. Closing the connection
Register the Driver
Class.forName() is used to load the driver class explicitly.
The forName() method of Java Class class returns the Class object associated
with the class or interface with the given name in the parameter as String.
Syntax:
getConnection(String url)
getConnection(String url, Properties prop)
getConnection(String url, String user, String password)
1. Class classlldr = Class.forName("com.mysql.cj.jdbc.Driver"); //lang class load
er is passed as parameter
2. System.out.println("Name of Class = " + classlldr.getName()); //get the n
ame of class
3. System.out.println("Package Name = " + classlldr.getPackage());//get the
name of class
4. System.out.println("Interface Name = " + classlldr.getInterfaces()); //get
the name of class
Create a Connection
getConnection() method of DriverManager class is used to create a
connection.
The getConnection(String url) method of Java DriverManager class attempts
to establish a connection to the database by using the given database URL. The
appropriate driver from the set of registered JDBC drivers is selected.
Syntax
getConnection(String url)
getConnection(String url, String username, String password)
getConnection(String url, Properties info)
Example establish connection with Oracle Driver
1. Class.forName("com.mysql.jdbc.Driver");
2. url="jdbc:mysql://localhost:3306/spring";
3. con = DriverManager.getConnection(url);
Create SQL Statement
createStatement() method is invoked on current Connection object to create
a SQL Statement. Before you can use a Statement object to execute a SQL
statement, you need to create one using the Connection object's
createStatement( ) method, as in the following example −
Syntax
public Statement createStatement() throws SQLException
Example to create a SQL statement
Statement stmt = null;
try {
stmt = conn.createStatement( );
...
catch (SQLException e) {
Statement s=con.createStatement();
Execute SQL Statement
executeQuery() method of Statement interface is used to execute SQL
statements.
Syntax
Commonly used methods of Statement interface:
The important methods of Statement interface are as follows:
1) public ResultSet executeQuery(String sql): is used to execute SELECT query. It ret
ResultSet.
2) public int executeUpdate(String sql): is used to execute specified query, it may
insert, update, delete etc.
3) public boolean execute(String sql): is used to execute queries that may return mult
4) public int[] executeBatch(): is used to execute batch of commands.
public ResultSet executeQuery(String query) throws SQLException
Example to execute a SQL statement
1. int result=stmt.executeUpdate("delete from emp765 where id=33");
ResultSet rs=s.executeQuery("select * from user");
while(rs.next())
{
System.out.println(rs.getString(1)+" "+rs.getString(2));
}
ResultSet
The object of ResultSet maintains a cursor pointing to a row of a table.
Initially, cursor points to before the first row.
Commonly used methods of ResultSet interface
1) public is used to move the cursor to the one row
boolean next(): next from the current position.
2) public is used to move the cursor to the one row
boolean previous from the current position.
previous():
3) public is used to move the cursor to the first row in
boolean first(): result set object.
4) public is used to move the cursor to the last row in
boolean last(): result set object.
5) public is used to move the cursor to the specified
boolean row number in the ResultSet object.
absolute(int
row):
6) public is used to move the cursor to the relative row
boolean number in the ResultSet object, it may be
relative(int positive or negative.
row):
7) public int is used to return the data of specified column
getInt(int index of the current row as int.
columnIndex):
8) public int is used to return the data of specified column
getInt(String name of the current row as int.
columnName):
9) public String is used to return the data of specified column
getString(int index of the current row as String.
columnIndex):
10) public String is used to return the data of specified column
getString(String name of the current row as String.
columnName):
Closing the connection
After executing SQL statement you need to close the connection and release
the session. The close() method of Connection interface is used to close the
connection.
Syntax
public void close() throws SQLException
Example of closing a connection
con.close();
Servlet and JDBC
connection
By the end of the following steps you will be able to configure your eclipse
to run Servlet and JSP programs to connect to the MySQL database.
1. Install MySQL Database.
2. Install Apache Tomcat.
3. Install Eclipse IDE for Java EE Developers.
4. Check whether MySQL is properly installed and running on the
system by following below steps:
o Open terminal or command prompt.
o Enter “mysql -u root -p” (Here the username is root).
o Enter the password when prompted.(Example:123/sql)
o Now create a database using “CREATE DATABASE
EXAMPLE”(Here EXAMPLE is the name of database).
o Select the database using “USE EXAMPLE”.
o Create the tables according the required schema.
5. Check whether Apache Tomcat is properly installed
6. Select the connector for MySQL Database :
Eg: mysql-connector-java-8.012-bin.jar. When you download
MySQL Connector/J it contains mysql-connector-java-5.1.12-bin.jar.
This jar file contains the connection implementations.
7. Place the jar file into common/lib folder hierarchy of Tomcat
Home directory.
8. To configure Eclipse to use Apache Tomcat as server:
o Find the Servers view in Eclipse IDE. You can open it using
menu Window/Show View/Servers.
o Right-click on Servers view and select New/Server.
o Select the server type as Tomcat version installed on your
system and click Next.
o Set the Tomcat installation directory and click finish.
o Now you will find a “Apache Tomcat at localhost” in the
Servers view.
9. To create Project:
o Create a new project using menu File/New/Other and then
Web/Dynamic Web Project.
o Enter the name of the project and select the target runtime
as Apache Tomcat.()
o Set the configuration as Default configuration for Apache
Tomcat and click Finish.
o Select Version (2.5).Select Generate web.xml.
10. To create Servlet:
o Go to the folder hierarchy and right-click on the Src and
select New/Other and then Servlet.
o Enter Class name for Servlet and other details and click
Finish.
11. Connection URL:Format of the Connection URL is
“jdbc:mysql://[host][:port]/[database][?properties][=values]”.
For example,
“jdbc:mysql:localhost:3306/EXAMPLE?user=root&password=123”.
Here,
o host is host name where MySQL Database is
running(localhost or 127.0.0.1),
o port is port number where MySQL Database is
running(Default port number for MySQL is 3306),
o database is database name which is to be
connected(Database Name is EXAMPLE), and
o property is supplied for purposes like username,
password, etc(user is “root” or “student” and password is
“123 “or “sql”).
12. To run Servlet,
o Right-click on the Project name in Project Explorer, and
select Run as/Run on server.
o Select the Apache Tomcat and click finish.
o The output will be generated in browser.
Note: If class not found exception is encountered:
Right click on Project->build path->configure path->deployment
assembly->/src:web-inf/classes->add->archieve form file system.-
>next->add mysql.connector-java-xxxx.jar.
