Java MS Access database connectivity Follow these steps

Java MS Access database connectivity Follow these steps: 1)Go to the start->Control Panel->Administrative Tools> data sources. 2)Click Add button and select the driver Microsoft Access Driver(*. mdb). 3)After selecting the driver, click finish button. 4)Then give Data Source Name and click ok button. 5)Your DSN will get created. 6) Restart your compiler and compile your java code.

DBC Steps to be followed while writing JDBC program: �Loading Driver �Establishing Connection �Executing Statements �Getting Results �Closing Database Connection

Loading Driver �Loading Database driver is very first step towards making JDBC connectivity with the database. It is necessary to load the JDBC drivers before attempting to connect to the database. The JDBC drivers automatically register themselves with the JDBC system when loaded. Here is the code for loading the JDBC driver: Class. for. Name(driver). new. Instance();

Establishing Connection �In the above step we have loaded the database driver to be used. Now its time to make the connection with the database server. In the Establishing Connection step we will logon to the database with user name and password. Following code we have used to make the connection with the database: con = Driver. Manager. get. Connection(url+db, user, p ass);

Executing Statements �In the previous step we established the connection with the database, now its time to execute query against database. You can run any type of query against database to perform database operations. In this example we will select all the rows from employee table. Here is the code that actually execute the statements against database: Result. Set rs = st. execute. Query( "SELECT * FROM Hi" );

Getting Results �In this step we receives the result of execute statement. In this case we will fetch the employees records from the recordset object and show on the console. Here is the code: while (rs. next()) { String Name = res. get. Int( " name " ); System. out. println( Name ); }

Closing Database Connection �Finally it is necessary to disconnect from the database and release resources being used. If you don’t close the connection then in the production environment your application will fail due to hanging database connections. Here is the code for disconnecting the application from database: con. close();

Ms Access

�import java. sql. *; �class Search{ public static void main(String[] args){ � try{ � Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Dri ver"); � Connection con = Driver. Manager. get. Connection("jdbc: odbc: Data"); � Statement st=con. create. Statement(); � Result. Set rs=st. execute. Query("select * from Hi"); � �

�while(rs. next()){ System. out. println(rs. get. String("ID")+" "+rs. get. String("Name")); � } � catch(Exception e){ � System. out. println(e); � } �} �

Insert �import java. sql. *; �class Java. Application 1{ public static void main(String[] args){ � try{ � Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); � Connection con = Driver. Manager. get. Connection("jdbc: odbc: Data"); � Statement st=con. create. Statement(); � int ID=5; � String Name ="Meena"; �

� int i=st. execute. Update("insert into Hi(ID, Name) values('"+ ID +"', '"+ Name +"')"); � System. out. println("Row is added"); Result. Set rs=st. execute. Query("select * from Hi"); � while(rs. next()){ � System. out. println(rs. get. String("ID")+" "+rs. get. String("Name")); � } � catch(Exception e){ � System. out. println(e); � } �}

Update �import java. sql. *; �class Update{ public static void main(String[] args){ � try{ � Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Drive r"); � Connection con = Driver. Manager. get. Connection("jdbc: odbc: Data"); � Statement st=con. create. Statement(); � �

� int i=st. execute. Update("update Hi set name='Reena' where ID = 3"); � System. out. println("Row is updated"); � � Result. Set rs=st. execute. Query("select * from Hi"); � while(rs. next()){ � System. out. println(rs. get. String("ID")+" "+rs. get. String("Name")); � } � catch(Exception e){ � System. out. println(e); � } �}

Delete �import java. sql. *; �class Delete{ � public static void main(String[] args){ � try{ � Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); � Connection con = Driver. Manager. get. Connection("jdbc: odbc: Data"); � Statement st=con. create. Statement(); � � int i=st. execute. Update("delete from Hi where id=3"); � System. out. println("Row is deleted");

� Result. Set rs=st. execute. Query("select * from Hi"); while(rs. next()){ � System. out. println(rs. get. String("ID")+" "+rs. get. String("Name")); � } � catch(Exception e){ � System. out. println(e); � } �} �

Difference between Statement & Prepared. Statement There are four steps for the execution of query : • Query is parsed, • Query is compile, • Query is optimized and • Query is executed. In case of statement interface these four steps are performed , each time when query is submitted for execution. But in case of prepared statement first three steps are performed only once, when the query in initially submitted. Only the last step is performed each time query is submitted(in subsequence submissions), i. e if same query is submitted to be execute with different values multiple times then prepared statement interface provides better perfromance as compared to statement interface. *

• Comparing with the execution control of Statement – 1) execute. XXX() method is invoked on the SQL statement execute. Update(), execute. Query() 2) The Statement object submits SQL Statement to DB 3) The DB compiles the given SQL statement 4) An execution plan is prepared by DB to execute the statement 5) Execution plan is then executed. If SQL contains SELECT, the results are cached in buffer– Results are sent to the Statement object 6) Finally, response is sent to java application execute. Update() methods

