SQL to NO-SQL mappings

Terminology and Concepts

The following table presents the various SQL terminology and concepts and the corresponding MongoDB terminology and concepts.

SQL Terms/Concepts	MongoDB Terms/Concepts
database	database
table	collection
row	document or BSON document
column	field
index	index
table joins	$lookup, embedded documents
primary key Specify any unique column or column combination as primary key.	primary key In MongoDB, the primary key is automatically set to the _id field.
aggregation (e.g. group by)	aggregation pipeline See the SQL to Aggregation Mapping Chart.
SELECT INTO NEW_TABLE	$out See the SQL to Aggregation Mapping Chart.
MERGE INTO TABLE	$merge (Available starting in MongoDB 4.2) See the SQL to Aggregation Mapping Chart.

Create and Alter¶

The following table presents the various SQL statements related to table-level actions and the corresponding MongoDB statements.

SQL Schema Statements	MongoDB Schema Statements
CREATE TABLE people ( id MEDIUMINT NOT NULL AUTO_INCREMENT, user_id Varchar(30), age Number, status char(1), PRIMARY KEY (id) )	Implicitly created on first insertOne() or insertMany() operation. The primary key _id is automatically added if _id field is not specified. db.people.insertOne( { user_id: "abc123", age: 55, status: "A" } ) However, you can also explicitly create a collection: db.createCollection("people")
ALTER TABLE people ADD join_date DATETIME	Collections do not describe or enforce the structure of its documents; i.e. there is no structural alteration at the collection level. However, at the document level, updateMany() operations can add fields to existing documents using the $set operator. db.people.updateMany( { }, { $set: { join_date: new Date() } } )
ALTER TABLE people DROP COLUMN join_date	Collections do not describe or enforce the structure of its documents; i.e. there is no structural alteration at the collection level. However, at the document level, updateMany() operations can remove fields from documents using the $unset operator. db.people.updateMany( { }, { $unset: { "join_date": "" } } )
CREATE INDEX idx_user_id_asc ON people(user_id)	db.people.createIndex( { user_id: 1 } )
CREATE INDEX idx_user_id_asc_age_desc ON people(user_id, age DESC)	db.people.createIndex( { user_id: 1, age: -1 } )
DROP TABLE people	db.people.drop()

Formatting the code in intellij

The most easy way to reformat the code is with the shortcut: Ctrl + Alt + L (or also Ctrl + Windows Key + Alt + L, useful for Linux users that have the Ctrl + Alt + L shortcut for locking the screen.

 Format options

The options for formatting the code are defined in “File/Settings/Editor/Code Style”. Under this last option, there’s a code style scheme for different file types, but we will just look for the Java ones.

The code style options are categorized in the following categories:

• Tabs and indents: the configuration for the indentation, i.e., the indentation of the code blocks. You can also choose to use the tab character, or several spaces.
• Spaces: this section allows to configure the white spaces before and after the control structures and operators, for almost every possible option. For example, to set a white space before the opening curly brace {, after commas in method definitions/calls, the assignment operation =, and a large etc.
• Wrapping and braces: similar to the previous one, but in this case wrapping the lines, for control structures curly braces, method calls, etc.
• Blank lines: here we can configure the number of blank lines between different “sections” of the code, for both the minimum blank lines to apply, and for the maximum lines to keep. We can, for example, the configure the number of lines for before and after the imports, the lines between each method declaration, after the class header, etc.
• JavaDoc: for the code documentation. For setting the options for the alignment of the parameter descriptions and blank lines, among others.
• Arrangement: here there are two different sections:
  • Grouping rules: how to make groups. We can configure to set together the getters and setters, the overridden methods, and the dependent methods.
  • Matching rules: for ordering the fields, methods, etc. depending on their modifiers. An example is shown in the 2nd image.

This means that, if we reformat the code with this options, inside the class, in the first “position” would go the fields matching public, static and final modifiers; in the second, the fields matching protected, static and final; and so on.

For rearranging the code, the code formatting shortcut won’t work; we have to select “Code/Rearrange code” option. So, for example, if we would have the following code:

Enums vs Constants

Suppose you use constant strings (or int values - the same goes for them):

// Constants for player types
public static final String ARCHER = "Archer";
public static final String WARRIOR = "Warrior";

// Constants for genders
public static final String MALE = "Male";
public static final String FEMALE = "Female";

then you end up not really knowing the type of your data - leading to potentially incorrect code:

String playerType = Constants.MALE;

If you use enums, that would end up as:

// Compile-time error - incompatible types!
PlayerType playerType = Gender.MALE;

Likewise, enums give a restricted set of values:

String playerType = "Fred"; // Hang on, that's not one we know about...

vs

PlayerType playerType = "Fred"; // Nope, that doesn't work. Bang!

Additionally, enums in Java can have more information associated with them, and can also have behaviour. Much better all round.

Method Overloading

Method Overloading:

 Method overloading is a feature that allows s to have more than one method with the same name , so as long as we use different parameters.   

Example:

    we have to create a method which can do:

   a. The sum of two numbers

   b.  The sum of three numbers

   c.  The sum of four numbers

Advantages 

 1. it improves code readability.

 2. it improves code re-usability

 3. it's easier to remember one method name instead of multiple method name.

 4. it achieves  consistency in naming . One name for different methods that are commonly used.

 5. Overloaded methods gives programmers the flexibility to call  similar method with different types of data.

   

  

Each method would have parameters passed to it with the numbers to sum.

     1 inch = 2.54 centimeters

     1 foot  = 12 inch

Program:

public class MethodOverloading {

public static void main(String[] args) {

int calculateScore = calculateScore("Pankaj kumar", 70);

System.out.println("Points = " + calculateScore);

calculateScore(50);

}

public static int calculateScore(String playerName, int score) {

System.out.println("Player name : " + playerName + " score : " + score + " points ");

return score * 1000;

}

public static int calculateScore(int score) {

System.out.println("Unnamed Player name  score : " + score + " points ");

return score * 1000;

}

}

Program:

public class FeelAndInches {

public static void main(String[] args) {

calFeetAndInches(6, 0);

calFeetAndInches(7, 5);

calFeetAndInches(-10, 0);

calFeetAndInches(1, 1);

calFeetAndInches(100);

}

public static double calFeetAndInches(double feet, double inches) {

if ((feet < 0) || ((inches < 0) && (inches > 12))) {

System.out.println("Invalid feet || inches");

return -1;

}

double centimeters = (feet * 12) * 2.54;

centimeters += inches * 2.54;

System.out.println(feet + " feet " + inches + " inches " + centimeters + " centimeters ");

return centimeters;

}

public static double calFeetAndInches(double inches) {

if (inches < 0)

return -1;

double feet = (int) inches / 12;

double remaningInInches = (int) inches % 12;

System.out.println(inches + " inches is equal to " + feet + " feet and " + remaningInInches + " inches ");

return calFeetAndInches(feet, inches);

}

}

program:

public class SecondsAndMinutes {

public static void main(String[] args) {

getDurationString(65, 45);

// System.out.println(3945L);

}

public static String getDurationString(long minute, long seconds) {

if ((minute < 0) || (seconds < 0) || (seconds > 59)) {

return "Invalid input";

}

long hours = minute / 60;

long remainingMinutes = minute % 60;

return hours + " h " + remainingMinutes + " m " + seconds + " s";

}

public static String getDurationString(long seconds) {

if (seconds < 0)

return "Invalid input";

long minutes = seconds / 60;

long remainingSeconds = seconds % 60;

return getDurationString(minutes, seconds);

}

}