Connection Oracle using Java

[besianhoxha]

Please, could anyone help me?

I cannot connect this code with Oracle database..

Can anyone to check if is there any problem in my code, and if the classes are correct?


=====================
Connect Oracle
-------------------------
import java.util.SQLTools;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;

public class ConnectOracle {
private static String executeQuery = null;
private String databaseType = null;

static {
executeQuery = "select * from studenti where viti_regjistrimit = ?";
}
public ConnectOracle() throws DatabaseException{
this("DatabaseOracle");
}

public ConnectOracle (String type) throws DatabaseException{
if(!(type.equalsIgnoreCase("DatabaseOracle")))
throw new DatabaseException("Databaza duhet te jete Oracle");
databaseType = type.toLowerCase();
}

private Connection getConnection() throws SQLException{
Connection connection = null;
if(databaseType.equals("DatabaseOracle"))
connection = getOracleConnection();
return connection;
}

private Connection getOracleConnection() throws SQLException {
Connection connection = null;
try{
String driver ="sun.jdbc.odbc.JdbcOdbcDriver"; //
String cs="jdbc:odbc:ConnectOracle"; //connectOracle is the data source name
String user = "shkparadite"; //username of oracle database
String pwd = "shkparadite"; //password of oracle database
Connection con = DriverManager.getConnection ("jdbc:odbc:ConnectOracle","shkparadite","shkparad ite");
con.setAutoCommit(false);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select* from FILIALET ");
System.out.println ("Leximi i Tables FILIALET: ");
con=DriverManager.getConnection(cs,user,pwd);// for establishing connection with database
}
catch(Exception e){
e.printStackTrace();
}
return connection;

}
public void printTabelat() throws SQLException{
Connection connection = null;
ResultSet resultSet = null;
try{
connection = getConnection();
String [] table = {"TABLE"};
DatabaseMetaData tabelat = connection.getMetaData();
resultSet = tabelat.getTables(null,null,"%",table);
System.out.println("Loading tables...");
System.out.println("-------------------");

while (resultSet.next()){
System.out.println("TABELA = "+resultSet.getString("TABLE_NAME"));
}
}
catch(SQLException sqle){
sqle.printStackTrace();
}
finally{
try{
if(resultSet != null)
resultSet.close();
if(connection != null)
connection.close();
}
catch(SQLException SQLE){
SQLE.printStackTrace();
}
}
}
public Collection <AllVeturat> getVeturat() throws SQLException{
Connection connection = null;
Statement stmt = null;
ResultSet rs = null;
ArrayList <AllVeturat> list = new ArrayList< AllVeturat>();
try{
connection = getConnection();
stmt = connection.createStatement();
rs = stmt.executeQuery("select * from VETURAT");
while(rs.next())
list.add(build(rs));
}
finally {
if (rs != null)
rs.close();
if(stmt != null)
stmt.close();
if(connection != null)
connection.close();
}
return list;
}
private static AllVeturat build (ResultSet result) throws SQLException {
AllVeturat AllVeturat = new AllVeturat();
int index = 0;
AllVeturat.setVeturaId(SQLTools.getString(result, ++index));
AllVeturat.setEmriVetures(SQLTools.getString(resul t, ++index));
AllVeturat.setLlojiIMotorit(SQLTools.getString(res ult, ++index));
AllVeturat.setNgjyra(SQLTools.getString(result, ++index));
AllVeturat.setModeli(SQLTools.getString(result, ++index));
AllVeturat.setCmimi(SQLTools.getInteger(result, ++index));

return AllVeturat;
}
public Collection<AllVeturat> getPagesa() throws SQLException{
Connection connection = null;
Statement stmt = null;
ResultSet rs = null;
ArrayList <AllVeturat> list = new ArrayList< AllVeturat>();
try{
connection = getConnection();
stmt = connection.createStatement();
rs = stmt.executeQuery("select * from PAGESA");
while(rs.next())
list.add(build(rs));
}
finally {
if (rs != null)
rs.close();
if(stmt != null)
stmt.close();
if(connection != null)
connection.close();
}
return list;
}

public static void main(String[] args) throws SQLException {
try{
ConnectOracle co;
if(args.length > 0)
co = new ConnectOracle(args[0]);
else
co = new ConnectOracle();
//db.printMetaData();
//Collection students = db.getStudents();
//System.out.println(students);
Collection<AllVeturat> veturat = co.getVeturat();
System.out.println(veturat);
}
catch(Exception e){
e.printStackTrace();
}
}
public static String getExecuteQuery() {
return executeQuery;
}
public static void setExecuteQuery(String executeQuery) {
ConnectOracle.executeQuery = executeQuery;
}

public String getDatabaseType() {
return databaseType;
}

public void setDatabaseType(String databaseType) {
this.databaseType = databaseType;
}
}


