1. MySql Database Installation
Download the MySQL software from http://dev.mysql.com/downloads/mysql/ Download the right installer EXE(msi file) based on your computer architecture by clicking on download button. An image is shown below for your reference.
You need to create an Oracle account and log in into that to download this msi. Once the msi gets downloaded, just double click on the exe which guides you through further installation.
2. Install the Driver Called Connector/J
This is required to connect to the mysql database using Java code. The same can be downloaded from http://dev.mysql.com/downloads/connector/j the way you download mysql msi. Double click to install the same.
3. Create sample database and a User
3.1: Open the MySQL Command Line Client. When you install mysql, a short cut gets created which should b
e available in Windows->All Programs->MySQL->MySQL Server 5.5 (Windows 7, MySql v5.5). If you can not find this under windows programs, you can start using the following command from command prompt.
"C:\Program Files\MySQL\MySQL Server
5.5\bin\mysql.exe" "--defaults-file=C:\Program Files\MySQL\MySQL
Server 5.5\my.ini"
|
3.2: The command line program asks for the password, supply the password that you had given while installing MySQL msi.
3.3 Now create a sample database and change the database using MySQL command line client to the newly created one as shown below.
Enter password: *********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates.
All rights reserved.
Oracle is a registered trademark of Oracle Corporation
and/or its
affiliates. Other names may be trademarks of their
respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the
current input statement.
mysql> create database mydatabase;
Query OK, 1 row affected (0.19 sec)
mysql> use mydatabase;
Database changed
|
3.4: Create a Database User and Grant privileges using command line client as shown below.
mysql> create user user1 identified by
'userpassword';
Query OK, 0 rows affected (1.28 sec)
mysql> grant usage on *.* to user1@localhost identified
by 'userpassword';
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> grant all privileges on mydatabase.* to
user1@localhost;
Query OK, 0 rows affected (0.02 sec)
|
Now, the database is ready, moving on to Java part where we write a Java program to connect this database called mydatabase using user 'user1' and password 'userpassword'
4. Write a Java Program To Connect to Database & Create a table
4.1: To write Java program, you require an editor for example, Eclipse. Once the Java project is created, you need to place the driver Jar you had downloaded in step2 into Java project CLASSPATH. When you install Connector/J EXE, the jar gets stored under Drive:\Program Files\MySQL\MySQL Connector J\mysql-connector-java-5.1.29-bin.jar. Pace this jar into project's class path.
4.2: Create a simple Java project and create a class called MySqlConnect as shown below.
import
java.sql.Connection;
import
java.sql.DatabaseMetaData;
import
java.sql.DriverManager;
import
java.sql.PreparedStatement;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.sql.Statement;
public class MySqlConnect {
private Connection con = null;
private Statement statement = null;
private PreparedStatement preparedStatement = null;
private ResultSet rs = null;
public void connectToSql(){
try{
//Load the driver
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection
("jdbc:mysql://localhost/mydatabase","user1", "userpassword");
statement = con.createStatement();
DatabaseMetaData dbmd = con.getMetaData();
rs = dbmd.getTables(null, null, "EMPLOYEE", null);
rs.next();
boolean tableExists = (rs.getRow() == 1);
if(!tableExists){
String sql = "CREATE
TABLE EMPLOYEE " +
"(empid
INTEGER not NULL, " +
" firstname
VARCHAR(255), " +
" lastname
VARCHAR(255), " +
" cubicle
VARCHAR(255), " +
" PRIMARY
KEY ( empid ))";
statement.executeUpdate(sql);
}
//Place a record
into EMPLOYEE table
preparedStatement = con
.prepareStatement("insert
into mydatabase.EMPLOYEE values (?, ?,
?, ?)");
preparedStatement.setInt(1, 100);
preparedStatement.setString(2, "John");
preparedStatement.setString(3, "Nelson");
preparedStatement.setString(4, "Cube1");
preparedStatement.executeUpdate();
}catch(SQLException e){
//handle
exception
e.printStackTrace();
}catch(Exception e){
//nandle
exception
e.printStackTrace();
}finally {
try {
if (statement != null) {
statement.close();
}
if (con != null) {
con.close();
}
} catch (Exception e) {
}
}
}
public static void main(String[]
args){
MySqlConnect mySqlConnect = new MySqlConnect();
mySqlConnect.connectToSql();
}
}
|
Note1: If there are two packages to import from a) java.sql.* b) com.mysql.jdbc.*. The difference is that the (b) is specific to MySql database and (a) is generic one. We can (a) for any databases such as Oracle DB, MySQL, DB2 etc.
Note2: Change the primary key 'empid' to be auto_increment and give a default value so that mysql can increment by itself whenever new records are inserted.
Note2: Change the primary key 'empid' to be auto_increment and give a default value so that mysql can increment by itself whenever new records are inserted.
5. Verify if the database table and record are created in MySql DB
Now, you've just created a table called EMPLOYEE under database 'mydatabase'. Open the MySql Command Line Client, issue following commands to verify if the Java program is doing the job or not.
We are done. The table that we created and record that we inserted using Java program are available in MySQL database!
To learn how to connect to MySQL database using Hibernate Java Framework, see next post.
To learn how to connect to MySQL database using Hibernate Java Framework, see next post.
