File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Database? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Database?" Watch "Database?" New topic
Author

Database?

James Eman
Ranch Hand

Joined: Mar 10, 2013
Posts: 45
ok after some research im still confused can someone help. i am building a program that will submit data to a database after a some questions. i have a mysql server i use for my website so.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60053
    
  65

Database access in Java is provided by JDBC. I would search for tutorials on such. As you get more advanced you might want to research some ORM )(Object Relational Management) tools.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
James Eman
Ranch Hand

Joined: Mar 10, 2013
Posts: 45
yea i downloaded a jar file dose that go some where?
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60053
    
  65

Please TellTheDetails. What jar file? What kind of application are you writing? Have you looked up JDBC?
James Eman
Ranch Hand

Joined: Mar 10, 2013
Posts: 45
it called mysql connector java 5.1.23 bin do i need to import that or somthing and the program asks a list of questions then submits the responses to a database. i know java has some built in database functions i believe and i need to create a database called questions and then i have a few tables one takes the input the other has a list of usernames and passwords and last table has ids. i have done a few days worth of looking but just confusing myself more and more.
Keith Lynn
Ranch Hand

Joined: Feb 07, 2005
Posts: 2367
Yes, you need to have the full path including the jar file in your CLASSPATH. There are classes in java.sql that you can use to establish a connection and create and execute queries.
James Eman
Ranch Hand

Joined: Mar 10, 2013
Posts: 45
yes but where dose this jar file go?
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60053
    
  65

If this is web app, it doesn't matter where the jar file goes for compiling your classes. Put it wherever it makes the most sense for your build system. For deployment, it must be placed in the web app's WEB-INF/lib folder.

As far as the code goes, that's where you'll need to read up on JDBC. It's way too extensive to learn via a forum post.
James Eman
Ranch Hand

Joined: Mar 10, 2013
Posts: 45
so it dosn't go in like a file in the mysql application??
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60053
    
  65

What is a "mysql application"? You haven't really answered the question to being with. Is this a web app? A Swing app? Other?
James Eman
Ranch Hand

Joined: Mar 10, 2013
Posts: 45
its called MySQL connector 5.1.23 im a summing it is a library i need or an attachment to my database?
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60053
    
  65

I have no idea at all what you are trying to say with that last post. Please take the time to use complete sentences, as well as proper capitalization and punctuation. Clearly explain what you are trying to get across. If you want people to volunteer their time to help you, you need to take some time to make your posts understandable.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1478
    
  11

