Solved

SQL/Java

Posted on 2003-12-03
17
268 Views
Last Modified: 2010-03-31
Thank you for replying.

I would like to write a removeRecords method, which removes all the records from a sigle user who's account includes the bookauthor given. I am know using the create table function.

Statement stmt = conn.createStatement();
String sql = "CREATE TABLE " + books + " (" +
"username VARCHAR(8), bookAuthor VARCHAR(50), bookTitle CHARVAR(50));";
0
Comment
Question by:AntoniRyszard656
  • 9
  • 7
17 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 9865608
String delete = "DELETE FROM books WHERE username = ? AND bookAuthor = ?";

Use a PreparedStatement
0
 

Author Comment

by:AntoniRyszard656
ID: 9867481
Thank you for replying.

The table book has three fields username, bookauthor and booktitle

If I use the delete line as your explained would this also remove the booktitle value in a record if the username and bookauthor were found?




 
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9867687
It'd remove the whole row
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:AntoniRyszard656
ID: 9869887
Thank you for replying.

I would like to ask, in one of the databases methods I take a single string containing several words, then take the first 2 characters of each word and store these in a Stringbuffer.

The reason being I can not be sure how many words the string will contain. And there I think I could not store the 2 characters directly into  a string[]

I would now like to store the stringbuffer values and store these in a string[]. I have been trying this, though I could not workout how to loop through a stringbuffer. Any advice would be appreciated.

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9870750
If you just want the first two characters of each word in an array, you could do this sort of thing:

String source = "Alpha Beta Gamma Delta Epsilon";
String[] firstTwos = source.split(" ");
for(int i = 0;i < firstTwos.length;i++) {
      if (firstTwos[i].length() > 2) {
            firstTwos[i] = firstTwos[i].substring(0, 3);
      }
 
}
0
 
LVL 24

Expert Comment

by:sciuriware
ID: 9873432
SQL: don't create the table every time;
use "SHOW TABLES;" to see if it is already there.
Every unnecessary error message will obscure serious messages.
;JOOP!
0
 

Author Comment

by:AntoniRyszard656
ID: 9891868
Hello,

I saw in a Java text an Queue data structure, which adds and removed objects.

The text declared the Queue as   Queue q = new Queue();

Though I cannot find the Queue class in the jdk1.3

Could anyone suggest the import statement?

Thank you
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9892111
It was probably a custom class. They're very easy to make, using a List of some sort:

public class Queue extends LinkedList {

//FIFO queue

      public Object dequeue() {
            if (size() < 1)
                  throw new ArrayIndexOutOfBoundsException("Queue empty!");
            return removeFirst();
      }

      public void enqueue(Object o) {
            add(o);
      }
      
}
0
 

Author Comment

by:AntoniRyszard656
ID: 10011561
Thank you for replying.

I have written an add method already which adds to a access database, and this method works. Eventually I intend to use the mysql software rather than MS Access. For some reason the remove method here is not working, I have no errors but the rows are not being deleted. Just wondered if anyone could see the error.

Regards


Connection conn;
static String Book = "booktable";

void remove(String userName, String bookAuthor, String titleName){
try{
Statement stmt = conn.createStatement();
String sql = "CREATE TABLE " + Book + " (" + "user VARCHAR(10), author VARCHAR(50), booktitle CHARVAR(50));";

String delete = "DELETE FROM Book WHERE user = userName AND author = bookAuthor AND booktitle = titleName";
stmt.executeUpdate(delete.toString());

}catch(Exception e){

}
}
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10011601
You need to make sure the all resources are closed in your code. toString() is not necessary. Why are you creating tables btw?
0
 

Author Comment

by:AntoniRyszard656
ID: 10011705
Thank you for replying.

I have checked the code many times, and cannot find any errors and the method does not give any errors when running.

I am planned to link the Java file to mysql, and in the example I have they created a temp table in each of the methods and used gobel string Book. I used the create table function in the add method and this works.

Could you offer any further guidance with the remove.

Regards
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10011738
In your code example there is no code at all that deallocates resources. You create a Statement that never gets closed.
0
 

Author Comment

by:AntoniRyszard656
ID: 10012597
Thank you for replying.

I have written a disconnect function to be used in the main method, after this method is complete.

I decided to change the exception to type sql, the error now shown is, [Microsoft][ODBC Microsoft Access Driver] Too few prameters Expected 3.

I donot fully understand the Create table function, in the add method I used it to store the values before using the method stmt.executeUpdate(sqlInsert.toString());

Would it be possible to copy all the Access database values into the create table (string table) and delete the values then use the stmt.executeUpdate() function to update the Access database? Could you possible demonstrate this?

Many regards
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10012637
>>Too few prameters Expected 3.

Your function needs to pass the parameters to the statement. At the moment it is not doing so.

>>Would it be possible to copy ...

Please say in detail what your ultimate objectives are. I don't know what's going on with this temporary table stuff.
0
 

Author Comment

by:AntoniRyszard656
ID: 10037067
Thank you for replying.

My apologies for not replyng eariler, I decided to remove the create table line, and I also checked the three values were being passed correctly to the method, this workes fine. I have a access database with a table called Book, this table includes the fields user, author and booktitle. I wrote an add method which work's perfectly and adds to the table Book.

Though with this method I am still getting the error [Microsoft][ODBC Microsoft Access Driver] Too few prameters Expected 3.

void remove(String user1, String author1, String booktitle1){
try{
System.out.println(user1 + " " +author1 + " " + booktitle1);

Statement stmt = conn.createStatement();
String delete = "DELETE FROM Book WHERE user = user1 AND author = author1 AND booktitle = booktitle1";
stmt.executeUpdate(delete);

}catch(SQLException e){
System.err.println(e.getMessage());
}

disconnect();

}             //end remove
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 65 total points
ID: 10037147
>>Though with this method I am still getting the error ...

I mentioned the reason for that already:


>>Your function needs to pass the parameters to the statement. At the moment it is not doing so. Rather than concatenate Strings, you can use a PreparedStatement

final String DELETE_STATEMENT = "DELETE FROM Book WHERE user = ? AND author = ? AND booktitle = ?";
PreparedStatement ps = conn.prepareStatement(DELETE_STATEMENT);
ps.setString(1, user1);
ps.setString(2, author1);
ps.setString(3, booktitle1);
ps.executeUpdate();


0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10076653
8-)
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to parse custom JSON to POJO java 4 72
JDeveloper 12c for 32 bit 4 84
ArrayIndexOutOfBoundException 9 80
expectj telnet failing 5 35
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now