jdbc program

Database Access with JDBC

JDBC Introduction
• JDBC provides a standard library for accessing relational databases – API standardizes
• Way to establish connection to database
• Approach to initiating queries
• Method to create stored (parameterized) queries
• The data structure of query result (table) – Determining the number of columns – Looking up metadata, etc. – API does not standardize SQL syntax
• You send strings; JDBC is not embedded SQL
– JDBC classes are in the java.sql package
– JDBC stands for “Java DataBase Connectivity”
JDBC Drivers
• JDBC consists of two parts:
– JDBC API, a purely
Java-based API – JDBC Driver Manager,which communicates with vendor-specific drivers that perform the real communication with the database.
• Point: translation to vendor format is performed on the client – No changes needed to server – Driver (translator) needed on client jdbc program for connecitivity program1 for create a table in sql 5.5 database

//STEP 1. Import required packages import java.sql.*; class CreateProductTable { public static void main(java.lang.String[ ] args) { try { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection( url, "root", "00" ); Statement statement = con.createStatement(); String createProductTable = "CREATE TABLE PRODUCT1 " + "(NAME VARCHAR(64), " + "ID VARCHAR(32) NOT NULL, " + "PRICE FLOAT, " + "DESCrt VARCHAR(256), " + "PRIMARY KEY(ID))"; statement.executeUpdate( createProductTable ); } catch( Exception e ) { e.printStackTrace(); } } }

save it as CreateProductTable.java on location C:\Documents and Settings\amar\My Documents\jdbc> in my computer for compile command is javac CreateProductTable.java

C:\Documents and Settings\amar\My Documents\jdbc>JAVAC CreateProductTable.java

for run it

C:\Documents and Settings\amar\My Documents\jdbc>JAVA CreateProductTable C:\Documents and Settings\amar\My Documents\jdbc>

program2 inserting rows in to table using jdbc

//STEP 1. Import required packages import java.sql.*; class InsertProducts { public static void main(java.lang.String[ ] args) { try { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection( url, "root", "00" ); Statement statement = con.createStatement(); statement.executeUpdate("INSERT INTO PRODUCT1 " + "VALUES ( 'UML User Guide', " + "'8-201-57168-4', 47.99, 'The UML user guide')" ); statement.executeUpdate("INSERT INTO PRODUCT1 " + "VALUES ( 'Java Enterprise in a Nutshell', " + "'1-56592-483-5', 29.95, 'A good introduction to J2EE')" ); con.close(); statement.close(); }catch( Exception e ) { e.printStackTrace(); } } }

save it as InsertProducts.java on location C:\Documents and Settings\amar\My Documents\jdbc> in my computer for compile command is javac InsertProducts.java

C:\Documents and Settings\amar\My Documents\jdbc>JAVAC InsertProducts.java

for run it java InsertProducts

C:\Documents and Settings\amar\My Documents\jdbc>JAVA InsertProducts C:\Documents and Settings\amar\My Documents\jdbc>

program3.----------------------------- select or retrieve data from rows in to table using jdbc

//STEP 1. Import required packages import java.sql.*; public class SelectProducts { public static void main(String[] args) { try { Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection( "jdbc:mysql://localhost/TEST", "root", "00" ); Statement statement = con.createStatement( ); String sql; sql = "SELECT NAME, PRICE FROM PRODUCT"; ResultSet rs = statement.executeQuery("SELECT NAME, PRICE FROM PRODUCT1"); while ( rs.next( ) ) { String name = rs.getString( "NAME" ); String price = rs.getString( "PRICE" ); System.out.println("Name: "+name+", price: "+price); } statement.close( ); con.close( ); } catch( Exception e ) { e.printStackTrace( ); } } }

save it as SelectProducts.java on location C:\Documents and Settings\amar\My Documents\jdbc> in my computer for compile command is JAVAC SelectProducts.java

C:\Documents and Settings\amar\My Documents\jdbc>JAVAC InsertProducts.java

for run it java SelectProducts.java

C:\Documents and Settings\amar\My Documents\jdbc>JAVA SelectProducts Name: UML User Guide, price: 47.99 Name: Java Enterprise in a Nutshell, price: 29.95 Name: UML User Guide, price: 47.99 Name: UML User Guide, price: 47.99 Name: UML User Guide, price: 47.99 Name: UML User Guide, price: 47.99 C:\Documents and Settings\amar\My Documents\jdbc>