Lecture 1. java database connectivity

Lecture 1. java database connectivity

• 1.
  Java Database Connectivity Nangarhar University Computer Science faculty Fall semester 2014 Waheedullah Sulaimankhail
• 2.
  Overview 1. JDBCIntroduction 2. JDBC Configuration 3. Practical Examples with JDBC
• 3.
  1. JDBC introduction  JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.  The JDBC library includes APIs for each of the tasks commonly associated with database usage
• 4.
  How JDBC works 1. Registering JDBC Driver 2. Making a connection to a database 3. Creating SQL or MySQL statements 4. Executing that SQL or MySQL queries in the database 5. Viewing & Modifying the resulting records
• 5.
  2. JDBC Configuration  We will use eclipse and Microsoft Sql Server  So please install Eclipse and Ms Sql Server
• 6.
  Configuration . Add Sql Server Path to environment variable.
• 7.
  Configuration .. Download JDBC driver for Ms Sqlserver  Download the sqljdbc_<version>_enu.exe to a temporary directory.  Run sqljdbc_<version>_enu.exe (unzip it)
• 8.
  Configuration … Copy sqljdbc_auth.dll  From this folder : …Microsoft JDBC Driver 4.0 for SQL Serversqljdbc_4.0enuauthx64  To C: windows / System32 folder
• 9.
  Add JDBC Driverfor Ms Sql Server to Project properties in Eclipse
• 10.
  3. Practical Exampleswith JDBC  Setting Up JDBC Examples  3 important steps to set up JDBC 1. Write the import statements  import java.sql.*; 2. Register JDBC Driver  Class.forName("com.microsoft.sqlserver.jdbc.SQL ServerDriver"); 3. Creating connection and opening it  Connection con = DriverManager.getConnection("jdbc:sqlserver://loc alhost:1433;databaseName=db_name;integrated Security=true","User","Pw");
• 11.
  Executing queries withJDBC  After jdbc is set up we need to write queries and execute them.  In sql server we create query and execute them.  However in JDBC we need to create a statement and initialize the statement with object of connection class.  And execute the query with an object of statement class.
• 12.
  Create statement andexecute create table query  Statement st = con.createStatement();  String query = "CREATE TABLE student " + "(id INTEGER not NULL, " + " name VARCHAR(255), " + " fname VARCHAR(255), " + " grade Varchar(255), " + " PRIMARY KEY ( id ))" ;  St.executeUpdate(query);  System.out.println(“New Table with name student is created ”);
• 13.
  Creating Select Queryand executing  Statement st= con.createStatement();  String query= "SELECT id, first, last, age FROM Employee";  ….. = st.executeQuery(query);  executeQuery method returns a dataset so we need to store the outcome in a dataset  We will rewrite the executeQuery method as above  ResultSet rs = st.executeQuery(query);  Now we need to fetch the data from our result set
• 14.
  Fetching data fromdata set  Rs.next() // will fetch the next record (next row)  To get each record we will run a while loop till the last record is reached  While(rs.next()) {  int id = rs.getInt("id"); // this will fetch the column named “id”  // the same for all other columns which we want to fetch from this record  System.out.println(“ID :”+id); // show the record  }
• 15.
  Clearing the Environment  Rs.close();  St.close();  Con.close();
• 16.
  Thanks for attention Download these slides from : https://app.box.com/s/62nb671lj6vsz23ylw7v