PreparedStatement with java
2 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   david_kelly
Posted On:   Tuesday, May 1, 2007 03:39 PM

Following from my thread about Java & MS Access, I got info from Robert that a prepared statement is the way forward for my needs. I have a few questions, after doing a little digging around the API & Google. when I insert or update data I was thinking of using a method, now I have a method that runs my main database so it opens the connection and statements, runs a query, then inserts or updates the table. if i want to make seperate methods to update and insert data on the table, do I need to have new conncetions open? example: public void data() { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String dataSourceName = &q   More>>

Following from my thread about Java & MS Access, I got info from Robert that a prepared statement is the way forward for my needs.



I have a few questions, after doing a little digging around the API & Google.



when I insert or update data I was thinking of using a method, now I have a method that runs my main database so it opens the connection and statements, runs a query, then inserts or updates the table.

if i want to make seperate methods to update and insert data on the table, do I need to have new conncetions open?

example:

			
public void data()
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String dataSourceName = "mdbTEST";
String dbURL = "jdbc:odbc:" + dataSourceName;
Connection con = null;
Statement s = null;
try
{
con = DriverManager.getConnection(dbURL, "","");
s = con.createStatement();
s.execute("SELECT * FROM "+tableName+" WHERE column_1 = '"+date+"'");
ResultSet rs = s.getResultSet();
boolean found = rs.next();
if (rs != null)
{
if(!found)
{
//insert(); //use this instead of next statement
s.execute(insert statement, but very big statement that works);
} //close if
else
{
//update(); //use this instead of next statement
s.execute(update statement that is big but works);
}//close else
} //close if
}
catch (Exception err)
{
System.out.println("
ERROR: ");
err.printStackTrace();
}
finally
{
s.close();
con.close();
}
}
catch (Exception err)
{
System.out.println("
ERROR from outside Catch statement: ");
err.printStackTrace();
}
}

/*
public void insert()
{
try
{
PreparedStatement insertRow;
String insert = "INSERT INTO " +tableName+ " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
insertRow = con.preparedStatement(insert);
insertRow.setString(1, date);
for(int i = 0; i <= 11; i++)
{
insertRow.setString(i+2,apps[i]); //setting from column_2 using begining of said array
}
for(int j = 0; j <= 4; j++)
{
insertRow.setInt(j+14, stats[j]); //setting from column_14 using begining of said array
}
insertRow.setString(19, noteData);
}
catch(Exception err)
{
System.out.println("
ERROR from insert() method: ");
err.printStackTrace();
}
}
*/

/*
public void update()
{
try
{
PreparedStatement updateDiary;
String update = "UPDATE " +tableName+ " SET * WHERE column_1 = " +date;
updateDiary = con.preparedStatement(update);
updateDiary.setString(1, date);
for(int i = 0; i <= 11; i++)
{
updateDiary.setString(i+2,apps[i]); //setting from column_2 using begining of said array
}
for(int j = 0; j <= 4; j++)
{
updateDiary.setInt(j+14, stats[j]); //setting from column_14 using begining of said array
}
updateDiary.setString(19, noteData);
}
catch(Exception err)
{
System.out.println("
ERROR from update() method: ");
err.printStackTrace();
}
}
*/



would I need to open the connections like the first try in the first method or cause I am calling the methods from within this method is it ok to let it be?



davy

   <<Less

Re: PreparedStatement with java

Posted By:   balasubramanyam_g  
Posted On:   Friday, July 6, 2007 05:03 AM

you can use already opened connection for insert or update.

Re: PreparedStatement with java

Posted By:   WarnerJan_Veldhuis  
Posted On:   Tuesday, May 1, 2007 05:28 PM

Any object has the scope of where it's declared. So if you declare your Connection con in public void data(), that's it's scope. The insert() and update() methods won't compile, since con is unknown.



There's two choices here:

1) Pass the connection as a parameter to the insert() and update() methods like this:

public void data() {
Connection con = DriverManager.getConnection(...);
insert(con);
}
public void insert(Connection c) {
Prep ps = c.prep....
}

Then you can call insert(con) so that the insert method can us variable c as a connection

2) Make the Connection object a class member. Ie, declare it in the beginning of the class like this:

public class MyDatabaseStuff {
private Connection con;

public void data() {
con = DriverManager.getConnection( yada yada yada);
}

public void insert() {
Prep ps = con.prep....
}
}

The last example is not my choice, since the connection time needs to be as short as possible. I'd go for option 1.



Happy coding :)



Surf
About | Sitemap | Contact