How to connect java and mysql

In this tutorial, I’m going to show you how you can connect your java application and mysql database.  Without using an IDE or Integrated Development Environment.

 

Requirements

First thing that you have to do is to install the requirements on your computer.

For Connector/J, extract the files.

image

 

Code

First thing that you have to do is to import the packages needed to connect to mysql database:

import java.sql.*;
import java.util.Scanner;
try{  
    String userName = "root"; //username, default is root
    String password = ""; //password, default is none
    String url = "jdbc:mysql://localhost/questions"; //questions is the database
    Class.forName ("com.mysql.jdbc.Driver").newInstance ();
    conn = DriverManager.getConnection (url, userName, password);
catch(Exception e){}

Still inside the try catch, the query. I’m using 3 tables here so the query is a little bit complicated.

PreparedStatement statement = conn.prepareStatement

("SELECT * FROM q_table, choices, answers WHERE q_table.QID='" + number_input + "' AND choices.CID='" + number_input + "' AND answers.AID='" + number_input + "'"); ResultSet result = statement.executeQuery();

Then loop through all the records as long as the query returns true:

while(result.next()){
    System.out.println("Question # " + number_input + "n" + result.getString(2));
    System.out.println("A. " + result.getString(4) + "n" +  "B. " 
                               + result.getString(5) + "n" + "C. " 
                               + result.getString(6) + "n" + "D. " + result.getString(7));
    System.out.println("Answer: ");
    answer=nrew.next();
 
    if(answer.equals (result.getString(9))){
        System.out.println("Your answer is correct!");
    }else{
        System.out.println("Your answer is wrong!");
    }
 
}

As you can see the code is a bit incomplete and unsecure. Because this is just for the purpose of showing how to connect mysql and java. Its up to you to discover how you can secure your applications.

Copying the data that is fetched by result.next() command to an array will make the data that you want to output. Easier to manipulate.

Here’s a sample program that copies the the data in the resultset into an array:

while(result.next()){
   Question[result.getRow()]=result.getString("Question");
   choiceA[result.getRow()]=result.getString("A");
   choiceB[result.getRow()]=result.getString("B");
   choiceC[result.getRow()]=result.getString("C");
   choiceD[result.getRow()]=result.getString("D");
}

What the above code does is to get the String value in the database field that you specified in the database. The database fields here are: Question, A, B, C, and D. Note that the getString() method is overloaded and you can either use the field name or the column number. As I have done in the previous one. I also used another method here, the getRow() method. It doesn’t require any parameter but it returns an integer value for the current row count. The first record on the database will obviously return 0. So index 0 of the array specified will contain the first record in the database. Remember also that this is a while loop. So it actually fetches all the records until it reaches the last record in the database. That is why I specified the size of each array to be 100. So that I won’t get an Array Index Out of bounds error.

 

Executing

You can execute your program and make use of the library for connecting to mysql using the commands below:

javac -cp .;mysqlcon.jar filename.java
java -cp .;mysqlcon.jar filename

-cp means classpath. Remember to include the complete path together with the filename if you didn’t launch your command line on the folder where the java file is.

Just a quick tip, you can launch a command line in any folder by pressing shift key on your keyboard. And right click on any empty space on the explorer window:

image

The command line will automatically have the path of the folder you are currently in:

image

 

Conclusion

That’s it for this tutorial. In the next one, I’ll be showing you how to connect mysql and java using an IDE like Netbeans or Eclipse.

One thought on “How to connect java and mysql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s