Pages

January 24, 2012

Writing JDBC Applications with MySQL

Please find one simple example of using JDBC with MySQL in java.

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

public class MySQLAccess {

private Connection connect = 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 the connection with the DB
connect = DriverManager.getConnection("jdbc:mysql://localhost/testdb?" +  user=user&password=pwd");

// Statements allow to issue SQL queries to the database
statement = connect.createStatement();

// Result set get the result of the SQL query
resultSet = statement.executeQuery("select * from testdb.testtable");

PrintResultSet(resultSet);

// PreparedStatements can use variables and are more efficient
preparedStatement = connect.prepareStatement("
 insert into testtable values ( ?, ?, ?, ? , ?, ?)");

// Parameters start with 1
preparedStatement.setString(1, "va1ue1");
preparedStatement.setString(2, "va1ue2");
preparedStatement.setString(3, "value3");
preparedStatement.setDate(4, new java.sql.Date(2011, 01, 01));
preparedStatement.setString(5, "value4");
preparedStatement.executeUpdate();

preparedStatement = connect.prepareStatement("
SELECT col1, col2, col3, col4 from testtable ");
resultSet = preparedStatement.executeQuery();
PrintResultSet(resultSet);

  }catch (Exception e) { throw e; }
   finally { close();}
}


private void PrintMetaData(ResultSet resultSet) throws SQLException {

// Result set get the result of the SQL query
System.out.println("The columns 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().getColumnName(i));
}
}

//=======================================================================

private void PrintResultSet(ResultSet resultSet) throws SQLException {
 
 // ResultSet is initially before the first data set
    while (resultSet.next()) {
// It is possible to get the columns via name
// also possible to get the columns via the column number
// which starts at 1
// e.g. resultSet.getSTring(2);
String val1    = resultSet.getString(1);
String website = resultSet.getString(2);
String summery = resultSet.getString(3);
Date date      = resultSet.getDate(4);
String comment = resultSet.getString(5);

System.out.println("val1: "    + val1);
System.out.println("val2: " + val2);
System.out.println("val3: " + val3);
System.out.println("val4: "    + val4);
}
  }

//=======================================================================

// You need to close the result Set
private void close() {

    if (resultSet != null) {resultSet.close();}
    if (statement != null) {statement.close();}
    if (connect != null)   {connect.close();}
}

1 comment:

  1. Good sample code. Helpful. thanks

    ReplyDelete