Connecting to the MySQL server with Java

Connecting to MySQL from Java using Windows

The sample code below will allow you to connect to MySQL through Java applications in Windows :

import java.sql.Connection;
    import java.sql.DriverManager; 
    import java.sql.SQLException; 
    import java.sql.*;
     
    public class DBConnect { 
        public static void main(String[] args) {
            try { 
                //loads jconnect driver
                Class.forName("com.mysql.jdbc.Driver"); 
            } catch (Exception ex) { 
                System.out.println("this didn't work " + ex); 
            }
    
            // makes db connection
            try {
                Connection conn = DriverManager.getConnection("jdbc:mysql://mysql.cms.gre.ac.uk/mdb_username?user=username&password=password");
                // Do something with the Connection 
                Statement stmt = conn.createStatement(); 
                String query = "select * from table1"; // define query
                ResultSet theresult = stmt.executeQuery(query);
                while(theresult.next()) {
                    int number = theresult.getInt("Col1");
                    //prints the result
                    System.out.println(number);
                }
            } catch (SQLException ex) {
                // handle any errors 
                System.out.println("SQLException: " + ex.getMessage()); 
                System.out.println("SQLState: " + ex.getSQLState()); 
                System.out.println("VendorError: " + ex.getErrorCode()); 
            }
        }
    }
    

Replace ‘username’ (in red) and ‘password’ (in red) with your own username and MySQL password.

For security reasons, you should ensure you use a different password for your SQL Server login than you do for other University systems.

Documentation for JDBC can be found by visiting the following link:
JDBC Documentation


Connecting to MySQL from Java using the Unix system

The following is an example Java application which connects to a MySQL database server:

/* import needed for JDBC access */
import java.sql.*;

/**
 *  MySQL Demo Program
 *		this program is just a little demonstration of the usage
 *		of MySQL in combination with Java JDBC
 */

public class TestMySQL {
	
	public void test ( String host, String database, String userid, String password ) 
			throws Exception {
		/* first, we'll test whether the MySQL driver is installed */
		testDriver ( );

		/* then, we'll get a connection to the database */
		Connection con = getConnection ( host, database, userid, password );
      
		/* we create a table */
		executeUpdate ( con, "create table test (id int not null,text varchar(20))" );
	  
		/* we insert some data */
		executeUpdate(con,"insert into test (id,text) values (1,'first entry')");
		executeUpdate(con,"insert into test (id,text) values (2,'second entry')");
		executeUpdate(con,"insert into test (id,text) values (3,'third entry')");

		/* then we'll fetch this data */
		executeQuery ( con, "select * from test" );

		/* and we'll destroy the table ... */
 		executeUpdate ( con, "drop table test" );
      
		/* finally, we close the database */
		con.close ( );
	}

	/**
	*  Checks whether the MySQL JDBC Driver is installed
	*/	
	protected void testDriver ( ) throws Exception {    
		try {
			Class.forName ( "org.gjt.mm.mysql.Driver" );
			System.out.println ( "MySQL Driver Found" );
		} catch ( java.lang.ClassNotFoundException e ) {
			System.out.println("MySQL JDBC Driver not found ... ");
			throw ( e );
		}
	}

	/**
	*  Returns a connection to the MySQL database
	*/
	protected Connection getConnection ( String host, String database, String userid, String password ) 
			throws Exception {
		String url = "";  /* Connection String */
		String url2 = ""; /* Displayed connection with masked password */
		try {
			url = "jdbc:mysql://" + host + "/" + database + "?user=" + userid +"&password="+password;
			url2 = "jdbc:mysql://" + host + "/" + database + "?user=" + userid +"&password=********"; 
			Connection con = DriverManager.getConnection(url);
			System.out.println("Connection established to " + url2 + "...");
			return con;
		} catch ( java.sql.SQLException e ) {
			System.out.println("Connection couldn't be established to " + url2);
			throw ( e );
		}
	}

	/**
	 *  This method executes an update statement
	 *  @param con database connection
	 *  @param sqlStatement SQL DDL or DML statement to execute
	 */
	protected void executeUpdate ( Connection con, String sqlStatement ) 
			throws Exception {
		try {
			Statement s = con.createStatement ( );
			s.execute ( sqlStatement );      		
			s.close ( );
		} catch ( SQLException e ) {
			System.out.println ( "Error executing sql statement" );
			throw ( e );
		}
	}

	/**
	 *  This method executes a select statement and displays the result
	 *  @param con database connection
	 *  @param sqlStatement SQL SELECT statement to execute
	 */
	protected void executeQuery( Connection con, String sqlStatement ) 
			throws Exception {
		try {
		Statement s = con.createStatement ( );
		ResultSet rs = s.executeQuery( sqlStatement );
			while ( rs.next ( ) ) {
				String id = ( rs.getObject ("id").toString() );
				String text = ( rs.getObject ("text").toString() );
				System.out.println ( "found record : " + id + " " + text );
			}
			rs.close ( );
		} catch ( SQLException e ) {
			System.out.println ( "Error executing sql statement" );
			throw ( e );
		}
	}

	/**
	 *  This one is used to start the program.
	 */
	public static void main ( String args[] ) throws Exception {
		String hostname = "mysql.cms.gre.ac.uk"; /* hostname of database server */
		String database = "mdb_username";        /* database name mdb_[username] */
		String userid = "username";              /* your username */
		String password = "password";            /* your mysql password */
		new TestMySQL ( ).test ( hostname, database, userid, password );
	}
}

To download this example, please visit the following link (right-click > Save As)TestMySQL.java
Java file • 3.80 KB

Then edit the file and change the values of ‘username’ and ‘password’.

To run this code, transfer the file to your Unix area and log into Unix (or click here for details).

At the terminal change to the directory that you transfered the file to and run the following commands:

% javac TestMySQL.java 
% java TestMySQL

Output:

MySQL Driver Found
Connection established to jdbc:mysql://studb.cms.gre.ac.uk/mdb_unixweb?user=unixweb&password=********...
found record : 1 first entry
found record : 2 second entry
found record : 3 third entry

Comments are closed.