I have created a simple class (needs a lot of improvements i.e. clean sql statements from code injections) which should be very handy when connecting to databases.
I would really appreciate it if you guys wouldn't mind having a look and may be giving the class a test drive.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.Properties;
public class MyConnection {
private String dbDriver; // database driver
private String url; // database url. check documentation of DB
private String user; // database user name
private String password; // database password
private Properties properties; // use properies instead of user and password
private Connection connect = null; // instance of the connection class
private Statement statement = null; // instance of the statement class
private ResultSet resultSet = null; // instance of the resultset which is returned once a guery is executed.
/* First class Constructor
* @param database driver
* @param database url
* @param user name and password passed as an instance of Properties.
*/
public MyConnection( final String dbDriver, final String url,
final Properties properties ) {
this.dbDriver = dbDriver;
this.url = url;
this.properties = properties;
this.loadDriver();
this.getConnected();
this.getStatement();
} //-- ends class constructor
/* Second class Constructor
* @param database driver
* @param database url
* @param user name
* @param user's password
*/
public MyConnection( final String dbDriver, final String url,
final String user, final String password ) {
this.dbDriver = dbDriver;
this.url = url;
this.user = user;
this.password = password;
this.loadDriver(); // load database driver into memory
this.getConnected(); // establish a connection to the database
this.getStatement(); // get an instance of the Statement object
} //-- ends class constructor
/* Instance method to execute a select query
* @param sql to execute (i.e. select)
* @return result set of the executed query
*/
public ResultSet executeQuery( final String sql ) {
/*
* You should ensure that sql is clean of any injections.
*/
try {
this.resultSet = this.statement.executeQuery( sql );
} catch( SQLException e ) {
e.printStackTrace( System.err );
} //-- ends catch block
return this.resultSet;
} //-- ends instance method executeQuery
/* Instance method to execute queries to the database
* @param sql to execute (i.e. insert)
* @returns int value to indicate number of affected rows or -1 if none were affected
*/
public int executeUpdate( final String sql ) throws SQLException {
return this.statement.executeUpdate( sql );
} //-- ends instance method executeUpdate
/* private instance method that loads the database driver into JVM
*
*/
private void loadDriver() {
//load MySql driver
try {
Class.forName( this.dbDriver ).newInstance();
} catch( Exception e ) {
e.printStackTrace( System.err );
} //-- ends catch block
} //-- ends instance method loadDriver
/*
* private instance method that establishes a connection to the database
*/
private void getConnected() {
try {
if( this.properties == null ) {
this.connect = DriverManager.getConnection( this.url, this.user, this.password );
} else {
this.connect = DriverManager.getConnection( this.url, this.properties );
}
} catch( SQLException e ) {
System.out.println(
"SQL Exception: " + e.getMessage() +
"\nSQLState: " + e.getSQLState() +
"\nVendor Error: " + e.getErrorCode()
);
} //-- ends catch block
} //-- ends instane method getConnected
/* private instance method that instantiates
* an instance of the statement class
*/
private void getStatement() {
try {
this.statement = this.connect.createStatement();
} catch( SQLException e ) {
e.printStackTrace( System.err );
} //-- ends catch block
} //-- ends instance method getStatement
/* instance method to close connection to the database
* and clean resources.
*
*/
public void close() {
if( this.resultSet != null ) {
try {
this.resultSet.close();
this.resultSet = null;
} catch( SQLException e ) {
e.printStackTrace( System.err );
} //-- ends catch block
} //-- ends if
if( this.statement != null ) {
try {
this.statement.close();
this.statement = null;
} catch( SQLException e ) {
e.printStackTrace( System.err );
} //-- ends catch block
} //-- ends if
if( this.connect != null ) {
try {
this.connect.close();
this.connect = null;
} catch( SQLException e ) {
e.printStackTrace( System.err );
} //-- ends catch block
} //-- ends if
} //-- ends instance method close
} //-- ends class definition
I have also create an example to go with this sample, so people can easily test this class. First the SQL table. I have created a database and called "Java_Test" in MySql database.
CREATE TABLE IF NOT EXISTS `users` (
`ID` bigint(20) unsigned NOT NULL auto_increment,
`Name` varchar(65) NOT NULL,
`email` varchar(60) NOT NULL,
`website` varchar(100) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
INSERT INTO `users` (`ID`, `Name`, `email`, `website`) VALUES
(1, 'John Smith', 'john.smith@smithwebsite.com', 'htt://www.smithwebsite.com'),
(2, 'Marco Denmark', 'mdenmark@ddmarcowebsite.com', 'http://www.ddmarcowebsite.com'),
(3, 'George Madless JR', 'gmadless@madnessmad.com', 'http://www.madnessmad.com');
finally, the class that performs the testing.
import java.sql.*;
import java.util.Properties;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
public class MySqlConnection {
private final String DB_DRIVER = "com.mysql.jdbc.Driver";
private String URL = "jdbc:mysql://localhost:3306/Java_Test";
private final String USER = "DATABASE_USER_NAME";
private final String PASSWORD = "USER_PASSWORD";
private Properties credential;
private MyConnection connection;
private ResultSet resultSet;
private JFrame frame;
private final int WIDTH = 300;
private final int HEIGHT = 150;
private JButton showAll, enterNew, addNewUser, clear;
private JLabel name, email, website;
private JTextField nameField, emailField, websiteField;
private JDialog newUserDialog;
private MySqlConnection() {
// connect to MySQL database
this.credential = new Properties();
this.credential.setProperty( "user", USER );
this.credential.setProperty( "password", PASSWORD );
// connect to mysql database
this.connection = new MyConnection( DB_DRIVER, URL, this.credential );
// start creating a GUI
this.frame = new JFrame( "Testing MySql Conenction" );
/* when the frame is closed, we want to ensure that the connection
* to the database is closed. see frame_close(event)
*/
this.frame.setDefaultCloseOperation( JFrame.DO_NOTHING_ON_CLOSE );
this.frame.addWindowListener( new WindowAdapter() {
public void windowClosing( WindowEvent event ) {
frame_close( event );
} //-- ends windowClosing event
}); //-- ends event handler
// initialize GUI components
this.initComponents();
// set frame in middle of the monitor
this.frame.setLocation(
( Toolkit.getDefaultToolkit().getScreenSize().width - WIDTH ) / 2,
( Toolkit.getDefaultToolkit().getScreenSize().height - HEIGHT) / 2
);
// set frame size and make it visible.
this.frame.setSize( WIDTH, HEIGHT );
this.frame.setVisible( true );
} //-- ends class constructor
private void frame_close( WindowEvent event ) {
this.connection.close(); // close the connection to the database..
System.exit( 0 ); // clear memory and exit system
} //-- ends instancem ethod frameClose
private void initComponents() {
Container pane = this.frame.getContentPane();
pane.setLayout( new FlowLayout() ) ;
this.showAll = new JButton( "Show All Users " );
this.showAll.addActionListener( new ActionListener() {
public void actionPerformed( ActionEvent event ) {
showAll_clicked( event );
} //-- ends event actionPerformed
}); //-- ends event handler for showAll
pane.add( this.showAll );
this.enterNew = new JButton( "Enter New User" );
this.enterNew.addActionListener( new ActionListener() {
public void actionPerformed( ActionEvent event ) {
enterNew_clicked( event );
} //-- ends actionPerformeds
}); //-- ends event handler for enterNew
pane.add( this.enterNew );
} //-- ends instance method initComopentns
private void showAll_clicked( ActionEvent event ) {
String sql = "SELECT * FROM Users";
String output = "<html><body><table>";
try {
this.resultSet = this.connection.executeQuery( sql );
while( this.resultSet.next() ) {
output += "</td>" + this.resultSet.getInt("id") + "</td>";
output += "</td>" + this.resultSet.getString("name") + "</td>";
output += "</td>" + this.resultSet.getString( "email" ) + "</td>";
output += "</td>" + this.resultSet.getString( "website" ) + "</td></tr>";
} //-- ends while loop
output += "</table>";
JOptionPane.showMessageDialog( this.frame, output );
output = null;
} catch( SQLException e ) {
e.printStackTrace( System.err );
} //-- ends catch block
} //-- ends instance method showAll_clicked
private void enterNew_clicked( ActionEvent event ) {
this.newUserDialog = new JDialog( this.frame, "Enter New User", true );
this.newUserDialog.setDefaultCloseOperation( JDialog.DISPOSE_ON_CLOSE );
Container pane = this.newUserDialog.getContentPane();
SpringLayout layout = new SpringLayout();
pane.setLayout( layout );
name = new JLabel( "Name: " );
nameField = new JTextField( 20 );
JPanel namePanel = new JPanel( new BorderLayout() );
namePanel.add( name, BorderLayout.LINE_START );
namePanel.add( nameField, BorderLayout.LINE_END );
email = new JLabel( "Email: " );
emailField = new JTextField( 20 );
JPanel emailPanel = new JPanel( new BorderLayout() );
emailPanel.add( email, BorderLayout.LINE_START );
emailPanel.add( emailField, BorderLayout.LINE_END );
website = new JLabel( "Website: " );
websiteField = new JTextField( 20 );
JPanel websitePanel = new JPanel( new BorderLayout() );
websitePanel.add( website, BorderLayout.LINE_START );
websitePanel.add( websiteField, BorderLayout.LINE_END );
pane.add( namePanel );
pane.add( emailPanel );
pane.add( websitePanel );
layout.putConstraint( SpringLayout.NORTH, namePanel, 10, SpringLayout.NORTH, pane );
layout.putConstraint( SpringLayout.WEST, namePanel, 25, SpringLayout.WEST, pane );
layout.putConstraint( SpringLayout.NORTH, emailPanel, 5, SpringLayout.SOUTH, namePanel );
layout.putConstraint( SpringLayout.EAST, emailPanel, 0, SpringLayout.EAST, namePanel );
layout.putConstraint( SpringLayout.NORTH, websitePanel, 5, SpringLayout.SOUTH, emailPanel );
layout.putConstraint( SpringLayout.EAST, websitePanel, 0, SpringLayout.EAST, emailPanel );
this.addNewUser = new JButton( "Add User" );
this.addNewUser.addActionListener( new ActionListener() {
public void actionPerformed( ActionEvent event ) {
addNewUser_clicked( event );
}
});
this.clear = new JButton( "Clear" );
this.clear.addActionListener( new ActionListener() {
public void actionPerformed( ActionEvent event ) {
clear_clicked( event );
}
});
pane.add( this.addNewUser );
layout.putConstraint( SpringLayout.NORTH, this.addNewUser, 5, SpringLayout.SOUTH, websitePanel );
layout.putConstraint( SpringLayout.EAST, this.addNewUser, 0, SpringLayout.EAST, websitePanel );
pane.add( this.clear );
layout.putConstraint( SpringLayout.NORTH, this.clear, 5, SpringLayout.SOUTH, websitePanel );
layout.putConstraint( SpringLayout.EAST, this.clear, -5, SpringLayout.WEST, this.addNewUser );
this.newUserDialog.setSize( 320, 155 );
this.newUserDialog.setResizable( false );
this.newUserDialog.setVisible( true );
} //-- ends instance method enterNew_clicked
private void addNewUser_clicked( ActionEvent event ) {
// make sure that user has enter all required fields
if( "".equals( this.nameField.getText() ) ||
"".equals( this.emailField.getText() ) ||
"".equals( this.websiteField.getText() ) ) {
JOptionPane.showMessageDialog( newUserDialog,
"Please Enter All Fields First", "Warning Message",
JOptionPane.WARNING_MESSAGE );
this.nameField.requestFocus();
} else {
String sql = "INSERT INTO Users (name,email,website ) VALUES('"+
this.nameField.getText()+"','"+ this.emailField.getText()
+"','"+ this.websiteField.getText()+"')";
try {
if( this.connection.executeUpdate(sql) != -1 ) {
JOptionPane.showMessageDialog( newUserDialog,
"User Added Successfully", "Information",
JOptionPane.INFORMATION_MESSAGE );
this.nameField.setText("");
this.websiteField.setText("");
this.emailField.setText("");
this.nameField.requestFocus();
}
}catch( SQLException e ) {
JOptionPane.showMessageDialog( newUserDialog,
e.getMessage(),
"Error", JOptionPane.ERROR_MESSAGE );
} //-- ends catch block
} //-- ends else block
} //-- ends instance method addNewUser_clicked
private void clear_clicked( ActionEvent event ) {
this.nameField.setText("");
this.websiteField.setText("");
this.emailField.setText("");
this.nameField.requestFocus();
} //-- ends instance method clear_clicked
public static void main( String [] args ) {
new MySqlConnection();
} //-- ends class method main
} //-- ends class definition
Please note that you should edit the following lines and the correct user name and password to connect to my sql database.
private final String USER = "DATABASE_USER_NAME";
private final String PASSWORD = "USER_PASSWORD";
I have downloaded the latest JDBC driver from http://dev.mysql.com/downloads/connector/j/5.0.html
Then I uncompressed the zip, and then again uncompressed the contents of the Jar file in the project folder for this example.
Please let me know what you think.
Cheers,
Ess