MySQL - Part 2 - with JDBC

Logging in with root user to creat database and new user

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql --user=root mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 28

Server version: 5.1.31-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Create Database E_COM_DB

mysql> CREATE DATABASE E_COM_DB;

Query OK, 1 row affected (0.02 sec)

Create user and giving the Grants.

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON E_COM_DB.*

-> TO 'ECOMDEV1'@'LOCALHOST' IDENTIFIED BY 'ECOMDEV1';

Query OK, 0 rows affected (0.00 sec)

mysql> \q

Bye

Loggin into the DB MY_SQL_DB with user ECOMDEV1(-uECOMDEV1) and its password (-pECOMDEV1) ECOMDEV1
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uECOMDEV1 -pECOMDEV1  E_COM_DB

Critical steps in using JDBC

There are five critical steps in using JDBC to manipulate a database:

1. Load and register the JDBC driver classes (programming interface) for the database server that you intend to use.

2. Get a Connection object that represents a connection to the database server (analogous to logging onto the server).

3. Get one or more Statement objects for use in manipulating the database.

4. Use the Statement objects to manipulate the database.

5. Close the connection to the database.

Getting JDBC Driver for mysql. Formally konw as Connector/J 5.1.

Use the link below to download for windows.

http://mysql.oss.eznetsols.org/Downloads/Connector-J/mysql-connector-java-5.1.7.zip

Extract the zip file. mysql-connector-java-5.1.7-bin.jar will be contained in zip. Put the jar file in classpath.

Exceute the main method of the class below to do the same operations as given in the bolg entry above.

package com.spring.test.mysql;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

public class Jdbc11 {

public static void main(String args[]){

System.out.println(

"Copyright 2004, R.G.Baldwin");

try {

Statement stmt=null;

//Register the JDBC driver for MySQL.

Class.forName("com.mysql.jdbc.Driver");

//Define URL of database server for

// database named mysql on the localhost

// with the default port number 3306.

String url = "jdbc:mysql://localhost:3308/mysql";

//Get a connection to the database for a

// user named root with a blank password.

// This user is the default administrator

// having full privileges to do anything.

Connection con = DriverManager.getConnection(url,"root", "");

//Display URL and connection information

System.out.println("URL: " + url);

System.out.println("Connection: " + con);

//Get a Statement object

stmt = con.createStatement();

//Create the new database

stmt.executeUpdate("CREATE DATABASE E_COM_DB");

//Register a new user named auser on the

// database named JunkDB with a password

// drowssap enabling several different

// privileges.

stmt.executeUpdate(

"GRANT SELECT,INSERT,UPDATE,DELETE," +

"CREATE,DROP " +

"ON JunkDB.* TO 'ECOMDEV1'@'localhost' " +

"IDENTIFIED BY 'ECOMDEV1';");

con.close();

}catch( Exception e ) {

e.printStackTrace();

}//end catch

}//end main

}//end class Jdbc11

Refer the link below for more information. Info above are extracted for this link only.

http://www.developer.com/java/data/article.php/3417381