aspose file tools*
The moose likes JDBC and the fly likes prepared staements insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "prepared staements insert" Watch "prepared staements insert" New topic
Author

prepared staements insert

janice garcia
Greenhorn

Joined: May 23, 2012
Posts: 4
Not sure where to ask, but I have a similar problem with prepared statments. I am using oracle 11g and java in eclipse Indigo. My binding arguments are not being set in prepared statements. I am not getting any type of errors,even though there is code to catch for them. This is a new installation with Weblogic 10.3.6. I can see the values, but in the debugger I don't see the values being set int he prepared statement??

J
Palak Mathur
Ranch Hand

Joined: Jan 29, 2007
Posts: 313

janice garcia wrote:Not sure where to ask, but I have a similar problem with prepared statments. I am using oracle 11g and java in eclipse Indigo. My binding arguments are not being set in prepared statements. I am not getting any type of errors,even though there is code to catch for them. This is a new installation with Weblogic 10.3.6. I can see the values, but in the debugger I don't see the values being set int he prepared statement??

J


Paste part of your code so that we know what exactly you are doing.


Palak Mathur | My Blog | TechJaunt | What is JavaRanch? | List of All FAQs
janice garcia
Greenhorn

Joined: May 23, 2012
Posts: 4
package com.java;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletContext;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import oracle.jdbc.internal.OracleResultSet;
import weblogic.jdbc.extensions.WLConnection;
import weblogic.jdbc.vendor.oracle.*;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import java.sql.Blob;

import org.apache.struts2.ServletActionContext;
import org.apache.struts2.interceptor.ServletRequestAware;
import org.apache.struts2.interceptor.ServletResponseAware;
import org.apache.struts2.interceptor.SessionAware;
import org.apache.struts2.util.ServletContextAware;

import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;


