Java-Gaming.org    
Featured games (79)
games approved by the League of Dukes
Games in Showcase (477)
Games in Android Showcase (107)
games submitted by our members
Games in WIP (536)
games currently in development
News: Read the Java Gaming Resources, or peek at the official Java tutorials
 
    Home     Help   Search   Login   Register   
Pages: [1]
  ignore  |  Print  
  Problems with JDBC  (Read 5792 times)
0 Members and 1 Guest are viewing this topic.
Offline Treebranch

Senior Newbie





« Posted 2009-01-01 00:43:50 »

I am trying to make a word game and obviously I need a word list to check the correctness of the words that people enter, so I found a .txt file containing all the words I could ever want and wrote some code to have the .txt file loaded line by line into a database using Java Database Connectivity, but now I am trying to query the database to see if it can retrieve information from the database. If it isn't able to retrieve anything, then it will tell the user that it is not a word, but if it is able to retrieve info, that means the word is in the database and therefore is a real word... My program doesn't work though, it keeps giving me the following error when I run it at the point in time that it tries to query the database...

ERROR: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Here is the code I am using, any ideas?

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
46  
47  
48  
49  
50  
51  
52  
53  
54  
55  
56  
57  
58  
59  
60  
61  
62  
63  
64  
65  
66  
67  
68  
69  
70  
71  
72  
73  
74  
75  
76  
77  
78  
79  
80  
81  
82  
83  
84  
85  
86  
87  
88  
89  
90  
91  
92  
93  
94  
95  
96  
97  
98  
99  
100  
101  
102  
103  
104  
105  
106  
107  
108  
109  
110  
111  
112  
113  
114  
115  
116  
117  
118  
119  
120  
121  
122  
/*
 * The purpose of this class is to test that I can connect to the database
 * I created with WordDBMaker.java and WordDBLoader.java and query the database
 * to check that words the user enters are in the database and therefore valid
 * words.
 */


/**
 * @author Josh Branchaud
 * @version 12/30/2008
 */


//*************************************
//***** Imported Classes **************
//*************************************
// added for reading user input from the console
import java.util.Scanner;

// added for connecting to and querying the database
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;

public class WordDBTester
{
    // create a volatile boolean variable for knowing when to end execution
   public volatile boolean endExecution = false;

    // the main method for this class
   public static void main(String[] args)
    {
        WordDBTester WDBT = new WordDBTester();

        // Scanner called in for reading user input from the console
       Scanner in = new Scanner(System.in);

        // try/catch block for connecting to the database and querying it.
       try
        {
            // specifying the driver we want to use for database connection
           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            // String variable holding the location of the database
           String DBLocation = "jdbc:odbc:WordList1";

            // connect to the database found at DBLocation
           Connection DBConnection = DriverManager.getConnection(DBLocation);

            // system message for confirmation of connection to the database
           System.out.println("Successfully connected to " + DBLocation);

            Statement s1 = DBConnection.createStatement();

            while(!(WDBT.endExecution))
            {
                System.out.println("Type 'quit!' if you would like to exit, otherwise");
                System.out.print("enter the word you would like to check: ");

                String inputWord = "";

                inputWord = in.nextLine();

                System.out.println("");

                if(inputWord.compareTo("quit!") == 0)
                {
                    WDBT.endExecution = true;
                    break;
                }

                System.out.println("The word you are checking for is " + inputWord);

                String wordQuery = "SELECT * FROM WordList1 WHERE Word = " + inputWord;

                System.out.println("The query looks like " + wordQuery);

                ResultSet rs = s1.executeQuery(wordQuery);

                int resultCount = 0;
                String thisWord = "";
                int pointValue = 0;

                while(rs.next())
                {
                    resultCount++;

                    thisWord = rs.getString("Word");
                    pointValue = rs.getInt("Points");
                }

                if(resultCount == 0)
                {
                    System.out.println("Sorry, but " + inputWord + " is not a word.");
                }
                else
                {
                    System.out.println(thisWord + " is a word and it is worth " + pointValue + " points.");
                }
            }

            // disconnect from the database
           DBConnection.close();

            // confirmation message of the disconnection from the database
           System.out.println("Disconnection successful!");
        }
        catch(ClassNotFoundException CNFError)
        {
            System.out.println("ERROR: " + CNFError);
        }
        catch(SQLException SQLError)
        {
            System.out.println("ERROR: " + SQLError);
        }
        catch(Exception Error)
        {
            System.out.println("ERROR: " + Error);
        }
    }
}


