DB Automation

So let’s start with DB Automation:

1.      Download ojdbc7.jar

      [More information around the ojdbc jars can be found on the Oracle site here.]

2.      Load the jar file. Can be done in following 2 ways:

1. Paste the ojdbc14.jar file in jre/lib/ext folder
2. Set classpath

3.      Now time to try to write the code.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

//STEP 1. Import required packages import java.sql.*; //STEP 2: Create class and variables public class rough { //JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String dbURL = "jdbc:mysql://localhost/xxx"; //Database credentials static final String un = "username"; static final String pwd = "password"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try{ //STEP 3: Register JDBC driver Class.forName(JDBC_DRIVER); //STEP 4: Open a connection System.out.println("Connecting to db ..."); conn = DriverManager.getConnection(dbURL, un, pwd); //STEP 5: Execute a query System.out.println("Creating statement..."); stmt = conn.createStatement(); String sql = "SELECT id, name FROM [your table name]"; ResultSet rs = stmt.executeQuery(sql); //STEP 6: Extract data from result set while(rs.next()){ //Retrieve by column name int id = rs.getInt("id"); //Retrieve by column index // Use this ONLY if you are sure of the index # String name = rs.getString(3); //Display values System.out.println("ID: " + id); System.out.println("Name: " + name); } //STEP 7: Clean-up environment rs.close(); stmt.close(); conn.close();

4.      This is how you can get the column name dynamically:

1 2 3 4 5 6

//NOTE: Column count starts from 1 for (int i = 1; i <= rsmd.getColumnCount(); i++) { ResultSetMetaData rsmd = rs.getMetaData(); String name = rsmd.getColumnName(i); }

5.      ResultSetMetaData methods can be found here (this is link to external site and you will be navigated away from newbies'sHelper blog).


Sample SQL queries.