THE NEW CREATOR: java connectivity with mysql example

Showing posts with label java connectivity with mysql example. Show all posts

Thursday, 13 March 2014

java connectivity with mysql projects

Create Database

To create a database you have to supply CREATE DATABASE command followed by the database name and then semicolon.

mysql> CREATE DATABASE EXPDB;
Query OK, 1 row affected (0.08 sec)

mysql>

Use Database

Once you have created the database then you have to select it for use to perform operations on it. Command USE <DATABASE-NAME> begins a mysql (The MySQL Command-line Tool) session and lets you perform database operations. Note that, you need to create database only once but have to use it each time you start a mysql session.

mysql> USE EXPDB;
Database changed

mysql>

Create a table

The EXPTABLE, example table to demonstrate JDBC (Java database connectivity) is created by issuing CREATE TABLE command as shown below:

mysql> CREATE TABLE EXPTABLE (
    -> ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> NAME VARCHAR (50)
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql>

Insert Records

Just for illustration, two records into EXPTABLE are being inserted, you can insert more if you like. Later we will perform select and edit operations on these records using JDBC (Java database connectivity).

   
mysql> INSERT INTO EXPTABLE (NAME) VALUES ("ANUSHKA K");
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO EXPTABLE (NAME) VALUES ("GARVITA K");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM EXPTABLE;
+----+-----------+
| ID | NAME      |
+----+-----------+
|  1 | ANUSHKA K |
|  2 | GARVITA K |
+----+-----------+
2 rows in set (0.03 sec)

----------------------------------------------------------------------

java database connectivity code with mysql example

/* JDBC_Connection_Demo.java */
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JDBC_Connection_Demo
{
  /* static block is executed when a class is loaded into memory 
   * this block loads MySQL's JDBC driver
   */
  static
  {
    try
    {
      // loads com.mysql.jdbc.Driver into memory
      Class.forName("com.mysql.jdbc.Driver");
    } 
    catch (ClassNotFoundException cnf) 
    {
      System.out.println("Driver could not be loaded: " + cnf);
    }
  }
 
  public static void main(String[] args)
  {
    String connectionUrl = "jdbc:mysql://localhost:3306/EXPDB";
    String dbUser = "root";
    String dbPwd = "TYPE YOUR PASSWORD";
    Connection conn;
    ResultSet rs;
    String queryString = "SELECT ID, NAME FROM EXPTABLE";
 
    try
    {
      conn = DriverManager.getConnection(connectionUrl, dbUser, dbPwd);
      Statement stmt = conn.createStatement();
 
      // INSERT A RECORD
      stmt.executeUpdate("INSERT INTO EXPTABLE (NAME) VALUES (\"TINU K\")");
 
      // SELECT ALL RECORDS FROM EXPTABLE
      rs = stmt.executeQuery(queryString);
 
      System.out.println("ID \tNAME");
      System.out.println("============");
      while(rs.next())
      {
        System.out.print(rs.getInt("id") + ".\t" + rs.getString("name"));
        System.out.println();
      }
      if (conn != null)
      {
        conn.close();
        conn = null;
      }
    }
    catch (SQLException sqle) 
    {
      System.out.println("SQL Exception thrown: " + sqle);
    }
  }
} //JDBC_Connection_Demo ends here

---------------------------------------------------------------------------

TO RUN IT ON CMD

E:\java mysql code driver>javac JDBC_Connection_Demo.java

E:\java mysql code driver>java -cp . JDBC_Connection_Demo
ID      NAME
============
1.      ANUSHKA K
2.      GARVITA K
3.      TINU K

java database connectivity code with mysql example

Create database in mysql as:- aavikme.

   import java.sql.*;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.Properties;

    public class MySQLConnectExample {
    static
      {
        try
        {
          // loads com.mysql.jdbc.Driver into memory
          Class.forName("com.mysql.jdbc.Driver");
        }
        catch (ClassNotFoundException cnf)
        {
          System.out.println("Driver could not be loaded: " + cnf);
        }
      }

        public static void main(String[] args) {

            // creates three different Connection objects
            Connection conn1 = null;
            Connection conn2 = null;
            Connection conn3 = null;
     try {
               Class.forName("com.mysql.jdbc.Driver");

                // connect way #1
                String url1 = "jdbc:mysql://localhost:3306/aavikme";
                String user = "root";
                String password = "your password";

                conn1 = DriverManager.getConnection(url1, user, password);
                if (conn1 != null) {
                    System.out.println("Connected to the database test1");
                }

                // connect way #2
                String url2 = "jdbc:mysql://localhost:3306/aavikme?user=root&password=your password";
                conn2 = DriverManager.getConnection(url2);
                if (conn2 != null) {
                    System.out.println("Connected to the database test2");
                }

                // connect way #3
                String url3 = "jdbc:mysql://localhost:3306/aavikme";
                Properties info = new Properties();
                info.put("user", "root");
                info.put("password", "your password");

                conn3 = DriverManager.getConnection(url3, info);
                if (conn3 != null) {
                    System.out.println("Connected to the database test3");
                }
       } catch (SQLException ex) {
                System.out.println("An error occurred. Maybe user/password is invalid");
                ex.printStackTrace();
       } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
       }
      }
     }


save it as MySQLConnectExample.java
DONE IT

Run it in cmd

E:\java mysql code driver>javac MySQLConnectExample .java

E:\java mysql code driver>java -cp . MySQLConnectExample

SAVE ALL FILE AS SHOWN IN PIC.

Monday, 10 March 2014

java database connectivity code with mysql

jdbc connectivity with mysql

For MySQL 5.1
For Windows users only

Pre-Requisites:

jdk1.6.0_11(Works with previous versions of JDK) can be downloaded from here
MySQL Server 5.1
mysql-connector-java-3.0.11-stable-bin.jar or Download mysql Connector/J

java connectivity with mysql projects,

Environment Variables:
Enter Variable name and Variable value which is mentioned below.

Variable name:	JAVA_HOME
Variable value:	C:\Program Files\Java\jdk1.6.0_11

Variable name:	PATH
Variable value:	C:\Program Files\Java\jdk1.6.0_11\bin

Variable name:	CLASSPATH
Variable value:	.;C:\MysqlDriver\lib\mysql-connector-java-3.0.11-stable-bin.jar

Simple java program to connect Mysql with java using JDBC

java connectivity with mysql example,

java database connectivity code with mysql example

THE NEW CREATOR