Offline Renoria

Junior Member




...


« Reply #1 - Posted 2009-01-01 02:33:43 »

You should use MySQL -> www.dev.mysql.com

Its way better than access, and PHP supports it too Wink

But for your simple word game, it would be better off using a encrypted file.

Anyway, this is how I used MySQL in my server:

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
           String driver = "com.mysql.jdbc.Driver";
           String url = "jdbc:mysql://localhost:3306/renoria";
           String username = "root";
           String password = "***************";
            try {
                Class.forName(driver); // touch the MySQL driver
           } catch (ClassNotFoundException e) {
                System.out.println("Database error:");
                e.printStackTrace();
            }
            try {
                Connection con = DriverManager.getConnection(url, username, password);
                allConnections.add(con);
                System.out.println("Database Connection Initiallized.");
                return con;
            } catch (SQLException e) {
                System.out.println("Database error:");
                e.printStackTrace();
                return null;
            }


Also, you must install the MySQL ODBC JDBC driver as a lib in your project.
Offline Treebranch

Senior Newbie





« Reply #2 - Posted 2009-01-02 02:09:36 »

I was wondering if mySQL might be a better way to go as far as databases go. Mostly, I went with Access because it was already on my machine. Where do I find a driver for that? Also, when you say I need to include it in my project library, do you mean the that the sql driver is a jar file and I need to add it to the compile time libraries?
Games published by our own members! Check 'em out!
Legends of Yore - The Casual Retro Roguelike
Offline ewjordan

Junior Member





« Reply #3 - Posted 2009-01-02 03:50:42 »

How big is your word list?  Generating and connecting to an external database just to check if a word is on the list seems like overkill if you ask me - can't you just store the thing in memory using a HashSet or something like that?
Offline Treebranch

Senior Newbie





« Reply #4 - Posted 2009-01-04 03:44:56 »

Well, the list has a little over 200,000 words, I am trying to get a slightly more extensive one though. Anyway, I considered a hashmap, but I don't really know anything about Hashsets. What do you suggest instead of a database, it seems to be working okay so far?
Offline woogley
« Reply #5 - Posted 2009-01-04 05:10:13 »

Personally.. I would go with an SQL database. 200k is a TON of memory to be holding in a HashMap. Plus, with SQL, you have already have wildcard searching and sorting done for you.

Before I go on, let me just tell you what's wrong with you're query:

1  
String wordQuery = "SELECT * FROM WordList1 WHERE Word = " + inputWord;


If I'm looking for the word "dog" .. here is what your query looks like to the DB:

1  
SELECT * FROM WordList1 WHERE Word = dog


The word "dog" needs to be surrounded by quotes.

Going back to the DB discussion..

I disagree with Renoria.. you should not use MySQL for this (unless the code you posted is for a server app). MySQL is a great production database for a server to use, but it's unreasonable to assume every client that downloads your application will have MySQL installed locally. Using an Access database is an okay alternative - since everything is local - but I don't know if that will work on non-Windows platforms.

In my opinion, the best option would be to use SQLite. SQLiteJDBC gives you the option to load a platform-specfific native DB driver, or pure-Java.

This way, you can let SQLite search the giant list of words for you, and you don't have to worry about JDBC driver compatibilities (because you can deploy SQLiteJDBC with your app).
Offline Treebranch

Senior Newbie





« Reply #6 - Posted 2009-01-04 05:56:24 »

I was looking at SQLLite, do I do the same coding as I have been doing and this is just a different driver in order to access my database?

Also, I have never done a project like this before and I am wondering once I have my word game complete (probably just with GUI) and a word list that I am happy with (stored in a database), how do I make this accessible to anyone? I am thinking of making it into an applet that will be on my website, do i need to have my database stored somewhere so that anyone from any computer can play the game and the game will be able to access the database?
Offline woogley
« Reply #7 - Posted 2009-01-04 06:06:52 »

