IBM DB2 ile DataSource bağlantı

Eclipse Projesi: indir

IBM DB2 Veritabanı yapısı

CREATE table IF NOT EXISTS USERS(
USERID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
NAME varchar(20),
SURNAME varchar(20),
PASSPORT INTEGER,
primary key (USERID))

ConnectWithDataSource.Java

import java.io.Serializable;
import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class ConnectWithDataSource implements Serializable{

 private static final long serialVersionUID = 1L;
 static Connection connection = null;

 public static Connection getConnection() {

  try {
   InitialContext context = new InitialContext();
   DataSource dataSource = (DataSource) context.lookup("java:/comp/env/jdbc/MYDB");
   connection = dataSource.getConnection();
  } catch (NamingException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return connection;
 }

 public static boolean closeConnection() {

  boolean result = false;
  try {
   if (connection != null && !connection.isClosed()) {
    connection.close();
    System.out.println("Database connection terminated");
    result = true; 
   }
  } catch (Exception ex) {
   ex.printStackTrace();
   result = false;
  }
  return result;
 } 
}

DBController.Java

import java.sql.*;
import javax.naming.*;
import javax.sql.DataSource;
 
public class DBController {
private DataSource ds;
private Connection con;// NEW CHANGE

public void setUp() throws NamingException,SQLException{
 
 InitialContext ctx = new InitialContext();
    ds = (DataSource)ctx.lookup("java:/comp/env/jdbc/MYDB");
    con = ds.getConnection(); // NEW CHANGE
}

public ResultSet readRequest(String dbQuery){
    ResultSet rs=null;
    try{
        Statement stmt = con.createStatement();
        rs = stmt.executeQuery(dbQuery);
    }
    catch(Exception e){e.printStackTrace();}
    return rs;
}

public int updateRequest(String dbQuery){
    int count=0;
    try{
        Statement stmt = con.createStatement();
        count=stmt.executeUpdate(dbQuery);
    }
    catch(Exception e){e.printStackTrace();}
    return count;
}

public void terminate(){
    try {con.close();}
    catch(Exception e){e.printStackTrace();}
}
}

Kisiler.Java

 public class Kisiler
{
private int _tcno;
private String _adi;
private String _soyadi;
public Kisiler() {

}
public int get_tcno() {
    return _tcno;
}
public void set_tcno(int _tcno) {
    this._tcno = _tcno;
}
public String get_adi() {
    return _adi;
}
public void set_adi(String _adi) {
    this._adi = _adi;
}
public String get_soyadi() {
    return _soyadi;
}
public void set_soyadi(String _soyadi) {
    this._soyadi = _soyadi;
}
 
}

PersonelBean.Java

import java.io.Serializable; 
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.naming.NamingException;

@ManagedBean(name = "yb")
@SessionScoped
public class PersonelBean implements Serializable{
 private static final long serialVersionUID = 1L;
 public PersonelBean() {
    }
    public List<Kisiler> getPersonelList() throws SQLException,NamingException{

     DBController cntrl =new DBController();
     cntrl.setUp();

        ResultSet resultSet =  cntrl.readRequest("SELECT * FROM DB2ADMIN.users");

        List<Kisiler> list = new ArrayList<Kisiler>();

        while(resultSet.next()){
         Kisiler personel = new Kisiler();
      
         personel.set_adi(resultSet.getString("NAME"));
         personel.set_soyadi(resultSet.getString("SURNAME"));
         personel.set_tcno(resultSet.getInt("USERID"));

            list.add(personel);
        }
        cntrl.terminate();
        return list;
    }
   
}

Context.xml

 <?xml version="1.0" encoding="UTF-8"?>
<Context>
      <Resource name="jdbc/MYDB" auth="Container" 
  type="javax.sql.DataSource"
               maxActive="100" maxIdle="30" maxWait="10000"
               username="db2admin" password="db2admin" 
               driverClassName="com.ibm.db2.jcc.DB2Driver"
               url="jdbc:db2://localhost:50000/MYDB" 
               />  
            
</Context>

test.xhtml

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:h="http://java.sun.com/jsf/html"
      xmlns:p="http://primefaces.org/ui"
      xmlns:f="http://java.sun.com/jsf/core">
<h:head><title>PrimeFaces Test</title>
</h:head>
<h:body>
<h:form prependId="false">  
  
    <p:dataTable id="dataTable" var="car" value="#{yb.personelList}"  
                 paginator="true" rows="10"  
                 paginatorTemplate="{CurrentPageReport}  {FirstPageLink} {PreviousPageLink} {PageLinks} {NextPageLink} {LastPageLink} {RowsPerPageDropdown}"  
                 rowsPerPageTemplate="5,10,15">  
        <f:facet name="header">  
            Ajax Pagination  
        </f:facet>  
  
        <p:column>  
            <f:facet name="header">  
                <h:outputText value="Model" />  
            </f:facet>  
            <h:outputText value="#{car._adi}" />  
        </p:column>  
  
        <p:column>  
            <f:facet name="header">  
                <h:outputText value="Year" />  
            </f:facet>  
            <h:outputText value="#{car._soyadi}" />  
        </p:column>  
  
        <p:column>  
            <f:facet name="header">  
                <h:outputText value="Manufacturer" />  
            </f:facet>  
            <h:outputText value="#{car._tcno}" />  
        </p:column>  
         
    </p:dataTable>  
  
</h:form>  
                
</h:body></html>