Java database question homework problem

Question

I have to write a Java program that creates a Car table with car manufacturers, models, model years, and fuel efficiency ratings. Insert several cars. Print out the average fuel efficiency. Use CREATE TABLE, INSERT, and SELECT AVG SQL commands.

This is a homework question I am having trouble starting. This has to be done solely in UNIX, like using mysql in a unix shell and writing the java code in the shell which I can do. I have also figured out how to make tables in the shell using mysql. I have the JDBC driver and access to mysql in unix. The problem is, is how to write the code to connect to the database (I have a url, username, and password). If you could help me with the code, I would greatly appreciate it. The shell I am running on has the JDBC driver built in and ready to use. This is what I have done, but I am not sure if its how you do it.

String userName = "user name";
String password = "user password";
String url = "jdbc:mysql://Hostname/DatabaseName";
Class.forName ("com.mysql.jdbc.Driver").newInstance ();
conn = DriverManager.getConnection (url, userName, password);

Please help walking me through this with some code examples. Thanks.

Answer 1

The JDBC tutorial is good, but you will only need 5% from there, and filtering out what you need is probably hard. A view simple lines that should get you started:

    Class.forName(...);
    Connection conn = DriverManager.getConnection(...);
    Statement stat = conn.createStatement();

    stat.execute("create table test(id int primary key, name varchar(255))");
    stat.execute("insert into test values(1, 'Hello')");
    ResultSet rs;
    rs = stat.executeQuery("select * from test");
    while (rs.next()) {
        System.out.println(rs.getString("name"));
    }
    rs.close();
    stat.close();
    conn.close();

In addition to that, you will also need PreparedStatement:

    PreparedStatement prep;
    prep = conn.prepareStatement("insert into account(name) values(?)");
    prep.setString(1, "Joe");
    prep.execute();
    prep.close();