Sounds like you are out of your depth with this, so here's a few basic points that may help:

  • MySQL uses SQL to manipulate data in the database.
  • Java is not SQL, so your Java program cannot do this directly, but there is a set of standard Java APIs (classes, functions etc) called JDBC ("Java DataBase Connectivity") which provide Java functions for talking to the database e.g. creating a connection to the database, executing a SQL command etc.
  • Each database has its own implementation of the JDBC APIs, so you need to make the JDBC library for your database (MySQL Connector/J) available to your Java application. This will allow your app to make use of the JDBC functions etc. The best place to put the JDBC library depends on your application, as people have explained above.
  • Once your Java application can access the JDBC library, you need to figure out what you want to do - connect to the DB, read data, write data etc - then figure out how to do each of these tasks with the relevant JDBC calls. You'll probably need to work through some tutorials for this - Google for "MySQL Java tutorial" and pick one.

  • If you hit more problems after you've worked through the tutorials and are starting to build your application, come here and ask your questions, but try to be as specific as possible about what you are trying to do and where you think the problem is.

    One more tip: I recommend you try your SQL out in the MySQL SQL interpreter to make sure it works, before you put it into a Java JDBC call. It's much easier to de-bug your SQL if you are only dealing with a bad SQL statement rather than a bad SQL statement and a bad Java call.


    No more Blub for me, thank you, Vicar.
    James Eman
    Ranch Hand

    Joined: Mar 10, 2013
    Posts: 45
    OK I'm kinda getting this thanks.
    James Eman
    Ranch Hand

    Joined: Mar 10, 2013
    Posts: 45
    ok so this is what i have.





    But i get this error
    java.sql.SQLException: No suitable driver found for jdbc:m­ysql://website.com/test
    at java.sql.DriverManager.getConnection(DriverManager.java:604)
    at java.sql.DriverManager.getConnection(DriverManager.java:221)
    at Sql.main(Sql.java:8)
    at __SHELL2.run(__SHELL2.java:6)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at bluej.runtime.ExecServer$3.run(ExecServer.java:725)

    i would like it to display the items in the following table columns id username password
    chris webster
    Bartender

    Joined: Mar 01, 2009
    Posts: 1478
        
      11

    My guess is that your JDBC library is not on your runtime classpath, but maybe one of the Java folk can help you out there.

    Couple of other points to bear in mind:

    Don't use "SELECT * ...". You may not want to fetch all the columns of a table into your application,. This might be for reasons of security e.g. don't fetch the password - use your WHERE clause to check it within the database, or to save bandwidth and other resources e.g. what if your table has 1000 columns and you're only interested in 3 of them? Use "SELECT col1, col2..." instead.

    It's not a good idea to run your app through the "root" DB account, because "root" can do absolutely anything to your database. You should have a separate DB account for your application which only has the permissions necessary for your application. In fact, it's common to have one DB account for the application owner, who owns and can modify the relevant DB tables etc, then have a separate DB account for the application users, who have the minimum permissions needed to read/write data in those tables. Most DBs offer very powerful and flexible access control, so it's a good idea to use these facilities to keep your app secure and prevent people doing stupid things by accident.
    James Eman
    Ranch Hand

    Joined: Mar 10, 2013
    Posts: 45
    i just had it fech the users and passwords of users because i couldn't think of a better to fetch. so should i post this in the advanced java forums?
    Bear Bibeault
    Author and ninkuma
    Marshal

    Joined: Jan 10, 2002
    Posts: 60053
        
      65

    James Eman wrote:so should i post this in the advanced java forums?

    No. This is the appropriate place to discuss database code.
    James Eman
    Ranch Hand

    Joined: Mar 10, 2013
    Posts: 45
    ok so i think i fix it then i got this error my new code is below then under it is the error




    error
    java.sql.SQLException: null, message from server: "Host 'www.asusnetwork.net' is not allowed to connect to this MySQL server"
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1110)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2465)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2498)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2283)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:822)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:404)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:317)
    at java.sql.DriverManager.getConnection(DriverManager.java:579)
    at java.sql.DriverManager.getConnection(DriverManager.java:221)
    at Test.main(Test.java:15)
    at __SHELL0.run(__SHELL0.java:6)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at bluej.runtime.ExecServer$3.run(ExecServer.java:725)

    Rob Spoor
    Sheriff

    Joined: Oct 27, 2005
    Posts: 19543
        
      16

    The error message is clear. Your current host is not white listed to be able to connect. Contact your DBA.


    SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
    How To Ask Questions How To Answer Questions
    James Eman
    Ranch Hand

    Joined: Mar 10, 2013
    Posts: 45
    ok since i know nothing about MySQL and im going to need the program to be broadcasted to many computers over many networks how do i change that? mysql is not my strong suit.
    Rob Spoor
    Sheriff

    Joined: Oct 27, 2005
    Posts: 19543
        
      16

    http://dev.mysql.com/doc/mysql-security-excerpt/5.6/en/adding-users.html

    You'll see in the example that it creates two users called monty - one that can only connect from the same machine ('monty'@'localhost') and one that can connect from any machine ('monty'@'%'). If you check your user table you'll probably see that all records for your username have a different host specified.
    James Eman
    Ranch Hand

    Joined: Mar 10, 2013
    Posts: 45
    ok so my scripted was below im using mysql workbench on the same database as my website and the website is set to be the default database i think

    CREATE USER 'school'@'%' IDENTIFIED BY 'some_pass';
    GRANT ALL PRIVILEGES ON *.* TO 'school'@'%'

    i get the same error as before
    error
    java.sql.SQLException: null, message from server: "Host 'www.asusnetwork.net' is not allowed to connect to this MySQL server"
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1110)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2465)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2498)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2283)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:822)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:404)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:317)
    at java.sql.DriverManager.getConnection(DriverManager.java:579)
    at java.sql.DriverManager.getConnection(DriverManager.java:221)
    at Test.main(Test.java:15)
    at __SHELL0.run(__SHELL0.java:6)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at bluej.runtime.ExecServer$3.run(ExecServer.java:725)
    James Eman
    Ranch Hand

    Joined: Mar 10, 2013
    Posts: 45
    ok so i fixed part of it now it works kinda my code below


    what is the best way to add a new row to the table in sql is there a add query or something so i dont need to find the next primary id every time and block dupicates
    Rob Spoor
    Sheriff

    Joined: Oct 27, 2005
    Posts: 19543
        
      16

    Use auto-generated keys. You can retrieve those using Statement.getGeneratedKeys() if you have called one of the execute methods with Statement.RETURN_GENERATED_KEYS or the column names.
    James Eman
    Ranch Hand

    Joined: Mar 10, 2013
    Posts: 45
    is there a way to connect to the mysql server without the connector jar file i want to have people download the jar file for my application but don't want them to have to download the other file too.
    Paul Clapham
    Bartender

    Joined: Oct 14, 2005
    Posts: 18127
        
        8

    No.
    James Eman
    Ranch Hand

    Joined: Mar 10, 2013
    Posts: 45
    like could i copy some classes or something?? i just would like to have my program stay in one file
    Paul Clapham
    Bartender

    Joined: Oct 14, 2005
    Posts: 18127
        
        8

    James Eman wrote:i just would like to have my program stay in one file


    Why? Usually when people want to install something on somebody else's computer which is more complicated than a single file, they use an installer. You realize that you have to make sure that the target machine has a compatible version of Java installed, don't you? A Java installer takes care of that for you. Although I would really recommend using Java Web Start to distribute your application, which takes care of most of the things which an installer does and quite a few things which it doesn't do. And did you plan on having MySQL installed on the target computer and having your database set up there, or were you planning to access a centralized database over the web?
    James Eman
    Ranch Hand

    Joined: Mar 10, 2013
    Posts: 45
    a central database on my home server while the program will be on clients across the web
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Database?
     
    Similar Threads
    concurrent access to derby database
    timing in Java
    Tomcat hanging
    Getting data from a Thread
    how to select 2nd,3rd or the Nth highest salary from a table in desc or ascen order?