Easy Way to Connect MySql using JDBC

Hi, i will show you how to connect mysql using jdbc in easy way. For those who does not know what jdbc means, it is actually stand for Java Data Base Connector. There are several things you need to prepare before connecting the mysql server. In this tutorial, i was using WAMP server which is pre-installed with mysql just to demonstrate the connection to the mysql from java programming.

Okay, the requirements for this tutorials are:-

1)  Java IDE (eclipse MARS). You can download here (new version eclipse Neon )

2) WAMP server (assume the package installed is v3.0.6).  You can download here.

3) MySQL connector (i am using 5.1.40 connector). You can download here.

4) Set of Java program (will be shown below).

===================================================

1.1) Install eclipse and open it, create new package and new class name.

===================================================

2.1) After the WAMP server is successfully installed and run, you can see the green “W”  icon on the taskbar(on the most left position):-

We can open the phpmyadmin page by left click on the icon:-

The default username for phpmyadmin is ‘root’ and left blank for password:-

After login, we can see the dashboard panel for our database:-

As highlighted in above image, we can straight away create the database by clicking new and put a name for the database for example here my database name is ‘test1’.

Please be remind to always keep refresh for any changes made.

===================================================

3.1) To loading mysql connector into eclipse IDE, we have to configure the build path of the package, and load external connector jar as shown here:-

a) First right click on the package and choose Build Path > Configure Build Path.

b) After that, Add External JARs:-

c) Choose all the JARs files in the mysql connector folder and ok.:-

===================================================

4.1) Assuming all the requirements has been installed and running properly, you have to write the code which require the connection class to be used and driver manager method.

Below is the example to establish the connection to the mysql:-

public static Connection getConnection() {
String driver = “com.mysql.jdbc.Driver”;
String url = “jdbc:mysql://localhost:3306/test1”;
String username = “root”; //leave blank if none
String password = “”; //leave blank if none

try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}

Below is the sample code for creating table:-

public static Connection writeTable() throws SQLException{
Connection conn2 = getConnection();

String createString=
“CREATE TABLE table8″+
“(ID integer NOT NULL,”+
“LASTNAME varchar(32) NOT NULL,”+
“FIRSTNAME varchar (32) NOT NULL,”+
“Telephone varchar (16) NOT NULL,”+
“PRIMARY KEY (ID))”;
System.out.println(“the db suppose to be written”);

Statement stmt=null;
try{
stmt=conn2.createStatement();
stmt.executeUpdate(createString);
System.out.println(“the db suppose to be written now”);
}
catch(SQLException e){
e.getErrorCode();
System.out.println(“the db have an error:”+e.getSQLState());
}
finally {
if (stmt!=null){stmt.close();}}
return conn2;
}

After you compile and run the code, below result will be appeared:-

After create table, you have to refresh your table by clicking refresh button as below example:-

 

You can check the table created in phpmyadmin page:-

 

And  below code is for inserting data into the table:-

public static Connection populateTable() throws SQLException{
Connection conn2 = getConnection();
Statement stmt=null;
String query = “INSERT INTO `table7`”+”values(125,’Mark’,’Walbergue’,0123456789)”;
try{
stmt=conn2.createStatement();
stmt.executeUpdate(query);}
catch(SQLException e){e.printStackTrace();}
finally{if(stmt!=null){stmt.close();}}
return conn2;

}

 

Keep refresh the table, the data inserted can be viewed as below:-

And below here is the example for viewing the table that we just created:-

private static void viewTable() throws SQLException{
// TODO Auto-generated method stub
Connection conn2 = getConnection();
Statement stmt=null;
String query =
” Select * FROM table8″;
try{
stmt=conn2.createStatement();
ResultSet rs=stmt.executeQuery(query);
while(rs.next()){
int ID=rs.getInt(“ID”);
String lastName=rs.getString(“LASTNAME”);
String firstName=rs.getString(“FIRSTNAME”);
int phone=rs.getInt(“Telephone”);
System.out.println(ID+”\t”+lastName+”\t”+firstName+”\t”+phone+”\t”);
}}
catch(SQLException e){e.printStackTrace();}
finally{if (stmt!=null){stmt.close();}}
}

The result will be shown in the console as below example:-

Main method for this class is shown as below:-

public static void main(String args[]) throws SQLException {

try {

writeTable();
viewTable();
populateTable();
} catch(Exception e) {
System.out.println(e.getMessage());
}

Thats all the examples for establishing connection to the mysql, creating and viewing table and inserting data to table. Hope it can give you an idea how it is works and thanks for viewing this tutorial.

See you again..!