Well, if you're using something like Access or SQLite, the DB is just a local file, so it's no different than accessing any other file.

Question.. for your word database, do you ONLY want to check if a word exists? Nothing more? If so, you can probably get away with reading a plain text file that contains all the words.

200k is a lot of words, but you can use a simple index strategy (All 'a' words go into a.txt, all 'b' words go into b.txt .. to reduce how much data you scan)

I wrote you an example you can build off of. The words.txt contains all the words. To build the index, run this command:

1  
java Index


This will create an index folder, and you'll see the a.txt, b.txt, c.txt.. etc. files in there.

Then run this:
1  
java Example


As you can see from the Example.. the speed of the word lookup doesn't take long at all.

So, building off this example.. all you have to do is add your words to words.txt, then rebuild the index. No database needed.
Offline Treebranch

Senior Newbie





« Reply #8 - Posted 2009-01-04 06:15:16 »

Ooh, I considered that idea a while ago, but didn't go anywhere with it. Would that work in the context of a web applet?
Offline woogley
« Reply #9 - Posted 2009-01-04 06:33:33 »

Sure, all you have to do is package your applet as a JAR, and include the index folder inside that JAR. Then to access the files you just call getClass().getResource(...)

Example: http://woogley.net/misc/WordList/applet.zip
Games published by our own members! Check 'em out!
Legends of Yore - The Casual Retro Roguelike
Offline Renoria

Junior Member




...


« Reply #10 - Posted 2009-01-04 11:14:57 »

actually, once its a JAR, you cannot use getResource() anymore. You MUST use getResourceAsStream().
Offline cylab

JGO Ninja


Medals: 38



« Reply #11 - Posted 2009-01-04 13:54:49 »

@Renoria
Thats a misconception, you can use getResource(), but you can't use getResource().getFile().

Mathias - I Know What [you] Did Last Summer!
Offline woogley
« Reply #12 - Posted 2009-01-04 15:56:52 »

actually, once its a JAR, you cannot use getResource() anymore. You MUST use getResourceAsStream().

If you ran the applet demo I posted, you would've immediately found out you are incorrect. I mean, dude, it is literally right in front of you.
Offline Treebranch

Senior Newbie





« Reply #13 - Posted 2009-01-04 18:04:14 »

Okay, that makes sense.

So it would really be faster to read through a file every time I want to check for a word rather than connect to a database at the beginning of the game and have O(1) search time the rest of the game?

Also, I had planned to use the database for a few extra things, for instance, there were some extra columns with the word list the contained how many points each word was worth as well as some other statistics about each word, this was all calculated at the time of populating the database, that way I wouldn't have to do it each time the user entered a word in actual game play.
Offline woogley
« Reply #14 - Posted 2009-01-04 20:02:42 »

So it would really be faster to read through a file every time I want to check for a word rather than connect to a database at the beginning of the game and have O(1) search time the rest of the game?

Well, this is debatable. SQL's search is probably faster on the millisecond level, but both options determine if a word exists in less than a second. It's instantaneous to the end user.

Normally I use SQLite whenever possible, since it has so much built in. But for an applet, getting JDBC to work consistently means you're likely going to have to sign your applet and include native libraries. SQLite does offer a pure-Java option that wouldn't require signging, but it is very slow.

So.. given the fact that you're using an applet, I think you would save yourself some trouble by staying away from using a database.
Offline ewjordan

Junior Member





« Reply #15 - Posted 2009-01-05 01:53:08 »

Personally.. I would go with an SQL database. 200k is a TON of memory to be holding in a HashMap. Plus, with SQL, you have already have wildcard searching and sorting done for you.
It sounds like you're already arriving at a reasonable solution; that said, it's probably worth pointing out that a HashSet can handle 200k elements in memory without much trouble:

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
46  
import java.util.HashSet;
import java.util.Random;

public class HashSetStressTest {

   /**
    * Stress test java.util.HashSet with random strings inserted.
    */

