Java @ Desk

Java JDBC SQL Update a record example
To insert a record in database in SQL using JDBC, you need to perform below mentioned steps:
1) Create the database connection. There are two ways to get the DB connection. One is through java.sql.DriverManager and another is through javax.sql.DataSource. Click on below links for specific configuration.
DriverManager DB connection
DataSource DB Connection

2) Create a PreparedStatement object using sql query:
PreparedStatement fielStatement = connection.prepareStatement(SQL_STRING);

3) Execute sql statement: This is done using execute() method of PreparedStatement object
fielStatement.execute();

Java JDBC SQL Sorting Column Order By Clause
Sorting a column is done using the Order By clause in SQL. Sorting a column can be done using following keyword:
1) Order By <COLUMN_NAME> asc - Display the result in ascending order for the <COLUMN_NAME>
2) Order By <COLUMN_NAME> desc - Display the result in descending order for the <COLUMN_NAME>

SELECT col_name FROM table_name ORDER BY col_name ASC:
SELECT col_name FROM table_name ORDER BY col_name DESC:

Consider the below table:
Customer

CustId	CustName
1	Cynthia
2	Thanh
3	Peter
4	Eric

Java JDBC SQL Delete example
To delete a record in database in SQL using JDBC, you need to perform below mentioned steps:
1) Create the database connection. There are two ways to get the DB connection. One is through java.sql.DriverManager and another is through javax.sql.DataSource. Click on below links for specific configuration.
DriverManager DB connection
DataSource DB Connection

2) Create a PreparedStatement object using sql query:
PreparedStatement fielStatement = connection.prepareStatement(SQL_STRING);

3) Execute sql statement: This is done using execute() method of PreparedStatement object
fielStatement.execute();

Java JDBC SQL Insert example
To insert a record in database in SQL using JDBC, you need to perform below mentioned steps:
1) Create the database connection. There are two ways to get the DB connection. One is through java.sql.DriverManager and another is through javax.sql.DataSource. Click on below links for specific configuration.
DriverManager DB connection
DataSource DB Connection

2) Create a PreparedStatement object using sql query:
PreparedStatement fielStatement = connection.prepareStatement(SQL_STRING);

3) Execute sql statement: This is done using execute() method of PreparedStatement object
fielStatement.execute();

Difference between Union and Union All

UNION The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type.

1) UNION removes duplicate records, UNION ALL does not
2) UNION is slower than UNION ALL since there is an additional tasks need to be performed to remove the duplicate rows i.e. UNION selects the distinct results
3) In order to remove duplicates the result set must be sorted, and this may have an impact on the performance of the UNION
4) UNION is used to select distinct values from two tables where as UNION ALL is used to select all values including duplicates from the tables

Different types of Inner Join are :
1) Equi Join
2) Natural Join
3) Cross Join

One of the most common interview questions that is being asked is explain Equi Join, Natural Join and Cross Join in sql. I will break the question in different meaningful easy to understand concepts.

Consider the below tables
Employee

EmpId	EmpName
1	John
2	David
3	Peter
4	Eric

One of the most common interview questions that is being asked is explain Inner Join, Right Outer Join, Left Outer Join, Full Outer Join, Self Join in Sql. I will break the question in different meaningful easy to understand concepts.

What is sql join?
Join, as the name suggests, is used to combine data from two tables based on some common features(columns). The resulting data is the temporary table created by comparing two tables.

Consider the below tables

SQL Delete Statement
It is a DML command
This command can be used to remove some or all rows from the table
After deletion, it does free the space containing the table
While deleting rows, it applies lock on each row
Where condition can be used
It deletes specified rows if where clause exists in the query
Rollback is possible
Triggers are fired
It is used to manipulate/modify the table data. It does not change the property of a table
Slower in performance since it maintains logs

JDBC Prepared Statement/PreparedStatement in java is used for execution of SQL statements. If you want to execute a Statement object many times, it usually reduces execution time to use a PreparedStatement object instead.

SQL query execution involves :
1) Parsing of SQL query string
2) Compilation
3) Execute the SQL query

JDBC Statement is used for execution of SQL statements. Statement interface provides basic method for SELECT, INSERT, UPDATE, DELETE operations in the database.

SQL query execution involves :
1) Parsing of SQL query string
2) Compilation
3) Execute the SQL query

Three types of statements in JDBC:

1. Statement(Click for implementation)
2. Prepared Statement(Click for implementation)
3. Callable Statement

SQL query execution involves :

1) Parsing of SQL query string
2) Compilation
3) Execute the SQL query

JNDI Datasource connection in JBOSS

1) Create a dataSource-ds.xml in $jboss_home/server/default/deploy folder and edit it:
2)
<datasources>
<local-tx-datasource>
<jndi-name>MyDSJndi</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/DATASERVICE </connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>userName</user-name>
<password>password</password>
</local-tx-datasource>
</datasources>

JDBC Connection steps:

1) Register a database driver: This is done in order to register which type of database drivers going to use.




2) Load database driver: This is done to get the connection object using DriverManager class



DriverManager uses the first two terms of the URL (jdbc:msql) to locate a suitable driver from among those loaded at initialization and those loaded explicitly using the same classloader as the current applet or application.