Texas A&M University



How to use MySQL Databases with Your Java Applications

By Samuel Alex Schoen

Installing MySQL:

MySQL has released a community server which is very easy to set up and install. It can be found at

You do not need to register. At the bottom will be to option to move to downloads.

When you download and install it, it will ask if you want to run the MySQL configuration wizard. It is highly recommended that you do this.

1. By default, the port for MySQL is :3306/. If you do not do that then you will not be able to establish a connection. Make sure to allow the port through your router and to set up a rule in the Windows Firewall.

Windows XP ( Control Panel ( Security Center (Windows Firewall ( Exceptions tab

[pic]

** MAC USERS. Some installs don’t let you set a username/password etc…, by default the username is “root”, and the password is empty (“”)

2. Choose the “Typical” installation

3. When asked for the configuration type, select Standard Configuration

4. If you intend for the database to run all the time, check the 'Install as Windows Service' Check Box.

5. On the next page, it will ask you to set the Security Settings. Change the root password to “CMSC202” (something secure yet easy for you to remember). Do not check 'Create an Anonymous User Account' because it will cause your database to be insecure. Make sure to check 'Allow Remote Login'.

6. At the last page, click Execute to have the Configuration Wizard set up the database

Now that the MySQL database has been installed, you can now load it up by going to the Start Menu, finding the MySQL folder, and loading the MySQL Command Line Client.

If something pops up about “authentication string doesn’t have a default value”, use these links below to help:







Creating Your First Database:

Now that you have the MySQL Command Line Client installed and the window open, you can create your first database. You must first log in using the 'root' user name and the password that you specified.

This is accomplished by typing the following command and pressing enter.

CREATE DATABASE myFirstAndLastName;

where myFirstAndLastName is obviously replaced by the desired name of the database. Like in Java, all of the commands must end in a semicolon.

Now that the MySQL database has been created, you can manipulate it. Let’s prove you created it. In the DOS prompt type:

show myFirstAndLastName;

It should say OK (and list, which is empty), if not, alert your professor. Many other basic MySQL commands are covered in the Reference section in WebCT/BB.

Installing the Java Database Connector (JDBC):

The Java Database Connector (known from now on as the JDBC) allows one to create Java programs which interface with a wide variety of Databases. The JDBC API provides classes and methods which allow us to manage the Database from within the Java program.

JDBC helps you to write Java applications that manage these three programming activities:

1. Connect to a data source, like a database

2. Send queries and update statements to the database

3. Retrieve and process the results received from the database in answer to your query

Installing the JDBC is fairly simple. You will need to download the Connector, which provides the classes and methods needed for your Java program to connect to the Database. It is located at:



The files are contained in a .ZIP file. The actual file you are looking for is located right in the .ZIP file. It is called 'mysql-connector-java-[versionnumber]-bin.jar'. In my case, it was named

'mysql-connector-java-5.1.14-bin.jar'. You need to extract this file into your Java CLASSPATH.

In my case, I had to place the connector jar into the 'C:\Program Files\Java\jre6\lib\ext' folder, you may have a newer version, put it in THAT folder. After restarting eclipse, it automatically allowed me to use any of the methods from the JDBC API.

Using the JDBC Connector:

Using the JDBC connector is actually fairly simple. You first load the class object for the MySQL driver (in our case, the JDBC) into an object known as the DriverManager. Then you attempt to establish a connection to the Database using the DriverManager. Once you have established a connection, it is as simple as submitting the SQL queries and then closing the connection once you are finished.

One thing that is extremely important to note is that you should try to establish as few connections and submit as few queries as possible to the Database. Excess connections and queries will cause your program to run slowly.

In this example method from my program, it establishes a connection with the server and then returns the connection for further use.

|import java.sql.*; |

| |

| |

|public class driver { |

| |

|public static void main(String args[]) |

|{ |

| |

|Connection con = null; |

|//String url = "jdbc:mysql://mer.hosted.:3306/"; |

|String url = "jdbc:mysql://localhost:3306/"; |

|String dbName = "test"; |

|String driverName = "com.mysql.jdbc.Driver"; |

|String userName = "root"; |

|String password = "cmsc202"; |

|try |

|{ |

|Class.forName(driverName).newInstance(); |

|con = DriverManager.getConnection(url+dbName, userName, password); |

|try |

|{ |

|Statement st = con.createStatement(); |

|String table = "CREATE TABLE crap(Emp_ID INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (Emp_ID), Emp_code INT, Emp_name varchar(20), Emp_Salary |

|INT )"; |

|st.executeUpdate(table); |

|System.out.println("Table creation process successfully!"); |

|} |

|catch(SQLException s) |

|{ System.out.println(e); /* will show error!! very useful!! */ } |

|con.close(); |

|} |

|catch(Exception e) { e.printStackTrace(); } |

|} |

| |

|} |

In the above example, it connects to the specified URL and creates a table using the specified Database, user, password, and URL.

The connection methods must be contained in a try/catch block in case there is an exception. Also you must import the java.sql.*; libraries in order for the JDBC connector to work.

The first is declaring a Connection. The reason for 'Connection con = null;' is because it is always best to ensure that the connection is empty at first.

After that happens, the program loads a class object for the MySQL driver into the DriverManager using 'Class.forName(driverName).newInstance();' using the name of the driver specified in the ' driverName' string. You can also load a specific driver into the DriverManager but since we are only using MySQL there is no reason to do otherwise.

After you have specified the driver to use and loaded the MySQL driver, you must create the actual connection. As mentioned earlier, it is best to try to do this as little as possible, to avoid placing too many queries on the Database. An improperly set up MySQL program could cause the Database to run poorly.

The DriverManager has the following method with establishes a connection to the

DriverManager.getConnection(String url, String user, String password)

One extremely important thing to note is that when setting up the URL for the getConnection method is that you must provide the name of the SQL driver and the type of SQL software you are connecting to, as well as the port number at the end.

Example URL...

jdbc:mysql://mer.hosted.:3306/

In the URL I used for my program, you can see how before the URL there is the jdbc (driver) and the mysql (SQL software) before the actual host name of the database. They must be separated by colons as well. However in our case, you can essentially just use that all the time in front of the connection statement. Also you must specify the port at the end of the URL if you are connecting remotely. By default, the port for MySQL is :3306/. If you do not do that then you will not be able to establish a connection. Make sure to allow the port through your router and to set up a rule in the Windows Firewall.

Now that you have established a connection, it is time to actually manipulate the data in the Database. The first thing you need to do is create a Statement. A statement is what actually executes the SQL query. Since the MySQL service was selected to run “full time”, you do not have to run the MySQL DOS screen.

In my example, Statement st = con.createStatement(); creates a statement known as st using the createStatement() method from the con Connection. Once you have created the statement, you must create a String variable which holds the SQL query.

String table = "CREATE TABLE EmpTable(Emp_ID INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (Emp_ID), Emp_code INT, Emp_name varchar(20), Emp_Salary INT )";

In this example. The String holds the SQL statement to create the table.

The SQL statement to create a table is 'CREATE TABLE [tablename] followed by the name of the fields and the type of variable which the field holds. Please note that the brackets around tablename are there just to show that you enter in the name of the table. In the actual code, there are no brackets around the table name. In this example, it creates a table known as EmpTable with four fields. The first, Emp_ID is set as the primary field and auto-increments itself as each entry is added. That means the first entry receives the value of 0, the second, 1, and so on. Then it creates the fields Emp_Code as an INT, Emp_Name as a string (in SQL it is known as a varchar) that holds 20 characters, and Emp_Salary, which is an INT.

Once you have build the SQL statement in the string, whether from user input or a programmer defined string, you must actually execute the query.

In this example, the the statement is 'st.executeUpdate(table);'

The executeUpdate(String) method for the Statement that you created actually executes the SQL statement which is contained in the string. In this example, the string is named table, because it is creating a table. In order for you to make any changes to the table, you must execute the query.

You can also use the executeUpdate(String) to execute almost any SQL query that you desire.

|// This example uses Swing GUI to grab values from JTextboxes |

| |

|try{ |

|Statement st = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); |

|String empcode = textCode.getText(); |

|String empname = textName.getText(); |

|String empsalary = textSalary.getText(); |

|String add = "INSERT INTO EmpTable (Emp_code, Emp_name,Emp_Salary) VALUES (" + empcode + ", '"+empname+"',"+empsalary+")"; |

|System.out.println(add); |

|st.executeUpdate(add); |

|JOptionPane.showMessageDialog(null, add+ " - Added"); |

|System.out.println("Added"); |

|} |

|catch(SQLException s){ |

|System.out.println("Failure to Add"); |

|} |

|con.close(); |

In this example, it shows how you can add a value into the table. A database without any entries is useless so this is very important. The Statement that is created is slightly different then in the previous example. Whenever you create a statement, it returns a ResultSet, which is a table of data retrieved from the SQL query. A ResultSet is extremely powerful and has its own methods for navigating the data and is essential for creating a powerful Java Database application. In the Statement, ' ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE' the ResultSet.TYPE_FORWARD_ONLY makes it so that the ResultSet can only navigate forward. Some Statements allow you to have full control over navigating the ResultSet, however since in this example we are adding data, it makes sense for the ResultSet to only be able to go forward. The ResultSet.CONCUR_UPDATABLE means that the Statement can modify the ResultSet, allowing the program to change and modify rows in the Database. This is important because while a lot of the time, you do not want the user to be able to modify the data in the table, in this case you are adding an Employee.

In this example, the SQL query is built using Strings retrieved from the JtextFields. It is very important to have the SQL Query be properly contructed.

A proper SQL Insert statement is setup like this: 'INSERT INTO [tablename] (columnname1, columnname2, ....) VALUES ( variable1, variable2, ...)'

It is also important to make sure that any string variables are surrounded with apostrophes, else it will not execute properly. Once you have built the SQL statement, you simply use the Statement.executeUpdate(String) method to insert the value into the table, similar to the first example.

One of the most common actions involving a Database involves retrieving data from it. The syntax of an SQL Select statement is 'SELECT [columns] FROM [tablename] WHERE [condition]'. When you issue a select statement using a Statement.executeUpdate(String) method, it will return the data in a ResultSet. Navigating through the ResultSet is luckily made very easy because of the methods for the ResultSet. The ResultSet is navigated using a 'cursor' which points at a row in the ResultSet. The cursor is used to retrieve data and modify or delete the row. A full list of the ResultSet methods can be found here.

Some of the most important though are as follows.

|absolute(int rownumber) |Moves the cursor to the given row number. |

|first() |Moves the cursor to the first row in the ResultSet. |

|Last() |Moves the cursor to the last row in the ResultSet. |

|next() |Moves the cursor to the next row in the ResultSet. |

|previous() |Moves the cursor to the previous row in the ResultSet. |

|deleteRow() |Deletes the row which the ResultSet cursor is currently pointing at. |

|getInt(int columnnum) |Retrieves an integer value from the column number from which the cursor is currently pointing at. NOTE: there|

| |is a getVariable method for every type of variable which works exactly the same way. The first column is 0, |

| |the second 1, and so on. |

|updateInt(int columnname) |Updates the integer value from the column number from which the cursor is currently pointing at. NOTE: there |

| |is a updateVariable method for every type of variable which works exactly the same way. The first column is |

| |0, the second 1, and so on. |

|updateRow() |Updates the current row and saves the values into the database. |

|refreshRow() |Refreshes the current row with its most recent value in the database. |

Using the above Methods makes it very easy to navigate a ResultSet and retrieve Data from it.

|try { |

|resultSet.absolute(currentSpinVal); |

|resultSet.updateString(2, textCode.getText()); |

|resultSet.updateString(3, textName.getText()); |

|resultSet.updateString(4, textSalary.getText()); |

|resultSet.updateRow(); |

|System.out.println("Row " + currentSpinVal + " updated!"); |

| |

|} catch (SQLException e1) { |

|e1.printStackTrace(); |

|} |

In this example, the ResultSet moves the cursor to the value indicated by the integer variable (actually a Jspinner component). Then the ResultSet updates the rows based on Strings retrieved from three JtextFields. Finally it uses the updateRow() method to make the values permanent.

| resultSet.absolute(currentSpinVal); |

|resultSet.deleteRow(); |

In the above example, it shows how you can delete a row from the Database as well. It is as simple as pointing the ResultSet cursor at the row you want to be deleted and then using the ResultSet.deleteRow() method.

While not used in my example program, it is also important to mention how to delete data without using a ResultSet. The following SQL query is used to delete rows which follow a condition.

'DELETE FROM [tablename] WHERE condition'

A good example would be

'DELETE FROM EmpTable WHERE empcode = 5'

That would delete any record from the table EmpTable which can an empcode of 5.

Deleting a column is simple as well.

It is simply 'ALTER TABLE [tablename] DROP COLUMN [columnname]'

If for some reason you desire to delete an entire table from a Database you simply use the

'DROP TABLE [tablename]' SQL statement.

Conclusion:

The JDBC is extremely powerful and has many options available to it. Hopefully this guide has helped you understand how to use it. Creating Java applications which interface with databases can be daunting at first but the JDBC connector has a large amount of helpful methods.

You can find out more at the following...

Java JDBC API and Guides

Using JDBC with MySQL, Getting Started

Pictures of the program...

Contents of my program...

EmployeeFrame

import javax.swing.*;

public class EmployeeFrame {

static JTabbedPane tp = new JTabbedPane();

public static void main(String[] args){

JFrame frame = new JFrame("Employee Manager");

frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

tp.addTab("Settings", new EmployeeSetting());

tp.addTab("Add", new EmployeeAdd());

tp.addTab("Edit/View", new EmployeeEdit());

frame.getContentPane().add(tp);

frame.pack();

frame.setVisible(true);

frame.setResizable(false);

}

public static void redoTab(){

tp.removeTabAt(2);//removes the tab and readds it, refreshing the values

tp.addTab("Edit/View", new EmployeeEdit());

tp.setSelectedIndex(2);

}

}

EmployeeAdd Class

import java.awt.event.*;

import java.sql.*;

import javax.swing.*;

public class EmployeeAdd extends JPanel{

ButtonListener listener = new ButtonListener();

JLabel labelCode = new JLabel("Employee ID");

JLabel labelName = new JLabel("Employee Name");

JLabel labelSalary = new JLabel("Employee Salary");

JTextField textCode = new JTextField(10);

JTextField textName = new JTextField(20);

JTextField textSalary = new JTextField(10);

JButton O_O = new JButton("Enter");

EmployeeAdd(){

add(labelCode);

add(textCode);

add(labelName);

add(textName);

add(labelSalary);

add(textSalary);

O_O.addActionListener(listener);

add(O_O);

}

private class ButtonListener implements ActionListener{

public void actionPerformed(ActionEvent event){

System.out.println("Connecting...");

Connection con = null;

String url = "jdbc:mysql://mer.hosted.:3306/";

String dbName = "mer_junk";

String driverName = "com.mysql.jdbc.Driver";

String userName = "mer";

String password = "outorrfLM9";

try{

System.out.println("Add Data to Table");

Class.forName(driverName).newInstance();

con = DriverManager.getConnection("jdbc:mysql://" + EmployeeSetting.urlString + ":3306/"+dbName, EmployeeSetting.userNameString, EmployeeSetting.passwordString);

System.out.println(con);

try{

Statement st = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

String empcode = textCode.getText();

String empname = textName.getText();

String empsalary = textSalary.getText();

String add = "INSERT INTO EmpTable (Emp_code, Emp_name,Emp_Salary) VALUES (" + empcode + ", '"+empname+"',"+empsalary+")";

System.out.println(add);

st.executeUpdate(add);

JOptionPane.showMessageDialog(null, add+ " - Added");

System.out.println("Added");

}

catch(SQLException s){

System.out.println("Failure to Add");

}

con.close();

}

catch (Exception e){

e.printStackTrace();

}

textCode.setText("");

textName.setText("");

textSalary.setText("");

}

}

}

EmployeeEdit Class

import java.awt.event.ActionEvent;

import java.awt.event.ActionListener;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import javax.swing.*;

import javax.swing.event.ChangeEvent;

import javax.swing.event.ChangeListener;

public class EmployeeEdit extends JPanel{

JLabel labelCode = new JLabel("Employee Code");

JLabel labelName = new JLabel("Employee Name");

JLabel labelSalary = new JLabel("Employee Salary");

JTextField textCode = new JTextField(10);

JTextField textName = new JTextField(20);

JTextField textSalary = new JTextField(10);

JButton edit = new JButton("Edit");

JButton refresh = new JButton("Refresh");

JButton delete = new JButton("Delete");

ResultSet resultSet = null;

static Connection con = null;

static String tableName = "EmpTable";

int NumRows = 0;

int currentSpinVal;

SpinnerModel model = null;

public EmployeeEdit(){

con = null;

try {

con = getConnection();

NumRows = countRows(con, tableName);

System.out.println("rowCount=" + NumRows);

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);

resultSet = stmt.executeQuery("SELECT * FROM EmpTable");

model = new SpinnerNumberModel(1, 1, NumRows, 1);

JSpinner box = new JSpinner(model);

box.addChangeListener(new SpinnerListener());

add(box);

} catch (Exception e) {

e.printStackTrace();

System.exit(1);

}

add(labelCode);

add(textCode);

add(labelName);

add(textName);

add(labelSalary);

add(textSalary);

try {

resultSet.absolute(1);

textCode.setText(resultSet.getString(2));

textName.setText(resultSet.getString(3));

textSalary.setText(resultSet.getString(4));

} catch (SQLException e2) {

e2.printStackTrace();

}

add(refresh);

refresh.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent e)

{

EmployeeFrame.redoTab();

}

});