Normal statement execution –Compilation includes syntax check, name validation etc. –After validation, query optimizer prepares execution plan • Returns best execution plan • SQL statement goes thru above every time it is executed • If same query is executed multiple times, – Then execution plan can be saved and reuse it– This stored execution plan is known as pre-compiled statement • The Prepared. Statement interface is designed for it –Hence, their execution is much faster compared to statement

when using Prepared. Statement –Must be associated with one connection –It represents the execution plan, need to pass parameters –On connection close, it implicitly gets closed –Execution of query is processed as follows The connection object submits the SQL statement todatabase Database compiles the given statement • Execution plan is prepared by the database • Database returns the execution plan with unique id–Connection object– The set. XXX() methods are used to set the parameters of SQL– execute. XXX () method is invoked to execute the SQL –Database executes the execution plan with supplied parameters– Finall the result is sent to the java application

Advantages –Improves the application performance compared to Statement() –Inserts or updates SQL 99 data types • CLOB, BLOB –Provides a programming approach to set the values • Disadvantages –Can represent only one SQL statement at a time –Can not execute more than one SQL statement in a single Prepared. Statement • Situations when it is useful to use –A single query is executed multiple times –When a query consists of numerous parameters and complex types

Preapre Statement Example � import java. sql. *; � public class Prepare { � static private final String driver = "sun. jdbc. odbc. Jdbc. Odbc. Driver"; � static private final String connection = "jdbc: odbc: emp"; � public static void main(String args[]) { � Connection con = null; � Prepared. Statement pst = null; � Result. Set rs = null;

try { Class. for. Name(driver); con = Driver. Manager. get. Connection(connection); con. set. Auto. Commit(false); String sql = "insert into Employees (ID, First. Name, Last. Name) values(? , ? )"; pst = con. prepare. Statement(sql); pst. set. Int(1, 4); pst. set. String(2, "DDD"); pst. set. String(3, "JOSHI");

pst. execute. Update(); pst. close(); sql = "select * from Employees"; pst = con. prepare. Statement(sql); rs = pst. execute. Query(); while (rs. next()) { System. out. print(rs. get. String(1) + "t"); System. out. print(rs. get. String(2) + "t"); System. out. println(rs. get. String(3)); }

rs. close(); pst. close(); con. close(); } catch (Exception e) { } } }

