java connectivity with mysql projects

java connectivity with mysql example

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