Java Database Derby : JDBC to Derby connectivity

Apache Derby is an open source relational database implemented entirely in Java and has some key advantages include:
  • Derby has a small footprint — about 2.6 megabytes for the base engine and embedded JDBC driver.
  • Derby is based on the Java, JDBC, and SQL standards.
  • Derby provides an embedded JDBC driver
  • Derby is easy to install, deploy, and use.
So without wasting time lets start
IN NETBEANS
Derby
  • Steps To Create Derby Database and Table
  1. First goto Windows => Services
  2. Navigate to the Databases section and click Java DB
  3. Right click and say Start Server
  4. Now create a database by again right clicking on Java DB ‘create database’
  5. Enter your database name , user-name and your password
  6. now you would see an active connection in the Databases column such as jdbc:derby://localhost:1527/meraDB [swapnil on SWAPNIL]
  7. Right click connect
  8. create a table by Right clicking on database connection you have established by clicking Execute Command
 
CREATE TABLE USERS (
FIRST_NAME VARCHAR(30) NOT NULL,
LAST_NAME VARCHAR(30) NOT NULL,
EMP_NO INTEGER NOT NULL CONSTRAINT EMP_NO_PK PRIMARY KEY
);
 
 
CREATE TABLE PC (
TYPE VARCHAR(10) NOT NULL,
SERIAL VARCHAR(50),
OS VARCHAR(20),
EMP_NO INTEGER,
CODE_NO INTEGER NOT NULL CONSTRAINT CODE_NO_PK PRIMARY KEY
);
 
Insert some values like this
INSERT INTO USERS VALUES('Bill','Gates',1);
INSERT INTO USERS VALUES('Joe','Bloggs',2);
INSERT INTO USERS VALUES('Peter','Kropotkin',3);
INSERT INTO PC VALUES('Desktop','01010','Linux',1,1);
INSERT INTO PC VALUES('Laptop','101010','BSD',2,2);
INSERT INTO PC VALUES('Desktop','101010','XP',3,12);
 
Now you are ready to jdbc ! Include the derbyclient.jar in your library / classpath by right clicking library in project and Add jar     Derby Library import in Netbeans  
package nik;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSetMetaData;

public class Derby {
private static String dbName = "meraDB";
private static String dbUser = "swapnil";
private static String dbPass = "redhat";

private static String dbURL ="jdbc:derby://localhost:1527/"+dbName+";create=true;user="+dbUser+";password="+dbPass;
private static String tableName = "USERS";
// jdbc Connection
private static Connection conn = null;
private static Statement stmt = null;

public static void main(String[] args)
{
createConnection();
selectInput();
//insertUser("LaVals", "Berkeley",9);
selectUsers();
shutdown();
}

private static void createConnection()
{
try
{
Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
//Get a connection
conn = DriverManager.getConnection(dbURL);
}
catch (Exception except)
{
except.printStackTrace();
}
}

private static void insertUser( String firstName, String lastName,int emp_no)
{
try
{
stmt = conn.createStatement();
stmt.execute("insert into " + tableName + " values ('" + firstName + "','" + lastName + "'," +emp_no +")");
stmt.close();
}
catch(java.sql.SQLIntegrityConstraintViolationException insExp){
insertUser(firstName,lastName,++emp_no);
}
catch (SQLException sqlExcept)
{
sqlExcept.printStackTrace();
}
}

private static void selectUsers()
{
try
{
stmt = conn.createStatement();
ResultSet results = stmt.executeQuery("select * from " + tableName);
ResultSetMetaData rsmd = results.getMetaData();
int numberCols = rsmd.getColumnCount();
for (int i=1; i<=numberCols; i++)
{

//print Column Names
System.out.print(rsmd.getColumnLabel(i)+"\t\t");
}

System.out.println("\n-------------------------------------------------");

while(results.next())
{
int id = results.getInt(3);
String restName = results.getString(1);
String cityName = results.getString(2);
System.out.println(id + "\t\t" + restName + "\t\t" + cityName);
}
results.close();
stmt.close();
}
catch (SQLException sqlExcept)
{
sqlExcept.printStackTrace();
}
}

private static void shutdown()
{
try
{
if (stmt != null)
{
stmt.close();
}
if (conn != null)
{
DriverManager.getConnection(dbURL + ";shutdown=true");
conn.close();
}
}
catch (SQLException sqlExcept)
{
sqlExcept.printStackTrace();
}

}

private static void selectInput() {
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
try{
do{
System.out.println("Insert Employee First Name");
String fname = br.readLine();
System.out.println("Insert Employee Last Name");
String lname = br.readLine();
System.out.println("Insert Employee Private Code");
int emp_code = Integer.parseInt(br.readLine());
insertUser(fname, lname, emp_code);
System.out.println("Thankyou for Inserting please press any key to continue and small 'q' to exit");
char str =(char) br.read();
System.out.println(str);
if(str == 'q')break;
System.out.println("Press Enter to continue");
br.readLine();
}while(true);
}catch(Exception e){
e.printStackTrace();
}
}
}