How to connect with MySQL database using Java

Database plays an important role in storing large amount of information in a manageable manner. Connecting Java with database can make J2EE based web app really effective. All the customer information can be stored inside the database. And also its really easy to work with database like mysql.

This article will teach you on how you may connect java with database like mysql

Here we are going to learn following

1. Making connection
2. Selecting and inserting values

First we will start with making some database in mysql along with some table

For this simply download MySql GUI editor, this will make things very easy.

Open the query Browser of Mysql and then make a new Database and make some tables inside it

Fill the table with some data

Now the most important thing while connecting Java with MySql is that you will require a library called mysql-connector-java-5.1.6-bin.jar.Here, the version may vary.You need to include this library in build path.

After this we are ready to make the java program to connect to mysql

Listing1: Connecting to mysql

import java.sql.*;


public class Java2MySql 
{
	public static void main(String[] args) {
		  String url = "jdbc:mysql://localhost:3306/";
		  String dbName = "demo”
		  String driver = "com.mysql.jdbc.Driver";
		  String userName = "root"; 
		  String password = "mypasswd";
		  try {
		  Class.forName(driver).newInstance();
		  Connection conn = DriverManager.getConnection(url+dbName,userName,password);
		  
		  conn.close();
		  } catch (Exception e) {
		  e.printStackTrace();
		  }
		  }
}

Here,

1. We first import java.sql.* so that the program can access all classes defined in this package
2. Now we make a class named Java2MySql and then the main method
3. We define the variables and initialize the variable
4. We define url to contain the connection string to database.Here we tell that we want to connect to mysql running on localhost at port 3306
5. We define the database name as demo.So now we can access information inside demo database
6. We define the driver.Now this tells which type of database we use.For mysql connection driver is com.mysql.jdbc.Driver
7. The user name for mysql is then defined inside username
8. The password is defined inside variable password
9. Now first we load the driver class using Class.forName
10. We make a connection o database using the connection url,database name, username and password.
11. Now we may do any processing here.We will learn that in next section
12. After all processing we close the connection.
13. We put this all inside a try-catch to handle any exception which may occur.

    Now we will learn to select and insert values in database

    Listing2: Selecting and Inserting Values

    import java.sql.*;
    
    
    public class Java2MySql 
    {
    	public static void main(String[] args) {
    		  String url = "jdbc:mysql://localhost:3306/";
    		  String dbName = "demo";
    		  String driver = "com.mysql.jdbc.Driver";
    		  String userName = "root"; 
    		  String password = "mypassword";
    		  try {
    		  Class.forName(driver).newInstance();
    		  Connection conn = DriverManager.getConnection(url+dbName,userName,password);
    		  Statement st = conn.createStatement();
    		  ResultSet res = st.executeQuery("SELECT * FROM  event");
    		  while (res.next()) {
    		  int id = res.getInt("id");
    		  String msg = res.getString("msg");
    		  System.out.println(id + "\t" + msg);
    		  }
    		  int val = st.executeUpdate("INSERT into event VALUES("+1+","+"'Easy'"+")");
    		  if(val==1)
    			  System.out.print("Successfully inserted value");
    		  conn.close();
    		  } catch (Exception e) {
    		  e.printStackTrace();
    		  }
    		  }
    }
    
    

    Here,

14. We make a connection object as told earlier
15. We make a statement object which is used to execute sql query
16. Now we make a ResultSet object which stores result of sql select query.We make a query to select all rows from event table.And then using execute method of statement class we execute the query and the result is stored in the Resultset
17. First the resultset variable points to null so we use the next function so that now it points to the first record obtained after executing query
18. We use the getInt and getString method to obtain the id and the message column from the table.We used getInt because id is integer.getString is used because message is string.
19. Now to insert any value in database we use the executeUpdate method and pass the sql query in it as argument.Now after running if its success then 1 is returned otherwise it return 0.
20. Lastly we close the connection

This is all for this article. See you next time with some more exciting articles