What Exactly IS a Relational Database Management System RDBMS

IBM doesn’t just make hardware. They also have laboratories like mad scientists. It was in their San Jose Research Laboratory that Edgar Frank Codd developed the DBMS (database management system) relational model. RDBMSs still encase the backbone of most systems worldwide, even with the introduction of object-oriented database management systems or OODBMS …seriously not every computer term needs an acronym.

MySQL by Oracle Corporation is still deployed on 50% of database backed sites according to Gartner. Because it’s open source (free!) it is typically the introductory database most developers come across. The other big giants are Microsoft’s SQL Server and Oracle Database. Structured Query Language (SQL) is a specific language used to insert, query, update, and delete data inside the database.

Here’s the basic structure of an SQL Statement:
SQL example statement

Java Connect to Remote Database through SSH using Port Forwarding

There may be a time when you need to connect to a remote database using an SSH connection. Here is an example of using port forwarding to connect remotely. This example is for MySQL but you can use it for almost any database.

package com.journaldev.java.ssh
 
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
import java.sql.Connection;
 
 
public class MySqlConnOverSSH {
 
   
    public static void main(String[] args) throws SQLException {
 
        int lport=5656;
        String rhost="secure.sethcodes.com";
        String host="secure.sethcodes.com";
        int rport=3306;
        String user="sshuser";
        String password="sshpassword";
        String dbuserName = "mysql";
        String dbpassword = "mysql123";
        String url = "jdbc:mysql://localhost:"+lport+"/mydb";
        String driverName="com.mysql.jdbc.Driver";
        Connection conn = null;
        Session session= null;
        try{
            //Set StrictHostKeyChecking property to no to avoid UnknownHostKey issue
            java.util.Properties config = new java.util.Properties(); 
            config.put("StrictHostKeyChecking", "no");
            JSch jsch = new JSch();
            session=jsch.getSession(user, host, 22);
            session.setPassword(password);
            session.setConfig(config);
            session.connect();
            System.out.println("Connected");
            int assinged_port=session.setPortForwardingL(lport, rhost, rport);
            System.out.println("localhost:"+assinged_port+" -> "+rhost+":"+rport);
            System.out.println("Port Forwarded");
             
            //mysql database connectivity
            Class.forName(driverName).newInstance();
            conn = DriverManager.getConnection (url, dbuserName, dbpassword);
            System.out.println ("Database connection established");
            System.out.println("DONE");
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            if(conn != null && !conn.isClosed()){
                System.out.println("Closing Database Connection");
                conn.close();
            }
            if(session !=null && session.isConnected()){
                System.out.println("Closing SSH Connection");
                session.disconnect();
            }
        }
    }
 
}

Write to MySQL Database Using Java

Probably one of the most common tasks you’ll ever utilize is writing to a database. Java INSERT is a very basic code snippet. This code inserts a first and last name into the table.

create table users (
  id int unsigned auto_increment not null,
  first_name varchar(32) not null,
  last_name varchar(32) not null,
  date_created timestamp default now(),
  is_admin boolean,
  num_points int,
  primary key (id)
);

Just for your reference here is another way to write to a MySQL database using JavaScript. Note: I’ve got the database host set to “localhost” since we are executing the script on the same server. The username is set to “root”.

This script will be inserting a first name, last name, and date created etc. I created a Prepared Statement to capture all the values before executing the write.

import java.sql.*;
import java.util.Calendar;

public class JavaMysqlPreparedStatementInsertExample
{

  public static void main(String[] args)
  {
    try
    {
      // create a mysql database connection
      String myDriver = "org.gjt.mm.mysql.Driver";
      String myUrl = "jdbc:mysql://localhost/test";
      Class.forName(myDriver);
      Connection conn = DriverManager.getConnection(myUrl, "root", "");
    
      // create a sql date object so we can use it in our INSERT statement
      Calendar calendar = Calendar.getInstance();
      java.sql.Date startDate = new java.sql.Date(calendar.getTime().getTime());

      // the mysql insert statement
      String query = " insert into users (first_name, last_name, date_created, is_admin, num_points)"
        + " values (?, ?, ?, ?, ?)";

      // create the mysql insert preparedstatement
      PreparedStatement preparedStmt = conn.prepareStatement(query);
      preparedStmt.setString (1, "Barney");
      preparedStmt.setString (2, "Rubble");
      preparedStmt.setDate   (3, startDate);
      preparedStmt.setBoolean(4, false);
      preparedStmt.setInt    (5, 5000);

      // execute the preparedstatement
      preparedStmt.execute();
      
      conn.close();
    }
    catch (Exception e)
    {
      System.err.println("Got an exception!");
      System.err.println(e.getMessage());
    }
  }
}

Now that you’ve written to the database you can query it using the following script:

select * from users;

The output should resemble this:


+----+------------+-----------+---------------------+----------+------------+
| id | first_name | last_name | date_created        | is_admin | num_points |
+----+------------+-----------+---------------------+----------+------------+
|  1 | Barney     | Rubble    | 2010-06-23 14:02:00 |        0 |       5000 | 
+----+------------+-----------+---------------------+----------+------------+
1 row in set (0.00 sec)

Inserting Data Into MySQL Database Using Perl

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
  `name_first` varchar(30) NOT NULL,
  `name_last` varchar(30) NOT NULL,
  `address_01` varchar(40) NOT NULL,
  `address_02` varchar(40) NOT NULL,
  `address_city` varchar(30) NOT NULL,
  `address_state` varchar(20) NOT NULL,
  `address_postal_code` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 1;


We are only going to insert one address line with our data. You can modify this script to loop the process and insert multiple rows of data.





use DBI;
use DBD::mysql;

use warnings;

$database = "scripting_mysql";

$connection = ConnectToMySql($database);

$query = "insert into address (name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code) 
			values (?, ?, ?, ?, ?, ?, ?) ";
$statement = $connection->prepare($query);

$statement->execute('John', 'Smith', '100 Main Street', 'Suite 500', 'Buffalo', 'NY', '14201');


$query2 = "select name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code from address where name_last = 'Smith'";


$statement = $connection->prepare($query2);

$statement->execute();

# we will loop through the returned results that are in the @data array
# even though, for this example, we will only be returning one row of data

   while (@data = $statement->fetchrow_array()) {
      $name_first = $data[0];
      $name_last = $data[1];
      $address_01 = $data[2];
      $address_02 = $data[3];
      $address_city = $data[4];
      $address_state = $data[5];
      $address_postal_code = $data[6];

print "RESULTS - $name_first, $name_last, $address_01, $address_02, $address_city, $address_state, $address_postal_code\n";

}

exit;

sub ConnectToMySql {

my ($db) = @_;

open(ACCESS_INFO, "<..\/accessAdd") || die "Can't access login credentials";

my $database = <ACCESS_INFO>;
my $host = <ACCESS_INFO>;
my $userid = <ACCESS_INFO>;
my $passwd = <ACCESS_INFO>;

my $connectionInfo="dbi:mysql:$db;$host";

close(ACCESS_INFO);
chomp ($database, $host, $userid, $passwd);
my $l_connection = DBI->connect($connectionInfo,$userid,$passwd);
return $l_connection;

}