How to Connect JDBC Driver to a Java Program

Well, when you do programming, its quite common to work with databases. So many people use mysql as their database in most of the cases. So I thought it will be useful for the newcomers to learn how to connect mysql to a java program.

Assuming you have installed mysql in your machine lets look at how we write our codes.
or here are the links for you to get them. :)
Get JDBC Driver for Ubuntu
MySQL-Query-Browser

First you need to create a database for your purpose.

Here is the code for creating a database using mysql -

In Query Browser

CREATE DATABASE testdb;

in phpmyadmin

CREATE DATABASE testdb;

USE testdb;

Then you need to define the tables. Lets create a table called Student including student id, name, address as the fields.

create table student(

stu_id int (2),

stu_name varchar (20),

stu_address varchar (50)

);

Now you need to open your IDE, in my case its Netbeans.

Now create a New Java project giving a name - i.e. testdb
Now goto Libraries in the left panel & right click to get the menu & select add a librabry
From the menu select  
MYSQL JDBC Driver - mysql-connector-java-5.1.6-bin.jar
(you need to download this first from the site)

Then in the java file you need to import below package.
import java.sql.*;

Now within the main method, write these codes

      Connection con = null;
       String url = "jdbc:mysql://localhost:3306/testdb";
       String user = "root";
       String password = "root";

Here you should give your database username & password.
Then within a try block you need to write this.
          con = DriverManager.getConnection(url, user, password);
           Statement st = con.createStatement();

Now you are ready to write queries to insert/upadate & delete records in the table.

1. Insert data to the Database table.

         String sql = ("INSERT student VALUES   ('1','Saman','Colombo')");                                   

      st.executeUpdate(sql);

          System.out.println("Entered");

2. Selecting & Displaying Results

 ResultSet rs = st.executeQuery("SELECT * FROM student WHERE user_id ='1'");       

   while(rs.next())  {      

           int id = rs.getInt("userstu_id");          

          String name = rs.getString("stu_name");       

          String address = rs.getString("stu_address");     

          System.out.println("ID :" + id);        

         System.out.println("Name :" + name);   

         System.out.println("Address :" + address);         

}

3. Deleting Data from the Table

String sql1 = ("DELETE FROM user_info");

st.executeUpdate(sql1);

So hope this little push would help you out guyz. Try more things your own :)