Licence Fondamentale d'Informatique

Hello people I am gonna show you here the shortest and simplest way to connect your Java project with MySQL. By the end of this tutorial, you will be able to fetch, insert, or fire any query to your database from Java very easily and yes also you will be able to fetch data in a JTable.

Talking about simplest method, there are just two steps:
Step 1: Download “mysql-connector-java-5.1.24.zip” from below link and extract it anywhere in your computer.

http://dev.mysql.com/downloads/connector/j/
or
http://ge.tt/4QETwMf (Direct download extracted jar file)

Step 2:

CMD users: If you are not using any IDE like net beans or eclipse etc., and executing your project from command prompt then you’ll have to set CLASSPATH to your“mysql-connector-java-5.1.24-bin.jar” file. There are two methods to do this:

Method 1: If you are using this, you will have to execute a command every time you start command prompt. The command is:

set CLASSPATH=%CLASSPATH%; C:\mysql-connector-java-5.1.24-bin.jar;

Note: Use the exact path of where you’ve placed mysql-connector-java-5.1.24-bin.jar. Mine is “C:\mysql-connector-java-5.1.24-bin.jar”

Method 2:

For Windows XP users:

Start>Control Panel>System>Advanced tab>Environment Variables

For Windows Vista or above users:

Right click My Computer>Properties>Advanced system settings link>Environment Variables

Then in System variables section, find variable named CLASSPATH and select it. Click Edit. If the CLASSPATH environment variable does not exist, click New.
Variable Value: Don’t delete the existing value, just add‘;’ at the end followed by your path (C:\mysql-connector-java-5.1.24-bin.jar)
NetBeans users: Create new project, find Libraries in project panel in left

Right click Libraries>Add JAR/Folder>Browse to select your “mysql-connector-java-5.1.24-bin.jar” file.

Eclipse users: Create new project, find your project in project explorer panel in left

Right click on your project>Build Path>Add External Archives>Browse to select your “mysql-connector-java-5.1.24-bin.jar” file.

Now all you need to do is learn how to use this connecter. For that I recommend you to use below class:
create a new package named mySql and in that package create a class namedMySQL and paste below code to that class

package mySql;

import java.sql.*;

import java.util.Vector;

import javax.swing.JOptionPane;

import javax.swing.table.DefaultTableModel;

/**

 *

 * @author Kanak

 */

public class MySQL {

    public Connection con;

    public Statement s;

        public static DefaultTableModel buildTableModel(ResultSet rs){

            try{

                ResultSetMetaData metaData = rs.getMetaData();

                Vector<String> columnNames = new Vector<String>();

                int columnCount = metaData.getColumnCount();

                for (int column = 1; column <= columnCount; column++) {

                    columnNames.add(metaData.getColumnName(column));

                }

                Vector<Vector<Object>> data = new Vector<Vector<Object>>();

                while (rs.next()) {

                    Vector<Object> vector = new Vector<Object>();

                    for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {

                        vector.add(rs.getObject(columnIndex));

                    }

                    data.add(vector);

                }

                return new DefaultTableModel(data, columnNames);

            }catch(Exception e){

                return null;

            }

        }

    public ResultSet open(String query)

    {

            System.out.println(query);

        try {

            Class.forName("com.mysql.jdbc.Driver"); // Load Driver

            /*

             * PLEASE NOTE

             * CHANGE DATABASE, USERNAME AND PASSWORD

             * FROM THE BELOW LINE

             */

            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/DATABASE","USERNAME","PASSWORD");

            s = con.createStatement(); // Create Statement

            s.execute(query); // Execute Query

            ResultSet rs = s.getResultSet(); // return the data from Statement

                        if(rs==null) {

                            s.execute("SELECT CURRENT_TIME;");

                            rs = s.getResultSet();

                        }// into ResultSet

            return rs;

        } catch (Exception e) {

            JOptionPane.showMessageDialog(null, e, "Exception in Database", 2);

            return null;

        }

    }

    public void close() {

        try {

            con.close();

            s.close();

        } catch (Exception e) {

            JOptionPane.showMessageDialog(null, e, "Exception in Database", 2);

        }

    }

}

That’s all now wherever you need to use your database:

import mySql package
create MySQL object
call method open(String query);

example:

import java.sql.ResultSet;

import java.sql.SQLException;

import javax.swing.JTable;

import mySql.MySQL;

public class Demo {

    static MySQL sqlObj = new MySQL();

    public static void main(String[] args) {

        //Display result by fetching data from database

        ResultSet rs = sqlObj.open("SELECT * FROM table");

        try{

            while(rs.next()){

                System.out.println(rs.getString("COLUMN1_NAME")+" "+

                        rs.getString("COLUMN2_NAME")+" "+ rs.getString("COLUMN3_NAME"));

            }

        }catch(SQLException ex){

            System.out.println(ex);

        }

        //if you have any jTable and need to pass result to it, use below syntax

        JTable jTable1 = new JTable();

        jTable1.setModel(MySQL.buildTableModel(rs));

        // to insert new row, use :

        //NOTE: this will automatically show up error message on failure and will return null

        sqlObj.open("INSERT INTO table VALUES(value1, value2, value3)");

    }

}

AhmedOumezzine

11:30

How to connect Java and MySQL (using mysql-connector-java-5.1.24)