// 2012-04-08 johnpfeiffer
/* ensure you download the appropriate driver, i.e. http://dev.mysql.com/downloads/connector/j/
right click on the project -> Build Path -> Add Libraries -> Connectivity Driver Definition -> New Driver Definition ->
Available Templates (scroll and select MySQL JDBC Driver 5.1) -> Jar List (tab) -> Add Jar/Zip (browse)
Project Explorer will now display a MySQL JDBC Driver in the Project
For a Servlet mysql-connector-java-5.1.19-bin.jar must be in the WEB-INF/lib directory,
F5 refresh the project, then right click on the driver file above and "Add to Build Path"
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class Main
{
String dbms;
String host;
String port;
String user;
String password;
public static void main( String args[] )
{
Main m = new Main();
Connection c = null;
Statement stmt = null;
ResultSet rs = null; //cursor in the remote database = a table of data representing a database result set
try{
m.dbms = "mysql";
m.host = "localhost";
m.port = "3306";
m.user = "root";
m.password = "password";
String query = "show databases;";
c = m.getConnection();
stmt = c.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY ); //the default are these paramaters but to be safest
rs = stmt.executeQuery( query ); //better are prepared statements as the DBMS can just run the PreparedStatement SQL statement without having to compile it first
ResultSetMetaData rsmeta = rs.getMetaData();
int columnCount = rsmeta.getColumnCount();
while( rs.next() )
{
for ( int i = 1 ; i <= columnCount ; i++ ) //column counting starts at 1
{
String name = rs.getObject( i ).toString();
String columnName = rsmeta.getColumnName( i );
String columnLabel = rsmeta.getColumnLabel( i );
String columnTypeName = rsmeta.getColumnTypeName( i );
System.out.print( "COLUMN " + i + " = " + name + " , columnName: " + columnName );
System.out.println( " , Label: " + columnLabel + " , Type: " + columnTypeName );
}
}
stmt.close();
rs.close();
c.close();
}catch( SQLException sqle )
{ System.err.println( sqle.getMessage() );
}
catch( Exception e )
{ System.err.println( e.getMessage() ) ;
}
finally
{
try{
if( stmt != null ){ stmt.close(); }
if( rs != null ){ rs.close(); }
if( c != null ){ c.close(); }
}catch( Exception e )
{}
}
}
//In previous versions of JDBC, to obtain a connection, you first had to initialize your JDBC driver by calling the method Class.forName.
//This methods required an object of type java.sql.Driver. Each JDBC driver contains one or more classes that implements the interface java.sql.Driver. The drivers for Java DB are org.apache.derby.jdbc.EmbeddedDriver and org.apache.derby.jdbc.ClientDriver, and the one for MySQL Connector/J is com.mysql.jdbc.Driver. See the documentation of your DBMS driver to obtain the name of the class that implements the interface java.sql.Driver.
protected Connection getConnection() throws SQLException
{
Connection conn = null;
Properties connectionProps = new Properties();
connectionProps.put( "user" , this.user );
connectionProps.put( "password" , this.password );
if( this.dbms.equals( "mysql" ) )
{ conn = DriverManager.getConnection( "jdbc:" + this.dbms + "://" + this.host + ":" + this.port + "/mysql", connectionProps );
}else if( this.dbms.equals( "derby" ) )
{// conn = DriverManager.getConnection( "jdbc:" + this.dbms + ":" + this.dbName + ";create=true", connectionProps );
}
System.out.println( "DEBUG: Connected to database" );
return conn;
}
//NOTE if you leave the default /mysql database off of the end of the connection you may get errors
} //end class