public class CreateMember extends ActionSupport implements SessionAware, ServletRequestAware,
ServletContextAware,ServletResponseAware{

private final static Logger log = Logger.getLogger(CreateMember.class .getName());
BufferedInputStream bs;
private PreparedStatement pstmt3 = null;

private OracleConnection orclconn = null;
private Connection con = null;
private DataSource ds = null;

private InitialContext ic;
protected Map <String, Object> session;
private long customerid ;
private long profileid;
private long paymentid;
private long creditcardid;
private double state_tax;
private String expdate;
protected DateConversion dt = new DateConversion();
private OutputStream outStream[] = null;
private InputStream inStream[] = null;
private FileInputStream instream = null;
private byte [] buffer;


//add member
private PreparedStatement insertCustomer = null;
private PreparedStatement selectCustomer = null;
private PreparedStatement insertUser = null;

//add account
private PreparedStatement updateBlob2 = null;
private PreparedStatement selectBlob = null;
private PreparedStatement insertPayment = null;
private PreparedStatement selectPayment = null;
private PreparedStatement insertInvoice = null;
private PreparedStatement insertCreditCard = null;
private PreparedStatement selectCreditCard = null;
private PreparedStatement insertAccount = null;
private java.sql.Blob myRegularBlob[] = null;
private OracleThinBlob blob[] = null;

private int a=27; private long filesize=0;
private File savedFile = null; String dataDir;
private FileInputStream fs=null;
private int numberofImages =0;

//add profile
private PreparedStatement insertProfile = null;
private ResultSet rs = null;
private oracle.jdbc.internal.OracleResultSet orclrs = null;


private List<File> fileUpload = new ArrayList<File>();





public void setServletResponse(HttpServletResponse arg0) {
// TODO Auto-generated method stub

}


public void setServletContext(ServletContext arg0) {
// TODO Auto-generated method stub

}


public void setServletRequest(HttpServletRequest arg0) {
// TODO Auto-generated method stub

}


public Map<String, Object> getSession() {
return session;
}


public void setSession(Map<String, Object> arg0) {
// TODO Auto-generated method stub

}


@SuppressWarnings({ "unchecked", "static-access", "deprecation" })
//do I have to synchronize method??
public String addMember(){

session= ActionContext.getContext().getSession();

//need system date
Calendar cal = Calendar.getInstance();
java.sql.Date today = new java.sql.Date( cal.getTime().getTime() );

//sales tax
//state tax ca need to get user zipcode, country, state determine tax.
String country, state;
country= (String)session.get("country");
state=(String) session.get("state");

if((country =="US")&&(state=="CA")){
double creditCardAmt = Double.valueOf((String) session.get("ccamount"));
int decimalPlaces = 2;
BigDecimal ca_state_tax = new BigDecimal(creditCardAmt * .085);
ca_state_tax = ca_state_tax.setScale(decimalPlaces, BigDecimal.ROUND_HALF_UP);
state_tax = ca_state_tax.doubleValue();
}else{
state_tax = 0.0;
}




//'YYYY-MM-DD' exp date valid
StringBuffer d = new StringBuffer((String)session.get("ccexpdate"));
String month = null; String year = null; String day = null;
d.trimToSize();
if( d.length()>= 6){
month = d.substring(0,2);
year = d.substring(3,7);
day ="";
// expdate = year +"-"+ month + "-"+ "01";
}


//got through photos
//how many files, before setting to data base
session= ActionContext.getContext().getSession();


fileUpload = (List<File>) session.get("photosList");



try {
// Load the MySQL JDBC driver
try {
// Put connection properties in to a hashtable.
Hashtable<String,String> ht = new Hashtable<String,String>();
ht.put(Context.INITIAL_CONTEXT_FACTORY,
"weblogic.jndi.WLInitialContextFactory");
ht.put(Context.PROVIDER_URL,
"t3://localhost:7001");

// Get a context for the JNDI lookup
ic = new InitialContext(ht);
// Look up the data source
ds
= (javax.sql.DataSource) ic.lookup("Oracle11g");

} catch (NamingException e) {
e.printStackTrace();

} catch ( Exception e) {
e.printStackTrace();
}
//con = ds.getConnection("system", "Janice21a");
con = ds.getConnection();

//transaction manager; rollsback if any of it fails
con.setAutoCommit(false);

/**
* Oracle 11g requires table/columns,db objects to be Capital!!!
* Used this to test database access
*
*/



String insertString = "INSERT INTO STRUTS.CUSTOMER(CUSTOMERID , FIRSTNAME , MIDDLENAME , " +
"LASTNAME , ADDRESS1 , ADDRESS2 , CITY , STATE , "+
"ZIPCODE , COUNTRY , EMAIL , EMAIL2 , PHONENUMBER , PHONENUMBER2 ) " +
" VALUES(STRUTS.CUSTOMER_SEQ.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?) ";

String select = "SELECT STRUTS.CUSTOMER_SEQ.CURRVAL FROM DUAL";

String insertString2 = "INSERT INTO STRUTS.USERS (USERID,USERNAME, PASSWORD, CUSTOMERID) VALUES(STRUTS.USERS_SEQ.NEXTVAL,?,?,?) ";


//add account statements

String insertPay = "INSERT INTO STRUTS.PAYMENT "+ " (PAYMENTID,CUSTOMERID, PAYMENTTYPE, PAYMENTDATE, PAYMENTMETHOD, PAYMENTTERMS)" +
" VALUES(STRUTS.PAYMENT_SEQ.NEXTVAL,?,?,?,?,?) ";


String selectPay = "SELECT STRUTS.PAYMENT_SEQ.CURRVAL FROM DUAL ";

String insertInv = "INSERT INTO STRUTS.INVOICE "+ " (INVOICEID, INVOICENUMBER,INVOICEDATE,INVOICEAMOUNT,STATETAXAMOUNT,PAYMENTID)" +
" VALUES(STRUTS.INVOICE_SEQ.NEXTVAL,?,?,?,?,?) ";

String insertAcct = "INSERT INTO STRUTS.ACCOUNT (ACCOUNTID,CUSTOMERID,CREDITCARDID,NOTES)" +
" VALUES(STRUTS.ACCOUNT_SEQ.NEXTVAL,?,?,?) ";


String selectCredCard = "SELECT STRUTS.CREDITCARD_SEQ.CURRVAL FROM DUAL";

String insertCredCard = "INSERT INTO STRUTS.CREDITCARD (CREDITCARDID,CUSTOMERID,PAYMENTID,CREDITCARDTYPE,CARDHOLDERSNAME,CREDITCARDEXP,CREDITCARDAUTHNUM,CREDITCARDAMT, " +
"FIRSTNAME , MIDDLENAME , LASTNAME , ADDRESS1 , ADDRESS2 , CITY , STATE , ZIPCODE ) VALUES( STRUTS.CREDITCARD_SEQ.NEXTVAL,?,?, ?,?,?,?,?,?,?,?,?,?,?,?,?) ";

//add profile statements
String insertProf = "INSERT INTO STRUTS.PROFILE (PROFILEID, CUSTOMERID, BIRTHDATE, GENDER, MARITAL_STATUS, LIVING_STATUS," +
" ETHINICITY, SEEKING, CHILDREN,RELIGION, SEXUAL_OPENNESS, DESCRIBE_LOOKS, HAIR_COLOR, EYE_COLOR, HEIGHT, BODY_TYPE," +
" EDUCATION, CAREER, POLITICS, INCOME_LEVEL, SMOKING_HABIT, DRUG_HABIT, DRINKING_HABIT, ABOUT_ME, ATTRACTED_TO, LIKES," +
" PHOTOS, PHOTOS1, PHOTOS2, PHOTOS3, PHOTOS4, PHOTOS5)"
+" VALUES( STRUTS.PROFILE_SEQ.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,EMPTY_BLOB(),EMPTY_BLOB(),EMPTY_BLOB(),EMPTY_BLOB(),EMPTY_BLOB(),EMPTY_BLOB()) ";

String BlobId = "SELECT STRUTS.PROFILE_SEQ.CURRVAL FROM DUAL";

// String updateBlob = "SELECT PHOTOS, PHOTOS1,PHOTOS2,PHOTO3,PHOTO4,PHOTO5 FROM STRUTS.PROFILE FOR UPDATE WHERE PROFILE.PROFILEID = ?";




insertCustomer = con.prepareStatement(insertString);
selectCustomer = con.prepareStatement(select);
insertUser = con.prepareStatement(insertString2);
// updateBlob2 = con.prepareStatement(updateBlob);
selectBlob = con.prepareStatement(BlobId);
//get next incremental val custid

// Set the values
//insertCustomer.setInt(1,custNextVal);
insertCustomer.setString(1,(String) session.get("firstName"));
insertCustomer.setString(2,(String) session.get("middleName"));
insertCustomer.setString(3,(String) session.get("lastName"));
insertCustomer.setString(4,(String) session.get("address1"));
insertCustomer.setString(5,(String) session.get("address2"));
insertCustomer.setString(6,(String) session.get("city"));
insertCustomer.setString(7,(String) session.get("state"));
insertCustomer.setString(8,(String) session.get("zipCode"));
insertCustomer.setString(9,(String) session.get("country"));
insertCustomer.setString(10,(String) session.get("email"));
insertCustomer.setString(11,(String) session.get("email2"));
insertCustomer.setString(12,(String) session.get("phone"));
insertCustomer.setString(13,(String) session.get("cell"));

insertCustomer.executeUpdate();


//isolation Level from database
try{
int trans = con.getTransactionIsolation();

} catch(SQLException w){
log.info(w.getMessage());

}

// selectCustomer.setString(1,(String) session.get("lastName")) ;
// selectCustomer.setString(2,(String) session.get("firstName"));

rs = selectCustomer.executeQuery();
while (rs.next()) {
customerid = rs.getLong(1);
}




insertUser.setString(1, (String) session.get("username"));
insertUser.setString(2,(String) session.get("password"));
insertUser.setLong(3, customerid);

insertUser.executeUpdate();

//insert account

insertPayment = con.prepareStatement(insertPay);
selectPayment = con.prepareStatement(selectPay);
insertInvoice = con.prepareStatement(insertInv);
insertCreditCard = con.prepareStatement(insertCredCard);
selectCreditCard = con.prepareStatement(selectCredCard);
insertAccount = con.prepareStatement(insertAcct);



insertPayment.setLong(1, customerid);
insertPayment.setString(2, (String) session.get("paymenttype"));
insertPayment.setDate(3, (java.sql.Date) today );
insertPayment.setString(4, (String) session.get("paymenttype"));
insertPayment.setInt(5, 31);

insertPayment.executeUpdate();


//get paymentid from payment
// selectPayment.setLong(1, customerid);
rs = selectPayment.executeQuery();
while (rs.next()) {
paymentid = rs.getLong(1);
}

insertInvoice.setString(1, ( today + (String)session.get("lastName") + customerid).toUpperCase());
insertInvoice.setDate(2,today);
insertInvoice.setDouble(3, new Double((String) session.get("ccamount")).valueOf((String)session.get("ccamount")));
insertInvoice.setDouble(4, state_tax);
insertInvoice.setLong(5, paymentid);

insertInvoice.executeUpdate();

insertCreditCard.setLong(1,customerid);
insertCreditCard.setLong(2,paymentid);
insertCreditCard.setString(3,(String)session.get("paymenttype"));
insertCreditCard.setString(4,(String)session.get("firstName")+ " "+ (String)session.get("middleName")+" "+ (String)session.get("lastName"));
insertCreditCard.setDate(5, dt.getDate1(year, month, day ));
insertCreditCard.setDouble(6, new Double((String)session.get("ccnumber")).valueOf((String)session.get("ccnumber")));
insertCreditCard.setDouble(7,new Double((String)session.get("ccamount")).valueOf((String)session.get("ccamount")));
insertCreditCard.setString(8,(String)session.get("firstName2"));
insertCreditCard.setString(9,(String)session.get("middleName2"));
insertCreditCard.setString(10,(String)session.get("lastName2"));
insertCreditCard.setString(11, (String)session.get("address1B"));
insertCreditCard.setString(12,(String)session.get("address2B"));
insertCreditCard.setString(13,(String)session.get("city2"));
insertCreditCard.setString(14,(String)session.get("state2"));
insertCreditCard.setString(15,(String)session.get("zipCode2"));
// insertCreditCard.setString(16,(String)session.get("country2"));


insertCreditCard.executeUpdate();

//get creditcardid from creditcard
// selectCreditCard.setLong(1,customerid);

rs = selectCreditCard.executeQuery();
while (rs.next()) {
creditcardid = rs.getLong(1);
}

insertAccount.setLong(1,customerid);
insertAccount.setLong(2,paymentid);
insertAccount.setLong(3,creditcardid);

insertAccount.executeUpdate();


//insert profile


insertProfile = con.prepareStatement(insertProf);

insertProfile.setLong(1,customerid);
insertProfile.setDate(2, dt.getDate1((String)session.get("birthyear"),(String)session.get("birthmonth"),(String)session.get("birthday")));
insertProfile.setString(3,(String)session.get("gender"));
insertProfile.setString(4, (String)session.get("maritalStatus"));
insertProfile.setString(5, (String)session.get("livingStatus"));
insertProfile.setString(6,(String)session.get("ethnicity"));
insertProfile.setString(7,(String)session.get("seeking"));
insertProfile.setString(8,(String)session.get("children"));
insertProfile.setString(9,(String)session.get("religion"));
insertProfile.setString(10,(String)session.get("sexualOpenness") );
insertProfile.setString(11,(String)session.get("describeLooks"));
insertProfile.setString(12,(String)session.get("hairColor"));
insertProfile.setString(13,(String)session.get("eyeColor"));
insertProfile.setString(14,(String)session.get("height"));
insertProfile.setString(15,(String)session.get("bodyType"));
insertProfile.setString(16,(String)session.get("education"));

insertProfile.setString(17, (String)session.get("career"));
insertProfile.setString(18,(String)session.get("politics"));
insertProfile.setString(19,(String) session.get("incomeLevel") );
insertProfile.setString(20,(String)session.get("smokingHabit") );
insertProfile.setString(21, (String)session.get("drugHabit") );
insertProfile.setString(22,(String)session.get("drinkingHabit") );
insertProfile.setString(23,(String)session.get("aboutMe") );
insertProfile.setString(24,(String)session.get("attractedTo") );
insertProfile.setString(25, (String)session.get("likes") );

insertProfile.executeUpdate();

//get profileId
// selectBlob.setLong(1,customerid);
rs = selectBlob.executeQuery();
while (rs.next()) {
profileid = rs.getLong(1);
}






PreparedStatement pstmt = null;

String statement4= "SELECT PHOTOS, PHOTOS1,PHOTOS2,PHOTOS3,PHOTOS4,PHOTOS5 FROM STRUTS.PROFILE WHERE PROFILEID = ? FOR UPDATE";


rs = pstmt.executeQuery(statement4);

pstmt.setLong(1,profileid);

while( rs.next())
{
for( int y=1;y <= numberofImages;y++) { //for each column
myRegularBlob[y] = rs.getBlob(y);

}
}

//get images to db
int i =0;File val = null;
ServletContext servletContext2 = null;
servletContext2 = ServletActionContext.getServletContext();

dataDir = servletContext2.getRealPath("/photos/");

File file = new File(dataDir);

if (!file.exists() || !file.isDirectory()) {

System.out.println("Parameter is not a directory");
//System.exit(1);
}
File[] fileArray = file.listFiles();

for ( i = 0; i < fileArray.length; i++) {

for ( int z=0; z < numberofImages; z++) {
val = (File) session.get("photos" + String.valueOf(z));

if( fileArray[i].getName().endsWith(val.getName()) == true){

try {

instream = new FileInputStream("C:\\Users\\janice\\workspace\\StrutsAction\\build\\weboutput\\photos\\"+ fileArray[i]);


outStream[z] = (OutputStream)myRegularBlob[z].setBinaryStream(1l);
// use buffer to write from file to db
int size = ((ServletResponse) myRegularBlob[z]).getBufferSize();
buffer = new byte[size];
int length = -1;

while ((length = instream.read(buffer)) != -1)
{
outStream[z].write(buffer, 0, length);
}
inStream[z].close();
outStream[z].close();

} catch(IOException w){
log.info(w.getMessage());

} catch(IndexOutOfBoundsException e){
log.info(e.getMessage());
}
}//end if
} //end inner for/next
} //end outer for/next
// con.commit();
//close add member
insertCustomer.close();
insertUser.close();
selectCustomer.close();

//close add account
insertPayment.close();
selectPayment.close();
insertInvoice.close();
insertCreditCard.close();
selectCreditCard.close();
insertAccount.close();

// con.close();
rs.close();

//transacton object
con.setAutoCommit(true);


} catch (SQLException e ) {
e.printStackTrace();
log.info(String.valueOf(e.getErrorCode() ));

log.info(e.getMessage());
if (con != null) {
try {
System.err.print("Transaction is " +
"being rolled back");
con.rollback();
con.close();
rs.close();
} catch(SQLException excep) {
log.info(excep.getMessage());
}
}
}

return SUCCESS;

}
/**
//find out how many photos to update
switch (numberofImages) {
case 5:
myRegularBlob[31].setBinaryStream(1l);
break;
case 4:
insertProfile.setBinaryStream(31, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(32, bs = null, (int) (filesize = 0));
break;
case 3:
insertProfile.setBinaryStream(30, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(31, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(32, bs = null, (int) (filesize = 0));
break;
case 2:
insertProfile.setBinaryStream(29, null, 0);
insertProfile.setBinaryStream(30, null, 0);
insertProfile.setBinaryStream(31, null, 0);
insertProfile.setBinaryStream(32, null, 0);
break;
case 1:
insertProfile.setBinaryStream(29, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(30, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(31, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(32, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(33, bs = null, (int) (filesize = 0));
break;
case 0:
insertProfile.setBinaryStream(29, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(30, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(31, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(32, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(33, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(34, bs = null, (int) (filesize = 0));
break;

default:
log.info("no images");
break;
}
**/


private PreparedStatement executeQuery(String sql) {
// TODO Auto-generated method stub
return null;
}






} //END OF CLASS
Palak Mathur
Ranch Hand

