Java Coding question

Tharkun

Hi,

I am new to Java so forgive my ignorance on what might be am simple issue.

I was assigned a project the purpose of witch is to read from a file (done in another class), write the file to a database then read from the database.

The project was a list of Guns N Roses albums and songs that had to be sorted into a normalized database.

The part I am stuck on is writing the file to the database.
When trying to write duration I am receiving an error. In the database I have duration set as a time field. Any suggestions on resolving this would be greatly appriciated.

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Time;
import java.util.StringTokenizer;

import com.mysql.jdbc.Statement;

/**
 * 
 */

/**
 * @author Paul Murphy
 * 
 */
public class TextFileToTable {
    Connection con = null;
    Statement st;

    public static void main(String[] args) {
        System.out.println("Write Text File to Table!");
        TextFileToTable text = new TextFileToTable();
    }

    public Connection connection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException c) {
            System.out.println("Class not found!");
        }
        try {
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/music", "root", "root;");
        } catch (SQLException s) {
            System.out.println("Connection is not found!");
        }
        return con;
    }

    public TextFileToTable() {
        try {
            FileInputStream fstream = new FileInputStream(
            "C:/Users/ipt/Documents/music.csv");
            DataInputStream dstream = new DataInputStream(fstream);
            BufferedReader bf = new BufferedReader(new InputStreamReader(
                    dstream));
            String data = null;
            String comma = ";";
            int count = 0;
            while ((data = bf.readLine()) != null) {
                if (count++ == 0)
                    continue;
                StringTokenizer stoken = new StringTokenizer(data, comma);
                String artist = stoken.nextToken();
                String album = stoken.nextToken();
                String year = stoken.nextToken();
                String track_number = stoken.nextToken();
                int track_no = Integer.parseInt(track_number);
                String track_name = stoken.nextToken();
                String composer = stoken.nextToken();
                String duration = stoken.nextToken(); // The code fails here due
                // to the duration being
                // set to time in the
                // database
                int dur = Integer.parseInt(duration); // I tried setting it to
                // int as this was the
                // only way I thought it
                // would work
                st = (Statement) connection().createStatement();
                int t_artist = st
                .executeUpdate("INSERT INTO t_artist (artist) VALUES ('"
                        + artist + "')");
                int t_album = st
                .executeUpdate("INSERT INTO t_album (album, composer, year) VALUES ('"
                        + album
                        + "', '"
                        + composer
                        + "', "
                        + year
                        + ")");
                int t_track = st
                .executeUpdate("INSERT INTO t_track (track_number, track_name, duration) VALUES ("
                        + track_no
                        + ", '"
                        + track_name
                        + "', "
                        + dur
                        + ")");
            }

            System.out.println("All data are inserted in the database table");
            bf.close();
            st.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Tharkun

Thought it might also be helpful to post the error.

java.lang.NumberFormatException: For input string: "04:34"
    at java.lang.NumberFormatException.forInputString(Unknown Source)
    at java.lang.Integer.parseInt(Unknown Source)
    at java.lang.Integer.parseInt(Unknown Source)
    at TextFileToTable.<init>(TextFileToTable.java:70)
    at TextFileToTable.main(TextFileToTable.java:27)

jebuz

Can't you simply change the data type of duration from time to varchar in your database schema. You really only use time/date types when dealing with an actual date, a duration in a track should use a decimal or varchar.

jebuz

By the way, you are converting your string 'duration' to an integer called 'dur' and then effectively ignoring it by still passing 'duration' into the query.

Leave it as it is, remove dur and change the datatype of duration in the database to varchar(5) that should fix it

maggy_thatcher

You have two choices:

1. Have the DB field Duration as a varchar, and then just store the string directly
2. Have the DB field Duration as a decimal, then take the minutes/seconds as two separate numbers, dividing the seconds by 60 and adding them in

For the second option--
String duration = stoken.nextToken();
int mins = Integer.parseInt(duration.substring(0, duration.indexOf(':')));
int secs = Integer.parseInt(duration.substring(duration.indexOf(':') + 1));
double dur = mins + (secs / 60d);

As a BTW - you really shouldn't use non-prepared statements...doing it the way you have it has two big drawbacks:

• The DB statement cache is going to fill up with lots of different values each time
• You are wide open to SQL injection attacks

Not only that -- it also means you can keep the one statement, and reuse it for each row, rather than having to keep rebuilding the statement each and every time.

jebuz

maggy_thatcher wrote: »

[*]You are wide open to SQL injection attacks

It sounds like a college project, he is obviously learning and I don't think he needs to worry about that sort of thing yet.

maggy_thatcher

jebuz wrote: »

It sounds like a college project, he is obviously learning and I don't think he needs to worry about that sort of thing yet.

True - but in my view, it's better to learn how to do it "the proper way" and never do it the other way at all

It results in cleaner, safer & faster code. There's virtually no downsides.

jebuz

Ah yeah completely agree, it's good to be aware of these things but the "proper way" may be a little overwhelming to a learning student, I'd go so far as to say that clean and safe code rarely comes into the equation for college projects.

I remember when I was in college, it was an achievement to get the code performing it's primary task, clean and safe code using best practices always (and in hindsight, mistakingly) took a backseat and I know I wasn't the only one. But that improved with experience and time and learning off others. But yes, no harm being aware of it and trying to put it into practice as early as possible, as you say, no downsides.

Tharkun

Thanks for the help guys, decided to go with your your second option Maggy.

Now I just have one last questions. How would I go about using a where statement in the following code? If I wanted to get the duration of a certain album. Sorry for what may be poor coding but I've only been doing this for 6 weeks now

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;

/**
 * 
 */

/**
 * @author Paul Murphy
 *
 */
public class AlbumLength {

    public static void main(String[] args) {
        Connection con = null;
        int sum = 0;
        String col_name;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/music", "root", "root;");
            try {
                Statement st = (Statement) con.createStatement();
                BufferedReader bf = new BufferedReader(new InputStreamReader(
                        System.in));
                System.out.println("Enter table name:");
                String table = bf.readLine();
                System.out.println("Enter column name which have to sum:");
                String col = bf.readLine();
                System.out.println("Enter album name which have to sum:");
                String album = bf.readLine();
                ResultSet res = (ResultSet) st.executeQuery("SELECT SUM" + "(" + col + ")"
                        + " FROM " + table);// + " WHERE album="+ album); // Tried using this for the WHERE statement but had no luck
                while (res.next()) {
                    double c = res.getInt(1);
                    sum = (int) (sum + c);
                }
                System.out.println("Sum of column = " + sum);
            } catch (SQLException s) {
                System.out.println("SQL statement is not executed!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

maggy_thatcher

Tharkun wrote: »

Thanks for the help guys, decided to go with your your second option Maggy.

Now I just have one last questions. How would I go about using a where statement in the following code? If I wanted to get the duration of a certain album. Sorry for what may be poor coding but I've only been doing this for 6 weeks now

SQL requires using single quotes for values -- you have

... " WHERE album="+ album);

But that is looking for a column called (the name of the album) - so you'll get errors back.

Either:

1. Change that to

   WHERE album='" + album + "'");
   

2. Use a PreparedStatement

A Prepared Statement would be:

PreparedStatement stmt = con.prepareStatement("SELECT SUM(" + col + ") FROM " + table + " where album = ?");
stmt.setString(1, album);
ResultSet res = stmt.executeQuery();

(I'm not sure why you'd want to sum a provided column name but I'm sure you have your reasons )

This also has the advantage of working if the album has apostrophes in it...

Tharkun

The prepared statement seems to work better, due to it being the only one to display the result But it is giving me a result of 0.

Here is the information of the table if it helps.

1 Welcome to the Jungle Appetite for Destruction 4.57

maggy_thatcher

Tharkun wrote: »

The prepared statement seems to work better, due to it being the only one to display the result But it is giving me a result of 0.

Here is the information of the table if it helps.

1 Welcome to the Jungle Appetite for Destruction 4.57

First - you are retrieving ints rather than doubles, so things aren't going to be rendered correctly. Use doubles everywhere instead (double sum, res.getDouble...etc)

Second - SELECT SUM() will only return one row in the way you have it (you haven't specified a GROUP BY clause).

Third - Using the regular mysql client, verify that the statement you have is correct (select sum(duration) from songs where album = 'Appetite for Destruction') and see what comes back (assuming 'duration' is the column name, and 'songs' is the table name).

Fourth - Insert additional logging to show you what's happening -- e.g. you're catching the SQL Exception, but not dumping what the exception is...Similarly - rather than immediately doing the conn.prepareStatement(...) - have something like String query = "SELECT ....", then conn.prepareStatement(query); - then do a System.out.println("Executing " + query) - which shows you what your app is trying to do...it allows you to trace what's going wrong easier (allows you to copy that statement verbatim straight into the mysql client).