More Tutorials
GETTING STARTED WITH MySQL

This document summarizes the steps necessary for accessing MySQL database management system as well as using JDBC to connect to MySQL from Java programs.

CSIL Lab has setup one database for each student on gemini, students have full access to his/her own database. The initial username/password should be studentid/studentnumber.

Using Command Line MySQL management

To use mysql database management system, you can go through command line method.

Following is a typical steps to give a simple demo on how to connect to MySQL database, create a new table, insert records into the table, and execute SQL queries on the table.

Log on to gemini.csil.sfu.ca: (in your case, replace tlie by your own Unix ID)

 

72: tlie_gemini% cd /WWW/mysql/bin //change to the bin sub-directory of mysql /WWW/mysql/bin

73: tlie_gemini% mysql --user='tlie' --password='200001234' tlie

//I connect to database 'tlie' with username/password combination = 'tlie'/ '200001234'

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 197140 to server version: 3.23.27-beta

Type 'help;' or '\h' for help. Type '\c' to clear the buffer

mysql> create table hellomysql (title char(20));

//create a new table 'hellomysql' in the database

Query OK, 0 rows affected (0.01 sec)

mysql> insert into hellomysql (title) values ('Titanic');

//insert a new tuple 'title="Titanic"' in the table 'hellomysql'

Query OK, 1 row affected (0.00 sec)

mysql> insert into hellomysql (title) values ('Pokemon 2000');

//insert a new tuple 'title="Pokemon 2000"' in the table 'hellomysql'

Query OK, 1 row affected (0.00 sec)

mysql> select * from hellomysql;

//show everything in table hellomysql

+--------------+

| title                |

+--------------+

| Titanic           |

| Pokemon 2000 |

+--------------+

2 rows in set (0.00 sec)

mysql> \q

Bye

74: tlie_gemini%

 

Using JDBC to access MySQL

To use mysql database management system, you can can also use JDBC in your Java program or Java Servlets. You need two additional components to acheive this. First, you need JDBC classes package which provides you the standard API for SQL manipulation and Database connection. Second, you need a special designed Driver connecting to MySQL database system.

Following is a simple example explaining the process to connect, to submit query, to process the query result, and to disconnect to the dababase:

This program just presents yo

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class jjj extends HttpServlet

{	public Connection dbc;
	
	public void init(ServletConfig g) throws ServletException
	
	{	super.init(g);
		dbc=null;
		try
		{	Class.forName("org.gjt.mm.mysql.Driver").newInstance(); //register Driver for MySQL
			dbc=DriverManager.getConnection("jdbc:mysql://localhost:3306/tlie","tlie","200001234"); 
                                                  //connecting to MySQL
		}
		catch (Exception e)
		{	e.printStackTrace();
		}
	}

public void destroy() {
   try {
     dbc.close();
   }
   catch (Exception e){
     e.printStackTrace();
   }
}     

	
	public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException

	{	res.setContentType("text/html");
		PrintWriter out=res.getWriter();

		out.println("<head > < /head>");
		out.println("Hello!");

		
		try
		{
		Statement stmt=dbc.createStatement();
		stmt.execute("select * from hellomysql");
		ResultSet rs=stmt.getResultSet();

		while (rs.next())
		{	out.println(rs.getString("title"));
		}

		rs.close();
		stmt.close();
		}

		catch (SQLException e)
		{	e.printStackTrace();	}
	}
	
}

You have to include the path to mysql jdbc driver--/WWW/jdbc_driver/mysql_comp.jar to your classpath.
More Tutorials