Joined: Jan 29, 2007
Posts: 313

Hi Janice,

Please use code tag.
janice garcia
Greenhorn

Joined: May 23, 2012
Posts: 4
package com.java;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletContext;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import oracle.jdbc.internal.OracleResultSet;
import weblogic.jdbc.extensions.WLConnection;
import weblogic.jdbc.vendor.oracle.*;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import java.sql.Blob;

import org.apache.struts2.ServletActionContext;
import org.apache.struts2.interceptor.ServletRequestAware;
import org.apache.struts2.interceptor.ServletResponseAware;
import org.apache.struts2.interceptor.SessionAware;
import org.apache.struts2.util.ServletContextAware;

import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;


public class CreateMember extends ActionSupport implements SessionAware, ServletRequestAware,
ServletContextAware,ServletResponseAware{

private final static Logger log = Logger.getLogger(CreateMember.class .getName());
BufferedInputStream bs;
private PreparedStatement pstmt3 = null;

private OracleConnection orclconn = null;
private Connection con = null;
private DataSource ds = null;

private InitialContext ic;
protected Map <String, Object> session;
private long customerid ;
private long profileid;
private long paymentid;
private long creditcardid;
private double state_tax;
private String expdate;
protected DateConversion dt = new DateConversion();
private OutputStream outStream[] = null;
private InputStream inStream[] = null;
private FileInputStream instream = null;
private byte [] buffer;


//add member
private PreparedStatement insertCustomer = null;
private PreparedStatement selectCustomer = null;
private PreparedStatement insertUser = null;

//add account
private PreparedStatement updateBlob2 = null;
private PreparedStatement selectBlob = null;
private PreparedStatement insertPayment = null;
private PreparedStatement selectPayment = null;
private PreparedStatement insertInvoice = null;
private PreparedStatement insertCreditCard = null;
private PreparedStatement selectCreditCard = null;
private PreparedStatement insertAccount = null;
private java.sql.Blob myRegularBlob[] = null;
private OracleThinBlob blob[] = null;

private int a=27; private long filesize=0;
private File savedFile = null; String dataDir;
private FileInputStream fs=null;
private int numberofImages =0;

//add profile
private PreparedStatement insertProfile = null;
private ResultSet rs = null;
private oracle.jdbc.internal.OracleResultSet orclrs = null;


private List<File> fileUpload = new ArrayList<File>();





public void setServletResponse(HttpServletResponse arg0) {
// TODO Auto-generated method stub

}


public void setServletContext(ServletContext arg0) {
// TODO Auto-generated method stub

}


public void setServletRequest(HttpServletRequest arg0) {
// TODO Auto-generated method stub

}


public Map<String, Object> getSession() {
return session;
}


public void setSession(Map<String, Object> arg0) {
// TODO Auto-generated method stub

}


@SuppressWarnings({ "unchecked", "static-access", "deprecation" })
//do I have to synchronize method??
public String addMember(){

session= ActionContext.getContext().getSession();

//need system date
Calendar cal = Calendar.getInstance();
java.sql.Date today = new java.sql.Date( cal.getTime().getTime() );

//sales tax
//state tax ca need to get user zipcode, country, state determine tax.
String country, state;
country= (String)session.get("country");
state=(String) session.get("state");

if((country =="US")&&(state=="CA")){
double creditCardAmt = Double.valueOf((String) session.get("ccamount"));
int decimalPlaces = 2;
BigDecimal ca_state_tax = new BigDecimal(creditCardAmt * .085);
ca_state_tax = ca_state_tax.setScale(decimalPlaces, BigDecimal.ROUND_HALF_UP);
state_tax = ca_state_tax.doubleValue();
}else{
state_tax = 0.0;
}




//'YYYY-MM-DD' exp date valid
StringBuffer d = new StringBuffer((String)session.get("ccexpdate"));
String month = null; String year = null; String day = null;
d.trimToSize();
if( d.length()>= 6){
month = d.substring(0,2);
year = d.substring(3,7);
day ="";
// expdate = year +"-"+ month + "-"+ "01";
}


//got through photos
//how many files, before setting to data base
session= ActionContext.getContext().getSession();


fileUpload = (List<File>) session.get("photosList");



try {
// Load the MySQL JDBC driver
try {
// Put connection properties in to a hashtable.
Hashtable<String,String> ht = new Hashtable<String,String>();
ht.put(Context.INITIAL_CONTEXT_FACTORY,
"weblogic.jndi.WLInitialContextFactory");
ht.put(Context.PROVIDER_URL,
"t3://localhost:7001");

// Get a context for the JNDI lookup
ic = new InitialContext(ht);
// Look up the data source
ds
= (javax.sql.DataSource) ic.lookup("Oracle11g");

} catch (NamingException e) {
e.printStackTrace();

} catch ( Exception e) {
e.printStackTrace();
}
//con = ds.getConnection("system", "Janice21a");
con = ds.getConnection();

//transaction manager; rollsback if any of it fails
con.setAutoCommit(false);

/**
* Oracle 11g requires table/columns,db objects to be Capital!!!
* Used this to test database access
*
*/



// while( rs.next() ){
// long s1 = rset.getLong(1);
// String s2 = res.getString(2);
// System.out.print(" generated key: " + s1 + " " );
// }
} catch (SQLException e) {
e.printStackTrace();
} finally {}}


String insertString = "INSERT INTO STRUTS.CUSTOMER(CUSTOMERID , FIRSTNAME , MIDDLENAME , " +
"LASTNAME , ADDRESS1 , ADDRESS2 , CITY , STATE , "+
"ZIPCODE , COUNTRY , EMAIL , EMAIL2 , PHONENUMBER , PHONENUMBER2 ) " +
" VALUES(STRUTS.CUSTOMER_SEQ.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?) ";

String select = "SELECT STRUTS.CUSTOMER_SEQ.CURRVAL FROM DUAL";

String insertString2 = "INSERT INTO STRUTS.USERS (USERID,USERNAME, PASSWORD, CUSTOMERID) VALUES(STRUTS.USERS_SEQ.NEXTVAL,?,?,?) ";


//add account statements

String insertPay = "INSERT INTO STRUTS.PAYMENT "+ " (PAYMENTID,CUSTOMERID, PAYMENTTYPE, PAYMENTDATE, PAYMENTMETHOD, PAYMENTTERMS)" +
" VALUES(STRUTS.PAYMENT_SEQ.NEXTVAL,?,?,?,?,?) ";


String selectPay = "SELECT STRUTS.PAYMENT_SEQ.CURRVAL FROM DUAL ";

String insertInv = "INSERT INTO STRUTS.INVOICE "+ " (INVOICEID, INVOICENUMBER,INVOICEDATE,INVOICEAMOUNT,STATETAXAMOUNT,PAYMENTID)" +
" VALUES(STRUTS.INVOICE_SEQ.NEXTVAL,?,?,?,?,?) ";

String insertAcct = "INSERT INTO STRUTS.ACCOUNT (ACCOUNTID,CUSTOMERID,CREDITCARDID,NOTES)" +
" VALUES(STRUTS.ACCOUNT_SEQ.NEXTVAL,?,?,?) ";


String selectCredCard = "SELECT STRUTS.CREDITCARD_SEQ.CURRVAL FROM DUAL";

String insertCredCard = "INSERT INTO STRUTS.CREDITCARD (CREDITCARDID,CUSTOMERID,PAYMENTID,CREDITCARDTYPE,CARDHOLDERSNAME,CREDITCARDEXP,CREDITCARDAUTHNUM,CREDITCARDAMT, " +
"FIRSTNAME , MIDDLENAME , LASTNAME , ADDRESS1 , ADDRESS2 , CITY , STATE , ZIPCODE ) VALUES( STRUTS.CREDITCARD_SEQ.NEXTVAL,?,?, ?,?,?,?,?,?,?,?,?,?,?,?,?) ";

//add profile statements
String insertProf = "INSERT INTO STRUTS.PROFILE (PROFILEID, CUSTOMERID, BIRTHDATE, GENDER, MARITAL_STATUS, LIVING_STATUS," +
" ETHINICITY, SEEKING, CHILDREN,RELIGION, SEXUAL_OPENNESS, DESCRIBE_LOOKS, HAIR_COLOR, EYE_COLOR, HEIGHT, BODY_TYPE," +
" EDUCATION, CAREER, POLITICS, INCOME_LEVEL, SMOKING_HABIT, DRUG_HABIT, DRINKING_HABIT, ABOUT_ME, ATTRACTED_TO, LIKES," +
" PHOTOS, PHOTOS1, PHOTOS2, PHOTOS3, PHOTOS4, PHOTOS5)"
+" VALUES( STRUTS.PROFILE_SEQ.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,EMPTY_BLOB(),EMPTY_BLOB(),EMPTY_BLOB(),EMPTY_BLOB(),EMPTY_BLOB(),EMPTY_BLOB()) ";

String BlobId = "SELECT STRUTS.PROFILE_SEQ.CURRVAL FROM DUAL";

// String updateBlob = "SELECT PHOTOS, PHOTOS1,PHOTOS2,PHOTO3,PHOTO4,PHOTO5 FROM STRUTS.PROFILE FOR UPDATE WHERE PROFILE.PROFILEID = ?";




insertCustomer = con.prepareStatement(insertString);
selectCustomer = con.prepareStatement(select);
insertUser = con.prepareStatement(insertString2);
// updateBlob2 = con.prepareStatement(updateBlob);
selectBlob = con.prepareStatement(BlobId);
//get next incremental val custid

// Set the values
//insertCustomer.setInt(1,custNextVal);
insertCustomer.setString(1,(String) session.get("firstName"));
insertCustomer.setString(2,(String) session.get("middleName"));
insertCustomer.setString(3,(String) session.get("lastName"));
insertCustomer.setString(4,(String) session.get("address1"));
insertCustomer.setString(5,(String) session.get("address2"));
insertCustomer.setString(6,(String) session.get("city"));
insertCustomer.setString(7,(String) session.get("state"));
insertCustomer.setString(8,(String) session.get("zipCode"));
insertCustomer.setString(9,(String) session.get("country"));
insertCustomer.setString(10,(String) session.get("email"));
insertCustomer.setString(11,(String) session.get("email2"));
insertCustomer.setString(12,(String) session.get("phone"));
insertCustomer.setString(13,(String) session.get("cell"));

insertCustomer.executeUpdate();


//isolation Level from database
try{
int trans = con.getTransactionIsolation();

} catch(SQLException w){
log.info(w.getMessage());

}

// selectCustomer.setString(1,(String) session.get("lastName")) ;
// selectCustomer.setString(2,(String) session.get("firstName"));

rs = selectCustomer.executeQuery();
while (rs.next()) {
customerid = rs.getLong(1);
}




insertUser.setString(1, (String) session.get("username"));
insertUser.setString(2,(String) session.get("password"));
insertUser.setLong(3, customerid);

insertUser.executeUpdate();

//insert account

insertPayment = con.prepareStatement(insertPay);
selectPayment = con.prepareStatement(selectPay);
insertInvoice = con.prepareStatement(insertInv);
insertCreditCard = con.prepareStatement(insertCredCard);
selectCreditCard = con.prepareStatement(selectCredCard);
insertAccount = con.prepareStatement(insertAcct);



insertPayment.setLong(1, customerid);
insertPayment.setString(2, (String) session.get("paymenttype"));
insertPayment.setDate(3, (java.sql.Date) today );
insertPayment.setString(4, (String) session.get("paymenttype"));
insertPayment.setInt(5, 31);

insertPayment.executeUpdate();


//get paymentid from payment
// selectPayment.setLong(1, customerid);
rs = selectPayment.executeQuery();
while (rs.next()) {
paymentid = rs.getLong(1);
}

insertInvoice.setString(1, ( today + (String)session.get("lastName") + customerid).toUpperCase());
insertInvoice.setDate(2,today);
insertInvoice.setDouble(3, new Double((String) session.get("ccamount")).valueOf((String)session.get("ccamount")));
insertInvoice.setDouble(4, state_tax);
insertInvoice.setLong(5, paymentid);

insertInvoice.executeUpdate();

insertCreditCard.setLong(1,customerid);
insertCreditCard.setLong(2,paymentid);
insertCreditCard.setString(3,(String)session.get("paymenttype"));
insertCreditCard.setString(4,(String)session.get("firstName")+ " "+ (String)session.get("middleName")+" "+ (String)session.get("lastName"));
insertCreditCard.setDate(5, dt.getDate1(year, month, day ));
insertCreditCard.setDouble(6, new Double((String)session.get("ccnumber")).valueOf((String)session.get("ccnumber")));
insertCreditCard.setDouble(7,new Double((String)session.get("ccamount")).valueOf((String)session.get("ccamount")));
insertCreditCard.setString(8,(String)session.get("firstName2"));
insertCreditCard.setString(9,(String)session.get("middleName2"));
insertCreditCard.setString(10,(String)session.get("lastName2"));
insertCreditCard.setString(11, (String)session.get("address1B"));
insertCreditCard.setString(12,(String)session.get("address2B"));
insertCreditCard.setString(13,(String)session.get("city2"));
insertCreditCard.setString(14,(String)session.get("state2"));
insertCreditCard.setString(15,(String)session.get("zipCode2"));
// insertCreditCard.setString(16,(String)session.get("country2"));


insertCreditCard.executeUpdate();

//get creditcardid from creditcard
// selectCreditCard.setLong(1,customerid);

rs = selectCreditCard.executeQuery();
while (rs.next()) {
creditcardid = rs.getLong(1);
}

insertAccount.setLong(1,customerid);
insertAccount.setLong(2,paymentid);
insertAccount.setLong(3,creditcardid);

insertAccount.executeUpdate();


//insert profile


insertProfile = con.prepareStatement(insertProf);

insertProfile.setLong(1,customerid);
insertProfile.setDate(2, dt.getDate1((String)session.get("birthyear"),(String)session.get("birthmonth"),(String)session.get("birthday")));
insertProfile.setString(3,(String)session.get("gender"));
insertProfile.setString(4, (String)session.get("maritalStatus"));
insertProfile.setString(5, (String)session.get("livingStatus"));
insertProfile.setString(6,(String)session.get("ethnicity"));
insertProfile.setString(7,(String)session.get("seeking"));
insertProfile.setString(8,(String)session.get("children"));
insertProfile.setString(9,(String)session.get("religion"));
insertProfile.setString(10,(String)session.get("sexualOpenness") );
insertProfile.setString(11,(String)session.get("describeLooks"));
insertProfile.setString(12,(String)session.get("hairColor"));
insertProfile.setString(13,(String)session.get("eyeColor"));
insertProfile.setString(14,(String)session.get("height"));
insertProfile.setString(15,(String)session.get("bodyType"));
insertProfile.setString(16,(String)session.get("education"));

insertProfile.setString(17, (String)session.get("career"));
insertProfile.setString(18,(String)session.get("politics"));
insertProfile.setString(19,(String) session.get("incomeLevel") );
insertProfile.setString(20,(String)session.get("smokingHabit") );
insertProfile.setString(21, (String)session.get("drugHabit") );
insertProfile.setString(22,(String)session.get("drinkingHabit") );
insertProfile.setString(23,(String)session.get("aboutMe") );
insertProfile.setString(24,(String)session.get("attractedTo") );
insertProfile.setString(25, (String)session.get("likes") );

insertProfile.executeUpdate();

//get profileId
// selectBlob.setLong(1,customerid);
rs = selectBlob.executeQuery();
while (rs.next()) {
profileid = rs.getLong(1);
}






PreparedStatement pstmt = null;

String statement4= "SELECT PHOTOS, PHOTOS1,PHOTOS2,PHOTOS3,PHOTOS4,PHOTOS5 FROM STRUTS.PROFILE WHERE PROFILEID = ? FOR UPDATE";


rs = pstmt.executeQuery(statement4);

pstmt.setLong(1,profileid);

while( rs.next())
{
for( int y=1;y <= numberofImages;y++) { //for each column
myRegularBlob[y] = rs.getBlob(y);

}
}

//get images to db
int i =0;File val = null;
ServletContext servletContext2 = null;
servletContext2 = ServletActionContext.getServletContext();

dataDir = servletContext2.getRealPath("/photos/");

File file = new File(dataDir);

if (!file.exists() || !file.isDirectory()) {

System.out.println("Parameter is not a directory");
//System.exit(1);
}
File[] fileArray = file.listFiles();

for ( i = 0; i < fileArray.length; i++) {

for ( int z=0; z < numberofImages; z++) {
val = (File) session.get("photos" + String.valueOf(z));

if( fileArray[i].getName().endsWith(val.getName()) == true){

try {

instream = new FileInputStream("C:\\Users\\janice\\workspace\\StrutsAction\\build\\weboutput\\photos\\"+ fileArray[i]);


outStream[z] = (OutputStream)myRegularBlob[z].setBinaryStream(1l);
// use buffer to write from file to db
int size = ((ServletResponse) myRegularBlob[z]).getBufferSize();
buffer = new byte[size];
int length = -1;

while ((length = instream.read(buffer)) != -1)
{
outStream[z].write(buffer, 0, length);
}
inStream[z].close();
outStream[z].close();

} catch(IOException w){
log.info(w.getMessage());

} catch(IndexOutOfBoundsException e){
log.info(e.getMessage());
}
}//end if
} //end inner for/next
} //end outer for/next
// con.commit();
//close add member
insertCustomer.close();
insertUser.close();
selectCustomer.close();

//close add account
insertPayment.close();
selectPayment.close();
insertInvoice.close();
insertCreditCard.close();
selectCreditCard.close();
insertAccount.close();

// con.close();
rs.close();

//transacton object
con.setAutoCommit(true);


} catch (SQLException e ) {
e.printStackTrace();
log.info(String.valueOf(e.getErrorCode() ));

log.info(e.getMessage());
if (con != null) {
try {
System.err.print("Transaction is " +
"being rolled back");
con.rollback();
con.close();
rs.close();
} catch(SQLException excep) {
log.info(excep.getMessage());
}
}
}

