JDBC - Java Database Connectivity

package test;

import java.sql.*; //this import contains the jdbc classes and methods

import java.io.File; //these files are from the java io package which we need to create an html file

import java.io.FileWriter;

import java.io.IOException;

import java.io.PrintWriter;

import java.net.*; //need this package for url connection to uri

import java.awt.Desktop; //need this so we can open url/uri html file

/* JDBC micro tutorial for beginners...connect to data using SQLite

* this program will create, embed and display sqlite table

* be sure u add sqlite driver to ur build path..see separate instructions for this

*

* jdbc objects used

* connection object = cn

* driver manager object....get connection is the driver manager method

* prepared statement - select, create, insert, update (not covered here)

* result set object

* print writer object

* for loop array

*

*/

public class JDBC {

//declaring class variables

//create a connection object from java.sql package imported above

public static Connection cn = null;

//declare static variables

public static String col1,col2,col3;

public static void main(String[] args) {

//declare method variables

Statement stmt;

PreparedStatement stmt2;

ResultSet rs;

PrintWriter pw;

//create a jdbc driver instance..be sure sqlite jar file is in build path

loadDriver(); //this refers to the load driver method below...ie, run method below then proceed with try block

//now create a table in test4.db

try {

stmt = cn.createStatement();

String sql = "CREATE TABLE IF NOT EXISTS test_table3"

+ "("

+ " column1 TEXT," // in other dbs we use column, datatype, length, null or not null, primary key

+ " column2 TEXT,"

+ " column3 INTEGER" //i left this as integer to show below that it also takes a string

+ ")";

//note that for sqlite, data types are really not needed (sqlite assumes txt is used)

//but for other sql dbs like MySQL or PostgreSQL we need to use type syntax

stmt.executeUpdate(sql);

stmt.close();

//now insert some data

//declare more variables

String sql2;

int i; //each of 5 rows

int j=1;

String value;

//for loop to insert 5 rows of data

for(i = 0; i < 5; i ++){

//code to insert 1 row of data to sqlite database..for each pass insert a row....do 5 passes then loop stops

stmt2 = cn.prepareStatement("insert into test_table3 (column1,column2,column3) values (?,?,?)");

stmt2.setInt(1,j); //note that we setInt instead of set string, but as i said above sqlite assumes txt

//I am setting int here just as a reminder if u decide to use mysql or other dbs

stmt2.setString(2,"val2");

stmt2.setString(3,"value"+ String.valueOf(j)); //concatenation of of a string +string val of an integer

int result3 = stmt2.executeUpdate();

j = j+1; //increment integer by 1 for each pass

//note: for other insertions of data we can also read from an existing file with double for loop

//not covered here

} //end of for loop

System.out.println("table created and data inserted");

//create pw to write result to html

pw = new PrintWriter(new FileWriter("C:/Users/rickd/Desktop/files/html_table.html")); //my local path to html file

//format some html and pass to filewriter which will write html file to above path

//******very important...use ur own local path not mine

//first create table html tag and table header with border

pw.println("<TABLE BORDER><TR><TH>ID<TH>some value<TH>some other value</TR>");

// then get results

rs = stmt.executeQuery("Select * from test_table3");

//String col1,col2,col3; must declare above as static, when using main method

//while loop....while we have records, pass thru each one

while(rs.next()) {

//assign 'get from db' to local strings col1, col2 and col3

col1 = rs.getString("column1");

col2 = rs.getString("column2");

col3 = rs.getString("column3");

//for each pass append each line to html table

pw.println("<TR><TD>" + col1 + "<TD>" + col2 + "<TD>" + col3);

System.out.println(col1 +col2 +col3); //display result in console

} //end of while loop

pw.println("</TABLE>"); //closing tag for html table

pw.close(); //close print writer when we are done

System.out.println("Web page saved");

//display html table in addition to seeing result in console

String url2 = "C:/Users/rickd/Desktop/files/html_table.html";

File htmlFile = new File(url2);

Desktop.getDesktop().browse(htmlFile.toURI()); //pass local file to uri method, opens browser automatically

//note u may get NonCelloThread warning (with chrome browser) because this is a file system request

//the browser should still open in this event..but if u have problem let me know

//u can also manually open the html table file on whatever path u saved it

} //end of try block

// catch any exceptions occurring from sql query

catch (SQLException e) {

System.out.println(" Exceptions : " + e.toString());

} //end catch block

//also need to catch io exception because pw is in io package

catch (IOException e) {

System.out.println("An I/O error occurs: " + e.getMessage());

} //end catch block

} //end main method

//load driver method

public static void loadDriver() {

try {

Class.forName("org.sqlite.JDBC");

System.out.println("The Driver has been loaded successfully!");

} catch (Exception E1) {

System.out.println("Unable to load the Driver!");

System.out.println("Exceptions:" + E1.toString());

System.exit(1);

}

System.out.println("Establishing connection to Server");

//Establishing the connection with the test4.db database

try {

cn = DriverManager.getConnection("jdbc:sqlite:test4.db");

System.out.println("Connection to Server was Established");

} catch (Exception E2) {

System.out.println(" Exceptions : " + E2.toString());

}

} //end load driver method

} //end of class