Preapre Statement Update Example � import java. sql. *; � public class Update. Pre { � static private final String driver = "sun. jdbc. odbc. Jdbc. Odbc. Driver"; � static private final String connection = "jdbc: odbc: emp"; � public static void main(String args[]) { � Connection con = null; � Prepared. Statement pst = null; � Result. Set rs = null;

� try { � Class. for. Name(driver); � con = Driver. Manager. get. Connection(connection); � con. set. Auto. Commit(false); � � � � String sql = "update Employees set Last. Name = ? where id =? "; pst = con. prepare. Statement(sql); pst. set. String(1, "DAVE"); pst. set. Int(2, 2); pst. execute. Update(); pst. close(); // System. out. println("updated"); sql = "select * from Employees";

� pst = con. prepare. Statement(sql); � rs = pst. execute. Query(); � � � � � while (rs. next()) { System. out. print(rs. get. String(1) + "t"); System. out. print(rs. get. String(2) + "t"); System. out. println(rs. get. String(3)); } rs. close(); pst. close(); con. close(); � } catch (Exception e) { � } �} �

JDBC with My. SQL �To connect java application to My. SQL database we must have at least one database created in My. SQL. �And to create database in My. SQL, it should be installed on your system. �So first of all install My. SQL database in your system. �After installing it open My. SQL console.

�Create database with syntax create databasename; and press enter. �You can see message like Query OK, 1 row affected (0. 03 sec) �Now our database is created in My. SQL. Second step is to create table in our database.

�For creating table in particular database type Use databasename; and press enter and you can see message like database changed. �Now for creating table type create tablename (field 1 type of field 1, field 2 type of field 2, field 3 type of field 3); �Now press enter again you can see the message like Query OK, 0 row affected (0. 01 sec).

�EX: �Create database java; �Use java; �Create table data (id int, name char(20), city char(20), age int); �Now the next step is to insert data into our table.

� For inserting data simply type insert into table name (field 1, field 2, field 3) values (value 1, value 2, value 3); � EX: � insert into data (id, name, city, age) values (1, “ROMA", ”AHM", 30); � So by that’s way you can insert as many data as you want in your table. Now for viewing our data from table just type � select * from tablename; � EX: � select * from data;

� String which we are writing in Class. for. Name("com. mysql. jdbc. Driver"); to load the driver. � String which we are writing in Connection con = Driver. Manager. get. Connection("jdbc: mysql: //localhost: 3 306/java", "root", ”Root") to create connection with particular database. � Here the string jdbc: mysql: //localhost: 3306 is for connecting My. SQL to JDBC in our local system and the name /java is our database name and 1 st "root" is username of My. SQL and 2 nd “Root" is password of My. SQL.

�Here no need of Data Source Name as in access but one jar file named mysql-connector-java-5. 1. 6 -bin. jar must be loded in your java IDE. � For adding jar file simply right click on your project and select Set configuration Customize library and you can see window like this. �Select option add JAR/Folder.

JDBC � import java. sql. *; � public class My. Sql. Demo 1 �{ � public static void main(String[] args) � { � try � { � Class. for. Name("com. mysql. jdbc. Driver"); � System. out. println("Driver is loaded"); � Connection c = Driver. Manager. get. Connection("jdbc: mysql: //localhost: 3306/java", "root", "Root"); � System. out. println("Connection created"); �

� Statement s = c. create. Statement(); � Result. Set rs = s. execute. Query("select * from data"); � System. out. println("IDt. Namet. Cityt. Age"); � � while(rs. next()) // Retrieve data from Result. Set � { � System. out. println(rs. get. String(1)+"t"+rs. get. String(2)+"t"+rs. get String(3)+"t"+rs. get. String(4)); � } � � � � } catch(Exception e) { System. out. println("Exception : " +e); } }}

Stored procedure in My. Sql � Now we all know that for Callable. Statement first of all we must have stored procedure in our database. � Now how can we create stored procedure in My. SQL. � For that follow the steps given below. � We already create a database in My. SQL now for creating stored procedure for that particular database � Use java; (use databasename; ) � Example stored procedure for addition of two numbers.

�DELIMITER $$ CREATE PROCEDURE `addproc`( IN a INT, IN b INT, OUT c INT) BEGIN SET c = a + b; END$$

�You can get message like Query OK, 0 rows affected (0. 00 sec) �It means your stored procedure is created successfully. �Here we create stored procedure for add two int number. �The stored procedure has 2 IN parameter and 1 OUT parameter so total 3 parameters

JDBC program for Callable � mport java. sql. *; � public class Mysql_Callable. Demo �{ � public static void main(String[] args) � { � try � { � Class. for. Name("com. mysql. jdbc. Driver"); � System. out. println("Driver is loaded"); � Connection c = Driver. Manager. get. Connection("jdbc: mysql: //localhost: 3306/ java", "root", "Root"); � System. out. println("Connection created");

� String q = "call addproc(? , ? )"; � Callable. Statement cs = c. prepare. Call(q); � cs. set. Int(1, 10); � cs. set. Int(2, 20); � cs. register. Out. Parameter(3, Types. INTEGER); � cs. execute(); � int add = cs. get. Int(3); � System. out. println("addition = "+add); � } � catch(Exception e) � { � System. out. println("Exception : " +e); � } �}

Using finally() � import java. sql. *; � public class Mydemo �{ � public static void main(String[] args) throws SQLException � { � Connection c = null; � try � { � Class. for. Name("com. mysql. jdbc. Driver"); � System. out. println("Driver is loaded"); � c= Driver. Manager. get. Connection("jdbc: mysql: //localhost: 3306/java", "root", " Root"); � System. out. println("Connection created"); � Statement s = c. create. Statement();

� Result. Set rs = s. execute. Query("select * from data"); � System. out. println("IDt. Namet. Cityt. Age"); � � while(rs. next()) // Retrieve data from Result. Set � { � System. out. println(rs. get. String(1)+"t"+rs. get. String(2)+"t"+rs. get. String(3)+"t"+rs. get. String(4)); � } � � � } catch(Exception e) { System. out. println("Exception : " +e); } finally { c. close(); } }}