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

Hibernate Examples - Native SQL Queries

You can also express queries in the native SQL dialect of your database. This is useful if you want to utilize database-specific features such as query hints or the CONNECT keyword in Oracle. It also provides a clean migration path from a direct SQL/JDBC based application to Hibernate.

Hibernate3 allows you to specify handwritten SQL, including stored procedures, for all create, update, delete, and load operations.

To know more, click here.

For example - we can have methods in DAO for executing Native SQL as shown below.

public List executeNativeQuery(String query) {
return getSession().createSQLQuery(query).list();
}

public List executeQuery(String query) {
return getSession().createQuery(query).list();
}

You can pass native SQL query as shown below -

List allObjects = empDao.executeNativeQuery("Select emp_id, emp_name  from EMPLOYEE");

Iterator it = allObjects.iterator();
while(it.hasNext())
{
Object row[] = (Object[])it.next();
for(Object eachRow : row) {
System.out.println(eachRow.toString());
}
}

Below are the known errors and exception -

1. Exception in thread "main" org.hibernate.MappingException: No Dialect mapping for JDBC type: -1
at org.hibernate.dialect.TypeNames.get(TypeNames.java:79)
at org.hibernate.dialect.TypeNames.get(TypeNames.java:104)
at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:393)
at org.hibernate.loader.custom.CustomLoader$Metadata.getHibernateType(CustomLoader.java:582)
at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.performDiscovery(CustomLoader.java:508)
at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:524)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1821)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2232)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
at org.hibernate.loader.Loader.list(Loader.java:2124)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1723)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)


Solution - Above exception occurs if you pass * to fetch all columns in SQL like - "Select * from EMPLOYEE". this should be avoided.

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

);

MS SQL / Oracle : Get all Table / Column names from database

MS SQL

Get all table names of particular database
Query: select NAME from SYSOBJECTS where TYPE = 'U'

Get all column names of a table
Query: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = '<table name>'

Oracle

Get all table names of particular database
Query: select TABLE_NAME from DBA_TABLES where OWNER like '<database name>'

Get all column names of a table
Query: select COLUMN_NAME from ALL_TAB_COLS where TABLE_NAME ='<table name>'

MS SQL: Join tables of different database

Assume you have two database, Db1 and Db2.

Employee table is in Db1 and Department in Db2

Query:

select emp_name, dept_name from Db1.dbo.Employee a, Db2.dbo.Department b where a.dept_id = b.dept_id

Syntax is Database name followed by .dbo. followed by table name

eg. Db1.dbo.Employee

Similarly you can use above syntax for any type of query.

MS SQL: Get Day/Month of Date

1.This is simple query, which gives Date

select GETDATE()

output:

2009-07-11 19:56:18.543

2.This query give day and month

select DATENAME(dw,GETDATE()) as Day, DATENAME(mm,GETDATE()) as Month

output:

Saturday July

3:This query gives individual date, month, year

select DAY(GETDATE()) as Date,MONTH(GETDATE()) as Month,YEAR(GETDATE()) as Year

output:

11 7 2009

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();
}
}
}