return SUCCESS;

}
/**
//find out how many photos to update
switch (numberofImages) {
case 5:
myRegularBlob[31].setBinaryStream(1l);
break;
case 4:
insertProfile.setBinaryStream(31, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(32, bs = null, (int) (filesize = 0));
break;
case 3:
insertProfile.setBinaryStream(30, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(31, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(32, bs = null, (int) (filesize = 0));
break;
case 2:
insertProfile.setBinaryStream(29, null, 0);
insertProfile.setBinaryStream(30, null, 0);
insertProfile.setBinaryStream(31, null, 0);
insertProfile.setBinaryStream(32, null, 0);
break;
case 1:
insertProfile.setBinaryStream(29, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(30, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(31, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(32, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(33, bs = null, (int) (filesize = 0));
break;
case 0:
insertProfile.setBinaryStream(29, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(30, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(31, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(32, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(33, bs = null, (int) (filesize = 0));
insertProfile.setBinaryStream(34, bs = null, (int) (filesize = 0));
break;

default:
log.info("no images");
break;
}
**/


private PreparedStatement executeQuery(String sql) {
// TODO Auto-generated method stub
return null;
}






} //END OF CLASS


Not sure how to use code tag?
In edit I clicked on 'Code' Button and pasted code?

Palak Mathur
Ranch Hand

Joined: Jan 29, 2007
Posts: 313

Select the entire code and then Click on Code button.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19670
    
  18

And please leave out all the irrelevant stuff. Nobody likes reading through lots of code.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
janice garcia
Greenhorn

Joined: May 23, 2012
Posts: 4
I did review table datatype which were char datatypes. I changed them to varchar2 and set the session values to a string and set the binding variables, but this did not seem to help?
Palak Mathur
Ranch Hand

Joined: Jan 29, 2007
Posts: 313

janice garcia wrote:I did review table datatype which were char datatypes. I changed them to varchar2 and set the session values to a string and set the binding variables, but this did not seem to help?


Make the code readable, by putting it inside code tag and removing the code which is not necessary to be put here. It will be easier for us to help.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: prepared staements insert