Monday, May 24, 2010

Jdbc Mysql Connection String

In this Tutorial we want to explain you a code that makes you to understand JDBC MysqlConnection String. The code include a class JdbcMysqlConnectionString,Inside this class we have a main method that follow the list of steps -

1)The first step is to import a package java.sql.* - This provides you a network interface that communicate between front end application in java and database.

2)Loading a driver is the next step by calling a class class.forname( ) and accept driver class as argument.

3)DriverManager.getConnection( ) - This is used to built a connection between url and database.

Finally the print ln print the connection value, username and password. In case there is an exception in try block, the subsequent catch block caught and handle the exception occurred in try block.

4)e,printStacktrace( ) - This method print the list of all methods that are currently executed at that time. It also contain message string information about the error.



JdbcMysqlConnectionString.java.java

import java.sql.*;

public class JdbcMysqlConnectionString {

static public final String driver = "com.mysql.jdbc.Driver";
static public final String connection =
"jdbc:mysql://localhost:3306/komal";
static public final String user = "root";
static public final String password = "root";

public static void main(String args[]) {
try {
Class.forName(driver);
Connection con =
DriverManager.getConnection(connection, user, password);

System.out.println("Jdbc Mysql Connection String :");
System.out.println(connection);

System.out.println("User Name :" + user);
System.out.println("Password :" + password);

if (!con.isClosed()) {
con.close();
}

} catch (Exception e) {
e.printStackTrace();
}
}
}
Output

Jdbc Mysql Connection String :
jdbc:mysql://localhost:3306/komal
User Name :root
Password :root

Inserting Image in Database Table

In this section, you will learn to insert an image to the MySQL database table.

Flow of the process :

This program helps the user to insert an image in the MySQL database table. To insert an image, you need to establish a connection with MySQL database. Lets' take a database hibernatetutorial, in our case. This database has a table (Image table). For inserting an image in table, we have used the SQL statement “INSERT INTO Image values(?,?,?)” with the prepareStatement() method. Whenever an image is inserted into the database, it displays “Inserting Successfully”. If it fails to insert an image then it throws an exception.

[Note: The table has a specified field (or data type) to store an image of type e.g. Blob]

Here is the Code of Program :

import java.sql.*;
import java.io.*;

public class insertImage{
public static void main(String[] args) {
System.out.println("Insert Image Example!");
String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/";
String dbName = "hibernatetutorial";
String userName = "root";
String password = "root";
Connection con = null;
try{
Class.forName(driverName);
con = DriverManager.getConnection(url+dbName,userName,password);
Statement st = con.createStatement();
File imgfile = new File("images.jpg");
FileInputStream fin = new FileInputStream(imgfile);
PreparedStatement pre = con.prepareStatement("insert into Image values(?,?,?)");
pre.setInt(1,5);
pre.setString(2,"Durga");
pre.setBinaryStream(3,fin,(int)imgfile.length());
pre.executeUpdate();
System.out.println("Inserting Successfully!");
pre.close();
con.close();
}
catch (Exception e){
System.out.println(e.getMessage());
}
}
}
Download this code.

Output of this Program:

C:\Roseindia\vinod\jdbc4.0>javac insertImage.java

C:\Roseindia\vinod\jdbc4.0>java insertImage
Insert Image Example!
Inserting Successfully!

C:\Roseindia\vinod\jdbc4.0>
Table Name: Image

Insert Data in Table Using Stored Procedure

In this example we are inserting data into a table using stored procedure.

Steps:
1.Create database:
To create database we use syntax: create database database_name. We can create database with the name "Employee" as

mysql> create database Employee;

Query OK, 1 row affected (0.09 sec)
2.Change the database:
We are using use database_name; to change the database

mysql> use employee;
Database changed
3.Create the table:

mysql> create table emp(name char(12),fathername char(12),password char(12));

Query OK, 0 rows affected (0.55 sec)
4.Create procedure :
1.We can use delimiter to create multiple statements. To create delimiter we can use following syntax.

mysql> DELIMITER //

2.To create procedure we use following syntax:
mysql>create procedure procedure_name(IN |OUT | INOUT) param_name type)
3.We write sql statement into begin......end body. To start begin use mysql>begin and to end use: mysql>end;
4.To finish the procedure use mysql>// delimiter.

mysql> delimiter //
mysql> create procedure empproc(in name char(12),in fathername char(12),in password char(12))
-> begin
-> insert into emp values(name,fathername,password);
-> end;
-> //
Query OK, 0 rows affected (0.22 sec)
5.Step to load driver:
To load the driver we are using syntax:
Class.forName("com.mysql.jdbc.Driver").newInstance();

6.Steps to make connection:
We are using getConnection("url/database","user","password") method to create a connection.
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/employee","root","root");

7.Step to call procedure:
We are using CallableStatement to execute a stored procedure into java code. The following syntax is used to create an object of CallableStatement interface.
CallableStatement calstat=conn.prepareCall("{call empproc(?,?,?)}");

8.Step to pass the values into procedure:
To set the values we are using setXXX(). Here XXX is data type Object class name, e.g setString() to pass string in procedure.

9.Step to execute query :
To execute query we can use:
ResultSet rs = calstat.executeQuery();

10.Close connection :
calstat.close();

The code of the program is given below:

import java.sql.*;
public class Insert_EMP{
public static void main(String a[])throws Exception {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn=DriverManager.getConnection("jdbc:mysql:
//localhost:3306/employee","root","root");
CallableStatement calstat=conn.prepareCall("{call
empproc(?,?,?)}");
calstat.setString(1,a[0]);
calstat.setString(2,a[1]);
calstat.setString(3,a[2]);
ResultSet rs = calstat.executeQuery();
conn.close();
calstat.close();
System.out.println("Your data has been inserted into table.");
}
}
The code of the stored procedure is given below:

delimiter //
create procedure empproc(in name char(12),in fathername
char(12),in password char(12))
begin
insert into emp values(name,fathername,password);
end;
//


The output of the program is given below:

C:\rajesh\mysql>javac Insert_EMP.java
C:\rajesh\mysql>java Insert_EMP Rajesh Ram Raju
Your data has been inserted into table.