add(edit);

edit.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent e)

{

try {

resultSet.absolute(currentSpinVal);

resultSet.updateString(2, textCode.getText());

resultSet.updateString(3, textName.getText());

resultSet.updateString(4, textSalary.getText());

resultSet.updateRow();

JOptionPane.showMessageDialog(null, "Row " + currentSpinVal + " updated!");

System.out.println("Row " + currentSpinVal + " updated!");

} catch (SQLException e1) {

e1.printStackTrace();

}

}

});

add(delete);

delete.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent e)

{

try {

resultSet.absolute(currentSpinVal);

resultSet.deleteRow();

JOptionPane.showMessageDialog(null, "Row " + currentSpinVal + " Deleted!");

EmployeeFrame.redoTab();

} catch (SQLException e1) {

e1.printStackTrace();

}

}

});

}

public static Connection getConnection() throws Exception {

String dbName = "mer_junk";

String driverName = "com.mysql.jdbc.Driver";

System.out.println("Connected");

Class.forName(driverName).newInstance();

Connection con = DriverManager.getConnection("jdbc:mysql://" + EmployeeSetting.urlString + ":3306/" + dbName, EmployeeSetting.userNameString, EmployeeSetting.passwordString);

return con;

}

public static SpinnerModel CreateSpinnerModel() throws Exception{

SpinnerModel smodel = null;

try {

Connection con = getConnection();

smodel = new SpinnerNumberModel(1, 1, countRows(con = getConnection(), tableName), 1);

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

return smodel;

}

public static int countRows(Connection con, String tableName) throws SQLException {

Statement stmt = null;

ResultSet rs = null;

int rowCount = -1;

try {

stmt = con.createStatement();

rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);

rs.next();

rowCount = rs.getInt(1);

} finally {

rs.close();

stmt.close();

}

return rowCount;

}

