Archive for the ‘MySQL 5.6’ Category
Create MySQL Index
Indexes are separate data structures that provide alternate pathways to finding data. They can and do generally speed up the processing of queries and other DML commands, like the INSERT
, UPDATE
, REPLACE INTO
, and DELETE
statements. Indexes are also called fast access paths.
In the scope of the InnoDB Database Engine, the MySQL database maintains the integrity of indexes after you create them. The upside of indexes is that they can improve SQL statement performance. The downside is that they impose overhead on every INSERT
, UPDATE
, REPLACE INTO
, and DELETE
statement, because the database maintains them by inserting, updating, or deleting items for each related change in the tables that the indexes support.
Indexes have two key properties—usability and visibility. Indexes are both usable and visible by default. That means they are visible to the MySQL cost-based optimizer and usable when statements run against the tables they support.
You have the ability to make any index invisible, in which case queries and DML statements won’t use the index because they won’t see it. However, the cost-based optimizer still sees the index and maintains it with any DML statement change. That means making an index invisible isn’t quite like making the index unusable or like dropping it temporarily. An invisible index becomes overhead and thus is typically a short-term solution to run a resource-intensive statement that behaves better without the index while avoiding the cost of rebuilding it after the statement runs.
It is also possible to make an index unusable, in which case it stops collecting information and becomes obsolete and the database drops its index segment. You rebuild the index when you change it back to a usable index.
Indexes work on the principal of a key. A key is typically a set of columns or expressions on which you can build an index, but it’s possible that a key can be a single column. An index based on a set of columns is a composite, or concatenated, index.
Indexes can be unique or non-unique. You create a unique index anytime you constrain a column by assigning a primary key or unique constraint, but they’re indirect indexes. You create a direct unique index on a single column with the following syntax against two non-unique columns:
1 2 | CREATE INDEX common_lookup_u1 ON common_lookup (common_lookup_table) USING BTREE; |
You could convert this to a non-unique index on two columns by using this syntax:
1 2 | CREATE INDEX common_lookup_u1 ON common_lookup (common_lookup_table, common_lookup_column) USING BTREE; |
Making the index unique is straightforward;, you only need to add a UNIQUE
key wordk to the CREATE INDEX
statement, like
1 2 3 4 | CREATE UNIQUE INDEX common_lookup_u1 ON common_lookup ( common_lookup_table , common_lookup_column , common_lookup_type) USING BTREE; |
Most indexes use a B-tree (balanced tree). A B-tree is composed of three types of blocks—a root branch block for searching next-level blocks, branch blocks for searching other branch blocks, or and leaf blocks that store pointers to row values. B-trees are balanced because all leaf-blocks are at the same level, which means the length of search is the same to any element in the tree. All branch blocks store the minimum key prefix required to make branching decisions through the B-tree.
Bash Arrays & MySQL
Student questions are always interesting! They get me to think and to write. The question this time is: “How do I write a Bash Shell script to process multiple MySQL script files?” This post builds the following model (courtesy of MySQL Workbench) by using a bash shell script and MySQL script files, but there’s a disclaimer on this post. It shows both insecure and secure approaches and you should avoid the insecure ones.
It seems a quick refresher on how to use arrays in bash
shell may be helpful. While it’s essential in a Linux environment, it’s seems not everyone masters the bash
shell.
Especially, since I checked my Learning the Bash Shell (2nd Edition) and found a typo on how you handle arrays in the bash
shell, and it’s a mistake that could hang newbies up (on page 161). Perhaps I should update my copy because I bought it in 1998. 😉 It was good then, and the new edition is probably better. The error is probably corrected in the current Learning the Bash Shell, but if not, the following examples show you how to use arrays in loops.
Naturally, these do presume some knowledge of working with bash
shell, like the first line always is the same in any bash
shell script. That you open an if-statement with an if
and close it with a fi
, and that you else-if is elif
; and that a semicolon between a for-statement and the do
statement is required when they’re on the same line because they’re two statements.
If you’re new to bash
shell arrays, click on the link below to expand a brief tutorial. It takes you through three progressive examples of working with bash
arrays.
Working with bash
Arrays ↓
A basic example of working with an array in bash
shell is the following list1.sh
script:
1 2 3 4 5 6 7 8 9 10 11 12 13 | #!/usr/bin/bash # Print script name. echo $0 #!/usr/bin/bash # Define an array. declare -a cmd=("one" "two" "three") # Call the array elements. for i in ${cmd[*]}; do echo ${i} done |
Line 8 declares the cmd
array by assigning three strings. Line 12 returns the elements of the array to the ${i}
variable, which lets you manage them one at a time. You use the chmod
command to make the list1.sh
executable, like this:
chmod 755 list1.sh |
Then, you can run it like this from the present working directory (pwd
):
./list1.sh |
It should print:
one two three |
The list2.sh
example changes the cmd
array declaration from list1.sh
. It declares the cmd
array as an empty array, and then it assigns elements by index numbers (using a zero-based index), as shown below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | #!/usr/bin/bash # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="one" cmd[1]="two" cmd[2]="three" # Call the array elements. for i in ${cmd[*]}; do echo ${i} done |
Lines 7 through 9 assign values to the elements of the cmd
array. You would chmod
the file, and run the file as qualified above for the list1.sh
script.
The last pre-implementation example requires that you create three demonstration scripts, the one.sh
, two.sh
, and three.sh
scripts. You should put them in the same directory as the list3.sh
script.
The demonstration scripts should all have the same code, like this:
1 2 3 4 | #!/usr/bin/bash # Print script name. echo $0 |
Line 4 returns command line parameter $0
or ${0}
, which is always the command line program’s file name. The file name may be provided as a relative or absolute file name, and if that’s new to you please check out The Linux Command Line: A Complete Introduction (also downloadable as a PDF for free).
The list3.sh
script should contain the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | #!/usr/bin/bash <code> # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="one.sh" cmd[1]="two.sh" cmd[2]="three.sh" # Call the array elements. for i in ${cmd[*]}; do `pwd`/${i} done |
When you run the list3.sh
script from the /home/student/Code/bash directory with a local syntax, the script should return the fully qualified file names of the subshell programs. The output should look like this:
/home/student/Code/bash/one.sh /home/student/Code/bash/two.sh /home/student/Code/bash/three.sh |
The list3.sh
script provides the present working directory (pwd
) and the one.sh
, two.sh
, and three.sh
scripts return only their executable name. For example, if you ran one.sh
with the following syntax:
./one.sh |
It returns
./one.sh |
Only one more trick needs to be qualified before our main MySQL examples. That trick is how you pass parameters to a bash
shell script. For reference, this is the part that’s insecure because user command histories are available inside the Linux OS.
Here’s a hello_whom.sh
script to demonstrates the concept of parameter passing:
1 2 3 4 5 6 7 8 9 10 | #!/usr/bin/bash # This says hello to the argument while managing no argument. if [[ ${#} = 1 ]]; then echo 'The '${0}' program says: "Hello '${1}'!"' elif [[ ${#} > 1 ]]; then echo 'The '${0}' program wants to know if you have more than one name?' else echo 'The '${0}' program wants to know if you have a name?' fi |
If you need more on how parameters are passed and managed, you can check a prior blob post on Handling bash
Parameters, or check the bash help pages. The following leverages bash
arrays to run scripts and query the MySQL database from the command line.
You will need the three batch SQL files first, so here they are:
Setup SQL Files ↓
The actor.sql
file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- Use the sampledb database. USE sampledb; -- Disable foreign key checking. SET foreign_key_checks = 0; -- Drop an actor table. DROP TABLE IF EXISTS actor; -- Create an actor table. CREATE TABLE actor ( actor_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , actor_name VARCHAR(30) NOT NULL ); -- Insert two rows. INSERT INTO actor (actor_name) VALUES ('Chris Hemsworth'); INSERT INTO actor (actor_name) VALUES ('Chris Pine'); INSERT INTO actor (actor_name) VALUES ('Chris Pratt'); |
The film.sql
file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | -- Use the sampledb database. USE sampledb; -- Disable foreign key checking. SET foreign_key_checks = 0; -- Drop a film table. DROP TABLE IF EXISTS film; -- Create a film table. CREATE TABLE film ( film_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , film_name VARCHAR(30) NOT NULL ); -- Insert rows. INSERT INTO film (film_name) VALUES ('Thor'); INSERT INTO film (film_name) VALUES ('Thor: The Dark World'); INSERT INTO film (film_name) VALUES ('Star Trek'); INSERT INTO film (film_name) VALUES ('Star Trek into Darkness'); INSERT INTO film (film_name) VALUES ('Guardians of the Galaxy'); |
The movie.sql
file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | -- Use the sampledb database. USE sampledb; -- Disable foreign key checking. SET foreign_key_checks = 0; -- Drop an movie table. DROP TABLE IF EXISTS movie; -- Create an movie table. CREATE TABLE movie ( movie_id int unsigned PRIMARY KEY AUTO_INCREMENT , actor_id int unsigned NOT NULL , film_id int unsigned NOT NULL , CONSTRAINT actor_fk FOREIGN KEY (actor_id) REFERENCES actor (actor_id) , CONSTRAINT film_fk FOREIGN KEY (film_id) REFERENCES film(film_id)); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Hemsworth') ,(SELECT film_id FROM film WHERE film_name = 'Thor')); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Hemsworth') ,(SELECT film_id FROM film WHERE film_name = 'Thor: The Dark World')); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Pine') ,(SELECT film_id FROM film WHERE film_name = 'Star Trek')); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Pine') ,(SELECT film_id FROM film WHERE film_name = 'Star Trek into Darkness')); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Pratt') ,(SELECT film_id FROM film WHERE film_name = 'Guardians of the Galaxy')); |
The following list_mysql.sh
shell script expects to receive the username
, password
, database
and fully qualified path
in that specific order. The script names are entered manually because this should be a unit test script. Naturally, you can extend the script to manage those parameters but as mentioned I see this type of solution as a developer machine only script to simplify unit testing. Anything beyond that is risky!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | #!/usr/bin/bash # Assign user and password username="${1}" password="${2}" database="${3}" directory="${4}" # List the parameter values passed. echo "Username: " ${username} echo "Password: " ${password} echo "Database: " ${database} echo "Directory: " ${directory} echo "" # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="actor.sql" cmd[1]="film.sql" cmd[2]="movie.sql" # Call the array elements. for i in ${cmd[*]}; do mysql -s -u${username} -p${password} -D${database} < ${directory}/${i} > /dev/null 2>/dev/null done # Connect and pipe the query result minus errors and warnings to the while loop. mysql -u${username} -p${password} -D${database} <<<'show tables' 2>/dev/null | # Read through the piped result until it's empty but format the title. while IFS='\n' read list; do if [[ ${list} = "Tables_in_sampledb" ]]; then echo $list echo "----------------------------------------" else echo $list fi done echo "" # Connect and pipe the query result minus errors and warnings to the while loop. mysql -u${username} -p${password} -D${database} <<<'SELECT CONCAT(a.actor_name," in ",f.film_name) AS "Actors in Films" FROM actor a INNER JOIN movie m ON a.actor_id = m.actor_id INNER JOIN film f ON m.film_id = f.film_id' 2>/dev/null | # Read through the piped result until it's empty but format the title. while IFS='\n' read actor_name; do if [[ ${actor_name} = "Actors in Films" ]]; then echo $actor_name echo "----------------------------------------" else echo $actor_name fi done |
The IFS
(Internal Field Separator) works with whitespace by default. The IFS
on lines 33 and 47 sets the IFS
to a line return ('\n'
). That’s the trick to display the data, and you can read more about the IFS
in this question and answer post.
You can run this script with the following input parameters from the local directory where you deploy it. The a parameters are: (1) username
, (2) password
, (3) database
, and (4) a fully qualified path to the SQL setup files.
./list_mysql.sh student student sampledb "/home/student/Code/bash/mysql" |
With valid input values, the list_mysql.sh
bash
script generates the following output, which confirms inputs and verifies actions taken by the scripts with queries:
Username: student Password: student Database: sampledb Directory: /home/student/Code/bash/mysql Tables_in_sampledb ---------------------------------------- actor film movie Actors in Films ---------------------------------------- Chris Hemsworth in Thor Chris Hemsworth in Thor: The Dark World Chris Pine in Star Trek Chris Pine in Star Trek into Darkness Chris Pine in Guardians of the Galaxy |
If you forgot to provide the required inputs to the list_mysql.sh
bash
script, it alternatively returns the following output:
Username: Password: Database: Directory: ./list_mysql.sh: line 25: /actor.sql: No such file or directory ./list_mysql.sh: line 25: /film.sql: No such file or directory ./list_mysql.sh: line 25: /movie.sql: No such file or directory |
The secure way removes the password at a minimum! The refactored program will require you to manually enter the password for all elements of the array (three in this sample), and twice for the two queries. Here’s the refactored code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | #!/usr/bin/bash # Assign user and password username="${1}" database="${2}" directory="${3}" # List the parameter values passed. echo "Username: " ${username} echo "Database: " ${database} echo "Directory: " ${directory} echo "" # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="actor.sql" cmd[1]="film.sql" cmd[2]="movie.sql" # Call the array elements. for i in ${cmd[*]}; do mysql -s -u${username} -p -D${database} < ${directory}/${i} > /dev/null 2>/dev/null done # Connect and pipe the query result minus errors and warnings to the while loop. mysql -u${username} -p -D${database} <<<'show tables' 2>/dev/null | # Read through the piped result until it's empty. while IFS='\n' read list; do if [[ ${list} = "Tables_in_sampledb" ]]; then echo $list echo "----------------------------------------" else echo $list fi done echo "" # Connect and pipe the query result minus errors and warnings to the while loop. mysql -u${username} -p -D${database} <<<'SELECT CONCAT(a.actor_name," in ",f.film_name) AS "Actors in Films" FROM actor a INNER JOIN movie m ON a.actor_id = m.actor_id INNER JOIN film f ON m.film_id = f.film_id' 2>/dev/null | # Read through the piped result until it's empty. while IFS='\n' read actor_name; do if [[ ${actor_name} = "Actors in Films" ]]; then echo $actor_name echo "----------------------------------------" else echo $actor_name fi done |
Please let me know if you think there should be any more scaffolding for newbies in this post. As always, I hope this helps those looking for this type of solution.
Eclipse, Java, MySQL
While I previously blogged about installing Netbeans 8, some of my students would prefer to use the Eclipse IDE. This post shows how to install and configure Eclipse IDE, include the mysql-connector-java.jar, and write Java to access the MySQL.
You can download Eclipse IDE and then open it in Fedora’s Archive Manager. You can use the Archive Manager to Extract the Eclipse IDE to a directory of your choice. I opted to extract it into my student
user’s home directory, which is /home/student
.
After extracting the Eclipse IDE, you can check the contents of the eclipse
directory with the following command:
ls -al eclipse |
You should see the following:
drwxrwxr-x. 8 student student 4096 May 8 22:16 . drwx------. 33 student student 4096 May 8 21:57 .. -rw-rw-r--. 1 student student 119194 Mar 20 07:10 artifacts.xml drwxrwxr-x. 11 student student 4096 May 8 22:16 configuration drwxrwxr-x. 2 student student 4096 Mar 20 07:10 dropins -rwxr-xr-x. 1 student student 78782 Mar 20 07:08 eclipse -rw-rw-r--. 1 student student 315 Mar 20 07:10 eclipse.ini -rw-rw-r--. 1 student student 60 Mar 17 15:11 .eclipseproduct drwxrwxr-x. 41 student student 4096 Mar 20 07:10 features -rwxr-xr-x. 1 student student 140566 Mar 20 07:08 icon.xpm drwxrwxr-x. 4 student student 4096 Mar 20 07:09 p2 drwxrwxr-x. 12 student student 40960 Mar 20 07:10 plugins drwxrwxr-x. 2 student student 4096 Mar 20 07:10 readme |
You can launch the Eclipse IDE with the following command-line from the eclipse
directory:
./eclipse & |
While you can run this from the /home/student/eclipse
directory, it’s best to create an alias for the Eclipse IDE in the student
user’s .bashrc
file:
# Set alias for Eclipse IDE tool. alias eclipse="/home/student/eclipse/eclipse" |
The next time you start the student
user account, you can launch the Eclipse IDE by entering eclipse
in the search box opened by clicking on the Activities menu.
The following steps take you through installing Eclipse on Fedora Linux, which is more or less the same as any Linux distribution. It’s very similar on Windows platforms too.
Eclipse Installation
- Navigate to eclipse.org/downloads web page to download the current version of the Eclipse software. Click the Linux 32 Bit or Linux 64 Bit link, as required for your operating system.
- Click the Green Arrow to download the Eclipse software.
- The next dialog gives you an option to open or save the software. Click the Open with radio button to open the archive file.
- This the Linux Archive Manager. Click the Extract button from the menu tab to open the archive file.
- This extract button on file chooser dialog to install Eclipse into the /home/student/eclipse directory. Click the Extract button to let the Archive Manager create a copy of those files.
- The Archive Manager presents a completion dialog. Click the Close button to close the Archive Manager.
After installing the Eclipse software, you can configure Eclipse. There are sixteen steps to setup the Eclipse product. You can launch the product with the
Eclipse Setup
You need to launch the Eclipse application to perform the following steps. The syntax is the following when you did create the alias mentioned earlier in the blog post:
eclipse & |
The following steps cover setting up your workspace, project, and adding the MySQL JDBC Java archive.
- The branding dialog may display for 30 or more seconds before the Eclipse software application launches.
- The Workspace Launcher opens first on a new installation. You need to designate a starting folder. I’m using
/home/student/workspace
as my Workspace. Click the OK button when you enter a confirmed workspace.
- After setting the Workspace Launcher, you open to the Eclipse Welcome page. Click second of the two icons on the left to open a working Eclipse environment. Alternatively, you can connect to Tutorials on the same page.
- From the developer view, click on the File menu option, the New option on the list, and the Java Project option on the floating menu. Eclipse will now create a new Java project.
- The New Java Project dialog lets you enter a project name and it also gives you the ability to set some basic configuration details. As a rule, you simply enter the Project Name and accept the defaults before clicking the Finish button.
- After creating the new Java project, Eclipse returns you to the Welcome page. Click second of the two icons on the left to open a working Eclipse environment.
- Now you should see the working environment. Sometimes it takes the full screen but initially it doesn’t. Navigate to the lower right hand side, and expand the window to full size.
- Now you should see the full screen view of the Eclipse working environment.
- Now you create a new Java class by navigating to the File menu options, then the New menu option, and finally choosing the Class floating menu.
- The New Java Class dialog requires you to provide some information about the Java object you’re creating. The most important thing is the Java class name.
- The only difference in this copy of the New Java Class dialog is that I’ve entered
HelloWorld
as the Java Class’s name. Click the Finish button when you’re done.
- Eclipse should show you the following
HelloWorld.java
file. It’s missing amain()
method. Add a static main() method to theHelloWorld.java
class source file.
- This form shows the changes to the
HelloWorld.java
file. Specifically, it adds the It’s missing amain()
method. Add a static main() method to theHelloWorld.java
class source file.
- You can click the green arrow from the tool panel or you can click the Run menu option and Run submenu choice to test your program.
1 2 3 4
// Class definition. public class HelloWorld { public static void main(String args[]) { System.out.println("Hello World."); }}
- The Save and Launch dialog tells you that you’re ready to test creating a copy of the Java class file. Click the OK button to continue.
- The results from your program are written to the Console portion of the Eclipse IDE. This concludes the setup of a workspace, project, and deployment of actual Java classes.
Hello World.
Add MySQL JDBC Library
The following instructions add the MySQL Library and demonstrate how to write Java programs that connect to the MySQL database. They also use the mysql
project.
- Navigate to the Project menu and choose the Properties menu option.
- The Properties menu option opens the Properties for the
mysql
project on the Order and Export tab. Click the Libraries tab to add an external library.
- In the Libraries tab click the Add Library… button on the right to add an external library.
- In the JAR Selection dialog, click on Computer in the Places list, then click on usr, click on share, and click on java. The Name list should now include
mysql-connector-java.jar
file, and you should click on it before clicking on the OK button.
- You create new Java class file by clicking on the File menu. Then, you choose the New menu option and the Class menu option from the floating menu.
- Enter
MysqlConnector
as the name of the new Java class file and click the Finish button to continue.
- Eclipse generates the shell of the
MysqlConnector
class as shown in the illustration to the left.
- You should replace the
MysqlConnector
class shell with the code below. Then, click the green arrow or the Run menu and Run menu option to compile and run the new MysqlConnector Java class file.1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class MysqlConnector extends Object { public static void main(String[] args) { try { /* The newInstance() call is a work around for some broken Java implementations. */ Class.forName("com.mysql.jdbc.Driver").newInstance(); /* Verify the Java class path. */ System.out.println("===================="); System.out.println("CLASSPATH [" + System.getProperty("java.class.path") + "]"); System.out.println("===================="); } catch (Exception e) {} finally { /* Verify the Java class path. */ System.out.println("===================="); System.out.println("CLASSPATH [" + System.getProperty("java.class.path") + "]"); System.out.println("===================="); } } }
- The Save and Launch dialog informs you are saving a
MysqlConnector.java
file to yourmysql
project. Click the OK button to continue.
- The next screen shows that the program successfully connected to the MySQL database by printing the following information to the Console output tab.
==================== CLASSPATH [/home/student/Code/workspace/MySQL/bin:/usr/share/java/mysql-connector-java.jar] ==================== ==================== CLASSPATH [/home/student/Code/workspace/MySQL/bin:/usr/share/java/mysql-connector-java.jar] ====================
- Instead of repeating steps #5 through #10, the image displays the testing of the MysqlResults class file. The code follows below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
/* Import the java.sql.* package. */ import java.sql.*; /* You can't include the following on Linux without raising an exception. */ // import com.mysql.jdbc.Driver; public class MySQLResult { public MySQLResult() { /* Declare variables that require explicit assignments because they're addressed in the finally block. */ Connection conn = null; Statement stmt = null; ResultSet rset = null; /* Declare other variables. */ String url; String username = "student"; String password = "student"; String database = "studentdb"; String hostname = "localhost"; String port = "3306"; String sql; /* Attempt a connection. */ try { // Set URL. url = "jdbc:mysql://" + hostname + ":" + port + "/" + database; // Create instance of MySQL. Class.forName ("com.mysql.jdbc.Driver").newInstance(); conn = DriverManager.getConnection (url, username, password); // Query the version of the database, relies on *_ri2.sql scripts. sql = "SELECT i.item_title, ra.rating FROM item i INNER JOIN rating_agency ra ON i.item_rating_id = ra.rating_agency_id"; stmt = conn.createStatement(); rset = stmt.executeQuery(sql); System.out.println ("Database connection established"); // Read row returns for one column. while (rset.next()) { System.out.println(rset.getString(1) + ", " + rset.getString(2)); } } catch (SQLException e) { System.err.println ("Cannot connect to database server (SQLException):"); System.out.println(e.getMessage()); } catch (ClassNotFoundException e) { System.err.println ("Cannot connect to database server (ClassNotFoundException)"); System.out.println(e.getMessage()); } catch (InstantiationException e) { System.err.println ("Cannot connect to database server (InstantiationException)"); System.out.println(e.getMessage()); } catch (IllegalAccessException e) { System.err.println ("Cannot connect to database server (IllegalAccesException)"); System.out.println(e.getMessage()); } finally { if (conn != null) { try { rset.close(); stmt.close(); conn.close(); System.out.println ("Database connection terminated"); } catch (Exception e) { /* ignore close errors */ } } } } /* Unit test. */ public static void main(String args[]) { new MySQLResult(); } }
After you click the green arrow or the Run menu and Run menu option to compile and run the program, you should see the following output. That is if you’re using my
create_mysql_store_ri2.sql
andseed_mysql_store_ri2.sql
files.Database connection established I Remember Mama, NR Tora! Tora! Tora!, G A Man for All Seasons, G Around the World in 80 Days, G Camelot, G Christmas Carol, G I Remember Mama, G The Hunt for Red October, PG Star Wars I, PG Star Wars II, PG Star Wars II, PG The Chronicles of Narnia, PG Beau Geste, PG Hook, PG Harry Potter and the Sorcerer's Stone, PG Scrooge, PG Harry Potter and the Sorcer's Stone, PG Harry Potter and the Sorcer's Stone, PG Harry Potter and the Chamber of Secrets, PG Harry Potter and the Chamber of Secrets, PG Harry Potter and the Prisoner of Azkaban, PG Harry Potter and the Prisoner of Azkaban, PG Harry Potter and the Half Blood Prince, PG Star Wars III, PG-13 Casino Royale, PG-13 Casino Royale, PG-13 Die Another Day, PG-13 Die Another Day, PG-13 Die Another Day, PG-13 Golden Eye, PG-13 Golden Eye, PG-13 Tomorrow Never Dies, PG-13 Tomorrow Never Dies, PG-13 The World Is Not Enough, PG-13 Clear and Present Danger, PG-13 Clear and Present Danger, PG-13 Harry Potter and the Goblet of Fire, PG-13 Harry Potter and the Goblet of Fire, PG-13 Harry Potter and the Goblet of Fire, PG-13 Harry Potter and the Order of the Phoenix, PG-13 Harry Potter and the Deathly Hallows, Part 1, PG-13 Harry Potter and the Deathly Hallows, Part 2, PG-13 Brave Heart, R The Chronicles of Narnia, E MarioKart, E Need for Speed, E Cars, E RoboCop, M Pirates of the Caribbean, T Splinter Cell, T The DaVinci Code, T Database connection terminated
As always, I hope the note helps those trying to work with the Eclipse product.
MySQL OCP Exams
Planning out my year, I decided to take the Oracle OCP and MySQL OCP exams. I checked for review books and was pleasantly surprised to find the soon to be released OCP MySQL Database Administrator Exam Guide (Exam 1Z0-883). However, I noticed that the book was actually prepared for the obsolete and discountinued Exams 1Z0-870, 1Z0-873, and 1Z0-874. As it turns out, Steve O’Hearn has informed me that there isn’t a book and that the posting in Amazon.com is in error.
There isn’t an alternative review book for the OCP MySQL 5.6 Developer or Database Administrator Exams. The question that I have is simple: “How relevant is this book because it was prepared for the older exams?” There isn’t a table of content published on the Amazon.com site. If there was a table of contents it could help me determine how close the book’s content is to the new exam.
As a preparation to figure out the value of the book as a study guide, I’ve reviewed the current Oracle MySQL Training Objectives (listed below). The new MySQL OCP Developer and Administrator exams have the following descriptions and objectives:
- MySQL 5.6 Developer 1Z0-882. Oracle provides the following outline for their MySQL for Developer (Ed 3) training course:
Course Objectives
- Describe the MySQL client/server architecture
- Use MySQL client programs and common options
- Program MySQL applications with Java and PHP connectors
- Use a “NoSQL” approach to store and retrieve data
- Design efficient tables
- Create and delete database objects
- Use expressions in SQL statements
- Examine database metadata
- Use SQL statements to modify table data
- Maintain database integrity with transactions
- Write multiple table queries
- Create “virtual tables” containing specific data
- Create user-defined variables, prepared statements, and stored routines
- Create and manage triggers
- Identify and deal with errors and exceptions in client programs
- Write queries that are fast and effective, even under heavy loads
- MySQL 5.6 Database Administrator 1Z0-883. Oracle provides the following outline for their MySQL for Database Administrators (Ed 3.1) training course:
Course Objectives
- Describe the MySQL Architecture
- Install and Upgrade MySQL
- Use the INFORMATION_SCHEMA database to access metadata
- Perform the MySQL start and shutdown operations
- Configure MySQL server options at runtime
- Use available tools including MySQL Workbench
- Evaluate data types and character sets for performance issues
- Understand data locking in MySQL
- Understand the use of the InnoDB storage engine with MySQL
- Maintain integrity of a MySQL installation
- Use triggers for administration tasks
- Use Enterprise Audit and Pluggable Authentication
- Configure advanced replication topologies for high availability
- Describe introductory performance tuning techniques
- Perform backup and restore operations
- Automate administrative tasks with scheduled events
As always, I hope this helps those who read it; and, in this case I hope it helps you make an effective decision on preparation resources for the MySQL 5.6 OCP exams.