JDBC example using MS Access

Lets see a simple JDBC example using MS Access.

We are using below mentioned particulars

• MS Access 2007
• Java 1.6

Step 1. Create Database using MS Access

Create a New blank database, Name the file EmployeeMgmt.mdb and save it in your desire location. Please see the screen shot below.

Create the columns empId and empName in table employee as shown below. Also add few records which we will be fetching through Java code i.e JDBC.

JDBC Example – Batch Update using PreparedStatement

Using Batch Update feature of PreparedStatement you can Insert, Update or Delete multiple records with single database hit. For example if you want to insert multiple records into database using Batch Update you will just need to conect to database once. You can loop through records and add it to bach and once done you can execute the bach i.e insert all records in a single shot.


Below is a code snippet which shows how to insert few records in batch process, via JDBC PreparedStatement

PreparedStatement ps=con.prepareStatement("INSERT INTO BS_Books (cat_id,book_title,book_details,book_price,book_author) values (?,?,?,?,?)");

ps.setInt(1, 1);
ps.setString(2, "Book1");
ps.setString(3, "Details of book1");
ps.setDouble(4, 100.0);
ps.setString(5, "Author1");
ps.addBatch();//Adding record to the batch

ps.setInt(1, 1);
ps.setString(2, "Book2");
ps.setString(3, "Details of book2");
ps.setDouble(4, 150.0);
ps.setString(5, "Author2");
ps.addBatch();//Adding record to the batch

ps.executeBatch();//Executing the batch

Please see the complete self explanatory java code below

MySQL - JDBC example for BLOB storage

A blob (binary large object) is a collection of binary data stored as a single entity in a database. Blobs can be used to store images, audio or other multimedia files. There are four variation of blob datatype -

• TINYBLOB - Maximum length of 255 (2^8 - 1) characters i.e 25 bytes
• BLOB - Maximum length of 65535 (2^16 - 1) characters i.e 64 KB
• MEDIUMBLOB - Maximum length of 16777215 (2^24 - 1) characters i.e 16 MB
• LONGBLOB - Maximum length of 4294967295 (2^32 - 1) characters i.e 4GB

About the example: We will create a table in mySql and see how to insert/reterive an image using JDBC.

First lets create a table in MySQL. Please see the syntax below, we have used MEDIUMBLOB as it would be sufficient for medium size object stotage. You can use any other as per your requirement. We have just 3 columns pic_id as a auto increment primary key, pic_name will hold the picture name and pic_file a blob which will store actual picture.

CREATE TABLE blobtest (

pic_id INT NOT NULL AUTO_INCREMENT,

PRIMARY KEY (pic_id),

pic_name VARCHAR(100) NOT NULL,

pic_file MEDIUMBLOB

);

Why PreparedStatement is used to prevents SQL Injection?

What is a SQL Injection?

In simple language SQL injection is injecting malicious sql code into the application's sql that may help attacker to retrieve sensitive information like user name / passwords etc OR it can also be used by hackers for login without authentication.

For example, We have a human resource management system, where in employee logs in and can view his / her sensitive information like attendance, salary etc. To log on to system every employee requires username and password. Now suppose below function is used to do authentication.

private boolean isAuthenticate(String userName, String password) {

Statement stat = connection.createStatement();

String query = "SELECT 1 FROM EMPLOYEE WHERE USER_NAME  = '"+userName+"'  AND PASSWORD = '"+password+"';";

ResultSet rset = stat.executeQuery(query);

if (rset.next()) {
return true;
}
else {
return false;
}
}


if the above query fetches result then allow the user to enter into the system else don't allow. You might think that the above process is correct but it's not like that, the above function has a serious flaw. let's see how.

PHP - Database connectivity

Below are the 6 steps used for PHP database connectivity.

Step 1. Create a connection to the database using mysql_connect() function, which returns a connection object.

Syntax : $con = mysql_connect("localhost","root","password");

Step 2. Select database or schema using mysql_select_db() function

Syntax : mysql_select_db("database_name", $con);

Step 3. Execute the query using mysql_query() function. This function is used to send a query or command to a MySQL connection. It returns result object

Syntax : $result = mysql_query($sql);

Step 4. Looping through results. mysql_fetch_array() function is used to fetch a result row as an associative array, a numeric array, or both.

Syntax : $row = mysql_fetch_array($result)

Step 5. Printing the results. Simply printing the values from result array.

Syntax : echo $row['column1']." ".$row['column2'];

Step 6. Closing the connecting using mysql_close() function.

Syntax : mysql_close($con);


Please see the complete tested code, Save the page with .(dot) php extension e.g. ConnectDatabase.php. Provide correct credentials and see the output.

Java : JDBC Connection codes in Oracle and MS SQL

Oracle Connection

/* Here is complete code for OracleConnection.java */

import java.sql.*;

