Connecting to the MySQL server with Java – CMS Support CMS Support Technical support for students in the School of Computing and Mathematical Sciences (CMS) Skip to content Home King William labs Contact us King Wiliam lab rules KW Labs Virtual Counter Opening hours Timetables King William Computer Labs KW015 – Powerful PCs KW102 – Gaming PCs KW103A KW103B – Forensic PCs KW116 – Hardware Comms lab KW215 – Gaming PCs KW216 Open areas (KW118, KW218, KW304, KW314, KW318) Virtual Desktop Accessing the CMS Virtual Desktop Software on the CMS Virtual Desktop Application Development Programming ASP (Active Server Pages) Running ASP scripts on stuiis.cms.gre.ac.uk ASP.NET Creating an ASP.NET Web Project in Visual Studio Running ASP.NET Web Projects through the http://stuiis.cms.gre.ac.uk IIS web server Enabling the test form for an ASP.NET web service Sending email using ASP.NET Connecting to SQL Server using Visual Studio Connecting to SQL Server in ASP.NET Connecting to an Oracle database in Visual Studio Connecting to MySQL in Visual Studio Connecting Visual Studio to Microsoft Access Java PHP Connecting to the MySQL server with PHP Sending email using PHP Running PHP scripts on stuiis.cms.gre.ac.uk Running PHP scripts on stuweb.cms.gre.ac.uk Connecting to an Oracle Database using PHP Connecting to SQL Server using PHP Databases Oracle Access Oracle servers using SQL Developer Connecting between Oracle databases Connecting Oracle to SQL Server Creating a User DSN Creating functions and procedures in Oracle Creating tables in Oracle Exporting from Microsoft Access to Oracle Importing to Microsoft Access from Oracle Logging into Oracle for the first time: Recovering dropped tables and managing the recycle bin MySQL Changing your MySQL password Connect to MySQL using MySQL Workbench Connecting to the MySQL server with ASP.NET Connecting to the MySQL server with Java Connecting to the MySQL server with PHP MySQL command line interface SQL Server Change SQL Server Password Using SQL Server Management Studio MongoDB Web Servers Default pages STUIIS.CMS.GRE.AC.UK I: drive STUWEB.CMS.GRE.AC.UK Systems Unix & Linux Logging into Unix Using Putty Using RDP to access X11 Unix Commands Archiving and Compressing Common Unix Commands Navigating the Unix file system Using files and directories Viewing and Searching Files Unix Home Area Using the web server Classroom environment Software Adobe Software Software Center Azure Dev Tools Visual Studio 2019 Office 365 Riverbed Modeler User Guide Memory Errors Launch errors Visual Paradigm Central IT Services Eduroam IT Service Desk Moodle Portal Printing and scanning Remote working Health and Safety Labs Equipment Laptop Loan Scheme Equipment Allocator Equipment Click & Collect Equipment Search 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. Search for: Search CMS Support Information, technical support, guides and news for students in the School of Computing and Mathematical Sciences. Visit the support counter in lab KW116, phone us on 0208 331 8780 or email cms-support@gre.ac.uk. Central IT support - Portal, Office 365, Email, Teams, Moodle Design and Film & TV support - TV Studios, Workshop and Design facilities in Stockwell Street building 11 Search for: Search Recent Posts stuweb – PHP 7.4/8.0 Recent Comments Archives September 2021 Categories Uncategorised Meta Log in Entries RSS Comments RSS Greenwich Blogs Pages maintained by the School of Computing & Mathematical Sciences support team. Powered by Parabola & WordPress.
