Jenny the database code generator - Tutorial

Download and install mySQL

Assuming that you are using some flavor of Windows, here is how it might work:

Download mysql-xxxxxxxx-win.zip from mysql.com. Create a directory called C:\mysql and put the zip file in that. Change your current directory to C:\mysql. Unzip the zip file into the current directory. This will generate a few files including SETUP.EXE. Shut down all other programs and then run SETUP.EXE. Go along with all of the default settings.

MySQL should now be installed. This is a command line server program, although there are GUI interfaces available. To activate it, run the program "\mysql\bin\mysqld-nt --standalone" (the "d" stands for "daemon" meaning a program that runs in the background) (the "nt" part stands for Windows NT, although the docs say this should work on any windows. If it doesn't, there are some other flavors of mysqld available to try). When you run it on Windows, it will not spawn a new window or anything, so it will just sit there and run. You need to open a new console window to interact with it.

Open a new console (DOS window) and run \mysql\bin\mysql. You should see a prompt like this: "mysql>".

Create a table with SQL

          +----------+
          | Database |
          +----------+
          | mysql    |
          | test     |
          +----------+
        

"SHOW" is not a SQL command. It's something that mySQL provides for you on top of SQL.

Let's try a SQL command. Type "CREATE DATABASE SOUP;" and press enter. If you now type "SHOW DATABASES;" and press enter you should see:

          +----------+
          | Database |
          +----------+
          | mysql    |
          | soup     |
          | test     |
          +----------+
        

Now give the command "USE SOUP;" - this way mySQL will know that future commands are to be applied to the soup database.

What is a relational database?

Suppose that we have a table

VideoID	Title	Star	Type	VHS	DVD	Description
1	Alien	Sirgourney Weaver	action	yes	yes	On the way home to Earth, a small team of miners discovers a new life form.
2	The Gods Must Be Crazy	A coke bottle	comedy	yes	no	A bushman is introduced to civilization by a coke bottle.
3	Aladdin	Punk kid	kids	yes	no	A young criminal dances his way into the princess' heart with the help of a genie.

Different brands of relational databases might store this data in all sorts of weird ways, but it doesn't matter to us. We are going to work with the relational database through a common interface: SQL. Nearly all relational databases speak SQL. So if we write programs that interface with the database only via SQL, it should all work the same no matter what database we use on the back end.

Let's create this table in our "SOUP" database. Type "CREATE TABLE Videos ( VideoID INTEGER PRIMARY KEY, Title VARCHAR(80) NOT NULL, Star VARCHAR(40) NULL, Type VARCHAR(15) NULL, VHS CHAR(1) NULL, DVD CHAR(1) NULL, Description VARCHAR(200) NULL);" (or you can try your hand at cut and paste). This creates a table called "VIDEOS" with seven columns.

Now let's add one row to our new table. Type "INSERT INTO Videos ( VideoID , Title , Star , Type , VHS , DVD , Description ) VALUES ( '1' , 'The Gods Must Be Crazy' , 'a coke bottle' , 'comedy' , 'Y' , 'N' , 'A bushman is introduced to civilization by a coke bottle.' );". This is getting a little trickier. You have to make sure that your column names line up with your data.

If you now type "SELECT * from Videos;" you should see:

        +----------------------------------+---------------+--------+------+------+----------------------------------------------------------+
        | VideoID | Title                  | Star          | Type   | VHS  | DVD  | Description                                              |
        +----------------------------------+---------------+--------+------+------+----------------------------------------------------------+
        |    1    | The Gods Must Be Crazy | a coke bottle | comedy | Y    | N    | A bushman is introduced to civilization by a coke bottle.|
        +----------------------------------+---------------+--------+------+------+----------------------------------------------------------+

        

Download and install the mysql JDBC driver

Cut and paste this program into a file called Test.java:

        import java.sql.* ;

        public class Test
        {

            public static void main( String[] args )
            {
                try
                {
                    Class.forName( "com.mysql.jdbc.Driver" ).newInstance();
                    try
                    {
                        Connection con = DriverManager.getConnection( "jdbc:mysql://localhost/soup" );

                        try
                        {
                            Statement statement = con.createStatement();

                            ResultSet rs = statement.executeQuery("SELECT TITLE FROM VIDEOS");

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

                            rs.close();
                            statement.close();
                        }
                        catch ( SQLException e )
                        {
                            System.out.println( "JDBC error: " + e );
                        }
                        finally
                        {
                            con.close();
                        }

                    }
                    catch( SQLException e )
                    {
                        System.out.println( "could not get JDBC connection: " + e );
                    }
                }
                catch( Exception e )
                {
                    System.out.println( "could not load JDBC driver: " + e );
                }
            }

        }

        

JDBC: Java Database Connectivity

And using Jenny makes it even easier!!