public class OracleConnection
{
public static void main(String[] args)
{
//connection
Connection con=null;
ResultSet rs=null;
Statement stmt=null;

try
{
Class.forName("oracle.jdbc.driver.OracleDriver"); //Step 1: Loading Drivers

con = DriverManager.getConnection("jdbc:oracle:thin:@10.10.10.10:8080:vas","username","password"); //Step 2: Making Connection

stmt = con.createStatement(); //Step 3: Creating JDBC Statement

String query = "Select * from EMPLOYEE";

rs = stmt.executeQuery(query); //Step 4: Execute the Ststement

while (rs.next()) //Step 5: Looping through the ResultSet
{
System.out.println(rs.getString(1)+""+rs.getString(2));
}

stmt.close(); //step 6: Close the Connection and Statement
con.close();
rs=null;
stmt=null;
con=null;
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}

MS SQL Connection

/* Here is complete code for MSSQLConnection.java */

import java.sql.*;

public class MSSQLConnection
{
public static void main(String[] args)
{
//connection
Connection con=null;
ResultSet rs=null;
Statement stmt=null;

try
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //Step 1: Loading Drivers

con = DriverManager.getConnection("jdbc:sqlserver://10.10.10.10:8080;databaseName=DBNAME;user=scott;password=tiger"); //Step 2: Making Connection

stmt = con.createStatement(); //Step 3: Creating JDBC Statement

String query = "Select * from EMPLOYEE";

rs = stmt.executeQuery(query); //Step 4: Execute the Ststement

while (rs.next()) //Step 5: Looping through the ResultSet
{
System.out.println(rs.getString(1)+""+rs.getString(2));
}

stmt.close(); //step 6: Close the Connection and Statement
con.close();
rs=null;
stmt=null;
con=null;
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}

Java : Java Database Connectivity (JDBC), Explained All four types with simple example

There are four types of JDBC drivers explained with simple examples using oracle database:

Type 1 : JDBC-ODBC bridge plus ODBC driver.

This driver translates JDBC method calls into ODBC function calls.
The Bridge implements Jdbc for any database for which an Odbc driver is available.

/********Code for Type1.java**********/

import java.sql.*;
public class Type1
{
public static void main(String[] args) throws Exception
{
Connection con;
Statement stat;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//Step 1: Loading Drivers

con=DriverManager.getConnection("jdbc:odbc:madan","scott","tiger");//Step 2: Making Connection

stat=con.createStatement();//Step 3: Creating JDBC Statement

String query = "SELECT * FROM Employee";

ResultSet rset=stat.executeQuery(query);//Step 4: Execute the Ststement

while(rset.next())//Step 5: Looping through the ResultSet
{
System.out.println(rset.getInt(1)+" "+rset.getString(2));
}
stat.close();//step 6: Close the Connection and Statement
con.close();
}
}

Type 2 : Native-API, partly Java driver.

Type 2 drivers use the Java Native Interface (JNI) to make calls to a local
database library API. This driver converts the JDBC calls into a database
specific call for databases such as SQL, ORACLE etc.

Note : to set path

set Classpath=%Classpath%;.;D:\oracle\ora92\jdbc\lib\Classes12.jar

/********Code for Type2.java**********/

import java.sql.*;
public class Type2
{
public static void main(String[] args) throws Exception
{
Connection con;
Statement stat;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//Step 1: Loading Drivers

con=DriverManager.getConnection("jdbc:oracle:oci8:@madan","scott","tiger");//Step 2: Making Connection

stat=con.createStatement();//Step 3: Creating JDBC Statement

String query = "SELECT * FROM Employee";

ResultSet rset=stat.executeQuery(query);//Step 4: Execute the Ststement

while(rset.next())//Step 5: Looping through the ResultSet
{
System.out.println(rset.getInt(1)+" "+rset.getString(2));
}
stat.close();//step 6: Close the Connection and Statement
con.close();
}
}

Type 3 : JDBC-Net, pure Java driver.

Type 3 drivers are pure Java drivers that uses a proprietary network protocol to
communicate with JDBC middleware on the server.Its requests are passed through the
network to the middle-tier server. The middle-tier then translates the request to the
database. The middle-tier server can in turn use Type1, Type 2 or Type 4 drivers.

Type 4 : Native-protocol, pure Java driver.


Type 4 drivers communicates directly with the database engine rather than through
middleware or a native library, they are usually the fastest JDBC drivers available.
This driver directly converts the java statements to SQL statements.

Note : to set path

set Classpath=%Classpath%;.;D:\oracle\ora92\jdbc\lib\Classes111.jar

/********Code for Type4.java**********/


import java.sql.*;
public class Type4
{
public static void main(String[] args) throws Exception
{
Connection con;
Statement stat;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//Step 1: Loading Drivers

con=DriverManager.getConnection("jdbc:oracle:thin:@madan:1521:oracle9","scott","tiger");//Step 2: Making Connection

stat=con.createStatement();//Step 3: Creating JDBC Statement

String query = "SELECT * FROM Employee";

ResultSet rset=stat.executeQuery(query);//Step 4: Execute the Ststement

while(rset.next())//Step 5: Looping through the ResultSet
{
System.out.println(rset.getInt(1)+" "+rset.getString(2));
}
stat.close();//step 6: Close the Connection and Statement
con.close();
}
}