public class SpinnerListener implements ChangeListener{

public void stateChanged(ChangeEvent e) {

JSpinner spinner = (JSpinner) e.getSource();

Object value = spinner.getValue();

try {

currentSpinVal = (Integer) value;

resultSet.absolute((Integer) value);

textCode.setText(resultSet.getString(2));

textName.setText(resultSet.getString(3));

textSalary.setText(resultSet.getString(4));

} catch (SQLException e1) {

e1.printStackTrace();

}

}

}

}

EmployeeSetting Class

import javax.swing.*;

import java.awt.event.ActionEvent;

import java.awt.event.ActionListener;

import java.io.*;

import java.util.Scanner;

public class EmployeeSetting extends JPanel {

JLabel url = new JLabel("SQL Server URL");

JLabel userName = new JLabel("User Name");

JLabel password = new JLabel("Password");

static JTextField urlText = new JTextField(30);

static JTextField userNameText = new JTextField(10);

static JTextField passwordText = new JTextField(10);

JButton save = new JButton("Save Connection");

JButton load = new JButton("Load Connection");

static String urlString = null;

static String userNameString = null;

static String passwordString = null;

EmployeeSetting(){

add(url);

add(urlText);

add(userName);

add(userNameText);

add(password);

add(passwordText);

add(save);

save.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent e)

{

saveServer();

}

});