=========================
Class Autosalloni
===============
import java.io.Serializable;
import java.util.ArrayList;

public class Autosalloni implements Serializable{
private String emriAutosallonit = null;
private ArrayList <AllVeturat> veturat = null;


public Autosalloni(String emriAutosallonit){
this.emriAutosallonit = emriAutosallonit;
}
public void setEmriAutosallonit(String emriAutosallonit){
this.emriAutosallonit = emriAutosallonit;
}
public String getEmriAutosallonit(){
return emriAutosallonit;
}
public void setVeturat(ArrayList <AllVeturat> veturat){
this.veturat = veturat;
}
public ArrayList <AllVeturat> getVeturat(){
return veturat;
}
public void addVetura(AllVeturat vetura){
veturat.add(vetura);
}

public String toString(){
StringBuffer buffer = new StringBuffer(100);
buffer.append(emriAutosallonit);
buffer.append(veturat.toString());
return buffer.toString();
}
}

==================
class AllVeturat
==================
import java.io.Serializable;

public class AllVeturat implements Serializable{
private static String veturaId = null;
private String emriVetures = null;
private String llojiIMotorit = null;
private String ngjyra = null;
private String modeli = null;
private int cmimi = (Integer) null;


public AllVeturat() {

}
public void setEmriVetures(String emriVetures){
this.emriVetures = emriVetures;
}
public String getEmriVetures(){
return emriVetures;
}
public void setVeturaId(String veturaId){
this.veturaId = veturaId;
}
public String getVeturaId(){
return veturaId;
}
public void setLlojiIMotorit(String llojiIMotorit){
this.llojiIMotorit = llojiIMotorit;
}
public String getLlojiIMotorit(){
return llojiIMotorit;
}
public void setNgjyra(String ngjyra){
this.ngjyra = ngjyra;
}
public String getNgjyra(){
return ngjyra;
}
public void setModeli(String modeli){
this.modeli = modeli;
}
public String getModeli(){
return modeli;
}
public void setCmimi(int cmimi){
this.cmimi = cmimi;
}
public int getCmimi(){
return cmimi;
}
public String toString(){
StringBuffer buffer = new StringBuffer(100);
//buffer.append("\n========================\n");
buffer.append("emriVetures="+ emriVetures+";");
buffer.append("veturaId="+ veturaId+";");
buffer.append("cmimi="+ cmimi+";");
buffer.append("llojiIMotorit="+ llojiIMotorit+";");
buffer.append("ngjyra="+ ngjyra+";");
buffer.append("modeli="+ modeli+";");
//buffer.append("========================\n");
return buffer.toString();
}
}

==================
class SQLException
==================


package java.util;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.sql.Types;
import java.sql.Date;
import java.text.SimpleDateFormat;


public class SQLTools {

private static SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");

public static void setInteger(PreparedStatement statement, int index, Integer value) throws SQLException {
if(value == null)
statement.setNull(index, Types.NUMERIC);
else
statement.setInt(index, value.intValue());
}

public static void setString(PreparedStatement statement, int index, String value) throws SQLException {
if(value == null)
statement.setNull(index, Types.VARCHAR);
else
statement.setString(index, value);
}

public static void setDate(PreparedStatement statement, int index, Date value) throws SQLException {
if(value == null)
statement.setNull(index, Types.DATE);
else
statement.setDate(index, value);
}

public static void setDouble(PreparedStatement statement, int index, Double value) throws SQLException {
if(value == null)
statement.setNull(index, Types.DECIMAL);
else
statement.setDouble(index, value.doubleValue());
}

public static void setLong(PreparedStatement statement, int index, Long value) throws SQLException {
if(value == null)
statement.setNull(index, Types.NUMERIC);
else
statement.setDouble(index, value.longValue());
}

public static void setBoolean(PreparedStatement statement, int index, Boolean value) throws SQLException {
if(value == null)
statement.setNull(index, Types.BOOLEAN);
else
statement.setBoolean(index, value.booleanValue());
}

public static Integer getInteger(ResultSet resultSet, int index) throws SQLException {
int returnValue = resultSet.getInt(index);
if(resultSet.wasNull())
return null;
return new Integer(returnValue);
}

public static Date getDate(ResultSet resultSet, int index) throws SQLException {
java.sql.Date returnValue = resultSet.getDate(index);
if(resultSet.wasNull())
return null;
return returnValue;
}

public static Double getDouble(ResultSet resultSet, int index) throws SQLException {
double returnValue = resultSet.getDouble(index);
if(resultSet.wasNull())
return null;
return new Double(returnValue);
}

public static Long getLong(ResultSet resultSet, int index) throws SQLException {
long returnValue = resultSet.getLong(index);
if(resultSet.wasNull())
return null;
return new Long(returnValue);
}

public static String getString(CallableStatement statement, int index) throws SQLException {
String value = statement.getString(index);
if(statement.wasNull())
return null;
return value;
}

public static String getString(ResultSet resultSet, int index) throws SQLException {
String returnValue = resultSet.getString(index);
if(resultSet.wasNull())
return null;
return returnValue;
}

public static Boolean getBoolean(ResultSet resultSet, int index, Boolean defaultValue) throws SQLException {
boolean returnValue = resultSet.getBoolean(index);
if(resultSet.wasNull())
return defaultValue;
return new Boolean(returnValue);
}

public static Boolean getBoolean(ResultSet resultSet, int index) throws SQLException {
boolean returnValue = resultSet.getBoolean(index);
if(resultSet.wasNull())
return null;
return new Boolean(returnValue);
}

public static SimpleDateFormat getFormatter() {
return formatter;
}

public static void setFormatter(SimpleDateFormat formatter) {
SQLTools.formatter = formatter;
}
}

