I
Ike
I've amended a servlet I had found on the web for accessing a database. I am
wondering if you good folks could take a look at it as I am going to need it
to handle sometimes high volume simultaneously, and just want to have
someone with knowledgeable eyes take a look for perhaps glaring touble
ahead. In particular, the part below where I return the key (which is an
AUTO_INCREMENTED integer id) for any INSERT statements. This is the part
with the remarks "THIS CONERNS ME" below.
Thanks for any help. Gratefully, Ike
public class TestServlet2 extends HttpServlet {
/** Creates a new instance of TestServlet2 */
public TestServlet2() {
}
public void init(ServletConfig config) throws ServletException {
// This method initializes the servlet and only gets call once.
// Allocate all of the servlet resources here.
super.init(config);
}
public void destroy() {
// Once this method is called then any instance of this class can be
garbage collected
// Here is where all servlets resources can be deallocated.
}
// public synchronized void service (HttpServletRequest request,
HttpServletResponse response)
public void service(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException {
try {
String returnString = "OK";;
Vector headers = new Vector();
Vector rows = new Vector();
// get an input stream from the applet
(driver,url,userid,password,query-string)
ObjectInputStream inputFromApplet = new
ObjectInputStream(request.getInputStream());
Object connArray[] = (Object[])inputFromApplet.readObject();
inputFromApplet.close();
String driver = (String)connArray[0];
String url = (String)connArray[1];
String user = (String)connArray[2];
String password = (String)connArray[3];
String SQLString = (String)connArray[4];
// perform query
int numberOfRows = 0;
Statement st = null;
Connection con = null;
try {
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url,user,password);
if(SQLString.indexOf("?")>-1){//this is a prepared statement
ArrayList conntypes=((ArrayList)connArray[5]);
st =con.prepareStatement(SQLString);
for(int u=6;u<connArray.length;u++){
int t =
JavaClassNamesSQLTypes((String)conntypes.get(u-6));
switch(t){
case Types.VARCHAR:
((PreparedStatement)st).setString(u-5, (String)connArray);break;
case Types.INTEGER:
((PreparedStatement)st).setInt(u-5,
((Integer)connArray).intValue());break;
case Types.DOUBLE:
((PreparedStatement)st).setDouble(u-5,
((Double)connArray).doubleValue());break;
case Types.FLOAT:
((PreparedStatement)st).setFloat(u-5,
((Float)connArray).floatValue());break;
case Types.BOOLEAN:
((PreparedStatement)st).setBoolean(u-5,
((Boolean)connArray).booleanValue());break;
case Types.TIME:
((PreparedStatement)st).setTime(u-5, (java.sql.Time)connArray);break;
case Types.DATE:
((PreparedStatement)st).setDate(u-5, (java.sql.Date)connArray);break;
default:/*this will be the blob*/;
}
}
/* OLD WAY:
for(int u=6;u<connArray.length;u++){
if(((String)conntypes.get(u-6)).equals("java.lang.String"))
((PreparedStatement)st).setString(u-5,
(String)connArray);
}
*/
((PreparedStatement)st).execute();
}else{
st = con.createStatement();
st.execute(SQLString);
}
int updRows = st.getUpdateCount(); // -1 if no updates
if (updRows > 0) { // update,insert......
returnString = ("Rows affected: " + updRows);
}
else if (updRows == 0) { // no updates
returnString = ("Error, no rows affected");
}
else { // result of a sql-select
ResultSet rs = st.getResultSet();
ResultSetMetaData md = rs.getMetaData();
// headers
int numberOfColumns = md.getColumnCount();
for(int column = 0; column < numberOfColumns; column++)
{
headers.addElement(md.getColumnLabel(column+1));
}
// result
while (rs.next()) {
numberOfRows++;
Vector newRow = new Vector();
for (int i = 1; i <= numberOfColumns; i++) {
//newRow.addElement(rs.getString(i));
//newRow.addElement(rs.getObject(i));
if(((String)headers.get(i-1)).equals("picture")){
try{
Blob blob = rs.getBlob(i);
if(blob!=null){
int iLength = (int)(blob.length());
byte[] jack = blob.getBytes( 1,
iLength );
newRow.addElement(jack);
}else{
//dont bother - null Objects do not
get added to a Vector
newRow.addElement(new
javax.swing.ImageIcon().getImage());
}
}catch(Exception ex){returnString=
ex.toString();}
} else
newRow.addElement(rs.getObject(i));
}
// System.out.println("\n row:
"+(rs.getObject(i)));
rows.addElement(newRow);
}
rs.close();
if (numberOfRows == 0) returnString = "no rows
selected";
}
st.close();
/** THIS CONERNS
ME...........****************************************************/
if(SQLString.startsWith("INSERT")){//if it is an insert,
return the id (which is an INT) as String
ResultSet rs=null;
try{
st = con.createStatement();
rs = st.executeQuery("SELECT LAST_INSERT_ID()");
if (rs.next())
returnString=Integer.toString(rs.getInt(1));
}catch(Exception ignore0){}
if(rs!=null)
rs.close();
if(st!=null)
st.close();
}
/***************************************************************************
/
con.close();
}
catch (SQLException e) {
if (st != null) st.close();
if (con != null) con.close();
returnString = e.toString();
}
// send objects back to applet
ObjectOutputStream outputToApplet = new
ObjectOutputStream(response.getOutputStream());
outputToApplet.writeObject(returnString); // sql-message
outputToApplet.writeObject(headers); // fieldnames
outputToApplet.writeObject(rows); // result-vector
outputToApplet.flush();
outputToApplet.close();
}
catch(Exception e) {
e.printStackTrace();
}
}
public static int JavaClassNamesSQLTypes(String sqltype){
if(sqltype.equals("java.lang.Boolean"))
//return Types.BIT;
return Types.BOOLEAN;
else if(sqltype.equals("java.math.BigInteger"))
return Types.BIGINT;
else if(sqltype.equals("java.math.BigDecimal"))
return Types.DECIMAL;
else if(sqltype.equals("java.lang.String"))
return Types.VARCHAR;
else if(sqltype.equals("java.lang.Integer"))
return Types.INTEGER;
else if(sqltype.equals("java.lang.Short"))
return Types.SMALLINT;
else if(sqltype.equals("java.lang.Byte"))
return Types.TINYINT;
else if(sqltype.equals("java.lang.Double"))
return Types.DOUBLE;
else if(sqltype.equals("java.lang.Float"))
return Types.FLOAT;
else if(sqltype.equals("java.sql.Time"))
return Types.TIME;
else if(sqltype.equals("java.sql.Timestamp"))
return Types.TIMESTAMP;
else if(sqltype.equals("java.util.Date"))
return Types.DATE;
else
return Types.BLOB;
}
}
wondering if you good folks could take a look at it as I am going to need it
to handle sometimes high volume simultaneously, and just want to have
someone with knowledgeable eyes take a look for perhaps glaring touble
ahead. In particular, the part below where I return the key (which is an
AUTO_INCREMENTED integer id) for any INSERT statements. This is the part
with the remarks "THIS CONERNS ME" below.
Thanks for any help. Gratefully, Ike
public class TestServlet2 extends HttpServlet {
/** Creates a new instance of TestServlet2 */
public TestServlet2() {
}
public void init(ServletConfig config) throws ServletException {
// This method initializes the servlet and only gets call once.
// Allocate all of the servlet resources here.
super.init(config);
}
public void destroy() {
// Once this method is called then any instance of this class can be
garbage collected
// Here is where all servlets resources can be deallocated.
}
// public synchronized void service (HttpServletRequest request,
HttpServletResponse response)
public void service(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException {
try {
String returnString = "OK";;
Vector headers = new Vector();
Vector rows = new Vector();
// get an input stream from the applet
(driver,url,userid,password,query-string)
ObjectInputStream inputFromApplet = new
ObjectInputStream(request.getInputStream());
Object connArray[] = (Object[])inputFromApplet.readObject();
inputFromApplet.close();
String driver = (String)connArray[0];
String url = (String)connArray[1];
String user = (String)connArray[2];
String password = (String)connArray[3];
String SQLString = (String)connArray[4];
// perform query
int numberOfRows = 0;
Statement st = null;
Connection con = null;
try {
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url,user,password);
if(SQLString.indexOf("?")>-1){//this is a prepared statement
ArrayList conntypes=((ArrayList)connArray[5]);
st =con.prepareStatement(SQLString);
for(int u=6;u<connArray.length;u++){
int t =
JavaClassNamesSQLTypes((String)conntypes.get(u-6));
switch(t){
case Types.VARCHAR:
((PreparedStatement)st).setString(u-5, (String)connArray);break;
case Types.INTEGER:
((PreparedStatement)st).setInt(u-5,
((Integer)connArray).intValue());break;
case Types.DOUBLE:
((PreparedStatement)st).setDouble(u-5,
((Double)connArray).doubleValue());break;
case Types.FLOAT:
((PreparedStatement)st).setFloat(u-5,
((Float)connArray).floatValue());break;
case Types.BOOLEAN:
((PreparedStatement)st).setBoolean(u-5,
((Boolean)connArray).booleanValue());break;
case Types.TIME:
((PreparedStatement)st).setTime(u-5, (java.sql.Time)connArray);break;
case Types.DATE:
((PreparedStatement)st).setDate(u-5, (java.sql.Date)connArray);break;
default:/*this will be the blob*/;
}
}
/* OLD WAY:
for(int u=6;u<connArray.length;u++){
if(((String)conntypes.get(u-6)).equals("java.lang.String"))
((PreparedStatement)st).setString(u-5,
(String)connArray);
}
*/
((PreparedStatement)st).execute();
}else{
st = con.createStatement();
st.execute(SQLString);
}
int updRows = st.getUpdateCount(); // -1 if no updates
if (updRows > 0) { // update,insert......
returnString = ("Rows affected: " + updRows);
}
else if (updRows == 0) { // no updates
returnString = ("Error, no rows affected");
}
else { // result of a sql-select
ResultSet rs = st.getResultSet();
ResultSetMetaData md = rs.getMetaData();
// headers
int numberOfColumns = md.getColumnCount();
for(int column = 0; column < numberOfColumns; column++)
{
headers.addElement(md.getColumnLabel(column+1));
}
// result
while (rs.next()) {
numberOfRows++;
Vector newRow = new Vector();
for (int i = 1; i <= numberOfColumns; i++) {
//newRow.addElement(rs.getString(i));
//newRow.addElement(rs.getObject(i));
if(((String)headers.get(i-1)).equals("picture")){
try{
Blob blob = rs.getBlob(i);
if(blob!=null){
int iLength = (int)(blob.length());
byte[] jack = blob.getBytes( 1,
iLength );
newRow.addElement(jack);
}else{
//dont bother - null Objects do not
get added to a Vector
newRow.addElement(new
javax.swing.ImageIcon().getImage());
}
}catch(Exception ex){returnString=
ex.toString();}
} else
newRow.addElement(rs.getObject(i));
}
// System.out.println("\n row:
"+(rs.getObject(i)));
rows.addElement(newRow);
}
rs.close();
if (numberOfRows == 0) returnString = "no rows
selected";
}
st.close();
/** THIS CONERNS
ME...........****************************************************/
if(SQLString.startsWith("INSERT")){//if it is an insert,
return the id (which is an INT) as String
ResultSet rs=null;
try{
st = con.createStatement();
rs = st.executeQuery("SELECT LAST_INSERT_ID()");
if (rs.next())
returnString=Integer.toString(rs.getInt(1));
}catch(Exception ignore0){}
if(rs!=null)
rs.close();
if(st!=null)
st.close();
}
/***************************************************************************
/
con.close();
}
catch (SQLException e) {
if (st != null) st.close();
if (con != null) con.close();
returnString = e.toString();
}
// send objects back to applet
ObjectOutputStream outputToApplet = new
ObjectOutputStream(response.getOutputStream());
outputToApplet.writeObject(returnString); // sql-message
outputToApplet.writeObject(headers); // fieldnames
outputToApplet.writeObject(rows); // result-vector
outputToApplet.flush();
outputToApplet.close();
}
catch(Exception e) {
e.printStackTrace();
}
}
public static int JavaClassNamesSQLTypes(String sqltype){
if(sqltype.equals("java.lang.Boolean"))
//return Types.BIT;
return Types.BOOLEAN;
else if(sqltype.equals("java.math.BigInteger"))
return Types.BIGINT;
else if(sqltype.equals("java.math.BigDecimal"))
return Types.DECIMAL;
else if(sqltype.equals("java.lang.String"))
return Types.VARCHAR;
else if(sqltype.equals("java.lang.Integer"))
return Types.INTEGER;
else if(sqltype.equals("java.lang.Short"))
return Types.SMALLINT;
else if(sqltype.equals("java.lang.Byte"))
return Types.TINYINT;
else if(sqltype.equals("java.lang.Double"))
return Types.DOUBLE;
else if(sqltype.equals("java.lang.Float"))
return Types.FLOAT;
else if(sqltype.equals("java.sql.Time"))
return Types.TIME;
else if(sqltype.equals("java.sql.Timestamp"))
return Types.TIMESTAMP;
else if(sqltype.equals("java.util.Date"))
return Types.DATE;
else
return Types.BLOB;
}
}