I found this information somewhere and l want to share it with everybody. It’s a note about basic mysql.
MySQL is the most popular open source relational database system. It is suitable for small to large web applications and is widely used. It can interface with PHP, Java, C, and Perl. It is available for free under the GNU public license.
This document contains
- Instructions for downloading, installing, and configuring MySQL in Windows environment.
- Instructions for downloading, installing, and configuring the Java jar file that contains the driver that interfaces with the MySQL database, including setting the environment variable Classpath.
- Simple Java Page example to test interfacing with MySQL, including reference to sample database, user name and password, turning on MySQL as a service as well
as Apache Tomcat server, and simple SQL query to database.
- Interfacing with the MySQL database from the command prompt using the mysql program, navigating to the correct directory, notations for providing user name and password.
- Examples of mysql SQL commands used with a sample database provided with the installation package.
- How to change the user password.
- Installing and using the MySQL GUI tool MySQLQueryBrowser.
- Java Servlets application with MySQL
Setting up MySQL on Windows:
At http://dev.mysql.com/downloads/mysql/4.1.html scroll down to Windows, and download the Windows essentials package .
Once the file is done downloading, execute it to begin the installation.
Select Typical and then press next.
Now press install.
Select skip signup and then click next.
Make sure configure MySQL server is checked, and press finish.
Select detailed configuration and click next.
Select developer machine and click next.
Select multifunctional database and click next.
Leave these settings the same and click next.
Select manual setting and change the number of connections to 5.
Leave Enable TCP/IP checked and click next.
Leave standard character set selected and click next.
You can choose to install MySQL as a service if you expect to use it frequently. However, if you do not want MySQL running all the time, uncheck ‘install as service’. You can initially install as service and then disable that later using: control panel>admin tools>services > stop
Set a root password and check ‘Root may only connect from localhost’.
Finally, press execute to make the changes.
And you’re done!
Interfacing with MySQL
We will illustrate several ways to interface with a MySQL database:
1. Java Server Pages and Tomcat server
2. Dos command prompt
3. MySQL GUI tool.
To access the database from java, we first need to install the appropriate Java driver.
Installing MySQL Java Driver
After the MySQL installation is finished, you need to get the appropriate JDBC driver to enable a Java or JSP program to communicate with a MySQL database. The official JDBC driver for MySQL is MySQL Connector/J. You should already have Tomcat up and running before you do the next step. Go to http://dev.mysql.com/downloads/connector/j/3.0.html and click on the zip file to download it. Once downloaded, unzip it and locate the jar file:
mysql-connector-java-3.0.15-ga-bin.jar.
Copy the jar file to:
C:Program Filesjavajre_versionlibext
and paste it in that directory. A jar file is a type of compressed Java library that is directly useable by a Java compiler. So that the compiler knows where to locate the jar file, you should add a new entry to the CLASSPATH system environment variable (under control panel>system>advanced) identifying the location of the jar file. After doing this, my own CLASSPATH became:
c:j2sdk1.4.0frelibrt.jar;.;c:program filesapache tomcat
4.0commonlibservlet.jar;”C:Program
FilesJavaj2re1.4.0libextQTJava.zip”;C:myjspapp;C:jdombuildjdom.jar;C:Program Filesjavajre_versionlibextmysql-connector-java-3.0.15-ga-bin.jar
The blue text represents the added entry in the CLASSPATH because that was where I happened to store my copy of the needed jar file; the earlier entries were already in my CLASSPATH variable but yours will likely be different. Be sure to include the semi-colon separator before adding the new entry. If everything has been done properly, you should now be able to use JSP and MySQL together via a server like Tomcat.
Testing Access to MySQL from Tomcat
To test to see if everything is working, try the following JSP program which was stored on my system at: c:/myjspapp/chapter11/testMySQL01.jsp. This program accesses a sample database called “mysql” that comes with the installation. It also uses the user name “root” and the password (in my case “it202”) that I used when I installed MySQL on my system.
<%@ page import=”java.sql.*” %>
<%
Connection ocon = null;
ResultSet rs = null;
Class.forName(“com.mysql.jdbc.Driver”);
ocon = DriverManager.getConnection(“jdbc:mysql://127.0.0.1/mysql”, “root”, “it202”);
Statement stmtt = ocon.createStatement();
ResultSet rc = stmtt.executeQuery(“Select count(*) from user”);
rc.next();
out.println(“Total Records : ” + rc.getInt(1) + “<br>”);
if (rs !=null) rs.close();
if (ocon !=null) ocon.close();
if (stmtt !=null) stmtt.close();
%>
Run the JSP using Tomcat with the url:
http://localhost:8080/myapp/chapter11/testMySQL01.jsp
This requires both Apache Tomcat to be started as well as the MySQL database server (!) The latter can be started via the Services feature under the Admin Tool: Control panel > Admin > Services> MySQL start.
Refer to such as http://lococo.sch.ac.kr/jclass/notes/jdbc/basics/concept.html for the database url syntax in getConnection for MySQL since it is different than that for the ODBC style data source name. In particular see the remarks on the odbc subprotocol. The MySQL environment does not require the data source name convention we have used in other JSP examples which used the odbc subprotocol.
The JSP example uses the embedded SQL query:
Select count (*) from user
to return the number of rows in the user table (of the mysql database provided with the installation). If everything has been set up properly you should see something like:
Total Records: 2
Another sample database called test also comes with the installation. We access test using the command line interface that is commonly used to interface with MySQL. You could modify the JSP program to access the test database instead.
Interfacing with MySQL from Dos Prompt
To connect to MySQL server:
Just as in the JSP example, you must first have already started the MySQL server (via control panel > admin > service s> MySQL start), although now you will not need the Tomcat server. To manually connect to MySQL open up the command prompt: click on start, run, type cmd, and press enter to open the command prompt. You can then navigate in the command prompt to the MySQL directory where the needed program is stored via the dos change directory command cd:
Then execute:
mysql –uUSERNAME –pPASSWORD –hHOST
which in my case is:
mysql –uroot –hlocalhost –cos202
(Don’t paste these commands from Word ! Enter directly in Dos prompt.) The arguments after the mysql command (which is just an executable program) are called options. You can write them in several ways, for example as: -hlocalhost (as shown), or as –h localhost, or even as –host=localhost (with two dashes and an equal sign this is called the long form), but the password option pPassword must have no space between the p and the password. You can also also use the form:
mysql –uroot –hlocalhost –p
with the password omitted, in which case the system prompts you for the password and echoes what you type as asterisks, for security. The username and password are those that you initially specified when you initially installed MySQL. You can change the password using a somewhat complex method described later.
Once this is executed, you will be at the mysql> prompt. This prompt is the basis for your direct interaction with MySQL.
SQL commands in mysql
The following section illustrates the use of SQL commands in the MySQL context. All the commands are understood by the MySQL system since it is by design an SQL database environment. Each command ends with a semi-colon.
The first command show databases lists current databases. In this case, these are the databases that came with the installation package:
mysql> show databases;
+———-+
| Database |
+———-+
| mysql |
| test |
+———-+
2 rows in set (0.00 sec)
The dos output is set up using vertical and horizontal dashes ( |’s, -‘s and +’s) to look approximately like a rectangular grid for a table, with the title at the top row and the contents in the lower row.
The database called mysql holds various MySQL settings and users.
To examine this particular database, use the command:
use mysql;
The reply should be the message Database Changed. Once you are manually connected to this specific database, you can view and manipulate its data.
To see what tables mysql contains, use the command: show tables. This will display a list of its tables like the following (or an extended list):
mysql> show tables;
+—————–+
| Tables_in_mysql |
+—————–+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+—————–+
6 rows in set (0.00 sec)
To display the attributes of a particular table and their properties, use the desc tablename command. For example, desc user for the user table in the mysql database results in a list like (though in my newer installation version its actually longer, with 31 rows of attributes):
mysql> desc user;
+—————–+—————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————–+—————–+——+—–+———+——-+
| Host | char(60) binary | | PRI | | |
| User | char(16) binary | | PRI | | |
| Password | char(16) binary | | | | |
| Select_priv | enum(‘N’,’Y’) | | | N | |
| Insert_priv | enum(‘N’,’Y’) | | | N | |
| Update_priv | enum(‘N’,’Y’) | | | N | |
| Delete_priv | enum(‘N’,’Y’) | | | N | |
| Create_priv | enum(‘N’,’Y’) | | | N | |
| Drop_priv | enum(‘N’,’Y’) | | | N | |
| Reload_priv | enum(‘N’,’Y’) | | | N | |
| Shutdown_priv | enum(‘N’,’Y’) | | | N | |
| Process_priv | enum(‘N’,’Y’) | | | N | |
| File_priv | enum(‘N’,’Y’) | | | N | |
| Grant_priv | enum(‘N’,’Y’) | | | N | |
| References_priv | enum(‘N’,’Y’) | | | N | |
| Index_priv | enum(‘N’,’Y’) | | | N | |
| Alter_priv | enum(‘N’,’Y’) | | | N | |
+—————–+—————–+——+—–+———+——-+
17 rows in set (0.36 sec)
Notice that in this case the attributes host and user are keys, so that their combi ation is expected to uniquely identify a record (row). The desc output is useful, for example, when entering data into a table because it shows the order of the columns or attributes which is required for inserting data correctly using the insert command.
To see the actual data contents of a table use the SQL command select * from tablename. Try this for the user table in the mysql database.
Note on DOS-Prompt Display
The table is displayed in the dos window. If the dos window buffer properties and/or window size properties are not large enough for the display, it gets wrapped around in the window. This is readable but not terribly user-friendly. To fix the display, you may want to alter the properties of the dos prompt to accommodate the size of the displayed contents. (Right-click on the title bar of the prompt, then left-click on properties and select layout tab. Then change the buffer size settings.) For example, in the present instance, the user table is quite wide, so I altered the size of the buffer to 500 (and the window to 200). Part of the display is as shown below:
mysql> select * from user;
+———–+——+———————————————————————+————+-
| Host | User | Password | Select_priv ————-+——+———————————————————————+————+-
| localhost | root | *D6AD0960FA1AB590BA249A2F6662EC3918B1D71B | Y |
+———–+——+———————————————————————+————+-
1 row in set (0.00 sec)
The page is far too narrow to show the entire row, so only the first four properties are shown. Of course, you can also leave the dos-prompt properties alone and accept the display as shown there in the default window size setting. It will display the first row of table attribute names over several successive lines, and then show the next row of corresponding values over several additional lines of output. The graphical interface tool for interacting with MySQL that we will describe shortly displays such tables more clearly.
To add an entry or row to a table use the SQL insert command which has the format:
insert into tableName values (“value1”, “value3”, etc );
For a password attribute, enter the value as: Password (“value”) which causes the value to be encrypted by the Password function before insertion into the database. For example, to add an entry to the user table with user bert on Host localhost, and the password hello, the (somewhat cumbersome) command is:
insert into user values (“localhost”, “bert”, Password(“hello”), “N”,”N”, “N”, “N”, “N”, “N”, “N”, “N”, “N”, “N”, “N”, “N”, “N”, “N”, “N”,”N”, “N”, “N”, “N”, “N”, “N”, “N”, “N”, “N”, “N”, “N”, “N”, “N”);
At least in my Windows XP environment, the data values in the dos prompt had to be entered with double quotes – though in a JSP program context they would have been enclosed in single quotes as is usual in SQL syntax. If you thereafter display the updated contents of the table, the first three values for the newly added entry will be:
localhost | newGuy | *881C3310FC1CDF54613539F0F01BAAB645099D3F
Observe that the host and user were entered without modification. The password is scrambled or encrypted. The values for the bert ‘s privileges are all N, denying any privileges.
The replace command is used to update a row in a database. The keys user and host uniquely identify the record whose values are to be changed. SQL query is:
replace into tableName values (“value1”, Password(“value2 “), etc );
In the current example, the replace SQL for the table user is:
replace into user values(“localhost”, “bert”, Password(“hello”), “Y”,”Y”, “Y”, “Y”, “Y”, “Y”, “Y”, “Y”, “Y”, “Y”, “Y”, “Y”, “Y”, “Y”, “Y”,”Y”, “Y”, “Y”, “Y”, “Y”, “Y”, “Y”, “Y”, “Y”, “Y”, “Y”, “Y”, “Y”);
which changes all of the N’s to Y’s granting bert full privileges to the MySQL system. Importantly, for these changes to take effect, you must also execute the command:
flush privileges
The command to delete a row from a table requires the name of the table and a where condition that specifies a the row(s) to be deleted:
delete from tableName where Where-condition
Warning: If you omit the Where-condition then all the rows in the table will be deleted. In the current example, the values of the attributes user and host are keys that together uniquely identify a row thereby selecting one row for deletion such as in:
delete from user where user = ‘newGuy’ and host = ‘localhost’
The examples we considered so far used pre-existing databases that came with the installation package. To create new MySQL databases, use the SQL command:
create database databaseName ;
For example, do: create database Person. You can verify that the database was created using show databases command.
mysql> show databases;
+————+
| Database |
+————+
| menagerie |
| mysql |
| person |
| test |
+————+
4 rows in set (0.00 sec)
To delete a database use the SQL command: drop database databaseName. Be forewarned that this will remove a populated database without asking the user for any confirmation.
The SQL syntax to create a table is illustrated by the following example. Before using the command, make sure to identify the database being used with the command: use Person. The command creates a table with attributes: id, username, password, name, and email.
mysql> create table Person
( id int(5) not null auto_increment,
username varchar(20) binary not null,
password varchar(20) binary not null,
name varchar(20) not null,
email varchar(30) not null,
primary key(id)
);
The general form of the syntax is:
create table tableName ( col1Name datatype otherSettings, etc)
The type for each attribute is prescribed, along with a possible length in input characters. All the values are required (none of them can be ‘null’). The id attribute is automatically incremented each time a person is entered. The type varchar is alphanumeric. The binary representation has the effect of making it case-sensitive. Note that the name and email attributes are not binary and so are not case-sensitive. The id is established as the primary key for the table, uniquely identifying each row of the table.
mysql> desc Person;
+———-+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———-+————-+——+—–+———+—————-+
| id | int(5) | | PRI | NULL | auto_increment |
| username | varchar(20) | | | | |
| password | varchar(20) | | | | |
| name | varchar(20) | | | | |
| email | varchar(30) | | | | |
+———-+————-+——+—–+———+—————-+
5 rows in set (0.01 sec)
You can use the insert command to insert data in the table. For example:
mysql> insert into Person values
(null, ‘murph’, ‘magic7’, ‘Tom Murphy’, ‘tomM@njit.edu’);
insert into Person values
(null, ‘lou’, ‘havanian’, ‘Louie Mack, ‘lou@cincy.com’);
Since the id attribute is auto-incrementing, use a null for the data entry. (A null value is indicated with the reserved word null; this is not the same as a blank entry such as ” ” – which is not null but a blank.) The id attribute then starts at 1 and increments by 1 with each insertion of a new row. You can verify the content of the newly populated table with the SQL command: mysql> select * from Person.
mysql> select * from Person;
+—-+———-+———-+————+—————+
| id | username | password | name | email |
+—-+———-+———-+————+—————+
| 1 | murph | magic7 | Tom Murphy | tomM@njit.edu |
| 2 | lou | havanian | Louie Mack | lou@cincy.com |
+—-+———-+———-+————+—————+
2 rows in set (0.00 sec)
To remove a table use the command: drop table tableName.
How to Reset the root password
This process is only done after the service is started (contrary to what is said in the MySQLQueryBrowser documentation) and uses double quotes not single quotes (also contrary to what is said in the MySQLQueryBrowser documentation).
1. First, connect to the MySQL server with the original password you established:
>mysql -uroot -hlocalhost -pOriginalPassword
2. Then execute the following command with the new password as shown:
mysql> update mysql.user set password=PASSWORD("it202") where user = "root";
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
3. The new password does not become effective until the following command is executed:
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
Note: The example given earlier for insert and replace command applied to the user table of the mysql database shows how to change both the user name and the password.
GUI Tool for MySQL
MySQL is frequently interfaces with from the command prompt rather than a GUI interface as is available for MS Access, but there are also recent graphical interface tools for MySQL. We will use the MySQLQueryBrowser which you can download from http://dev.mysql.com/downloads (under the Graphical Clients list). Execute MySQLQueryBrowser.exe, or pin to the start menu, or make a shortcut. When its window opens, fill in the password field: leave the root user name alone and leave the first field alone (clear). Refer to the Help > online docs > MySQL Tutorial (item 3) and section 8.3.3 for SQL commands.
The following is a very simple overview of the tool capabilities. You can explore the tool to determine other features. The elements of the interface include:
1. Schemata window.
2. Syntax window.
3. SQL command window.
4. ResultSet tabbed windows.
5. Function tab window.
The right side of the window contains two sub-windows: a Schemata window that lists the available databases and a window with icons for the syntax of SQL commands grouped by category. The mysql database is the default database that appears in the Schemata sub-window; open a different database by lift-clicking on its icon in the window. To get the syntax for an SQL command, left-click one of the icons for groups of commands in the Syntax window. This opens a corresponding inline help document section in the main sub-window which can be closed (x) once read. This window (lower right of screen) also contains a tab for built-in Functions.
You can issue SQL commands in the sub-window at the top of the screen. For example, if you type “use person” and execute it (by clicking the lightning icon to the right of the top sub-window), then enter and execute “desc person”, the list of the tables and their properties in the person database appears in the main window. You can move back and forth in the main window and refresh its contents with the buttons on the top left. The data retrieved by a select command displays a table whose content can be edited (see the edit button at bottom of screen).
mysql> SELECT VERSION();
mysql> SELECT USER();
mysql> SELECT CURRENT_USER();
Sample Java Servelets and MySQL
See also: http://www.apl.jhu.edu/~hall/java/Servlet-Tutorial/ for JSP and servlets tutorial. This guestbook example is from O'Reilly.com modified by Ubaldi.
First we need to create the database.
Connect to MySQL and enter the following:
create database GUESTBOOK;
use GUESTBOOK;
create table comments (comment_id int primary key not null auto_increment, name varchar(40) not null, date varchar(30), comment varchar(255));
OK – now we’re ready to move onto the JSP:
<html><head><title>guestbook.jsp</title></head>
<body>
<%@ page import="javax.servlet.*" %>
<%@ page import="javax.servlet.http.*" %>
<%@ page import="java.io.IOException" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.text.DateFormat" %>
<%@ page import="java.util.Properties" %>
<%@ page import="java.util.Random" %>
//various includes that will be needed throughout
<%!
// GuestBook2Servlet, based on example from http://www.oreilly.com/catalog/msql/servlet.txt
// Changed it quite a lot.
//These are the connection strings to connect to your database – //change them as needed.
static final String driverName = "com.mysql.jdbc.Driver";
static final String jdbcURL1 = dbc:mysql://127.0.0.1/";
static final String databaseName = "GUESTBOOK";
static final String userName = "USERNAME";
static String userPassword = "PASSWORD";
static final String URI = "Guestbook.jsp";
private Driver driver = null;
private Random random = new Random();
//The following makes sure the driver is installed and working
protected void openDriver() throws Exception {
if (driver!= null) return;
driver = (Driver)Class.forName(driverName).newInstance();
if (driver== null) throw new SQLException("newInstance of "+ driverName+ "returned NULL!");
}
//The following connects to MySQL database & throws exception if fails
protected Connection openConnection() throws Exception {
openDriver();
Connection conn;
conn= DriverManager.getConnection(jdbcURL1+ databaseName, userName, userPassword);
return conn;
}
//Sets the date.
protected String curDateString() {
java.util.Date date= new java.util.Date();
return DateFormat.getDateTimeInstance().format(date);
}
//Gets the body of the post from the HTML form
public void doGetBody(JspWriter out, HttpServletRequest
req, HttpServletResponse res) throws ServletException,
IOException {
int id = -1;
printComments(out);
}
//Posts the body to the MySQL database
public void doPostBody(JspWriter out, HttpServletRequest req,
HttpServletResponse res) throws ServletException, IOException {
String date = curDateString();
String name, comment;
Connection conn = null;
Exception err = null;
int id = -1;
String[] tmp;
tmp = req.getParameterValues("name");
if( tmp == null || tmp.length != 1 ) { name = null; }
else { name = tmp[0]; }
tmp = req.getParameterValues("comments");
if( tmp == null || tmp.length != 1 ) { comment = null; }
else { comment = tmp[0];}
res.setContentType("text/html");
// validate values
if( name.length() < 1 ) {
out.println("You must specify a valid name!");
return;
}
if( comment.length() < 1 ) {
out.println("You left no comments!");
return;
}
try {
ResultSet result;
Statement stmt;
conn= openConnection();
stmt = conn.createStatement();
comment = fixComment(comment);
stmt.executeUpdate("INSERT into comments (comment_id, name,
comment, date) "+ "VALUES (0, '" + name + "', '" +
comment + "', '" + date + "')");
//[Not for Msql or MySql]conn.commit();
stmt.close();
}
catch( Exception e ) {
e.printStackTrace();
err = e;
}
finally {
if( conn != null ) {
try { conn.close(); }
catch( Exception e ) { }
}
}
if( err != null ) {
out.println("An error occurred on save: " +
err.getMessage());
}
else { printComments(out); }
//println("...doPostBody done");
}
public String getServletInfo() {
return "Guestbook Script";
}
private void printComments(JspWriter out)
throws IOException {
Connection conn = null;
//Gets comments from MySQSL and prints to the page.
try {
ResultSet results;
Statement stmt;
int rows, count;
conn = openConnection();
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
results = stmt.executeQuery("SELECT name, date, " +
"comment, comment_id " +
"FROM comments order by comment_id");
out.println("<DL>");
results.last();
count = 0;
// print up to 5 rows going backwards
while (count < 5) {
String name, cmt, date;
name = results.getString(1);
if( results.wasNull() ) name = "Unknown User";
date = results.getString(2);
if( results.wasNull() ) date = curDateString();
cmt = results.getString(3);
if( results.wasNull() ) cmt= "No comment";
out.println("<DT><B>" + name + "</B> on " + date);
cmt = noHTML(cmt);
out.println("<DD> <PRE>" + cmt + "</PRE>");
if (!results.previous()) break; // no more.
count++;
}
out.println("</DL>");
}
catch( Exception e ) {
out.println("Error: " + e.getMessage());
e.printStackTrace();
}
finally {
if( conn != null ) {
try { conn.close(); }
catch( SQLException e ) { }
}
}
}
private String noHTML(String cmt) {
int ilt= cmt.indexOf('<');
int igt= cmt.indexOf('>');
if (ilt< 0 && igt< 0) return cmt;
String tmp = "";
for (int i=0; i<cmt.length(); i++) {
char c = cmt.charAt(i);
if( c == '<' ) tmp = tmp + "<";
else if( c == '>' ) tmp = tmp + ">";
else tmp = tmp + c;
}
return tmp;
}
private String fixComment(String comment) {
if( comment.indexOf(''') != -1 ) {
String tmp = "";
for(int i=0; i<comment.length(); i++) {
char c = comment.charAt(i);
if( c == ''' ) tmp = tmp + "'";
else tmp = tmp + c;
}
comment = tmp;
}
return comment;
}
%>
//The HTML page where comments are submitted.
<CENTER>
<FORM ACTION="/<%= URI %>" METHOD="POST">
<TABLE>
<TR ALIGN="LEFT">
<TD>Name:</TD>
<TD><INPUT SIZE=30></TD>
<TD><INPUT VALUE="Save"></TD>
</TR>
<TR ALIGN="LEFT">
</TR>
<TR ALIGN="LEFT">
<TD>Comments:</TD>
</TR>
<TR ALIGN="CENTER">
<TD COLSPAN=3>
<TEXTAREA COLS=40 ROWS=7>
</TEXTAREA></TD>
</TR>
</TABLE>
</FORM>
</CENTER>
<%= E.toString() %>
<%
}
//Starts the whole process.
String method= request.getMethod();
if (method.equals("GET")) doGetBody(out, request, response);
else doPostBody(out, request, response);
%>
