- How to connect Java and MySQL (using mysql-connector-java-5.1.24) - Licence Fondamentale d'Informatique
samedi 1 mars 2014

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

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.
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
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
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:
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
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)");
    }
}

0 commentaires:

Enregistrer un commentaire

 
-