JDBC - Java Database Connectivity

a micro tutorial for beginners - Copy and Paste the below highlighted code block into Spring STS

what we will be building, a fully functional, embedded SQLite database right inside ur java app

heads-up..this is not meant to be a blow by blow tutorial with images..it is just basic guidance to provide a kick start, then u have to do much more research on each topic if u want to advance ur skills. The format is a java file with comments as the instructions. Drop the below Code Block into Eclipse or preferably Spring STS and run the JDBC class with its Main method.

pre requisites - windows platform, basic knowledge of Eclipse, JDK and Jar file dependencies. Ability to run a java app using main method. Absent any of this knowledge go to my tutorial 1 on how to get started with Java....... also MUST download and Install SQLite Driver (see below)

optionally and highly recommended, the cmd command line version of sqlite can be found in my tutorial #4, so ur options are to

1. just run this below program in Eclipse. If u want to avoid a Tomcat install then run this program in Spring STS 3.9.17 instead.

and/or

2. once db is embedded, after running below program, manually manipulate it from the program folder following tutorial 4 instructions

what to expect when running this app

table is created, sample data is inserted and browser popup displays the table results (my default browser is Google Chrome)

now for the code, copy below java class into Eclipse and run, see below for instructions which also include installing the jdbc driver. This code will be more readable once u are on ur desktop in Eclipse. Also note that because u are copying and pasting html, the code will be double spaced in Eclipse. After ur initial run u can edit text formatting inside Eclipse as u wish, for a better read.

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

INSTRUCTIONS

open eclipse

first create project

right click project, then click new, choose class, name package test, name class JDBC, press finish then new java file pops up and u will see JDBC.java inside the test package in the java resources/src folder.

erase current contents of jdbc.java file and replace with a copy of above block of code

then press file save

if u were to run this java file now an error will appear saying unable to load driver, classNotFoundException

this is because we need to include a dependency in our program. We need to import the SQLite Jar file into the project (also known as a library file). See Download and Install Instructions below

after installing the driver we can right click on JDBC.java, then run as a java application and our test4 data base file will be created with a table called test_table3....and a browser will appear with table contents

at this point we need to find the path for our newly created sqlite db

my windows path is

C:\Users\rickd\eclipse-workspace\JDBC Example.....u need to navigate to ur windows path

test4.db appears embedded right into this path. It is also possible to manually manipulate the data from a cmd prompt by dropping sqlite3 into this path. To do this, see my tutorial 4 command line instructions. I highly recommend doing this part in addition to the java piece because by entering manual commands u can learn a lot about SQL

instructions for jar file dependency

Download the sqlite jar file from this link

click on

sqlite/sqlite-jdbc-3.7.2.jar.zip( 3,176 k)

x out of any ad that might appear

then go to downloads folder, open the zip file and copy

sqlite-jdbc-3.7.2 to a convenient location in ur local file system

I copied mine to my windows desktop

note: if u are using eclipse maven plugin we add this jar dependency to the POM.xml file but we are not doing Maven here, we are doing this the old school way to get u familiarized with manually loading jar file dependencies in our project

next we need to install this jar file into our project

Right click on the project name, choose properties then click on java build path, then libraries tab. Here we will press add external jars button so we can include our new jar file into the project. Then navigate to where we stored the sqlite jar file, highlite the jar file and press open and then press apply and close. The library is now part of our project and it appears under referenced libraries in project explorer.

u are now good to go

next up, web enable this program as a Java Servlet and understand why Java Servlet is important to know. Also for this servlet exercise we provide download instructions for Spring STS (includes Tomcat) which I prefer over Eclipse so we avoid a challenging separate Tomcat install. Enable the above JDBC tutorial as a web page so it can be shared. Run a java servlet hosted on AWS.


go to howtolearnJava Homepage

Ideally, u would also want to learn the java servlet spec so u can host ur jdbc on a website (a public url). My free tutorials explain how to do this. Click on the tutorial tab at my homepage for more or better yet, click the above java servlet link.


for additional information on SQLite visit the SQLite homepage here.


for additional information on JDBC, visit this friendly site here. Since my tutorial is only a micro tutorial, it is best to do some other research too. Thanks for reading.