================
class Auto_Interface
================
import java.util.ArrayList;

public interface Auto_Interface{

public ArrayList <AllVeturat> getVeturat (String modeli) throws Exception;

}

================
class DatabaseException
================


public class DatabaseException extends Exception {

public DatabaseException(String message){
super(message);
}
}

====================

====================


PLEASE help,I needed for exam.

I'm thankful :)

[doWhile]

Please use the code tags...it makes your code actually readable. And please, if you cannot connect - post the full exception stack trace you are receiving.

[Tolls]

String driver ="sun.jdbc.odbc.JdbcOdbcDriver"; //
String cs="jdbc:odbc:ConnectOracle"; //connectOracle is the data source name

Don't use that driver.
Oracle comes with a perfectly good one that is designed to work with Oracle.
Which version you need depends on the version of Oracle you have and the version of Java you are using.

[besianhoxha]

Please, can u check if I have any problem in my code..

I'm receiving those errors:
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(Unknow n Source)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)ConnectOracle@42e816 databaseoracle

at ConnectOracle.getOracleConnection(ConnectOracle.ja va:85)
at ConnectOracle.getModeli(ConnectOracle.java:142)
at ConnectOracle.main(ConnectOracle.java:222)
java.lang.NullPointerException
at ConnectOracle.getModeli(ConnectOracle.java:143)
at ConnectOracle.main(ConnectOracle.java:222)


import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

