web analytics
Google + RSS Feed

How to connect with MySQL database using Java

0

February 18, 2014 by admin

Imporatant link for poi api use and download sample files:-

http://howtodoinjava.com/2013/06/19/readingwriting-excel-files-in-java-poi-tutorial/

 

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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 databaseListing2: Selecting and Inserting Values
      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
      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,

      • We make a connection object as told earlier
      • We make a statement object which is used to execute sql query
      • 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
      • 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
      • 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.
      • 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.
      • Lastly we close the connection

 

OR :-

 

  1.  ConnectMySQL.java
  2.  */
  3. package com.javaworkspace.connectmysql;
  4. import java.sql.Connection;
  5. import java.sql.DriverManager;
  6. import java.sql.ResultSet;
  7. import java.sql.Statement;
  8. /**
  9.  * @author www.javaworkspace.com
  10.  *
  11.  */
  12. public class ConnectMySQL {
  13.     public static void main(String[] args) {
  14.         try {
  15.             Class.forName(“com.mysql.jdbc.Driver”);
  16.             Connection connection = DriverManager.getConnection(
  17.                     “jdbc:mysql://localhost:3306/EMPLOYEE”, “root”, “root”);
  18.             Statement statement = connection.createStatement();
  19.             ResultSet resultSet = statement
  20.                     .executeQuery(“SELECT EMPNAME FROM EMPLOYEEDETAILS”);
  21.             while (resultSet.next()) {
  22.                 System.out.println(“EMPLOYEE NAME:”
  23.                         + resultSet.getString(“EMPNAME”));
  24.             }
  25.         } catch (Exception e) {
  26.             e.printStackTrace();
  27.         }
  28.     }
  29. }

0 comments »

Leave a Reply

Your email address will not be published. Required fields are marked *


six − = 5

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Recent Posts