   public static void main(String[] args) {
      final int mapSize = 200000;
      System.out.println("Memory usage before HashSet creation: "+(Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory()));
      HashSet<String> map = new HashSet<String>(mapSize);
      for (int i=0; i<mapSize; ++i) {
         map.add(randomString());
      }
      System.out.println(mapSize + " element HashSet created.");
      long memoryUsed = (Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory());
      long tBegin = System.nanoTime();
      int foundCount = 0;
      for (int i=0; i<mapSize*100; ++i) {
         //System.out.println("Test "+i+": "+map.contains(randomString()));
        boolean found = map.contains(randomString());
         if (found) foundCount++;
      }
      long tEnd = System.nanoTime();
      System.out.println("Found "+foundCount+" random elements.");
      System.out.println("Done checking "+(100*mapSize)+" random elements.");
      System.out.println("Memory usage with HashSet in memory: "+memoryUsed);
      System.out.println("Took "+((tEnd-tBegin)/1000000000) + " seconds total.");
      double timePerElement = ((tEnd-tBegin)) / (100*mapSize);
      System.out.println("Nanos per check: "+timePerElement);
   }

   /** Generate a random 5 letter string. */
   static private String randomString() {
      StringBuilder s = new StringBuilder();
      Random rand = new Random();
      for (int i=0; i<5; ++i) {
         int myInt = rand.nextInt(25);
         char letter = (char)('a' + myInt);
         s.append(letter);
      }
      return s.toString();
   }
   
}