public class ConnectOracle {
private static String executeQuery = "";
private String databaseType = null;


public static String getExecuteQuery() {
return executeQuery;
}
public static void setExecuteQuery(String executeQuery) {
ConnectOracle.executeQuery = executeQuery;
}

public String getDatabaseType() {
return databaseType;
}

public void setDatabaseType(String databaseType) {
this.databaseType = databaseType;
}

static {
executeQuery = "select * from FILIALET";
}
public ConnectOracle() throws DatabaseException{
this("DatabaseOracle");
}

public ConnectOracle (String type) throws DatabaseException{
if(!(type.equalsIgnoreCase("DatabaseOracle")))
throw new DatabaseException("Databaza duhet te jete Oracle");
databaseType = type.toLowerCase();
}

private Connection getConnection() throws SQLException{
Connection connection = null;
if(databaseType.equalsIgnoreCase("DatabaseOracle") )
connection = getOracleConnection();
return connection;
}
public void printPuntori() throws SQLException{
Connection connection = null;
ResultSet resultSet = null;
try{
connection = getConnection();
DatabaseMetaData table = connection.getMetaData();
resultSet = table.getTables(null,"","",null);
System.out.println("Duke lexuar tabelen...");
System.out.println("-------------------");

while (resultSet.next()){
System.out.println("PUNTORI = "+resultSet.getString("EMRI"));
}
}
catch(SQLException sqle){
sqle.printStackTrace();
}
finally{
try{
if(resultSet != null)
resultSet.close();
if(connection!= null)
connection.close();
}
catch(SQLException SQLE){
SQLE.printStackTrace();
}
}
}

private Connection getOracleConnection() throws SQLException {
Connection connection = null;
try{
String cs ="jdbc:odbc:ConncetOracle"; //VeturaDB is the data source name
String user = "shkparadite"; //username of oracle database
String pwd = "shkparadite"; //password of oracle database
Connection con = DriverManager.getConnection (cs,"shkparadite","shkparadite");
con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt = con.createStatement();
System.out.println("Lidhja me databazen Oracle u krijua ...");

ResultSet rs = stmt.executeQuery("select* from FILIALET ");
rs.next();
System.out.println ("________________________");
System.out.println ("Leximi i Tables FILIALET: " );
System.out.println ("|-" + rs.getString(1));
System.out.println ("|-" + rs.getString(2));
System.out.println ("|-" + rs.getString(3));
System.out.println ("|-" + rs.getString(4));
stmt.close();

connection =DriverManager.getConnection(cs,user,pwd);// for establishing connection with database
con.setAutoCommit(false);




System.out.println (con.toString());
}
catch(Exception e){
e.printStackTrace();
}
return connection;

}
/*public Collection <Filialet> getFiliala()throws SQLException{
Connection connection = null;
try{
stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("select* from FILIALET ");
rs.next();
System.out.println ("________________________");
System.out.println ("Leximi i Tables FILIALET: " );
System.out.println ("|-" + rs.getString(1));
System.out.println ("|-" + rs.getString(2));
System.out.println ("|-" + rs.getString(3));
System.out.println ("|-" + rs.getString(4));
}
catch (Exception e){
e.printStackTrace();

}
return null;
}*/


public Collection <Modeli> getModeli() throws SQLException{
Connection connection = null;
Statement stmt = null;
ResultSet rs = null;
ArrayList <Modeli> list = new ArrayList <Modeli>();
try{
connection = getConnection();
stmt = connection.createStatement();
rs = stmt.executeQuery("select * from MODELI");
rs.next();

while(rs.next())
list.add(getModeli(rs));
System.out.println("");
}
finally {
if (rs != null)
rs.close();
if(stmt != null)
stmt.close();
if(connection != null)
connection.close();
}
for (Modeli modeli : list) {
System.out.println(modeli);
}
return list;
}
private static Modeli getModeli (ResultSet result) throws SQLException {
Modeli modeli = new Modeli();
int index = 0;
modeli.setModeli_Id(result.getInt(++index));
modeli.setLloji_modelit(result.getString(++index)) ;
modeli.setLloji_motorit(result.getString(++index)) ;
modeli.setNgjyra(result.getString(++index));
// modeli.setViti_prodhimit(result.getInt(++index));
// modeli.setCmimi(result.getInt(++index));
return modeli;
}

public Collection <Vetura> getVetura () throws SQLException{
Connection connection = null;
Statement stmt = null;
ResultSet rs = null;
ArrayList<Vetura> list = new ArrayList<Vetura>();
try{
connection = getConnection();
System.out.println("Connection:" + connection.getCatalog() + connection.toString());
stmt = connection.createStatement();
System.out.println("stmt:" + stmt.toString());

rs = stmt.executeQuery("select * from VETURA");
System.out.println("select * from VETURA: ");
while(rs.next())
list.add(getVetura(rs));
}
finally {
if (rs != null)
rs.close();
if(stmt != null)
stmt.close();
if(connection != null)
connection.close();
}
return list;
}
private static Vetura getVetura (ResultSet rs2) throws SQLException {
Vetura vetura = new Vetura();
int index = 0;
vetura.setModeli_id(rs2.getInt(++index));
vetura.setKuantiteti(rs2.getInt(++index));
vetura.setModeli_id(rs2.getInt(++index));
return vetura;
}
public static void main(String[] args) throws SQLException {


try{
ConnectOracle connection;
if(args.length > 0)
connection = new ConnectOracle(args[0]);
else
connection = new ConnectOracle();
//co.printMetaData();
//Collection modeli = co.getModeli();
System.out.println(connection.toString() + " " + connection.getDatabaseType());
Collection <Modeli> modeli = connection.getModeli();
System.out.println(modeli);
Collection<Vetura> vetura = connection.getVetura();
System.out.println(vetura);
}
catch(Exception e){
e.printStackTrace();
}
}







}

[Tolls]

As I said above, use the driver that oracle provides.
Do not use the JDBC/ODBC bridge.

[Sintu Kumar]

This program is based on type-1 driver so at 1st you have to create a Data source name with the name 'ConnectOracle'. Go to control Panel and create a data souce name with name which you provided to DriverManager.getConnection ("jdbc:odbc:ConnectOracle","shkparadite","shkpa rad ite"); statement.

[Sintu Kumar]

If u want without DSN then use type-4 driver. This driver is provided by oracle installation copy the driver from oracle driver and use with this statement


register type-4 driver with this code

Class.fornName("oracle.jdbc.driver.DriverManager") ;

and get connection object with

Connection con = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:xe","shkparadit e","shkparad ite");

after that you have no need for dsn

[Tolls]

Again...use the driver specific to the database you are using.
Do not waste time with the JDBC-ODBC bridge.

Oracle has perfectly good drivers, so use them.