MySQL and Java JDBC ,Connection to database with Java

By Sitansu S Swain

package com.demo.mysqlaccess;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MySqlAccess {
private Connection connection = null;
private Statement statement = null;
private PreparedStatement preparedStatement = null;
private ResultSet resultSet = null;

public void readDataBase() throws Exception {
try {
// This will load the MySql Driver, each DB has its own driver
Class.forName("com.mysql.jdbc.Driver");

// Setup connection with the DB
connection = DriverManager.getConnection("jdbc:mysql://localhost/springdemoproject?" + "user=root&password=");

// Statement allow to issue the Sql queries to the Database
statement = connection.createStatement();

// ResultSet gets the result of the Sql Query.
resultSet = statement.executeQuery("SELECT * FROM login");
writeResultSet(resultSet);

// preparestatement can use variables and more efficient
preparedStatement = connection.prepareStatement("insert into login values(default,?,?,?,?)");

// Here parameter start with index 1
preparedStatement.setString(1, "Sitansu");
preparedStatement.setString(2, "sitansudev");
preparedStatement.setInt(3, 97189);
preparedStatement.setString(4, "orissa");
preparedStatement.executeUpdate();
preparedStatement = connection.prepareStatement("select name,password from login");
resultSet = preparedStatement.executeQuery();
writeResultSet(resultSet);

// Remove again the insert login
preparedStatement = connection.prepareStatement("delete from login where name=? ;");
preparedStatement.setString(1, "Sitansu");
preparedStatement.executeUpdate();
resultSet = preparedStatement.executeQuery("select * from login");
metaDateResultSet(resultSet);
} catch (Exception e) {
throw e;
} finally {
close();
}
}

private void metaDateResultSet(ResultSet resultSet) throws SQLException {
// Now get the metadata from thye database
System.out.println("The column in the table are....");
System.out.println("Table :" + resultSet.getMetaData().getTableName(1));
for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
System.out.println("column :" + i + " " + resultSet.getMetaData().getColumnClassName(i));

}
}

private void writeResultSet(ResultSet resultSet) throws SQLException {
// resultSet initialised before the first data set
while (resultSet.next()) {
// It is possible to get the columns via name
// also posssible to get the column via column no
// which starts at 1
// e.g., resultSet.getSTring(2);
System.out.println("User:" + resultSet.getString("name"));
System.out.println("password:" + resultSet.getString("password"));
}
}

// you need to close all three to make sure
private void close() {
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

package com.demo.mysqlaccess;

public class TestDataBase {

public static void main(String[] args) throws Exception{

// TODO Auto-generated method stub

MySqlAccess mySqlAccess = new MySqlAccess();

mySqlAccess.readDataBase();

}

}