My results (Apple's 1.5 client JVM):

1  
2  
3  
4  
5  
6  
7  
Memory usage before HashSet creation: 441304
200000 element HashSet created.
Found 404694 random elements.
Done checking 20000000 random elements.
Memory usage with HashSet in memory: 17971792
Took 16 seconds total.
Nanos per check: 836.0


This is not meant to be an accurate benchmark or anything (in particular, those timings are probably overwhelmed by the random string generation), but it serves as a proof of concept: the point is, even with a 200,000 element HashSet, and wasting a lot of time generating strings, the lookup time is completely and utterly negligible (I can do 20 million checks in less than a minute), and the memory usage is about 20 megabytes - not exactly trim, but not overwhelming, either.

Not saying that this is necessarily the solution for you (I didn't realize you were talking about such a massive word list, I thought you were talking maybe 30k), just figured it's worth pointing out...I'd say go with a DB if you need the searching and wildcard features that it gives you, as woogley suggests; if you just need to check if something's on the list or not, there are a lot of solutions that don't involve extra dependencies, and to be honest, any of them will do just fine and you won't notice any difference performance-wise.
Offline Mr_Light

Senior Member




shiny.


« Reply #16 - Posted 2009-01-05 04:19:39 »

Actually the hashmap size in memory should be rather small, it's the words themselfs that take up the most space. Anyway 20mb is peanuts on any modern pc. if it's really a concern you can sacrifice speed for memory: just stream the file. The database is going to occour overhead as well. I can't see you getting that back by the extra optimalsations a database would bring. Esp since in this particular case most of the high impact optmalsations can be applied to the file based aproach and should be reasonable trivial to implement. As a bonus you can easilly add words by editing a file.

It's harder to read code than to write it. - it's even harder to write readable code.

The gospel of brother Riven: "The guarantee that all bugs are in *your* code is worth gold." Amen brother a-m-e-n.
Offline Treebranch

Senior Newbie





« Reply #17 - Posted 2009-01-05 06:17:57 »

If I do something like a hashmap or .txt files is the best way of checking to do a String.compareTo()... or is there a different/better way?
Offline Mr_Light

Senior Member




shiny.


« Reply #18 - Posted 2009-01-05 07:05:47 »

If you have some sort of collection you can use contains() given that it might not be fast enough (not likely) you can look at building a tree out of the words by using chars for nodes. Though just using the first letter(or two) as a key in a map might already be a 'good enough'.

As for using the text file, you can either stream the whole file (which is probably fast enough) but.. you can optimise by making sure the entries is the file are sorted or are sorted in a new file and use that and map by the first letter and skip to that byte(/char) or sort into different (temp?)files a.db b.db c.db and only read that file.. or combination of the two.

It's harder to read code than to write it. - it's even harder to write readable code.

The gospel of brother Riven: "The guarantee that all bugs are in *your* code is worth gold." Amen brother a-m-e-n.
Offline ewjordan

Junior Member





« Reply #19 - Posted 2009-01-05 07:15:07 »

If you do it in-memory and the only thing you need to do is check if a word is in the list, HashSet is probably the way to go - again, if you need any other functionality, it might not suffice.  A HashSet just contains a bunch of objects and the only really useful thing it does is allow you to use contains() to see if an element is there or not.  If you can fit your list in a reasonable amount of memory, then contains() will not cause any perceptible speed problems at all unless you're literally calling it thousands of times per frame.

Example:

1  
2  
3  
4  
5  
HashSet<String> mySet = new HashSet<String>();
mySet.add("Hello");
mySet.add("Goodbye");
boolean hasHello = mySet.contains("Hello"); //true
boolean hasGoodDay = mySet.contains("Good Day"); //false
Offline Treebranch

Senior Newbie





« Reply #20 - Posted 2009-01-05 08:40:57 »

I had originally thought of building a tree (char by char) or using something like a hashmap or hashset, but I was concerned because I didn't want to have to build the tree or hashset at the beginning of each running of the game. For instance when I was building my tree, it took almost 3 minutes to populate the tree with every path of valid words, plus I realized with building a tree that you would have to make a tree for each length of word assuming that you check for a valid word by seeing if the next node is null. However I suppose you could make a tree with nodes of two elements one is the char and two is a boolean saying if ending on this node maps the path of a valid word. Unless I am confused, I don't think I want to use a tree structure. Also, I am still concerned about the hashset... does it take too much build/initialization time?
Offline ewjordan

Junior Member





« Reply #21 - Posted 2009-01-05 09:06:54 »

Also, I am still concerned about the hashset... does it take too much build/initialization time?
On my computer, populating a 500k HashSet with random Strings takes about 5 seconds, including the String generation (which is extremely inefficient, too).

I'm not sure how long it would take to load the data in from a file, but the bottleneck will almost certainly be file I/O, not HashSet overhead.

If you're concerned, I'd just try it and see if it works well enough for you - the code is very simple, at least once you have the file loading handled.
Pages: [1]
  ignore  |  Print  
 
 
You cannot reply to this message, because it is very, very old.

 

Add your game by posting it in the WIP section,
or publish it in Showcase.

The first screenshot will be displayed as a thumbnail.

CogWheelz (15 views)
2014-07-30 21:08:39

Riven (21 views)
2014-07-29 18:09:19

Riven (14 views)
2014-07-29 18:08:52

Dwinin (12 views)
2014-07-29 10:59:34

E.R. Fleming (32 views)
2014-07-29 03:07:13

E.R. Fleming (12 views)
2014-07-29 03:06:25

pw (42 views)
2014-07-24 01:59:36

Riven (42 views)
2014-07-23 21:16:32

Riven (30 views)
2014-07-23 21:07:15

Riven (31 views)
2014-07-23 20:56:16
HotSpot Options
by dleskov
2014-07-08 03:59:08

Java and Game Development Tutorials
by SwordsMiner
2014-06-14 00:58:24

Java and Game Development Tutorials
by SwordsMiner
2014-06-14 00:47:22

How do I start Java Game Development?
by ra4king
2014-05-17 11:13:37

HotSpot Options
by Roquen
2014-05-15 09:59:54

HotSpot Options
by Roquen
2014-05-06 15:03:10

Escape Analysis
by Roquen
2014-04-29 22:16:43

Experimental Toys
by Roquen
2014-04-28 13:24:22
java-gaming.org is not responsible for the content posted by its members, including references to external websites, and other references that may or may not have a relation with our primarily gaming and game production oriented community. inquiries and complaints can be sent via email to the info‑account of the company managing the website of java‑gaming.org
Powered by MySQL Powered by PHP Powered by SMF 1.1.18 | SMF © 2013, Simple Machines | Managed by Enhanced Four Valid XHTML 1.0! Valid CSS!