add(load);

load.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent e)

{

loadServer();

}

});

loadServer();

}

public static void saveServer(){

try {

PrintWriter pw = null;

pw = new PrintWriter(new FileWriter("server_con.txt", false));

// file server_con.txt is NOT provided!!!

pw.println(urlText.getText());

pw.println(userNameText.getText());

pw.println(passwordText.getText());

pw.close();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static void loadServer(){

try {

Scanner infile = null;

infile = new Scanner(new FileReader("server_con.txt"));

urlString = infile.nextLine();

userNameString = infile.nextLine();

passwordString = infile.nextLine();

infile.close();

urlText.setText(urlString);

userNameText.setText(userNameString);

passwordText.setText(passwordString);

} catch (FileNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

CreateTable Class

import java.sql.*;

public class CreateTable{

public static void main(String[] args) {

System.out.println("Table Creation Example!");

Connection con = null;

String url = "jdbc:mysql://mer.hosted.:3306/";

String dbName = "mer_junk";

String driverName = "com.mysql.jdbc.Driver";

String userName = "mer";

String password = "outorrfLM9";

try{

Class.forName(driverName).newInstance();

con = DriverManager.getConnection(url+dbName, userName, password);

try{

Statement st = con.createStatement();

String table = "CREATE TABLE EmpTable(Emp_ID INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (Emp_ID), Emp_code INT, Emp_name varchar(20), Emp_Salary INT )";

st.executeUpdate(table);

System.out.println("Table creation process successfully!");

}

catch(SQLException s){

System.out.println("Table all ready exists!");

}

con.close();

}

catch (Exception e){

e.printStackTrace();

}

}

}

DB Connector Class

• thank you Wahhd

| |

|Wahhd Sahlu |

|04/04/11 |

|CMSC 202 - CH1 |

|Lupoli |

| |

|Advanced Databases Lab |

| |

|Note: Changes to the program, besides the ones asked, have been made throughout the lab. For example, the closeConnection method in problem 5 was created to simplify the |

|code. These changes do not affect the answers of previous problems any significant way. The full example of the final code is shown at the end. It has been made more readable|

|than previous code examples. |

| |

|1. |

| |

|driver.java |

| |

|public class driver |

|{ |

|public static void main(String[] args) |

|{ |

| |

|} |

|} |

| |

|DBConnection.java |

| |

|public class DBConnection |

|{ |

|private String URL; |

|private String username; |

|private String password; |

|private String driverName; |

|private String currentDatabase; |

| |

|public String getURL() { return URL; } |

|public String getUsername() { return username; } |

|public String getPassword() { return password; } |

|public String getDriverName() { return driverName; } |

|public String getCurrentDatabase() { return currentDatabase; } |

| |

|public void setURL(String uRL) { URL = uRL; } |

|public void setUsername(String username) { this.username = username; } |

|public void setPassword(String password) { this.password = password; } |

|public void setDriverName(String driverName) { this.driverName = driverName; } |

|public void setCurrentDatabase(String currentDatabase) { this.currentDatabase = currentDatabase; } |

|} |

| |

|2. |

| |

|driver.java |

| |

|public class driver |

|{ |

|public static void main(String[] args) |

|{ |

|String URL = "jdbc:mysql://localhost:3306/"; |

|String username = "root"; |

|String password = "cmsc202"; |

|String driverName = "com.mysql.jdbc.Driver"; |

|String currentDatabase = "myfirstdatabase"; |

| |

|DBConnection newDBConnection = new DBConnection(URL, username, password, driverName, currentDatabase); |

|} |

|} |

| |

|DBConnection.java |

| |

|import java.sql.*; |

| |

|public class DBConnection |

|{ |

|private String URL; |

|private String username; |

|private String password; |

|private String driverName; |

|private String currentDatabase; |

|private Connection newConnection; |

| |

|public DBConnection() |

|{ |

|URL = null; |

|username = null; |

|password = null; |

|driverName = null; |

|currentDatabase = null; |

|newConnection = null; |

|} |

| |

|public DBConnection(String uRL, String username, String password, String driverName, String currentDatabase) |

|{ |

|URL = uRL; |

|this.username = username; |

|this.password = password; |

|this.driverName = driverName; |

|this.currentDatabase = currentDatabase; |

|newConnection = null; |

| |

|try |

|{ |

|Class.forName(driverName).newInstance(); |

|newConnection = DriverManager.getConnection(URL + currentDatabase, username, password); |

|System.out.println("Connection was successful.\n"); |

|newConnection.close(); |

|} |

|catch(Exception e) { System.out.println("Connection was unsuccessful.\n"); e.printStackTrace(); } |

|} |

| |

|public String getURL() { return URL; } |

|public String getUsername() { return username; } |

|public String getPassword() { return password; } |

|public String getDriverName() { return driverName; } |

|public String getCurrentDatabase() { return currentDatabase; } |

| |

|public void setURL(String uRL) { URL = uRL; } |

|public void setUsername(String username) { this.username = username; } |

|public void setPassword(String password) { this.password = password; } |

|public void setDriverName(String driverName) { this.driverName = driverName; } |

|public void setCurrentDatabase(String currentDatabase) { this.currentDatabase = currentDatabase; } |

|} |

| |

|[pic] |

| |

|driver.java |

| |

|public class driver |

|{ |

|public static void main(String[] args) |

|{ |

|String URL = "jdbc:mysql://localhost:3306/"; |

|String username = "root"; |

|String password = "cmsc202"; |

|String driverName = "com.mysql.jdbc.Driver"; |

|String currentDatabase = "asdfjkl;"; // currentDatabase value has changed |

| |

|DBConnection newDBConnection = new DBConnection(URL, username, password, driverName, currentDatabase); |

|} |

|} |

| |

|DBConnection.java (stays the same) |

| |

|[pic] |

|[pic] |

| |

|3. |

| |

|driver.java |

| |

|public class driver |

|{ |

|public static void main(String[] args) |

|{ |

|String URL = "jdbc:mysql://localhost:3306/"; |

|String username = "root"; |

|String password = "cmsc202"; |

|String driverName = "com.mysql.jdbc.Driver"; |

|String currentDatabase = "myfirstdatabase"; |

| |

|DBConnection newDBConnection = new DBConnection(URL, username, password, driverName, currentDatabase); |

| |

|newDBConnection.showTable("gamesPlayed"); |

|} |

|} |

| |

|DBConnection.java |

| |

|import java.sql.*; |

| |

|public class DBConnection |

|{ |

|private String URL; |

|private String username; |

|private String password; |

|private String driverName; |

|private String currentDatabase; |

|private Connection newConnection; |

|private ResultSet newResultSet; |

|private ResultSetMetaData newResultSetMetaData; |

| |

|public DBConnection() |

|{ |

|URL = null; |

|username = null; |

|password = null; |

|driverName = null; |

|currentDatabase = null; |

|newConnection = null; |

|newResultSet = null; |

|newResultSetMetaData = null; |

|} |

| |

|public DBConnection(String uRL, String username, String password, String driverName, String currentDatabase) |

|{ |

|URL = uRL; |

|this.username = username; |

|this.password = password; |

|this.driverName = driverName; |

|this.currentDatabase = currentDatabase; |

|newConnection = null; |

|newResultSet = null; |

|newResultSetMetaData = null; |

| |

|try |

|{ |

|Class.forName(driverName).newInstance(); |

|newConnection = DriverManager.getConnection(URL + currentDatabase, username, password); |

|System.out.println("Connection was successful.\n"); |

|} |

|catch(Exception e) { System.out.println("Connection was unsuccessful.\n\n" + e + "\n");} |

|} |

| |

|public String getURL() { return URL; } |

|public String getUsername() { return username; } |

|public String getPassword() { return password; } |

|public String getDriverName() { return driverName; } |

|public String getCurrentDatabase() { return currentDatabase; } |

| |

|public void setURL(String uRL) { URL = uRL; } |

|public void setUsername(String username) { this.username = username; } |

|public void setPassword(String password) { this.password = password; } |

|public void setDriverName(String driverName) { this.driverName = driverName; } |

|public void setCurrentDatabase(String currentDatabase) { this.currentDatabase = currentDatabase; } |

| |

|public boolean showTable(String newTableName) |

|{ |

|if(newTableName.contains(" ")) |

|{ |

|System.out.println("Your table name input has one or more spaces. Please try again.\n"); |

|try { newConnection.close(); System.out.println("Connection has been closed.\n"); } |

|catch(Exception e) { System.out.println("Connection is still open.\n\n" + e + "\n"); } |

|return false; |

|} |

|else |

|{ |

|try |

|{ |

|Statement statement1 = newConnection |

|.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); |

|newResultSet = statement1.executeQuery("SELECT * FROM " + newTableName); |

|System.out.println("The table \"" + newTableName + "\" in the \"" + currentDatabase |

|+ "\" database is listed below.\n"); |

|newResultSetMetaData = newResultSet.getMetaData(); |

|System.out.print("| "); |

|for(int i = 1; i ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download