Oracle MySql Mssql JDBC connection

Java JDBC connection example

example to show you how to connect to MySQL  or Oracle or any other database via a JDBC driver.
First create database

CREATE DATABASE CUSTOMER

Create Table

CREATE TABLE `Customer`.`CUSTOMER_LOGIN` ( `ID` INT(10) NOT NULL AUTO_INCREMENT , `USERNAME` VARCHAR(40) NOT NULL , `PASSWORD` VARCHAR(40) NOT NULL , PRIMARY KEY (`ID`)) ENGINE = MyISAM;

Let’s create a simple JDBC Example Project and see how JDBC API helps us in writing loosely-coupled code for database connectivity.

Properties file to store database information

DBConnection.properties

serverAddress =localhost
portNumber =3306
databaseName =CUSTOMER
databaseUser =root
databasePass =root
databaseVender =mysql

Reading properties file class
ReadPropertiesFile.java

/**
*
* @author rashim.dhaubanjar
*/
public class ReadPropertiesFile {

private static Properties dbConnectionProperties;
private PropertiesDAO propertiesDAO;

public void readRecord() {
try {
dbConnectionProperties = new Properties();
dbConnectionProperties.load(new FileInputStream("DBConnection.properties"));
} catch (Exception e) {
e.printStackTrace();
}
propertiesDAO = new PropertiesDAO();

PropertiesDAO.setDatabaseName(dbConnectionProperties.getProperty("databaseName").trim());
PropertiesDAO.setDatabasePass(dbConnectionProperties.getProperty("databasePass").trim());
PropertiesDAO.setDatabaseServer(dbConnectionProperties.getProperty("serverAddress").trim());
PropertiesDAO.setDatabaseUser(dbConnectionProperties.getProperty("databaseUser").trim());
PropertiesDAO.setDatabaseVendor(dbConnectionProperties.getProperty("databaseVender").trim());
PropertiesDAO.setPortNumber(dbConnectionProperties.getProperty("portNumber").trim());
}
}

Establish the connection with database
DatabaseConnection.java


/**
*
* @author rashim.dhaubanjar
*/
public class DatabaseConnection {

public static Connection getConnection() {
System.out.println("Connecting to server...\n");
Connection cn = null;
String url = null;
String con = null;
//PropertiesDAO propertiesDAO=new PropertiesDAO();
try {
if (PropertiesDAO.getDatabaseVendor().equalsIgnoreCase("oracle")) {
url = "jdbc:oracle:thin:@" + PropertiesDAO.getDatabaseServer() + ":" + PropertiesDAO.getPortNumber() + ":" + PropertiesDAO.getDatabaseName();
con = "oracle.jdbc.driver.OracleDriver";
} else if (PropertiesDAO.getDatabaseVendor().equalsIgnoreCase("mysql")) {
url = "jdbc:mysql://" + PropertiesDAO.getDatabaseServer() + ":" + PropertiesDAO.getPortNumber() + "/" + PropertiesDAO.getDatabaseName();
con = "com.mysql.jdbc.Driver";
} else if (PropertiesDAO.getDatabaseVendor().equalsIgnoreCase("mssql")) {
url = "jdbc:sqlserver://" + PropertiesDAO.getDatabaseServer() + ":" + PropertiesDAO.getPortNumber() + ";DatabaseName=" + PropertiesDAO.getDatabaseName();
con = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
}
Class.forName(con);
cn = DriverManager.getConnection(url, PropertiesDAO.getDatabaseUser(), PropertiesDAO.getDatabasePass());
System.out.println("Databbase Server Connected !\n");
} catch (Exception ex) {
ex.printStackTrace();
}
return cn;

}

public static void main(String[] args) {
new ReadPropertiesFile().readRecord();
DatabaseConnection.getConnection();
}
}

Data Transfer Object

CustomerLogin.java


/**
*
* @author rashim.dhaubanjar
*/
public class CustomerLogin {

private Long id;
private String username;
private String password;

public long getId() {
return id;
}

public void setId(long id) {
this.id = id;
}

public String getPassword() {
return password;
}

public void setPassword(String password) {
this.password = password;
}

public String getUsername() {
return username;
}

public void setUsername(String username) {
this.username = username;
}
}

Main Class

/**
*
* @author rashim.dhaubanjar
*/
public class MainClass {

Statement stat = null;
ResultSet rs = null;
String sql;
Connection cn;
CustomerLogin customerLogin;
List<CustomerLogin> customerLogins;

public MainClass() {
new ReadPropertiesFile().readRecord();
cn = DatabaseConnection.getConnection();
customerLogin = new CustomerLogin();
customerLogins = new ArrayList<CustomerLogin>();
}

private void insertCustomer() {
try {
stat = cn.createStatement();
sql = "INSERT INTO `CUSTOMER_LOGIN` (`USERNAME`, `PASSWORD`) VALUES "
+ "('John', 'papa'), "
+ "('Ramesh', 'doggy');";
if(stat.execute(sql)){
System.out.println("Data inserted successfully");
}
while (rs.next()) {
customerLogin = new CustomerLogin();
customerLogin.setId(rs.getLong("ID"));
customerLogin.setUsername(rs.getString("USERNAME"));
customerLogin.setPassword(rs.getString("PASSWORD"));
customerLogins.add(customerLogin);
}
} catch (Exception e) {
e.printStackTrace();
}
}

public void getCustomer() {
try {
stat = cn.createStatement();
sql = "SELECT * FROM CUSTOMER_LOGIN ";
rs = stat.executeQuery(sql);
while (rs.next()) {
customerLogin = new CustomerLogin();
customerLogin.setId(rs.getLong("ID"));
customerLogin.setUsername(rs.getString("USERNAME"));
customerLogin.setPassword(rs.getString("PASSWORD"));
customerLogins.add(customerLogin);
}

System.out.println("\n All data  Retrived !\n");
System.out.println("processing data size of " + customerLogins.size());

for (int i = 0; i < customerLogins.size(); i++) {
CustomerLogin custLogin = customerLogins.get(i);
System.out.println("**********************************");
System.out.println("customer id = " + custLogin.getId());
System.out.println("customer username = " + custLogin.getUsername());
System.out.println("customer password = " + custLogin.getPassword());
}
} catch (Exception e) {
e.printStackTrace();
}
}

public static void main(String[] args) {
MainClass main = new MainClass();
main.insertCustomer();
main.getCustomer();
}

}

Output:

**********************************
customer id = 5
customer username = John
customer password = papa
**********************************
customer id = 6
customer username = Ramesh
customer password = doggy