Pace CS 396S - Study Notes (6 pages)

Previewing pages 1, 2 of 6 page document View the full content.
View Full Document

Study Notes



Previewing pages 1, 2 of actual document.

View the full content.
View Full Document
Unformatted text preview:

MySQL Database and Java Programs Downloading and Installing MySQL MySQL is a free database that can be downloaded from the web site http www mysql com The specific download URL is http dev mysql com downloads index html MySQL can be used to create a database and query it using Structured Query Language SQL After downloading MySQL onto your computer you can install it using the installer that comes with it There is an excellent tutorial on the MySQL web site The URL for it is http dev mysql com doc mysql en Tutorial html This gives extensive information on how to use MySQL To install MySQL after downloading unzip it into a folder called mysql and then in Windows Explorer click on setup exe If you downloaded the version that comes with an installer it will create a directory structure with a number of subfolders The important ones are bin and data The first contains the applications that run the database service and the second is where the databases you create will be stored While you are downloading and installing programs you might also want to get the MySQL Connector ODBC that allows you to connect to a MySQL database server from a Java program This can be found at http www mysql com products connector odbc This file MyODBCstandard 3 51 07 win exe is an application so all you have to do is click on it in Windows Explorer to install it When installed it registers the driver with the Windows operating system Starting and Stopping the MySQL Database Server There are two ways to start the MySQL server The simplest method is to run the file mysqld exe found in the bin folder This will create a Command Prompt window This window cannot be used or closed In order to close it you have to shut down the MySQL server This is done with the command mysqladmin u root shutdown This must be done in a second command prompt window When you type this command the first window will disappear but the second will not You can close it in the usual way with the X in the upper right hand corner Both of these commands can be stored in batch files These can be called MySQLstartup bat and MySQLshutdown bat The files can consist of just the single line commands You can also put short cuts to them on your desktop should you wish The second way to start the MySQL server is to install it as a service in Windows You can do this if the operating system on your computer is Windows NT or later Installing MySQL as a Windows service is done by typing mysqld install You should be able to do this in the c directory in a Command Prompt window However to be sure do so from the MySql bin folder This registers MySQL as a Windows service that will be started when Windows starts and stopped when Windows is closed down If you do this you can use MySQL any time that your computer is turned on You can view all the Windows services by going into Control Panel Administrative Tools Services You can view all the services registered with Windows there and can see if they have started or not You can stop the server even if it is registered as a service with the same command as before mysqladmin u root shutdown You can also remove the server from the registry with the command mysqld remove Creating a Database First start MySQL and go to a second Command Prompt window Change directories to MySql bin and then type mysql You should see the following screen In this window you can create a database add tables and data to it and make queries There are two important things to remember The command quit is used to exit the MySQL monitor and all other commands must end with semicolons MySQL comes with two databases in the data folder mysql and test Use the show databases command to see information about them You can create a new database with the command create database produce where produce is the name for your database If you now type show databases again you will see an additional database in the list Any time now that you wish to work with this database all you have to do is type use produce Then you can add data make queries etc The new database is empty and has no tables You can create a table for the database with the SQL command Create Table table name column name1 datatype1 column name2 datatype2 For our example this can be create table Fruit id varchar 10 name varchar 20 variety varchar 20 quantity int 5 You can use this window to insert data into the database show contents etc using standard SQL commands These operations can also be done using a Java program Before doing that however we will add an item to the table with the following command insert into fruit values 123 45 Apples Red Delicious 20 We can see what is in the entire table with the SQL command select select from fruit This returns a table containing everything in the database table fruit Registering the MySQL Database with Windows In order to connect to a database with a Java program the database must be registered with the Windows operating system When doing so you have to specify the appropriate driver For MySQL this is the MySQL Connector ODBC You must install it before it can be used To do the registration go into Control Panel Administrative Tools Data Sources ODBC From there choose to Add a new database On the next screen choose the MySQL ODBC 3 51 Driver You will have to scroll down to find it When you type Finish you should see the next screen Fill in the Data Source Name and the Database Name If you want you can add a username and password but this is optional Notice that the box does not ask for the location of the database MySQL assumes that the database will be located in the data folder If you want to put is somewhere else you will have to specify a path as well as a name You can use any name you wish for the Data Source Name But you must use the name you actually gave the database for the Database Name The Data Source Name is the one that will be used in your program not the Database Name Using a Java Program to Connect to a Database Java programs use a jdbc odbc bridge to connect to a database This can be done with the commands Class forName sun jdbc odbc JdbcOdbcDriver Connection con DriverManager getConnection jdbc odbc deli Where deli is the Data Source Name given before This creates a connection to the database that can be used throughout the program The following program uses the connection to obtain a ResultSet that is generated by a SQL query The query Selects everything from the database and returns the data The data is then displayed on the


View Full Document

Access the best Study Guides, Lecture Notes and Practice Exams

Loading Unlocking...
Login

Join to view Study Notes and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Study Notes and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?